views:

40

answers:

2

I am having trouble running a simple sql query in Microsoft SQL Server 2005 to create a database and im not sure why.

When I run this query

CREATE DATABASE 4444-virtual2

I receive this error

Incorrect syntax near '4444'.

Is there anything in particular that I must specify if I am creating a database table with numeric values in the name? Or am I forgetting something?

+5  A: 

Database names need to start with a letter, underscore, at sign or number sign:

The first character must be one of the following:

  • A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages.

  • The underscore (_), at sign (@), or number sign (#).

Certain symbols at the beginning of an identifier have special meaning in SQL Server. A regular identifier that starts with the at sign always denotes a local variable or parameter and cannot be used as the name of any other type of object. An identifier that starts with a number sign denotes a temporary table or procedure. An identifier that starts with double number signs (##) denotes a global temporary object. Although the number sign or double number sign characters can be used to begin the names of other types of objects, we do not recommend this practice.

Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, you should not use names that start with @@.

Unless you want to delimit every use of the name with double quotes "4444-virtual2" or brackets [4444-virtual2].

Ben S
Agreed. See: http://msdn.microsoft.com/en-us/library/ms175874.aspx
Mike
Thank you very much, that was very insightful!
Mad Gibberish
+1  A: 

You can still create a database with that name, but you need to put it in quotes or brackets. e.g. this works:

CREATE DATABASE [4444-virtual2]

or this:

CREATE DATABASE "4444-virtual2"
Cade Roux