June 10, 2006 at 3:12 am
Hi,
Well we all know about Clustered index,Non-clustered index.
I need to know why do we create clustered index on a table and why do we create a non -clustered index.
when we should create a clustered index and when to create non-clustered index.
What are the criteria of using the two?
Hope u get the question?
from
Killer
June 10, 2006 at 8:41 pm
It's hard to determine when to create a clustered and nonclustered index. There's rarely one sold rule for "this is the best possible way to do this".
A clustered index simply defines the sort order for the data in the table. If I have a table with a clustered index on columnX, SQL Server will store the data physically based on columnX.
A nonclustered index is a seperate object designed to allow SQL Server to more quickly find the data its looking for. It maintains pointers to the physical locations of the data in the table. For instance, if I have a table with 20 columns, but I know that I'll run a lot of queries to search on column3, I can create a nonclustered index on column3. It in turns maintains pointers to the physical location of the actual data in my table. So while column3 might not be stored in order in my table, the query engine can look at my index on column3 really fast and find where individual data is located.
Also, a benefit of nonclustered indexes is that at times you don't even need to hit the physical table for query data. If I have a table called customers and in that table is a column called customer_state (char (2) if you're curious) which contains the 2 digit state abbreviation. I've created a nonclustered index on that column for faster searching. Now I want to know how many customers I have in California. I can execute the following query:
SELECT COUNT(*) FROM customers WHERE customer_state = 'CA'
The query engine doesn't need to go to the physical data store because all the information needed to satisfy the query is located in the index. Because the index only contains the column values and pointers to their physical location, it can be searched much faster (in many cases) than searching the physical data store.
I know this is long winded, but hopefully you understand the difference. There's several decent articles about the subject online, the following seems to be pretty good:
http://www.odetocode.com/Articles/70.aspx
Good luck.
June 11, 2006 at 10:47 pm
Hi, Thanx for the reply.
But u did not answered any of my question asked.
The discription cant help me.
from
Killer
June 12, 2006 at 5:44 am
You can only have one clustered index because that's the way the data is physically sorted. Non-clustered indexes reference the clustered index, if one is created, which is why you can have many of those.
Indexes are put in place to improve performance. Therefore, there are a lot of cases when you want multiple indexes on a table (but don't go crazy as too many indexes can impair performance any time a change is made). You typically use clustered indexes to handle queries where a range of data is brought back (for instance, all loans over a particular date period). As to what makes better sense, a clustered or non-clustered index, that's determined based on how the data is accessed.
K. Brian Kelley
@kbriankelley
June 12, 2006 at 9:10 pm
Hi bkelley,
That is what i need to know.When we should create a clustered index or non clustered index.
If i had a table with 16 GB of Data with 87 columns.
Do i need to go for primary key index with create as clustered or just primary key index.
and how many non clustered indexes should i create.
Hope u understad the question?
from
Killer
June 12, 2006 at 11:08 pm
Hi Killer,
I think your question is incomplete to get the ans. Knowing the table size and no of columns one cannot decide on how many non clustered index are required and should primary key be clustered index or not.
For ur ans I think you need to do little bit of investigation on following things.
1) columns frequently used in the queries
2) data type of columns frequently used in the queries
3) Sort order used in the queries
4) Where clauses & grouping & filtering
IF you find the above things and do a bit of investigations on it than you urself will find the ans of ur question
Hope you get my point................... IF not than do let me know.
Thanks
Sejal Gudhka
June 13, 2006 at 5:24 am
I agree. The primary key is there to ensure each row is unique within the table. By default SQL Server will create a primary key as a clustered index, but Sejal Gudhka is right: where you put the clustered index is dependent on how the data is being used. Just because a column or set of columns helps identify the row doesn't mean it's the best option to put the clustered index on.
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply