SQL Server maximum capacity specifications and defaults

I have noticed one thing in most of the interviews I have taken so far, that the candidates can hardly answer on SQL Server specifications and default values of components. These are very commonly asked SQL Server interview questions, you will for sure get one or more questions from SQL Server maximum capacity specifications in any interviews no matter if you are a pro or novice in SQL. So, decided to have them lined up here, which are being commonly asked.

Default / Maximum Value
Default Installation Path C:\Program Files\Microsoft SQL Server
Default SQL Server port 1433
Default Instance Name MSSQLSERVER
Default collation SQL_Latin1_General_CP1_CI_AS
Maximum Instances per computer 50 numbers
Maximum user connection 32,767 numbers
Default Database Size 4 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 instance Master, Model, MSDB, Tempdb and ResourceDB(Hidden)
Maximum Database size 524,272 terabytes however it varies with different editions of SQL Server.
Maximum Databases per instance 32,767 numbers
Maximum Filegroups and files per database 32,767 numbers
MDF per database 1
LDF per database 1
Maximum data file size 16 terabytes
Maximum log file size 2 terabytes
A database can contain maximum objects (Table, SPs, Funs, Constarints etc) upto  2,147,483,647 numbers
Maximum Clustered index per table 1
Maximum  Non clustered index per table 999 numbers
Maximum XML Indexes 249 numbers
Maximum FK references per table 253 numbers
Maximum Columns per index key 16 numbers
Maximum Columns per primary key 16 numbers
Maximum Columns per SELECT statement 4,096 numbers
Maximum Columns per UPDATE statement 4,096 numbers
Maximum Columns per INSERT statement 4,096 numbers
Maximum Columns in a Table or View 1,024 numbers
Maximum partions per partioned table 1,000 (32 Bit) | 15,000 (64 Bit)
Maximum Bytes per short string column 8,000 bytes
Maximum Bytes per GROUP BY or ORDER BY
columns.
8,060 bytes
Maximum Bytes per index key 900 bytes
Maximum Bytes per foreign key 900 bytes
Maximum Bytes per primary key 900 bytes
Maximum Bytes per row 8,060 bytes
Maximum text size of a stored procedure 250 MB
Maximum Object name length 128 charcters
Maximum Parameters per stored procedure and user-defined function 2,100 numbers
Maximum Nesting level of a stored procedure 32 numbers
Maximum Nesting level of a trigger 32 numbers
Maximum Nested subqueries 32 numbers
Default recursion level in CTE upto 100 numbers
Maximum rescursion level in CTE is upto 32,767 numbers
Default JOIN keyword INNER JOIN
Default sorting of ORDER BY clause Ascending
Default INDEX keyword creates A non unique non-clustered index
Maximum Articles (merge publication) 256 numbers
Maximum Articles (snapshot or transactional publication 32767 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

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

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

One thought on “SQL Server maximum capacity specifications and defaults

  • April 11, 2017 at 11:23 pm
    Permalink

    Very useful!!!

    Reply

Leave a Reply

%d bloggers like this: