I do not beleive the author says the solution is clever. It is just offered as a solution to a common problem. Personally, I have used your technique as it works well for our environment. In some circumstances, we have used Dynamic SQL (Prior to learning this technique).

Overall, the article examples should be simplified for beginners to follow. The technique is valid however.

Be careful with this one.

we have tables with 6 million rows and doing

WHERE (CustomerName = @CustomerName OR @CustomerName IS NULL)
killed perfomance. The query took more than 2 minutes.

User dynamic SQL with EXEC or SP_EXECUTESQL.
watch out for single quotes with EXEC, to prevent SQL Injection and to deal with certain last names.
O'Malley in SQL is 'O''Malley'

IF @CustomerName IS NOT NULL
BEGIN
    SET @sql = @sql + N' AND CustomerName = ''' + REPLACE(@CustomerName,'''','''''') + ''''
END

I've found that that is NOT true... not with INT ID's anyway... In my testing of the various solutions posted against this article, I get INDEX SCAN's, not table scans using WHERE (CustID = @CustID OR @CustID IS NULL).

In fact, the method advertised is the second fastest method (direct and dynamic SQL tied for first fastest) of those posted and some not posted.  Considering the flexibility of the code and the fact that you don't have to juggle the dynamic SQL code to make it "injection proof", it's not a bad method.

Still, I do like the dynamic SQL because of the blazing speed even if I do have to add a little code to keep Gumby and his brothers out...

how is this technique all that clever? If anything, using select * from Customers where customerid=isnull(@customerid,customerId) is more efficient, easier to read and takes up less space.

More efficient?  Like a previous respondent stated, you may want to test the code before making those type of statements.  The "efficient" method you spoke of forces a table scan resulting in a 43 second time of return.  The method Alex wrote about (colname=@variable OR @variable IS NULL) only took 453 MILLI-seconds.  That's about 94 times faster give or take a clock cycle.

Here's the code I used on the lowly 1.4 million record Customer table on the Test Server at work... some of the print statements also serve as the documentation for what I found for performance on a quiet server...

DECLARE @StartTime DATETIME
DECLARE @CustID INT
SET @CustID = 1650

  PRINT 'BASELINE... simple direct lookup (0.016 seconds - Index Seek)'
   DBCC DROPCLEANBUFFERS
   DBCC FREEPROCCACHE
    SET @StartTime = GETDATE()
 SELECT *
   FROM dbo.Customer WITH (NOLOCK)
  WHERE CustID = @CustID
  PRINT DATEDIFF (ms,@StartTime,GETDATE())
  PRINT REPLICATE('-',78)

  PRINT 'Using the technique from the article (0.453 seconds - Index Scan)'
   DBCC DROPCLEANBUFFERS
   DBCC FREEPROCCACHE
    SET @StartTime = GETDATE()
 SELECT *
   FROM dbo.Customer WITH (NOLOCK)
  WHERE (CustID = @CustID OR @CustID IS NULL)
  PRINT DATEDIFF (ms,@StartTime,GETDATE())
  PRINT REPLICATE('-',78)

  PRINT 'Using dynamic sql (extendable) (0.016 seconds - Index Seek)'
   DBCC DROPCLEANBUFFERS
   DBCC FREEPROCCACHE
DECLARE @SQL VARCHAR(8000)
    SET @StartTime = GETDATE()
    SET @SQL = '
                SELECT *
                FROM dbo.Customer WITH (NOLOCK)
                WHERE 1=1' + ISNULL(' AND CustID = '+STR(@CUSTID),'')
   EXEC (@SQL)
  PRINT DATEDIFF (ms,@StartTime,GETDATE())
  PRINT REPLICATE('-',78)

  PRINT 'Using ISNULL (one of the alternatives offered) (43.110 seconds - Table Scan)'
   DBCC DROPCLEANBUFFERS
   DBCC FREEPROCCACHE
    SET @StartTime = GETDATE()
 SELECT *
   FROM dbo.Customer WITH (NOLOCK)
  WHERE CustID = ISNULL(@CustID,CustID) --REAL SLOW!!! TABLE SCAN!!!
  PRINT DATEDIFF (ms,@StartTime,GETDATE())
  PRINT REPLICATE('-',78)

  PRINT 'Using semi-static method (42.703 seconds - Table Scan)'
   DBCC DROPCLEANBUFFERS
   DBCC FREEPROCCACHE
DECLARE @IntMin INT
DECLARE @IntMax INT
    SET @IntMin = -2147483648
    SET @IntMax =  2147483647
    SET @StartTime = GETDATE()
 SELECT *
   FROM dbo.Customer WITH (NOLOCK)
  WHERE CustID BETWEEN ISNULL(@CustID,@IntMin) AND ISNULL(@CustID,@IntMax)
  PRINT DATEDIFF (ms,@StartTime,GETDATE())
  PRINT REPLICATE('-',78)

  PRINT 'Using CASE method (42.610 seconds - Table Scan)'
   DBCC DROPCLEANBUFFERS
   DBCC FREEPROCCACHE
    SET @StartTime = GETDATE()
 SELECT *
   FROM dbo.Customer WITH (NOLOCK)
  WHERE CustID = CASE WHEN @CustID IS NULL THEN CustID ELSE @CustID END
  PRINT DATEDIFF (ms,@StartTime,GETDATE())
  PRINT REPLICATE('-',78)

Alex,

Nice article especially for the proverbial "newbie".  I do agree that the examples were a bit overdone but how are ya going to learn if you don't try?

Note that it has nothing to do with the fact that it's an integer ID btw. Because we're looking for CustomerID, which is the clustered index, and every non-clustered index contains the clustered index column(s) as the last column(s), SQL Server will scan the smallest index, in this case the City index. If CustomerID was a varchar, the execution plan would be just the same.

City Index?  Neither the code you posted or my code would even come near a "city" index if the Primary Key was the CustomerID column.   Certainly, it wouldn't do a table scan if an index were present on CustomerID... Index Scan, yes... table scan, no.

The only reason I made the disclaimer about INT was because I didn't test it using VARCHAR.

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

Fyi, this is my execution plan for the smart filter query:

StmtText                                                                                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------
  |--Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT[Northwind].[dbo].[Customers]))
       |--Index Scan(OBJECT[Northwind].[dbo].[Customers].[City]),  WHERE[Customers].[CustomerID]=[@CustomerID] OR [@CustomerID]=NULL))

My Northwind database is unaltered (I never used it until today). I updated the statistics (UPDATE STATISTICS customers WITH FULLSCAN) but I get the same query plan. I'm running SQL 2000 SP3a btw.

There's not enough data in northwind as it is to properly test this and you must test this like the author suggested using multiple ( optional ) parameters - testing with one where clause does not fully illustrate the issues, the idea of using this approach, quite correctly and I agree with Alex's solution, is that you wish to typically deal with many parameters , please choose at least 6 for testing, which may or may not be passed thus making multiple procs ( one to match each combination ) or multiple if statements unworkable. Then see how the performance degrades with respect to data size and how difficult it is to index. And don't always just have one matching result per where clause - real life often isn't like that.

btw - good one Alex you've sparked an interesting thread!