Comprehensive Index Information

Hi,

I am writing an in house utility to attempt to compare different
aspects of databases.
I am currently writing the queries to list all of the indexes in the
database (including primary key indexes at present - I may move these
and compare separately at some point).

I would like the following information, in one result set if possible:

Table Name
Index Name
Column Name
Column Position
Unique?

Now on Oracle, this is easily done with the following query:

SELECT IND.TABLE_NAME, IND.INDEX_NAME, IND.COLUMN_NAME,
IND.COLUMN_POSITION, COL.UNIQUENESS
FROM USER_IND_COLUMNS IND,
USER_INDEXES COL
WHEREIND.INDEX_NAME = COL.INDEX_NAME
ORDER BY 1, 2, 3, 4, 5

I have been trying for over an hour now to get the equivalent, and I
really cannot figure it out. If anybody can come up with this then I
would greatly appreciate it!

Many Thanks,

PaulPaul (paulwragg2323@.hotmail.com) writes:

Quote:

Originally Posted by

I am writing an in house utility to attempt to compare different
aspects of databases.

Before you go too far, pay a visit to http://www.red-gate.com and
if SQL Compare meets your needs.

Quote:

Originally Posted by

I am currently writing the queries to list all of the indexes in the
database (including primary key indexes at present - I may move these
and compare separately at some point).
>
I would like the following information, in one result set if possible:
>
Table Name
Index Name
Column Name
Column Position
Unique?

SELECT tablename = t.name, indexname = i.name,
colname = c.name, pos = ic.index_column_id,
indextype = i.type_desc, isunique = i.is_unique
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.index_columns ic ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
JOIN sys.columns c ON t.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE i.is_hypothetical = 0

There are probably more columns should include in the output, but I
levae that as an exercise.

Note: the above works in SQL 2005 only. Next time, please specify which
version of SQL Server you are using.

--
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|||Hi Erland,

Thankyou very much for this. Of course, as usual I stupidly forgot to
post the version. Sorry about that. Really I need something that will
work on both SQL Server 2000 and SQL Server 2005.

Thanks for the link - unfortunately this is more of an exercise for
the time being and so we are not willing to spend money on a tool at
present!

Thanks for the help - if you do know something that will work on both
versions that would be good.

Paul|||Paul (paulwragg2323@.hotmail.com) writes:

Quote:

Originally Posted by

Thankyou very much for this. Of course, as usual I stupidly forgot to
post the version. Sorry about that. Really I need something that will
work on both SQL Server 2000 and SQL Server 2005.

Then you need to work against sysobjects, sysindexes, sysindexkeys and
syscolumns. The query will be similar, but you need to filter for
statistics, since in SQL 2000 statistics and indexes live in sysindexes.

These are documented in Books Online, and since this is an exercise for you,
I leave you there. :-)

--
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|||Thanks Erland.

composite primary keys versus composite unique indexes

Hello,

I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.

The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold

1. multiple columns need to be used for joins and I think this might degrade performance?
2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.

A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.

I would like to have some tips, recommendations and alternatives for what I should do in this case.

One item that is not always immediately apparent for this type of scenario has to do with clustering. Is your primary key also presently your clustered index? If the answer is yes and you change to a primary key based on an identity column and leave the CLUSTERED / NOT CLUSTERED aspect of this primary key to be defaulted then any query that still accesses data based on your current primary key will experience a new performance problem -- these accesses will now also require a bookmark lookup.

The good news is that this is might not grieve you for most OLTP activity, but it is likely to impact some reports and perhaps some record lookups; however, this is rarely enough of a drawback to preclude the change your are considering. Just file keep this in mind and if some reports or lookups become slower there is a chance that this modification might be the reason.

The bigger problem usually is modifying tables that reference your table with foreign key constraints. You have two alternatives: First, you can replicate the identity column into the foreign table and now use this value as the foreign key reference; however, if you do this you should also drop the columns that references the old foreign key columns of the table that you are changing. The second alternative is to continue to reference your modified table by what has now become the UNIQUE constraint rather than the primary key. This works and I have used this a number of times but it can be unsavory. Also, if you choose this alternative you need to know the that REFERENCES clause of the foreign key constraint will now need to explicitly list the columns of the UNIQUE constraint or your FK constraint will not compile.

