DECLARE @Intage INT
2.Use User Defined Datatypes if a particular column repeats in multiple tables so that the datatype of that column is consistent across all our tables(UDT).
UDT_Intage
3.Use VARCHAR(Variying Length Type) instead of CHAR(Fixed Length Type)
4.Do not use column numbers in the ORDER BY clause.
Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 ORDER BY Column2
Do not Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 ORDER BY 2
5.SQL 92 Join should be used instead of SQL 89 Join.
Use - SQL 92
SELECT a.Column1,
a.Column2, b.Column3 FROM Tb_Table1 a JOIN Tb_Table2 B ON(a.Column1=b.Column1)
Do not Use - SQL 89
SELECT a.Column1, a.Column2, b.Column3 FROM Tb_Table1 a, Tb_Table2 B WHERE a.Column1=b.Column1
6.To make SQL statements more readable, start each CLAUSE on a new line and indent when needed.
SELECT Column1, Column2, Column3
FROM Tb_Table1
WHERE Column1=100
ORDER BY Column2
7.Unicode data type should not be used, only when they are absolutely needed(NCHAR,NVARCHAR)
8.Do not call functions repeatedly within your stored procedures, triggers, functions and batches.
9.Do not use 'SELECT *' in your queries. Always write the required column names in (Stored Procedures/Views/Normal Select)
Use
SELECT Column1, Column2, Column3 FROM Tb_Table1
Do not Use
SELECT * FROM Tb_Table1
10.LIKE Operator should not be used. Only when they are absolutely needed
Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 WHERE Column3 = 'SQLBuddy'
Do not Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 WHERE Column3 LIKE 'SQLBuddy'
11.Avoid searching using negative operators (<> and NOT) as they result in table and index scans.
Do not Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 WHERE Column3 <> 'SQLBuddy'
12.Perform all your referential integrity checks and data validations using constraints (foreign key and check constraints) instead of TRIGGERS.
13.Try to avoid cursors as much as possible. Use WHILE LOOP instead
14. Always access tables in the same order in all your stored procedures and triggers consistently. This helps in avoiding deadlocks.
15.Use UPPER case for table, view & Keywords.
Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 WHERE Column3 = 'SQLBuddy'
Do not Use
select Column1, Column2, Column3 from Tb_Table1 where Column3 = 'SQLBuddy'
16.A foreign key name should identify both tables participating in a foreign key, the column(s) involved in the relationship, and the direction.
17.Do not use alter procedure. Scripts should be structured so that procedures are dropped and recreated.
Use
IF OBJECT_ID('PROCEDURENAME','P') IS NOT NULL
DROP PROC PROCEDURENAME
GO
CREATE PROC PROCEDURENAME
...
...
Do not Use
ALTER PROC PROCEDURENAME
...
...
18.Use parenthesis to increase readability, especially when working with branch conditions or complicated expressions.
19.Use BEGIN…END blocks only when multiple statements are present within a conditional code segment.
Use
DECLARE @Variable INT
SELECT @Variable = 1
IF (@Variable = 1)
BEGIN
SELECT @Variable = 2
SELECT @Variable = @Variable + 2
SELECT @Variable = @Variable + 2
END
ELSE
SELECT @Variable = 10
20.When a result set is not needed, use the syntax.. IF EXISTS(......) Insteadof IF (SELECT COUNT())
Use
IF EXISTS(SELECT 1 FROM TABLE1)
PRINT 'Record Exists'
ELSE
PRINT 'Record Not Exists'
Do not Use
IF (SELECT COUNT(1) FROM TABLE1) >0
PRINT 'Record Exists'
ELSE
PRINT 'Record Not Exists'
21.Always list column names within an insert statement. Never perform inserts based on column position alone.
Use
INSERT TABLE1(Col1, Col2, Col3) VALUES ('A','B','C')
Do not Use
INSERT TABLE1 VALUES ('A','B','C')
22.Do not call a stored procedure during an insert.(Inserting record(s) based on the resultset of a stored procedure)
Do not Use
CREATE PROC USP_Proc1
(
@ID INT
)AS
BEGIN
SET NOCOUNT ON
IF (@ID <0)
SELECT 'Negative'
ELSE
SELECT 'Possitive'
END
Go
INSERT TABLE1(Col1)
EXEC USP_Proc1 -1
23.Avoid using Temp / derived tables, Use only CTE (Based on the statement Scope) (or) use table variables instead for single SELECT operations.
24.Avoid using table variables when JOIN with physical tables. Use Temp tables instead.
25.PRIMARY KEY column should be in WHERE clause of UPDATE/DELETE operations.
26.Avoid using SPACE or Special Characters except '_' in Database, Objects and Column names.
27.Do not use RESERVED words on Objects/columns.
28.Frequently required / complicated JOINs should be used inside the VIEWs.
29.Use NOLOCK table optimizer hint.
SELECT a.Column1, a.Column2, b.Column3 FROM Tb_Table1 a(NOLOCK) JOIN Tb_Table2 B(NOLOCK) ON(a.Column1=b.Column1)
30.Do not depend on undocumented functionality (May be Obsolated in next release)
31.Try not to use system tables directly. System table structures may change in a future release or Obsolete.
32.Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as that results in an index scan, which is defeating the purpose of having an index.
Use
ColumnName LIKE 'p%n'
Do not Use
ColumnName LIKE 'p%'
33.Be aware that the old style *= and =* left and right outer join syntax should be avoided even(It will make compatibility issue)
34.Always store 4 digit years in dates (especially, when using char or int datatype columns)
Use
DECLARE @IntDate INT
SELECT @IntDate = 2010
Do not Use
DECLARE @IntDate INT
SELECT @IntDate = 23
35.In queries and other SQL statements, always represent date in yyyy/mm/dd format. This format will always be interpreted correctly.
Use
DECLARE @Date DATETIME
SELECT @Date = '2010-07-23'
36.Do not use GO or use it Carefully
Do not Use
DECLARE @Date DATETIME
SELECT @Date = '2010-07-23'
GO
SELECT @Date = @Date + 1
36.Use UNION ALL insteadof UNION if possible(we can avoide DISTINCT and Worktables)
Use
SELECT Col1, Col2, Col3 FROM Tb_Table1
UNION ALL
SELECT Col1, Col2, Col3 FROM Tb_Table2
ELSE
PRINT 'Record Not Exists'
21.Always list column names within an insert statement. Never perform inserts based on column position alone.
Use
INSERT TABLE1(Col1, Col2, Col3) VALUES ('A','B','C')
Do not Use
INSERT TABLE1 VALUES ('A','B','C')
22.Do not call a stored procedure during an insert.(Inserting record(s) based on the resultset of a stored procedure)
Do not Use
CREATE PROC USP_Proc1
(
@ID INT
)AS
BEGIN
SET NOCOUNT ON
IF (@ID <0)
SELECT 'Negative'
ELSE
SELECT 'Possitive'
END
Go
INSERT TABLE1(Col1)
EXEC USP_Proc1 -1
23.Avoid using Temp / derived tables, Use only CTE (Based on the statement Scope) (or) use table variables instead for single SELECT operations.
24.Avoid using table variables when JOIN with physical tables. Use Temp tables instead.
25.PRIMARY KEY column should be in WHERE clause of UPDATE/DELETE operations.
26.Avoid using SPACE or Special Characters except '_' in Database, Objects and Column names.
27.Do not use RESERVED words on Objects/columns.
28.Frequently required / complicated JOINs should be used inside the VIEWs.
29.Use NOLOCK table optimizer hint.
SELECT a.Column1, a.Column2, b.Column3 FROM Tb_Table1 a(NOLOCK) JOIN Tb_Table2 B(NOLOCK) ON(a.Column1=b.Column1)
30.Do not depend on undocumented functionality (May be Obsolated in next release)
31.Try not to use system tables directly. System table structures may change in a future release or Obsolete.
32.Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as that results in an index scan, which is defeating the purpose of having an index.
Use
ColumnName LIKE 'p%n'
Do not Use
ColumnName LIKE 'p%'
33.Be aware that the old style *= and =* left and right outer join syntax should be avoided even(It will make compatibility issue)
34.Always store 4 digit years in dates (especially, when using char or int datatype columns)
Use
DECLARE @IntDate INT
SELECT @IntDate = 2010
Do not Use
DECLARE @IntDate INT
SELECT @IntDate = 23
35.In queries and other SQL statements, always represent date in yyyy/mm/dd format. This format will always be interpreted correctly.
Use
DECLARE @Date DATETIME
SELECT @Date = '2010-07-23'
36.Do not use GO or use it Carefully
Do not Use
DECLARE @Date DATETIME
SELECT @Date = '2010-07-23'
GO
SELECT @Date = @Date + 1
36.Use UNION ALL insteadof UNION if possible(we can avoide DISTINCT and Worktables)
Use
SELECT Col1, Col2, Col3 FROM Tb_Table1
UNION ALL
SELECT Col1, Col2, Col3 FROM Tb_Table2
Do not Use
SELECT Col1, Col2, Col3 FROM Tb_Table1
UNION
SELECT Col1, Col2, Col3 FROM Tb_Table2
37.When you have a choice of using the IN or the BETWEEN clauses in your Transact-SQL, you will generally want to use the BETWEEN clause, as it is more efficient.
Use
SELECT Col1, Col2, Col3 FROM TABLE1WHERE BETWEEN 1 AND 5
Do not Use
SELECT Col1, Col2, Col3 FROM TABLE1WHERE ID IN(1,2,3,4,5)
38.Don’t turn ON the CLR unless you will be using it
39.Standard data access, such as SELECT, INSERTs, UPDATEs, and DELETEs are best done via Transact-SQL code, not the CLR
40.Use the CLR for error handling, as it is more robust than what Transact-SQL offers
41.Use the CLR for string manipulation, as it is generally faster than using Transact-SQL
42.Use the CLR when you want to access external resources, such as the file system, Event Log, a web service, or the registry
43.When possible, use typed XML data types over untyped XML data to boost performance
44.Add primary and secondary indexes to XML columns to speed retrieval of xml data
45.Schedule large data imports, exports, or transformation during less busy periods of the day to reduce the impact on users
46.Table names should accurately reflect the table's content and function.
47.View names follow the same conventions as table names, but should be prefixed with the literal 'VW' or 'VW_'
CREATE VIEW VW_VIEW1 AS SELECT a.Col1, a.Col2, a.Col3 FROM TABLE1 a(NOLOCK) JOIN TABLE1 b(NOLOCK) ON (a.ID = b.ID)
48.Each table should have PRIMARY KEY
49.Table name should be Prefixed with 'TB_' (or) 'TB'
50.Alias name should be used for Tables when using JOINs
Use
SELECT a.Col1, b.Col22, b.Col33 FROM Tb_Table1 a(NOLOCK) JOIN Tb_Table2
b(NOLOCK) ON (a.ID = b.IDs)
Do not Use
SELECT Col1, Col22, Col33 FROM Tb_Table1(NOLOCK) JOIN
Tb_Table2(NOLOCK) ON (ID = IDs)
51.Primary key have a Prefix of 'PK_'
52.Foreign keys have a Prefix of 'FK_'
53.Clustered indexes have a Prefix of 'IDX_'
54.All other indexes have a Prefix of 'NDX_'
55.All application level and user defined stored procedures are prefixed with the constant 'USP' (or) 'USP_' with a description of what the stored procedure does.
CREATE PROC USP_Proc1
(
@TableID INT
)AS
BEGIN
SET NOCOUNT ON
SELECT a.Col1, b.Col22, b.Col33 FROM Tb_Table1 a(NOLOCK) JOIN Tb_Table2 b(NOLOCK)ON (a.ID = b.IDs) WHERE a.ID = @TableID
END
Go
56.Stored procedures should not be started as 'SP_' as the system stored procedures used in Master Database.
57.Use SET NOCOUNT ON at the beginning of your SQL batches, stored procedures and triggers
58.Do not use alter procedure. Scripts should be structured so that procedures are dropped and recreated.
59.Do not use temporary stored procedures (Local / Global)
CREATE PROC #USP_Proc1
AS
BEGIN
SET NOCOUNT ON
…
END
Go
CREATE PROC ##USP_Proc1
AS
BEGIN
SET NOCOUNT ON
…
END
Go
60.Do not create stored procedures that return multiple result sets (if realy required)
CREATE PROC USP_Proc1
AS
BEGIN
SET NOCOUNT ON
SELECT Col1, Col2, Col3 FROM TABLE1
SELECT Col1, Col2, Col3 FROM TABLE2
END
61.Never use the recompile option(Procedure / Statement level) as the procedure recompile every time.
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON
SELECT Col1 FROM TABLE1
END
CREATE PROC USP_Proc1
AS
BEGIN
SET NOCOUNT ON
SELECT Col1 FROM TABLE1 OPTION(RECOMPILE)
END
62.Fully qualify all stored procedure and table references in all stored procedures
USE DatabaseName
Go
IF OBJECT_ID('
DROP PROC
GO
CREATE PROC
(
Input Parameter,
Output Parameter,
@ErrorLog BIT = 0
)AS
BEGIN
/********************Modification History**********************/
/* Created By : Pandian S */
/* Created On : 23-July-2010, Fri */
/* Tables Used : TB_Table1, TB_Table2,... */
/* DB Used : DB01, DB2,... */
/* Description : Template of Stored Procedure */
/*********************************************************/
SET NOCOUNT ON
BEGIN TRY
BEGIN TRAN
COMMIT
END TRY
BEGIN CATCH
IF (@ErrorLog=1)
BEGIN
DECLARE @ErrorMessage NVARCHAR(100)
SELECT @ErrorMessage = 'Error Line # :' + CAST(ERROR_LINE() AS NVARCHAR) + ', ' + ERROR_MESSAGE()
EXEC XP_LOGEVENT 60001,@ErrorMessage,'Error'
END
ROLLBACK TRAN
END CATCH
END
Go
63.Place all declare statements before any other code in the procedure to give the query optimizer the best shot at reusing query plans.
64.Place SELECT statements before any executing code in the procedure. (variable Assigning operations)
65.Always create stored procedures in same Database.
66.Write comments in your stored procedures, triggers and SQL batches generously. Don't worry about the length of the comments, as it won't impact the performance.
67.SET statements should not be used (Except NOCOUNT, ROWCOUNT) inside the Procedures. Use SELECT instead.
i.e: SET is an environment setting(Session specific) statement, We can not use multiple assignments on single line(We have to assign each value in seperate line), We can not assign a value directly from a table(without SELECT clause)
DBA Recommendations :
68.Monitor disk space to ensure your SQL Servers won’t run out of disk space.
69.Throughout the day, periodically monitor performance using System Monitor, Profiler, Activity monitor, Client statistics and DMVs.
70.Keep a log of any changes you make to servers, including documentation of any performance issues you identify and correct.
71.Create SQL Server alerts to notify you of potential problems, and have them emailed to you. Take actions as needed
72.If you upgrade from a previous version of SQL Server, you should update all of the statistics in all your databases. (Because statistics are not automatically updated during the upgrade process)
73.When possible, use Windows Authentication logins instead of SQL Server logins.
74.Don’t grant permissions to the public database role.
75.When using distributed queries, use linked servers instead of remote servers.
76.At least every two weeks, Check all the databases and data integrity.
77.Avoid running most DBCC commands during busy times of the day. These commands are often I/O intensive and can reduce performance of the SQL Server, negatively affecting users
78.In almost all cases, leave the "auto create statistics" and "auto update statistics" options on for all user databases.
79.Databases need to be shrunk periodically in order to free up disk space as older data is deleted from the database. But don’t be tempted to use the "auto shrink" database option, as it can waste SQL Server resources unnecessarily. Instead, SHRINK databases manually.
80.Regularly monitor your SQL Servers for BLOCKED transactions
81.Regularly monitor system performance.
82.Periodically, run the DTA against current Load File to identify potentially missing indexes and Remove indexes that are never used
83.Don’t REINDEX your tables when your database is in active production, as it can lock resources and cause your users performance problems
84.REINDEXing should be scheduled during down times, or during light use of the databases
85.Don’t accidentally create redundant indexes.
86.If possible, avoid adding a clustered index to a GUID column (uniqueidentifier data type). GUIDs take up 16-bytes of storage, more than an Identify column, which makes the index larger, which increases I/O reads, which can hurt performance.
87.Indexes should be considered on all columns that are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.
88.A high fill factor is good for rarely changed data, but highly modified data needs a lower fill factor to reduce page splitting. So we can avoid unwanted fragmentation on Tables/Indexes.
89.Use covering index, which is a form of a composite index, includes all of the columns referenced in SELECT, JOIN, and WHERE clauses of a query. It will reduce the Physical and Logical I/O
90.Periodically, Check the Fragmentation and take action accordingly.
/* DB Used : DB01, DB2,... */
/* Description : Template of Stored Procedure */
/*********************************************************/
SET NOCOUNT ON
BEGIN TRY
BEGIN TRAN
COMMIT
END TRY
BEGIN CATCH
IF (@ErrorLog=1)
BEGIN
DECLARE @ErrorMessage NVARCHAR(100)
SELECT @ErrorMessage = 'Error Line # :' + CAST(ERROR_LINE() AS NVARCHAR) + ', ' + ERROR_MESSAGE()
EXEC XP_LOGEVENT 60001,@ErrorMessage,'Error'
END
ROLLBACK TRAN
END CATCH
END
Go
63.Place all declare statements before any other code in the procedure to give the query optimizer the best shot at reusing query plans.
64.Place SELECT statements before any executing code in the procedure. (variable Assigning operations)
65.Always create stored procedures in same Database.
66.Write comments in your stored procedures, triggers and SQL batches generously. Don't worry about the length of the comments, as it won't impact the performance.
67.SET statements should not be used (Except NOCOUNT, ROWCOUNT) inside the Procedures. Use SELECT instead.
i.e: SET is an environment setting(Session specific) statement, We can not use multiple assignments on single line(We have to assign each value in seperate line), We can not assign a value directly from a table(without SELECT clause)
DBA Recommendations :
68.Monitor disk space to ensure your SQL Servers won’t run out of disk space.
69.Throughout the day, periodically monitor performance using System Monitor, Profiler, Activity monitor, Client statistics and DMVs.
70.Keep a log of any changes you make to servers, including documentation of any performance issues you identify and correct.
71.Create SQL Server alerts to notify you of potential problems, and have them emailed to you. Take actions as needed
72.If you upgrade from a previous version of SQL Server, you should update all of the statistics in all your databases. (Because statistics are not automatically updated during the upgrade process)
73.When possible, use Windows Authentication logins instead of SQL Server logins.
74.Don’t grant permissions to the public database role.
75.When using distributed queries, use linked servers instead of remote servers.
76.At least every two weeks, Check all the databases and data integrity.
77.Avoid running most DBCC commands during busy times of the day. These commands are often I/O intensive and can reduce performance of the SQL Server, negatively affecting users
78.In almost all cases, leave the "auto create statistics" and "auto update statistics" options on for all user databases.
79.Databases need to be shrunk periodically in order to free up disk space as older data is deleted from the database. But don’t be tempted to use the "auto shrink" database option, as it can waste SQL Server resources unnecessarily. Instead, SHRINK databases manually.
80.Regularly monitor your SQL Servers for BLOCKED transactions
81.Regularly monitor system performance.
82.Periodically, run the DTA against current Load File to identify potentially missing indexes and Remove indexes that are never used
83.Don’t REINDEX your tables when your database is in active production, as it can lock resources and cause your users performance problems
84.REINDEXing should be scheduled during down times, or during light use of the databases
85.Don’t accidentally create redundant indexes.
86.If possible, avoid adding a clustered index to a GUID column (uniqueidentifier data type). GUIDs take up 16-bytes of storage, more than an Identify column, which makes the index larger, which increases I/O reads, which can hurt performance.
87.Indexes should be considered on all columns that are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.
88.A high fill factor is good for rarely changed data, but highly modified data needs a lower fill factor to reduce page splitting. So we can avoid unwanted fragmentation on Tables/Indexes.
89.Use covering index, which is a form of a composite index, includes all of the columns referenced in SELECT, JOIN, and WHERE clauses of a query. It will reduce the Physical and Logical I/O
90.Periodically, Check the Fragmentation and take action accordingly.
No comments:
Post a Comment