About Commas-Transact-SQL formatting standard (encoding style)

About commas-Transact-SQL formatting standard (encoding style)

This article is a translation. For the original article, see:
Http://www.simple-talk.com/ SQL /t-sql-programming/transact-sql-formatting-standards (coding-Styles)

SQL code formatting tool: http://www.sqlzoo.cn/sqlpp

Commas (comma)

Discussions about how to process commas in T-SQL code will never have results, especially in the select list. However, there should also be a standard. In order to establish this standard, the following factors should be taken into account:

 

• Does commas need to be processed differently? For example, in the SELECT list
  Must the columns in be processed the same as those in the order by clause? How to deal with commas in the parameter list?
• Similar to the case where multiple rows exist in the SELECT list, database objects in other places may also be on multiple rows. Should comma be at the beginning or end of the row?
• Do spaces and tabs need to be used before and after comma?

 

These questions are obvious, but the answer is not. There have been countless discussions on the Internet. Before you set standards, let's take a look at some examples. The following SELECT statement has two places that require comma,

Select list and order by clauses:

 

 

SELECT
FirstName,

MiddleName,

LastName,

City,

StateProvinceName

FROM
HumanResources.
VEmployee

WHERE
JobTitle LIKE
'Production Technician %'

ORDER
BY
StateProvinceName,
City

In the select list, commas is placed behind the column name. However, in the order by clause, two column names are placed on one row. Therefore, comma is followed by a space after the first column name. Another method is to place comma before the column name of the select list, as shown in the following example:

SELECT
FirstName

,
MiddleName

,
LastName

,
City

,
StateProvinceName

FROM
HumanResources.
VEmployee

WHERE
JobTitle LIKE
'Production Technician %'

ORDER
BY
StateProvinceName,
City

 

 

In this example, comma is directly placed before the column name of the select list. However, you can add a fixed number of spaces before the column Name:

SELECT
FirstName

,
MiddleName

,
LastName

,
City

,
StateProvinceName

FROM
HumanResources.
VEmployee

WHERE
JobTitle LIKE
'Production Technician %'

ORDER
BY
StateProvinceName,
City

Now let's take a look at the example where comma in the order by clause treats the select list like this:

SELECT
FirstName,

MiddleName,

LastName,

City,

StateProvinceName

FROM
HumanResources.
VEmployee

WHERE
JobTitle LIKE
'Production Technician %'

ORDER
BY
StateProvinceName,

City,

LastName

As you can see, there are several different methods you can use commas. In addition, the use of commas is not limited to the select list and order by clauses. In the following DDL (Data Definition Language) Statement, comma is
Defines the elements and columns in the OBJECT_ID function:

IF
OBJECT_ID

(

'Productdocs'
,

'U'

)
IS
NOT
NULL

DROP
TABLE
ProductDocs

GO

CREATE
TABLE
ProductDocs

(

DocID int
NOT
NULL
IDENTITY
,

DocTitle nvarchar
(
50)
NOT
NULL,

DocFileName nvarchar
(
400)
NOT
NULL,

CONSTRAINT
PK_ProductDocs_DocID PRIMARY
KEY
CLUSTERED
(
DocID ASC
)

)

GO

 

In this example, the function parameters and the comma in the column definition are processed in the same way. There is no doubt that you have your own rules to handle formatting similar to functions and column definitions. However, regardless of the standard used, various formats of commas must be taken into account. At the same time, how to format the comma depends partly on how the elements in the statement are aligned and how spaces are used.