Clustered Key. (Making it unique).

Question

Clustered Key. (Making it unique).

ben.brugman

SSChampion

Points: 13369
More actions
February 15, 2013 at 7:26 am

#289468

Using:
(sp_SQLskills_SQL2008_helpindex: provided by Kimberly L. Tripp).
(sp_help)
drawing_eid (identity field)
index_keys: [client_eid], [drawing_eid]
columns_in_tree: [client_eid], [drawing_eid], UNIQUIFIER
Why is de UNIQUIFIER there?
(With drawing_eid as identity it is not needed).
Ben Brugman
The definition of the table:
CREATE TABLE [dbo].[SKP_Ben](
[drawing_eid] [int] IDENTITY(1,1) NOT NULL,
[preferred_client_id] [varchar](20) NULL,
[client_eid] [int] NULL,
[design_img] [image] NOT NULL,
[Some_More_Fields] [varchar] (300) NULL,
CONSTRAINT [PK_SKP_Ben] PRIMARY KEY NONCLUSTERED
(
[drawing_eid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE CLUSTERED INDEX [IX_SKP_Ben] ON [dbo].[SKP_Ben]
(
[client_eid] ASC,
[drawing_eid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SKP_Ben_1] ON [dbo].[SKP_Ben]
(
[preferred_client_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It's there because the clustered index wasn't defined as UNIQUE. SQL can't intuit or guess that the columns are unique or not, so unless the clustered index is created with UNIQUE, the assumption is that it's not unique and hence needs a uniquifier (and identity column does not guarantee uniqueness).

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ben.brugman SSChampion Points: 13369 More actions · Answer 2

GilaMonster (2/15/2013)
... ... UNIQUE ... ... (and identity column does not guarantee uniqueness).

Thanks, most or all other tables have the UNIQUE 'constraint', this table should have the UNIQUE constraint. Thanks for pointing this out,

ben brugman

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Even if you put a unique constraint on the identity column, it won't (afaik) be enough. If the clustered index is unique, the clustered index should be created with the UNIQUE keyword

ie CREATE UNIQUE CLUSTERED INDEX ...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ben.brugman SSChampion Points: 13369 More actions · Answer 4

GilaMonster (2/15/2013)
the clustered index should be created with the UNIQUE keyword

Thanks, and yes this was clear to me from your posts.

Ben

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

GilaMonster (2/15/2013)
identity column does not guarantee uniqueness.

is it so ? but it always generate new value incremented to previous one

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Lowell SSC Guru Points: 323518 More actions · Answer 6

Bhuvnesh (2/18/2013)
GilaMonster (2/15/2013)
identity column does not guarantee uniqueness.
is it so ? but it always generate new value incremented to previous one

True but its only half of the puzzle, you have the ability to set identity_insert on or off which allows you to insert anything, including duplicates. You can also reset the seed anytime as well. You need to combine it with a primary key or unique constraint to guarantee uniqueness.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Bhuvnesh (2/18/2013)
GilaMonster (2/15/2013)
identity column does not guarantee uniqueness.
is it so ? but it always generate new value incremented to previous one

Yes, it is so.

An identity will, by itself generate unique numbers, but it is not a constraint, it does not enforce uniqueness.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass