LIKE with integers, in SQL

Question

Can I replace the = statement with the LIKE one for the integers ?

by eg. are the following the same thing:

select * from FOOS where FOOID like 2     // and  select * from FOOS where FOOID = 2 

I'd prefer to use LIKE instead of = because I could use % when I have no filter for FOOID...

SQL Server 2005.

EDIT 1 @Martin

Answer 1

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_values   SELECT * FROM #FOOS WHERE FOOID LIKE 2  SELECT * FROM #FOOS WHERE FOOID = 2  DROP 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_count FROM sys.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*/