April 2011 – sqlforge

Overall conventions

– Keep names short as possible, but long enough to be meaningful
– Reserve ALL CAPS only for SQL keywords such as SELECT, UPDATE, INSERT, WHERE, JOIN, AND, OR, LIKE
– Be generous in use of comments, but not distracting
– Do not use spaces in object names

Tables

– A 1-M or M-M named with the primary table name, an underscore, and the dependent table name (ie: user_permissions)
– A “logging” table can have a suffix of ‘log’ as in “userlog”, whereas a an “archive” table with the same structure as a “live” table can be named with an “_archive” suffix as “user_archive”.
– Name a primary key (or in the event of a compound PK create a surrogate identity or guid key) named after the table (ie: users.user_id). Makes it easier to match up foreign keys to primary keys.
– All tables should have some form of primary key, having a surrogate identity or guid key makes it easier for the application to reference specific rows.
– Except for the primary key, don’t repeat the table name in the column name (ie: users.username -vs- users.name). This makes for annoying long and redundant column names.
– Table and column names should typically single words, be all lower case, and use underscores sparingly (with the exception of the primary key “_id” column). If a column name is multiple words then run the words together such as “lastname” or if it is an commonly understandable abbreviation “lname”. Another possible convention is to begin with lowercase and use title case for remaining words (ie: lastName).
– Avoid using reserved words for table and column name
– For transaction databases (OLTP) target 3rd normal form for normalization. Is best to normalize as far as possible to best support transactions. If you don’t need a full blown counterpart OLAP database, then can add some “rollup” or “aggregation” tables to support long running reports.
– Most “entity” and “lookup/list of value” Table names should end in plural ‘s’ (ie: users, accounts, locations) as a table is a collection of items as opposed to an instance of an item.

Constraints/Indexes

– Primary key index should be “PK_tablename”
– Foreign key index should be “FK_dependenttable_ref_primarytable”
– Alternate key indexes should be “AK_tablename” (for surrogate identity or guid field in the event of a compound PK)
– For all other indexes start with “IX_tablename” or “IX_tablename_colname”, if unique append a “_u” suffix, if clustered append a “_c” suffix, if clustered-unique append a “_cu” suffix
– Defaults are named “DF_tablename_columname”

Triggers

– Triggers should be used VERY sparingly
– Triggers should be named “TR_tablename” or “TR_tablename_columname”, and append a suffix of “_i” for insert, “_u” for update, “_d” for delete or some combination thereof (ie: “_iu”, “_iud”)
– Perform referential integrity checks and data validations using constraints instead of triggers, as they are faster. Limit the use of triggers only for auditing, custom tasks, and validation that cannot be performed using constraints and would be impractical to enforce in stored procedures.

Other Object Names

– Use “Pascal” notation for objects with a meaningful prefix (ie: vUserDetails, fnUsersGetDetails)
– Recommend prefix of “v” for views
– Recommend prefix of “fn” for functions, or could even go with “ft” for table-valued functions, “fn” for scalar-valued functions, “fa” for aggregate functions

Stored Procedures

– DO NOT use “sp_” as a prefix for stored procedures, unless you are specifically creating a user defined system-wide stored procedure. This is a naming convention reserved for system procedures.
– Use SET NOCOUNT ON at the beginning of stored procedures and triggers
– If you use a prefix for a stored procedures (sp), recommend using something like “p” for process, “r” for report, “u” for utility, etc
– When supporting an application that relies entirely on stored procedures (a highly recommended configuration btw!), it is helpful to incorporate the name of the module into the name and what function the sp suports (ie: pUserGetDetails, pUserSetDetails, rManningStatus)
– Typical action descriptions might include Get, Delete, Update, Write, Archive, Insert, Log along with the primary entity being impacted by the action
– Depending on scale you may choose one of the following conventions to take advantage of the alphabetical sorting in the tree view: uAction
– utility procedures are typically run by database-side administrators or an automated process pModuleAction
– supporting a single application, module could, for example, be the name of the .aspx page the sp supports (ie: pAccountEditGetUser, pAccountEditAddUser, pAccountEditDeleteUser) pApplicationModuleAction
– if your database supports multiple interfaces, then you may want to include the application name

Jobs

– Jobs should be named with project mnemonic name in all caps and brackets and then something indicating in general what it does (ie: “[PROJECT] WeeklyDataPull”)

General SQL Tips

– (NOLOCK) query optimizer hint can (and likely should) be used on most all SELECT statements
– In many cases EXCEPT or NOT EXIST clauses can be used more efficiently in place of LEFT JOIN or NOT IN
– In SQL Server Management Studio, turn on the “Include Actual Query Plan” and run the query. The query plan analyzer often suggests indexes.
– Do not call the same functions repeatedly, instead call them once and store the value in a variable
– Always access tables consistently in the same order in all stored procedures and triggers to avoid deadlocks.
– Use unicode data types like nchar, nvarchar, and ntext sparingly as they use twice as much space as non-unicode data types.
– Use a specific size instead of varchar(max) and nvarchar(max), as they are treated as out of row TEXT blobs and not as efficient in searching as using a specific size (except for instances where a text blob is truly appropriate)
– Minimize use of NULL where possible as they require additional complexity in queries to deal with
– Joins and common elements of complex queries that are used in multiple locations should be incorporated into a view.
– Avoid using ‘*’ in queries, always use explicit column names in your permanent code
– Be sure to explicitly DROP #temp tables and DEALLOCATE cursors as soon as they are not needed
– Using Common Table Expressions (CTE) are often (but not always) more efficient than using #temp tables and recursive functions
– Using @table variables for small amounts of data are often (but not always) more efficient than using #temp tables and cursors
– If you do not need a result set for a query, use a constant (ie: IF EXISTS ( SELECT TOP 1 1 FROM users WHERE id = 10 ) )
– Indentation and capitalization is KEY to readability:

--
   SELECT A.col1
        , A.col2
        , B.col3
     FROM tabname1 A (NOLOCK)
    INNER JOIN tabname2 B (NOLOCK)
       ON A.col1 = B.col1
    WHERE A.col3 = 1
 ORDER BY A.col2

Month: April 2011

Memory Status

sp_MSforeachtable

SQL Naming Conventions