Knowledge of T-SQL inbuilt functions and store procedure can save great amount of time for developers. Following is very simple store procedure which can display name of Indexes and the columns on which indexes are created. Very handy stored Procedure.
USE AdventureWorks;
GO
EXEC sp_helpindex 'Person.Address'
GO
Above SP will return following information.
IndexName - IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
Index_Description - nonclustered, unique located on PRIMARY
Index_Keys - AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
Let me know if you think this kind of small tips are useful to you.
Reference : Pinal Dave (http://www.SQLAuthority.com)
pinaldave
Hi,
Yes these king of small tips are helpful. I have a question of my own though it has to do with system tables containing information about user defined tables.
If I want to find the number of columns in a user defined table, can i do it with a simple select statement, or do i have to manually go in each table and count it?
I hope you will answer this question at the earliest possible
Regards
Mohammad
Hi,
Sorry, I should have checked other articles before posting my question.
Thanks again
Mohammad
Table:
TableA
Columns:
Column1
Column2
Column3
Column4
Indexes
Column1 – Simple individual Index
Column2 – Simple individual Index
Query
1. SELECT * FROM TABLEA WHERE COLUMN1=’A’ AND COLUMN2=’B’
2. SELECT * FROM TABLEA WHERE COLUMN2=’B’ AND COLUMN1=’A’
3. SELECT * FROM TABLEA WHERE COLUMN1=’A’ AND COLUMN3=’B’
4. SELECT * FROM TABLEA WHERE COLUMN2=’A’ AND COLUMN4=’B’
could you pleasetell me which indexes use in case (queries.)
or both are equally important.?
is the other individual index useless and should be deleted?
Really useful!!!
Thx!!!