Optimizing the performance of T-SQL script in SQL Server
Page 1 of 4
The performance of a gift influenced by several factors and one of them is definitely the time it takes to process SQL Server T-SQL queries (queries) that makes the application itself. Sometimes it is the database structure that influences the speed of queries, other is the way they are written instructions to have a negative impact on performance. In this case the rewriting appropriate T-SQL SQL Server helps the engine to optimize performance.
There are suggestions of writing T-SQL query (as well as other devices) that, if applied, result in an improvement in their speed of execution and thus the performance of applications that use them and then we will see some.
Explicitly write the name of the columns in the SELECT
It will happen at all to write a query like this
SELECT * FROM [table_name]
Using the asterisk indicates the engine of our database that we want to see all the columns of the table in the FROM clause. However it is not a good practice, even if the objective is to obtain the values ​​of all columns. It 'always better to explain it the name of the columns
SELECT id, code, description, DataModifica FROM [table_name]
Explicitly write the name of the columns has several advantages: First, SQL Server returns only the data that our application needs, and does not report additional data that may not be used, returning only the data required will reduce the workload of SQL Server and finally in this way reduces the network load (in terms of bytes transferred) in the receipt of the query results.
Another aspect not to be underestimated is that clearly the name of the columns in a sense we isolate our application from potential future problems associated with changing the schema of the tables from which data is extracted. In fact, if you use the asterisk, and one day someone decided to change the table to be interrogated present in our application it would not budgeted to receive additional information at the time of its implementation. This in some cases could lead to the occurrence of unhandled errors. If, however, the application queries are listed in the column names will never come this problem (unless of course the schema of the table is deleted a column in the SELECT list of columns).
List the names of the columns in INSERT
As in the previous case also in the instructions for entering data (INSERT) in a table is a good idea to explicitly identify the name of the columns to be enhanced.
In fact, if we write an INSERT statement like this
TabellaLettere INSERT INTO VALUES ('A', 'B', 'C')
SQL Server will expect that the table in question consists of only three columns. If for any reason you add a column to the table by executing this instruction you will get the following error
Column name or number of supplied values ​​does not match table definition.
If instead we write the statement as follows
INSERT INTO TabellaLettere (First, Second, Third) VALUES ('A', 'B', 'C')
are added to the table even if one or more columns, it will continue to be successful.
 |
MS Access Course
Learn how to create and manage databases easily and quickly. Discount -10% until 06/01/2012. |
 |
Course MySQL
Management of open-source database. -15% Discount until 06/01/2012. |
 |
Course Database and SQL
Creating and managing relational databases. -15% Discount until 06/01/2012. |
