Showing posts with label specification. Show all posts
Showing posts with label specification. Show all posts

Wednesday, March 21, 2012

Identity Specification limit

what happens when a column marked as Identity Specification reaches the limit? for example, I have some code tables using tinyints as keys, the actual number of entries will be 20 or so but there is some volatility, so eventually the 255 limit will be reached, what happens then?

the same thing applies to ints or bigints used as keys, eventually the database must run out of numbers

information will be appreciated

David Wilson.

Hi,

When the maximum has been reached, an error will be generated. Here is an example:

--CREATE TABLE IDENTITYTEST
--(
-- ID TINYINT IDENTITY(1, 1),
-- TEXTVALUE VARCHAR(50)
--)

DECLARE @.COUNTER INT
SET @.COUNTER = 0

WHILE @.COUNTER < 260
BEGIN
INSERT INTO IDENTITYTEST(TEXTVALUE) VALUES ('VALUE ' + CAST(@.COUNTER AS VARCHAR(3)))
SET @.COUNTER = @.COUNTER + 1
END

/*
RESULT:
Msg 8115, Level 16, State 1, Line 12
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.
*/

Reference: http://msdn2.microsoft.com/en-us/library/ms186775.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

right, I did about the same thing (not quite as elegant :-)) - with the same result

the question is - How do you fix it? and how do you code something into a daily checkup routine or the like to find it and fix it before it happens?

Identity specification

Hi,

How do I determine what the next "id" will be in a collumn where the Identity Specification is turned on with Identity Increment?

I have two tables, the one contains product information and the other is a photo album. When I create a new product, then I automatically create an associated photo album. It all works fine, but I have found some problems after using this setup for a while to manage products.

For example, say I have created 10 products (id's 1 to 10) , and then I decide to remove the last 5 products from the list. The next time I add a new product, it will be product number 6, but the id will be 11 (seeing that the id's will always be unique). How do I determine what the next Identity value will be seeing that the actual last id might not be the last one that was allocated?

Regards
Jan

You can't know in advance what the next id field will be, but you can easily find out what the id was generated and use it in your proc. SCOPE_IDENTITY () returns the last id that was generated within your scopehttp://msdn2.microsoft.com/en-us/library/ms190315.aspx and would be used as follows

SET NOCOUNT ON
SET XACT_ABORT ON // guarantees that an error rolls back the transaction

declare @.lastId as int

begin tran
insert into table1...
select @.lastId = SCOPE_IDENTITY()

insert into table2 values (@.lastId,...)
end tran



|||

Hi David

Thanks for your response. I have read everything I could lay my hands on, and the long and short of my story is that I will have to rethink my whole implementation.

Thanks in any case - I have learned something that I am sure will be helpfull in future.

Regards

Jan

Monday, March 19, 2012

Identity property

Hello friends,

I am using sql server 2005. In some tables to create the column Autoincrement I had set the 'Idetity Specification' property to 'Yes'. I want to know that how can we do it through sql scripts i.e. by writing query.

Please let me know

Thanks & Regards
Girish Nehte

CREATE TABLE your_table( id_numint IDENTITY(1,1), fnamevarchar (20), minitchar(1), lnamevarchar(30))
PS: IDENTITY(seed, increment)|||

Thanks Addie.

Actually I have already build table and its identity field for id column is already set to "Yes". Now what I want to do is create a script which when executed will first set identity field to "No" and then again to "Yes", i.e. I want to alter that table.

How it can be done?

Thanks & Regards
Girish Nehte

|||T-SQL's ALTER TABLE statement doesn't support dropping the IDENTITY property in SQL Server 2000 or 7.0. Your only option for deleting an IDENTITY column is to create a new table structure without the IDENTITY column, then copy the data into this structure.

Just curious - why would you like to do that?

|||

I suspect what you want to do is to turn off the auto increment on the identity column so you can insert your own values. To do that, use

SETIDENTITY_INSERT tablenameOFF

then populate the table and issue

SETIDENTITY_INSERT tablenameON

|||

Actually in my project I want to create a script after running that script all the data from the data will be deleted and columns with identity "YES" will be reset to 0. Thats why and I think that it can be done by setting and resetting the identity field.

|||

Try:

1. using the truncate statement instead of delete (ex: TRUNCATE TABLE theNameOfYourTable)
2. EXEC ('DBCC CHECKIDENT(theNameOfYourTable,RESEED,0)')