LIKE with integers, in SQL

select * from FOOS where FOOID like 2

should be avoided as it will cause both sides to be implicitly cast as varchar and mean that an index cannot be used to satisfy the query.

CREATE  TABLE #FOOS(FOOID INT PRIMARY KEY,Filler CHAR(1000))INSERT INTO #FOOS(FOOID)SELECT DISTINCT number FROM master..spt_valuesSELECT * FROM #FOOS WHERE FOOID LIKE 2SELECT * FROM #FOOS WHERE FOOID = 2DROP TABLE #FOOS

Plans (notice the estimated costs)

Another way of seeing the difference in costs is to add SET STATISTICS IO ON

You see that the first version returns something like

Table '#FOOS__000000000015'. Scan count 1, logical reads 310

The second version returns

Table '#FOOS__000000000015'. Scan count 0, logical reads 2

This is beacuse the reads required for the seek on this index are proportional to the index depth whereas the reads required for the scan are proportional to the number of pages in the index. The bigger the table gets the larger the discrepancy between these 2 numbers will become. You can see both of these figures by running the following.

SELECT index_depth, page_countFROMsys.dm_db_index_physical_stats (2,object_id('tempdb..#FOOS'), DEFAULT,DEFAULT, DEFAULT)WHERE object_id = object_id('tempdb..#FOOS') /*In case it hasn't been created yet*/

Use a CASE statement to convert an input string to an integer. Convert the wildcard % to a NULL. This will give better performance than implicitly converting the entire int column to string.

CREATE PROCEDURE GetFoos(@fooIdOrWildcard varchar(100))ASBEGIN    DECLARE @fooId int    SET @fooId =        CASE            -- Case 1 - Wildcard             WHEN @fooIdOrWildcard = '%'                THEN NULL            -- Case 2 - Integer            WHEN LEN(@fooIdOrWildcard) BETWEEN 1 AND 9            AND @fooIdOrWildcard NOT LIKE '%[^0-9]%'                THEN CAST(@fooIdOrWildcard AS int)            -- Case 3 - Invalid input            ELSE 0        END    SELECT FooId, Name    FROM dbo.Foos    WHERE FooId BETWEEN COALESCE(@fooId, 1) AND COALESCE(@fooId, 2147483647)END

Yes, you can just use it:

SELECT  *FROM    FOOSWHERE   FOOID like 2   

or

SELECT  *FROM    FOOSWHERE   FOOID like '%'  

Integers will be implicitly converted into strings.

Note that neither of these condition is sargable, i. e. able to use an index on fooid. This will always result in a full table scan (or a full index scan on fooid).