Prev: Help querying for a specific date
Next: Estimated number of rows
From: shapper on 6 Aug 2010 05:35
Hello,

When should I use clustered or not clustered for primary keys?

And in a table that performs only Many to Many relationship (please,
see ResourcesConstratains) should I have another primary key instead
of using the composition of the other two?

This is my T-SQL code:

create table dbo.Constraints
(
Id int identity not null,
[Type] nvarchar(100) not null,
constraint Constraints_PK primary key clustered(Id)
)

create table dbo.Resources
(
Id int identity not null,
Content varbinary(max) filestream constraint Resources_Content_DF
default(0x),
[Description] nvarchar(800) null,
[Key] uniqueidentifier not null rowguidcol constraint Resources_Key_U
unique,
Locked bit not null constraint Resources_Locked_DF default(0),
[Name] nvarchar(100) not null
constraint Resources_PK primary key clustered(Id)
)

create table dbo.ResourcesConstraints
(
ResourceId int not null,
ConstraintId int not null,
[Value] nvarchar(100) not null,
constraint ResourcesConstraints_PK primary key clustered(ResourceId,
ConstraintId)
)

alter table dbo.ResourcesConstraints
add constraint ResourcesConstraints_Resources_FK foreign
key(ResourceId) references dbo.Resources(Id) on delete cascade on
update cascade,
constraint ResourcesConstraints_Constraints_FK foreign
key(ConstraintId) references dbo.Constraints(Id) on delete no action
on update cascade;

Thanks,
Miguel
From: --CELKO-- on 6 Aug 2010 12:50
>> When should I use clustered or NOT clustered for primary keys? <<

How do you use the data? What clustering will give you the best
performance? For example, if you do a lot of reports by countries,
then use a clustered index on the country code column.

>> And in a table that performs only Many to Many relationship (please, see ResourcesConstratains) should I have another primary key instead of using the composition of the other two? <<

Does the relationship have its own key? A marriage is a relationship
between a Husband and a wife, but it has a marriage license number of
its own. The marriage also has a location and date, a presiding
authority, etc.
A set of constraint codes applied to a resource have no such
identifier and need only the compound key.
In your case, using those non-relatioanl IDENTITY properties for mock
pointer chains instead of a relational model is your real problem.

>> This is my T-SQL code: <<

Ever think about using ISO-11179 data element names and real keys
instead of the count of the physical insertion attempts? You should
never use vague names like "type" (of what? blood type?) And do you
really have a type encoded with up to 100 characters? Why do you use
reserved words for column names?

I would guess that your Constraints (of what?) is a look up table that
should have been declared like this:

CREATE TABLE Resource_Constraints
(constraint_code INTEGER NOT NULL,
constraint_description VARCHAR(100) NOT NULL);

From: Erland Sommarskog on 6 Aug 2010 17:31
shapper (mdmoura(a)gmail.com) writes:
> When should I use clustered or not clustered for primary keys?

That is by no means no easy question to answer.

First, there are at least two different criteria when you select
your clustered index:

o Good support for range queries.
o Minimize fragmentation on insert.

And these criterias are often in conflict. The latter criteria
essentially means: cluster on an monotonically ID column. But this
is not a column you are likely to run range queries over. It is
worth adding that the second point becomes more and more important
the higher the insertion rate is. So for a Customers table it's
not a big deal, but for an Orders table, it is likely to be.

What I can say is that unless you really know what you are doing, you
should have a clustered index on your table, because clustered
indexes are prime citizens in SQL Server, but heaps are not.

And if no other colunm stand out as the obvious column to cluster on,
you may as well cluster on the primary key.

> And in a table that performs only Many to Many relationship (please,
> see ResourcesConstratains) should I have another primary key instead
> of using the composition of the other two?

In my strong opinion, no!

Well, there are occasions there are reasons to add a surrogate key,
for instance because you are going to derive further from this table,
but there should always be UNIQUE/PK constraint to the composition.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Tony Rogerson on 7 Aug 2010 05:04
> In your case, using those non-relatioanl IDENTITY properties for mock
> pointer chains instead of a relational model is your real problem.

We've been here before and you've not answered why you are saying the
IDENTITY property is non-relational.

The IDENTITY property returns an atomic value that is persistent and checks
all the boxes for the latest ratified ISO SQL standard.

YOUR real problem here --CELKO-- is that you are not willing to accept the
industry definition of what a surrogate key is.

> CREATE TABLE Resource_Constraints
> (constraint_code INTEGER NOT NULL,
> constraint_description VARCHAR(100) NOT NULL);
>

That is not a table at all, it has no candidate key thus breaks the
relational model.

--ROGGIE--


From: gareth erskine-jones on 9 Aug 2010 21:17
On Fri, 6 Aug 2010 02:35:35 -0700 (PDT), shapper <mdmoura(a)gmail.com>
wrote:

>Hello,
>
>When should I use clustered or not clustered for primary keys?

Clustered / nonclustered is a performance issue. If you have many
queries which join to a table using the primary key, then you'll
probably get better performance if you have a clustered primary key.

Sometimes this isn't true - I'm currently working on an app which has
an column called "Id" on every table which is the PK and contains an
arbitary integer. This is a design flaw - many of these tables have
other columns which would have been suitable choices for PK and which
are reference much more often in joins than the int columns. Moving
the clusted index on these tables from the Id columns to the other key
columns produces an improvement in performance without a change in
fuctionality.

>And in a table that performs only Many to Many relationship (please,
>see ResourcesConstratains) should I have another primary key instead
>of using the composition of the other two?

The composite key is the obvious one - what do you gain from adding
another column?

GSEJ
 | 
Pages: 1
Prev: Help querying for a specific date
Next: Estimated number of rows