Develop a good SQL habit and bring a great fortune

We do software development, most people are inseparable from dealing with the database, especially the development of ERP, and database dealing with more frequent, storage process is thousands of rows, if the volume of data large, people flow, then I can also guarantee the next period of time the system can still run smoothly? So can the next person be able to understand my stored procedures? Then I combined with the company's usual training and personal work experience and share with you, I hope to help everyone.

To know the SQL statements, I think we need to know how SQL Server Query Analyzer executes the statement I have, a lot of people will look at the execution plan, or use profile to monitor and tune the query statement or the reason for the slow stored procedure, but if we know the query parser execution logical order, When the hands of the time to be confident, then the start is not a certain point?

One: The logical execution order of the query

(1) from < left_table>

(2) on < join_condition>

(3) < join_type> join < right_table>

(4) WHERE < where_condition>

(5) GROUP by < group_by_list>

(6) with {cube | rollup}

(7) Having < having_condition>

(8) SELECT (9) DISTINCT (one) < top_specification> < select_list>

(Ten) ORDER by < order_by_list>

The parsing order of standard SQL is:

(1). The FROM clause assembles data from different data sources

(2). The WHERE clause filters records based on the specified criteria

(3). The GROUP BY clause divides the data into multiple groupings

(4). Using aggregate functions for calculations

(5). Use the HAVING clause to filter the grouping

(6). Calculate All expressions

(7). Use order by to sort the result set

Two execution order:

1.FROM: Performs a cartesian product of the first two tables in the FROM clause to generate a virtual table VT1

2.ON: Apply on filter to VT1 table only rows that meet < join_condition> are inserted VT2

3.OUTER (Join): Adds rows that are not found in the OUTER join reservation table (preserved table) as outer rows to the VT2 Generate T3 If the from contains more than two tables repeat steps and steps for the result table and the next table generated by the previous junction directly end

4.WHERE: Apply a WHERE filter to vt3 only rows that make < where_condition> true are inserted VT4

5.GROUP by: Grouping rows in VT4 by the list of columns in the GROUP BY clause VT5

6.cube| ROLLUP: Inserts a Hyper-group (supergroups) into the VT6 generation VT6

7.HAVING: Apply a have filter to vt6 only groups that make < having_condition> true are inserted vt7

8.SELECT: Process SELECT list Generation Vt8

9.DISTINCT: Remove duplicate rows from vt8 to produce VT9

10.ORDER by: Sorts the rows of VT9 by the column list in the ORDER BY clause to generate a cursor VC10

11.TOP: Select a specified number or proportion of rows from the beginning of VC10 to generate vt11 and return the caller

See here, then used Linqtosql grammar a bit similar ah? If we understand the order of SQL Server execution, then we will further develop the daily good habit, that is, in the implementation of the function of the thinking of performance, the database is a tool to perform set operations, We should try our best to use this tool, the so-called set operation is actually a batch operation, is to minimize the large amount of data in the client loop operation, and the use of SQL statements or stored procedures instead.

Iii. return only the data you need

The return data to the client needs at least the database extracts data, the network transmits the data, the client receives the data as well as the client processing data and so on, if returns the unnecessary data, will increase the server, the network and the client invalid labor, its harm is obvious, avoids this kind of event to need to notice:

A, Horizontal view

(1) Instead of writing the SELECT * statement, select the field you want.

(2) When you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the alias to each column. This reduces the time to parse and reduces the syntax errors caused by column ambiguity.

1. If there are tables table1 (id,col1) and table2 (id,col2)
2. Select a.id, A.col1, b.col2
3. --Select a.ID, col1, col2– do not write this, not conducive to future program expansion
4. From table1 A inner joins table2 B on a.id=b.id Where ...

B, longitudinal view

(1) write the WHERE clause reasonably, and do not write the SQL statement without where.

(2) SELECT TOP N *-Use this substitution without a where condition

Four: Try to do less repetitive work

A, control the execution of the same statement multiple times, especially the number of basic data execution is rarely noticed by many programmers.

B, reduce the number of data conversion, may require data conversion is a design problem, but the reduction is the programmer can do.

C, eliminate unnecessary sub-queries and join tables, sub-query in the execution plan is generally interpreted as an external connection, redundant connection table brings additional overhead.

D. Combine multiple update of the same condition on the same table, such as

1. UPDATE EMPLOYEE SET fname=' haiwer '
2. WHERE emp_id=' vpa30890f ' UPDATE EMPLOYEE SET lname=' YANG '
3. WHERE emp_id=' vpa30890f '

These two statements should be merged into one of the following statements

UPDATE EMPLOYEE SET fname= ' haiwer ', lname= ' YANG ' WHERE emp_id= ' vpa30890f '

E, update operation do not split into the form of delete operation +insert operation, although the function is the same, but the performance difference is very large.

V. Note the use of temporary tables and table variables

In complex systems, temporary tables and table variables are difficult to avoid, and for the use of temporal tables and table variables, it is important to note:

A, if the statement is complex and there are too many connections, consider stepping through the temporary table and table variables.

B, if you need to use the same part of a large table multiple times, consider staging this part of the data with temporary tables and table variables.

C, if you need to synthesize data from multiple tables to form a result, consider using temporal tables and table variables to summarize data for these tables in steps.

D, in other cases, you should control the use of temporary tables and table variables.

E, on the choice of temporary tables and table variables, many of the statements are table variables in memory, fast, should prefer table variables, but in actual use found that

(1) The main consideration is the amount of data that needs to be placed on the temporary table, and the temporary table will be faster than the amount of data.

(2) Execution time period and expected execution time (how long)

F, about temporary table generation using SELECT INTO and CREATE TABLE + INSERT into selection, in general,

The SELECT into will be much faster than the Create TABLE + INSERT into method.

However, select into locks the system tables sysobjects, sysindexes, and syscolumns in tempdb, which can easily block other processes in a multiuser concurrency environment.

So my advice is, in a concurrent system, try to use create TABLE + INSERT into, while a single statement of large data is used, using SELECT INTO.

Vi. usage of subqueries

A subquery is a select query that is nested within a SELECT, INSERT, UPDATE, DELETE statement, or other subquery.

Subqueries can be used wherever expressions are allowed, and subqueries make our programming flexible and can be used to implement special functions. But on the performance,

Often an inappropriate subquery usage can create a performance bottleneck. If a subquery's condition uses a field of its outer table, this seed query is called a correlated subquery.

Correlated subqueries can be introduced with in, not in, EXISTS, not EXISTS. For related subqueries, it should be noted that:

(1)

A, not in, not EXISTS related subquery can use the left join instead of the notation.

For example: Select Pub_name from publishers where pub_id not in (select pub_id from TITLES WHERE TYPE = ' business ') can be rewritten as: Select A.pub_name from Publishers A, left JOIN, TITLES B on b.type = "Business" and a.pub_id=b. pub_id WHERE b.pub_id is NULL

(2)

1. SELECT TITLE from TITLES
2. WHERE not EXISTS
3. (SELECT title_id from SALES
4. WHERE title_id = TITLES. title_id)

Can be changed to write:

1. SELECT TITLE
2. From the TITLES left JOIN SALES
3. On SALES. title_id = TITLES. title_id
4. WHERE SALES. title_id is NULL

B, if guaranteed subqueries are not duplicated, in, exists related subqueries can be replaced with inner JOIN. Like what:

1. SELECT pub_name
2. From publishers
3. WHERE pub_id in
4. (SELECT pub_id
5. From TITLES
6. WHERE TYPE = ' business ')

Can be changed to write:

1. SELECT a.pub_name --select DISTINCT a.pub_name
2. From publishers A INNER JOIN TITLES B
3. On b.type = "Business" and
4. A.pub_id=b. pub_id

(3)

The related subqueries of C, in are replaced by exists, for example

1. SELECT pub_name from Publishers
2. WHERE pub_id in
3. (SELECT pub_id from the TITLES WHERE TYPE = ' business ')

You can use the following statement instead:

1. SELECT pub_name from Publishers WHERE EXISTS
2. (SELECT 1 from the TITLES WHERE TYPE = ' business ' and
3. Pub_id= Publishers. pub_id)

D, do not use the subquery of Count (*) to determine whether there is a record, preferably with a left join or exists, such as someone to write such a statement:

1. SELECT Job_desc from JOBS
2. Where (SELECT COUNT (*) from EMPLOYEE WHERE job_id=jobs. job_id) =0

Should be changed to:

1. SELECT JOBS. Job_desc from JOBS left JOIN EMPLOYEE
2. On EMPLOYEE. Job_id=jobs. job_id
3. WHERE EMPLOYEE. emp_id is NULL
4. SELECT Job_desc from JOBS
5. Where (SELECT COUNT (*) from EMPLOYEE WHERE job_id=jobs. job_id) <>0

Should be changed to:

1. SELECT Job_desc from JOBS
2. where EXISTS (SELECT 1 from EMPLOYEE WHERE job_id=jobs. job_id)

Seven: Try to use the index

After indexing, not every query uses an index, and the efficiency of indexing can vary greatly when using indexes. As long as we do not enforce the specified index in the query statement,

The selection and use of an index is an automatic choice for the SQL Server optimizer, and it is selected based on the conditions of the query statement and the statistics of the related table, which requires us to write the SQL

When you try to make the optimizer use the index. In order for the optimizer to use the index efficiently, it should be noted when writing statements:

A, do not operate on the indexed fields, but want to do a change, such as

SELECT ID from T WHERE num/2=100

should read:

SELECT ID from T WHERE num=100*2

-------------------------------------------------------

SELECT ID from T WHERE num/2=num1

