Maximum Capacity Specifications for Database Objects in SQL Server 2008 R2

The following table specify the maximum sizes and numbers of various Database Objects defined in SQL Server 2008 components:

Database object	Maximum sizes/numbers (32-bit)	Maximum sizes/numbers (64-bit)
Columns per index	16	16
Columns per Primary Key	16	16
Columns per Foreign Key	16	16
Columns per SELECT statement	4096	4096
Columns per INSERT statement	4096	4096
Database Size	524,272 TB	524,272 TB
No of Databases per SQL Server instance	32,767	32,767
Files per database	32,767	32,767
Maximum File size (data)	16 TB	16 TB
Maximum File size (log)	2 TB	2 TB
Foreign key references per table	253	253
Identifier length in characters	128	128
Nested stored procedure levels	32	32
Nested subqueries	32	32
Nested trigger levels	32	32
REFERENCES per table	253	253
Nonclustered indexes per table	999	999
xml indexes per table	249	249
Parameters per stored procedure	2100	2100
Parameters per user-defined functions	2100	2100
Partitions per partitioned table or index	1000	1000
Btach Size	65,536 * Network Packet Size	65,536 * Network Packet Size
Bytes per GROUP BY, ORDER BY	8,060	8,060
Bytes per key (PK, FK, Index)	900	900
Bytes per row	8,060	8,060
Bytes in source text of a stored procedure	lesser than batch size or 250 MB	lesser than batch size or 250 MB
Bytes per varchar(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

Reference: BOL, here is link

Explain Candidate Key, Alternate Key, and Composite Key

A Candidate Key is one that can uniquely identify each row of a table.
Generally a candidate key becomes the Primary Key of the table.

If the table has more than one Candidate Key, one of them will become the Primary Key, and the rest are called Alternate Keys.

A key formed by combining at least two or more columns is called Composite Key.

What's the difference between a primary key and a unique key constraints?

Both Primary Key and Unique Key enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default.

Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL.

Primary key:

1. Primary key used to uniqly identify each row in Table.
2. Primary key does not allow duplicate values and Null values.
3. Primary key is default Clustered Indexes
4. One table can have only one Primary key.

 Unique Key:

1. Unique Key is used to uniqly identify each row in Table.
2. Unique Key does not allow duplicate values but allowes only one Null value.
3. Unique Key is default Non- Clustered Indexe
4. One table can have more than one Unique Keys.