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