I have a query,SELECT TOP (@BatchCount)column1,column2, column3 FROM [table] WITH (NOLOCK) WHERE[column1] > @var OR@var is Null ORDER BY column1; If i write it as IF exists (@var )SELECT TOP (@BatchCount)column1,column2, column3 FROM [table] WITH (NOLOCK) WHERE[column1] > @var ORDER BY column1; ELSE SELECT TOP (@BatchCount) column1 ,column2, column3 FROM [table] WITH (NOLOCK) ORDER BY column1; Which will be better, there will be lakhs of records. I am using sql server 2008
If you want to use @var to pass a query statement, then please note that your IF EXISTS (@var) syntax will always give you a runtime error. The complete statements have to be executed as a single dynamic SQL statement to make that work. In these types of cases, if there is a performance problem with this type of criteria, we generally suggest to use dynamic SQL to compile the statement with criteria if the parameter is given, or without criteria if the parameter is null.
Additionally surf over to http://www.sommarskog.se and read Erland's article on Dynamic Search Conditions.
The query is working any way correctly. It is only a aprt of a big query. @var will either contain null or will contain a sequential guid. I just want to have a performance based analysis
Hi Shobin Mathew, As per your subject line I can go with "OR" rather than using "If else" keyword. Thanks, Sandy.