Tips for Golfing in T-SQL What general tips do you have for golfing in T-SQL? I'm looking for ideas that can be applied to code golf problems in general that are at least somewhat specific to T-SQL. Please post one tip per answer.

Thanks to Marcog for the original idea. :)
My general bag of tricks::


@ is a valid variable in t-sql.
T-sql 2012 added iif a VB style case statement. This is almost always shorter than an equivalent if else.
\ is a useful way to initialize a number as 0 in a money type. You can convert a value to to a float by adding e. e.g. 4e or \k which will set k to the value 0.00 money.
rCTE seem to be the best way to create a number table of less than a 100 entries. Even shorter than using spt_values. If you need more than a 100, cross join and add them.
+= and other compound operators were added in 2008. Use them it saves a few characters.
Literals are usually a good enough delimiter for aliasing purposes. You rarely need a space or a ;.
Use ANSI SQL joins if you need them. Select*from A,B where condition is shorter than select*from A join b on condition
If you can be assured that the your while loop will do the first iteration it's best to rewrite it as a do-while style goto loop.
STR() is the shortest function to turn an int into a string. If you are doing more than one conversion or may need to concat numerous different datatypes consider the concat function.  E.g. 'hello'+str(@) is shorter than concat('hello',@), but hello+str(@)+str(@a) is longer than concat('hello',@,@a)


For example
These two are semantically equivalent.

while @&lt;100begin/*code*/set @+=1 end
s:/*code*/set @+=1if @&lt;100goto s


You can use Values to create a table or subquery. This will only really be a benefit if you need a few constant rows.