A few tips on SQL and SQL

A few tips on SQL and SQL

Preface: after a previous project was completed, the database and system architecture was designed, the system was put online on schedule after a period of coding with the brothers, and everything was running well at the beginning, later, with the rapid expansion of data volumes, many inexplicable problems gradually emerged. After debugging, several stored procedures in the database were modified. Interestingly, in a stored procedure, a lot of code is written to implement a small function, and the cursor is a loop, in fact, it can be solved by a default function of the system. What I want to talk about here is that, after studying and working, I suggest you do not have to solve the problem.System readingFor some knowledge points that are useless, it is recommended that you take a closer look and practice your hands. You may be able to use them at any time, we will not blindly follow our own ideas to repeatedly create inefficient solutions.

======= Start of the body ================

1. [] usage

When the table we want to query is a system keyword or the table name contains spaces, we need to use [] to enclose it. For example, we have created two new tables: user, user info, if select * from user and select * from user info, an error is reported, which must be written as select * from [user] and select * from [user info].But do not name the table randomly because of the help of []. It is troublesome, but I do see someone naming the user table as user.

2. NULLIF Function

NULLIF (Expression1, Expression2): Given two parameters Expression1 and Expression2, if the two parameters are equal, NULL is returned; otherwise, the first parameter is returned.

It is equivalent to: Case WHEN Expression1 = Expression2 Then null else Expression1.

For example, Select NULLIF () returns NULL, and Select NULLIF () returns 1.

There is a practical application, for example, to prevent the release of the 0 operation, you can use a/NULLIF (B, 0), so that it is not afraid that B is 0, of course, operations other than 0 can also be determined by other methods.

3. NULL

NULL is a magic thing. It indicates a NULL value and an unknown value. If any number is involved in addition, subtraction, multiplication, and division, NULL is returned.

4. ISNULL Function

ISNULL (Expression1, Expression2): specify two parameters: Expression1 and Expression2. If Expression1 is NULL, Expression2 is returned; otherwise, Expression1 is returned.

It is equivalent to: Case WHEN Expression1 is NULL Then Expression2 ELSE Expression1.

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

There is a practical application that can replace null values with default values. For example, select isnull (email, 'do not fill in email') from table1, all emails are null, use 'do not fill in the email 'instead.

5. COALESCE Function

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

For example, select coalesce (NULL, NULL, 4, NULL, NULL, 5), 4 is returned. If all the parameters are NULL, an error is returned.

6. WITH TIES

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

For example, there is a table table1

(1) select * from table1 order by name desc: The result is as follows:

(2) select top (3) * from table1 order by name desc: The result is as follows: (only three items exist)

(3) select top (3) with ties * from table1 order by name desc: The result is as follows:

7. order by newid (): returns the random sorting result.

8. BETWEEN a AND B: ReturnGreater than or equalA,Less than or equalB. If a> B, NULL is returned.

9. Do not use a function in the where condition. It will force each row to calculate the function and cannot use index search.

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 ending with _ desc are used to better indicate the meaning of a column.

Example: SELECT * FROM sys. databases

11. We recommend a small plug-in, SQL Prompt, and Microsoft SQL Server Management Studio. It is very convenient to use and the following shortcut keys are added:

(1) ctrl + 5 or F5: run the code. To run a specific statement, select the statement and then press F5 or ctrl + E.

(2) ctrl + L: displays the execution plan.

(3) ctrl + R: Hide the following result window and increase the space for writing SQL statements.

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