[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]
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]
**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]
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]
It is possible to use some [**bitwise** operators in T-SQL](http://msdn.microsoft.com/en-us/library/ms176122.aspx).
I don't have a concrete example, but I believe it is a good-to-know fact when golfing in T-SQL.
[Answer]
## Print instead of Select
It's as simple as that! So here's a T-SQL / Python polyglot:
```
print'Hello, World!'
```
[**Try it online**](https://data.stackexchange.com/codegolf/query/666656/hello-world)
[Answer]
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]
# Use GZIP compression for very long strings!
So I knew that SQL 2016 added a [`COMPRESS`](https://docs.microsoft.com/en-us/sql/t-sql/functions/compress-transact-sql?view=sql-server-2017) function (and a [`DECOMPRESS`](https://docs.microsoft.com/en-us/sql/t-sql/functions/decompress-transact-sql?view=sql-server-2017) 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](https://stackoverflow.com/a/35559661/21398). 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](https://codegolf.stackexchange.com/a/190643/70172) 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](https://codegolf.stackexchange.com/questions/192715/output-a-super-mario-image#comment459401_192718):
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]
[Michael B mentioned using a recursive CTE for a number table](https://codegolf.stackexchange.com/a/32797/70172), but didn't show an example. Here is a MS-SQL version we [worked out in this other thread](https://codegolf.stackexchange.com/questions/129211/sql-select-number-ranges/129215#129215):
```
--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]
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](https://codegolf.meta.stackexchange.com/questions/2447/default-for-code-golf-input-output-methods/5341#5341):
> 
> 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](https://codegolf.stackexchange.com/a/32841/70172), 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](https://codegolf.stackexchange.com/a/118041/70172), 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]
**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]
**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](https://data.stackexchange.com/codegolf/query/new)
 (SQL 2016) or with [dbfiddle.uk](http://dbfiddle.uk/) (SQL 2016 or SQL "vNext").
**STRING\_SPLIT** ([SQL 2016 and later](https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql))
```
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](https://docs.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql))
Shorter than `RTRIM()` and certainly shorter than `LTRIM(RTRIM())`.
Also has [an option to remove other characters or sets of characters](https://sqlwithmanoj.com/2016/12/26/new-built-in-function-trim-in-sql-server-vnext-2018/) from either the beginning or the end:
```
SELECT TRIM('sq,0' FROM 'SQL Server 2000')
```
returns `L Server 2`
**TRANSLATE** ([SQL 2017 or later](https://docs.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql))
`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`](https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql) and [`STRING_AGG`](https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql) that are probably worth a look as well.
[Answer]
Holy cow, I have discovered the wonder of **`PARSENAME`** ([SQL 2012 or higher](https://docs.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql?view=sql-server-2017)).
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]
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](https://codegolf.stackexchange.com/a/155150/70172).
```
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](https://codegolf.stackexchange.com/a/88729/70172):
```
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]
See [this question on dba.stackexchange](https://dba.stackexchange.com/questions/221290/adding-a-row-number-with-no-column-to-order-by) 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`](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-2017):
```
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*](https://docs.microsoft.com/en-us/sql/t-sql/functions/identity-function-transact-sql?view=sql-server-2017) (which is *not* the same as [the `IDENTITY` *property*](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017) 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]
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]
\_ 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.
]