Search code examples
t-sqlobjectname

Are periods in object names bad practice?


For example, a constraint for a default value of 0 could be named DF__tablename.columnname.

Although my search for this being bad practice doesn't yield results, in the numerous constraints examples I've seen on SO and many other sites, I never spotted a period.


Solution

  • Using period in an object name is bad practice.

    Don't use dot character in an identifier. Yes it can be done but the drawbacks outweigh any benefits.


    tl;dr

    Special characters, such as a dot, are not allowed in regular identifiers. If an identifier does not follow the rules for regular identifier, then references to the identifier must be enclosed in square brackets (or ANSI double quotes).

    https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers?view=sql-server-2017

    In terms of the period (dot character), using that in an identifier is not allowed in a regular identifier; but it could be used within square brackets.

    The dot character is even more of a special-ish character in SQL; it's used to separate an identifier from a preceding qualifier.

      SELECT mytable.mycolumn FROM mytable
    

    We could also write that as

      SELECT [mytable].[mycolumn] FROM mytable
    

    We could also write

      SELECT [mytable.mycolumn] FROM mytable
    

    but that means something very different. With that, we aren't referencing a column named mycolumn, we are now referencing an identifier that contains a dot character.

    SQL Server will deal with this just fine.

    But if we do this, and start using the dot character in our identifiers, we will be causing confusion and frustration to future readers. Any benefit we would gain by using dot characters in identifiers is going to be far outweighed by the downside for others.

    Similarly, why we don't create tables named WHERE (1=1) OR, or create columns named SUBSTR(foo.bar,1,10) to avoid monstrosities like

      SELECT [SUBSTR(foo.bar,1,10)] FROM [WHERE (1=1)] OR]
    

    Which may be valid SQL, but it will cause future readers to become very upset, and cause them to curse us, our descendants and loved ones. Don't make them do that. For the love of all that is good and beautiful in this world, don't use dot characters in identifiers.