Clustered and RowGuiCol - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Clustered and RowGuiCol

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2009-08-01 : 11:01:17

Hello,

I have the following tables:

create table Tags
(
ID uniqueidentifier not null constraint PK_Tag primary key clustered,
TypeID uniqueidentifier not null,
[Name] nvarchar(40) not null
) -- Tags

create table Posts
(
ID uniqueidentifier not null rowguidcol constraint PK_Post primary key clustered,
Body nvarchar(max) not null,
Created datetime not null,
Excerpt nvarchar(max) not null,
[File] varbinary(max) filestream default(0x),
Published bit not null default 0,
Title nvarchar(200) not null,
Updated datetime not null
) -- Posts

create table PostsTags
(
PostID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_PostsTags
primary key clustered (PostID, TagID),
constraint FK_PostsTags_Posts
foreign key(PostID)
references Posts(ID)
on delete cascade,
constraint FK_PostsTags_Tags
foreign key(TagID)
references Tags(ID)
on delete cascade
) -- PostsTags

When should I use clustered on a primary key? Does not improve the joins?
And besides using FileStream when should I use RowGuidCol?
I think it is needed for merging correct?

I though a Guid was always unique (probability of a match very low)

Thanks,
Miguel

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-08-01 : 21:21:34

When should I use clustered on a primary key? - When it goes in increasing order

Does not improve the joins? - Sometimes, sometimes not. It depends on the query. You have to measure it using the analyser. GUIDs can dramatically slow your insert on a CI. If you need guids there is a NewSequentialID option but the global uniqueness is compromised.

I think it is needed for merging correct? - don't understand your question

I though a Guid was always unique (probability of a match very low) - correct

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2009-08-02 : 17:33:48

quote:
Originally posted by LoztInSpace
I think it is needed for merging correct? - don't understand your question

When mergin two tables data for example.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources