| Author |
Topic |
|
MiaF
Starting Member
13 Posts |
 Posted - 2008-02-29 : 10:09:59
|
| I have a table "Client" that has two columns: "ClientID" and "ProductID". I created on clustered index on ClientID and when I opened the table in the management studio, I saw the table was in the order of ClientID.Then I added another non-clustered index on ProductID. When I open the table again, it is in the order of ProductID. Shouldn't the table always be in the order of clustered index? Non-clustered index should be a structure outside of the table itself? Did I do anything wrong?Thanks for any hint. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-02-29 : 10:12:19
|
| Add clustered index or non-clustered index is not the way to order table records. You can't always be sure with that. Use ORDER BY instead.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
MiaF
Starting Member
13 Posts |
Posted - 2008-02-29 : 10:18:40
|
| Thanks for the reply. I know that to get ordered query result, I should use ORDER BY in the queries.The problem I'm having now is to verify that the indexes I created are doing what they are supposed to do.Any other suggestions? Thanks |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-01 : 21:57:19
|
| Yes, data in the table are ordered by cluster index on disk. SSMS may not tell you truth. |
|
|
|
MiaF
Starting Member
13 Posts |
Posted - 2008-03-03 : 08:19:33
|
| Thank you, rmiao. So you were saying that the order of the table shown in SSMS panel may not be the real order of the table in the storage. That makes sense. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-03-03 : 09:01:49
|
if there's ever any question, just script the CI to find out what columns it's ordered on.
elsasoft.org |
|
|
|
MiaF
Starting Member
13 Posts |
Posted - 2008-03-03 : 09:14:22
|
jazemine, how should I do this? I never heard about it. Thanks.quote:
Originally posted by jezemine
if there's ever any question, just script the CI to find out what columns it's ordered on.
elsasoft.org
|
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-03-03 : 09:39:45
|
1. open SSMS2. find index in Object Explorer3. right click on index, choose "script as create..."
elsasoft.org |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
MiaF
Starting Member
13 Posts |
Posted - 2008-03-03 : 09:51:57
|
I got it. Thanks. Although it seems not relating to my original question about what column the table is ordered in, it is always helpful to learn new tools/techniques.quote:
Originally posted by jezemine
1. open SSMS2. find index in Object Explorer3. right click on index, choose "script as create..."
elsasoft.org
|
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-03-03 : 10:08:26
|
huh? the columns in the CI tell you how the rows are ordered on disk. if you write a query that orders by these columns, then the optimizer doesn't need to sort the data, it just does a CI scan/seek.seems related to me.
elsasoft.org |
|
|
|
MiaF
Starting Member
13 Posts |
Posted - 2008-03-03 : 10:26:20
|
Sorry I didn't make my point clear."the columns in the CI tell you how the rows are ordered on disk". Yes, that's the principle and what I was looking for after I created my CI. I opened this thread just tried to find a way to prove that the rows ARE ordered in the column of CI. quote:
Originally posted by jezemine
huh? the columns in the CI tell you how the rows are ordered on disk. if you write a query that orders by these columns, then the optimizer doesn't need to sort the data, it just does a CI scan/seek.seems related to me.
elsasoft.org
|
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-03 : 22:43:41
|
| Try 'select * from tab_name' in query window. |
|
|
|
MiaF
Starting Member
13 Posts |
Posted - 2008-03-04 : 08:01:28
|
This still gives me the result set in the order of the non-clustered index "productID".quote:
Originally posted by rmiao
Try 'select * from tab_name' in query window.
|
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-03-04 : 08:15:54
|
order is not guaranteed unless you specify order by. another way to check is to use an order by in your query, then look at the query plan and verify there is no SORT in the plan. there should only be CI Seek/Scan.
elsasoft.org |
|
|
|
MiaF
Starting Member
13 Posts |
Posted - 2008-03-04 : 08:26:11
|
You are right. There is no sort, just CI scan. And it is the same for the NCI, only scan, no sort. Thanks.quote:
Originally posted by jezemine
order is not guaranteed unless you specify order by. another way to check is to use an order by in your query, then look at the query plan and verify there is no SORT in the plan. there should only be CI Seek/Scan.
elsasoft.org
|
|
|
|
Mahesh_jd2k
Starting Member
3 Posts |
Posted - 2008-03-06 : 01:42:42
|
| Can you please give all the statements which you execute on the table. |
|
|
|
MiaF
Starting Member
13 Posts |
Posted - 2008-03-06 : 08:03:37
|
which statements you are asking for?quote:
Originally posted by Mahesh_jd2k
Can you please give all the statements which you execute on the table.
|
|
|
|
|
Table order in Clustered Index?