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 [InputOutputItem s] (
[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 ,
[DaysOfExpiratio n] [int] NULL ,
[DateOfValidatio n] [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 ,
[PackageMachineI D] [int] NOT NULL ,
[PackageEmployee ID] [int] NULL ,
[UserIDCreated] [int] NULL ,
[DateCreated] [datetime] NULL ,
[UserIDChanged] [int] NULL ,
[DateChanged] [datetime] NULL ,
[PaleteNumber] [int] NULL ,
[LinkedDocument] [int] NULL ,
CONSTRAINT [PK_InputOutputI tems] PRIMARY KEY CLUSTERED
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear],
[ProductID],
[SerialNumber],
[PackageMachineI D]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_InputOutputI tems_InputOutpu t] FOREIGN KEY
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
) REFERENCES [InputOutput] (
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
)
) ON [PRIMARY]
InputOutPutItem s 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 ,
[ManufactureInde nt] [int] NULL ,
[DeliveryDate] [datetime] NULL ,
[OrgUnitID] [int] NULL ,
[TypeOfTransferI D] [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.
