3

I have a query in SQL Server 2008R2 that I want to run against a computed column that in turn I am going to index - in order to speed things up.

The column is of type int right now.

I cannot get the syntax right.

alter table dbo.tOutput Add StateNew as State IN (0, 10)

I get an error

Incorrect syntax near the keyword 'IN'.

The query, coming from C# entity framework, should then later on use the index and - well - be fast.

The C# seems to be converted to the IN usage, the original is like this:

tOutput.Where(x => (x.State == 0) || (x.State == 10))

The generated SQL reads

.. FROM tOutput WHERE State IN (0, 10) ..
Improve this question

1 Answer 1

Reset to default
6

I do not think that you want a computed column here. It looks like what you really need is a filtered index (introduced in SQL Server 2008), using WHERE [State] IN (0, 10) as the filter. Once this index is created, queries that have that same predicate should use this index. Please take a look at:

Just FYI: a computed column is an expression that needs to return a scalar value. State IN (0, 10) is not a scalar expression, which is why you are getting the error. You could modify it to be something like CONVERT(TINYINT, CASE [State] WHEN 0 THEN 1 WHEN 10 THEN 1 ELSE 0 END), but then indexing that will still include all rows. Using a filtered index will only include the matching rows, which will be smaller and faster :-).

Improve this answer
5
  • Isnt a bit/bool a scalar value? I think the filtered index should work though, too. Did not think about it, since it does not work on a computed column, but in my case, the column is not computed.
    – Andreas Reiff
    Commented Jan 9, 2018 at 18:18
  • 2
    @AndreasReiff SQL Server does not have boolean type so ther can be no scalar boolean values. If the expression was modified to yield an INT or a BIT, it would be accepted, i.e.: .. ADD StateNew AS CASE WHEN State IN (0, 10) THEN 1 ELSE 0 END
    – ypercubeᵀᴹ
    Commented Jan 9, 2018 at 18:30
  • 1
    @AndreasReiff A bit value is a scalar value, BUT State IN (0, 10) does not return a value; it evaluates to a boolean true / false, which is similar, but not the same. For example, you can do IF (State IN (0, 10))... but you cannot do SELECT ([State] IN (0, 10)) AS [YesOrNo] FROM .... This is why I have the note in the "Just FYI" section showing how to represent this as a scalar expression for use in a computed column. But again, the filtered index is better than a non-filtered index on a computed column.
    – Solomon Rutzky
    Commented Jan 9, 2018 at 18:43
  • You can do that in PostgreSQL. ;) SELECT x IN (1,2) FROM ( VALUES (1),(2),(4) ) AS t(x); Having a boolean type is awesome!
    – Evan Carroll
    Commented Jan 9, 2018 at 18:47
  • @EvanCarroll interesting...thanks for mentioning :-)
    – Solomon Rutzky
    Commented Jan 9, 2018 at 18:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.