Transcript SQL Server Coding Standards and Best Practices

T-SQL
Coding Standards And Best Practices
Gogula G. Aryalingam
http://dbantics.wordpress.com
Rationale
 Witness to totally messed up databases
 Perspectives of a lot of developers regarding SQL
Server (or any database system):
 Hostility towards SQL Server (the database system)
 High priority to the client application
 Back-end nature of SQL Server (the database system)
 Any more reasons why you do not like SQL Server?
Developers:
 What Do You Do with SQL Server?
Object Naming Conventions
Object Naming Conventions
 Why naming conventions?
 Reduces the effort needed to read and understand
 Increases the fluency in identifying objects
 Do you use naming conventions?
Object Naming Conventions
 Tables:
 Use upper camel case
 Do not use underscores to separate words
 Use the plural form of nouns
 E.g.: EmailAddresses, Customers, SalesInvoices
 For relationship tables combine the related tables names
 E.g.: StudentsCourses
 Consider using Schemas (in SQL Server 2005 and later) to
group related objects (in databases with large structures)
 Suggestions?
Object Naming Conventions
 Field Names:
 Use upper camel case
 Do not use underscores to separate words
 Use the singular form of nouns
 E.g.: FirstName, DateOfBirth
 Using the data type as a prefix is not considered a best
practice anymore
 For identity column names use table name suffixed with ‘ID’
 Suggestions?
Object Naming Conventions
 Other SQL Server objects
 Views: vwSalesSummary2007
 Indexes: IX_Customers_SocialSecurityNumber
 Stored Procedures:
 Do not prefix with sp_
 Names based on functionality:
 Get: GetSalesInvoices
 Insert: InsertCustomers
 Update: UpdateCourses
 References:
 http://vyaskn.tripod.com/object_naming.htm
 http://www.cms.hhs.gov/dbadmin/downloads/sqlserverstandardsandguildelines.
pdf
Prettifying the Horrifying
Horrifying
 Take a look at this piece of code:
Prettifying the Horrifying
 Why prettify?
 Readability by others
 Readability by self
 Results in:
 Ease of debugging and modifying of code
Prettifying the Horrifying
 How to prettify?
 Indents
 Comments
 Upper case for keywords
 Shortening lines
 Use square braces for table/view names and column
names
 Use table aliases using
 Use tabs instead white spaces
 Suggestions?
Standards for Creating Tables
Standards for creating tables
 Make sure all tables are in the 3rd Normal form
 Primary keys for unique row identification
 Choosing a primary key
 Natural key vs. Surrogate key
 Natural keys have a tendency to change
 An integer identity (surrogate) column is the best suited
 [UserID] int IDENTITY(1,1)
 Avoid GUID/UNIQUEIDENTIFIER data types for primary key
 Reference:
 http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-
key.html
Standards for creating tables
 Choose data types with the minimal size as possible
 Use Unicode data types only if it is needed
 Make sure data integrity is applied
 Primary keys, Foreign keys, Check, Default and Unique
constraints
 Keep in mind the 8060 B row size rule
 Choose
varchar(max), nvarchar(max)
over text, ntext and image
and varbinary(max)
Standards for creating tables
 Avoid storing BLOBs in tables especially if there is
constant access
 Alternatively:
 Store the path in the table and the data in files
 Store the data in a varbinary(max) field using FILESTREAM
(SQL Server 2008)
Best Querying Practices
Querying Tips
 Rather than SELECT * FROM …
use SELECT [col1],… [coln] FROM …
Select only the columns that are required for output
 Use Common Tables Expressions (CTEs) wherever possible
instead of temporary and derived tables
 SET NOCOUNT ON within batches and stored procedures to
increase on performance
Querying Tips
 Avoid cursors as much as possible
 Alternatively use:
 Set based approach to update or insert data from one tables
to another
 Tables variable and While loop (suited for small result sets)
 Wild card characters at the beginning of a phrase in
the LIKE clause should be avoided
 WHERE [Name] LIKE ‘%Powell’
 Refer to table names with schema name prefixed
 … FROM [HumanResources].[Employee]
Querying Tips
 Prefix column names with table name or alias
 SELECT Employee.[Name],
Contact.[Address]
FROM …
 Avoid using functions on columns in the WHERE clause
 WHERE UPPER([Name]) = ‘BARBIE’
 Declare all variables and initialize values at the beginning
of the code (Makes the query optimizer reuse plans)
 When checking for existence of records, simply use
IF EXISTS(SELECT * FROM dbo.Employees).
It does not return a result set, hence is fast.
Querying Tips
 Avoid dynamic SQL
 Try to find alternatives that do not constitute of dynamic SQL.
 If at all using dynamic SQL, use sp_executesql instead of
EXECUTE (EXEC)
 When testing query performance using the graphical
execution plan, look for Index seeks over Index scans or
Table scans.
 When performing Inserts, use column list in the INSERT
clause:
 INSERT INTO ([Name], [Age], [Address])
VALUES (‘Neil’, 32, ‘Hendala Junc.’)
Querying Tips
 Place all data access tasks in SQL Server itself.
Avoid queries and data manipulations on the client
app/business tier.
 Use stored procedures
 Reference:
 http://blog.sqlauthority.com/2008/09/25/sql-server-
guidelines-and-coding-standards/
 Suggestions?
Trigger Mania
Trigger Mania
 Perform all referential and domain integrity rules using
constraints
 Avoid using triggers for this purpose (poor performance)
 Use only if cannot be implemented by constraints
 Avoid triggers for business functionality
 Less visible
 Can avoid indirect recursion problems
 Alternatively use stored procedures
 Triggers can be used for tasks such as auditing and custom
validations
Trigger Mania
 When writing triggers
 Write for a recordset rather than for a single record
 Suggestions?
Q&A
Q1
 You have an SQL Server 2008 database. You need to
load data from one table to another. New records will
have to be added, existing records need to be
updated and records not in the source should be
deleted from the destination.
 How would you perform this with best performance?
 Using the MERGE statement
Q2
 You use an SQL Server 2005 database. You need to
store map images which are between 50MB and
100MB in size. What is the optimum method to
perform the storage?
 Store the images in the file system and the file path in an
SQL Server table
Q3
 What alternatives can be used for cursors?
 Set based operations
 Table variables and WHILE loop (for small data sets)
Q4
 Name some Best Practices for triggers.
 Avoid using business functionality within triggers
 Code for record sets rather than single records
 Best suited for auditing and other custom tasks
Thank you
 E-mail:
 Twitter:
 Blog:
 Web:
[email protected]
http://twitter.com/gogula
http://dbantics.wordpress.com
http://sqlserveruniverse.com