Multiple indexes on the same column

Is it possible to create one clustered index and one nonclustered index on the same column of a table?

For example, if we have a table Employees (EmpID, EmpName), then will SQL Server execute the following commands successfully?

Create clustered index indx1 on Employees (EmpID)
Go

Create nonclustered index indx2 on Employees (EmpID)
Go

yes, it is possible but it is not clear why you would want to do that. Here is the output of sp_help on the table empoyee and you will notice that there are two indexes one clustered and other non-clustered on the same key.
Name Owner Type Created_datetime

-- -- - --

Employees dbo user table 2005-10-28 09:30:42.733

Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation

-- -- -- -- -- -- -- -- -- --

EmpID int no 4 10 0 yes (n/a) (n/a) NULL

EmpName varchar no 100 yes no yes SQL_Latin1_General_CP1_CI_AS

Identity Seed Increment Not For Replication

-- -

No identity column defined. NULL NULL NULL

RowGuidCol

--

No rowguidcol column defined.

Data_located_on_filegroup

--

PRIMARY

index_name index_description index_keys

-- -

indx1 clustered located on PRIMARY EmpID

indx2 nonclustered located on PRIMARY EmpID

No constraints are defined on object 'employees', or you do not have permissions.

No foreign keys reference table 'employees', or you do not have permissions on referencing tables.

No views with schema binding reference table 'employees'.

|||Well, I was asked this question in an interview :)
Even I am not aware about any practical aspect of the question.

Thank you for your response, anyway.|||Thinking more about it, I think it can be useful under following scenarios
(1) you have range queries on empid that retrieves large number of columns in the table so it may make sense to have clustered index on empid.
AND
(2) you have range query on empid where you need only a subset of columns. you can make those subset of columns as included columns such that your query is covered by the index. This will eliminate access to the data page.

thanks

Multiple files in FileGroup on RAID

We are putting out DBs on a RAID array and have the data and indexes in
their own file groups. My question is where can I find information about any
performance gain in I/O based on the number of files I have in the
FileGroup.
The array is made up of 14 drives with 1 hot spare (RAID 5). How many
files?
Thx"cw3" <cw@.3mc.com> wrote in message
news:%23yenkyNnDHA.3688@.TK2MSFTNGP11.phx.gbl...
> We are putting out DBs on a RAID array and have the data and indexes in
> their own file groups. My question is where can I find information about
any
> performance gain in I/O based on the number of files I have in the
> FileGroup.
> The array is made up of 14 drives with 1 hot spare (RAID 5). How many
> files?
>
Spreading out the data to multiple files in a filegroup on top of a single
RAID 5 array is redundant. Each file will be spread out over all 14 drives
by the RAID striping to begin with. It's useless do do it again with
multiple files per filegroup.
In your setup you needn't even bother putting data and indexes into seperate
filegroups.
If you had your disks RAIDed into 7 seperate 2 drive RAID 1 mirrors, then
you might spread a filegroup over multiple volumes for performance and
capacity.
But a better configuration might be to bundle the mirrored sets into 4-drive
stripe and mirror sets.
David|||While there can be performance gains with having multiple files per file
group there is no chart etc. that exists to show what your after. The gains
are going to be greatest with lots of drive arrays and lots of processors
when you have multiple files. Chances are you will not max out the
capabilities of the way you have it now with that hardware setup.
--
Andrew J. Kelly
SQL Server MVP
"cw3" <cw@.3mc.com> wrote in message
news:%23yenkyNnDHA.3688@.TK2MSFTNGP11.phx.gbl...
> We are putting out DBs on a RAID array and have the data and indexes in
> their own file groups. My question is where can I find information about
any
> performance gain in I/O based on the number of files I have in the
> FileGroup.
> The array is made up of 14 drives with 1 hot spare (RAID 5). How many
> files?
> Thx
>

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.

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.

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?

1. I'm sure you are in the wrong place, try the SQL Server forums.

2. A Key implies unique data in the column(s) (Unique Key / Primary Key) where as an index doesn't necessarily have to have unique data in the column(s).

3. An index and a key that use the same definitions (same columns etc.) would be redundant, cost space (and possibly performance) and not desired. The optimizer can use either.

4. The first column of an index is always the most important and determines whether the index will be used or not. If you have no better indexes of the first column then it will be used.

BTW - Don't over do it with indexes!

Please ask such question in the SQL Server Forum, this forum is only for Team Edition for Database Professionals.

Alle