Identity on Uniqueidentifier

Question

Post reply

Identity on Uniqueidentifier

tomy200501

SSC Rookie

Points: 33
More actions
April 1, 2015 at 12:56 pm

#295632

Hello All,
I am trying to set "Is Identity property" To Yes on a column that has uniqueidentifier as a type and has a primary key constraint on it. Is this possible? If yes, could anyone please help with pointers.
Thank you,

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

No, it's not possible.

From BOL.

The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 2

You have to use NEWID() to generate new values for a UNIQUEIDENTIFIER column. You might also want to look at NEWSEQUENTIALID() (in the same documentation).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Alan Burstein SSC Guru Points: 61152 More actions · Answer 3

If you wanted to get a column to act like an identity column but to be a uniqueidentifier instead of an int you could do this:

CREATE TABLE dbo.yourtable

(

pk_col AS newid(),

xxx varchar(10) not null

);

GO

INSERT dbo.yourtable (xxx) VALUES ('www'),('rrr');

SELECT *

FROM dbo.yourtable;

The problem here is that you can't use the column as a Primary key or a Unique key or as part of a clustered index. 🙁

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

tomy200501 (4/1/2015)
Hello All,
I am trying to set "Is Identity property" To Yes on a column that has uniqueidentifier as a type and has a primary key constraint on it. Is this possible? If yes, could anyone please help with pointers.
Thank you,

To be totally honest, using a UNIQUEIDENTIFIER as a Primary Key opens a world of hurt on you, especially if the PK is also the clustered index. I'd avoid it, if possible.

Perhaps we can help. Why do you think you need GUIDs (which are no longer guaranteed to be unique across multiple machines, btw).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 5

Jeff Moden (4/1/2015)
tomy200501 (4/1/2015)
Hello All,
I am trying to set "Is Identity property" To Yes on a column that has uniqueidentifier as a type and has a primary key constraint on it. Is this possible? If yes, could anyone please help with pointers.
Thank you,
To be totally honest, using a UNIQUEIDENTIFIER as a Primary Key opens a world of hurt on you, especially if the PK is also the clustered index. I'd avoid it, if possible.
Perhaps we can help. Why do you think you need GUIDs (which are no longer guaranteed to be unique across multiple machines, btw).

Further on Jeff's post, 99% fragmentation is not uncommon when using GUID as a clustered primary key, worse yet is that reorganizing efforts tend to be futile. As a result the server has to revert to random access reads which are very expensive.

😎