Reseting the unique id if a table.

I'm building a web site. there is a database.

I've set a primary key and unique of a field.also I set it to auto numbering.

Everytime I insert a record , that field will increase 1 (type bigint , start from 1).

After lots time of inserting record , the id going to be larger number.. I wondering how can I reset that to zero?

Run query Truncate Table YuorTableName

Reseting the Auto-Number for a table back to zero and Compact/Repair or Unload/Reload for

After deleting all the test data from all tables in a SQL 2000 database, is there a way to reset all the auto-incrementing fields back to zero in one shot? In Access, you can run the Compact and Repair option. Also, in Sybase SQL, there was an "unload/reload" option to reduce the database size. Is there a similar function in SQL2000? Thanks for all the help

Try to use truncate instead of delete to remove records from your table, it will remove records and reset identity fields to starting values.

Thanks

Reseting Select Permissions for Public Role

Every night, there are some stored procedures that run to recreate tables so that the information in the table is updated. After the tables are droped and recreated I have to go in and check the select box under the permissions for the public role. If i don't do this users will not be able to select from theres tables.

What can I do so that users are able to select from these tables after they are created?

Would you be able to specify the select permissions for the public role in the script that creates the table or run a script that gives all those tables select permissions for the public role?

All help is appreciated.I'd use GRANT SELECT ON (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp) within the script.

-PatP|||That did the trick. Thanks for all the help.sql

Reseting Indexes

