Hi everybody,

We found an interesting and not intuitive behavior of the CAST function.

The following sample code produces an error:

CREATE DATABASE Test
GO
USE Test
GO
Create TABLE Test1 (Fld1 int, Fld2 varchar(200)
CONSTRAINT PK_Tets1
    PRIMARY KEY CLUSTERED (Fld1)
)

Create TABLE Test2 (Fld1 int, Fld2 int, Fld3 int, Fld4 varchar(200))
CREATE INDEX IX_Test2 ON Test2 (Fld1, Fld2, Fld3)
GO

declare @counter int
set @counter = 1
while @counter < 100000 
  begin
	  insert into Test1 values (@counter, 'Counter' + cast(@counter as char(10))

	  insert into Test2 values (@counter, @counter, @counter,'Counter' + cast(@counter as varchar(10))

	  set @counter = @counter +1
end

Using Convert function works as expected:

declare @counter int
set @counter = 1
while @counter < 100000 
  begin
	  insert into Test1 values (@counter, 'Counter ' + convert(varchar(10),@counter))
	  insert into Test2 values (@counter, @counter, @counter,'Counter' + convert(varchar(10),@counter))
	  set @counter = @counter +1
  end

So, it looks like the CAST() function actually changes the type of the variable.

BTW, what SQL Server function would tell us the TYPE of the variable/field?