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 Index files

1. Is there any benifit to creating a distinct index file for each column to be indexe
Index file 1 - Field
Index File 2 - Field
Index file 3 - Field
Index file 4 - Field
2. Is there a benifit to creating multiple groups of indexes ie
Index file 1 - Field A, Field B, Filed
Index File 2 - Field B, Field
Index file 3 - Field B, FieldHi Mark.
There are benefits / limitations in taking either approach.
Multi-column indexes may "cover" more queries or join predicates than single
column queries and provide better performance that way.
Distinct indexes provide compact index width for fast join or seek
operations where query predicates or filter arguments are on those columns.
SQL Server can also intersect indexes during optimization which recovers
some of the benefits that would have come from multi-column indexes.
If your application is relatively read only (data mart / warehouse) & not
bound by disk space limitations you might find that the more indexes you
have the better, as there are simply more options for the optimizer to suit
more queries.
If your application is more of an online transaction processinng
application, you probably want to limit which indexes you add as each index
needs updating as rows are inserted, updated & deleted.
The Index Tuning Wizard can be helpful in determining which indexes are the
best for the database & queries you're using.
HTH
Regards,
Greg Linwood
SQL Server MVP
"MarkFirth" <anonymous@.discussions.microsoft.com> wrote in message
news:406C0C47-5B5C-45EF-93C2-DD721278F61F@.microsoft.com...
> 1. Is there any benifit to creating a distinct index file for each column
to be indexed
> Index file 1 - Field A
> Index File 2 - Field B
> Index file 3 - Field B
> Index file 4 - Field H
> 2. Is there a benifit to creating multiple groups of indexes ie:
> Index file 1 - Field A, Field B, Filed C
> Index File 2 - Field B, Field C
> Index file 3 - Field B, Field H
>|||Mark
It depends on what are you going to query and how.
If you issue SELECT Field A, Field B, Filed C FROM
SomeTable ORDER BY Field A, Field B, Filed C or
WHERE clause
so having covering index on these columns will gain perfomance improving.
"MarkFirth" <anonymous@.discussions.microsoft.com> wrote in message
news:406C0C47-5B5C-45EF-93C2-DD721278F61F@.microsoft.com...
> 1. Is there any benifit to creating a distinct index file for each column
to be indexed
> Index file 1 - Field A
> Index File 2 - Field B
> Index file 3 - Field B
> Index file 4 - Field H
> 2. Is there a benifit to creating multiple groups of indexes ie:
> Index file 1 - Field A, Field B, Filed C
> Index File 2 - Field B, Field C
> Index file 3 - Field B, Field H
>sql

Multiple Databases/One SQL Server instance

We would like to put several production databases (approx. 8) on one
sql server instance.
Each database has 3 filegroups(primary, index, log), what is the best
way to set up these files on the server? We will have 3 or 4 drives.
Is it best to spread the log files across the different drives, or keep
the log files all on one drive etc?
Hi
What do you mean by 3 or 4 drives? If these are physically separate drives
then if you want resilience then you would probably be best of mirroring
them, therefore leaving you with two pairs of which log files can go on one
pair and data files can go on the other pair. If you want more resilience
then you would need a minimum of 3 discs for Raid 5 and 4 discs for Raid 10.
You may want to read more on raid such as at
http://msdn2.microsoft.com/en-us/library/ms190764.aspx and
http://www.acnc.com/04_01_10.html
As the type of I/O for log files is different to that of the data files you
are best keeping them separate. If you subsequently have I/O problems then
you will need to analyse what is causing this, and possibly move one or more
filegroups and their data files onto their own set of discs.
John
"Jayme" wrote:

> We would like to put several production databases (approx. 8) on one
> sql server instance.
> Each database has 3 filegroups(primary, index, log), what is the best
> way to set up these files on the server? We will have 3 or 4 drives.
> Is it best to spread the log files across the different drives, or keep
> the log files all on one drive etc?
>

Multiple Databases/One SQL Server instance

We would like to put several production databases (approx. 8) on one
sql server instance.
Each database has 3 filegroups(primary, index, log), what is the best
way to set up these files on the server? We will have 3 or 4 drives.
Is it best to spread the log files across the different drives, or keep
the log files all on one drive etc?Hi
What do you mean by 3 or 4 drives? If these are physically separate drives
then if you want resilience then you would probably be best of mirroring
them, therefore leaving you with two pairs of which log files can go on one
pair and data files can go on the other pair. If you want more resilience
then you would need a minimum of 3 discs for Raid 5 and 4 discs for Raid 10.
You may want to read more on raid such as at
http://msdn2.microsoft.com/en-us/library/ms190764.aspx and
http://www.acnc.com/04_01_10.html
As the type of I/O for log files is different to that of the data files you
are best keeping them separate. If you subsequently have I/O problems then
you will need to analyse what is causing this, and possibly move one or more
filegroups and their data files onto their own set of discs.
John
"Jayme" wrote:
> We would like to put several production databases (approx. 8) on one
> sql server instance.
> Each database has 3 filegroups(primary, index, log), what is the best
> way to set up these files on the server? We will have 3 or 4 drives.
> Is it best to spread the log files across the different drives, or keep
> the log files all on one drive etc?
>

Multiple Databases/One SQL Server instance

