Semicolon in query breaks formatting rules

Preface:
I've reverted to using the default T-SQL formatting rules to rule out any of my custom rules, both results are identical.

If there's a semicolon at the end of an update query, the align datatypes/assignments option seems to be ignored.

See the video:

http://screencast.com/t/bDc9Tv0fJD6

Last edited by Mindflux on Thu Sep 12, 2013 1:51 pm; edited 1 time in total

Thank you. This is very helpful. I have logged this bug to your internal issue tracking system

Using aliases adds some spice to the mess. The statement that uses aliases and adds them to the SET part as well will ignore assignment alignment settings while omitting the alias there results in proper alignment.

Considering example #3 it also seems that an UPDATE statement without the semicolon is considered "somewhat incomplete" by SA and is influenced by things following it, because the alias-omitting first statement (followed with the "wouldn't-be-aligned-properly-anyway" second statement) isn't aligned properly either. On the other hand, reversing the order of the two statements results in proper alignment for the alias-omitting one, as it can be seen in #4.

Try to format the codes I provide below (each in its own editor).

#1. UPDATE with aliases but omitting the alias of the table to be updated in the SET part (aligned properly)

Code:

UPDATE arsr
SET SEL = ss.Rpt
,Rpt = ss.Rpt
,rawPQL = ss.PQL
,rawMDL = ss.MDL
,MCL = ss.MCL
,AnalyteType = ss.AnalyteType
,ListOrder = ss.ListOrder
FROM
tmpFillSeq AS tfs
INNER JOIN
AnalRunSeq AS ars
   ON tfs.SeqNo = ars.SeqNo
INNER JOIN
SampleSel AS ss
   ON ars.TestNum = ss.TestNum
INNER JOIN
AnalRunSeqResult AS arsr
   ON ss.Analyte = arsr.Analyte
   AND ars.SeqNo = arsr.SeqNo
WHERE WSID = '[Parameter2]'

#2. UPDATE with aliases everywhere (not aligned properly)

Code:

UPDATE arsr
SET arsr.ss.Rpt
,arsr.Rpt = ss.Rpt
,asrs.rawPQL = ss.PQL
,asrs.rawMDL = ss.MDL
,arsr.MCL = ss.MCL
,arsr.AnalyteType = ss.AnalyteType
,arsr.ListOrder = ss.ListOrder
FROM
tmpFillSeq AS tfs
INNER JOIN
AnalRunSeq AS ars
   ON tfs.SeqNo = ars.SeqNo
INNER JOIN
SampleSel AS ss
   ON ars.TestNum = ss.TestNum
INNER JOIN
AnalRunSeqResult AS arsr
   ON ss.Analyte = arsr.Analyte
   AND ars.SeqNo = arsr.SeqNo
WHERE WSID = '[Parameter2]'

#3. Formatting both statements at once with the alias-omitting one first (both misaligned)

Code:

UPDATE arsr
SET SEL = ss.Rpt
,Rpt = ss.Rpt
,rawPQL = ss.PQL
,rawMDL = ss.MDL
,MCL = ss.MCL
,AnalyteType = ss.AnalyteType
,ListOrder = ss.ListOrder
FROM
tmpFillSeq AS tfs
INNER JOIN
AnalRunSeq AS ars
   ON tfs.SeqNo = ars.SeqNo
INNER JOIN
SampleSel AS ss
   ON ars.TestNum = ss.TestNum
INNER JOIN
AnalRunSeqResult AS arsr
   ON ss.Analyte = arsr.Analyte
   AND ars.SeqNo = arsr.SeqNo
WHERE WSID = '[Parameter2]'

UPDATE arsr
SET arsr.ss.Rpt
,arsr.Rpt = ss.Rpt
,asrs.rawPQL = ss.PQL
,asrs.rawMDL = ss.MDL
,arsr.MCL = ss.MCL
,arsr.AnalyteType = ss.AnalyteType
,arsr.ListOrder = ss.ListOrder
FROM
tmpFillSeq AS tfs
INNER JOIN
AnalRunSeq AS ars
   ON tfs.SeqNo = ars.SeqNo
INNER JOIN
SampleSel AS ss
   ON ars.TestNum = ss.TestNum
INNER JOIN
AnalRunSeqResult AS arsr
   ON ss.Analyte = arsr.Analyte
   AND ars.SeqNo = arsr.SeqNo
WHERE WSID = '[Parameter2]'

#4. Formatting both statements at once with the alias-omitting one last (the omitting one aligned properly)

Code:

UPDATE arsr
SET arsr.ss.Rpt
,arsr.Rpt = ss.Rpt
,asrs.rawPQL = ss.PQL
,asrs.rawMDL = ss.MDL
,arsr.MCL = ss.MCL
,arsr.AnalyteType = ss.AnalyteType
,arsr.ListOrder = ss.ListOrder
FROM
tmpFillSeq AS tfs
INNER JOIN
AnalRunSeq AS ars
   ON tfs.SeqNo = ars.SeqNo
INNER JOIN
SampleSel AS ss
   ON ars.TestNum = ss.TestNum
INNER JOIN
AnalRunSeqResult AS arsr
   ON ss.Analyte = arsr.Analyte
   AND ars.SeqNo = arsr.SeqNo
WHERE WSID = '[Parameter2]'

UPDATE arsr
SET SEL = ss.Rpt
,Rpt = ss.Rpt
,rawPQL = ss.PQL
,rawMDL = ss.MDL
,MCL = ss.MCL
,AnalyteType = ss.AnalyteType
,ListOrder = ss.ListOrder
FROM
tmpFillSeq AS tfs
INNER JOIN
AnalRunSeq AS ars
   ON tfs.SeqNo = ars.SeqNo
INNER JOIN
SampleSel AS ss
   ON ars.TestNum = ss.TestNum
INNER JOIN
AnalRunSeqResult AS arsr
   ON ss.Analyte = arsr.Analyte
   AND ars.SeqNo = arsr.SeqNo
WHERE WSID = '[Parameter2]'