SQL Server If Else Indexed View

Question

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'.

Just a comment - for performance improvement change IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('test')) to IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('test')) - theres no need to select '*' where you are doing an exists check — Daniel Marcus, Apr 17 '18 at 19:04

score 0 · Accepted Answer · answered Apr 17 '18 at 18:51

0

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

answered Apr 17 '18 at 18:51

Andomar

232,371
49
380
404

Thank you! That helped. A follow-up question - I now have 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 ... in the else part of my query. SQL complains about the time now. Incorrect syntax near '00' – Dixie Apr 18 '18 at 21:12
Why not ask a new question instead of adding an unrelated question to an old question? At any rate, the date in `exec` is missing quotes. You can add quotes to a string by repeating them, i.e. two single quotes, like in `''2018-01-24 00:00:00''` – Andomar Apr 19 '18 at 05:45

SQL Server If Else Indexed View

1 Answers1