logic explanation

Question

logic explanation

faroz2003

SSC Journeyman

Points: 92
More actions
March 16, 2012 at 6:58 am

#253294

Hi All,
Pls explain me what is the logic in the below table
select * from xyz where year(isnull(last_updated_on,created_on)) <2012
note :Last_updated_on, and "created_on" is a column in my db containing date value.
Pls advice
thanks in advance

Viewing 10 posts - 1 through 9 (of 9 total)

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

calvo SSChampion Points: 12930 More actions · Answer 1

ISNULL() is a function that returns a second value if the first is null. In your situation, if the last_updated_on field is null the created_on field will be used.

YEAR( ISNULL(<if this is null>,<use this>) )

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

This query selects all columns and the rows from the table xyz who satisfy the condition that the row was updated before the year 2012. If the row was never updated (last_updated_on is NULL), it must be inserted before the year 2012.

Basically you're selecting all records that were created or updated before the year 2012.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

As written, it also creates a performance problem because it will never use an INDEX SEEK. This type of predicate is frequently referred to as a "non-SARGable" predicate. "SARG" is short for "Search Argument". It's one of the reasons why I strongly recommend never using a NULL to indicate that a row hasn't been modified. You should use a date of 9999-12-30 instead. Yeah, I know that there are 31 days in December but there are some advanced search techniques that sometimes require that you look for things one day further out than the day you're actually looking for so you need that one day of space in 9999-12-30 to accomodate that.

--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)

CapnHector SSCoach Points: 16143 More actions · Answer 4

Jeff,

Depending on the application would using the created on date for the first modified date be better than some future date?

so in the above query it would be rewritten

select * from bla

where YEAR(date_modified) < 2012

would (I guess) solve the "SARG" problem and simplify the query.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 5

I don't think Year is SARGable. Better bet is "DateModified < '1/1/2012'".

SARGability may not matter much in this case, since it's liable to pull a range of data anyway, and scans are often more efficient for those anyway.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

faroz2003 SSC Journeyman Points: 92 More actions · Answer 6

Hi calvo,Koen Verbeeck,Jeff Moden ,capn.hector,GSquared ,

Thanks to all.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

capn.hector (3/16/2012)
Jeff,
Depending on the application would using the created on date for the first modified date be better than some future date?
so in the above query it would be rewritten
select * from bla
where YEAR(date_modified) < 2012
would (I guess) solve the "SARG" problem and simplify the query.

Yes, using the first created on date for the modified date is fine. The query above, though, is not. As G2 points out, WHERE YEAR(Date_Modified) is not SARGable either. You should use something like the following to find a full year, instead.

WHERE Date_Modified >= '2012'

AND Date_Modified < '2013'

With the correct indexing, it would do an INDEX SEEK to the first date followed by a very high speed range scan.

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Jeff Moden (3/16/2012)
capn.hector (3/16/2012)
Jeff,
Depending on the application would using the created on date for the first modified date be better than some future date?
so in the above query it would be rewritten
select * from bla
where YEAR(date_modified) < 2012
would (I guess) solve the "SARG" problem and simplify the query.
Yes, using the first created on date for the modified date is fine. The query above, though, is not. As G2 points out, WHERE YEAR(Date_Modified) is not SARGable either. You should use something like the following to find a full year, instead.
WHERE Date_Modified >= '2012'
AND Date_Modified < '2013'
With the correct indexing, it would do an INDEX SEEK to the first date followed by a very high speed range scan.

Wouldn't the WHERE clause look more like this for this year?

WHERE

Date_Modified >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AND

Date_Modified < DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Lynn Pettis (3/16/2012)
Jeff Moden (3/16/2012)
capn.hector (3/16/2012)
Jeff,
Depending on the application would using the created on date for the first modified date be better than some future date?
so in the above query it would be rewritten
select * from bla
where YEAR(date_modified) < 2012
would (I guess) solve the "SARG" problem and simplify the query.
Yes, using the first created on date for the modified date is fine. The query above, though, is not. As G2 points out, WHERE YEAR(Date_Modified) is not SARGable either. You should use something like the following to find a full year, instead.
WHERE Date_Modified >= '2012'
AND Date_Modified < '2013'
With the correct indexing, it would do an INDEX SEEK to the first date followed by a very high speed range scan.
Wouldn't the WHERE clause look more like this for this year?
WHERE
Date_Modified >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AND
Date_Modified < DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0)

BWAAA-HAAA!!! "It depends!"

--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)