2

So hi there.

In my company we do a lot of reporting which needs a lot of filters and such to create dynamic queries.

This leads to us often needing to make chunks of T-SQL which may or may not have any restrictions, and its been practice here for a long time to get around this by using: -

    WHERE 0=0
    --insert restrictions after this

The reason? That way your always able to just plop your and/or on to the restriction without worrying if its actually the first thing to have been added and whether or not you need to put the and/or.

Long and short of the question - just how is this performance wise? Bad practice or generally accepted way of doing this?

Baron von Connor
  • 23
  • 4
  • 2
    run the query, display the actual execution plan and you'll find out if `WHERE 0=0` affects performance. I personally think that SQL Query compiler will strip this where statement out – AcidJunkie Jun 19 '14 at 10:00
  • 1
    Explain "get around this"... in the context of this question please? I'm also unsure what problem you are trying to solve here... are you hitting query lag? Putting "WHERE 0=0" into production seems redundant as its not going to affect the final selection. – robnick Jun 19 '14 at 10:06
  • Simple, if you don't include the 0=0 you have to logically work out in the language before (usually C#) if its the first restriction that's been added. So including it you save code to determine this. So it wasn't so much a problem as a query to determine if its good practice or not and if not why not. – Baron von Connor Jun 19 '14 at 10:20
  • Take a representative statement and run it in SSMS, and include the query plan. Take out the 0 = 0 and run it again. Compare query plans. I'm pretty sure, like @AcidJunkie, that there'll be no difference. If there is, please let us know! – simon at rcl Jun 19 '14 at 10:43
  • I'd call it bad practice. The following code should do it and is not too much work IMO: list.length == 0 ? String.Empty : " WHERE " + String.Join(" AND ", list.ToArray())); – Andreas Jun 19 '14 at 13:37
  • In practice it makes little difference, it will slightly increase the parsetime of your statements, but the query optimizer can discover that the statement is a tautology. Building queries that are just stringed along like this are in general a bad idea, but if you're concern is conditional filtering have you considered using the OPTION(RECOMPILE) and using nullable query parameters? – Michael B Jun 19 '14 at 14:08

1 Answers1

2

It does not have any performance hit. Detailed answers on similar question here T-SQL 1=1 Performance Hit and here Whats the exact meaning of having a condition like where 0=0?

Community
  • 1
  • 1
DiDi
  • 110
  • 6