) 21 military rules for SQL Server database development

If you are in charge of a SQLServer-based project or you have just been in touch with SQLServer, you may have to face some database performance problems, this article will provide you with some useful guidance (most of which can also be used for other DBMS ).? Here, I am not going to introduce how to use SQLServer, nor can I provide a package

If you are in charge of a SQL Server-based project or you have just been in touch with SQL Server, you may have to face some database performance problems, this article will provide you with some useful guidance (most of which can also be used for other DBMS ).? Here, I am not going to introduce the tips for using SQL Server, nor can I provide a package

If you are in charge of a project based on SQL Server, or you have just been in touch with SQL Server, you may have to face someDatabasePerformance problems, this article will provide you with some useful guidance (most of which can also be used for other DBMS ).?
Here, I am not going to introduce the tips for using SQL Server, nor can I provide a solution to cure all diseases. What I do is to sum up some experience-about how to form a good design. These experiences have come from the lessons I have learned over the past few years. I have seen many design errors repeated over and over again .?
1. What tools do you use?
Don't underestimate this. This is the most important one I have mentioned in this article. Maybe you also see a lot of SQL Server programmers don't master all of the T-SQL commands and those useful tools that SQL Server provides .?
"What? I will waste a month to learn the SQL commands that I will never use ???", You may say this. Yes, you don't need. But you should use a weekend to browse all the T-SQL commands. Here, your task is to understand. In the future, when you design a query, you will remember: "Yes, here is a command that can fully implement the functions I need ", so, go to MSDN to view the exact Syntax of this command .?
2. Do not use a cursor?
Let me repeat it again: Do not use a cursor. If you want to damage the performance of the entire system, they are your best choice. Most Beginners use cursors without being aware of their impact on performance. They occupy memory and use their incredible ways to lock tables. In addition, they are like snails. The worst thing is that they can make all the performance optimization your DBA can do is not done. I wonder if you know that every execution of FETCH is equal to execution of a SELECT command? This means that if your cursor has 10000 records, it will execute 10000 SELECT! If you use a set of SELECT, UPDATE, or DELETE operations to complete the corresponding work, it will be much more efficient .?
Beginners generally think that using a cursor is a familiar and comfortable programming method, but unfortunately, this will lead to poor performance. Obviously, what is the overall purpose of SQL, rather than how to implement it .?
I used a T-SQL to override a cursor-based stored procedure, the table only had 100,000 records, the original stored procedure took 40 minutes to complete the execution, the new stored procedure only takes 10 seconds. Here, I think you can see what an incompetent programmer is doing !!!?
We can write a small program to get and process data and update it.Database. Remember: T-SQL is powerless for loops .?
I will remind you again that using a cursor is not helpful. Except for DBA, I have never seen that using a cursor can effectively complete any work .?
3. normalize your data tables?
Why not normalizeDatabase? There are two excuses: performance considerations and laziness. As for the second point, you will have to pay for it sooner or later. For performance issues, you don't need to optimize things that are not slow at all. I often see some programmers "de-standardization"DatabaseThe reason is that the original design is too slow, but the result is that they often make the system slower. DBMS is designed to handle specificationsDatabaseTherefore, remember: Design According to standardization requirementsDatabase.?
4. Do not use SELECT *?
This is not easy to do. I know it too well, because I often do it myself. However, if you specify the columns you need in the SELECT statement, it will bring the following benefits :?
1. Reduce memory consumption and network bandwidth?
2. Can you get a safer design?
3 is the query optimizer given the opportunity to read all required columns from the index?
5. What operations will you perform on the data?
For yourDatabaseCreating a robust index is a matter of merit. This is simply an art. When you add an index to a table, the SELECT statement is faster, but the INSERT and DELETE statements are much slower, because creating and maintaining indexes requires a lot of additional work. Obviously, the key question here is: what kind of operations do you want to perform on this table. This problem is not very well grasped, especially when it comes to DELETE and UPDATE, because these statements often include the SELECT command in the WHERE section .?
6. Do not create an index for the "gender" column?
First, we must understand how indexes accelerate table access. You can think of indexes as a way to divide tables based on certain standards. If you create an index for a column like "gender", you just divide the table into two parts: male and female. What is the significance of this division when you are processing a table with 1,000,000 records? Remember: It is time-consuming to maintain indexes. When designing indexes, follow these rules: sort the number of different content that may be contained in a column from many to few, such as name + province + gender .?
7. Use transactions?
Use transactions, especially when the query is time consuming. If a problem occurs in the system, this will save your life. Generally, some experienced programmers have some experience. You may encounter unexpected situations that may cause the storage process to crash .?
8. Be careful about deadlocks?
Access your table in a certain order. If you lock table A and table B first, lock them in this order in all stored procedures. If you first lock table B in A stored procedure and then lock Table A, this may lead to A deadlock. If the lock sequence is not designed in detail in advance, the deadlock is not easy to detect .?
9. Do not open large datasets?
A frequently asked question is: How can I quickly add 100000 records to ComboBox? This is not correct. You cannot or do this. It's easy. Your user needs to browse 100000 records to find the desired records, and he will curse you. Here, you need a better UI. You need to display up to 100 or 200 records for your users .?
10. Do not use server-side cursors?
Compared with server-side cursors, client cursors can reduce the system overhead of servers and networks and reduce the lock time .?
11. query using parameters?
Sometimes, I see a problem similar to this in the CSDN Technology Forum: "SELECT * FROM a where a. id = 'A' B, what should I do because of an exception in single quotes query ?", The general answer is: Use two single quotes instead of single quotes. This is incorrect. In this way, you will encounter such problems on some other characters, not to mention that this will lead to serious bugs. In addition, this will make the SQL Server Buffer system unable to play its due role. If you use parameter query and pay-as-you-go, all these problems do not exist .?
12. Using large data volumes during program codingDatabase?
ProgrammersDevelopmentTests used inDatabaseGenerally, the amount of data is small, but often the size of end users is large. Our usual practice is wrong. The reason is very simple: Hard Disks are not very expensive now, but why should we be noticed when performance problems are irrecoverable ??
13. Do not use INSERT to import large amounts of data?
Do not do this unless it is necessary. With UTS or BCP, you can have both flexibility and speed .?
14. Do you need to pay attention to timeout?
QueryDatabaseAverageDatabaseIs relatively small by default, such as 15 seconds or 30 seconds. Some queries run longer than this, especially whenDatabase.?
15. Do not ignore the problem of simultaneously modifying the same record?
Sometimes, two users modify the same record at the same time, so that the last modifier modifies the operation of the previous modifier, and some updates will be lost. It is not difficult to deal with this situation: Create a timestamp field and check it before writing. If it is allowed, merge and modify it. If there is a conflict, the user is prompted .?
16. When inserting records in the detail table, do not execute select max (ID) in the master table )?
This is a common error. When two users insert data at the same time, this will cause an error. You can use SCOPE_IDENTITY, IDENT_CURRENT, and IDENTITY. If possible, do not use IDENTITY, because it may cause some problems in the case of a trigger (see the discussion here ).?
17. Do not set the column to NULLable?
If possible, you should avoid setting the column as NULLable. The system will allocate an additional byte to each row of the NULLable column, resulting in more system overhead during query. In addition, setting columns as NULLable makes encoding complex, because each access to these columns must be checked first.
I am not saying that NULLS is the root cause of troubles, although some people think so. I think that if you allow "NULL data" in your business rules, setting the column as NULLable sometimes plays a good role. However, if you use NULLable in a similar case, that's nothing more than suffering .?
CustomerName1?
CustomerAddress1?
CustomerEmail1?
CustomerName2?
CustomerAddress2?
CustomerEmail3?
CustomerName1?
CustomerAddress2?
CustomerEmail3?
In this case, you need to normalize your table .?
18. Do not use the TEXT data type whenever possible?
Do not use TEXT unless you use TEXT to process a large amount of data. Because it is not easy to query and slow, it will waste a lot of space if it is not used well. Generally, VARCHAR can better process your data .?
Do not use temporary tables?
Try not to use temporary tables unless you have. Generally, subqueries can replace temporary tables. Using a temporary table will bring about system overhead. If you program using COM +, it will also bring you a lot of trouble, because the use of COM +DatabaseThe temporary table exists from the connection pool to the end. SQL Server provides some alternative solutions, such as the Table data type .?
20. Learn to analyze and query?
SQL Server Query analyzer is a good partner. through it, you can understand how queries and indexes affect performance .?
21. is the use of reference integrity?
Define the primary key, Uniqueness constraint, and foreign key, which can save a lot of time.