21 rules for SQL Server database development _mssql

Here, I'm not going to introduce the tips of using SQL Server, nor do I provide a panacea, and what I do is summarize some of the experience----about how to make a good design. These experiences come from the lessons I've endured over the years, and I've seen many of the same design mistakes repeated over and over again.

First, understand the tools you use

Don't underestimate this, this is the most important thing I have to tell you in this article. You may also see that a lot of SQL Server programmers don't have all the T-SQL commands and the useful tools that SQL Server provides.

"What?" I'll waste one months learning the SQL commands I never use??? ", you might say. Yes, you don't have to do that. But you should spend a weekend browsing through all the T-SQL commands. Here, your task is to understand that, in the future, when you design a query, you will remember: "Right, here is a command to fully implement the functionality I need," so go to MSDN to see the exact syntax of the command.

second, do not use cursors

Let me repeat: do not use cursors. If you want to disrupt the performance of the entire system, they are your most effective preferred option. Most beginners use cursors without being aware of their performance impact. They take up memory and lock the tables in their incredible ways, and they're like snails. And, worst of all, they can make everything your DBA can do to optimize the performance is not done. I don't know if you know. Every time you perform a fetch, you execute a SELECT command? This means that if your cursor has 10,000 records, it will execute 10,000 times select! if you use a set of SELECT, Update, or delete to do the job appropriately, That would be more efficient.

Beginners generally think that using cursors is a more familiar and comfortable way of programming, but unfortunately, this can lead to poor performance. Obviously, the overall purpose of SQL is what you want to achieve, not how to implement it.

I have used T-SQL to rewrite a cursor-based stored procedure that has only 100,000 records, the original stored procedure took 40 minutes to complete, and the new stored procedure took only 10 seconds. Here, I think you should be able to see what an incompetent programmer is doing!!!

We can write a small program to get and process the data and update the database, which is sometimes more effective. Remember: for loops, T-SQL is powerless.

Let me remind you that using cursors is no good. In addition to the work of the DBA, I have never seen using cursors to do any work effectively.

third, standardize your data sheet

Why not normalize the database? There are probably two excuses: for performance reasons and sheer laziness. As for the 2nd, you'll have to pay for it sooner or later. As for performance problems, you don't need to optimize things that aren't slow at all. I've often seen programmers "reverse-normalize" databases, and their rationale is that "the original design is too slow", but often the result is that they make the system slower. The DBMS is designed to handle the canonical database, so keep in mind: Design the database in accordance with the requirements of normalization.

Iv. do not use SELECT *

It's not easy, I know too much, because I do it myself. However, if you specify the columns you want in the Select, that will bring the following benefits:

1 Reduce memory consumption and network bandwidth
2 You can get a more secure design
3 Read all required columns to the query optimizer from the index

Learn what you are going to do with your data

Create a robust index for your database, and that's a piece of merit. But to do this is an art. Every time you add an index to a table, the select is faster, and the insert and delete are considerably slower, since it takes a lot of extra work to create a maintenance index. Obviously, the point here is: what you want to do with the table. This problem is not very well grasped, especially when it comes to delete and update, because these statements often contain a SELECT command in the Where section.

Vi. do not create an index for the "Sex" column

First, we must understand how the index accelerates access to the table. You can interpret an index as a way of dividing a table based on a certain standard. If you create an index on a column like "Sex," You just divide the table into two parts: male and female. You're working on a table with 1,000,000 records, what's the point of this division? Remember: It's time consuming to maintain an index. When you design an index, follow this rule: List the number of items that may contain different content, such as name + Province + sex.

Vii. use of services

Use transactions, especially when queries are time-consuming. If there is a problem with the system, it will save your life. It is common for some experienced programmers to experience-----you often encounter unpredictable situations that can cause a stored procedure to crash.

Eight, beware of deadlock

Visit your table in a certain order. If you lock table a first and then lock table B, then lock them in this order in all stored procedures. If you (inadvertently) lock table B in a stored procedure and then lock table A, this can cause a deadlock. Deadlocks are less likely to be found if the locking order is not well designed in advance.

Nine, do not open a large data set

One frequently asked question is: how can I quickly add 100,000 records to ComboBox? That's not right, you can't and you don't need to. It's easy for your users to browse 100,000 records to find the records they need, and he'll curse you. Here, what you need is a better UI, and you need to display no more than 100 or 200 records for your users.

10. Do not use server-side cursors

Compared to server-side cursors, client cursors reduce system overhead for servers and networks, and also reduce lockout time.

11. Use parameter query

Sometimes, I see a question like this in the CSDN Technology Forum: "SELECT * from a WHERE a.id= ' a ' B, because the single quote query has an exception, what should I do?" and the general answer is: replace single quotes with two single quotes. This is wrong. This will not cure the problem, because you will also encounter some other characters such problems, not to mention this will lead to serious bugs, in addition, this would make the SQL Server buffer system can not play its proper role. Use parameter query, the most drastic, these problems do not exist.

12, the use of large amounts of data in the program code database

The test database used by programmers in development is usually small in size, but the amount of data available to the end-user is very large. Our usual practice is not right, the reason is very simple: now the hard drive is not very expensive, but why the performance problem to wait until there is no time to be recalled?

13, do not use inserts to import large numbers of data

Please do not do this unless it is necessary. Use UTS or BCP, so you can have both flexibility and speed.

14, pay attention to the timeout problem

When querying a database, the default for the general database is smaller, such as 15 seconds or 30 seconds. Some queries run longer than this, especially when the amount of data in the database is constantly getting larger.

15, do not ignore the simultaneous modification of the same record problem

Sometimes, two users modify the same record at the same time, so that the latter modifies the action of the previous modifier, and some updates are lost. It's not hard to deal with this: Create a timestamp field, check it before writing, and if so, merge the changes and prompt the user if there is a conflict.

16. Do not execute select MAX (ID) in the main table when inserting records in the detail table

This is a common mistake, which causes errors when two users insert data at the same time. You can use Scope_identity,ident_current and IDENTITY. If possible, do not use identity because, in the case of a trigger, it can cause problems (see the discussion here).

17. Avoid setting the column to nullable

If possible, you should avoid setting the column to nullable. The system allocates an extra byte for each row of the nullable column, which can result in more system overhead. In addition, setting the column to nullable makes the encoding complex, because each time you access the columns, you must check them first.

I'm not saying that Nulls is the source of trouble, although some people think so. I think that if you allow "null data" in your business rules, setting the column to nullable sometimes works well, but if you're using nullable in a situation like this, it's just asking for it.

CustomerName1
CustomerAddress1
CustomerEmail1
CustomerName2
CustomerAddress2
CustomerEmail3
CustomerName1
CustomerAddress2
CustomerEmail3

If this happens, you need to standardize your watch.

18, try not to use the text data type

Unless you use text to process a large number of data, do not use it. Because it is not easy to query, slow, poor use will also waste a lot of space. In general, varchar can better handle your data.

19, try not to use the temporary table

Try not to use temporary tables unless you have to. Generally, subqueries can be used in place of temporary tables. Using a temporary table can have overhead, and if you are programming with COM +, it can also cause you a lot of trouble because COM + uses a database connection pool and temporary tables all the while. SQL Server provides alternatives, such as the table data type.

20, learn to analyze the query
SQL Server Query Analyzer is your good partner, through which you can see how queries and indexes affect performance.

21. Use referential integrity
Defining primary, uniqueness, and foreign keys can save a lot of time