Table modified

Question

Post reply

Table modified

raja_saminathan

SSCommitted

Points: 1607
More actions
April 24, 2008 at 3:59 am

#186950

Hi,
Is there any way to identitfy the thats that are fresh and not yet rows added in the table .
My problem is i have a table with identity column and sometimes i want to reuse the same table with new set of datas,so i am deleting the contents of the table and reseeding the identity to 0 berfore inserting new set of values every time. If the table is a fresh table (not yet inserted any records and deleted ) since we are reseeding ,it starts from identity value 0 and if it already modified tables it starts from `1.
So is there any way to find the table modified property or kindly suggest some ideas to sort out this problem.
Expecting your valuable replies
Thanks and Regards,
Rajesh

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Rajesh

Please will you post your CREATE TABLE statement and the code you are using to reseed the identity value.

John

raja_saminathan SSCommitted Points: 1607 More actions · Answer 2

Hi John,

/****** Object: Table [dbo].[DiscountTable] Script Date: 04/24/2008 16:14:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[DiscountTable](

[DiscountPID] [smallint] IDENTITY(1,1) NOT NULL,

[Code] [varchar](50) NULL,

CONSTRAINT [PK_DiscountTable] PRIMARY KEY CLUSTERED

(

[DiscountPID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

I have created a table with name DiscountTable containing 2 columns

DiscountPID SmallInt - Primary key Identity(1,1)

Code - Varchar(50)

Insert into DiscountTable

values('r')

Now i want to delete the inserted row and insert new set of values

so since i want to start identity value for discountPID from 1,I am deleting and reseeding as like below

Delete from Discounttable

dbcc checkident (Discount, reseed, 0)

This method works fine while deleting the values from tables containing datas exist.

But if the table is new (Fresh table in database we have not yet created records in it) ,when i use the same

Delete from Discounttable

dbcc checkident (Discount, reseed, 0)

Insert into DiscountTable

values('r')

The identityValue starts from 0,for fresh tables

The identity Value starts from 1 for tables which already contain datas and deleted and then inserted.

Now how can set the identity value to start from 1, independent of whether it is fresh table or the table in which we have deleted the records from it and insert again.

Hope now you got what happening in it

Rajesh

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Rajesh

Can you not just use dbcc checkident (Discount, reseed, 1)?

John

raja_saminathan SSCommitted Points: 1607 More actions · Answer 4

The problem is when i reseed to 1 ,

the identity value starts from 2

Rajesh

Tony Webster SSChasing Mays Points: 610 More actions · Answer 5

Isn't the value you specify for the RESEED notionally the current existing value - so you just need to reseed to zero? (i.e. it increments before it uses it, rather than after - "++id" rather than "id++" 😉

raja_saminathan SSCommitted Points: 1607 More actions · Answer 6

Hi,

Actually i wrote a query which uses insert datas into a table,

But Before inserting the data every time iam deleting the previous records in the table.

Then I am Reseeding Identity to zero only.

My Problem is if the table has not yet inserted any datas and iam going to insert the data for the first time , since i am generally deleting the records and reseeding before the insertion of data into the table ,the identity value starts from zero instead of starting from 1.

This happens only the first time when it is a fresh table.But once inseerted and deleted if we reseed to 0 ,it starts from 1.

so iam asking you is there any way to find out whether the table is fresh,(no operation done on the table earlier)

Rajesh

Tony Webster SSChasing Mays Points: 610 More actions · Answer 7

Oh I see - that's pretty ugly - frist reaction was it was a bug, but it seems to be documented behaviour from BOL:

The current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.

I guess they might just not have anticipated that you might clear it out and reseed it.

What about using @@rowcount after the delete? For example, this seems to work okay:

CREATE TABLE test(id int IDENTITY(1, 1))

DELETE FROM test -- Just create and clear

IF @@rowcount > 0

DBCC CHECKIDENT (test, RESEED, 0)

ELSE

DBCC CHECKIDENT (test, RESEED, 1)

INSERT INTO test DEFAULT VALUES

SELECT 'Fresh table, no inserts, reseed', id

FROM test

DROP TABLE test

--------------------

CREATE TABLE test(id int IDENTITY(1, 1))

INSERT INTO test DEFAULT VALUES -- Test record

DELETE FROM test

IF @@rowcount > 0

DBCC CHECKIDENT (test, RESEED, 0)

ELSE

DBCC CHECKIDENT (test, RESEED, 1)

INSERT INTO test DEFAULT VALUES

SELECT 'Fresh table, test insert, reseed', id

FROM test

DROP TABLE test

PhilPacha Hall of Fame Points: 3645 More actions · Answer 8

If there are no foreign keys on the table, simply TRUNCATE TABLE [YourTableName]

raja_saminathan SSCommitted Points: 1607 More actions · Answer 9

Hai Tony,

Thanks for your Valuable suggestion,

This works fine,

But my problem is if the table contains no record ,in that case the id starts from 2 if we try to use this query.If the table contains some records it works fine.

I tried with some ohter options like using Ident_Current,Scope_identity,Min(Identitycol) but still iam struggling ,

Thanks for your valuable information to share with me,meanwhile i am also trying other methods ,i will post you also if i find any answer

Regards,

Rajesh

Tony Webster SSChasing Mays Points: 610 More actions · Answer 10

Rajesh -

I don't think I understand - my script showed the two cases -

- Create a table from scratch, *don't* insert a new record, execute a DELETE for the whole table, insert a record, look at the identify field value on the inserted record

- Create a table from scratch, *do* insert a new record, execute a DELETE for the whole table, insert a record, look at the identify field value on the inserted record

The other suggestion of using TRUNCATE TABLE also works, and is arguably a bit cleaner - so I am unclear what exactly remains to be struggled with?

Tony

Rajesh Patil SSC Veteran Points: 219 More actions · Answer 11

Can u put the entire business scenario in here. That led to this requirement. Probably if there is a workaround for the same, a different solution can satisfy it ?

raja_saminathan SSCommitted Points: 1607 More actions · Answer 12

Hi Tony,

Let me explain the problem with your cases and additional case.

the two cases -

-1) Create a table from scratch, *don't* insert a new record, execute a DELETE for the whole table, insert a record, look at the identify field value on the inserted record

- 2)Create a table from scratch, *do* insert a new record, execute a DELETE for the whole table, insert a record, look at the identify field value on the inserted record

- 3)Create a table from scratch, *do* insert a new record,

Use only Delete statement without identity reseed,what happens is table contains no records.

Now execute a DELETE for the whole table, insert a record, look at the identify field value on the inserted record it starts from 2.

To sort out i tried all other options but i could find solution yet

Hope now you have u'stood my problem

Thanks and Regards,

Rajesh

The other suggestion of using TRUNCATE TABLE also works, and is arguably a bit cleaner - so I am unclear what exactly remains to be struggled with?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

PhilPacha (4/25/2008)
If there are no foreign keys on the table, simply TRUNCATE TABLE [YourTableName]

Hey guys! Didn't you see PhilPacha's post? That's the way to do it... truncating a table also reseeds the table back to its original value with no fuss or muss.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

Sorry, Rajesh... didn't see the last line of your post... same idea.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)