SQL Interview Questions and Answers - Part 04

SQL Interview Questions and Answers - Part 04

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 4th 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

SQL Interview Questions and Answers - Part 04

Q25. What is CHECK Constraint in SQL Server?

The CHECK Constraints are nothing but the rules or set of rules that help to validate the value being inserted or updated) into a column of table based on a certain condition. They ensure that all the data in a column contains validated values according to the check constraint rule.

If the check condition evaluates to false, the record violates the constraint and isn't inserted into the table. It can be defined while creating the table or by ALTER command if the table is already created.

1. Check Constraints cannot be defined on Views.
2. The definition of a Check Constraint cannot include a subquery. Please watch the subquery video here for more details.
3. The Check Constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables.
4. The NULL values are often evaluated as true by check constraints. So using the NOT NULL constraints is the best method to control a field to contain a NULL value or not.
5. A Check Constraint can also use a scalar-valued user-defined function for evaluation logic.
6. You can also disable the Check Constraint. But doing so turns the table column into a 'NonTrusted' state.

Please read more about the CHECK constraints here.

Q26. What is Foreign Key Constraint in SQL Server?

A FOREIGN KEY Constraint is basically used to create a relationship between two tables. This constraint prevents actions that would destroy relational links between the tables.

A FOREIGN KEY is a constraint that enforces Referential Integrity in the SQL Server Database. They always enforce and ensure that the referential integrity is preserved.

A FOREIGN KEY can be a set of one or more than one column as well. In that case, it is known as a Composite Foreign Key. A Foreign Key Constraint can be defined in a single self table as well. This is known as a self-reference.

A Foreign Key Constraint prevents you from inserting or updating values that aren’t found in the related reference table's column. It also prevents deleting the records from the parent table.

Foreign Key Fundamental Points:

1. There can be more than one FOREIGN KEY Constraint in any table. In fact, more than one Foreign Key is allowed on the same columns as well. 
2. To create a SQL Foreign Key Constraint, the parent table should have either a PRIMARY KEYcolumn or UNIQUE KEY column or a Column with UNIQUE INDEX. Otherwise, you cannot reference any non-key column.
3. A FOREIGN KEY constraint specified at the column level can list only one reference column. This column must have the same data type as the column on which the constraint is defined.
4. A FOREIGN KEY constraint specified at the table level must have the same number of reference columns as the number of columns in the constraint column list. 
5. A Foreign Key can also be a Composite Foreign Key. It is important to note that all the columns in a single foreign key must point to the same table. So it is not possible to have a foreign key that references to a column in Table 1 and a column in Table 2.
6. A FOREIGN KEY Constraint cannot be enforced on Temporary Tables (both local and global). If you add the FOREIGN KEY on the Temp Table you will get a warning, but the table will be created successfully but FK Constraint will not be created along with the table.
7. A FOREIGN KEY constraint cannot be added on Table-Valued Type. 
8. A FOREIGN KEY constraint cannot be added on Table Variable as well. 
9. A FOREIGN KEY constraint can reference only tables within the same database on the same server.  If you try to do that you will get an error.

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

Q27. How the Foreign Key works? or How the Foreign Key preserves the Referential Integrity?

A Foreign Key is a constraint that enforces referential integrity in the SQL Database. The Referential Integrity says that if column value A refers to column value B, then column value B must exist.

The relationship between the child and the parent tables is maintained by checking the existence of the child table FOREIGN KEY values in the referenced table. So Referential Integrity ensures that the relationship between the database tables is preserved during the data insertion/updation/deletion process.

• A FOREIGN KEY constraint stops you from entering a value that is not found in the referenced column of the referenced table i.e stops you from adding a foreign key value that doesn’t exist in the primary key or unique key or unique index column.
• A FOREIGN KEY constraint stops you from changing the foreign key value to one which doesn’t exist as a value in the referenced table’s primary key.
• A FOREIGN KEY constraint stops you from deleting a record from the referenced table.
• A FOREIGN KEY constraint stops you from creating orphan records by making sure that the child record is always deleted first.

So when a value other than NULL is entered in the column of a FOREIGN KEY constraint, the value must exist in the Referenced Column. Otherwise, a Foreign Key violation error message is returned. 

Please read more about the Foreign Key here and watch the Foreign Key video here.

Q28. What is the difference between Primary Key and Foreign Key?

1. A primary key enforces the data integrity while a foreign key enforces the referential integrity.
2. Only one primary key is allowed in any table while multiple foreign keys are allowed in any table.
3. The primary key does not allow duplicate values while duplicate values are allowed in the foreign key.
4. Primary Key does not allow NULL values while Foreign Key allows NULL values.
5. A Foreign Key does not create any index internally while a Primary Key creates clustered index internally.
6. A primary key can be created on the temp table, but a foreign key can not be created on the temp table.
7. A primary key can be created on table variable and table-valued type but a foreign key can not be created on table variable and table-valued type.
8. A foreign key references the primary key column while a primary key is independent.

Please watch the Primary Key vs. Foreign Key video here.

Q29. What is the difference between Foreign Key Constraint and Check Constraint?

The Check constraint is also a way to enforce referential integrity. A check constraint requires a value in the database to comply with a specified condition. 

The CHECK constraint comes into action to evaluate the inserted or modified values, where the value that satisfies the condition will be inserted into the table, otherwise, the insert operation will be discarded.

A FOREIGN KEY constraint can reference only tables within the same database on the same server. So for ensuring referential integrity in Cross-Database you can use either Trigger or CHECK Constraint. Cross-Database Referential Integrity must be implemented through Triggers or CHECK Constraint.

You can also create a function and then use it in the CHECK Constraint. If the function is being referenced by the CHECK Constraint,  no one can DROP that function at all.

Please watch the Foreign Key video here for more details.

Q30. What is an index and what are the different types of indexes in SQL?

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Database indexes are used to improve the speed of database operations in a table with a large number of records.

Without indexes, you would have to search the whole table in order to perform a specific database operation. With indexes, you do not have to scan through all the table records. 

An index helps to speed up select queries and where clauses, but it slows down data input as well with the update and the insert statements.

An index is an on-disk structure associated with a table or view that speeds the retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

An index is also known as a double-edged sword. A well-designed index will enhance the performance of your system and speed up the data retrieval process. 

On the other hand, a badly-designed index will cause performance degradation in your system and will cost you extra disk space, and delay the data insertion and modification operations.

Types of Index:

SQL Server provides two main types of indexes clustered and non-clustered indexes. All other index types are derived from the two categories.

Clustered Index:

1. Clustered indexes sort and store the data rows in the table or view based on their key values. The primary key creates the clustered index by default.
2. There can be only one clustered index per table because the data rows themselves can be stored in only one order.  
3. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
4. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.

Please watch the complete video of the clustered index here.

Non-Clustered Index:

1. A nonclustered index can be defined on a table or view with a clustered index or on a heap.
2. Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key-value entry has a pointer to the data row that contains the key value.
3. There can be multiple non-clustered indexes in a table. Unique Key creates the non-clustered by default.
4. The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table.

Please watch the complete video of the non-clustered index here.

Unique Index:

1. Both clustered and non-clustered indexes can be unique as well. 
2. A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique.

Please watch the complete video of the unique index here.

There is some other derived index as well like Columnstore Index, Hash Index, Filtered Index, Spatial Index, XML Index, etc. Indexes are automatically maintained for a table or view whenever the table data is modified.

Q31. What is the difference between a Clustered Index and a Non-Clustered Index in SQL Server?

1. A table can have only one clustered index but a table can have multiple non-clustered indexes.
2. A clustered index is faster while a Non-clustered index is slower.
3. The clustered index requires less memory for operations while the Non-Clustered index requires more memory for operations.
4. In a clustered index, the index is the main data while in a Non-Clustered index, the index is separate from the actual data.
5. In a Clustered index, the leaf nodes contain the actual data itself while in the Non-Clustered index leaf nodes are not the actual data itself rather they only contain included columns.
6. In a Clustered index, Clustered key defines the order of data within the table while in a Non-Clustered index, the index key defines the order of data within the index.
7. In Clustered index table records are physically reordered to match the index but in a Non-Clustered index, the logical order of the index does not match the physical stored order of the rows on the disk.

Please read more about the index here and watch the video for the clustered index vs non clustered index here.

Q32. What is the difference between Constraint and Index?

Indexes are automatically created when PRIMARY KEY and UNIQUE constraints are defined on table columns. But these implicitly created indexes can not be deleted using the DROP command explicitly unlike the index.

When you create a table with a UNIQUE constraint, the Database Engine automatically creates a nonclustered index. If you configure a PRIMARY KEY, Database Engine automatically creates a clustered index, unless a clustered index already exists. 

When you try to enforce a PRIMARY KEY constraint on an existing table and a clustered index already exists on that table, SQL Server enforces the primary key using a nonclustered index.

To Be Continued Part-05...

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.