CODEWITHSUNDEEP
sql-serversql-server-2008
LIU
LIU

Reputation: 315

Best type for ID column in SQL Server 2008

I am a beginner of SQL Server 2008. there a colunm in table like StudentID. StudentID will be the pk of table, it only could be integer and it will be a huge number.

My question is what type for column StudentID in SQL Server 2008 is best?

bigint? numeric(18,0)? or others?

thanks a lot.

Upvotes: 19

Views: 54695

Answers (3)

Lakhan SINGH
Lakhan SINGH

Reputation: 67

Yes an int or any variation of int is a good choice ,but there is one downside to it which i can think of . When rows get deleted the ID field shows numbers missing like (1,3,4,5). Guid could be second choice for ID field

Upvotes: 1

Catto
Catto

Reputation: 6409

Int is the Best type for Id column in a table in SQL Server

If the table eventually has too many records we can change to BigInt

We can also create the Id as the Identity and/or PK of the table. 

CREATE TABLE [dbo].[TableName](
    [TableNameId] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

Example of Identity & Primary Key

CREATE TABLE [dbo].[TableName3](
    [TableName3Id] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_TableName3] PRIMARY KEY CLUSTERED 
 (
    [TableName3Id] ASC
 )
)

Hope this helps your question helped me.

Upvotes: 1

marc_s
marc_s

Reputation: 754518

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 ... (so in my opinion, this is more than enough for the vast majority of cases!)

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 ....

INT uses 4 bytes of storage, while BIGINT uses 8 bytes. Especially if you deal with a large number of rows, and a number of non-clustered indexes, you want to keep this as small as possible - yet another reason why I typically pick INT as my "ID" type (unless I have a very strong indication that INT won't be enough...)

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

Upvotes: 43

Related Questions