hi,

i want to know how can i retrieve data from a table using indexes?

 

Hi Methsika,

SQL Server automatically uses indexes if exist and can be used for query you execute. If you want to know whether the index is being used for your query, see the execution plan.

If SQL Server does not use the index available, again you can force SQL Server to use the index by using INDEX table hint.

hi,

actually there i meant that regarding the "create index" command and how can i retrieve data from those.

Your question is still not clear to me. "Create Index" command is used to create an index against a table. This command is not used to retreive data.
Do you want to know about Create Index Command?
Do you want to know what columns are beign used on an Existing index?
Do you want to retreive data from table by using the index only? (without doing a table scan)

Please give additional details on the question. ... Thanks

---

G.R.Preethiviraj Kulasingham MCITP: DBA
http://preethiviraj.blogspot.com/
Plan Your Work and Work Your Plan!

 yes sir,exactly i need explanation on the questions you raised regarding my post 

ok. It looks like you want to know everything about index. This is not the right place to give all the details about index. It needs at least an article, probabily a book to discuss all about index. Let me summarize things as much as possible:

Question: Do you want to know about Create Index Command?
To create an index you can use CREATE INDEX ON .([,

Question:Do you want to know what columns are beign used on an Existing index?
EXEC sp_HelpIndex will give details of the indices used, and the columns used. you can also get the details using sysindexes categolg view. Additionally, sysindexkeys will give the columns assigned to an index

Question: Do you want to retreive data from table by using the index only? (without doing a table scan).
If you refer the only columns used by a nonclustered index and clustered index in your query, there is a high possibility you will use only the index. (Yes there is a remote chance you may go aginst clustered index scan, which is almost equal to table scan) Limiting the columns is important because of this reason.

Hope this helps.

---

G.R.Preethiviraj Kulasingham MCITP: DBA
http://preethiviraj.blogspot.com/
Plan Your Work and Work Your Plan!