Grabi (drazen@git.hr) writes:
[quoted text, click to view]

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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

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.