Also, you might have some reports, functions, or stored procedures that filter based on one of these columns in one of your foreign tables. Changing the foreign key reference might be an impact here also.

The moral of this second problem is that you want to know all tables that have foreign key constraints to the table you are considering modifying. There might be a lot of hidden additional work if your table is referenced from a couple of dozen other tables.

composite primary key

i have a master table with around 15 columns and i am trying to find
the appropriate primary keys and indexes for the table.

To make the records unique, i need to include two datetime columns (
start and end dates ) and two integer columns ( attributes of the
record ) to make up a composite primary key. Both of these four
columns are found in the WHERE clause of my queries.

Is it acceptable in the view of performance and how should i create
the indexes?Hi

I think this is probably a candidate for a surrogate key.

John
"onder" <okeen1@.hotmail.com> wrote in message
news:9c3d3756.0309022355.10656428@.posting.google.c om...
> i have a master table with around 15 columns and i am trying to find
> the appropriate primary keys and indexes for the table.
> To make the records unique, i need to include two datetime columns (
> start and end dates ) and two integer columns ( attributes of the
> record ) to make up a composite primary key. Both of these four
> columns are found in the WHERE clause of my queries.
> Is it acceptable in the view of performance and how should i create
> the indexes?|||Hi John,

sorry for this but can you tell a little more on that?|||Hi

There is usually quite a debate in the newsgroups when surrogate keys are
mentioned. Basically it is when you add a column that you populate with
unique values, so you can reference a row by that instead of the 4 columns
you mentioned. This is a way to reduce the width of any table that refers to
your table with a foreign key (you would need all 4 columns replicated in
that table if you did not use a surrogate key). If you searched google for
"Surrogate SQLServer" it will turn up a few debates.

Quite often a surrogate key is given the IDENTITY property, so SQL Server
will populate the values for you,
http://msdn.microsoft.com/library/d...asp?frame=true
but it is possible to maintain the values your self.

HTH

John

"onder" <okeen1@.hotmail.com> wrote in message
news:9c3d3756.0309030711.5f4f582c@.posting.google.c om...
> Hi John,
> sorry for this but can you tell a little more on that?|||Ok about the surrogate keys. But i have an issue to ask:

In my 'select' queries, i always have the three columns which defines
a unique combination. So, i think the optimization of the table design
should bear this in mind.
If i add a surrogate key to the table to be a primary key, then i will
need to get that column to my select queries and then do the updates
based on that column.

What would you recommend for the index of the table?|||Hi

You would need to have the surrogate key in the JOIN or WHERE clause. This
could be totally transparent to the user. As a Primary Key there will
already be an index on the surrogate key column,

It is possible a covering index on the surrogate and candidate key columns
will be useful, but like any index you should investigate if it is being
used and if the speed improvements are worth while.

John

"onder" <okeen1@.hotmail.com> wrote in message
news:9c3d3756.0309080039.65cccc67@.posting.google.c om...
> Ok about the surrogate keys. But i have an issue to ask:
> In my 'select' queries, i always have the three columns which defines
> a unique combination. So, i think the optimization of the table design
> should bear this in mind.
> If i add a surrogate key to the table to be a primary key, then i will
> need to get that column to my select queries and then do the updates
> based on that column.
> What would you recommend for the index of the table?

composite nonclustered index

Hi everyone,
I have some problems on composite nonclustered indexes. I could not exatly understand their logic.
In my opininon, suppose that we have a table called Order and we create a composite nonclustered index on this table for OrderID column and OrderDate column. So I am using this query;

SELECT * FROM Order WHERE OrderID > 12 ORDER BY OrderDate
So in here, I think our first research is based on OrderID and ten after ordering our data pointer according to the OrderID and then our index is converted to an index which is based on OrderDate while performing ordering. So is this correct ?
Would you please explain this ?

Thanks

There would be an index seek on OrderID, then a bookmark lookup on the table to find the actual data. And then the order is done on the resultset by OrderDate.

You can see all this by highlight the query (in Query Analyzer) and press Ctrl+L.|||

i dont know what exactly it is that you don't understand.

lets start with the clustered and non clustered index. you can only create one clustered index per table. however there can be queries that may run against the clustered index so non clustered index was introduced ...

Clustered Indexes