If Num has an index, it should read:

SELECT ID from T WHERE num=num1*2

If the NUM1 has an index, it should not be changed.

--------------------------------------------------------------------

You have found a statement like this:

SELECT year, month, amount from balance table WHERE 100* year + month =2010*100+10

should read:

SELECT year, month, amount from balance table WHERE year =2010 and month =10

B. Do not format the indexed fields

Examples of date fields:

WHERE CONVERT (VARCHAR (10), date field, 120) = ' 2010-07-15 '

should read

Where date field 〉= ' 2010-07-15 ' and date field < ' 2010-07-16 '

Examples of IsNull conversions:

where ISNULL (field, ') <> ' should be changed to: where Field <> '

WHERE ISNULL (field, ') = ' should not be modified

where ISNULL (field, ' F ') = ' t ' should be changed to: where field = ' t '

WHERE ISNULL (field, ' F ') <> ' t ' should not be modified

C, do not use functions for indexed fields

where left (NAME, 3) = ' abc ' or where SUBSTRING (name,1, 3) = ' abc '

Should read: WHERE NAME like ' abc% '

Examples of date queries:

WHERE DATEDIFF (day, date, ' 2010-06-30 ') =0

Should read: WHERE date >= ' 2010-06-30 ' and date < ' 2010-07-01 '

WHERE DATEDIFF (day, date, ' 2010-06-30 ') >0

Should read: WHERE date < ' 2010-06-30 '

WHERE DATEDIFF (day, date, ' 2010-06-30 ') >=0

Should read: WHERE date < ' 2010-07-01 '

WHERE DATEDIFF (day, date, ' 2010-06-30 ') <0

Should read: WHERE date >= ' 2010-07-01 '

WHERE DATEDIFF (day, date, ' 2010-06-30 ') <=0

Should read: WHERE date >= ' 2010-06-30 '

D, do not make multi-field connections to indexed fields

Like what:

WHERE fame+ '. ' +lname= ' Haiwei. YANG '

should read:

WHERE fname= ' Haiwei ' and lname= ' YANG '

Eight: The connection condition of the multi-table connection is of great significance to the selection of the index, so we need to pay special attention when we write the condition of the connection.

A, multi-table connection, the connection conditions must be written in full, rather repeat, do not leave gaps.

B, connection conditions use clustered index as far as possible

C, notice the difference between on, where and having partial conditions

On is the first execution, where the second, having the last, because on is the non-qualified records filtered before the statistics, it can reduce the intermediate operation to process the data, it should be said that the speed is the fastest, where also should be faster than having, Because it filters the data before it does sum, it is used on when two tables are joined, so in a table, there is a comparison between where and having.

1 Consider the join precedence order:

2INNER JOIN

3LEFT Join (Note: Right join replaces with left join)

4CROSS JOIN

Other areas of attention and understanding are:

A, in the face value of the list, will appear the most frequent values on the front, the least appear in the last face, reduce the number of judgments

B, note the difference between Union and union all. --Allow duplicate data with union ALL good

C, pay attention to the use of distinct, do not use when not necessary

D, TRUNCATE TABLE and DELETE difference

E, reduce the number of access to the database

There is we write stored procedures, if long, and finally with the mark descriptor open, because it is very readable, even if the statement is not very good, but the statement is neat, C # has region

SQL I prefer to use is

1. --startof Enquiry for Number of employees
2. SQL statements
3. --end of

On the formal machine we generally can not easily debug the program, but many times the program in our machine is not a problem, but into the formal system there is a problem, but we can not casually operate on the formal machine, then how to do? We can debug our stored procedures or SQL statements with rollback, thus troubleshooting.

1. BEGIN TRAN
2. UPDATE a SET field =' '
3. ROLLBACK

Job stored procedures I usually add the following paragraph, so that the check error can be placed in the stored procedure, if the error rollback operation, but if there is already a transaction rollback in the program, then the stored procedure does not write the transaction, which will cause the transaction rollback nesting to reduce the efficiency of execution, But most of the time we can put the check in the stored procedure, which helps us to interpret the stored procedure and debug the error.

2. BEGIN TRANSACTION
4. --Transaction rollback begins
6. --Check the error
8. IF (@ @ERROR > 0)
10. BEGIN
12. --Rollback operation
14. ROLLBACK TRANSACTION
16. RAISERROR (' Delete work report error ', 3)
18. RETURN
20. END
22. --End transaction
24. COMMIT TRANSACTION

Long time no blog, work items one by one, coupled with the company's staff flow, the newcomer many things can not come down, overtime became a commonplace, rushed to write these hope to everyone has help, not to also welcome guidance, exchanges and improve each other.

There are mistakes in the place to welcome everyone to shoot bricks, hope to exchange and share.

Original link: http://www.cnblogs.com/MR_ke/archive/2011/05/29/2062085.html

Develop a good SQL habit and bring a great fortune