
create database test2 
go

use test2
go

create table Candidat (
 ca_code char(13) primary key,
 ca_name varchar(50)
) 
go

CREATE FUNCTION dbo.AddLeadingZeros
(
@textString NVARCHAR(100) ,@columnLEN int
)
RETURNS NVARCHAR(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultValue NVARCHAR(100)
,@i as INT
,@findLen as INT
-- Add the T-SQL statements to compute the return value here
SET @i = 1
SELECT @findLen = @columnLEN - LEN(@textString)
WHILE @i <= @findLen
BEGIN
SET @textString = '0' + @textString
SET @i = @i + 1
END
SET @ResultValue = @textstring
-- Return the result of the function
RETURN RTRIM(@ResultValue)
END
GO

declare @i int = 1
while (@i <= 10000)
begin
insert into Candidat values(dbo.AddLeadingZeros(CONVERT(char(11), @i), 11), 'fred') 
set @i += 1
end 

truncate table Candidat

-- clustered index seek
select COUNT(*) from Candidat
 where ca_code = '00000009701'

-- clustered index scan 
EXECUTE sp_executesql N'select count(*) from Candidat where ca_Code = @ca_Code',
N'@ca_Code nvarchar(11)',
@ca_Code = '00000009701' 


-- Msg 8622, Level 16, State 1, Line 1
-- Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
EXECUTE sp_executesql N'select count(*) from Candidat WITH (FORCESEEK) where ca_Code = @ca_Code',
N'@ca_Code nvarchar(11)',
@ca_Code = '00000009701' 

-- scan
EXECUTE sp_executesql N'select count(*) from Candidat WITH (INDEX=0) where ca_Code = @ca_Code',
N'@ca_Code nvarchar(11)',
@ca_Code = '00000009701' 

-- msg 8622
EXECUTE sp_executesql N'select count(*) from Candidat where ca_Code = @ca_Code 
                        OPTION (TABLE HINT (Candidat, FORCESEEK ))',
N'@ca_Code nvarchar(11)',
@ca_Code = '00000009701' 
