Which selection criteria is more efficient.

Question

Which selection criteria is more efficient.

eichnerm

Old Hand

Points: 359
More actions
December 8, 2024 at 5:44 pm

#4495058

Is it more efficient to search on an int than a nullable DateTime?
I have a table where I can choose to search on an int field in the where clause or a nullable datetime field looking for nulls in the where clause. Both methods return the same resultset.
While I am most likely nitpicking, I was wondering if its more efficient?
Thanks
December 10, 2024 at 8:49 am

This was removed by the editor as SPAM
December 11, 2024 at 3:49 pm

This was removed by the editor as SPAM

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

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

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 1

Just do a test.

SET STATISTICS TIME,IO ON:
Put code to be tested here.
SET STATISTICS TIME,IO OFF:

Do NOT use this method if any functions that contain the word BEGIN in them. See the following article for an explanation as to why not. For sure, the title is a play on words as to what the real problem is... and that's appropriate for this thread.

https://www.sqlservercentral.com/articles/how-to-make-scalar-udfs-run-faster-sql-spackle

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

eichnerm Old Hand Points: 359 More actions · Answer 2

eichnerm

Old Hand

Points: 359

December 8, 2024 at 9:35 pm

#4495114

Thanks. I will do what you suggested.

ScottPletcher SSC Guru Points: 101115 More actions · Answer 3

It depends. You'd need to look at the available indexes and whether SQL uses them for the query. So, look at the query plan and see what it "tells" you. If they both scan the full table / clustered index, then neither will be more efficient, they will be the same.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

eichnerm Old Hand Points: 359 More actions · Answer 4

There are no indexes on either field. I am going to create a query plan to see the results. '

I originally asked the question because I have a vague recollection of being told by an instructor from a seminar that I took several years ago where the instructor said that doing an is null on a query is very fast compared to checking the value of a field because the database engineer had to do less work.

Thanks