SQL Server maximum capacity specifications and defaults

Many times we need to answer on SQL Server specifications and default values of components. These are very commonly asked SQL Server questions. Below are the lined up all basic SQL Server specifications and default values of components.

Note: The default and maximum specifications varies from version to versions of SQL Server. But below mentioned specifications are very much common between different available versions of SQL Server.

Default / MaximumValue
Default Installation PathC:\Program Files\Microsoft SQL Server
Default SQL Server port1433
Default Instance NameMSSQLSERVER
Default collationSQL_Latin1_General_CP1_CI_AS
Maximum Instances per computer50 numbers
Maximum user connection32,767 numbers
Default Database Size4 MB (3MB MDF + 1 MB LDF) New databases gets created referring to the size of Model database.
Default System databases gets installed with the SQL Server instanceMaster, Model, MSDB, Tempdb and ResourceDB(Hidden)
Maximum Database size524,272 terabytes however it varies with different editions of SQL Server.
Maximum Databases per instance32,767 numbers
Maximum Filegroups and files per database32,767 numbers
MDF per database1
LDF per database1
Maximum data file size16 terabytes
Maximum log file size2 terabytes
A database can contain maximum objects (Table, SPs, Funs, Constarints etc) upto 2,147,483,647 numbers
Maximum Clustered index per table1
Maximum  Non clustered index per table999 numbers
Maximum XML Indexes249 numbers
Maximum FK references per table253 numbers
Maximum Columns per index key16 numbers
Maximum Columns per primary key16 numbers
Maximum Columns per SELECT statement4,096 numbers
Maximum Columns per UPDATE statement4,096 numbers
Maximum Columns per INSERT statement4,096 numbers
Maximum Columns in a Table or View1,024 numbers
Maximum partions per partioned table1,000 (32 Bit) | 15,000 (64 Bit)
Maximum Bytes per short string column8,000 bytes
Maximum Bytes per GROUP BY or ORDER BY columns.8,060 bytes
Maximum Bytes per index key900 bytes
Maximum Bytes per foreign key900 bytes
Maximum Bytes per primary key900 bytes
Maximum Bytes per row8,060 bytes
Maximum text size of a stored procedure250 MB
Maximum Object name length128 charcters
Maximum Parameters per stored procedure and user-defined function2,100 numbers
Maximum Nesting level of a stored procedure32 numbers
Maximum Nesting level of a trigger32 numbers
Maximum Nested subqueries32 numbers
Default recursion level in CTE upto100 numbers
Maximum rescursion level in CTE is upto32,767 numbers
Default JOIN keywordINNER JOIN
Default sorting of ORDER BY clauseAscending
Default INDEX keyword createsA non unique non-clustered index
Maximum Articles (merge publication)256 numbers
Maximum Articles (snapshot or transactional publication32767 numbers
Maximum Columns in a table (merge publication)246 numbers
Maximum Columns in a table (SQL Server snapshot or transactional publication)1000 numbers
Maximum Columns in a table (transactional publication)995 numbers
Maximum Bytes for a column used in a row filter (merge publication)1024 numbers
Maximum Bytes for a column used in a row filter (snapshot or transactional publication)8000 numbers

<<Click here to see all posts>>

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.