Plavement of rows when clustered index containing NULL values

Question

Plavement of rows when clustered index containing NULL values

Gabor Nyul

SSCrazy Eights

Points: 9459
More actions
February 27, 2006 at 3:44 am

#189362

Hi,
Could you tell me how the data is organisd within the data pages when a clustered index keys have NULL values.
i.e are the rows containing the NULL values at the beginning or at the end of the table

Bye
Gabor

Viewing 6 posts - 1 through 5 (of 5 total)

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

SQLZ SSChampion Points: 12872 More actions · Answer 1

Hi Gabor,

This depends on the order of the clustered index. If it is ascending, the null values will be at the beginning. If it is descending, the null values will be at the end.

Karl
source control for SQL Server

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 2

Hi Karl,

Do you know how I can verify it. I suppose also that the data is organised as you told me, but I would like to "see".

I didn't find it in the BOL nor in the book of Kalen Delaney. Maybe I was not searching in the right place.

Bye
Gabor

SQLZ SSChampion Points: 12872 More actions · Answer 3

Sure,

Just do a select * from

.

By default, the results are ordered on the clustered index (unless you explicitly use an order by clause).

Karl
source control for SQL Server

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

... doesn't it seem counter productive to have an index where a portion of it may be 'NULL' ???

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 5

Rudy,

It make sense to have NULL values in a clustered index. Especially when the index is an a date field and I'm doing reange selection based on this date field. When a case closed the close_date field is not NULL otherwise it is NULL.

Bye
Gabor