I ran your code against the Northwind database (assume that's where you were running it, as well)... Just to be sure, this is your code, isn't it (I added the ShowPlan)
SET SHOWPLAN_TEXT ON
go
DECLARE @CustomerID nchar(5)
SET @CustomerID = 'AROUT'
SELECT *
FROM Customers
WHERE (CustomerID = @CustomerID OR @CustomerID IS NULL)
SELECT *
FROM Customers
WHERE CustomerID = @CustomerID
When I run it, I get the same thing as when I show the graphical execution plan...
StmtText
------------------------------
SET STATISTICS PROFILE ON
(1 row(s) affected)
StmtText
-----------------------
SET SHOWPLAN_TEXT ON
(1 row(s) affected)
StmtText
--------------------------------------------------------------------------------------
DECLARE @CustomerID nchar(5)
SET @CustomerID = 'AROUT'
SELECT *
FROM Customers
WHERE (CustomerID = @CustomerID OR @CustomerID IS NULL)
(2 row(s) affected)
StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------
|--Clustered Index Scan(OBJECT
[Northwind].[dbo].[Customers].[PK_Customers]), WHERE[Customers].[CustomerID]=[@CustomerID] OR [@CustomerID]=NULL))
(1 row(s) affected)
StmtText
----------------------------------------------------------------
SELECT *
FROM Customers
WHERE CustomerID = @CustomerID
(1 row(s) affected)
StmtText
------------------------------------------------------------------------------------------------------------------------------------------------
|--Clustered Index Seek(OBJECT[Northwind].[dbo].[Customers].[PK_Customers]), SEEK[Customers].[CustomerID]=[@CustomerID]) ORDERED FORWARD)
(1 row(s) affected)
StmtText
-----------------------------
SET STATISTICS PROFILE OFF
(1 row(s) affected)
No city index... no table scan... I'm thinking that something is wrong with your Northwind Customers table... it must be missing an index or PK or something...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)