The point made in a post above about using a scalar-valued function in the WHERE clause resulting in a full table scan is incorrect. When the function has no parameters or none of the parameters passed into the function come from columns in the table then SQL Server can evaluate the function once and use an index seek (assuming there is an appropriate index).

In the example below, the execution plans for queries in options 1 and 2 are the same, utilizing a clustered index seek. Option 3 (while admittedly silly) passes in a column from the table as a parameter to the function and thus requires a full table scan.

CREATE TABLE #Letter

(

Letter char(1) PRIMARY KEY NOT NULL,

Number int NOT NULL

)

INSERT #Letter

VALUES ('A', 1)

INSERT #Letter

VALUES ('B', 2)

INSERT #Letter

VALUES ('C', 3)

CREATE FUNCTION dbo.GetFirstLetter(@text varchar(100)) RETURNS CHAR(1)

AS

BEGIN

RETURN LEFT(@text, 1)

END

-- Option 1

DECLARE @firstLetter char(1)

SET @firstLetter = dbo.GetFirstLetter('Apple')

SELECT * FROM #Letter WHERE Letter = @firstLetter

-- Option 2

SELECT * FROM #Letter WHERE Letter = dbo.GetFirstLetter('Apple')

-- Option 3

SELECT * FROM #Letter WHERE dbo.GetFirstLetter(Letter) = 'A'