10 errors that SQL Server query design should avoid

In this article, you will list 10 common query design errors that you need to avoid. Read this article to make sure you don't become the victim of these mistakes, consider the suggestions given, and modify your query.

1. Data model and concurrent query

If you do not take into account the way data is accessed when building a data model, you will cause queries that are difficult to handle. You may use an unnecessarily join to add code and damage performance.

To correct this problem, consider the query that requires access to the data. If the query is not very clear at this stage of processing, it will be more difficult to write code in the future. It is most likely that the database design is too complex and can be simplified to improve the performance of the query.

In this connection, if you are an intuitive person, print out the data model, or look at the online model when you select the Data Modeling tool. This can improve the time and accuracy of your code.

2. What is the best technique?

This is the infamous pointer and the logical discussion based on the set. Traditional wisdom says that all database accesses use a set based logic. Generally speaking, I agree that this is one of the best experiences. When the logic based on the set is the right choice, the pointer is used, which can cause great damage to the performance. SQL Server is designed to use a collection based logic, and it should be used in most processes.

Things are two sides, the other side is the example of the pointer. In this case, the pointer logic is better than the logic based on the collection. The conclusion from this information is to determine the type of processing you want to perform and to choose the skills that are best suited to your needs.

3, in the original way ...

SQL Server 2005 offers a whole new set of opportunities for your query. So using the old method may still work, but it's time to think about the latest options. TRY ... Catch error handling is one of the first techniques you should use in your code. In addition, it is necessary to consider the level of processing, you can use the common table compression; The last consideration is to extend the functionality of the relational database engine: the Common language Runtime (CLR). These three technologies have dramatically changed the way you work with SQL Server, and they are just the tip of the iceberg.

4, you also let a stupid goose there?

Check your code and schedule a time for the same view, which is what you have to do before you deploy the code. Check your code to make sure you have the right index, and that the query will run as important as you expect.

5. This is a classic mistake.

Enter the SELECT * statement, thinking that the table will never change, which is a classic query design error. Even in the simplest of solutions, table changes are unavoidable, and you need to look at the code to make sure it doesn't contain an extra field. Or, worse, you have to wait for the application to crash, and then fix the problems. The best practice scenario is to include the fields you need in your query, and then modify them if necessary. Don't waste your time in smoking patterns and sweeping the code.

6. I don't have a comment

Unfortunately, most of the code I've seen has little or no comment at all. So making changes is a daunting task, even for developers and/or database administrators who initially developed the application. Note that your code is really a fast and painless process that is critical for future developers to understand and modify code in a secure and time-saving manner.

7, of course, I will test ...

Few developers and database administrators like simple tests, nor do they like rigorous testing before releasing code into the product environment. Also, the development environment usually does not reach the size of the product environment in hardware and data volumes. That is, a simple query can work well on hundreds of or even thousands of records, but that's not the case in a product environment. There is no better way to prepare for your query, only test millions of pieces of data in a fragmented table in a test environment to ensure that the query runs as you expect.

8, let me use this bar, is this!

It is a bad idea to enter a SELECT statement that does not contain a WHERE clause, expecting the middle tier or front-end to process the resulting data in a more efficient way than SQL Server. SQL Server is designed to process queries and perform them very efficiently. Moving a large amount of data will only put a flood of systems and networks into trouble. Be sure to filter your data as much as possible to avoid performance impact.

9, please let me use the view to query it

Views can satisfy your need to simplify the code in complex queries. They are often used to help users who have rights to query the database. Unfortunately, too many good things can seriously affect performance. A view is a simple SELECT statement, and the SELECT statement of the view must be entered again each time you enter a SELECT statement. Restrict the use of views to prevent them from querying other views. Alternatively, build a stored procedure to query the data and pass it to the parameters it needs to meet the needs of the application or the user.

10, no, this is not my code ...

We all make mistakes, and the last system we work on should benefit from the knowledge we have in the current system. So, record what you've learned and share it with your team to benefit the collective. When you have a chance, go back to the previous system and improve them with the knowledge that you learned from that project.

Conclusion

If you make more than one mistake in the query, admit the mistake and try to correct it. It's easier said than done, but correcting these problems will benefit the business and be good for the reputation of the application. After reading this article, start building a private code guide for the projects you're doing or going to do later.