multi-field primary key index 1

[perfectchaoss]

Hi,
I have a table in my database that uses more than one field as a Unique ID. The table is growing very large and i would like to index it. However SQL requires a single field as the unique identifier to create an index. Is thier anyway to index the table so without adding a brand new identifier? the fields that make up the identifier are:
Date, ItemNumber and Store

thanks in advance,
JF

 

[JohnDTampaBay]

SQL has no problem creating multi-column indexes, unique or otherwise, clustered or not. Here's an example from BOL of a simple composite index:

CREATE INDEX emp_order_ind
    ON order_emp (orderID, employeeID)

You should be able to run something like this:

CREATE INDEX IndexName
    ON TableToBeIndexed ([Date], ItemNumber, Store)

Of course, TableToBeIndexed is really the table you want to be indexed and IndexName is what ever name you would like the index to have.

Does this help? Is there a specific error message you receive when you try to create an index? Good luck! --Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[perfectchaoss]

Angel
Thanks for the advice I did not know that, I guess i assumed it did not allow indexing because i get this message when i try to use the wizard:

The selected table has no
unique single column index on a column that does not allow NULLS.

Database: DailySales

Table: [dbo].[Daily1]

I will try your way instead
thanks again,
JF

 

[Databaseguy]

Is there a performance hit for using clustered indexes as a primary key ?

Tyrone Lumley
augerinn@gte.net

 

[SQLSister]

I would say yes. You have to store more data in the tables and index when you use it as a foreign key in another table. Generally they take up more bits of space than a single column key would take. Also the fastest joins are integer joins which you can't take advantage of when you have a clustered key. So I personally never use them.

If I have multiple items that make a record unique, I will put a unique constraint on them (or check uniqueness through a trigger if one or more of the fields could be null) but use an autogenerated integer as my key and my foreign key in other tables. If you design your user interface properly, the user will think that the cluster is the unique key and never even see the integer joins that are really there.

One place I can think of where the clustered key would be an advantage would be if by using it you could avoid joins altogether in most of your queries. But I can't think of any time when that has been true in any database I've ever worked with.