There can be only one clustered index on a table or view, because the clustered index key physically sorts the table or view. This type of index is particularly efficient for queries, because data records—also known as data pages—are stored in the leaf level of the B-tree. The sort order and storage location of a clustered index is analogous to a dictionary in that the words in a dictionary are sorted alphabetically and definitions appear next to the words.

When you create a primary key constraint in a table that does not contain a clustered index, SQL Server will use the primary key column for the clustered index key. If a clustered index already exists in a table, a nonclustered index is created on the column defined with a primary key constraint. A column defined as the PRIMARY key is a useful index because the column values are guaranteed to be unique. Unique values create smaller B-trees than redundant values and thus make more efficient lookup structures.

Nonclustered Indexes

You can create up to 250 nonclustered indexes (or 249 nonclustered indexes and one clustered index) on a table or view. You must first create a unique clustered index on a view before you can create nonclustered indexes. This restriction does not apply to tables, however. A nonclustered index is analogous to an index in the back of a book. You can use a book's index to locate pages that match an index entry. The database uses a nonclustered index to locate matching records in the database.

If a clustered index does not exist on a table, the table is unsorted and is called a heap. A nonclustered index created on a heap contains pointers to table rows. Each entry in an index page contains a row ID (RID). The RID is a pointer to a table row in a heap, and it consists of a page number, a file number, and a slot number. If a clustered index exists on a table, the index pages of a nonclustered index contain clustered index keys rather than RIDs. An index pointer, whether it is a RID or an index key, is called a bookmark. .

|||

Hi,
First of all, thanks for your replies.
Infact, I ask that what is the difference between using two different nonclustered index for each column and using a composite nonclustered index for the above example(in my first reply)

Thanks

|||

There is huge difference merit.
If you have a composite index on (fCOL1, fCOL2) and a query

select * from T where fCOL2 = @.Value

Then the optimizer has no way to use your index. If you have 2 separate indexes, the optimizer will most likely use the index on fCOL2.
I'd reccomend that you refer to BOL for datailed coverage of indexes.

|||

Hi,
Thanks for your reply but it came to me a little bit confusing.
By saying this, "Then the optimizer has no way to use your index"; I could not understand whay you meant completely. Would you please make it clear ?
And besides, I read the BOL and this is the question that I could not find the answer.

Thanks

|||

Infact, What is the logic of composite nonclustered indexes ?
How can I imagine its algorithm in my mind ?

Thanks

|||Ok merit, but let's begin from clustered index.
Imagine an encyclopedia. Encyclopedia is sorted by topics, in alphabetic(lexicographic) order. This is the phisical or clustered index. It corresponds volume number and page number to each topic in it. E.g. if you want to search for topic "Database", what should you do? You look at the volumes. Each volume has the names of first and last topics written in hardcover. For example, the 3-rd volume of encylopedia might have topics "Cat" as first and "Easter" as last on the cover. As "Cat"<"Database"<"Easter" in ordinal lexicographical order, you can safely assume that the article "Database" is located in 3-rd volume.
This is analogous to clustered index root level lookup.
What do you do next? You open the volume somewhere in the middle, look at the topic of article. If it is, say, "Dog", then you have to look for "Database" in the first half of the book. If it is "Dao", then you should consider only the second part if the volume. Using this technique you can easily navigate to "Databases" in quite a few steps.
Now, how can we imagine a non-clustered index?
You surely know that each article in encyclopedia has an author. In the encylopedias I have been lucky to look at, each author is pointed at the end of the article.
Now, what if I want to navigate to all topics that author "merit" has written? Well, at the end of most books, there is a list called "index", which shows correspondence between <specific-names> and pages on which that <specific-name> was mentioned. For example, the philosophy book I have just finished, has a whole page of index for "Plato". It's like Plato-2,3, 5,6,10,20... That is, if I want to look for an article by "merit" in encyclopedia I have to just open the end-book index, and look at the pages where "merit" has articles.
To this point, there is a nuance that makes a difference between most of book indexes and non-clustered indexes in database. While indexes in books point to actual pages, database non-clustered index points to clustered index entries. This means that if merit has authored the article "Database" in encylopedia, which is located in page 253 of volume 3, the encyclopedia index contains the entry "merit-volume 3, page 253", while a non-clustered index contains the entry 'merit-Database'. So, navigating in non-clustered index structure is like navigating the authors list of encyclopedia. As soon as you have found the author, you get all the articles he has authored, and, using the clustered index, easily navigate to the desired article.
Now assume that the authors index is organized in format <Name-Surname>. In that case, it will contain entry Andranik Khachatryan-"Indexes pseudo-explained using Plato" ;).
Now you see why you cannot use the index to navigate to authors whose surname is "Khachatryan"? Because the authors index structure is sorted by Firstname, so, if you wish to look for a specific surname, you must look up all entries! In language of databases, this is always much less effective than just scanning the encylopedia ... oops ... database for Khachatryan.
The analogy I described here is not full and contains some inaccuracies. It works only at first appoximation.|||

