0

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.

George Menoutis
  • 6,894
  • 3
  • 19
  • 43

2 Answers2

2

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.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
0

It is perfectly valid to have periods in the object names. However, this requires you to use square brackets around the object name when referring to it. In case you forget these square brackets you will get some error messages that can be less intuitive to the inexperienced developer. For this reason I recommend not to use periods in the object names. I would also guess this is the main reason you don't often see examples of periods in object names on the internet.

In your example, you could use another underscore instead of the period, like this: DF__tablename_columnname

Morten
  • 398
  • 1
  • 6
  • 16