I just migrated a Microsoft Access 2002 database to SQL Server 2005. So far
everything is working great. While updating my Access.mdb file to an
Access.ADP file I've been testing adding, editing and deleting records to
various tables so that I can ensure my Stored Procedures are working
properly.
Now, I have deleted much of my sample data and want all of my indexes to
restart with the lowest number, typically being Number 1. In Access, I would
empty the table, then compact/repair the database and the indexes would be
reset. How do I achieve this with SQL Server 2005 for the following two
examples.
1. No records in the table, so I want to have the index restart at Number 1.
2. I have records in the table (i.e. records 1 to 9) and I want the next
index to start at 10. In this example, my index is currently starting at 23
becuase I've added records to the table and deleted some.
Thank You.<DIV>"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in
message news:BFDAB950-91CD-4248-B042-6008C35A05ED@.microsoft.com...</DIV>>I
just migrated a Microsoft Access 2002 database to SQL Server 2005. So far
> everything is working great. While updating my Access.mdb file to an
> Access.ADP file I've been testing adding, editing and deleting records to
> various tables so that I can ensure my Stored Procedures are working
> properly.
> Now, I have deleted much of my sample data and want all of my indexes to
> restart with the lowest number, typically being Number 1. In Access, I
> would
> empty the table, then compact/repair the database and the indexes would be
> reset. How do I achieve this with SQL Server 2005 for the following two
> examples.
> 1. No records in the table, so I want to have the index restart at Number
> 1.
> 2. I have records in the table (i.e. records 1 to 9) and I want the next
> index to start at 10. In this example, my index is currently starting at
> 23
> becuase I've added records to the table and deleted some.
>
You mean "identity columns" not "indexes" (although your identity columns
are probably indexed).
DBCC CHECKIDENT (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms176057.aspx
David|||Excellent. That accomplished what I was looking for. And, yes, I was
referring to Identity Columns, which are indexed.
Thank You
"David Browne" wrote:
>
> <DIV>"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in
> message news:BFDAB950-91CD-4248-B042-6008C35A05ED@.microsoft.com...</DIV>>I
> just migrated a Microsoft Access 2002 database to SQL Server 2005. So far
> > everything is working great. While updating my Access.mdb file to an
> > Access.ADP file I've been testing adding, editing and deleting records to
> > various tables so that I can ensure my Stored Procedures are working
> > properly.
> >
> > Now, I have deleted much of my sample data and want all of my indexes to
> > restart with the lowest number, typically being Number 1. In Access, I
> > would
> > empty the table, then compact/repair the database and the indexes would be
> > reset. How do I achieve this with SQL Server 2005 for the following two
> > examples.
> >
> > 1. No records in the table, so I want to have the index restart at Number
> > 1.
> > 2. I have records in the table (i.e. records 1 to 9) and I want the next
> > index to start at 10. In this example, my index is currently starting at
> > 23
> > becuase I've added records to the table and deleted some.
> >
> You mean "identity columns" not "indexes" (although your identity columns
> are probably indexed).
> DBCC CHECKIDENT (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms176057.aspx
> David
>

Reseting Indexes

I just migrated a Microsoft Access 2002 database to SQL Server 2005. So far
everything is working great. While updating my Access.mdb file to an
Access.ADP file I've been testing adding, editing and deleting records to
various tables so that I can ensure my Stored Procedures are working
properly.
Now, I have deleted much of my sample data and want all of my indexes to
restart with the lowest number, typically being Number 1. In Access, I would
empty the table, then compact/repair the database and the indexes would be
reset. How do I achieve this with SQL Server 2005 for the following two
examples.
1. No records in the table, so I want to have the index restart at Number 1.
2. I have records in the table (i.e. records 1 to 9) and I want the next
index to start at 10. In this example, my index is currently starting at 23
becuase I've added records to the table and deleted some.
Thank You.<DIV>"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in
message news:BFDAB950-91CD-4248-B042-6008C35A05ED@.microsoft.com...</DIV>>I
just migrated a Microsoft Access 2002 database to SQL Server 2005. So far
> everything is working great. While updating my Access.mdb file to an
> Access.ADP file I've been testing adding, editing and deleting records to
> various tables so that I can ensure my Stored Procedures are working
> properly.
> Now, I have deleted much of my sample data and want all of my indexes to
> restart with the lowest number, typically being Number 1. In Access, I
> would
> empty the table, then compact/repair the database and the indexes would be
> reset. How do I achieve this with SQL Server 2005 for the following two
> examples.
> 1. No records in the table, so I want to have the index restart at Number
> 1.
> 2. I have records in the table (i.e. records 1 to 9) and I want the next
> index to start at 10. In this example, my index is currently starting at
> 23
> becuase I've added records to the table and deleted some.
>
You mean "identity columns" not "indexes" (although your identity columns
are probably indexed).
DBCC CHECKIDENT (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms176057.aspx
David|||Excellent. That accomplished what I was looking for. And, yes, I was
referring to Identity Columns, which are indexed.
Thank You
"David Browne" wrote:

>
> <DIV>"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in
> message news:BFDAB950-91CD-4248-B042-6008C35A05ED@.microsoft.com...</DIV>>I
> just migrated a Microsoft Access 2002 database to SQL Server 2005. So far
> You mean "identity columns" not "indexes" (although your identity columns
> are probably indexed).
> DBCC CHECKIDENT (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms176057.aspx
> David
>

Reseting Identity Seed

hi all..
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
Ansari
Check out DBCC CHECKIDENT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that...
> one method is to truncate table... but if table is used as parent in foreign
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>

Reseting Identity Seed

hi all..
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
AnsariCheck out DBCC CHECKIDENT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.
gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that..
.
> one method is to truncate table... but if table is used as parent in forei
gn
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>

Reseting Identity Seed

O.k. here's my deal. I have a table who's items get assigned an ID based on
the identity seed. The identity seed is incremented by 1, and the seed
length is 4 . When my program is run, some of these items get moved into
another table for future use and the others are deleted. The items that get
moved are used again the next time the program is run(which is only once a
day). The one items get deleted is based on whether or not they match off t
o
another item read in by the program. Ex. If the data in my table is 1 2 3
4
5 and the program reads in 1 2 4 5, then the 3 is moved to another table and
the 1 2 4 5 get deleted b/c they were matched off. The identity seed is the
n
reset using DBCC CHECKIDENT which works fine, but I need to be able to have
the value it is reset to tied in to something like the date so that it is
unique each time it is reset. This way the next time my program reads in a
3, I need to make sure it does get the same identity seed as the previous 3
that was saved from the first time. I hope you can understand all that and
give me some insight as to how/if this can be done. Thanks.Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"CD" wrote:

> O.k. here's my deal. I have a table who's items get assigned an ID based
on
> the identity seed. The identity seed is incremented by 1, and the seed
> length is 4 . When my program is run, some of these items get moved into
> another table for future use and the others are deleted. The items that g
et
> moved are used again the next time the program is run(which is only once a
> day). The one items get deleted is based on whether or not they match off
to
> another item read in by the program. Ex. If the data in my table is 1 2
3 4
> 5 and the program reads in 1 2 4 5, then the 3 is moved to another table a
nd
> the 1 2 4 5 get deleted b/c they were matched off. The identity seed is t
hen
> reset using DBCC CHECKIDENT which works fine, but I need to be able to hav
e
> the value it is reset to tied in to something like the date so that it is
> unique each time it is reset. This way the next time my program reads in
a
> 3, I need to make sure it does get the same identity seed as the previous
3
> that was saved from the first time. I hope you can understand all that an
d
> give me some insight as to how/if this can be done. Thanks.|||Here's how I read your *requirement* as opposed to what you are
actually asking for. It seems like you have a table and you need to
determine which is the 1st, 2nd, 3rd or Nth row inserted to that table
on any particular day. So add a DATETIME column to the table:
CREATE TABLE YourTable (creation_date DATETIME NOT NULL UNIQUE DEFAULT
CURRENT_TIMESTAMP, ...)
The derive the sequence number like this:
SELECT T1.creation_date, COUNT(*) AS seq
FROM YourTable AS T1
JOIN YourTable AS T2
ON T2.creation_date >= '20050225'
AND T1.creation_date < '20050226'
AND T1.creation_date >= T2.creation_date
GROUP BY T1.creation_date
ORDER BY T1.creation_date
Resetting the seed and relying on IDENTITY to do the same thing is a
really bad idea. IDENTITY sequences can have gaps.
If I've completely misunderstood then the standard advice applies:
Please post DDL, sample data, required results to maximize your chance
of getting a good answer. See:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--sql

Reseting Identity Seed

hi all..
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
AnsariCheck out DBCC CHECKIDENT.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that...
> one method is to truncate table... but if table is used as parent in foreign
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>

Reseting Identity Column using SQL

Hi, Can anyone write me a script, how can I reset identity on column? ( I want to start records from 1 again)

Thanks, radco

Take a look at the following pointer:http://msdn2.microsoft.com/en-US/library/ms176057.aspx

Reseting automatic increments on primary keys

Hello
I've a SQL database that has several tables and relathionships between them.
Most of my tables have
the primary key set to automatic increments using identity on table properti
es.
It all did well in testing fases...
My problem is that when going to production how can I reset those identities
values on several primary keys to have my keys strat on 1 instead of the la
st number used in testing...
my procedure, that didn't worked, was first erasing all tables, second conve
rt all primary keys with identity set to no identity, third save table, fort
h put primary key back to identity increments, fifth save table again
The first record inserted, instead of having primary key with 1 brought prim
ary key with 80 or so...
how can I reset those values?!?!
best regards
Jorge RibeiroHi Jorge,
You can reset the current value of the identity column with:
DBCC CHECKIDENT('<table name>', RESEED, 1)
However, you shouldn't attach any meaning to the value of the identity
column for a number of reasons, so whether it starts at 1 or 80 shouldn't
make any difference.
Jacco Schalkwijk
SQL Server MVP
"Jorge Ribeiro" <jorge.ribeiro@.irsocial.mj.pt> wrote in message
news:99FB747F-DAED-4264-886C-FF5D28FDF426@.microsoft.com...

quote:

---

> Hello
> I've a SQL database that has several tables and relathionships between

---

them. Most of my tables have

quote:

---

> the primary key set to automatic increments using identity on table

---

properties.

quote:

---

> It all did well in testing fases...
> My problem is that when going to production how can I reset those

---

identities values on several primary keys to have my keys strat on 1 instead
of the last number used in testing...

quote:

---

> my procedure, that didn't worked, was first erasing all tables, second

---

convert all primary keys with identity set to no identity, third save table,
forth put primary key back to identity increments, fifth save table again

quote:

---

> The first record inserted, instead of having primary key with 1 brought

---

primary key with 80 or so...

quote:

---

> how can I reset those values?!?!
> best regards
> Jorge Ribeiro

---

|||Jacco is correct. You can use the CHECKIDENT function to reseed the
identity. However this will not change any of the current values. To do
that you will have to move the data to a different table, reseed the
identity and then import the data back in.
Rand
This posting is provided "as is" with no warranties and confers no rights.

reseting 'allow new subscriptions to be created by attaching..."

1st question: can this value be changed from 'false' to 'true' after a
publication is up and running?
2nd question: can this be accomplished without re-initializing the
subscribers?
3rd question: if #1 & #2 are true what steps must be done to do this?
Note: I have already tried the sp_changeMergePublication sp with no
success:
exec sp_changeMergePublication
@.publication = 'rep_test_hub',
@.property = allow_subscription_copy,
@.value = true
Thanks.
1) I am able to do it with a pull subscription.
2) Yes
3) I ran the same command to allow this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"astro" <astro@.bcmn.com> wrote in message
news:N38Bf.42034$7S.25014@.tornado.rdc-kc.rr.com...
> 1st question: can this value be changed from 'false' to 'true' after a
> publication is up and running?
> 2nd question: can this be accomplished without re-initializing the
> subscribers?
> 3rd question: if #1 & #2 are true what steps must be done to do this?
> Note: I have already tried the sp_changeMergePublication sp with no
> success:
> exec sp_changeMergePublication
> @.publication = 'rep_test_hub',
> @.property = allow_subscription_copy,
> @.value = true
>
> Thanks.
>
|||hummm...time to turn trace on and check the logs i guess...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%230rE$5IIGHA.1124@.TK2MSFTNGP10.phx.gbl...
> 1) I am able to do it with a pull subscription.
> 2) Yes
> 3) I ran the same command to allow this.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "astro" <astro@.bcmn.com> wrote in message
> news:N38Bf.42034$7S.25014@.tornado.rdc-kc.rr.com...
>
|||BTW - you know that after you have enabled this setting, you have to pull
your subscription to another database using an anonymous pull, and then do
the copy subscription database from this subscriber database, right?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"astro" <astro@.bcmn.com> wrote in message
news:OlqBf.42145$7S.2470@.tornado.rdc-kc.rr.com...
> hummm...time to turn trace on and check the logs i guess...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%230rE$5IIGHA.1124@.TK2MSFTNGP10.phx.gbl...
>