SQL Server Interview Questions

In this article, I am going to discuss the most frequently asked basic SQL Server Interview Questions and Answers.

What is RDBMS?
Relational Database Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers.
This allows for a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

Explain DML, DDL, DCL and TCL statements with examples?
DML:
DML stands for Data Manipulation Language. DML is used to retrieve, insert, update and delete data in a database that means DML statements affect records in a table. These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records. DML statements include the following:

• SELECT – select records from a table
• INSERT – insert new records
• UPDATE – update/Modify existing records
• DELETE – delete existing records

DDL:
DDL stands for Data Definition Language. DDL statements are used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects.

• CREATE – create a new Table, database, schema
• ALTER – alter the existing table, column description
• DROP – delete existing objects from a database

DCL:
DCL stands for data control language. Generally, we will use these commands to secure database objects by creating roles, permissions using GRANT, REVOKE operations. In SQL Server, the following operations will come under DCL operations

• GRANT – allows users to read/write on certain database objects
• REVOKE – keeps users from the read/write permission on database objects

TCL:
TCL stands for Transactional Control Language. TCL is used to manage transactions within a database. Examples: COMMIT, ROLLBACK, Begin Transaction statements

• BEGIN Transaction – opens a transaction
• COMMIT Transaction – commits a transaction
• ROLLBACK Transaction – ROLLBACK a transaction in case of any error

What is the difference between Drop, Delete and Truncate statements in SQL Server?
Drop, Delete and Truncate – All operations can be rolled back.

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

All the statements (Delete, Truncate and Drop) are logged operations but the amount of information that is logged varies. Delete statement logs an entry in the transaction log for each deleted row, whereas Truncate Table logs only the Page deallocations. Hence, truncate is a little faster than Delete.

DELETE:

• The DELETE command is used to remove some or all rows from a table.
• A WHERE clause can be used with a DELETE command to remove some specific rows from a table.
• If the WHERE condition is not specified, then all rows are removed.
• The DELETE operation will cause all DELETE triggers on the table to fire.
• It does not reset the identity of the column value.
• It removes rows on the row-by-row basis and hence for each deleted row it records an entry in the transaction logs, thus this is slower than truncate.
• This is a DML command so it is just used to manipulate or modify the table data and it does not change any property of a table.

TRUNCATE:

• TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain.
• It does not require a WHERE clause, so we cannot filter rows while Truncating.
• IDENTITY columns are re-seeded on this operation if no seed was defined then the default value 1 is used.
• No Triggers are fired on this operation because it does not operate on individual rows.
• TRUNCATE removes the data by deallocating the data pages used to store the table’s data instead of rows and records, and only the page deallocations are recorded in the transaction log thus it is faster than delete.
• We cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
• TRUNCATE is DDL Command

DROP:

• The DROP command removes a table from the database.
• All the related Data, Indexes, Triggers, Constraints, and Permission specifications for the Table are dropped by this operation.
• Some objects like Views, Stored Procedures that reference the dropped table are not dropped and must be explicitly dropped.
• Cannot drop a table that is referenced by any Foreign Key constraint.
• No Triggers are fired on this operation because it does not operate on individual rows.

Note: If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if the session is closed, it cannot be rolled back but DELETE can be rolled back.

What is Cascading referential integrity constraint?
Cascading referential integrity constraints are foreign key constraints that tell SQL Server to perform certain actions when a user attempts to delete or update a primary key to which an existing foreign keys point.

SET NULL
If a delete or update statement affects rows in a foreign key table, those values will be set to NULL when the primary key record is deleted or updated. The foreign key columns affected must allow NULL values.

CASCADE
If a delete statement affects one or more rows in a foreign key table, those rows will be deleted when the primary key record is deleted. If an update statement affects rows in the foreign key table, those rows will be updated with the value from the primary key record after it has been updated.

SET DEFAULT
If a delete or update statement affects rows in a foreign key table, then all rows containing those foreign keys are set to the default value. All foreign key columns in the related table must have default constraints defined on them.

NO ACTION
This is the default action. This specifies that if an update or deletes statement affects rows in foreign key tables, then the action will be denied and rolled back. An error message will be raised.

What is the difference between where clause and having clause in SQL Server?
This is one of the most frequently asked SQL Server Interview Question and in almost all interviews this question being asked.

• WHERE clause cannot be used with aggregate functions whereas the HAVING clause can be used with aggregate functions. This means WHERE clause is used for filtering individual rows on a table whereas the HAVING clause is used to filter groups.
• WHERE comes before GROUP BY. This means WHERE clause filters rows before aggregate calculations are performed. HAVING comes after GROUP BY. This means HAVING clause filters rows after aggregate calculations are performed. So, from a performance standpoint, HAVING is slower than WHERE and should be avoided when possible.
• WHERE and HAVING clause can be used together in a SELECT query. In this case WHERE clause is applied first to filter individual rows. The rows are then grouped and aggregate calculations are performed, and then the HAVING clause filters the groups.
• WHERE clause can be used with – Select, Insert, and Update statements whereas the HAVING clause can only be used with the Select statement.

What are the differences between primary key and unique key in SQL Server?
This is of the most asked SQL Server Interview Questions in Interviews. Let discuss this question in detail.

• A table can have only one primary key. On the other hand, a table can have more than one unique key.
• The primary key column does not accept any null values whereas a unique key column accepts one null value.
• Both Primary key and unique key enforce the uniqueness of the column on which they are defined. But By default, the primary key creates a unique clustered index on the column whereas a unique key creates a unique non clustered index.

How can we copy the data from one table to another?
When we copy the data from one table to another table then the two tables should contain the same structure.

When we copy the data from one table to another table we use insert and select query. Tables always independent objects that mean a table does not depend on other tables

How to create a new table from an existing table or in how many ways we can create a new table from an existing table?
If required we can create a new table from an existing table as below.

Syntax1: (with all column from an existing table)

SELECT * INTO <NEW TABLE NAME> FROM <OLD TABLE NAME>

Example:

SELECT * INTO NEWEMPLOYEE FROM EMPLOYEE

When we execute the above query it will create a new table with all records from an existing table.

Syntax2: (with specific columns from an existing table)

SELECT <REQUIREDCOLUMN> INTO <NEW TABLE NAME> FROM <OLD TABLE NAME>

Example:

SELECT EID, SALARY INTO SPECEMP FROM EMPLOYEE

When we execute the above query it will create a new table with the specific column data from an existing table.

Syntax3: (creating a new table without data)

SELECT * INTO <NEW TABLE NAME> FROM <OLD TABLE NAME> WHERE 1 = 0

Example:

SELECT * INTO DUMMYEMP FROM EMPLOYEE WHERE 1 = 0

OR

SELECT <REQUIRED COLUMNS> INTO <NEW TABLE NAME> FROM <OLD TABLE NAME>

SELECT EID, SALARY INTO TAB1 FROM EMPLOYEE WHERE 1 = 0

When we execute the above query it will create a new table without records from an existing table.

In this article, I try to explain most frequently asked SQL Server Interview Questions and Answers. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.