Using date in order by

Question

Using date in order by

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

karthik M One Orange Chip Points: 29139 More actions · Answer 1

SQLGuru,

Clustered index: order_id (assume int - 4 bytes), order_date (assume datetime - 8 bytes), uniquefier (since it is non-unique - 4 bytes). So 16 bytes for CI
non-clustered PK index: order_id (4 bytes), clustered index pointer (16 bytes)

Can you explain it more details ?

karthik

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

When you create a non-clustered index the Clustered Index Key is added to the non-clustered index. That is what the CI pointer is. A pointer to the row in clustered index which is added to the non-clustered index.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

karthikeyan (8/13/2008)
SQLGuru,
Clustered index: order_id (assume int - 4 bytes), order_date (assume datetime - 8 bytes), uniquefier (since it is non-unique - 4 bytes). So 16 bytes for CI
non-clustered PK index: order_id (4 bytes), clustered index pointer (16 bytes)
Can you explain it more details ?

I am not sure that I can. Perhaps you need to read BOL on the fundamentals of indexing.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

karthik M One Orange Chip Points: 29139 More actions · Answer 4

karthikeyan (8/13/2008)
--------------------------------------------------------------------------------
SQLGuru,
Clustered index: order_id (assume int - 4 bytes), order_date (assume datetime - 8 bytes), uniquefier (since it is non-unique - 4 bytes). So 16 bytes for CI
non-clustered PK index: order_id (4 bytes), clustered index pointer (16 bytes)
Can you explain it more details ?
I am not sure that I can. Perhaps you need to read BOL on the fundamentals of indexing.

I think i have phrased my question wrongly, Actually i know about Clsutered and Non Clustered index, i just wanted to know the below part.

uniquefier (since it is non-unique - 4 bytes). So 16 bytes for CI

Kindly explain it.

karthik

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

[quote-0I think i have phrased my question wrongly, Actually i know about Clsutered and Non Clustered index, i just wanted to know the below part.

uniquefier (since it is non-unique - 4 bytes). So 16 bytes for CI

Kindly explain it.

[/quote-0]

Kindly do as I suggested and see BOL for information about indexes in SQL Server: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/c498fe0c-a0ff-4677-a419-31f0e7875b61.htm

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service