Tips for Golfing in T-SQL

Question

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

Answer 1

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.

Answer 2

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)

Answer 3

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

Answer 4

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.

Answer 5

Print instead of Select

It's as simple as that! So here's a T-SQL / Python polyglot:

print'Hello, World!'

Try it online

Answer 6

Scientific notation is a shorter method to express very large and very small numbers, e.g. select 1000000000 = select 1E9 and select 0.000001 = select 1E-6.

Answer 7

Michael B mentioned using a recursive CTE for a number table, but didn't show an example. Here is a MS-SQL version we worked out in this other thread:

--ungolfed
WITH t AS (
    SELECT 1 n 
    UNION ALL 
    SELECT n + 1
    FROM t 
    WHERE n < 99)
SELECT n FROM t

--golfed
WITH t AS(SELECT 1n UNION ALL SELECT n+1FROM t WHERE n<99)SELECT n FROM t

Note that you can change the starting value (1 n), the interval (n + 1) and the ending value (n < 99).

If you need more than 100 rows, though, you will need to add option (maxrecursion 0):

WITH t AS(SELECT 0n UNION ALL SELECT n+1FROM t WHERE n<9999)
SELECT n FROM t option(maxrecursion 0)

or join the rCTE to itself:

WITH t AS(SELECT 0n UNION ALL SELECT n+1FROM t WHERE n<99)
SELECT 100*z.n+t.n FROM t,t z

Although this last one isn't guaranteed to return in numeric order without an ORDER BY 1

Answer 8

Use GZIP compression for very long strings!

So I knew that SQL 2016 added a COMPRESS function (and a DECOMPRESS function), which (finally) brings the ability to GZIP a string or binary.

The problem is that it isn't immediately clear how to take advantage of this for golfing; COMPRESS can take a string but returns a VARBINARY, which is shorter in bytes (when stored in a SQL VARBINARY field), but is longer in characters (raw hex).

I've played with this before, but I finally was able to put together a working version, based on this old answer on SO. That post doesn't use the new GZIP functions, but it does convert a VARBINARY to a Base-64 encoded string. We just needed to insert the new functions into the right place, and golf it up a bit.

Here is the code you can use to convert your very long string to the Base-64 encoded compressed string:

DECLARE @s VARCHAR(MAX)='Your really long string goes right here'
SELECT CONVERT(VARCHAR(MAX),(SELECT CONVERT(VARBINARY(MAX),COMPRESS(@s))
       FOR XML PATH(''),BINARY BASE64))

Take the output, and use it in your code in place of the original long string, along with:

--To use your compressed string and return the original:
DECLARE @e VARCHAR(MAX)='H4sIAAAAAAAEAIvMLy1SKEpNzMmpVMjJz0tXKC4pygRS6fmpxQpFmekZJQoZqUWpAGGwW5YnAAAA'
SELECT CAST(DECOMPRESS(CAST(@e as XML).value('.','varbinary(max)'))AS varchar(max))

So instead of your original code (1471 bytes)

SELECT'Four score and seven years ago our fathers brought forth on this continent, a new nation, conceived in Liberty, and dedicated to the proposition that all men are created equal. Now we are engaged in a great civil war, testing whether that nation, or any nation so conceived and dedicated, can long endure. We are met on a great battle-field of that war. We have come to dedicate a portion of that field, as a final resting place for those who here gave their lives that that nation might live. It is altogether fitting and proper that we should do this. But, in a larger sense, we can not dedicate — we can not consecrate — we can not hallow — this ground. The brave men, living and dead, who struggled here, have consecrated it, far above our poor power to add or detract. The world will little note, nor long remember what we say here, but it can never forget what they did here. It is for us the living, rather, to be dedicated here to the unfinished work which they who fought here have thus far so nobly advanced. It is rather for us to be here dedicated to the great task remaining before us — that from these honored dead we take increased devotion to that cause for which they gave the last full measure of devotion — that we here highly resolve that these dead shall not have died in vain — that this nation, under God, shall have a new birth of freedom — and that government of the people, by the people, for the people, shall not perish from the earth.'

you would have this (1034 bytes):

SELECT CAST(DECOMPRESS(CAST('H4sIAAAAAAAEAGVUW47bMAy8Cg/g5hD9aLFA0a8C/aYt2hZWEVNJjpGT5LodinE2i/0JIouPmeFQP3QrVCctQpwDVblKpptwqcSLkt3O3FbBeSy6LWujWUtbSTO1NVaaNLeYJbeBmLLslLlFzYNdTBKvEihm+hVHKe029CZBQpy44aYpighdil60RsvDmRtxSnQGEAasqUiPlX8bpxP91p126TeSF168PtNiYTTFa0y0cxmoSQWwhfZVDL8XPsBpAZLb40hVX9B+QgganCkp6kgOW5ET/fXmZ2mmwdF45NaSfJujpEA6ezfg6PErX8FDz2KEj9pIvUBJ63/E92xoBO3xP3Oi8iBxSTyJKY9ArQJSSiAltFhp8IuFEuBXL/TClc7RhmaXJ3prhJFxarq4KHNsvb6RtikcOkHhuuoGLkH7nE/0fcOIu9SJy4LAKrnKYKGmUdb2Qe3++hXSVpnKl+8rpoxh3t1HC9yVw4n+wA9jMVYwwGC4D3xBGOIY89rKtiwJwzINhkPfow0cAagzY8aj4sZMfFG1n90IKnEIZoEgrfDUvOmuBXT3COulaMM0kCieEdgNUOQsZ9gYEB4K8e0BYNwgbHNm2KBik4LCHgmhbxSigz1mYKPcane/Uxyo9D0bDN8oL0vS5/zYlC3DF7Gu+Ay872gQp9U7mDCzb2jPWN0ZaGJKwOJZx3QD9SvD6uEA4l2feHrvnv9lS93ojeu7ScHAAVFGme3tQOr94eGiZwuHSVeFduKDM70avwscZAtd++er+sqrp068VTf5C63D4HBdRfWtvwxcsYq2Ns8a96dvnTxMD7JYH0093+dQxcFU897DhLgO0V+RK0gdlbopj+cCzoRGPxX+89Se5u/dGPtzOIO5SAD5e3drL7LAfiXDyM13HE+d6CWZY26fjr7ZH+cPgFhJzPspK+FpbuvpP9RXxXK3BQAA'as XML).value('.','varbinary(max)'))AS varchar(max))

See this answer which saved me nearly 200 bytes.

I haven't done the math, but clearly due to the overhead this is only going to be effective for extremely long strings. There are probably other places this can't be used; I've already discovered you have to SELECT it, you can't PRINT it, otherwise you get:

Xml data type methods are not allowed in expressions in this context.

EDIT: Shorter version of the decompress code, courtesy of @digscoop:

Save 10 bytes by changing the outer CAST to an implicit conversion using CONCAT:

SELECT CONCAT('',DECOMPRESS(CAST('encoded_string_here'as XML).value('.','varbinary(max)')))

You can also declare a variable of type XML instead of VARCHAR(MAX), and save on the inner CAST:

DECLARE @ XML='encoded_string_here'
SELECT CONCAT('',DECOMPRESS(@.value('.','varbinary(max)')))

This is slightly longer by itself, but if you need it in a variable for other reasons, then it might help.

Answer 9

A few thoughts about creating and using tables for challenges:

1. SQL input can be taken via a pre-existing table

Code Golf Input/Output Methods:

SQLs may take input from a named table

Creating and populating this table with input values doesn't count toward your byte total, you can just assume it is already there.

This means your calculations can output via simple SELECT from the input table:

SELECT 2*SQRT(a)FROM t

2. If possible, don't actually create a table at all

Instead of (69 bytes):

CREATE TABLE t(b INT)
INSERT t VALUES(7),(14),(21),(99)
SELECT b FROM t

Just do (43 bytes):

SELECT b FROM(VALUES(7),(14),(21),(99))t(b)

3. If possible, create the table with a SELECT INTO

Instead of (39 bytes):

CREATE TABLE t(p INT)
INSERT t VALUES(2)

Do this (17 bytes):

SELECT 2 p INTO t

4: Consider mashing multiple columns together

Here are two variations that return the same output:

SELECT a,b FROM
(VALUES('W','Bob'),('X','Sam'),('Y','Darla'),('Z','Elizabeth'))t(a,b)

SELECT LEFT(a,1),SUBSTRING(a,2,99)FROM
(VALUES('WBob'),('XSam'),('YDarla'),('ZElizabeth'))t(a)

After some testing, the top version (multiple columns) seems shorter with 7 or fewer rows, the bottom version (due to the LEFT and SUBSTRING) is shorter with 8 or more rows. Your mileage may vary, depending on your exact data.

5: Use REPLACE and EXEC for very long sequences of text

In the vein of comfortablydrei's excellent answer, if you have 15 or more values, use REPLACE on a symbol to get rid of the repeated '),(' separators between elements:

114 characters:

SELECT a FROM(VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H')
,('I'),('J'),('K'),('L'),('M'),('N'),('O'))t(a)

112 characters:

DECLARE @ CHAR(999)=REPLACE('SELECT a FROM(VALUES(''
 A-B-C-D-E-F-G-H-I-J-K-L-M-N-O''))t(a)','-','''),(''')EXEC(@)

If you're already using dynamic SQL for other reasons (or have multiple replaces), then the threshold where this is worth it is much lower.

6: Use a SELECT with named columns instead of a bunch of variables

Inspired by jmlt's excellent answer here, re-use strings via a SELECT:

SELECT a+b+a+b+d+b+b+a+a+d+a+c+a+c+d+c+c+a+a
FROM(SELECT'Hare 'a,'Krishna 'b,'Rama 'c,'
'd)t

returns

Hare Krishna Hare Krishna 
Krishna Krishna Hare Hare 
Hare Rama Hare Rama 
Rama Rama Hare Hare 

(For MS SQL I changed the \t to an in-line return, and changed CONCAT() to + to save bytes).

Answer 10

Tag your code for T-SQL syntax highlighting

Instead of just:

CREATE TABLE t(b INT)
INSERT t VALUES(7),(14),(21),(99)
SELECT b FROM t

Include a language tag like this:

<!-- language: lang-sql -->

    CREATE TABLE t(b INT)
    INSERT t VALUES(7),(14),(21),(99)
    SELECT b FROM t

and the result will be:

CREATE TABLE t(b INT)
INSERT t VALUES(7),(14),(21),(99)
SELECT b FROM t

Answer 11

Take advantage of new features/functions in MS SQL 2016 and SQL 2017

If you don't have local copies to work with, you can play online with the StackExchange Data Explorer (SQL 2016) or with dbfiddle.uk (SQL 2016 or SQL "vNext").

STRING_SPLIT (SQL 2016 and later)

SELECT *
FROM STRING_SPLIT('one,two,three,four,five',',')

If you need to alias the table or refer to the column name:

SELECT t.value
FROM STRING_SPLIT('one,two,three,four,five',',')t

TRIM (SQL 2017 or later)

Shorter than RTRIM() and certainly shorter than LTRIM(RTRIM()).

Also has an option to remove other characters or sets of characters from either the beginning or the end:

SELECT TRIM('sq,0' FROM 'SQL Server 2000')

returns L Server 2

TRANSLATE (SQL 2017 or later)

TRANSLATE allows you to replace multiple characters in one step, rather than a bunch of nested REPLACE statements. But don't celebrate too much, it only replaces individual single characters with different single characters.

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');

Each character in the second string is replaced by the corresponding character in the 3rd string.

Looks like we could eliminate a bunch of characters with something like REPLACE(TRANSLATE('source string','ABCD','XXXX'),'X','')

---

Some more interesting ones as well, like CONCAT_WS and STRING_AGG that are probably worth a look as well.

Answer 12

Holy cow, I have discovered the wonder of PARSENAME (SQL 2012 or higher).

The function was built to isolate the parts of an object name like servername.dbname.dbo.tablename, but it works for any dot-separated values. Just remember it counts from the right, not the left:

SELECT PARSENAME('a.b.c.d',1),      -- d
       PARSENAME('a.b.c.d',2),      -- c
       PARSENAME('a.b.c.d',3),      -- b
       PARSENAME('a.b.c.d',4)       -- a

If you have less than 4 dot-separated values, it will return NULL for the remainder (but it still counts right to left):

SELECT PARSENAME('a.b',1),      -- b
       PARSENAME('a.b',2),      -- a
       PARSENAME('a.b',3),      -- NULL
       PARSENAME('a.b',4)       -- NULL

Here's where the magic comes in, though: combine it with STRING_SPLIT (2016 or higher) to make in-memory multi-column tables!!

Old and busted:

SELECT a,b,c FROM
(VALUES('Bob','W','Smith'),
       ('Sam','X','Johnson'),
       ('Darla','Y','Anderson'),
       ('Elizabeth','Z','Turner'))t(a,b,c)

New hotness:

SELECT PARSENAME(value,3)a,PARSENAME(value,2)b,PARSENAME(value,1)c
FROM string_split('Bob.W.Smith-Sam.X.Johnson-Darla.Y.Anderson-Elizabeth.Z.Turner','-')

Clearly your actual savings depend on the size and contents of the table, and how exactly you are using it.

Note that if your fields are constant-width, you're probably better off using LEFT and RIGHT to separate them instead of PARSENAME (not only because the function names are shorter, but also because you can eliminate the separators entirely).

Answer 13

A couple more unrelated tricks I saw and wanted to preserve:

1. Use GO # to repeat a block a specific number of times.

Saw this clever trick on Paul's excellent answer.

PRINT'**********'
GO 10

This would, of course, reset any counter variables in the block, so you'd have to weigh this against a WHILE loop or a x: ... GOTO x loop.

2. SELECT TOP ... FROM systypes

From the same question as Paul's above, Anuj Tripathi used the following trick:

SELECT TOP 10 REPLICATE('*',10) FROM systypes

or, as suggested by pinkfloydx33 in the comments:

SELECT TOP 10'**********'FROM systypes

Note this doesn't rely on any of the actual contents of systypes, just that the system view exists (which it does in every MS SQL database), and contains at least 10 rows (it looks to contain 34, for most recent versions of SQL). I couldn't find any system views with shorter names (that didn't require a sys. prefix), so this may be ideal.

Answer 14

See this question on dba.stackexchange for some interesting ideas for adding a number column to a STRING_SPLIT result.

Given a string like 'one,two,three,four,five', we want to get something like:

value   n
------ ---
one     1
two     2
three   3
four    4
five    5

1. Per Joe Obbish's answer, use ROW_NUMBER() and order by NULL or a constant:

   SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT 1))n
   FROM STRING_SPLIT('one,two,three,four,five',',')
   

2. Per Paul White's answer, use a SEQUENCE:

   CREATE SEQUENCE s START WITH 1
   SELECT value, NEXT VALUE FOR s 
   FROM STRING_SPLIT('one,two,three,four,five', ',')
   

Sequences are interesting persistent objects; you can define the data type, the min and max value, the interval, and whether it wraps around to the beginning:

    CREATE SEQUENCE s TINYINT;     --Starts at 0
    CREATE SEQUENCE s MINVALUE 1;  --Shorter than START WITH
    SELECT NEXT VALUE FOR s        --Retrieves the next value from the sequence
    ALTER SEQUENCE s RESTART;      --Restarts a sequence to its original start value

3. Per Biju jose's answer, you can use the IDENTITY() function (which is not the same as the IDENTITY property in conjunction with an INSERT:

   SELECT value v,IDENTITY(INT) AS n
   INTO t
   FROM STRING_SPLIT('one,two,three,four,five',',')
   
   SELECT * FROM t
   

Note that the last two parameters in IDENTITY(INT,1,1) are optional, and will default to 1 if excluded.

Answer 15

Just discovered that you can use numerals for a single-character REPLACE to eliminate quotes:

--44 bytes
PRINT REPLACE('Baby Shark******','*',' doo')

--42 bytes
PRINT REPLACE('Baby Shark000000',0,' doo')

This is because REPLACE does an implicit conversion to string.

Both produce the same output:

Baby Shark doo doo doo doo doo doo

Answer 16

_ and # are valid aliases. I use them with CROSS APPLY to make it appear the columns it returns are part of the FROM clause e.g.

SELECT TOP 10 number, n2
FROM master.dbo.spt_values v
CROSS APPLY (SELECT number*2 n2) _

I like this when the only purpose of the CROSS APPLY is to compute an expression.

For that matter, using APPLY for computing sub-expressions is a neat way to make your code DRY-er (and shorter). From what I've seen in execution plans, there is no added cost to this approach. The compiler figures out you're just computing something and treats it like any other expression.