27

If I use a database name with a dot in it in SQL Server (2005 or 2008), something like "MyApp.Sales", will this cause any problems?

59

You can, but I wouldn't. You would always have to wrap the DB name with square brackets such as [MyApp.Sales].

So to recap: if you value your sanity, don't do it.

35

I think it's a very bad idea even if technically possible.

Over the years I found out that many people have trouble understanding the four-part naming convention even though it seems pretty obvious:

server_name.database_name.schema_name.object_name

Imagine what will happen if they see something like this:

MAIN-SQL.[MyApp.Sales].hr.CompetitorsProducts

or:

[MAIN-SQL\EXPRESS].[MyApp.Sales].sch_HR.[Products From.Our-Competitors]

Keeping things simple is important.

15

In case someone else encounters this question...

Keep in mind that it is not only a bad idea because users might be confused, but also because some tools might get confused.

Even Microsoft itself has problems with this. If you try to connect your database to an excel sheet using Microsoft Query (via ODBC), you get a configuration wizard which lets you choose the database you want to connect to. However choosing a database that contains a dot will produce an error stating that the server could not be found. It seems that the wizard does not check if the values needs escaping and blindly concatenates the identifiers.

There are of course workarrounds, but you save yourself some trouble by not doing this from the start.

7

Do not use period in database names, publication names, user names. And I strongly recommend never to use a period in name (column, table, view, database, etc).

Update: I can confirm, that the minus, dash "-" causes simmilar issues.

Here is what happens:

SQL Server uses scripts for internal usage like system stored procedures. As already mentioned here, the commands you use will sometimes force you to put the name into brackets and that is (seriously microsoft?) not (always) okay for stored procedures usage.

In fact I am not able anymore to cleanup abonnement informations because the stored procedure iterates through all databases and doesn't escape the database names propperly. This iteration is that complex I wasn't able to patch it in over 8 SPs.

Plus I am not able to use MMC or scripts to edit replication conflicts. If you have one (One!) database that has a dot inside its name, these problems will occure on everey db, every publication.

These are the occurences I have. Complex SQL actions are processed in sql language and will work fine, if the base is fine.

If you try to connect your database to an excel sheet using Microsoft Query (via ODBC), you get a configuration wizard which lets you choose the database you want to connect to. - aKzenT

These errors may more often be found in 3rd party software, so never ever use dots in every name of anything on sql server.

6

There are many characters allowed in names, but, convention and common sense limits special characters usage. '_' (underscore) is the proven safest separator and maintains readability ease.

1

Same deal: foreign key name should not contain any dot.

I found that if you need to rename such a name then you'll need brackets to make sp_rename work (because as you rename a Foreign key you need to specify the schema).

Example:

sp_rename '[MySchema].[MyFKName.With.Dots]' 'NewFKName' 'OBJECT'

If you forget the brackets, you get an error: 15225

-1

One of our suppliers has chosen to introduce the schema name: [System.Activities.DurableInstancing]. It's broken our dataload tool, so we now have further development to cope with their weirdness. Just don't do it. Use a '-'.

  • 5
    Why use a dash - when you can use an underscore _ and not have to enclose the name in square brackets at all? – ErikE Feb 25 '15 at 20:00
  • 3
    A minus will cause other but similar problems. – Daniel Aug 11 '15 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.