REAL WORLD SCENARIO

lets say i have a junction table for student and subject

which is a many to many relation

and i call it table "studentsgrades"

here's how the table look like

recno | studentno | subjectid | prelim | midterm |final

studentgrade has "recordno" as primary key

now i want to impose a unique index on studentno and subjectid

where studentno is a foreign key from students table and subjectid is a foreign key

from subjects table. the solution requires that for every subject there could only be one instance of "student 1" though different students such as student 2 and 3 maybe in the same subject.

now all i have to do to achive this solution is to create a unique composite index on students and subjects.

"recno" is clustered pk because of many inserts and that recno is used to relate to table "adviserscomments" which has recno as fk.

now there are two requirements which are to create a report that will print the "students grades" for all of his subject and the "class record" which will display the grades of every student in the class

the "student grades" uses the query

select studentno,subjectid,prelim,midterm, finals where studentno = @.studentno

the class record uses this query

select subjectid,studentno,prelim,midterm,final where subjectid = @.subjectid

these query will not make use of the pk so to make the solution work fast an index must be made.

to make it realy fast i make a covering index A on studentno,subjectid,prelim,midterm,final

only "student grades" requirement will benefit from this index so

i have to make covering index B on

subjectid,studentno,prelim,midterm,final

A covering index, which is a form of a composite index, includes all of the columns referenced in SELECT, JOIN, and WHERE clauses of a query. Because of this, the index contains the data you are looking for and SQL server doesn't have to look up the actual data in the table, reducing logical and/or physical I/O, and boosting performance.

to summarize composite non clustered index can be used to

1. implement uniqueness of more than 2 columns

2. implement covering index

for more reading on composite index pls consult this link

http://www.sql-server-performance.com/composite_indexes.asp

|||

Hi,
Indeed

thanks very much to both of you. Both of your post's are indeed

beneficial for me however, something still make me confused which I

would like to share with you.
I learned that while using composite

indexes, we must give importance to the order of the indexes. So if we

use where clause , we must put the column leftmost which we use in the

where clause. So, I am confused that what is the usage of other column in the index part in here ?

Thanks

|||

thats the index order of selectivity and sort-priority order starting from the left

A composite index is an index on two or more columns of a table. You should consider performance when creating a composite index because the order of columns in the index has a measurable effect on data retrieval speed. Generally, the most restrictive value should be placed first for optimum performance. However, the columns that will always be specified should be placed first.

About selectivity,Sometimes two or more columns, each with poor selectivity, can be combined to form a composite index with good selectivity.

|||

Hi,
Thanks for this reply again.
In here,
SELECT * FROM Order WHERE OrderID > 12 ORDER BY OrderDate

we create composite nonclustered index for both OrderID and OrderDate column.
Leftmost is OrderID.
So when our first research is happening(Where clause), the only nonclustered index which is used is OrderID index. And then, when we pass through the second search(ORDER BY clause), OrderDate index become activated.
So we can say that the seleection of indexes in composite indexes is determined according to the situation of the query at that time.

Hence, is this all correct ?

Thanks again for all clarifications.

Best wishes,
Mert

|||

Also,
we should not forget that first member of the composite index must be the most selective column for increasion of the performance.

composite indexes - a subtle question

