SQL naming conventions

SQL naming conventions for tables, and all the associated objects such as indexes, constraints, keys and triggers, are important for teamwork. Poorly-named tables and other objects make it difficult to maintain databases.

Table names must follow the rules for SQL Server identifiers, and be less than 128 characters. It is possible to force SQL Server to accept non-standard table names by surrounding them with square brackets but it is a very bad idea, because they have to be ‘quoted’ whenever they are used in scripts.

Temporary table names are slightly different in that they are prefixed with a single number sign (#) and are limited in length to 116 characters. After any prefix with a special meaning (‘@’ meaning a table variable, ‘#’ meaning a temporary table or ‘##’ meaning a global temporary table), the first letter must be a letter as defined by Unicode Standard 3.2. This means that it is either a Latin character from A through Z, upper or lower case, or else a letter character from other languages. Subsequent characters can legally be

• Letters as defined in the Unicode Standard 3.2.,
• Decimal numbers from either Basic Latin or other national scripts,
• The ‘at sign’(@) , the ‘dollar sign’ ($), the ‘number’ or ‘hash sign’ (#)
• The underscore, normally used to represent spaces such as Overdue_Account.

Never use spaces, embedded characters or reserved names, because they aren’t portable, require square brackets, and can confuse scripts and procedures.

We can test for compliance with SQL Server identifier spec very simply with the following SQL.

SELECT name FROM sys.objects 
   WHERE name LIKE '%[^_A-Z0-9@$#]%' COLLATE Latin1_General_CI_AI
 --contains illegal characters
   OR name NOT LIKE '[A-Z]%' COLLATE Latin1_General_CI_AI
 --doesn't start with a character

We can check for reserved words in objects in this slightly bulky but efficient code

SELECT name
  FROM sys.objects
    INNER JOIN
      (
      VALUES ('ADD'), ('EXTERNAL'), ('PROCEDURE'), ('ALL'), ('FETCH'),
        ('PUBLIC'), ('ALTER'), ('FILE'), ('RAISERROR'), ('AND'),
        ('FILLFACTOR'), ('READ'), ('ANY'), ('FOR'), ('READTEXT'), ('AS'),
        ('FOREIGN'), ('RECONFIGURE'), ('ASC'), ('FREETEXT'), ('REFERENCES'),
        ('AUTHORIZATION'), ('FREETEXTTABLE'), ('REPLICATION'), ('BACKUP'),
        ('FROM'), ('RESTORE'), ('BEGIN'), ('FULL'), ('RESTRICT'), ('BETWEEN'),
        ('FUNCTION'), ('RETURN'), ('BREAK'), ('GOTO'), ('REVERT'), ('BROWSE'),
        ('GRANT'), ('REVOKE'), ('BULK'), ('GROUP'), ('RIGHT'), ('BY'),
        ('HAVING'), ('ROLLBACK'), ('CASCADE'), ('HOLDLOCK'), ('ROWCOUNT'),
        ('CASE'), ('IDENTITY'), ('ROWGUIDCOL'), ('CHECK'), ('IDENTITY_INSERT'),
        ('RULE'), ('CHECKPOINT'), ('IDENTITYCOL'), ('SAVE'), ('CLOSE'), ('IF'),
        ('SCHEMA'), ('CLUSTERED'), ('IN'), ('SECURITYAUDIT'), ('COALESCE'),
        ('INDEX'), ('SELECT'), ('COLLATE'), ('INNER'),
        ('SEMANTICKEYPHRASETABLE'), ('COLUMN'), ('INSERT'),
        ('SEMANTICSIMILARITYDETAILSTABLE'), ('COMMIT'), ('INTERSECT'),
        ('SEMANTICSIMILARITYTABLE'), ('COMPUTE'), ('INTO'), ('SESSION_USER'),
        ('CONSTRAINT'), ('IS'), ('SET'), ('CONTAINS'), ('JOIN'), ('SETUSER'),
        ('CONTAINSTABLE'), ('KEY'), ('SHUTDOWN'), ('CONTINUE'), ('KILL'),
        ('SOME'), ('CONVERT'), ('LEFT'), ('STATISTICS'), ('CREATE'), ('LIKE'),
        ('SYSTEM_USER'), ('CROSS'), ('LINENO'), ('TABLE'), ('CURRENT'),
        ('LOAD'), ('TABLESAMPLE'), ('CURRENT_DATE'), ('MERGE'), ('TEXTSIZE'),
        ('CURRENT_TIME'), ('NATIONAL'), ('THEN'), ('CURRENT_TIMESTAMP'),
        ('NOCHECK'), ('TO'), ('CURRENT_USER'), ('NONCLUSTERED'), ('TOP'),
        ('CURSOR'), ('NOT'), ('TRAN'), ('DATABASE'), ('NULL'), ('TRANSACTION'),
        ('DBCC'), ('NULLIF'), ('TRIGGER'), ('DEALLOCATE'), ('OF'),
        ('TRUNCATE'), ('DECLARE'), ('OFF'), ('TRY_CONVERT'), ('DEFAULT'),
        ('OFFSETS'), ('TSEQUAL'), ('DELETE'), ('ON'), ('UNION'), ('DENY'),
        ('OPEN'), ('UNIQUE'), ('DESC'), ('OPENDATASOURCE'), ('UNPIVOT'),
        ('DISK'), ('OPENQUERY'), ('UPDATE'), ('DISTINCT'), ('OPENROWSET'),
        ('UPDATETEXT'), ('DISTRIBUTED'), ('OPENXML'), ('USE'), ('DOUBLE'),
        ('OPTION'), ('USER'), ('DROP'), ('OR'), ('VALUES'), ('DUMP'),
        ('ORDER'), ('VARYING'), ('ELSE'), ('OUTER'), ('VIEW'), ('END'),
        ('OVER'), ('WAITFOR'), ('ERRLVL'), ('PERCENT'), ('WHEN'), ('ESCAPE'),
        ('PIVOT'), ('WHERE'), ('EXCEPT'), ('PLAN'), ('WHILE'), ('EXEC'),
        ('PRECISION'), ('WITH'), ('EXECUTE'), ('PRIMARY'), ('WITHIN GROUP'),
        ('EXISTS'), ('PRINT'), ('WRITETEXT'), ('EXIT'), ('PROC')
      ) AS reserved (word)
      ON reserved.word = sys.objects.name;

 

Beware of numbers in any object names, especially table names. It normally flags up clumsy denormalization where data is embedded in the name, as in ‘Year2017’, ‘Year2018’ etc. Usually the significance of the numbers is obvious to the perpetrator, but not to the maintainers of the system.

SELECT name FROM sys.tables
   WHERE name LIKE '%[0-9]%' COLLATE Latin1_General_CI_AI --contains numbers

If you are more relaxed about this and will tolerate single numbers but no more, then tyy this

SELECT name FROM sys.tables
   WHERE name LIKE '%[0-9][0-9]%' COLLATE Latin1_General_CI_AI 
–contains more than one adjacent number

There are no generally accepted standards for naming SQL objects. Although ISO/IEC 11179 has been referred to as a standard for naming, it actually only sets a standard for defining naming conventions. There is a sample standard in the ‘Naming principles’ document (ISO/IEC 11179-5), but this is merely an example of how a standard should be defined. However, it is quite close to a general good-practice in programming.

When naming a table, it is a good idea to use a collective name or ‘object class term’ for the entity if one exists ( such as Employee, Cost, Tree, component, member, audience, staff or faculty) but use the singular rather than the plural form where possible. For the sake of maintenance, use a consistent naming convention that is informative but brief. It helps greatly to start with a dictionary of the correct nouns and verbs associated with the application domain and use that. If it proves inadequate, then the team can build on it. If a data model has been created as part of the design phase, this dictionary should be an end-product of this work.

Never use a descriptive prefix such as tbl_. This ‘reverse-Hungarian’ notation has never been a standard for SQL and clashes with SQL Server’s naming conventions. Some system procedures and functions were given prefixes “sp_”, “xp_” or “dt_” to signify that they were ‘special’ and should be searched for in the master database first. The use of the tbl_prefix for a table, often called ‘tibbling’, came from databases imported from Access when SQL Server was first introduced. Unfortunately, this was an access convention inherited from Visual Basic, a loosely typed language. Even if prefixes were a good thing, one wouldn’t use “Tbl_” for a table. There are established codes for SQL Server and the code for a table is U (short for ‘User Table’ evidently). There are still many DBAs that long to ‘tibble’, but there is never a doubt what type of object something is in SQL Server if you know its name, schema and database, because its type is there in sys.objects: Also it is obvious from the usage. SQL Server is a strongly-typed language.

SELECT name FROM sys.objects 
  WHERE Left(name,3) IN ('tbl','sp_','xp_','dt_')  --tibbling!

Do not give a table the same name as one of its columns.

SELECT Thetable.Name FROM sys.columns cols
INNER JOIN sys.tables Thetable 
  ON Thetable.object_id = cols.object_id
  WHERE cols.NAME=Thetable.name

Avoid, where possible, concatenating two table names together to create the name of a relationship table when there is already a word in the language to describe the relationship. e.g. use Client rather than EmployeeCustomer

This code will find these tables. Don’t try it on a huge database!

SELECT name
  FROM sys.tables AS TheTable
    INNER JOIN
      (
      SELECT first.name + second.name
        FROM sys.tables AS first
          CROSS JOIN
            (SELECT name FROM sys.tables) AS second
      ) AS combined(doubleName)
      ON combined.doubleName = TheTable.name;

On AdventureWorks2016, you will get a few tables that could be better-named

Keep table names short, because many naming conventions require that triggers, constraints and indexes include the name of the table or tables involved. A foreign key constraint can get cumbersome

Be consistent in the casing of tables and the use of underscore for delimiting words.

A table column should be a ‘quality common to all members of an object class’ and should have a name that corresponds to the way that plain language refers to the property such as First_Name, Amount, Measure, Number, Quantity or Text. Never apply the collective name to the property, such as having an ‘Employee_name’ property in an Employee table. This would provide redundancy when the qualified column was listed in a query – Employee.Employee_name.

You can quickly find all the columns with redundancy in their names if they are expressed with the dotted notation.

SELECT TheTable.name AS TableName, TheColumn.name AS ColumnName
  FROM sys.tables AS TheTable
    INNER JOIN sys.columns AS TheColumn
      ON TheColumn.object_id = TheTable.object_id
  WHERE TheColumn.name LIKE '%' + TheTable.name + '%';

AdventureWorks is full of this sort of problem.

Whoever thought up the name Person.Person.personType was short on vocabulary. It might be an idea to detect for the even more heinous crime of naming a table the same as a schema!

SELECT name FROM sys.objects WHERE Object_Schema_Name(object_id) = name;

A simple guide to naming is to respect the idea of SQL being an intelligible language based on written language. This would suggest that function names should fit into the semantics of the SELECT sentence, if we have a function that capitalises a sentence, makes the first character of each word longer than three characters a capital letter (MLA), then you’d call it ‘capitalized()’. Procedures would be verb-noun names of tasks, since they are executed.

Procedures should follow the Verb-noun convention popularised by PowerShell. Obviously, the standard verbs and nouns will come from the database design process and the data model of the organisation or the application domain.

Summary

There are certain style rules in SQL Server, but not that many. It is more important to be consistent and, where possible, write in a way that is closest to standard SQL. SQL Server gives you quite a bit of latitude in your style, but because you can do such things as putting numbers, whitespace and control characters into names doesn’t mean that you should. You can write in eccentric archaic dialects of SQL, but you are still being eccentric. In teamwork, it is best to adopt the defined standard that is in place, however absurd it may be, and work away at convincing the rest of the team to change.

SQL is unlike any other computer language in that it was designed to be as close to human language as possible, so it could be used by lay people to do business analysis. I believe that naming conventions should fit in with this basic idea so that database code reads clearly with just the minimum of documentation to assist in understanding.

See SQL Code Smells for more SQL Smells. See SQL Server Table Smells for SQL Code that flushes out more general problems with tables

The post SQL naming conventions appeared first on Simple Talk.

from Simple Talk http://bit.ly/2QxxNPu
via