Tips for Golfing in T-SQL

16

5

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

Michael B

Posted 2014-07-01T21:10:50.630

Reputation: 1 551

a tip - use a different language for golfing. Sql answers usually gets very few or no upvotes at all. – t-clausen.dk – 2019-04-07T16:17:41.953

Answers

16

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.

Michael B

Posted 2014-07-01T21:10:50.630

Reputation: 1 551

For me, $ is a bit more obvious than \ to initialize a number as 0 in a money type. YMMV – user1443098 – 2018-11-21T14:04:09.777

5

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)

comfortablydrei

Posted 2014-07-01T21:10:50.630

Reputation: 701

Great strategy, that multi-replace style can be useful in more conventional scenarios as well. – BradC – 2017-06-14T13:27:03.933

1After 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. – BradC – 2017-06-14T19:45:21.273

Actually for 4 or fewer replacements, you'd save bytes with an old-fashioned SET @=REPLACE(REPLACE(REPLACE(... – BradC – 2017-06-14T19:52:55.740

4

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

comfortablydrei

Posted 2014-07-01T21:10:50.630

Reputation: 701

1Actually 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 – 2014-07-02T04:48:31.803

1Note 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 – 2014-07-02T05:01:32.907

3

Print instead of Select

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

print'Hello, World!'

Try it online

mbomb007

Posted 2014-07-01T21:10:50.630

Reputation: 21 944

3

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.

naXa

Posted 2014-07-01T21:10:50.630

Reputation: 131

3

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.

Jacob

Posted 2014-07-01T21:10:50.630

Reputation: 1 582

1This 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 – 2014-07-02T22:33:01.063

2

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

BradC

Posted 2014-07-01T21:10:50.630

Reputation: 6 099

2

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.

BradC

Posted 2014-07-01T21:10:50.630

Reputation: 6 099

Nice, I don't know SQL but this looks cool still – MilkyWay90 – 2019-08-25T15:59:20.633

1

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

BradC

Posted 2014-07-01T21:10:50.630

Reputation: 6 099

1

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

BradC

Posted 2014-07-01T21:10:50.630

Reputation: 6 099

1

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.

BradC

Posted 2014-07-01T21:10:50.630

Reputation: 6 099

1

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

BradC

Posted 2014-07-01T21:10:50.630

Reputation: 6 099

I am not sure when PARSENAME came out, but there are articles describing it from 2003 – t-clausen.dk – 2019-03-30T06:37:48.277

1

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.

BradC

Posted 2014-07-01T21:10:50.630

Reputation: 6 099

1

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.

BradC

Posted 2014-07-01T21:10:50.630

Reputation: 6 099

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. – user1443098 – 2018-11-21T14:17:13.317

1

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

– BradC – 2018-11-21T15:28:58.993

1

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

BradC

Posted 2014-07-01T21:10:50.630

Reputation: 6 099

0

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

user1443098

Posted 2014-07-01T21:10:50.630

Reputation: 101

I find cross apply to long, it is really hard to find a useful situation using cross apply without finding another shorter method – t-clausen.dk – 2019-04-04T21:19:52.610

OK -- shorten the example given above! – user1443098 – 2019-04-05T13:54:19.887

SELECT TOP 10 number, number*2 n2 FROM master.dbo.spt_values v – t-clausen.dk – 2019-04-05T16:39:44.457

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. – user1443098 – 2019-04-05T19:12:21.627

Subselect is shorter than cross apply: SELECT top 10* FROM(SELECT number n, number*2n2 FROM master..spt_values)x – t-clausen.dk – 2019-04-05T20:06:21.277

XML queries doesn't need to contain cross apply. At least I can't think of an example. Besides XML queries are usually not used for golfing – t-clausen.dk – 2019-04-06T06:03:13.487