>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?

I don't see a difference between the two except the typos in CAST version. In what version does it work incorrect and what is that unexpected behavior? I get the same expected results from both in SQL2005.

Cetin