Microsoft SQL Server 2005 - Maximum Capacity Specifications

Maximum Capacity Specifications for SQL Server 2005

The following tables specify the maximum sizes and numbers of various objects defined in Microsoft SQL Server 2005 components.

Database Engine Objects

The following table specifies the maximum sizes and numbers of various objects defined in SQL Server 2005 databases or referenced in Transact-SQL statements. The table does not include SQL Server Windows CE Edition.

SQL Server 2005 Database Engine object	Maximum sizes/numbers SQL Server 2005 (32-bit)	Maximum sizes/numbers SQL Server 2005 (64-bit)
Batch size1	65,536 * Network Packet Size	65,536 * Network Packet Size
Bytes per short string column	8,000	8,000
Bytes per GROUP BY, ORDER BY	8,060	8,060
Bytes per index key2	900	900
Bytes per foreign key	900	900
Bytes per primary key	900	900
Bytes per row8	8,060	8,060
Bytes per varchar(max), varbinary(max), xml, text, or image column	2^31-1	2^31-1
Characters per ntext or nvarchar(max) column	2^30-1	2^30-1
Clustered indexes per table	1	1
Columns in GROUP BY, ORDER BY	Limited only by number of bytes	Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement	10	10
Columns per index key7	16	16
Columns per foreign key	16	16
Columns per primary key	16	16
Columns per base table	1,024	1,024
Columns per SELECT statement	4,096	4,096
Columns per INSERT statement	1,024	1,024
Connections per client	Maximum value of configured connections	Maximum value of configured connections
Database size	524,258 terabytes	524,258 terabytes
Databases per instance of SQL Server	32,767	32,767
Filegroups per database	32,767	32,767
Files per database	32,767	32,767
File size (data)	16 terabytes	16 terabytes
File size (log)	2 terabytes	2 terabytes
Foreign key table references per table4	253	253
Identifier length (in characters)	128	128
Instances per computer	50 instances on a stand-alone server for all SQL Server 2005 editions except for Workgroup Edition. Workgroup Edition supports a maximum of 16 instances. SQL Server 2005 supports 25 instances on a failover cluster.	50 instances on a stand-alone server. 25 instances on a failover cluster.
Length of a string containing SQL statements (batch size)1	65,536 * Network packet size	65,536 * Network packet size
Locks per connection	Maximum locks per server	Maximum locks per server
Locks per instance of SQL Server5	Up to 2,147,483,647	Limited only by memory
Nested stored procedure levels6	32	32
Nested subqueries	32	32
Nested trigger levels	32	32
Nonclustered indexes per table	249	249
Parameters per stored procedure	2,100	2,100
Parameters per user-defined function	2,100	2,100
REFERENCES per table	253	253
Rows per table	Limited by available storage	Limited by available storage
Tables per database3	Limited by number of objects in a database	Limited by number of objects in a database
Partitions per partitioned table or index	1,000	1,000
Statistics on non-indexed columns	2,000	2,000
Tables per SELECT statement	256	256
Triggers per table3	Limited by number of objects in a database	Limited by number of objects in a database
UNIQUE indexes or constraints per table	249 nonclustered and 1 clustered	249 nonclustered and 1 clustered
User connections	32,767	32,767
XML indexes	249	249

1 Network Packet Size is the size of the tabular data stream (TDS) packets used to communicate between applications and the relational Database Engine. The default packet size is 4 kilobytes (KB), and is controlled by the network packet size configuration option.

2 The maximum number of bytes in any index key cannot exceed 900 in SQL Server 2005. You can define a key using variable-length columns whose maximum sizes add up to more than 900, provided no row is ever inserted with more than 900 bytes of data in those columns. In SQL Server 2005, you can include nonkey columns in a nonclustered index to avoid the maximum index key size of 900 bytes. For more information, see Index with Included Columns.

3 Database objects include objects such as tables, views, stored procedures, user-defined functions, triggers, rules, defaults, and constraints. The sum of the number of all objects in a database cannot exceed 2,147,483,647.

4 Although a table can contain an unlimited number of FOREIGN KEY constraints, the recommended maximum is 253. Depending on the hardware configuration hosting SQL Server, specifying additional foreign key constraints may be expensive for the query optimizer to process.

5 This value is for static lock allocation. Dynamic locks are limited only by memory.

6 If a stored procedure accesses more than 8 databases, or more than 2 databases in interleaving, you will receive an error.

7 If the table contains one or more XML indexes, the clustering key of the user table is limited to 15 columns because the XML column is added to the clustering key of the primary XML index. In SQL Server 2005, you can include nonkey columns in a nonclustered index to avoid the limitation of a maximum of 16 key columns. For more information, see Index with Included Columns.

