SQL Server interview questions on data integrity constraints

In this article I will share some frequently asked data integrity constraints related interview questions and answers in SQL server.

1. What is table constraint?

 Constraints are used to specify rules on data to ensure quality data stored in table.

2. What are the Different Types of constraints?

=> Primary key constraint.

=> Foreign Key constraint.

=> Unique Key constraint.

=> Not Null constraint.

=> Check constraint

=> Default constraint

3. What is default constraint?

The DEFAULT constraint provides a default value to a column when an INSERT statement doesn't explicitly assign a particular value.

More info.. http://codechef4u.com/post/2015/05/04/default-constraint-in-sql-server

4. Explain primary key and foreign key constraints?

Foreign key is a column or field in current table and primary key of another table, the foreign key points to another table using primary key of that table.

The PRIMARY KEY constraint uniquely identifies each record in a database table; main goal is to prevent duplicate values for columns and provides unique identifier to each column.

More info.. http://codechef4u.com/post/2015/04/20/how-to-create-primary-key-constraint

http://codechef4u.com/post/2015/05/03/foreign-key-constraints

5. Explain PRIMARY KEY, FOREIGN KEY and Unique key?

PRIMARY KEY:

The PRIMARY KEY uniquely identifies each record in a database table; main goal is to prevent duplicate values for columns and provides unique identifier to each column.

FOREIGN KEY:

Foreign key is a column or field in current table and primary key of another table, the foreign key points to another table using primary key of that table.

Unique key:

Unique kay enforces uniqueness or prevent duplicate data on columns.

Single null value is allowed with unique key.

6. What is UNIQUE KEY Constraint?

Unique constraints are rules that enforce uniqueness or prevent duplication on column data.

More info.. http://codechef4u.com/post/2015/05/05/unique-constraint-in-sql-server

7. What is CHECK Constraint?

CHECK constraints are rules used to limit values that are accepted by one or more columns.

For example, Products with unit price between INR 5000 to INR 100000 are allowed only. This prevents product unit price from being entered beyond the regular price range.

   The logical expression would be the following: UnitPrice>= 5000 AND UnitPrice <= 100000

More info.. http://codechef4u.com/post/2015/04/24/check-constraints-in-sql-server

8. What is NOT NULL Constraint?

9. What is the difference between primary key and unique key?

Primary Key:

Primary Key can't accept null values.  

By default, Primary key is clustered index.

We can have only one Primary key in a table

Unique Key:

Unique key can accept only one null value.

We can have more than one unique key in a table.

Unique key is a unique non-clustered index.

10. What is data integrity and list types of data intigrity?

Data integrity is the consistency and accuracy of data which is stored in database.

For data integrity RDBMS follows ACID rules, enforcing data integrity ensures the quality of the data in the database.

Types:

Entity Integrity (Unique constraints, primary key)

Domain Integrity (Check constraints, null constraints, default constraints, foreign key)

Referential Integrity (Foreign key and check constraints)

User-Defined Integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).

11. Can you add constraint on table that has already some data?

Yes we can, you require to modify data in some cases for example if you want to add null constraint and

Table contains some null value in that’s case you have to add some data in column.

12. What is User-Defined Integrity?

User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).

13. What is referential Integrity?

Referential integrity preserves the defined relationships between tables when records are entered or deleted. In SQl server, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys (through FOREIGN KEY and CHECK constraints).

14. What is domain Integrity?

Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules).

15. What is entity Integrity?

Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties).

16. While creating foreign key you don’t want to check referential integrity, which keyword you have to use?

You required WITH NOCHECK in such case.

17. Can we apply unique constrains on multiple columns?

Yes that is possible.

18. How many primary keys are possible with single table?

Only one.

In this article I will share some frequently asked trigger related interview questions and answers in SQL server.

Frequently asked trigger interview questions: 

1.  Explain Trigger and trigger types?

A trigger is a special kind of a store procedure that executes in response to certain action performed on the table like insertion, deletion or updating of data

Basically there are two logical types

a. DDL triggers
b. DML triggers

DDL triggers you can categorize:

1.  Transact-SQL DDL Trigger
2. CLR DDL Trigger

DML trigger types are:

1. INSTEAD OF DML trigger
2. AFTER DML trigger

There is one special type trigger called logon trigger in sql server.

2. What are DDL Triggers and types of DDL trigger?

DDL triggers fire in response to an event happening on the server. However, DDL triggers do not fire in response to UPDATE, INSERT, or DELETE (DML) statements on a table or view. Instead, they fire in response to Data Definition Language (DDL) statements that start with the keywords CREATE, ALTER, and DROP.

