Index Scans with Case statementsgreenspun.com : LUSENET : DBAzine : One Thread
MS SQL Server 2000 There is a Table called Customer which has a NON CLUSTERED index on customer code. I have a view on top of the CUSTOMER TABLE.Within the view there is a CASE statement which is as following
Create view x as ( Select Case when cust_cd ='-9' Then Null else cust_cd End Customer_Code From Customer)
When I filter on the customer code the explain plan shows INDEX SCAN on the table.If the above case statement is not there and it only has the column name it does INDEX SEEK.I have to do CASE for some Business reasons,Anyone who can tell how can we make it Index Seeks rather than Index Scans. The STATISTICS of IO is much lower in Index seek in this case so I want to utilise it.
-- Dheeraj (Khosla@townisp.com), June 21, 2004