![SoftTree Technologies](templates/GreenTech/images/logo.gif) |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
13th
Joined: 12 Jan 2014 Posts: 27 Country: United Kingdom |
|
SQL Server formatting |
|
try to format this
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
|
Thu Oct 05, 2023 11:33 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Format it using which formatting style?
|
|
Fri Oct 06, 2023 1:30 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Never mind, it's in the subject
Here is what I get
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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.
|
|
Fri Oct 06, 2023 1:39 am |
|
![](templates/GreenTech/images/spacer.gif) |
13th
Joined: 12 Jan 2014 Posts: 27 Country: United Kingdom |
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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
|
|
Fri Oct 06, 2023 6:49 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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.
|
|
Fri Oct 06, 2023 8:44 am |
|
![](templates/GreenTech/images/spacer.gif) |
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|