DDL triggers you can categorize:

1.  Transact-SQL DDL Trigger
2. CLR DDL Trigger

3. What are DML triggers and types of DML triggers?

DDL triggers fire in response to UPDATE, INSERT, or DELETE (DML) statements on a table or view.

DML trigger types are:

1. INSTEAD OF DML trigger
2. AFTER DML trigger

4. What is Transact-SQL DDL Trigger?

A special type of Transact-SQL stored procedure that executes one or more Transact-SQL statements in response to a server-scoped or database-scoped event. For example, a DDL Trigger may fire if a statement such as ALTER SERVER CONFIGURATION is executed or if a table is deleted by using DROP TABLE.

5. What is CLR DDL Trigger?

Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL Server.

Example:

CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
   PRINT 'You must disable Trigger "safety" to drop or
alter tables!'
   ROLLBACK;

6. What is AFTER DML trigger?

AFTER triggers are executed after the action of the INSERT, UPDATE, MERGE, or DELETE statement is performed successfully.

More … http://codechef4u.com/post/2015/04/15/after-trigger

7. What is INSTEAD OF DML trigger?

DML trigger is executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. Therefore, they can be used to perform error or value checking on one or more columns and the perform additional actions before insert, updating or deleting the row or rows.

More … http://codechef4u.com/post/2015/04/15/instead-of-trigger

8. What is logon trigger?

Special type of trigger fire when LOGON event of Sql Server is raised is called lo logon trigger. You can use this trigger to audit Sql server activities, control server sessions, such as to track login activity or limit the number of sessions for a specific login.

More … http://codechef4u.com/post/2015/04/16/logon-trigger

9. Why the Trigger Fires Multiple Times in Single Login?

Reason multiple SQL Server services are running as well as intelligence is turned on.

10. Is it possible to create trigger on views?

Yes, only InsteadOf trigger.

11. How many triggers are possible per table?

One InsteadOfTrigger and any After Triggers.

12. When multiple after triggers are attached to sql table, how to control the order of execution?

Using sp_settriggerorder procedure.

13. What is the difference between For Trigger and after trigger?

It’s same.

In this article i will share some frequently asked stored procedure interview questions and answers in SQL server.

Top 10 SQL server stored procedure interview questions and answers :

1. What is a Stored Procedure?

Stored procedure is a set of Transact-SQL statements compiled into a single execution plan. You can use stored procedure with input and output parameters.

More details:

http://codechef4u.com/post/2015/05/01/stored-procedure

2. What are the Different Types of Stored procedures?

There are basically two types of stored procedures:

           1.   System stored procedures

           2.  User defined stored procedures.

More details:

http://codechef4u.com/post/2015/05/01/stored-procedure

3. What is User defined stored procedures?

A user-defined procedure is stored procedures created by user on user-defined database or in all system databases except the Resource database.

4. What is System stored procedures?

 System stored procedures are sql server inbuilt stored procedures. All system stored procedures start with prefix _sp.

5. What is Temporary Stored procedure?

Temporary stored procedures are user defined stored procedures stored in tempdb. Two types of temporary stored procedure Global and local.

6. Extended User-Defined?

Extended procedures enable creating external routines in a programming language such as C. These procedures are DLLs that an instance of SQL Server can dynamically load and run.

7. Explain some Stored Procedure creating best practices or guidelines?

Check link http://codechef4u.com/post/2015/05/06/stored-procedure1

8. Can a Stored Procedure call itself or a Recursive Stored Procedure? How many levels of SP nesting is possible?

Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves.

You can nest stored procedures and managed code references up to 32 levels.

9. How to Optimize a Stored Procedure using the Execution Plan?

1. Find the most costly statements

2. Determine why the statement is costly

3. Get an accurate baseline for the procedure

4. Optimize

10. How to Recompile Stored Procedure at Run Time?

When you create a stored procedure using the WITH RECOMPILE option, it gets a brand new execution plan every time it runs.  This can be good for high performance queries because they get a plan perfect for the variables that are passed in.

Example:

CREATE PROCEDURE dbo.GetProducts (@CatId INT)

WITH RECOMPILE

AS

SELECT *

FROM dbo.Products

WHERE Catid >=@CatId  AND DeletedDate is null

11. Explain advantages using stored procedure?

a. Group of SQL statement you can reuse.

b. Stored procedures can also improve performance.

c. Consistent, safe data modification

d. Improved security compared with inline t-sql query.

e. Sharing of application logic between applications.

f. Centralized code easy for modification and maintenance.

g. Stored procedures are cached on the server

h. Execution plans for the process are easily reviewable without having to run the application