SQL Interview Questions And Answers - PRIMARY KEY

SQL Interview Questions And Answers - PRIMARY KEY

In SQL Primary Key is the most important topic for both development and interview point of view as well. In this article, we will see some common interview questions which is based on the Primary Key. For experienced developers, there can be a lot of questions based on this topic. The interviewer may try to confuse you by asking the same question in a more tricky way.

Please visit our YouTube Channel for more SQL Interview Questions and Answers videos by the below link:

Watch SQL Interview Questions and Answers videos here.

Introduction

In SQL Primary Key is a value that is unique for every record in the table.  A Primary Key is used to uniquely identify the row of the table. The Primary Keys are stored within the index. Normally the Primary Key creates the Clustered Index by default. A table can have only one Primary Key.

I will recommend to please read the previous Primary Key article here first for more details about the primary key.

Below are some Common Interview Questions based on the SQL Primary Key Constraint:

Q01. What is the difference between INDEX vs KEY?

In SQL an index is a set of ordered references to the rows of a table. It can contain data from one or more columns of a table based on the index coverage. The index itself is stored on pages, making up the Index Pages in SQL Server.

An index is basically used for improving the performance of data retrieval by reducing the number of physical pages that the database must access in order to read a row in the database. 

Since the indexes always store the data in sorted order, they also eliminate the need to create temporary storage for the ORDER BY clause if the required column is included in the index. 

There are different type of Indexes, but actually, there is only two categories of any Index: Clustered Index and Non-Clustered Index. All other types of Index are derived from these two categories like Unique Index, Spatial Index, Columnstore Index, XML Index, Filtered Index, Hash Index, etc.

Please watch the Clustered vs Non-Clustered Index video here and the Unique Index video here for more details.

On the other hand, KEYs are nothing but constraints, and Constraints are the rules that the database enforces to improve data integrity. A Key can also consist of one or more columns same as the Index. 

There are five constraints (NOT NULL Constraints, Primary Key Constraints, Unique Key Constraints, Foreign Key Constraints, Check Constraints, Default) out of which three are also known as SQL Keys (Primary Key, Unique Key, Foreign Key).

In SQL the KEY and INDEX are two different things, they are not the same. Though some KEYs like PRIMARY KEY and UNIQUE KEY also create indexes internally when they are created. The main use of an INDEX is performance while the main purpose of KEY is to enforce data integrity.

Please watch the Primary Key video here, Unique Key video here, and Foreign Key video here for more details.

Q02. What is the difference between PRIMARY KEY vs UNIQUE KEY?

A PRIMARY KEY is a value that is used to uniquely identify the record or individual row of a table, while a UNIQUE KEY is used to enforce a unique value in a column or set of columns.

Let's see the similarities between PRIMARY KEY and UNIQUE KEY first:

1. Both create and use UNIQUE Index internally and both can be created on Clustered and Non-Clustered Index.
2. Both can uniquely identify the record.
3. Both can be referenced by a Foreign Key Constraint.
4. Both can be Identity Columns.
5. Both cannot be created on a column having MAX Size data type i.e VARCHAR(MAX) or NVARCHAR(MAX) data type.

Now let's see the difference between PRIMARY KEY and UNIQUE KEY:

1. Only one PRIMARY KEY is allowed in any table but multiple UNIQUE KEYs are allowed in any table.
2. The PRIMARY KEY does not allow any NULL Value while the UNIQUE KEY allows a single NULL Value.
3. PRIMARY KEY cannot be created on a NULL-able column while a UNIQUE KEY can be created on the nullable column.
4. PRIMARY KEY creates Clustered Index by default but a UNIQUE KEY creates a Non-Clustered Index by default. But it is not mandatory at all.
5. PRIMARY KEY does not create a separate index to store the key values while a UNIQUE KEY creates a separate secondary index to store the key values.

Please watch the UNIQUE Key video here for more details.

