October 22, 2009 at 1:21 pm
Hi
Have a table with clustered index on a datetime field. There are several other indexes (non-clustered) on same table.
If it were possible to change the datatype of the datetime field to smalldatetime would this Half the size of ALL non clustered indexes on that table? Because its 4 bytes down to 2.
Wanted to confirm that a clustered index is actually free, i.e. the data pages sorted in that way so doesn't effect data size (that much); I ask cos some of the SPs on this site which return index size quote the clustered index as the largest, but is it just reproducing size of the data itself?
October 22, 2009 at 1:30 pm
yes, clustered index = data. No separation there, so it's the largest size.
I think that if you move from datetime to smalldatetime, you will make the indexes smaller, both NCs and CLI. Data size, is also smaller, at the expense of precision.
October 22, 2009 at 1:40 pm
Wanted to double check.
Non clustered index will use a clustered index if exists, so important clustered index field(s) has to be small.
I wonder if sp_spaceused is reporting clustered index as part of size - so gets counted twice.
October 22, 2009 at 4:25 pm
You're right that clustered key should be small. But (maybe I don't understand you well) if you halve size of the clustered key it doesn't mean all nonclustered indexes will be half-size.
The data rows in nonclustered index consist of nonclustered key PLUS clustered key if it exists, or RID if the table doesn't have clustered index. So if you have nonclustered key that is let's say 10 bytes long, changing datetime to smalldatetime will shorten length of rows in the index from 18 (10 + 8) to 14 (10 + 4) bytes.
Regards
Piotr
...and your only reply is slàinte mhath
October 22, 2009 at 7:43 pm
Thanks for that, I had misunderstood.
I need to store just date no time part.
Converting to int 20091023 wouldn't even be beneficial.
October 23, 2009 at 6:48 am
Since you're posting this in the 2005 forum, you could use the Date data type. It's only 3 bytes. That's one less than smalldatetime and 5 less than datetime.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 23, 2009 at 12:16 pm
This would be SQL 2008 Grant 😉
Regards
Piotr
...and your only reply is slàinte mhath
October 23, 2009 at 12:47 pm
Piotr.Rodak (10/23/2009)
This would be SQL 2008 Grant 😉Regards
Piotr
Oh nuts am I bleeding over again? How come everyone doesn't just upgrade to 2008 and be done with it. It'd sure be easier on my brain.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply