Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

IDENTIY COLUMN Property behaviour Expand / Collapse
Author
Message
Posted Thursday, June 21, 2012 12:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 11, 2014 2:19 AM
Points: 415, Visits: 1,605
Hi All,

I have a table with IDENTITY(1,1), it will generate a sequence number.

How SQL Server is generating these sequences?

Thanks,


Post #1319094
Posted Thursday, June 21, 2012 1:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
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




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1319135
Posted Thursday, June 21, 2012 1:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:11 PM
Points: 1,302, Visits: 1,146
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.

Post #1319139
Posted Thursday, June 21, 2012 2:00 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 5:55 AM
Points: 1,483, Visits: 8,545
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/.


BrainDonor
Linkedin
Blog Site
Post #1319140
Posted Thursday, June 21, 2012 3:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 11, 2014 2:19 AM
Points: 415, Visits: 1,605
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?


Post #1319165
Posted Thursday, June 21, 2012 3:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1319169
Posted Thursday, June 21, 2012 12:01 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 4:06 AM
Points: 976, Visits: 550
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.
Post #1319525
Posted Thursday, June 21, 2012 12:14 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
Sys.identity_columns isn't a table, it's a view of the internal metadata.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1319533
Posted Thursday, June 21, 2012 1:15 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 2,330, Visits: 3,509
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1319590
Posted Tuesday, October 2, 2012 9:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 6:32 AM
Points: 136, Visits: 314
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.
Post #1367123
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse