Designing Primary Key and Clustered Index and Performance

I have several tables where the clustered index (the
physical way the data is stored) is different from the
Primary Key (which is just a unique number). It seems to
me that this will help me the most, as the clustered
index supports my SELECT statements, and the Primary Key
column will support my UPDATE and DELETE statements. I
am very new to SQL Server. My question is this. Is
designing the tables this way ok to do, or is this not
how it should be done?
Second part of my question is, If what I am doing is
fine, then does it make a difference (performance wise)
if I have the Primary Key column the first column table
or about the seventh column in?
Thanks so much for your help.Depends on who you ask. Zealots will say that you should never, ever use an
arbitrary unique integer as a row identifier. I disagree. However,
whenever possible, even if you are using a unique identifier you should
attempt to set a 'natural' primary key based on uniqueness in your data, and
use this for your primary key rather than the row identifier. You can still
use the ID to make life simpler (e.g. passing back lists of rows to client
applications for singleton selection), but the primary key will help
maintain and validate the table's data.
As for location within the column list, it makes no difference where
anything is. Don't rely on the ordering of your column list. Always
specify explicit column lists in the order you want them for selects and
inserts.
"Nancy" <anonymous@.discussions.microsoft.com> wrote in message
news:052b01c3aee5$09ea4e00$a301280a@.phx.gbl...
> I have several tables where the clustered index (the
> physical way the data is stored) is different from the
> Primary Key (which is just a unique number). It seems to
> me that this will help me the most, as the clustered
> index supports my SELECT statements, and the Primary Key
> column will support my UPDATE and DELETE statements. I
> am very new to SQL Server. My question is this. Is
> designing the tables this way ok to do, or is this not
> how it should be done?
> Second part of my question is, If what I am doing is
> fine, then does it make a difference (performance wise)
> if I have the Primary Key column the first column table
> or about the seventh column in?
> Thanks so much for your help.|||Placing the clustered index on a column other than the primary key is often
wise.
You only get ONE clustered index per table so use it wisely (Which is what I
believe you are doing).
If you are new to SQL Server, welcome to the community.
I recommend starting with these 3 books:
1. Inside SQL Server 2000 by Kalen Delaney
2. Professional SQL Server 2000 Programming By Robert Vieira
3. Transact-SQL Programming by Kline
Cheers
Greg Jackson
Portland, OR

Design problem

Hello. I'm using SQL2000, and I have a design problem.
I know that every time I make a primary key, sql server makes it by default
a clustered index. Since I have a large composite key in the table I don't
know if it's smart to leave it as a clustered index. This is the table:

CREATE TABLE [InputOutputItems] (
[FromStorage] [smallint] NOT NULL ,
[ToStorage] [smallint] NOT NULL ,
[DocumentTypeID] [int] NOT NULL ,
[DocumentNumber] [int] NOT NULL ,
[StorageDocYear] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[SerialNumber] [varchar] (50) COLLATE Croatian_CI_AI NOT NULL ,
[PartnerID] [int] NULL ,
[Barcode] [varchar] (50) COLLATE Croatian_CI_AI NULL ,
[DaysOfExpiration] [int] NULL ,
[DateOfValidation] [datetime] NULL ,
[Row] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[Column] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[Level] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[UnitDimensionID] [int] NULL ,
[UnitPack] [decimal](18, 4) NULL ,
[TotalWeight] [decimal](18, 4) NULL ,
[PackageMachineID] [int] NOT NULL ,
[PackageEmployeeID] [int] NULL ,
[UserIDCreated] [int] NULL ,
[DateCreated] [datetime] NULL ,
[UserIDChanged] [int] NULL ,
[DateChanged] [datetime] NULL ,
[PaleteNumber] [int] NULL ,
[LinkedDocument] [int] NULL ,
CONSTRAINT [PK_InputOutputItems] PRIMARY KEY CLUSTERED
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear],
[ProductID],
[SerialNumber],
[PackageMachineID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_InputOutputItems_InputOutput] FOREIGN KEY
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
) REFERENCES [InputOutput] (
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
)
) ON [PRIMARY]

InputOutPutItems is actually an Item table for documents, here is the header
table for documents:

CREATE TABLE [InputOutput] (
[FromStorage] [smallint] NOT NULL ,
[ToStorage] [smallint] NOT NULL ,
[DocumentTypeID] [int] NOT NULL ,
[DocumentNumber] [int] NOT NULL ,
[StorageDocYear] [int] NOT NULL ,
[AppUserID] [smallint] NULL ,
[ManufactureIndent] [int] NULL ,
[DeliveryDate] [datetime] NULL ,
[OrgUnitID] [int] NULL ,
[TypeOfTransferID] [int] NULL ,
[Note] [varchar] (500) COLLATE Croatian_CI_AI NULL ,
[Status] [int] NULL ,
[UserIDCreated] [int] NULL ,
[DateCreated] [datetime] NULL ,
[UserIDChanged] [int] NULL ,
[DateChanged] [datetime] NULL ,
[TypeOfIndent] [char] (2) COLLATE Croatian_CI_AI NULL ,
[TMOrgUnitID] [smallint] NULL ,
CONSTRAINT [PK_InputOutput] PRIMARY KEY CLUSTERED
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_InputOutput_DocumentTypes] FOREIGN KEY
(
[DocumentTypeID]
) REFERENCES [DocumentTypes] (
[DocumentTypeID]
),
CONSTRAINT [FK_InputOutput_OrgUnits] FOREIGN KEY
(
[FromStorage]
) REFERENCES [OrgUnits] (
[OrgUnitID]
),
CONSTRAINT [FK_InputOutput_OrgUnits1] FOREIGN KEY
(
[ToStorage]
) REFERENCES [OrgUnits] (
[OrgUnitID]
)
) ON [PRIMARY]

These tabels are used for documents in a Warehouse Managament System with
barcode scanners.
Because of the way that the system works the primary keys cannot be any
smaller. I never use the whole
primary key in a where clause, I just use parts of it.
Should I make somethig else a clustered index, or leave the clustered index
as a primary key?

Thanks in advance.
Drazen Grabovac.Grabi (drazen@.git.hr) writes:
> Hello. I'm using SQL2000, and I have a design problem. I know that every
> time I make a primary key, sql server makes it by default a clustered
> index. Since I have a large composite key in the table I don't know if
> it's smart to leave it as a clustered index. This is the table:

This question already has answers in microsoft.public.sqlserver.programming.
Please do not post the same question independly to several newsgroups,
as this can result in people wasting on a time on a problem that has
already been addressed.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Design problem