I have a table like so
CREATE TABLE [dbo].[account] (
[pty_id] [int] NOT NULL ,
[sort_code] [int] NOT NULL ,
[account_no] [int] NOT NULL ,
[account_open_dt] [datetime] NULL ,
[account_close_dt] [datetime] NULL ,
[account_nm] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[market_sector] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[market_segment] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[category_code] [smallint] NULL ,
[prime] [char] (1) COLLATE Latin1_General_CI_AS NULL
CONSTRAINT PK_ACCOUNT PRIMARY KEY CLUSTERED (sort_code, account_no)
) ON [PRIMARY]
as you can see there's a primary key clustered index on account_no and
sort_code
I can link it up to any other table efficiently which also has sort_code and
account_no as part of a single index (primary or otherwise)
ON (a.sort_code= b.sortCode) ANd (a.account_no = b.account_no)
etc...
and that will work fine...
Here's my question
if account_no is part or the sort_code/Account_no composite key (and
therefore index)
could I link another table to only the account_no column and still get some
indexing benefit
i.e can you join on a single column of a multiple column index and still get
some help from that composite index or does that composite index only work a
s
a single entity and in fact you need to create an extra nonclustered index
for that column on top of the other index it's participating in to get the
benefit of indexing on that column...
phew
I hope this makes sense
any help would be great appreciated
Regards and thanks in advance,
CharlesACharles,
When joining to the first column of a multi-column index, some
of the same optimizations are available--for example, a merge
join (if the other table also has a supporting index) or nested loop
joins with index ss. Depending on the exact query, the two-column
index may be just as useful as a separate one-column index or a
little less useful (typically because the two-column index takes up
more data pages because of duplications in the first column and the
presence of the second column even when duplicates are few), but it
should still help in most cases. The only real way to be sure is
to create the additional index and measure the performance.
Steve Kass
Drew University
CharlesA wrote:

>I have a table like so
>CREATE TABLE [dbo].[account] (
> [pty_id] [int] NOT NULL ,
> [sort_code] [int] NOT NULL ,
> [account_no] [int] NOT NULL ,
> [account_open_dt] [datetime] NULL ,
> [account_close_dt] [datetime] NULL ,
> [account_nm] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [market_sector] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
> [market_segment] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
> [category_code] [smallint] NULL ,
> [prime] [char] (1) COLLATE Latin1_General_CI_AS NULL
> CONSTRAINT PK_ACCOUNT PRIMARY KEY CLUSTERED (sort_code, account_no)
> ) ON [PRIMARY]
>
>
>as you can see there's a primary key clustered index on account_no and
>sort_code
>I can link it up to any other table efficiently which also has sort_code an
d
>account_no as part of a single index (primary or otherwise)
>ON (a.sort_code= b.sortCode) ANd (a.account_no = b.account_no)
>etc...
>and that will work fine...
>Here's my question
>if account_no is part or the sort_code/Account_no composite key (and
>therefore index)
>could I link another table to only the account_no column and still get some
>indexing benefit
>i.e can you join on a single column of a multiple column index and still ge
t
>some help from that composite index or does that composite index only work
as
>a single entity and in fact you need to create an extra nonclustered index
>for that column on top of the other index it's participating in to get the
>benefit of indexing on that column...
>phew
>I hope this makes sense
>any help would be great appreciated
>Regards and thanks in advance,
>CharlesA
>|||Charles,
Can you recreate your PK as (account_no, sort_code)? What are the
cardinalities of these 2 columns?|||Charles,
I know you'd like an immediate answer, but it might really help to
watch Kimberly Tripp's (SQL Server MVP) presentation at the following
website. I'm in the process of designing indexes and it was incredibly
helpful to watch the presentation. She keeps it simple but I finally
got it...
http://www.microsoft.com/uk/technet...aspx?videoid=29
Other links:
http://msevents.microsoft.com/CUI/E...e=en-U
S
http://www.sqlskills.com/blogs/kimb...
3-a58ba7b1265e
(The presentation took me a while to download and it is an hour long
but totally worth it.)|||Hi,
Whether you get performance benifit or not depends on the location of the
column in the order of the index key.
You want to join Account_no from tbl1 with Account no in tbl2 with
sort_code/Account_no as an index.
If the left most column in the index is sort_code i.e
Index(sort_code,Account_no) , you don't find any use of the index, it will
still go for an idex scan.
But if your index had been Index(Account_no,sort_code)
then this index is sufficient for joining Account_no alone or Account_no and
sort_code.
Hope I made sense :)|||Thanks everyone for your answers, much appreciated...
I will download that presentation when I'm at home, I've been dying to
understand indexing properly for ages now
as I say, thanks All
Regards,
CharlesA