T-SQL Coding, Naming and Formatting Standards for MS SQL Server Administrators and Developers
Pascal notation can be used naming SQL Server objects like databases, tables,
columns, views, stored procedures, user-defined functions, etc.
For instance, SalesOrders or CustomerAppliances, etc.
I prefer to use plural names for tables and views because they are actually
containers of a group of same type rows.
For instance you can give the name "Cities" instead of "City" to name a table
which contains city definitions in a country. Or as an other example, give the
name "Books" and "Authors" instead of preferring "Book", "Author" as table
names.
Although you can define database objects names with containing spaces between
square brackets "[...]", do not prefer this way unless you have to use spaces in
SQL Server object names, like table names or column names.
For example, do not prefer [Complaint Note] where you can use ComplaintNote as a
column name.
If you have to name a column with SQL Server specific special names that are
called reserved words, use square
brackets around the column name. Why I use brackets although there is no space
in the column name is to distinguish the column name from SQL Server reserved
words. Also SQL Server Management Studio 2008 (SSMS) automatically colors the
reserved words, commands, etc. So using brackets will not display the column
name as a reserved word.
For instance, if a column name is "Description" in the table you are executing a
SELECT query, prefer the following syntax:
SELECT CategoryId, [Description] FROM Categories
If you are creating a group of SQL Server objects in order to be used in a
process, you can use a short abbreviation at the beginning of the objects'
names.
For example, recently I have design a SQL Server application which is used to
find dublicate customers according to some criterias, and merge customers'
various records like appliances, addresses, contacts, orders, etc. I call this
process a SQL application since all the objects from tables to SQL jobs are all
working on MS SQL Server 2008 databases. I preferred to name this application as
Customer Merge System and used the CMS as abbreviation of this process. So I
added the "CMS_" before all object names like CMS_Criterias,
CMS_DublicateRecords, CMS_DeletedRecords, etc.
Write SQL commands, keys, built-in functions in upper cases. I've developed writing the SQL
commands and keys like SELECT, UPDATE, INSERT, DELETE, FROM, INNER JOIN, LEFT
JOIN, ON, WHERE, AND, OR, IN, LIKE, etc all in upper cases. I get used to capitalise
these words.
For example you can write your t-sql commands in the following form:
SELECT
FirstName, LastName, GETDATE(), OBJECT_ID(N'Customers')
FROM Customers
WHERE
FirstName LIKE 'A%'
Using Table Name Aliases or Avoding Aliasing
If I'm planning to code a t-sql query that joins tables, I prefer aliasing
tables with short aliases but powerful to aliase its real table name. I avoid
aliasing where aliases adds to complexity of the query where its usage should
increase the simplicity. In such cases where aliases should be avoided, I use
the table names before each column name.
Do not forget to identify the table of each column in a query, no matter you use
table name or table name aliases. If you do not care this rule, during
development of your applications two tables that are in the FROM part of your
query may be altered to have columns sharing the same name which will cause your
query to fail.
For example, Orders table has Note column but Customers table does not, then the
below query will execute without an error.
SELECT
CustomerId, FirstName, LastName, OrderNumber, Note
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId
But if a SQL developer alters the Customers table and adds a Note columns, then
the above query will fail since both table have the same column name and sql
engine will not be able to identify from which table it should read the column
value.
As an example for aliasing, you can refer to the following two sql select
statements. You can prefer one to the other, but your aim should be making the
query readable.
I've developed a readable and easily understandable query structure using tabs,
indentation,
grouping objects, without hesitating to extend the query text to new lines.
Because it is not bad how much line your query is, but it makes easier and
simpler for you to maintain and someone else to understand what your query is
used for.
For example, I write the column names side by side if they are less then 5 in a
SELECT query, and I place them one line below the SELECT command with an indent. I also place
the FROM one line below the selected column names. If I have joined tables I
place the JOIN statements on a line reserved for them and place the ON and the
following joins following.
SELECT
Customers.CustomerId, Customers.FirstName, Customers.LastName, Customers.HomePhone,
Addresses.AddressDetails
FROM Customers
INNER JOIN Addresses ON Customers.HomeAddressId = Addresses.AddressId
Sometimes in complex queries where the count of lines are a few thousands and you are placing lines of comments, I prefer the following syntax.
SELECT
C.CustomerId,
C.FirstName,
C.LastName,
C.HomePhone,
A.AddressDetails
FROM Customers C
INNER JOIN Addresses A
ON C.HomeAddressId = A.AddressId
Always use column names in INSERT statements. Do not prepare a t-sql INSERT
statement like below. Because the below statement is just like a timer-bomb that
will cause an error when a new column is added or an existing one is dropped
from the related database table.
INSERT INTO Categories VALUES (N'Comedy', N'Comedy', NULL)
Use the following t-sql INSERT syntax instead:
INSERT INTO Categories (
CategoryName, [Description], Picture
) VALUES (
N'Drama', N'Drama', NULL
)
If you are creating scripts of your data manupulation (DML) commands or data
definition (DDL) commands, surround your DML or DDL commands with check and
control conditions. I mean if you are going to add a column to a database table,
write your DDL script so that it should check whether or not a column with the
same name exists in the related SQL Server database table. For example:
GO
IF NOT EXISTS (
SELECT *
FROM sys.columns
WHERE
name = N'Producer' and
object_id = OBJECT_ID(N'Movies')
)
ALTER TABLE Movies ADD Producer nvarchar(256)
GO
For such controls system tables or system views like sys.tables, sys.columns,
sys.indexes are perfect to use with EXISTS, NOT EXISTS and OBJECT_ID() system
function.
|