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 (https://blog.sqlauthority.com)
14 Comments. Leave new
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!!!
send me some tips and material to my mail please
Hello Sir,
please answer me that how can i get the -ve value’s sum in other column like as given below table
id value diffrence
1 900 900
2 -500 400
3 -200 200
Please tell me the query of sql server 2005.
it will b great full.
[email removed]
mail me plz
Search for Running Total in this site
select *from(
(select *from Kishor.summ))c1,
(select SUM(value) summ from Kishor.summ)c2
Hi Shivam ,
try below query i think it will help you
select *from(
(select *from Kishor.summ))c1,
(select SUM(value) summ from Kishor.summ)c2
dear
Every one
to creart an index we write this quary
CREATE INDEX index_name
ON table_name (column_name)
but if you search values on it, so for this you write quary…………
You have to make sure that functions are not used over this column in the where clause
I have (Is Identity) = Yes for a column – ID
Now, I want to know which next value will be inserted for this column.
Can I know that?
Thank u in advance.
Use this
select ident_current(‘table_name’)
Hi,
Thanks for the useful tips.
I need help to sort out one problem. Actually in our database at one point of time we created an index on a single column and released it in our upgrade script and now some of the clients are running with this. But now according to our requirement we want to update the same index but want it on two columns. But we dont want to drop the index in our upgrade script. Is there any way to retrieve the column names on the existing index so that we would be able to update our upgrade script to check if the existing index is on one column create index on second as well other otherwise create multi column index .