IDENTIY COLUMN Property behaviour

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » IDENTIY COLUMN Property behaviour

13 posts, Page 1 of 2

1 2 »»

IDENTIY COLUMN Property behaviour

Rate Topic

Display Mode

Topic Options

Author

Message

SQL*

Posted Thursday, June 21, 2012 12:18 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 415, Visits: 1,458

Hi All,

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

How SQL Server is generating these sequences?

Thanks,

Smile

Post #1319094

anthony.green

Posted Thursday, June 21, 2012 1:50 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

baabhu

Posted Thursday, June 21, 2012 1:56 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:58 AM
Points: 1,112, Visits: 970

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

BrainDonor

Posted Thursday, June 21, 2012 2:00 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:05 AM
Points: 1,400, Visits: 6,896

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

Post #1319140

SQL*

Posted Thursday, June 21, 2012 3:11 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 415, Visits: 1,458

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?

Smile

Post #1319165

GilaMonster

Posted Thursday, June 21, 2012 3:18 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020

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

Ravi SQL

Posted Thursday, June 21, 2012 12:01 PM

SSC Eights!

Group: General Forum Members
Last Login: Saturday, May 18, 2013 7:42 AM
Points: 975, Visits: 517

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

GilaMonster

Posted Thursday, June 21, 2012 12:14 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020

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

ScottPletcher

Posted Thursday, June 21, 2012 1:15 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,324, Visits: 1,778

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)
One man with courage makes a majority. Andrew Jackson

Post #1319590

Ellen-477471

Posted Tuesday, October 02, 2012 9:34 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 15, 2013 4:35 PM
Points: 113, Visits: 290

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 »

13 posts, Page 1 of 2

1 2 »»

Permissions