Hello. I'm using SQL2000, and I have a design problem.
I know that every time I make a primary key, sql server makes it by default
a clustered index. Since I have a large composite key in the table I don't
know if it's smart to leave it as a clustered index. This is the table:
CREATE TABLE [InputOutputItems] (
[FromStorage] [smallint] NOT NULL ,
[ToStorage] [smallint] NOT NULL ,
[DocumentTypeID] [int] NOT NULL ,
[DocumentNumber] [int] NOT NULL ,
[StorageDocYear] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[SerialNumber] [varchar] (50) COLLATE Croatian_CI_AI NOT NULL ,
[PartnerID] [int] NULL ,
[Barcode] [varchar] (50) COLLATE Croatian_CI_AI NULL ,
[DaysOfExpiration] [int] NULL ,
[DateOfValidation] [datetime] NULL ,
[Row] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[Column] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[Level] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[UnitDimensionID] [int] NULL ,
[UnitPack] [decimal](18, 4) NULL ,
[TotalWeight] [decimal](18, 4) NULL ,
[PackageMachineID] [int] NOT NULL ,
[PackageEmployeeID] [int] NULL ,
[UserIDCreated] [int] NULL ,
[DateCreated] [datetime] NULL ,
[UserIDChanged] [int] NULL ,
[DateChanged] [datetime] NULL ,
[PaleteNumber] [int] NULL ,
[LinkedDocument] [int] NULL ,
CONSTRAINT [PK_InputOutputItems] PRIMARY KEY CLUSTERED
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear],
[ProductID],
[SerialNumber],
[PackageMachineID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_InputOutputItems_InputOutput] FOREIGN KEY
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
) REFERENCES [InputOutput] (
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
)
) ON [PRIMARY]
The tabel is used for document items in a WMS system. I never use the whole
primary key in a where clause, I just use parts of it.
Should I make somethig else a clustered index, or leave the clustered index
as a primary key?
Drazen Grabovac.If you use EM (Enterprise Manager) and set the PK first then by default a
clustred index will be created on the PK column(s). You can first create the
clustred index as desired and then set the PK.
When defining the clustred index take into concidaration how will you being
querying data. If you are going to have range queries i.e. by dates then
concider a clustred index on the relevant datetime column.
For a single row access sql will use your PK and for range queries the
clustered index.
In addition you may create other noneclustred indexs as required to server
data retreival.
"Grabi" <drazen@.git.hr> wrote in message news:dvjpor$5nq$1@.ss405.t-com.hr...
> Hello. I'm using SQL2000, and I have a design problem.
> I know that every time I make a primary key, sql server makes it by
> default a clustered index. Since I have a large composite key in the table
> I don't know if it's smart to leave it as a clustered index. This is the
> table:
> CREATE TABLE [InputOutputItems] (
> [FromStorage] [smallint] NOT NULL ,
> [ToStorage] [smallint] NOT NULL ,
> [DocumentTypeID] [int] NOT NULL ,
> [DocumentNumber] [int] NOT NULL ,
> [StorageDocYear] [int] NOT NULL ,
> [ProductID] [int] NOT NULL ,
> [SerialNumber] [varchar] (50) COLLATE Croatian_CI_AI NOT NULL ,
> [PartnerID] [int] NULL ,
> [Barcode] [varchar] (50) COLLATE Croatian_CI_AI NULL ,
> [DaysOfExpiration] [int] NULL ,
> [DateOfValidation] [datetime] NULL ,
> [Row] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
> [Column] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
> [Level] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
> [UnitDimensionID] [int] NULL ,
> [UnitPack] [decimal](18, 4) NULL ,
> [TotalWeight] [decimal](18, 4) NULL ,
> [PackageMachineID] [int] NOT NULL ,
> [PackageEmployeeID] [int] NULL ,
> [UserIDCreated] [int] NULL ,
> [DateCreated] [datetime] NULL ,
> [UserIDChanged] [int] NULL ,
> [DateChanged] [datetime] NULL ,
> [PaleteNumber] [int] NULL ,
> [LinkedDocument] [int] NULL ,
> CONSTRAINT [PK_InputOutputItems] PRIMARY KEY CLUSTERED
> (
> [FromStorage],
> [ToStorage],
> [DocumentTypeID],
> [DocumentNumber],
> [StorageDocYear],
> [ProductID],
> [SerialNumber],
> [PackageMachineID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [FK_InputOutputItems_InputOutput] FOREIGN KEY
> (
> [FromStorage],
> [ToStorage],
> [DocumentTypeID],
> [DocumentNumber],
> [StorageDocYear]
> ) REFERENCES [InputOutput] (
> [FromStorage],
> [ToStorage],
> [DocumentTypeID],
> [DocumentNumber],
> [StorageDocYear]
> )
> ) ON [PRIMARY]
> The tabel is used for document items in a WMS system. I never use the
> whole primary key in a where clause, I just use parts of it.
> Should I make somethig else a clustered index, or leave the clustered
> index as a primary key?
>
> Drazen Grabovac.
>|||Your design looks wrong.
Call you explain what a "type_id" means? An attribute can be a type or
an identifer, but NEVER both (see ISO-11179).
Can you give an example of varyign length 50 character barcode?
What is a user_id history being kept in this table? Where is the User
table that should have that kind of information?
What is "InputOutputItems" -- other than very vague.
Some of the other names also suggest LOTS of problems, which might be
why you have such a large key.|||Hello.
DocumentTypeID means ID, but it's named that way because it's a FOREIGN KEY
to this table:
CREATE TABLE [DocumentTypes] (
[DocumentTypeID] [int] NOT NULL ,
[AppUserID] [smallint] NULL ,
[Name] [varchar] (50) COLLATE Croatian_CI_AI NULL ,
[Description] [varchar] (500) COLLATE Croatian_CI_AI NULL ,
[OweInput] [int] NULL ,
[LookupInput] [int] NULL ,
[ActiveUntil] [datetime] NULL ,
[Label] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[ChargeTypeID] [int] NULL ,
[UserIDCreated] [int] NULL ,
[DateCreated] [datetime] NULL ,
[UserIDChanged] [int] NULL ,
[DateChanged] [datetime] NULL ,
CONSTRAINT [PK_DocumentTypes] PRIMARY KEY CLUSTERED
(
[DocumentTypeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
The remark about the Barcode is ok. It's a varyign length 50 character
barcode because
we made a lot of changes to the structure of the barcode.
This is the user table:
CREATE TABLE [Users] (
[UserID] [MyKey] NOT NULL ,
[EmployeeID] [MyKey] NULL ,
[PartnerID] [MyKey] NULL ,
[OrgUnitID] [smallint] NULL ,
[Type1] [MyKey] NULL ,
[Type2] [MyKey] NULL ,
[UserName] [MyString] NOT NULL ,
[Password] [MyString] NOT NULL ,
[LogTime] [smalldatetime] NULL ,
[LogCount] [MyKey] NULL ,
[LastLog] [datetime] NULL ,
[Active] [bit] NULL ,
[Activation] [datetime] NULL ,
[ActivationNote] [MyLongString] NULL ,
[LanguageID] [MyKey] NULL ,
[Sms] [MyKey] NULL ,
[SmsSent] [MyKey] NULL ,
[Note] [MyText] NULL ,
[PiccoLinkPassword] [varchar] (10) COLLATE Croatian_CI_AI NULL ,
[PActive] [char] (1) COLLATE Croatian_CI_AI NULL ,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_Users_Employees] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [Employees] (
[EmployeeID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_Users_Languages] FOREIGN KEY
(
[LanguageID]
) REFERENCES [Languages] (
[LanguageID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_Users_OrgUnits] FOREIGN KEY
(
[OrgUnitID]
) REFERENCES [OrgUnits] (
[OrgUnitID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_Users_Partners] FOREIGN KEY
(
[PartnerID]
) REFERENCES [Partners] (
[PartnerID]
) NOT FOR REPLICATION
) ON [PRIMARY]
InputOutPutItems is actually an Item table for documents, here is the header
table for documents:
CREATE TABLE [InputOutput] (
[FromStorage] [smallint] NOT NULL ,
[ToStorage] [smallint] NOT NULL ,
[DocumentTypeID] [int] NOT NULL ,
[DocumentNumber] [int] NOT NULL ,
[StorageDocYear] [int] NOT NULL ,
[AppUserID] [smallint] NULL ,
[ManufactureIndent] [int] NULL ,
[DeliveryDate] [datetime] NULL ,
[OrgUnitID] [int] NULL ,
[TypeOfTransferID] [int] NULL ,
[Note] [varchar] (500) COLLATE Croatian_CI_AI NULL ,
[Status] [int] NULL ,
[UserIDCreated] [int] NULL ,
[DateCreated] [datetime] NULL ,
[UserIDChanged] [int] NULL ,
[DateChanged] [datetime] NULL ,
[TypeOfIndent] [char] (2) COLLATE Croatian_CI_AI NULL ,
[TMOrgUnitID] [smallint] NULL ,
CONSTRAINT [PK_InputOutput] PRIMARY KEY CLUSTERED
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_InputOutput_DocumentTypes] FOREIGN KEY
(
[DocumentTypeID]
) REFERENCES [DocumentTypes] (
[DocumentTypeID]
),
CONSTRAINT [FK_InputOutput_OrgUnits] FOREIGN KEY
(
[FromStorage]
) REFERENCES [OrgUnits] (
[OrgUnitID]
),
CONSTRAINT [FK_InputOutput_OrgUnits1] FOREIGN KEY
(
[ToStorage]
) REFERENCES [OrgUnits] (
[OrgUnitID]
)
) ON [PRIMARY]
So now that you have an idea of how the document tabels look in my database,
could you give me an advice.
Should I put the primary key as a clustered index or not? InputOutPutItems
is pretty large and has millions or records,
and query's can sometimes be slow.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1142780014.593696.168280@.g10g2000cwb.googlegroups.com...
> Your design looks wrong.
> Call you explain what a "type_id" means? An attribute can be a type or
> an identifer, but NEVER both (see ISO-11179).
> Can you give an example of varyign length 50 character barcode?
> What is a user_id history being kept in this table? Where is the User
> table that should have that kind of information?
> What is "InputOutputItems" -- other than very vague.
> Some of the other names also suggest LOTS of problems, which might be
> why you have such a large key.
>
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1142780014.593696.168280@.g10g2000cwb.googlegroups.com...
> Your design looks wrong.
> Call you explain what a "type_id" means? An attribute can be a type or
> an identifer, but NEVER both (see ISO-11179).
> Can you give an example of varyign length 50 character barcode?
> What is a user_id history being kept in this table? Where is the User
> table that should have that kind of information?
> What is "InputOutputItems" -- other than very vague.
> Some of the other names also suggest LOTS of problems, which might be
> why you have such a large key.
>|||Grabi (drazen@.git.hr) writes:
> Hello. I'm using SQL2000, and I have a design problem. I know that every
> time I make a primary key, sql server makes it by default a clustered
> index. Since I have a large composite key in the table I don't know if
> it's smart to leave it as a clustered index. This is the table:
Whether it is a good idea or not that the PK by default is clustered
is disputed. Some people think it's bad. Personally, I think it's good
for the simple reason that many tables have no other index than the PK,
and in the verymost cases it's a good idea that all tables have a
clustered index.
Whatever, in very many cases, the clustered index shold not be on the
primary key. Typically you put the clustered index on a column that
appears commonly in range queries. For instance in an Orders table,
you would probably cluster on OrderDate or CustomerID depending on what
you most often want to view queries by.
What you should cluster on in InputOutputItems, I can't tell, because
I don't know the business. If most queries will by ProductID, maybe that
is the guy. But I don't know.
One thing to keep in mind, is that in non-clustered index, the clustered
index key is used as row locator. This means that a wide clustered key, also
increases the size of the non-clustered indexes.
As for the general design I note that all your non-key columns are nullable,
but is really that way? Could there really exist a row where DateCreated
and UserIDCreated are NULL? Or for that matter the BarCode? Strictly
specifying which columns that may be nullable and which may be not, will
help you to get better quality of the data.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx