What mistakes to avoid in SQL Server query design

[Galbraith]

Hi all,

With the SQL Server database, filling and continuous data growth, as well as user for the sub-second response time expectation to avoid the preparation of a bad check is paramount. I want some query to avoid design errors. Can anybody ensure me that i do not become a victim of these errors. Please suggest. Thanks in advance.

[Reegan]

If you are building a data model does not take into account when the data access methods will lead to difficult to deal with the investigation consultation. You may not use unnecessary JOIN increase in code, and hurt performance.
To correct this problem, consider the need to access the data query. If the query at this stage of processing is not very clear, then the future will be the time to write the code more difficult. Is probably too complex database design, you can pass been simplified to improve query performance. In this connection, if you are a visual person who likes, then print out the data model, or the data you choose to modeling tools, when a look-line models. This can improve your code to the time and accuracy. All the best.

[Zecho]

For all data library access to use set-based logic. In general, I agree that this is one of the best experience. When the collection-based logic is the right choice when they use the pointer, may have caused great damage to properties. SQL Server the design is to use set-based logic, and in most of the processing should be to use it. Matter sub-sides, the other side is a pointer example. In this case, the pointer logic than set-based logic from this information arise out of the conclusion is that you want to perform processing to determine the type. Check and reply.

[MindSpace]

SQL Server 2005 for your inquiries a complete set of new opportunities. So, using the old ways may still be work, but it is time to think about the latest choice. TRY ... CATCH error handling method is that you should first the techniques used in one of the code. Moreover, you should consider is the level of processing, they can use common table compression; a final consideration is to extend the relational database engine features: Common Language Runtime (CLR). These three techniques patients are in a great extent changed the work you are using SQL Server, rather they are only tip of the iceberg. All the best.

[Praetor]

Enter SELECT * statement, think of the table will never change, this is a classic query design errors. Even the simplest solution, the table to change the inevitable, you need to see the code to ensure that does not contain an amount of outside the field. Or, even worse, you have to wait for an application crash, and then fix these problems. Best Practice, the program is only in your query contains those fields you need to come in, and then if necessary to modify them. Not take your time wasted on the smoking patterns around the code a thorough investigation. Check and reply.

[Katty]

Few developers and database administrators will enjoy the simple test, they do not like to release the code to the product environment prior to rigorous testing. Also, development environment, usually in the amount of hardware and data products that are lower than on the environment scale. That is, a simple query in the hundreds or even thousands of records can work well, but in production environment is not the case. For your inquiries, there is no better way to prepare, and only in a test environment in the table containing fragments of several millions of data for testing, in order to ensure that the query is run in accordance with your expectations. Check and reply.