How would you pull Name, DOB, and Mobile Contact number from a table to generate a list of birthdays for today's date?

Question

I am at the very beginnings of my IT and Database experience, fortunately landed an IT Coordinator job. I am tasked with the responsibility of pulling contacts to run through our text manager service. I'll need to run birthdays according to the current date. Pull the list from the Contacts table which should contain all the necessary info needed. Full Name, DOB, and Mobile number. Using SQL Server.

Not entirely sure how the query would look, wondering how you guys would run this. I figured it would go along the lines of selecting those columns from the table in regard to today's (or whenever it is run) date. Those that have birthdays today would get a birthday text sent out to them.

Would this be a viable query? I know this could be entirely wrong. Any criticism and help is much appreciated! Thank you in advance.

SELECT InformalName, LastName, Birthdate, MobilePhone from Contact
WHERE DAY(BirthDate) = DAY(GETDATE())
    AND MONTH(BirthDate) = MONTH(GETDATE())
    AND MobilePhone like '(%'

This seems to pull all the necessary information I will need. Just looking to see if there is room for improvement, or if this should suffice. This query is strictly to pull birthdays for the current date to send out "Happy Birthday!" messages through our text message service.

Answer 1

Someday

Your query will work for the most part, but since you're just starting out, lets build some good habits.

The way you've written this query isn't SARGable, which means that even with a perfectly good index in place it won't be able to seek to values you're trying to locate.

Your Contacts table might be very small, so it may not matter much right now, but this is as good an opportunity as any to get you on the right path.

Let's say we create this index:

CREATE INDEX dobalina
    ON dbo.Contacts
(
    DOB
)
INCLUDE
(
    FullName,
    MobileNum
);

And then we run the query as you've written it. The query plan looks like so:

In my mocked up data, we need to read ~6 million rows in order to retrieve ~1 million of them.

To make this query faster and more efficient, we can pre-compute the day and month values for each DOB, and index those.

ALTER TABLE dbo.Contacts
    ADD BirthDay AS
            DATEPART(DAY, DOB),
        BirthMonth AS
            DATEPART(MONTH, DOB);

CREATE INDEX birfday
    ON dbo.Contacts
(
    BirthDay,
    BirthMonth
)
INCLUDE
(
    FullName,
    DOB,
    MobileNum
);

Without changing the query -- SQL Server is smart enough to match the expressions used in the query to the ones used in the computed columns -- we get a much friendlier query plan:

We save about ~800 milliseconds of execution time (not a huge deal in this case, admittedly), and we are able to seek exactly to the ~1 million values that we care about, rather than scanning the entire index to locate them.