Multiple Columns Index/Key (does it free me from creating a single column indexes?)

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):

1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.

Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3?

2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index?

can anyone explain the main diference between Keys and Indices?

thanks,

Ran Kizi

1. Yes, the multi-column index will also function as an index for the first column. In many situations, it will also be an quasi-efficient index for the second, and perhaps other columns. I would get some response measurements before assuming that indexes were required on the lesser columns.

2. A 'unique key' is in fact, a 'Unique CONSTRAINT.

A Primary Key can be created for one or more columns, requires indexing to be efficient, and an index is automatically generated when the Primary Key is created. A Primary Key requires a value, and that the value is unique.

There is no 'Unique Key', but there is a Unique CONSTRAINT. (Perhaps in your question, you were referring to a Primary Key, and if so, the above applies.) A Unique CONSTRAINT can be created for one or more columns, and automatically creates a matching index. A Unique CONSTRAINT does not require a value, but if a value is provided, it must be unique.

A Foreign Key 'should have' an index to be efficient -but the index is NOT automatically generated when the Foreign Key is created. It is recommended that a matching index be created when the Foreign Key is created. A Foreign Key does NOT require a value, BUT if a value is provided, that value must also exist in the table with the Primary Key relationship.

|||

1. Indexes on multiple columns can also be used as index on first column. and in my experience it can not be used as index on second and following columns. you would need to create seprate indexes on those column if your queries will only use second or third column without using first column in queries.

There is a workaround even if you dont have to use first column in your query you can still use COLUMN1 = COLUMN1 only to make optimizer use index as in best of my knowledge if column1 is not present in your query index will be used for second or third column.

2. when you create UNIQUE constraint on multiple columns SQL Server will ultimately create compound unique index on those colunms to make sure uniquenes. for selectivity of index above will apply.

Primary Key does create index automaticaly (if index type is not mentioned and table is heap then will create CLUSTERED INDEX). FORIEGN KEY does not create index automatically and it is highly recomemded to create index on foriegn key column. The one unique reason is when deleting record in primary key table sql server perform a look up in all tables having foriegn key on that table and if no index presented then it will perform clustered index scan or table scan that will ultimately slow down DELETE operation for primary key table.