Новости проекта

Best SQL Practices

27.08.2010

Although this is not an exhaustive list of best practices, these suggestions may improve procedure performance.

  • Use the SET NOCOUNT ON statement as the first statement in the body of the procedure. That is, place it just after the AS keyword. This turns off messages that SQL Server sends back to the client after any SELECT, INSERT, UPDATE, MERGE, and DELETE statements are executed. Overall performance of the database and application is improved by eliminating this unnecessary network overhead. For information, see SET NOCOUNT (Transact-SQL).

  • Use schema names when creating or referencing database objects in the procedure. It will take less processing time for the Database Engine to resolve object names if it does not have to search multiple schemas. It will also prevent permission and access problems caused by a user’s default schema being assigned when objects are created without specifying the schema. For more information, see User-Schema Separation.

  • Avoid wrapping functions around columns specified in the WHERE and JOIN clauses. Doing so makes the columns non-deterministic and prevents the query processor from using indexes.

  • Avoid using scalar functions in SELECT statements that return many rows of data. Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.

  • Avoid the use of SELECT *. Instead, specify the required column names. This can prevent some Database Engine errors that stop procedure execution. For example, a SELECT * statement that returns data from a 12 column table and then inserts that data into a 12 column temporary table will succeed until the number or order of columns in either table is changed.

  • Avoid processing or returning too much data. Narrow the results as early as possible in the procedure code so that any subsequent operations performed by the procedure are done using the smallest data set possible. Also, sending just the essential data to the client application is more efficient than sending extra data across the network and forcing the client application to work through unnecessarily large result sets.

  • Use explicit transactions by using BEGIN/END TRANSACTION and keep transactions as short as possible. Longer transactions mean longer record locking and a greater potential for deadlocking. For more information, see Locking and Row Versioning, Lock Compatibility (Database Engine), or Isolation Levels in the Database Engine.

  • Avoid using a wildcard as the leading character in a LIKE clause, for example, LIKE ‘%a%’. Because the first character is non-deterministic, the query processor is unable to use available indexes. Use LIKE ‘a%’ instead.

  • Use the Transact-SQL TRY…CATCH feature for error handling inside a procedure. TRY…CATCH can encapsulate an entire block of Transact-SQL statements. This not only creates less performance overhead, it also makes error reporting more accurate with significantly less programming. For more information, see Using TRY...CATCH in Transact-SQL.

  • Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQL statements in the body of the procedure. This will prevent passing NULL to columns that do not allow null values.

  • Use NULL or NOT NULL for each column in a temporary table. The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way the Database Engine assigns the NULL or NOT NULL attributes to columns when these attributes are not specified in a CREATE TABLE or ALTER TABLE statement. If a connection executes a procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behavior. If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created by using the same nullability for all connections that execute the procedure.

  • Use IF EXISTS (SELECT TOP 1 FROM table_name) instead of IF EXISTS (SELECT * FROM table_name). Because the output of the SELECT statement is never , design the statement so that it presents a Boolean condition; at least one value exists or no values exist. This will find only the first match, which sets the Boolean condition to true, and the search stops. Otherwise, it searches for the entire data structure if you do not use the TOP operator or you select all rows. Avoiding the selection of a complete result set reduces processing and network overhead.

  • Use modification statements that convert nulls and include logic that eliminates rows with null values from queries. Be aware that in Transact-SQL, NULL is not an empty or “nothing” value. It is a placeholder for an unknown value and can cause unexpected behavior, especially when querying for result sets or using AGGREGATE functions. For more information, see NULL Comparison Search Conditions and Null Values.

  • Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values. The UNION ALL operator requires less processing overhead because duplicates are not filtered out of the result set.

Архив новостей

 
 
© 1994—2022 "ФИНАМ"
АО «ФИНАМ». Лицензия на осуществление брокерской деятельности №177-02739-100000 от 09.11.2000 выдана ФКЦБ России без ограничения срока действия.
АО «Банк ФИНАМ». Лицензия на осуществление брокерской деятельности № 045-02883-100000 от 27.11.2000 выдана ФКЦБ России без ограничения срока действия.

Информация, публикуемая пользователями на сайте (в блогах, комментариях, чате, сообществах и т.д.) является личным (субъективным) мнением, суждением или выражением конкретного пользователя и не может являться основополагающей для принятия инвестиционных решений (покупки и/или продажи ценных бумаг) или побуждающей совершить какие-либо юридические или фактические действия. Администрация сайта не несёт ответственности за принятые решения, основанные на такой информации, а также в случае если такая информация направлена на возбуждение ненависти и/или вражды по отношению к каким-либо группам лиц, народам каких-либо национальностей, расам и каким-либо другим социальным группам и категориям лиц.