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 fo开发者_运维问答r ID column value. So my question is how to get auto generated INT ID value for table insert?

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.

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