In SQL Server 2012, I would like to run a query that checks, if an indexed view exists. If it does, I want to run a SELECT statement WITH(NOEXPAND). If it doesn't, I would like to run a select statement without expand. Below is the code I am using:
DECLARE @x int
IF NOT EXISTS (SELECT * FROM sys.indexes
WHERE object_id = OBJECT_ID('test'))
SET @x = 0
ELSE
SET @x = 1
IF(@x = 1)
SELECT category, _TimeStamp
FROM test WITH (NOEXPAND)
WHERE _TimeStamp >= '2018-01-24 00:00:00'
AND _TimeStamp < DATEADD(hh, +24, '2018-01-24 00:00:00')
ORDER BY _TimeStamp ASC
ELSE
SELECT category, _TimeStamp
FROM test
WHERE _TimeStamp >= '2018-01-24 00:00:00'
AND _TimeStamp < DATEADD(hh, +24, '2018-01-24 00:00:00')
ORDER BY _TimeStamp ASC
As of running this query, there isn't an indexed view in the database for this table and I get following error:
Msg 8171, Level 16, State 2, Line 13
Hint 'noexpand' on object 'test' is invalid.
I have another table that has an indexed view and if I run the same query on that table (with a little modifications such as table name), it runs just fine. I am wondering why does SQL Server complain about NOEXPAND when an indexed view does not exist, instead of running the statement in the Else part.
How else could I implement this?
EDIT: Changed the code to use exec() :
IF NOT EXISTS(
SELECT 1 FROM sys.indexes
WHERE object_id = OBJECT_ID('test'))
exec('SELECT category, _TimeStamp FROM test
WITH (NOEXPAND) Where _TimeStamp >= 2018-01-24 00:00:00 and _TimeStamp < DATEADD(hh, +24, 2018-01-24 00:00:00) ORDER BY _TimeStamp ASC')
Else
SELECT cartegory, _TimeStamp FROM test
WHERE _TimeStamp >= '2018-01-24 00:00:00' and _TimeStamp < DATEADD(hh, +24, '2018-01-24 00:00:00') ORDER BY _TimeStamp ASC
_TimeStamp has datetime format.
Following error message received:
Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '00'.
The query planner parses your entire query before it runs. It will flag noexpand on the view as invalid, even though it's in a branch of an if clause that never gets executed.
You can solve this with exec, which runs in a separate scope:
IF(@x = 1)
exec (N'SELECT category, _TimeStamp
FROM test WITH (NOEXPAND)
...')
else
SELECT category, _TimeStamp
FROM test
...