CODEWITHSUNDEEP
sqlsql-serversql-server-2005identifier
Mad Gibberish
Mad Gibberish

Reputation: 117

SQL Server query to create database is giving me an error when I include numeric characters in the database name

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?

Upvotes: 4

Views: 5488

Answers (2)

Ben S
Ben S

Reputation: 69342

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].

Upvotes: 12

Cade Roux
Cade Roux

Reputation: 89661

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"

Upvotes: 7

Related Questions