Q03. What is the difference between PRIMARY KEY vs Non-Nullable UNIQUE KEY?

We know that a Primary Key is nothing but a combination of NOT NULL Constraint and UNIQUE KEY Constraint together. But Primary Key is not the same as Non-Nullable UNIQUE KEY.

The purpose of both Primary Key and Unique Key is different. Second, only one Primary Key could be there on any table while multiple Unique Keys are allowed. 

Primary Key generally creates Clustered Index and Unique Key does not. The rest of the differences are the same as the above question no 2.

Q04. What is the difference between the PRIMARY KEY and UNIQUE Clustered Index?

Actually, both terms are looking confusing but they are two different things. The Primary Key is not the same as Unique Clustered Index.

Actually, the PRIMARY KEY also creates a UNIQUE CLUSTERED Index implicitly but this index is referenced by the Primary Key internally. You cannot DROP this index explicitly.

On the other hand, an explicit UNIQUE Clustered Index also does the same thing but can be deleted by the DROP command.

 

However, only one Clustered Index is allowed in any table either by Primary Key,  Unique Key, or by explicit CREATE INDEX command.

Please watch the PRIMARY KEY video here and the UNIQUE Clustered Index video here for more details.

Q05. How many NULL values are allowed in a PRIMARY KEY column?

No NULL Value is allowed in the PRIMARY KEY column. In fact, a Primary Key cannot be created on a nullable column.

Please watch the "SQL NULL Value" video here and the "SQL 3 Valued Logic" video here for more details. 

Q06. Can a PRIMARY KEY Contains an empty(blank) value or whitespace?

Yes, a single blank string or empty string is allowed in the Primary Key column. It is allowed in both numeric and string data type columns, but if it is a numeric type column by default 0 will be inserted.

Q07. Can a PRIMARY KEY column contain duplicate values?

In single-column (column level primary key) Primary Key, duplicate values are never allowed. However, if the Primary Key is a composite column (table level primary key), the duplicate value may be allowed in any individual composite column but the combination of all the composite columns must be always unique.

Q08. Can you add a Clustered Index explicitly on any column in SQL?

Yes, we can add a Clustered Index explicitly if it is not created by the Primary Key already.

Please watch the Clustered Index video here for more details.

Q09. Is it mandatory for a PRIMARY KEY to be a Clustered Index? Can a PRIMARY KEY have a Non-Clustered index?

It is not mandatory for a PRIMARY KEY to be Clustered Index. A Primary Key can be created on the Non-Clustered Index as well.

Please watch the Clustered Index vs Non-Clustered Index video here for more details.

Q10. Can you create a PRIMARY KEY on a table that has already a Clustered Index?

Yes, If there is a Clustered Index already created on the table, for Primary Key; SQL will create a Non-Clustered Index forcefully.

However, the interesting thing is that if the Clustered Index is already created on the same column, where you are creating the Primary Key, the SQL Engine never uses the existing clustered index because it knows that the Index Created by CREATE INDEX command can be dropped explicitly by the user.

Please watch the Clustered Index video here.

Q11. Can you add both Clustered and Non-Clustered Indexes on a column?

Yes, we can do that. But only one Clustered Index is allowed in the table.

Please watch the Clustered Index vs Non-Clustered Index video here for more details.

Q12. If a column contains a Clustered Index, is it mandatory that the column must be PRIMARY KEY as well?

No, it is not required. Both PRIMARY KEY and Clustered Index are two different and independent things.

Q13. Can you add multiple PRIMARY KEY or Clustered Indexes on any table?

No, Only one Primary Key or Clustered Index is allowed per table.

Please watch the Primary Key video here and Clustered Index video here for more details.

Q14. Can you create a PRIMARY KEY on the Temp Table?

Yes, we can create a Primary Key on a temp table. Please watch the Temp Table video here for more details.

Q15. Can you add a PRIMARY KEY on view?

No, we cannot create a Primary Key on view but Index can be created on a view.

Q16. What is the difference between table level and column level PRIMARY KEY?

Column-Level constraints refer to a single column in the table and do not specify a column name (except check constraints). They only refer to the column that they follow.

On the other hand, Table-Level constraints refer to one or more columns in the table. They specify the names of the columns to which they apply.

Column-level constraints are applied to the column only, whereas table-level constraints are used to define composite keys.

Q17. When the database table is in First Normal Form (1NF)?

Normalization is the process of minimizing redundancy from a relation or set of relations. Normal forms are used to eliminate or reduce redundancy in database tables. 

There are various levels of normalization. A database is in first normal form if it satisfies the following conditions:

• Contains only atomic values (Single Valued) i.e. table cannot hold multiple values.
• Attribute Domain does not change i.e all values should be of the same type.
• There are no repeating groups i.e two columns of a table should not be closely related.
• There is a unique name for every Attribute/Column.
• There is no duplicate record in the table.

Q18. Can you update or delete the PRIMARY KEY value of a table?

Yes, but not always. If the Primary Key column is referenced by the Foreign Key, then it cannot be either updated or deleted unless the referencing record of the child table is removed first.

Please watch the Foreign Key Constraint video here for more details.

Q19. Can you add the PRIMARY KEY constraint on a column that contains data?

Yes, but the column should not have any duplicate value or NULL value.

Q20. What is IAM (Index Allocation Map) Page in SQL?

IAM stands for Index Allocation Maps. IAM page is a page created by SQL for managing the data pages. It contains basically the metadata of the data. The page type of an IAM Page is always 10.

The SQL Server uses IAM to track which pages have been allocated to which allocation unit. We can check that by the DBCC IND command. Each allocation unit has at least one IAM Page; depending on the size of the table there might be many. Normally every index page has an associated IAM page.

Like every other page, an IAM page has a 96-byte header. The page header is followed by a special index allocation map header. That header contains a base page address. It points to the first page that this allocation map covers.

Please watch the SQL 8KB Page Concept video here for more details about the IAM Page.

Q21. What is a Composite Key?

A composite key is composed of two or more columns that are collectively used to uniquely identify each and every record. The individual key of a composite key is known as the candidate key. 

Columns that make up the composite key can be of different data types. In composite key,  combined columns uniqueness is guaranteed, but when it is taken individually it does not guarantee uniqueness.

Q22. What is a Candidate Key?

A candidate key is a part of a super key that can uniquely identify a table record. Each individual column or attribute of a composite key is a candidate key.

In a table, we can have one or more than one candidate key, but we can use only one as a primary key for a table.  

Q23. What is an Alternate Key?

An alternate key is a secondary key. An alternate key is also a candidate key that has not been selected for the primary key.

If a table has more than one candidate key, one of them will become the primary key, and the rest of all are called alternate keys.

Q24. Can you add a PRIMARY Key Constraint on a nullable column?

No, A Primary Key always enforces NON-NULLability. However, if you do not specify the NOT NULL explicitly on the Primary Key column, the PRIMARY KEY does that implicitly.

But if you have specified NULL explicitly, the PRIMARY KEY keyword will generate an error.

Q25. How many PRIMARY KEYs are allowed in a table?

Only one Primary Key is allowed per table. However, if 999 Non-Clustered Index and 1  Clustered Index is already created explicitly, no Primary Key will be allowed unless you drop any existing index.

Q26. How many Clustered Indexes are allowed in any table?

Only one Clustered Index is allowed per table. It can be either by the Primary Key implicitly or by CREATE INDEX command explicitly.

Q27. Can you delete the index created by the Primary Key?

No, we cannot delete the index created by the Primary Key. If you try to do so, you will get an error.

Q28. How can you remove the Primary Key Constraint?

We can remove the Primary Key Constraint by ALTER TABLE DROP CONSTRAINT command.

ALTER TABLE tblCustomer DROP CONSTRAINT PK_CustomerID;

Q29. Can you make a VARCHAR(MAX) column as a PRIMARY KEY column in SQL? Why?

No, we cannot add a Primary Key on any MAX size data type either a VARCHAR(MAX) or NVARCHAR(MAX) column. 

The reason behind this is that an index cannot be created on these data types and a Primary Key requires an implicit index. And the reason behind, why the index cannot be created on these columns is that they are stored in a LOB, not in an 8KB data page.

Please watch the SQL 8KB Page video here for more details.

Q30. Is it mandatory to make the PRIMARY KEY column an Identity Column? What will happen if two users will try to add the same value in PRIMARY KEY at the same time?

No, it is not mandatory that a Primary Key column should be an Identity Column as well. If two users will try to add the same value in PRIMARY KEY at the same time one will fail due to Primary Key Constraint violation.

Q31. If you have Clustered Index already on a column i.e. UserName in a table, Now you have to create a PRIMARY KEY on the UserId column. How will you do that?

No problem, we can still add a Primary Key on any column, the SQL will use the Non-Clustered Index for the Primary Key.

Please watch the Clustered vs Non-Clustered Index video here for more details.

Q32. In which order the data will be displayed by default if executing the SELECT command on the table without any ORDER BY clause?

Since the data is basically stored physically based on the Clustered Index sorting order which is by default ASC. So the data will be displayed in the sorted order of the Primary Key column as it creates the Clustered Index internally.

Please watch the SQL Clustered Index video here for more details.

Q33. Why does PRIMARY KEY enforce it to be Non-Nullable and UNIQUE KEY as Nullable?

The main purpose of a Primary Key is to uniquely identify each and every row of a table by a meaning full key value. In SQL a NULL is not a value and a NULL is never equal to itself because each and every NULL is different. So how can you uniquely identify and retrieve a record based on the NULL value, if the Primary Key allows NULL?

On the other hand, a UNIQUE Key allows a single NULL value. A table can have multiple attributes or columns which requires UNIQUE Constraint along with the Nullability and they can also be a composite attribute as well. 

Please watch the SQL NULL Value here and the UNIQUE Key video here for more details.

Q34. What is a HEAP in SQL?

A Heap table is a table in which, the data rows are not stored in any particular order within each data page. A heap is a table without any Clustered Index. The IndexID for a HEAP is 0 and a Clustered Table is 1. Since Heap is an unordered pile of stuff, a complete table scan happens every time.

Please watch the SQL 8KB Page video here and Clustered Index video here for more details about Heap Table vs Clustered Table.

Q35. Can you create a PRIMARY KEY on the table variable?

Yes, we can create a Primary Key on a table variable. But it must be specified at the time of declaration. 

Please watch the Table Variable video here for more details.

Q36. Can you create a PRIMARY KEY on the Table Valued Type (TVP)?

Yes, we can create a Primary Key on Table-Valued Parameters as well. But it must be specified at the time of definition.

Please watch the Table-Valued Type video here for more details.

Live Demo

Recommended Articles

Most Frequently Asked SQL Interview Questions
Most Frequently Asked C# Interview Questions
Foreign Key Interview Questions
Unique Key Interview Questions
Primary Key Interview Questions
Derived Table (DT) Interview Questions
Unicode vs Non-Unicode Data Types Interview Questions
Table Variable (TV) Interview Questions
Table-Valued Type (TVT) Interview Questions
Common Table Expression(CTE) Interview Questions
Temp Table Interview Questions
Static Class Interview Questions and Answers
Sealed Class Interview Questions and Answers
Sealed Method Interview Questions and Answers
Abstract Class vs Interface
Interface Interview Questions and Answers

Thanks for visiting this page. Please follow us on Twitter, Facebook, LinkedIn, Telegram, Youtube, and Quora for regular updates.