Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Max database name length in SQL Server

I am trying to do some UI validation on a database name. I was told from different places that the SQL Server database name is stored as sysname inside SQL Server. I also verified that by checking the sys.Databases.

So, I make my textbox in the UI to have maxlength 128 characters long. This is the max length of sysname type.

However, I found that in my SQL Server 2005 Express edition, if I enter a database name with 128 characters long, it always complains

Could not create default log file because the name was too long

To make sure I can successfully create the database, I found that I need to set the max limit to 124 characters.

My question now is whether 124 characters limit is true for all versions of SQL Server on all different Windows OS?

like image 932
Harvey Kwok Avatar asked Feb 23 '11 19:02

Harvey Kwok


People also ask

How long can a database name be?

Both regular and delimited identifiers must contain from 1 through 128 characters. For local temporary tables, the identifier can have a maximum of 116 characters.

What is the maximum length in SQL View name can have?

SQL maximum column name length limitation is 128 characters. If we create more than 128 characters, it shows an error.

Can store more than 8000 characters SQL Server?

Microsoft SQL Server 2008 (and above) can store up to 8000 characters as the maximum length of the string using varchar data type. SQL varchar usually holds 1 byte per character and 2 more bytes for the length information.


2 Answers

The log file name does not need to include the database name.

This works fine for me for example.

CREATE DATABASE [AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA] ON  PRIMARY 
( 
NAME = N'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\A128.mdf')
 LOG ON 
( 
NAME = N'A128_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\A128_log.LDF')
like image 173
Martin Smith Avatar answered Oct 11 '22 14:10

Martin Smith


Are you not running into the problem of the file names for the data and log files. The underlying operating system (Windows) places restrictions on it and therefore the database cannot be created. The database name may be 128 characters long and works given that the file names used are not being to long (when using the SQL Server Management Studio it automatically generates file names based on the database names, and they therefore end up being long).

like image 33
Anders Zommarin Avatar answered Oct 11 '22 13:10

Anders Zommarin