InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 21:08:12 GMT20http://www.sqlservercentral.com/Forums/Topic1319094-391-1.aspxThank 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. Tue, 02 Oct 2012 10:03:49 GMTEllen-477471http://www.sqlservercentral.com/Forums/Topic1319094-391-1.aspx[quote][b]Ellen-477471 (10/2/2012)[/b][hr]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.[/quote]It depends on the datatype of the column. Min and max values here: [url]http://msdn.microsoft.com/en-us/library/ms187745.aspx[/url]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.Tue, 02 Oct 2012 09:55:55 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1319094-391-1.aspx[quote][b]Ellen-477471 (10/2/2012)[/b][hr]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.[/quote]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,647Tue, 02 Oct 2012 09:38:20 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1319094-391-1.aspxIs 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.Tue, 02 Oct 2012 09:34:57 GMTEllen-477471http://www.sqlservercentral.com/Forums/Topic1319094-391-1.aspx[quote]When the table is truncated the table is reseeded with values back to 1 again. [/quote]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.Thu, 21 Jun 2012 13:15:48 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1319094-391-1.aspxSys.identity_columns isn't a table, it's a view of the internal metadata.Thu, 21 Jun 2012 12:14:33 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1319094-391-1.aspxHi,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.Thu, 21 Jun 2012 12:01:12 GMTRavi SQLhttp://www.sqlservercentral.com/Forums/Topic1319094-391-1.aspxIt'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 placeThu, 21 Jun 2012 03:18:20 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1319094-391-1.aspxThank 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?Thu, 21 Jun 2012 03:11:28 GMTSQL*http://www.sqlservercentral.com/Forums/Topic1319094-391-1.aspxDon'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 - [url]http://www.simple-talk.com/sql/t-sql-programming/identity-columns/[/url].Thu, 21 Jun 2012 02:00:53 GMTBrainDonorhttp://www.sqlservercentral.com/Forums/Topic1319094-391-1.aspx[quote][b]SQL* (6/21/2012)[/b][hr]Hi All,I have a table with IDENTITY(1,1), it will generate a sequence number.How SQL Server is generating these sequences?Thanks,[/quote]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. Thu, 21 Jun 2012 01:56:32 GMTbaabhuhttp://www.sqlservercentral.com/Forums/Topic1319094-391-1.aspx[url]http://msdn.microsoft.com/en-us/library/ms186775.aspx[/url]IDENTITY(1,1), means start at 1 (1,x) and add 1 (x,1) every time a new row is inserted1234567IDENTITY(100,200) means start at 100 (100,x) and add 200 (x,200) every time a new row is inserted10030050070090011001300Thu, 21 Jun 2012 01:50:42 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1319094-391-1.aspxHi All,I have a table with IDENTITY(1,1), it will generate a sequence number.How SQL Server is generating these sequences?Thanks,Thu, 21 Jun 2012 00:18:39 GMTSQL*