SQL Interview Questions and Answers - Part 03

SQL Interview Questions and Answers - Part 03

Are you preparing for the SQL Interview? If yes, then you are at the right place. This is the SQL Interview Questions and Answers article series. Here we will see Top 100+ SQL Interview Questions with Answers. 

Please visit our YouTube Channel for Interviews and other videos by below link:

Click Here To Watch Our Videos

Please read the complete Design Pattern, C#, MVC, WebAPI, and .Net Framework Interview Questions and Answers article series here.

Design Pattern Interview Questions and Answers

C# Interview Questions and Answers

MVC Interview Questions and Answers

Web API Interview Questions and Answers

.Net Framework Interview Questions and Answers

SQL Interview Questions and Answers

Introduction

This is the 3rd part of the SQL Interview Questions and Answers article series. Each part contains eight to ten SQL Interview Questions with Answers. Please read all the SQL Interview Questions list here.

I will highly recommend to please read the previous parts over here before continuing the current part:

SQL Interview Questions and Answers -  Part 01

SQL Interview Questions and Answers -  Part 02

SQL Interview Questions and Answers - Part 03

Q17. What is Constraint in SQL Server? What are the different types of Constraints available in SQL Server?

In SQL, Constraints are the rules that the database enforces to improve data integrity. They are also used to specify the business rules for data in a table.

SQL Constraints are used to restrict the type of data that can be inserted into a table. By doing so, it ensures the accuracy and reliability of the data in the table.

If there is any violation between the defined constraint and the data to be inserted, updated, or deleted, the respective action is aborted or terminated by the SQL.

A user can define constraints either at column level or table level. The column-level constraints apply only to a column, while table-level constraints apply to the whole table or set of columns.

SQL Constraints can be specified when the table is created by using CREATE TABLE statement, or after the table is created by using ALTER TABLE statement.

There are the following Constraints in SQL:

1. NOT NULL Constraint
2. UNIQUE Constraint or UNIQUE Key Constraint
3. PRIMARY Key Constraint
4. FOREIGN Key Constraint
5. CHECK Constraint
6. DEFAULT Constraint

Please read more about the SQL Constraints here.

Q18. What is the Primary Key Constraint in SQL Server?

The PRIMARY KEY is nothing but a Constraint that is used to uniquely identify each individual row or record of the table. It can be either a single column or a composite column. The purpose of the PRIMARY KEY is to enforce Entity Integrity in the table.

The PRIMARY KEY Constraint is used to uniquely identify each individual record of the table. It can be either any static column or an Identity Column. The purpose of the PRIMARY KEY is to enforce Entity Integrity in the column.

A PRIMARY KEY is a value that is unique for every record in the table and a record can be accessed by the Primary Key value directly without affecting any other record of the table.

Primary Key Fundamental Points:

1. There can be one and only one PRIMARY KEY Constraint in any table. 
2. A PRIMARY KEY cannot be created on Nullable Column, either by CREATE TABLE Command or by ALTER TABLE Command.
3. The PRIMARY KEY Constraint guarantees that no NULL or Duplicate Values are allowed except the composite Primary Key.
4. A PRIMARY KEY never creates a separate Index Data Page, unlike UNIQUE KEY. Primary Keys are always stored within the index which is created by the key itself.
5. A PRIMARY KEY is usually created for internal use only.
6. The value of a PRIMARY KEY column can never be modified or deleted if any FOREIGN KEY refers to that PRIMARY KEY. 
7. When you use ALTER TABLE statement to add a Primary Key on a column that contains data already, the Primary Key column must not contain any Duplicate Record or NULL Values.



8. A Temp Table can define a Primary Key either while creating the Temp Table by CREATE TABLE command or by ALTER TABLE statement as well. 
9. A Table Variable can also define a Primary Key but only at the time of declaration. You cannot add a Primary Key on a Table Variable later by ALTER command. 
10. A Table-Valued Type (TVP) can also have a Primary Key but it must be specified only at the time of declaration. You cannot add a Primary Key on a Table-Valued Type later by ALTER command. In fact, you cannot modify any SQL User Defined Type, you always have to drop and recreate it again.
11. A PRIMARY KEY cannot be created on any MAX Size data type for example VARCHAR(MAX), or NVARCHAR(MAX).
12. You cannot DELETE or DROP the Index created by the Primary Key explicitly.
13. A PRIMARY KEY cannot be added on View, like the Indexes. But you can add Index on a view without any issue.

Please read more about Primary Key Constraint here and the Primary Key Interview Questions and Answers article here for more details. Please also watch the Primary Key Constraint video here for more details. 

Q19. What is the difference between Primary Key and Clustered Index?

This is a famous misconception that the Primary Key and Clustered Index are the same in SQL. But it is never true. Both are two different things.

The reason behind this misconception is that a PRIMARY KEY creates a CLUSTERED INDEX by default. Since the Clustered Index is created by the Primary Key itself already, now no one can create any other Clustered Index explicitly on the table anymore.

But if there is any CLUSTERED INDEX defined already in the table, PRIMARY KEY always creates a NON-CLUSTERED INDEX forcefully.

We generally keep the Primary Key on the Clustered Index because the data is basically physically stored on the order of the Clustered Index. So it makes the data searching and retrieval very fast compared to the secondary indexes. But it is not mandatory that a Primary Key should always be a Clustered Index.

Please read the complete article of Primary Key here for more details. 

Please watch the CLUSTERED INDEX video here, and the NON-CLUSTERED INDEX video here for more details. You can also watch the Clustered Index vs Non-Clustered Index video here.

Q20. What is the Unique Key Constraint in SQL Server?

The UNIQUE Constraint ensures that all values in a column or set of columns are unique. This constraint prevents duplicate values in any table. There can be multiple UNIQUE KEY Constraints in any table. A UNIQUE KEY can also be a composite key.

The main job of the Unique Key is to place additional unique conditions on a column. A Unique Key is also known as a Unique Constraint. The UNIQUE KEY constraint specifies that each Non-NULL value in the constrained column must be unique. 

The UNIQUE Key constraint allows the NULL value by default (only one NULL value). So you can prevent NULL value by combining the NOT NULL Constraint in the UNIQUE KEY Constraint.

UNIQUE Key Fundamental Points:

1. There can be more than one UNIQUE KEY Constraint in any table.  While there can be only one Primary Key in any table. 
2. A UNIQUE KEY can be created on both Nullable and Non-Nullable Column, either by CREATE TABLE Command or by ALTER TABLE Command. But if it is a nullable column, only one NULL value will be allowed at all.
3. The UNIQUE KEY Constraint guarantees that no Duplicate Values are inserted.
4. A UNIQUE KEY generally creates a Separate Index Data Page, unlike a PRIMARY KEY. 
5. The value of a UNIQUE KEY column can never be modified or deleted if any FOREIGN KEY refers to that UNIQUE KEY. 
6. The Unique Key can be added while designing the table by CREATE TABLE statement or by ALTER TABLE statement.



7. When you use ALTER TABLE statement to add a Unique Key on a column that contains data already, the Unique Key column must not contain any Duplicate Record.
8. A Temp Table can also have a Unique Key either while creating the Temp Table by CREATE TABLE command or by ALTER TABLE statement. 
9. A Table Variable can also define a Unique Key but only at the time of declaration. You cannot add a Unique Key on a Table Variable later by ALTER command. 
10. A Table-Valued Type (TVP) can also have a Unique Key but it must be specified only at the time of type declaration. You cannot add a Unique Key on a Table-Valued Type later by ALTER command. 
11. A UNIQUE KEY also cannot be created on any MAX Size data type for example VARCHAR(MAX), or NVARCHAR(MAX).
12. You cannot DELETE or DROP the Index created by the Unique Key explicitly.
13. UNIQUE KEY cannot be added in any View but a UNIQUE INDEX can be added on View. 

Please read more about Unique Key Constraint here and the Unique Key Interview Questions and Answers article here for more details. Please watch the UNIQUE KEY Constraint video here for more details.

Q21. What is the difference between Unique Key and Unique Index?

It is a very common and famous misconception that the Unique Key and Unique Index are the same in SQL. But it is not true. Both are two different things.

The reason behind this misconception is that a UNIQUE KEY creates a UNIQUE INDEX by default. 

But the UNIQUE INDEX created by the UNIQUE KEY cannot be deleted forcefully.

You cannot add UNIQUE KEY on a view but UNIQUE INDEX can be created on View.

Please watch the UNIQUE INDEX video here for more details.

Q22. What is the difference between Primary Key and Unique Key?

Only one PRIMARY key is allowed but multiple  UNIQUE Keys are allowed.

PRIMARY key cannot allow NULL value but the UNIQUE Key allows NULL values.

PRIMARY key creates a clustered index by default while the UNIQUE Key creates a clustered index by default.

Please read more about the Primary Key here and Unique Key here. Please also watch the PRIMARY KEY vs. UNIQUE KEY video here.

Q23. What is NOT NULL Constraint?

This constraint ensures that a column cannot have a NULL value. By default all the columns are nullable. There can be multiple NOT NULL Constraint in any table. Please read more about the NOT NULL Constraints here.

The SQL NOT NULL Constraint can be used with other constraints together as well. Primary Key Constraint uses it along with Unique Key internally. We also use the NOT NULL constraint along with UNIQUE Key Constraint to avoid NULL values.

Please watch the SQL NULL values video here and the SQL Three Valued Logic video here for more details.

Q24. What is Default Constraint?

In SQL, a DEFAULT Constraint provides the default value when no value is supplied into the column. Actually, there should be some value in all the columns. So this DEFAULT constraint is used to specify a default value explicitly if none is supplied.

The ANSI SQL standard defines DEFAULT as being a column attribute whereas Microsoft implemented DEFAULT as a type of constraint. 

So you cannot change a DEFAULT using ALTER TABLE...ALTER COLUMN syntax. Therefore, you have to drop the DEFAULT Constraint and re-add the DEFAULT constraint with the new default value.

To Be Continued Part-04...

Recommended Articles

Static Class Interview Questions and Answers

Sealed Class Interview Questions and Answers

Sealed Method Interview Questions and Answers

C# Coding Interview Questions and Answers

Abstract Class vs Interface Interview Question

IDisposable Interface vs Finalizer in .Net

Singleton Design Pattern

SOLID Design Principles

Liskov Substitution Principle of SOLID

Dependency Inversion Principle of SOLID

Covariance vs Contravariance Feature of .Net Framework

Types of Constructors in C#

Static Class and Static Methods

Partial Class and Partial Methods

Abstract Class and Methods

Sealed Class and Sealed Methods

Static Polymorphism and Method Overloading

Dynamic Polymorphism and Method Overriding

Thanks for visiting this page. Please follow and join us on LinkedIn, Facebook, Telegram, Quora, YouTube, Twitter, Pinterest, Tumbler, and VK for regular updates.