8 SQL Server 2005 supports row-overflow storage which enables variable length columns to be pushed off-row. Only a 24-byte root is stored in the main record for variable length columns pushed out of row; because of this, the effective row limit is higher than in previous releases of SQL Server. For more information, see the "Row-Overflow Data Exceeding 8 KB" topic in SQL Server 2005 Books Online.

Regular Expressions

Phone or Fax 999-999-9999
"\d{3}\-\d{3}\-\d{4}"

Phone or Fax (999) 999-9999
"\(\d{3}\) \d{3}\-\d{4}"

Simple Email
(\w[-._\w]*\w@\w[-._\w]*\w\.\w{2,3})

Email blah@blah.com
"^([0-9a-zA-Z]+[-._+&])*[0-9a-zA-Z]+@([-0-9a-zA-Z]+[.])+[a-zA-Z]{2,6}$"

Email blah._blah@blah.com
"^(([\w-]+\.)+[\w-]+|([a-zA-Z0-9]{1}|[\w-]{2,}))+@([-0-9a-zA-Z]+[._])+[a-zA-Z]{2,6}$"

Number 965
"\d\"

Why Vista Failed?

Windows 7 is through beta, it is now release candidate, soon we are going to have a brand new operating system from Microsoft. I have been some what loyal to Microsoft Windows, when i worked on Windows 98 for the first time it was like Wow! I would say Windows 98 really changed the way people think about a personal computer. When Windows Me came in, i decided to stick with Windows 98, reason was i did not find any reason to switch as Windows 98 was doing everything i wanted at that time. When Windows XP, i still kept with Windows 98 until Windows XP Service pack 1 came in.

Now i am using Windows XP Service pack 3, i installed a dual boot with Windows Vista a number of times, but was unable to convince myself to switch. As i have been so used too with Windows XP and i really like it now.
Windows Vista Service pack 1 is a real improvement, but compatibility issue of Windows Vista with its predecessors is one of the reasons most of the users are not switching. Still i have kept Vista on a Virtual machine.

Reasons behind failure of Vista i think:
1. Strong security model, which keeps showing annoying dialog windows, and it is so frequent that users get used to just click OK without bothering for it, because they want to complete a task quickly

2. Hardware requirements, so Windows XP users will have to buy a new hardware if they want to have Windows Aero

3. Performance promises not met, seem to be slow as compare to XP

4. Windows Vista not showing backward computability is biggest reason among all

5. Most of the IT industry have applications developed that run nicely on Windows XP, but same application tends to crash on Vista without a clue

6. Software Vendors are also getting problem in making their programs work on Vista, for this they have to maintain a Virtual machine all the time to test their code on Vista.

7. For gamers that like their old games as well, get a set back when they see the older games are not working well on Vista

I hope Microsoft gets around and fixes them in the up coming Windows 7, i am hopping for the best, otherwise i will keep myself with Windows XP, and let Vista run on a Virtual machine.

Using Missing Index Information to Write CREATE INDEX Statements

SELECT 'CREATE NONCLUSTERED INDEX NewNameHere ON ' + sys.schemas.name
+ '.' + sys.objects.name + ' ( ' + mid.equality_columns + CASE WHEN mid.inequality_columns IS NULL
THEN '' ELSE CASE WHEN mid.equality_columns IS NULL
THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' +
CASE WHEN mid.included_columns IS NULL
THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement,
mid.equality_columns, mid.inequality_columns, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
INNER JOIN sys.objects WITH (nolock)
ON mid.object_id = sys.objects.object_id
INNER JOIN sys.schemas
ON sys.objects.schema_id = sys.schemas.schema_id
WHERE (migs.group_handle IN ( SELECT TOP 100 PERCENT group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC
AND sys.objects.type = 'U'

MSDN : Using Missing Index Information to Write CREATE INDEX Statements

Brian Knight's blog on the Missing Index DMV

Reference: http://msdn.microsoft.com/en-us/library/ms345405(SQL.90).aspx

Text Area Maximum Length

/*Text Area Maximum Length

<textarea cols="40" rows="3" id="" wrap="physical"

onKeyUp="return textCounter(this.form.other,this.form.remLen_1,100);"

onKeyDown="return textCounter(this.form.other,this.form.remLen_1,100);" name="other"></textarea>

<input type="text" name="remLen_1" value="100">

*/

function textCounter(field, countfield, maxlimit) {

var textlength = field.value.length;

if (textlength > maxlimit) {

field.value = field.value.substring(0, maxlimit);

} else {

countfield.value = maxlimit - textlength;

}

}