Indexing Questions
-
Query for tables index properties
Instead of using Enterprise Manager and going into design of a table to get the index properties, i.e. columns ect.. I was trying to find the syntax that would give me this in Query Manager. Also can you query multiple tables at once for this information vs one at a time?
Deciding when to add non-clustered index
Is it posible to add a non-clustered index to a clustered index. At what point would this be advantageous? Are there methods to determine if a table would benefit from this?
Thnxs in advance
-
See system stored procedure sp_helpindex
Not using the MS provided methods but there are some index related scripts at http://www.sqlservercentral.com/Scripts/listscripts.asp?categorynm=Index%20Management&categoryid=4
This is a very complicated subject.
I recommend Dr. Dennis Shasta's book titled
"Database Tuning: Principles, Experiments, and Troubleshooting Techniques" which has 440 pages of methodology which can apply to all RDBMS including Oracle, DB2 and SQL Server. Dr. Shasta's credentials include extensive hand-on experience since the mid-80s with performance tuning relational databases for financial clients e.g. Wall Street trading systems. ISBN is 1558607536
SQL = Scarcely Qualifies as a Language
-
You can add the clustered index to nonclustered index in SQL Server, it is called index covering, used by Microsoft consulting. The requirements the clustered index must be very small but I think the best book that covered SQL Server indexes is by Ken England he quantifies the IAM(index allocation mapping) pages.
BTW the Microsoft press performance tuning book is useless because one of the writers did not know the difference between UNION and UNION ALL and what is the now discontinued DBCC SHOWCONTIG.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
Uhm, the columns of the clustered index are always part of all non-clustered indexes. A covering index is a non-clustered index that includes all the columns (with the help of the columns in an existing clustered index) specified in a certian statements output list and where clause. If this is the case the index covers that statement, which means that the engine does not need to fetch the actual data rows to return the results for it, since all data is available in the non-clustered index.
-
(You can add the clustered index to nonclustered index in SQL Server, it is called index covering, used by Microsoft consulting.)
(A covering index is a non-clustered index that includes all the columns (with the help of the columns in an existing clustered index)
(Covered queries can improve performance. Covered queries are queries where all the columns specified in the query are contained within the same index. For example, a query retrieving columns a and b from a table that has a composite index created on columns a, b, and c is considered covered.)
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
Thhnxs for all your replies! I will look for one of the books mentioned. I'm a newer DBA but find SQL quite interesting and appreciate the feedback and suggestions
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply