Feedback – Getting Index Definitions
Today I share two comments regarding the issue of finding included columns in an index, not readily displayed using system tools such as sp_helpIndex. Chris provides some fixes to the code I sent as a sample on Friday. James demonstrates getting include columns using SQL Server Management Studio.
Chris:
I don’t know if you pasted the wrong version of your scripts into your email but the script for the sproc had several errors in them. For example you reference sfn_GetIndexColumnList in the sproc and provide 4 arguments when the function only takes 3. Also you hard code the db name for the 3 part naming convention of the function call: SysAdmin.dbo.sfn_GetIndexColumnList. It should be [DbName].dbo.sfn_GetIndexColumnList.
Once those items are corrected the script works and it does seem to be handy.
James:
This is in response to your article today where you mention no way of finding Included Index columns in the regular SQL tools.
This is just not so. It is even covered in the books online. There are other ways to find this in the standard DMV’s.
You can even find information on how well your covering index is covering a queries by merging the columns in the index with the columns in the Missing Index DMV.
In SSMS.
Right click on the Index in the Table Definition data in SSMS.
This Brings up the Index Properties Screen. From this just click “Included Columns” in the Select a page section.
Thanks Chris for the corrections on the SQL. James, Thanks for the tip on finding included columns using standard SQL Server tools.
If you have anything you’d like to share send your input to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Making Static SQL More Dynamic… and Vice Versa
SQL can be either static or dynamic. Static SQL is rigidly formed and usually provides a known access path to data. On the other hand, dynamic SQL, as you might expect, is more flexible. This flexibility brings with it important trade-offs, the most important of which is the potential for more accurate access path formulation at the cost of not knowing what that access path will be before the dynamic SQL runs.
Featured White Paper(s)
SharePoint 2010 Enables the Enterprise
Written by KnowledgeLake
read more)