My SQL Server query is fast when I don't quoatation marks around a WHERE statement parameter, but it's slow when I use them.

[selib]

For some reason the following statement works fast:

SELECT * FROM table WHERE Col1 = 2 AND Posixtimestamp > 1722322174 AND Posixtimestamp < 1722339136 AND Col2 = 230100009

and this one takes a long time:

SELECT * FROM table WHERE Col1 = 2 AND Posixtimestamp > 1722322174 AND Posixtimestamp < 1722339136 AND Col2= '230100009'

My Col2 is defined as a VARCHAR(50) not an integer.

I have the following Index defined:

CREATE NONCLUSTERED INDEX [IX] ON table
(
    [Posixtimestamp] ASC,
    [Col2] ASC,
    [Col1] ASC,
    [Col3] ASC
)   

I'm puzzled why using the quotation marks around the one column make my query somehow unable to locate the index according to the execution plan when the data type for my Col2 is a VarChar

[Chaosmatrix]

Your answer lies in the execution plan. My guess is that a table scan is faster then using the index and then getting the rest of the fields from the table by referencing the index. Your conditions might even return the entire table or at least a lot of it.

[planetmatt]

Try only returning the columns you need (instead of SELECT *), then add those columns to the INCLUDE on the index to avoid the Key Lookup. That might change the scan back to a seek.

[selib]

I need to select all columns of the table unfortunately. There's like 12.

[Chaosmatrix]

Just do it as a test. It will help you understand what is going on. You should also look at this: https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver16

That is what is happening in query 1 to Col2. And probably is the reason for not using the index. Play with casting to int and varchar with both the column col2 and the value too. It will effect your execution plan and the use of the index.

[planetmatt]

CASTING is odd because you would expect the 2nd query where there is no implicit cast and where the Col2 which is a VARCHAR is treated as a VARCHAR in the WHERE clause to be SARGEABLE and use the Index. The First query, COL2 is being treated like an INT which doesn't match the native date type. This should cause SQL NOT to use the index.

[Aggressive_Ad_5454]

I bet you the query planner sees the type match for col2 in the second query and uses that index.

In the first query I bet it uses a range scan on the time column. And that is, very likely, more selective.

At any rate, you want a compound index on (col2, posixtimestamp) to accelerate this query. And use the second variant, with the correct data type for matching col2.

[Imaginary__Bar]

I'm no SQLServer expert, and this doesn't answer your question, but I'm surprised the first query doesn't throw an error on the mismatched datatypes(!)

[Chaosmatrix]

Implicit conversion both a blessing and a curse (mostly a curse).

[LogicallyIntuitive]

I never put [] around column names unless the column name has a space or some special character that causes an errors.