CODEWITHSUNDEEP
databasesql-server-2005t-sql
user592704
user592704

Reputation: 3704

SQL Server 2005 - T-SQL - How to generate int ID?

I have faced a thing as NEWID(). I thought it should be useful as to insert INT key id-s values to a table, but it returns float or something which is not pretty good for ID column value. So my question is how to get auto generated INT ID value for table insert?

Upvotes: 1

Views: 3884

Answers (2)

marc_s
marc_s

Reputation: 754508

You define a column of type INT (or SMALLINT, TINYINT, BIGINT) with the IDENTITY attribute:

CREATE TABLE dbo.YourTable( ID INT IDENTITY(1,1) ......

With this setup, SQL Server will automatically generate consecutive ID's for your table when rows are being inserted into your table.

With a type INT, starting at 1, you get over 2 billion possible rows - that should be more than sufficient for the vast majority of cases. With BIGINT, you get roughly 922 quadrillion (922 with 15 zeros - 922'000 billions) - enough for you??

If you use an INT IDENTITY starting at 1, and you insert a row every second, you need 66.5 years before you hit the 2 billion limit ....

If you use a BIGINT IDENTITY starting at 1, and you insert one thousand rows every second, you need a mind-boggling 292 million years before you hit the 922 quadrillion limit ....

Read more about it (with all the options there are) in the MSDN Books Online.

Upvotes: 6

Wicked Coder
Wicked Coder

Reputation: 1118

CREATE TABLE Test
(
T_Id int IDENTITY (1, 1) NOT NULL,
LastName varchar(255),
FirstName varchar(255),
)

Here's a link to a tuorial if you are using SQL Server http://howtoideas.net/how-to-create-auto-increment-column-in-a-sql-server-table

Upvotes: 2

Related Questions