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

Reset The Auto Generated Numbers

How can I reset an auto generated number back to start from 1 again.
I made a test table, now to implement the table I need to start the counter back from 1.

The field that I am trying to reset is CustID
It is a primary key for a table and it is set as an Indentity with Increment 1.

Thanks
Taimur"Truncate table" will reset the identity counter to the original seed value.|||or you could use the following (where TestTable is the name of the table):

DBCC CHECKIDENT (jobs, TestTable, 1)|||Sorry... just to correct the previous posting:

DBCC CHECKIDENT (TestTable, RESEED, 1)|||Thanks Iwaker. This worked perfect. :)

Originally posted by lwaker
Sorry... just to correct the previous posting:

DBCC CHECKIDENT (TestTable, RESEED, 1)sql

reset report parameters back to defaults

I am running SQL 2005 Reporting Services and designing reports via Visual
Studio.
Is there an easy way to reset/clear the report parameters back to the
defaults while viewing the report in IE.
Right now I have create a hyperlink which will re-direct to the URL of the
report which thus sets the paramaters back to their defaults, but it seems
that there must be a better way to do this.Hello,
Reporting Services did not provide the method to reset the parameters back
to the default.
Your solution is the best way I think to achieve your requirement.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql

Reset Primary ID back to 1

Reset the Identity Increment
------------------------

Reset the Identity Increment

Hello:
I have a table with a bigint type column (field) that has an identity seed
of 1 and an identity increment of 1. The column is the primary key for the
table.

After I backup and clean out the database (delete all of the data in the DB)
I need to have the column with the identiy seed/increment value reset to 1
automatically. (start counting at 1 again). How does one do that, because
as it is now, the DB keeps increasing the value of the column from where it
left off, regardless of the fact that I deleted all of the data in the
table.

The DB is MS SQL Server 2000.

Thanks and appreciate any help.The only way to do that is to use truncate table instead of delete.
You need additional rights to be able to execute truncate table statement.

Good Luck.

Irina.|||Yes, of course you can use truncate. It will delete and reseed the identity columns.
It is also more efficient way to save the resources.

But, if you also want to use delete, you can reset the identity column by running the following command:

DBCC CHECKIDENT('mytable', RESEED, 0) ;

Hope to help.

Reset Page count, use distinctcount() for page number, or SPROC cursor to reset page numbe

I have a report that will come back as 20 pages.
What the user wants is on the beginning of another unique ID, which causes a new page to be started, to have the page count restart to 1.
So, if I have 3 UniqueIDs for all 20 pages, and the first, IDAA, is 10 pages long, the second, IDBB, is 4 pages long, and the third, IDCC, is 6 pages long, then the page numbering would be as:
1-10 for IDAA
1-4 for IDBB
1-6 for IDCC
I've been told to set a cursor in a SP that determines what page I'm on and then passses this to the result set as a column. This is then set in a group to control page breaks. How could I do this? I'm not very familiar with SPROC cursors.
I know how to do this scenario in Crystal Reports, but am trying to figure it out here with either a page count formula, or some type of distinct count().
Thanks!

This is what I've tried so far, but it resets on every page.
So, each page is 1 even if the Unique ID spans 2-3 pages.
I only need it to reset on a new field grouping of the list object.

Here's the VB code for the report:

Shared offset As Integer

Public Function GetPN(reset As Boolean, pagenumber As Integer) As Integer
If reset
offset = pagenumber - 1
End If
Return pagenumber - offset
End Function

***************************

In the header, I have a textbox named "tag"

In the footer, I've got the following expression:
="Unique ID Page: " & Code.GetPN(Not(ReportItems!tag.Value Is Nothing),Globals!PageNumber)

|||

Blank

|||

My fault...I had the textbox "tag" in the page header and not in the list object itself.

It works!

Thanks!

Reset Identity Question

SQL Server 2000
Is there a way to reset an identity field of an empty field back to one
without createing a temp table and renaming?
TIA
Tim MorrisonDBCC CHECKIDENT (<tableNamehere>, RESEED, 1)
"Tim Morrison" wrote:

> SQL Server 2000
> Is there a way to reset an identity field of an empty field back to one
> without createing a temp table and renaming?
> TIA
> Tim Morrison
>
>|||On Wed, 9 Mar 2005 16:07:35 -0600, Tim Morrison wrote:

>SQL Server 2000
>Is there a way to reset an identity field of an empty field back to one
>without createing a temp table and renaming?
Hi Tim,
DBCC CHECKIDENT, with the RESEED option.
Another way would be to use TRUNCATE TABLE instead of DELETE FROM when
deleting the last row - TRUNCATE TABLE automatically resets the identity
seed.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||For some tables this is what i do, but I have several parent-child tables,
and it appears I cannot do a TRUNCATE when there are child tables, even if
they are empty.
Tim Morrison
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:qhvu21p88vbenjpop7e92h6pj0fubtrueq@.
4ax.com...
> On Wed, 9 Mar 2005 16:07:35 -0600, Tim Morrison wrote:
>
> Hi Tim,
> DBCC CHECKIDENT, with the RESEED option.
> Another way would be to use TRUNCATE TABLE instead of DELETE FROM when
> deleting the last row - TRUNCATE TABLE automatically resets the identity
> seed.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Cool... seems to work... EXCEPT the next record that is inserted has a value
of 2 instead of 1. Its no big deal, I can deal with 2.
Tim Morrison
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:5FF180CC-BFB3-40FE-B2AC-2FE0F5F5291D@.microsoft.com...
> DBCC CHECKIDENT (<tableNamehere>, RESEED, 1)
>
> "Tim Morrison" wrote:
>|||reseed to 0 instead if you want 1
Simon Worth
"Tim Morrison" <sales_nospam_@.kjmsoftware.com> wrote in message
news:eeK316PJFHA.2852@.TK2MSFTNGP09.phx.gbl...
> Cool... seems to work... EXCEPT the next record that is inserted has a
> value of 2 instead of 1. Its no big deal, I can deal with 2.
> Tim Morrison
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:5FF180CC-BFB3-40FE-B2AC-2FE0F5F5291D@.microsoft.com...
>

Reset Identity DataType

How can I reset an identity Int column back to start with 1 if I remove all rows from the table?

Thanks,TRUNCATE TABLE tablename