I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

A few maximum limitations for SQL Server 2005

Every now and then i see a question pop up that asks what is the max this or that in sql server?

Well here are some maximum values:

Bytes per short string column8,000
Bytes per GROUP BY, ORDER BY8,060
Columns in GROUP BY, ORDER BYLimited only by number of bytes
Bytes per index key900
Bytes per foreign key900
Bytes per primary key900
Bytes per row8,060
Bytes per varchar(max), varbinary(max), xml, text, or image column2^31-1
Characters per ntext or nvarchar(max) column2^30-1
Clustered indexes per table1
Columns per index key16
Columns per foreign key16
Columns per primary key16
Columns per base table1,024
Columns per SELECT statement4,096
Columns per INSERT statement1,024
Connections per client32,767
Database size1,048,516 terabytes
Databases per instance of SQL Server32,767
Filegroups per database32,767
Files per database32,767
File size (data)16 terabytes
File size (log)2 terabytes
Foreign key table references per table253
Identifier length (in characters)128
Instances per computer50 (Workgroup Edition only 16)
Locks per connectionMaximum locks per server
Locks per instance of SQL ServerUp to 2,147,483,647
Nested stored procedure levels32
Nested subqueries32
Nested trigger levels32
Nonclustered indexes per table249
Parameters per stored procedure2,100
Parameters per user-defined function2,100
REFERENCES per table253
Rows per tableLimited by available storage
Tables per databaseLimited by number of objects in a database
Partitions per partitioned table or index1,000
Statistics on non-indexed columns2,000
Tables per SELECT statement256
Triggers per tableLimited by number of objects in a database
UNIQUE indexes or constraints per table249 nonclustered and 1 clustered
User connections32,767
XML indexes249

 

For a complete listing go here

kick it on DotNetKicks.com
 

 

Print | posted on Tuesday, July 24, 2007 5:13 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: A few maximum limitations for SQL Server 2005

Does 128 hold true for database name, database alias, instance name, package name, and functions also? Are these considered identifiers?
We are trying to check compatibility with Oracle and DB2 UDB.

Thanks,
Diane England
Avaya Inc.
10/16/2007 5:26 PM | Diane England
Gravatar

# re: A few maximum limitations for SQL Server 2005

yes. i believe so.
10/16/2007 5:36 PM | Mladen
Gravatar

# re: A few maximum limitations for SQL Server 2005

I see that the maximum connections per client is 32767. What is the maximum number of connections that the server can handle? And is there a curve in performance degradation?
10/8/2008 11:10 PM | Jared Saindon
Gravatar

# re: A few maximum limitations for SQL Server 2005

i think that value is called User connection and it's the same number.

no clue to the performance degradation since i've never had even remotely that many connection open to the server. you have to distingusih between users and acctual connections made from the web server to the sql server.
10/9/2008 10:58 AM | Mladen
Gravatar

# re: A few maximum limitations for SQL Server 2005

BUT U STILL WANT MORE ;)
3/25/2009 12:07 PM | sim
Gravatar

# re: A few maximum limitations for SQL Server 2005

Thanks. this was very helpful.
Can you post a similar page for SQL 2008? (Now i m getting greedy. :-))
7/2/2009 12:50 PM | Abhijeet
Gravatar

# re: A few maximum limitations for SQL Server 2005

Hi,

I have a database name Test. i am using SQL 2005 server standard edition. When ever my databse size increase to 4GB i get error like,

"Could not allocate space for an object "Table1" in "Test". Primary filegroup may be full......"

Even though i have enough space in my hard disk.

Can any one tell me why is this error comes?


Thanks

Sharan
9/14/2009 6:29 PM | Sharan
Gravatar

# re: A few maximum limitations for SQL Server 2005

Quote :: I have a database name Test. i am using SQL 2005 server standard edition. When ever my databse size increase to 4GB i get error like
"Could not allocate space for an object "Table1" in "Test". Primary filegroup may be full......"


Well, Are you running on FAT32 Disk format and not NTFS ??

FAT32 has a limit of 4gb filesize
11/11/2009 1:01 PM | iBorg
Gravatar

# re: A few maximum limitations for SQL Server 2005

Hi,
I would like to know, how mant databases can be created on SQL server 2005 if the avg size of database be 1 gb?

Thanks,
Joe
3/16/2010 1:55 PM | Joe Amon
Gravatar

# re: A few maximum limitations for SQL Server 2005

Hi Sharan,

You sure u using standarad edition??
5/21/2010 5:53 AM | Ratheesh.K.Nair
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET