What are the maximum number of Columns per index key,foreign key,primary key in SQL Server

Posted on : 16-04-2012 | By : Devi Prasad | In : SQL Server Basics

Share:

0


What are the maximum number of Columns per index key in SQL Server?
SQL Server supports a maximum of 16 Columns per index key .

What are the maximum number of Columns per foreign key in SQL Server?
SQL Server supports a maximum of 16 Columns per foreign key .

What are the maximum number of Columns per primary key in SQL Server?
SQL Server supports a maximum of 16 Columns per primary key .

Max Capacity For SQL Server 2012 SQL Server 2008 R2 SQL Server 2008 SQL Server 2005
Maximum number of Columns per index key in SQL Server 16 Columns 16 Columns 16 Columns 16 Columns
Maximum number of Columns per Foreign key in SQL Server 16 Columns 16 Columns 16 Columns 16 Columns
Maximum number of Columns per Primary key in SQL Server 16 Columns 16 Columns 16 Columns 16 Columns

Now if you want to test the maximum number of columns limitation for index key,primary key you can use the below test script.

Test Script:
[sql]
–DROP TABLE TestMaximumColumnsPKTable
CREATE TABLE TestMaximumColumnsPKTable
(
COLUMN1 BIT NOT NULL,
COLUMN2 BIT NOT NULL,
COLUMN3 BIT NOT NULL,
COLUMN4 BIT NOT NULL,
COLUMN5 BIT NOT NULL,
COLUMN6 BIT NOT NULL,
COLUMN7 BIT NOT NULL,
COLUMN8 BIT NOT NULL,
COLUMN9 BIT NOT NULL,
COLUMN10 BIT NOT NULL,
COLUMN11 BIT NOT NULL,
COLUMN12 BIT NOT NULL,
COLUMN13 BIT NOT NULL,
COLUMN14 BIT NOT NULL,
COLUMN15 BIT NOT NULL,
COLUMN16 BIT NOT NULL,
COLUMN17 BIT NOT NULL PRIMARY KEY (COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5, COLUMN6, COLUMN7, COLUMN8, COLUMN9, COLUMN10, COLUMN11, COLUMN12, COLUMN13, COLUMN14, COLUMN15, COLUMN16, COLUMN17)
);
[/sql]

When the script tries to create primary key with 17 columns you get below error:


Msg 1904, Level 16, State 1, Line 1
The index ” on table ‘TestMaximumColumnsPKTable’ has 17 column names in index key list. The maximum limit for index or statistics key column list is 16.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

http://msdn.microsoft.com/en-us/library/ms143432.aspx




References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Write a comment



Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,