SQL Server formatting

try to format this

Code:

select 1 --"drop table if exists" without first line formatting right
drop table if exists #num_table
--not a real life query, but shows a lot of problems of formatting (wraps, idents, different format style of subqueries)
with table1 as (select row_id ,operation_type_id from table2
union all select row_id ,operation_type_id from table3)
select top 10 a.row_id ,(select a.row_id) col from table1 a
cross join table1 f inner join table1 i
on i.row_id = a.row_id
and i.row_id = (select top 1 row_id from xx)
outer apply (select h.row_id from table1 h
where h.row_id = a.row_id)h inner join table1 k
on k.row_id = a.row_id cross apply (select g.row_id
from table1 g
where g.row_id = a.row_id)
g1

Format it using which formatting style?

Never mind, it's in the subject

Here is what I get

Code:

SELECT 1 --"drop table if exists" without first line formatting right
DROP TABLE
IF EXISTS #num_table
--not a real life query, but shows a lot of problems of formatting (wraps, idents, different format style of subqueries)
WITH table1 AS (
SELECT row_id,
operation_type_id
FROM table2
UNION ALL SELECT row_id,
operation_type_id
FROM table3
)
SELECT TOP 10 a.row_id,
(
SELECT a.row_id
) col
FROM table1 a
CROSS JOIN table1 f
INNER JOIN table1 i
ON i.row_id = a.row_id
AND i.row_id = (
SELECT TOP 1 row_id
FROM xx
)
OUTER APPLY (
SELECT h.row_id
FROM table1 h
WHERE h.row_id = a.row_id
)h INNER JOIN table1 k
ON k.row_id = a.row_id
CROSS APPLY (
SELECT g.row_id
FROM table1 g
WHERE g.row_id = a.row_id
)
g1

Without statement separators and batch separators it confuses "DROP ... IF EXISTS" with "DROP ...; IF EXISTS ... do..."

After adding statement separators, it gets better

Code:

SELECT 1;
--"drop table if exists" without first line formatting right
DROP TABLE
IF EXISTS #num_table
;

--not a real life query, but shows a lot of problems of formatting (wraps, idents, different format style of subqueries)
WITH table1 AS (
SELECT row_id,
operation_type_id
FROM table2
UNION ALL SELECT row_id,
operation_type_id
FROM table3
)

SELECT TOP 10 a.row_id,
(
SELECT a.row_id
) col
FROM table1 a
CROSS JOIN table1 f
INNER JOIN table1 i
ON i.row_id = a.row_id
AND i.row_id = (
SELECT TOP 1 row_id
FROM xx
)
OUTER APPLY (
SELECT h.row_id
FROM table1 h
WHERE h.row_id = a.row_id
)h INNER JOIN table1 k
ON k.row_id = a.row_id
CROSS APPLY (
SELECT g.row_id
FROM table1 g
WHERE g.row_id = a.row_id
)
g1

But still not perfect. To make it perfect, please add to your formatting rules a new rule for formatting DROP ... IF EXISTS type of statement so it knows how to handle them.

	SysOp wrote:
	To make it perfect, please add to your formatting rules

Believe me, before writing here I played with formatting rules for a very long time

i can't do correct indents with current system

There are some limits to what the code formatter can do, queries can get quite complicated, especially when like in T-SQL legacy code statements are not delimited. In some cases you may need to highlight part of a code, like a highlight separate SELECT statement which is in a complex procedure, CTE or sub-query and press Ctrl+F11 to format it separately. But there are other options available too.

Add additional formatting patterns to the formatting styles which better align with the type of your code. You can define new formatting patterns in the Options, change existing patterns to your taste too. They are not hard-coded, and can be customized. As said previously, if you use DROP TABLE ... IF EXISTS often, add a new pattern for that type of SQL construct to make the code formatter recognize it. I see you also use CROSS JOIN, which is not in the default collection of patterns, but JOIN is there. So please add a rule for CROSS JOIN ... or modify default SELECT rule and add CROSS to make it recognize that and don't add space indenting before JOIN.

Sometimes it's difficult to create a single formatting pattern that works well for all use cases. For example, sometimes you may want UNION SELECT to be on the same line for example, sometimes you want them on separate lines, etc.... You can define and use multiple formatting styles for different use cases and use Ctrl+Shift+F11 to choose on the fly which style to apply.