The identity column must be of data type int, bigi

Question

I have created a database project in Visual Studio 2012, with target platform set to 'Windows Azure SQL Database'. I added a table like:

CREATE TABLE [dbo].[Organization]
(
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED IDENTITY, 
    [Name] NVARCHAR(100) NOT NULL, 
    [CreationDate] DATETIME NOT NULL DEFAULT GetDate()
)

GO

CREATE CLUSTERED INDEX [IX_Organization_CreationDate] ON [dbo].[Organization] ([CreationDate])

But it keeps complaining about:

Error   1   SQL71518: The identity column '[dbo].[Organization].[Id]' must be of data type int, bigint, smallint, tinyint, decimal, or numeric with a scale of 0, and the column must not be nullable.  C:\Projects\Gastrology\MGP\trunk\Sources\Cfg.Mgp.Infrastructure.Database\Organization.sql   3   2   Cfg.Mgp.Infrastructure.Database

Anyone knows why I can't create a primary key of type guid? What Am I doing wrong?

Thanks!

Answer 1

Try something like this :-

CREATE TABLE [dbo].[Organization]
(
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED DEFAULT NEWID(), 
    [Name] NVARCHAR(100) NOT NULL, 
    [CreationDate] DATETIME NOT NULL DEFAULT GetDate()
)

Check out this link

From the source:-

The main disadvantage to using GUIDs as key values is that they are BIG. At 16 bytes a pop, they are one of the largest datatypes in SQL Server. Indexes built on GUIDs are going to be larger and slower than indexes built on IDENTITY columns, which are usually ints (4 bytes). ** > Not only that, but they're just plain hard to read. Unless you need a truly globally unique identifier, you're probably better off sticking with an IDENTITY.

**

Answer 2

The IDENTITY keyword is used to auto-increment a integer column that is usually used as a primary key. Because you are using a unique identifier, including this keyword makes no sense. (how do you increment a unique identifier?)

Just remove the "IDENTITY" keyword.

http://technet.microsoft.com/en-us/library/ms186775.aspx

Answer 3

IDENTITY cannot be used with GUID

Use NEWID instead.

CREATE TABLE [dbo].[Organization]
(
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED DEFAULT NEWID(), 
    [Name] NVARCHAR(100) NOT NULL, 
    [CreationDate] DATETIME NOT NULL DEFAULT GetDate()
)