June 21, 2012 at 12:18 am
Hi All,
I have a table with IDENTITY(1,1), it will generate a sequence number.
How SQL Server is generating these sequences?
Thanks,
🙂
June 21, 2012 at 1:50 am
http://msdn.microsoft.com/en-us/library/ms186775.aspx
IDENTITY(1,1), means start at 1 (1,x) and add 1 (x,1) every time a new row is inserted
1
2
3
4
5
6
7
IDENTITY(100,200) means start at 100 (100,x) and add 200 (x,200) every time a new row is inserted
100
300
500
700
900
1100
1300
June 21, 2012 at 1:56 am
SQL* (6/21/2012)
Hi All,I have a table with IDENTITY(1,1), it will generate a sequence number.
How SQL Server is generating these sequences?
Thanks,
create table Intpk (intslno int identity (1,1), name varchar(100))
When records are inserted the identity column will start incrementing from 1. Generating the sequence is a SQLserver architecture part.
When records are deleted the sequence is not reseeded.
When the table is truncated the table is reseeded with values back to 1 again.
Still got doubts please refer back good old school book. SQLserver books online.
Thank you.
June 21, 2012 at 2:00 am
Don't assume that these identities will always be consecutive though - there may be gaps in the sequence when you look at the data.
An interesting article with examples of manipulating the identity column - http://www.simple-talk.com/sql/t-sql-programming/identity-columns/.
June 21, 2012 at 3:11 am
Thank you,
I am aware of the statements what you have posted,
but my doubt is how sql server knows the next identity?
Ex: i have a table with identity, i have inserted 10 records so the identity column will contain 1 to 10 value (I assume that the sql server will check the identity property (seed , increment) and last inserted identity value by looking at the table, based on increment it will add the next identity.)
Suppose we are inserting one more 11th record into the above table (so the identity column will be having the value 11) if this insert was rolled back then the table will not contain the 11th identity value. If we have inserted one more record into the table this time 12 will be inserted into the identity column.
How SQL Server knows that the next Identity is 12 instead of 11?
🙂
June 21, 2012 at 3:18 am
It's stored in the metadata of the table.
One other point, don't assume identity columns are unique. There's nothing in the identity property that requires uniqueness, if it has to be unique put a unique or primary key constraint in place
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 21, 2012 at 12:01 pm
Hi,
SQL stores the current identity value generated in last_value column of sys.identity_columns table with this value SQL knows which value to be generated next based the increment_value column.
--Ravi.
Regards,
Ravi.
June 21, 2012 at 12:14 pm
Sys.identity_columns isn't a table, it's a view of the internal metadata.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 21, 2012 at 1:15 pm
When the table is truncated the table is reseeded with values back to 1 again.
Technically, the table is set back so that the next row inserted gets the initial seed/starting value, which is usually 1 but could be something else.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 2, 2012 at 9:34 am
Is there a chart somewhere that shows the maximum number of identity values for the data type used when creating an identity field in a table?
I've searched for this in the BOL but have not had any luck.
We had someone design tables in a database to use the an identity column of datatype int for the orders and line item sequence numbers.
If the seed value is 40,000,000 for orders with an increment of 1, how many orders can be created before the field has reached maximum value?
Thank you.
October 2, 2012 at 9:38 am
Ellen-477471 (10/2/2012)
Is there a chart somewhere that shows the maximum number of identity values for the data type used when creating an identity field in a table?I've searched for this in the BOL but have not had any luck.
We had someone design tables in a database to use the an identity column of datatype int for the orders and line item sequence numbers.
If the seed value is 40,000,000 for orders with an increment of 1, how many orders can be created before the field has reached maximum value?
Thank you.
An int can hold a value up to 2,147,483,647, so the number of values left from 40M woud be:
2,107,483,647
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 2, 2012 at 9:55 am
Ellen-477471 (10/2/2012)
Is there a chart somewhere that shows the maximum number of identity values for the data type used when creating an identity field in a table?I've searched for this in the BOL but have not had any luck.
We had someone design tables in a database to use the an identity column of datatype int for the orders and line item sequence numbers.
If the seed value is 40,000,000 for orders with an increment of 1, how many orders can be created before the field has reached maximum value?
Thank you.
It depends on the datatype of the column. Min and max values here: http://msdn.microsoft.com/en-us/library/ms187745.aspx
If you need a truly huge number of identities in a table, and don't want to go with BigInt because of the storage amount, keep in mind that you can seed an Int Identity column to start at -2,147,483,648, increment by 1, and pretty much double the number of available IDs (as compared to starting at 1, which is the default). That allows for over 4-billion (US billion) rows with unique ID values in that table. Not quite enough for every person on the planet, but still pretty vast.
BigInt, which takes twice the storage space as Int, can go from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, which is enough for over 18-pentillion entries. About 2.6-billion rows per person on the planet.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 2, 2012 at 10:03 am
Thank you both. There was an article somewhere referring to the limitations for identity column values with respect to replication. That is what sent me looking.
The chart answers my concern. We can't use negative numbers but the int will suffice for order and master order numbers.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply