15
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ a tip - use a different language for golfing. Sql answers usually gets very few or no upvotes at all. \$\endgroup\$ – t-clausen.dk Apr 7 at 16:17

15 Answers 15

16
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ For me, $ is a bit more obvious than \ to initialize a number as 0 in a money type. YMMV \$\endgroup\$ – user1443098 Nov 21 '18 at 14:04
5
\$\begingroup\$

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)
\$\endgroup\$
  • \$\begingroup\$ Great strategy, that multi-replace style can be useful in more conventional scenarios as well. \$\endgroup\$ – BradC Jun 14 '17 at 13:27
  • 1
    \$\begingroup\$ After some testing, I've determined that if your list of replacements has 7 or fewer items, you'd save bytes by doing SELECT @=REPLACE(@,i,j)FROM(VALUES(...)x(i,j) instead of using a single column with LEFT() and SUBSTRING(). If you have 8 or more, then avoiding the extra quotes and commas is a good tradeoff. \$\endgroup\$ – BradC Jun 14 '17 at 19:45
  • \$\begingroup\$ Actually for 4 or fewer replacements, you'd save bytes with an old-fashioned SET @=REPLACE(REPLACE(REPLACE(... \$\endgroup\$ – BradC Jun 14 '17 at 19:52
4
\$\begingroup\$

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

\$\endgroup\$
  • 1
    \$\begingroup\$ 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 :( \$\endgroup\$ – Jacob Jul 2 '14 at 4:48
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Michael B Jul 2 '14 at 5:01
3
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ 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! \$\endgroup\$ – Michael B Jul 2 '14 at 22:33
3
\$\begingroup\$

Print instead of Select

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

print'Hello, World!'

Try it online

\$\endgroup\$
3
\$\begingroup\$

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.

\$\endgroup\$
2
\$\begingroup\$

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

\$\endgroup\$
1
\$\begingroup\$

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

\$\endgroup\$
1
\$\begingroup\$

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
\$\endgroup\$
1
\$\begingroup\$

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.

\$\endgroup\$
1
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ I am not sure when PARSENAME came out, but there are articles describing it from 2003 \$\endgroup\$ – t-clausen.dk Mar 30 at 6:37
1
\$\begingroup\$

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.

  1. 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.

\$\endgroup\$
1
\$\begingroup\$

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
  1. 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.

\$\endgroup\$
  • \$\begingroup\$ the problem is that STRING_SPLIT doesn't guarantee any return order. You might think it will always return the rowset in the order of the tokens in the original string. Indeed it may even do that! However, there is no guarantee in the docs. that's fine if you don't care about the order. But if you do (e.g. parsing a row in CSV format), there's a problem. \$\endgroup\$ – user1443098 Nov 21 '18 at 14:17
  • 1
    \$\begingroup\$ @user1443098 I ultimately agree with you in the context of recommending code for a business purpose, like we might see on dba.SE. But for challenges on PPCG my standards are a bit different; if in testing my code returns rows in the order I want then I'll save the bytes where I can. Similar to how I'll leave out an ORDER BY if I can get away with it (see my answer to Toasty, Burnt, Brulee, for example). \$\endgroup\$ – BradC Nov 21 '18 at 15:28
1
\$\begingroup\$

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
\$\endgroup\$
0
\$\begingroup\$

_ 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.

\$\endgroup\$
  • \$\begingroup\$ I find cross apply to long, it is really hard to find a useful situation using cross apply without finding another shorter method \$\endgroup\$ – t-clausen.dk Apr 4 at 21:19
  • \$\begingroup\$ OK -- shorten the example given above! \$\endgroup\$ – user1443098 Apr 5 at 13:54
  • \$\begingroup\$ SELECT TOP 10 number, number*2 n2 FROM master.dbo.spt_values v \$\endgroup\$ – t-clausen.dk Apr 5 at 16:39
  • \$\begingroup\$ I mean, keeping the join. By the way, once you build xml queries, CROSS APPLY can become the only way to do it, since there may be no columns in a subquery do do a join on. \$\endgroup\$ – user1443098 Apr 5 at 19:12
  • \$\begingroup\$ Subselect is shorter than cross apply: SELECT top 10* FROM(SELECT number n, number*2n2 FROM master..spt_values)x \$\endgroup\$ – t-clausen.dk Apr 5 at 20:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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