1. The use of []

Select * from user and select * from user info (select * from user info, select * from user info, select * from user info) Select * from [user]; select * from [user info]; select * from [user info];

2. NULLIF function

NULLIF(Expression1,Expression2): given two parameters (Expression1 and Expression2), NULL is returned if the two parameters are equal; Otherwise, the first argument is returned.

Equivalent to: Case WHEN Expression1=Expression2 Then NULL ELSE Expression1

For example, when Select NULLIF(1,1), NULL is returned; when Select NULLIF(1,2),1 is returned.

For practical purposes, such as preventing the release of a division by 0 operation, you can use a/NULLIF(b,0) so that b is not afraid of being 0. Of course, the division by 0 operation can be determined in other ways.

3.NULL

NULL is the magic thing that means NULL, unknown value, and any number added, subtracted, multiplied, and divided by it returns NULL.

4. The ISNULL function

ISNULL(Expression1,Expression2): given two parameters Expression1 and Expression2, return Expression2 if Expression1 ISNULL, otherwise return Expression1.

Equivalent to: Case WHEN Expression1 is NULL Then Expression2 ELSE Expression1

For example, Select ISNULL(NULL,1) returns 1 and Select ISNULL(1,2) returns 1.

For example, SELECT ISNULL(email, ‘no email’) from table1. If all email ISNULL, replace with ‘no email’.

5. COALESCE function

COALESCE (Expression1 and Expression2, Expression3,…) : accepts a series of expressions or columns, returning the first non-null value.

For example, SELECT COALESCE(NULL,NULL,4,NULL,NULL,5),4 is returned. If all parameters are NULL, an error is reported.

6.WITH TIES

Used with top() and order by to return more rows than top. Prevent the loss of desired information.

For example, I have a table table1

Select * from table1 order by name desc

Select top(3) * from table1 order by name desc

(3) select top(3) with ties * from table1 order by name desc:

7.ORDER BY NEWID() : Returns a random sort result.

8.BETWEEN a AND B: Returns a result greater than or equal to a AND less than or equal to b. If a>b, then NULL is returned.

9. Do not use a function in a WHERE condition. It forces the function to be evaluated on every row and cannot be indexed.

For example, select * from table1 where ID +3>5 and select * from table1 where ID >5-3, the latter is more efficient than the former.

10. Many columns end with _desc to make the meaning of a column more friendly.

For example, SELECT * FROM sys.databases

11. We recommend SQL Prompt, a small plug-in, with Microsoft SQL Server Management Studio, very convenient to use, and also add the following shortcuts:

(1) CTRL +5 or F5, run the code, if you want to run a particular statement, just select that statement, then F5 or CTRL +E.

(2) CTRL +L: Display the execution plan.

(3) CTRL +R: show hide the following results window, increase the space to write SQL.

(4) CTRL +K, then press Y to format the SQL code.