July 29, 2010 at 6:08 am
Dear All,
I have a table Employees with the following structure. It has millions of records.
Employee_Id
Employee_Name
DOB
I need to find all the employees whose birthday is today. The query should use Index seek operation instead of scan.
Thanks,
Amit
July 29, 2010 at 6:41 am
DOB also has an Non clustered index on it.
July 29, 2010 at 6:58 am
Do you have any clusterindex on employee_id column?
July 29, 2010 at 6:58 am
Yes we have a clustered index on it.
July 29, 2010 at 11:02 am
I assume that "birthday" means just month and day, so my first thought is to add a computed column that just extracts the month and day in 'mmdd' format, then create a non-clustered index on that column. Something like this:
ALTER TABLE dbo.MyTable ADD
Birthday AS (right('0'+CONVERT([varchar],datepart(month,[DOB]),0),(2))+right('0'+CONVERT([varchar],datepart(day,[DOB]),0),(2)))
GO
CREATE NONCLUSTERED INDEX [IX_Birthday] ON [dbo].[ExtendedProfile]
(
[Birthday] ASC
) ON [PRIMARY]
GO
That should enable you to easily and quickly find those with birthday on, for example, '0729'.
Rob Schripsema
Propack, Inc.
July 29, 2010 at 11:14 am
Rob Schripsema (7/29/2010)
I assume that "birthday" means just month and day, so my first thought is to add a computed column that just extracts the month and day in 'mmdd' format, then create a non-clustered index on that column. Something like this:
ALTER TABLE dbo.MyTable ADDBirthday AS (right('0'+CONVERT([varchar],datepart(month,[DOB]),0),(2))+right('0'+CONVERT([varchar],datepart(day,[DOB]),0),(2)))
GO
CREATE NONCLUSTERED INDEX [IX_Birthday] ON [dbo].[ExtendedProfile]
(
[Birthday] ASC
) ON [PRIMARY]
GO
That should enable you to easily and quickly find those with birthday on, for example, '0729'.
It's highly unlikely that DOB would store hours/minutes/seconds so you really wouldn't need to go to this trouble. But just for fun let's say the field did store the exact time of birth. In that case, you would be better off having your calculated column as a datetime (storing dates as varchars in just a bad idea) and set it = DATEADD(dd,DATEDIFF(dd,0,[DOB]),0).
Now, the thing is ... you need to have the fields you want to return from your query involved in the index on the date field. Otherwise the optimizer has to do additional work to get the values (such as the name of the person) associated with the record it found by the date.
July 29, 2010 at 12:29 pm
It's highly unlikely that DOB would store hours/minutes/seconds so you really wouldn't need to go to this trouble. But just for fun let's say the field did store the exact time of birth. In that case, you would be better off having your calculated column as a datetime (storing dates as varchars in just a bad idea) and set it = DATEADD(dd,DATEDIFF(dd,0,[DOB]),0).
I'd agree, except we're not interested in BirthDate, but in BirthDay -- like May 15th, regardless of the year. In your solution, you're still including with the year. If you indexed on the full datetime field, how would you do a seek on just month and day?
I guess I didn't think of storing the birthday (mmdd) as storing a date. It's just a 4 character field that's readily indexed and seek-able.
Rob Schripsema
Propack, Inc.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply