Creating Index on DATETIME Column based just DATE

Question

Creating Index on DATETIME Column based just DATE

sudhirnune

SSCrazy

Points: 2342
More actions
January 19, 2012 at 11:20 pm

#251578

I have a table which has a column (ISSUE_DATE) which is a date time Data type.
But for improving the Query Performance i need to Index the Table based on the Date Column (ISSUE_DATE).
If i create a Index directly on the Column the index will be huge and it merely waste.
So I want to create an Index on the same column but which generates the Index on Date, instead of date time.
Bottleneck is cannot create an extra column on the table.
Please need help.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

sudhirnune (1/19/2012)
I have a table which has a column (ISSUE_DATE) which is a date time Data type.
But for improving the Query Performance i need to Index the Table based on the Date Column (ISSUE_DATE).
If i create a Index directly on the Column the index will be huge and it merely waste.
So I want to create an Index on the same column but which generates the Index on Date, instead of date time.
Bottleneck is cannot create an extra column on the table.
Please need help.

your second guessing whether the index will help without any proof or testing.

first, you of course need to test it on a development environment prior to deploying it.

Remember a datetime is actually stored behind the scenes as two integers. In my experience, indexes on datetime columns can help queries a LOT.

the index will still allow SQL to do a better job even if the dates are not an exact match (milliseconds)

i'd say test the potential index first, regardless of it's size, by comparing the execution plans before and after you add the index.

think of it this way: if you had an integer column that you KNEW only help numbers in increments of 5, an index help would help any queries that are searching the column regardless of the searched parameter...whether it was @param=42 or not, SQL would know the order of the data and be able to seek for it; if you needed valuesgreater than @param, then a seem would get a lot of help fromt he index too.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

sudhirnune (1/19/2012)
I have a table which has a column (ISSUE_DATE) which is a date time Data type.
But for improving the Query Performance i need to Index the Table based on the Date Column (ISSUE_DATE).
If i create a Index directly on the Column the index will be huge and it merely waste.

5 extra bytes per row, datetime vs date. Not that huge. Unless you have millions upon millions or rows (or a overly-large clustering key), your nonclustered index shouldn't be that large

So I want to create an Index on the same column but which generates the Index on Date, instead of date time.
Bottleneck is cannot create an extra column on the table.

The only way to create an index on a different datatype version of a column is to create a computed column and index that, then make sure that all your queries use the new column and that all the data types in those queries match the computed column or the index won't be used

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sudhirnune SSCrazy Points: 2342 More actions · Answer 3

Hi Friend thanks,

But my issue is not with the Size of Index, but with the retrival Time.

Table wil be loaded with around 5,00,00,000 Records and will be Queried by one more job for providing extracts.

Please need help and suggessions.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

So with an index on the datetime column the query is still slow? If so, post the query at the very least.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass