Take the 2-minute tour ×
Programming Puzzles & Code Golf Stack Exchange is a question and answer site for programming puzzle enthusiasts and code golfers. It's 100% free, no registration required.

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. :)

share|improve this question

4 Answers 4

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 @<100begin/*code*/set @+=1 end
s:/*code*/set @+=1if @<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.

share|improve this answer

Here's a funny one. This will convert the values in a column into a single tuple.

EDIT: Thank you for the comments. It seems like the shortest way of rolling up without the XML tags is:

SELECT (SELECT column1+''
FROM table
ORDER BY column1
FOR XML PATH(''))

Note: if XML is a valid output you can omit the outer select and parens. Also the column1+'', only works for strings. For number types it's best to do column1+0

share|improve this answer
1  
Actually it will return <column_name>value1</column_name><column_name>value2</column_name>.... In order to have a CSV from a column you can DECLARE @ VARCHAR(MAX)='';SELECT @+=column_name+',' FROM table_name;SELECT @ (thanks for @MichaelB's first tip) which will return value1,value2,... . However, it is actually 9 characters longer than your XML trick :( –  Jacob Jul 2 at 4:48
1  
Note you can make this shorter. Ltrim isn't necessary as select (select... for xml path('')) returns an nvarchar(max). Also, to solve the column thing just use a non-mutating expression. For numerics you can do v+0, for string add empty string etc. Although I don't really consider this a golfing tip, this is just sadly a reality of how to write queries in sql server. –  Michael B Jul 2 at 5:01

It is possible to use some bitwise operators in T-SQL.

I don't have a concrete example, but I believe it is a good-to-know fact when golfing in T-SQL.

share|improve this answer
    
This is very valid. Instead of writing a condition like x=0 or y=0, you can write that as the logically equivalent x|y=0 which saves quite a few bytes! –  Michael B Jul 2 at 22:33

Code compression using SQL

SQL is wordy,scores high, and as much as we love'em, SELECT FROM WHERE cost 23 bytes with every use. You can compress these and other repeated words or entire code snippets. Doing this will decrease the marginal cost of repeated code to 1 byte!*

How this works:

  • A variable is declared and assigned compressed SQL code
  • A table modifies the variable. Each row deflates the variable.
  • The modified variable is executed.

The problem:

The upfront cost is close to 100 bytes and each row in the replacement table costs another 6 bytes. This kind of logic won't be very effective unless you're working with a lot of code which you can't trim down or the challenge is compression-based.

Here's an example

The challenge is to get the last 10 multiples of 2,3, and 5 leading up to n. Let's say this (343 bytes golfed) is the best solution I could come up with:

WITH x AS(
    SELECT 99 n
UNION ALL 
    SELECT n-1
    FROM x
    WHERE n>1
)
SELECT w.n,t.n,f.n
FROM
    (SELECT n, ROW_NUMBER()OVER(ORDER BY n DESC)r
     FROM x WHERE n%2=0
    )w
,
    (SELECT n, ROW_NUMBER()OVER(ORDER BY n DESC)r
     FROM x WHERE n%3=0
    )t
,   (SELECT n, ROW_NUMBER()OVER(ORDER BY n DESC)r
     FROM x WHERE n%5=0
    )f
WHERE w.r=t.r AND w.r=f.r AND w.r<11
ORDER BY 1

Example after code is compressed

This executes the same code as above, is ~302 bytes golfed.

DECLARE @a CHAR(999)='
WITH x AS(!99n UNION ALL !n-1 @x#n>1)
!w.n,t.n,f.n@$2=0)w,$3=0)t,$5=0)f
#w.r=t.r AND w.r=f.r AND w.r<11^1'

SELECT @a=REPLACE(@a,LEFT(i,1),SUBSTRING(i,2,99))
FROM(VALUES
  ('$(!n,ROW_NUMBER()OVER(^n DESC)r@x#n%'),
  ('! SELECT '),
  ('@ FROM '),
  ('# WHERE '),
  ('^ ORDER BY ')
)x(i)

EXEC(@a)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.