Tsql bad coding specifications

As a DBA, you must deal with a variety of tsql code. See the following three examples:

 

1: parentheses are used in distinct:

SELECT DISTINCT (TransactionID),ProductID,TransactionTypeFROM adventureworks.Production.TransactionHistoryArchive

The results are exactly the same as those in the following sentence, but the above sentence may be confusing. Is it true that transactionid removes duplicate items or that all columns are combined to remove duplicate items! The standard format is as follows:

SELECT DISTINCT TransactionID,ProductID,TransactionTypeFROM adventureworks.Production.TransactionHistoryArchive

2: fields defined in many codes are not defined in length. Let's first look at the table definition:

CREATE TABLE tab1 (col1 VARCHAR )INSERT INTO tab1 SELECT 'english'

Code error. No insert successful!

MSG 8152, level 16, State 14, line 1

String or binary data wocould be truncated.

The statement has been terminated.

Insert a single character:

INSERT INTO tab1 SELECT 'e'

OK. The definition table has no specified length. The default value is 1.

Another common case is that cast and convert functions are used for conversion:

DECLARE @y varchar(100)='This article explains the different technologies that are available for scaling out'SELECT len(@y),len(cast(@y AS VARCHAR ))

Check the result.

The default value is 30 characters! Therefore, to make the code clear and readable, we need to include the length.

 

3: Where nvarchar should be used, varchar should also be used.

Declare @ name1 varchar (10) = 'in 'descare @ name2 nvarchar (10) =' select datalength (@ name1), datalength (@ name2)

We can see that the two use the same number of bytes, and the use of varchar (10) = 'in the method will produce an inexplicable problem in SSIs, so in order to make the code readable, we still need to follow the rules!