We would like to put several production databases (approx. 8) on one
sql server instance.
Each database has 3 filegroups(primary, index, log), what is the best
way to set up these files on the server? We will have 3 or 4 drives.
Is it best to spread the log files across the different drives, or keep
the log files all on one drive etc?Hi
What do you mean by 3 or 4 drives? If these are physically separate drives
then if you want resilience then you would probably be best of mirroring
them, therefore leaving you with two pairs of which log files can go on one
pair and data files can go on the other pair. If you want more resilience
then you would need a minimum of 3 discs for Raid 5 and 4 discs for Raid 10.
You may want to read more on raid such as at
http://msdn2.microsoft.com/en-us/library/ms190764.aspx and
http://www.acnc.com/04_01_10.html
As the type of I/O for log files is different to that of the data files you
are best keeping them separate. If you subsequently have I/O problems then
you will need to analyse what is causing this, and possibly move one or more
filegroups and their data files onto their own set of discs.
John
"Jayme" wrote:

> We would like to put several production databases (approx. 8) on one
> sql server instance.
> Each database has 3 filegroups(primary, index, log), what is the best
> way to set up these files on the server? We will have 3 or 4 drives.
> Is it best to spread the log files across the different drives, or keep
> the log files all on one drive etc?
>

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

Multiple columns full text search doesn't seems to work properly

Hi all !
I have a full text search index on a table with several fields
indexed.
I tried the following query :
SELECT MyIndexedTable.MyIndexedTableID
MyIndexedTable.Title,
KEY_TBL.RANK RANK_Total
FROM
MyIndexedTable
INNER JOIN
CONTAINSTABLE(MyIndexedTable,*,'"sport" AND "news"') AS KEY_TBL
ORDER BY KEY_TBL.RANK DESC
There are some records that are not returned by the query but they
have the words "sport" and "news" in their fields.
Any idea what could be happening?
Thanks in advance.
Xavi
This should work. Note that sports and news will have to be in the same
column to get a hit form this row.
"Xavi" <xaspas@.gmail.com> wrote in message
news:1177326871.889457.41300@.e65g2000hsc.googlegro ups.com...
> Hi all !
> I have a full text search index on a table with several fields
> indexed.
> I tried the following query :
> SELECT MyIndexedTable.MyIndexedTableID
> MyIndexedTable.Title,
> KEY_TBL.RANK RANK_Total
> FROM
> MyIndexedTable
> INNER JOIN
> CONTAINSTABLE(MyIndexedTable,*,'"sport" AND "news"') AS KEY_TBL
> ORDER BY KEY_TBL.RANK DESC
> There are some records that are not returned by the query but they
> have the words "sport" and "news" in their fields.
> Any idea what could be happening?
> Thanks in advance.
> Xavi
>
|||Exactly but I'd like to know how to make it work when words are
located in different fields.
I have read about creating an extra Text (or nText) field with all the
content in it and then create an index based on that field, I also
have seen something using unions and multiple querys on the index. I
wonder if there's any better approach and which one will give better
perfomance.
Thank you for your answer!
Xavi
On 23 abr, 14:31, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> This should work. Note that sports and news will have to be in the same
> column to get a hit form this row.
> "Xavi" <xas...@.gmail.com> wrote in message
> news:1177326871.889457.41300@.e65g2000hsc.googlegro ups.com...
>
>
|||The concatenated column offers the best performance.
"Xavi" <xaspas@.gmail.com> wrote in message
news:1177335970.294430.289990@.p77g2000hsh.googlegr oups.com...
> Exactly but I'd like to know how to make it work when words are
> located in different fields.
> I have read about creating an extra Text (or nText) field with all the
> content in it and then create an index based on that field, I also
> have seen something using unions and multiple querys on the index. I
> wonder if there's any better approach and which one will give better
> perfomance.
> Thank you for your answer!
> Xavi
> On 23 abr, 14:31, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>

Multiple Column Indexed Select?

There is a large table with a multiple-column index. What is the
proper way to search using these columns so the multiple-column index
is used?
Thanks.
Can you be a little more specific? You normally don't have to do anything
special for sql server to utilize an index as long as it is useful for the
query. Whether or not the index is used depends on the query, the index,
how many rows in the table, how selective the index / query is etc, etc. If
you post the DDL for the table and the query you are looking to issue maybe
we can make a suggestion.
Andrew J. Kelly SQL MVP
"localhost" <primpilus@.cohort.ces> wrote in message
news:jd4cr05eucf55m6kq2rq716e8kpkqb1069@.4ax.com...
> There is a large table with a multiple-column index. What is the
> proper way to search using these columns so the multiple-column index
> is used?
> Thanks.
>
|||localhost wrote:
> There is a large table with a multiple-column index. What is the
> proper way to search using these columns so the multiple-column index
> is used?
> Thanks.
Nothing really. You just need to be aware that in order to use the
index, you have to include columns in the query from left to right (as
they appear in the index). You don't need to include all columns in the
query, but once you skip a column, the index is only useful up to that
point.
For example:
Index: (col1, col2, col3)
Query: Where col1 = 5 and col2 = 6 and col3 = 7 (index used to its
fullest)
Query: Where col1 = 5 and col3 = 7 (index only uses col1)
Query: Where col2 = 5 and col1 = 6 (index uses col1 and col2)
Query: Where col3 = 7 (index not used at all)
Query: Where col2 = 7 (index not used at all)
Query: Where col1 = 7 (index uses col1)
But Andrew's comments are correct. Just because a query can make use of
an index, doesn't mean it will be used.
David Gugick
Imceda Software
www.imceda.com