Microsoft SQL Server Interview Questions & Answers

1. What is SQL Server?

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is used to store and manage data in a structured manner.

2. What is a Relational Database Management System and name some of the RDBMS available in the market?

A Relational Database Management System (RDBMS) refers to the software used to store, manage, and query data. Data is stored in tables and can be linked to other datasets based on shared information, hence the name “relational”. 

Some of the most popular RDBMS are:

• Oracle Database
• MySQL
• Microsoft SQL Server
• PostgreSQL
• IBM DB2
• SQLite

3. How does RDBMS differ from a DBMS?

The key differences between Relational Database Management Systems (RDBMS) and Database Management Systems (DBMS) are:

> An RDBMS stores data in a relational table with rows and columns, whereas a DBMS stores data as a file.

> An RDBMS provides access to multiple users (including client-server side interaction), whereas a DBMS only supports single users.

4. What is the role of a SQL and difference between SQL and MySQL?

SQL is a programming language used to perform data-related tasks; every RDBMS uses SQL as its standard programming language. In these databases, SQL allows users to create tables, update data, make queries, and perform analytics.

> MySQL is an example of an RDBMS. MySQL was one of the first open-source database systems on the market, and it is still fairly popular in the market. 

> SQL is the licensed product of Microsoft & MySQL open-source platform managed by Oracle Corporation.

5. What is a SQL query and What are the main types of SQL queries?

A query can be seen as a request for data results or for a certain action on data (combine data from multiple tables, add, modify the data or remove them from the database).

There are five types of SQL queries:

> Data Definition Language (DDL) – to create objects

> Data Manipulation Language (DML) – to manipulate the data 

> Data Control Language (DCL) – to assign and remove permissions 

> Transaction Control Language (TCL) – to save and restore changes to a database

>  Data Query Language (DQL) commands retrieve information from the database

6. What are the most important types of action queries?

There are several SQL statements for running an action query. Their purposes and procedures vary.

Some of the important action statements include:

• UPDATE modifies the values of fields in a table
• DELETE removes records from a table
• CREATE TABLE creates a new table
• INSERT INTO adds records to a table

7. What is a subquery?

A subquery is a query that is embedded within another statement that requires multiple steps. The subquery provides the enclosing query with additional information needed to execute a task, such as when the completion of one query depends firstly on the results of another.

The following are the subquery types:

• Subqueries with table aliases
• Subqueries with IN and NOTIN
• Subqueries in UPDATE, DELETE and INSERT statements
• Subqueries with comparison statements
• Comparison operators modified by ANY, SOME or ALL
• Subqueries with EXISTS and NOT EXISTS

8. What are tables and fields in SQL?

A table is an arrangement of data in a database. Traditionally it consists of rows (or records) and fields (or columns). Records are collections of values of a certain entity, whereas the term “field” denotes an area within a record meant for a particular piece of information.

9. How to create a database and Table in SQL?

> To create a database in SQL, use the following command:

CREATE DATABASE database_name.

> The following command is used to create a table:

CREATE TABLE table_name (

   column1 datatype constraints,

   column2 datatype constraints,

   ...

   columnN datatype constraints

);

Unlock your potential in SQL Server with our SQL Server Developer Training program.

10. What command is used to rename the database?

sp_renamedb 'oldname', 'newname';

11. What are constraints?

SQL constraints are a set of rules or conditions implemented on an RDBMS to specify what data can be inserted, updated, or deleted in its tables. This is done to maintain data integrity and ensure that the information stored in database tables is accurate.

12. What are the most important SQL constraints and how are they used?

Here are some of the most commonly used SQL constraints:

• NOT NULL ensures a column cannot contain a NULL value
• UNIQUE ensures all values in a column are different
• DEFAULT provides a default value for a column when none is specified
• INDEX creates an index for data retrieval purposes
• CHECK checks values in a column against certain specified conditions

13. What is Normalization in SQL?

Normalization is the multi-step process of organizing the data in the database to eliminate data redundancy and ensure data integrity.

There are different types of normalization, commonly known as normal forms. The most widely used normal forms are:

First Normal Form (1NF): In 1NF, data is organized into tables with rows and columns, and each column contains only atomic values (indivisible values). There should be no repeating groups or arrays within a single column.

Second Normal Form (2NF): 2NF builds upon 1NF by ensuring that each non-key column in a table is fully dependent on the entire primary key. In other words, there should be no partial dependencies, where a non-key column depends on only part of the primary key.

Third Normal Form (3NF): 3NF further eliminates transitive dependencies. It states that each non-key column in a table should depend only on the primary key and not on other non-key columns. This ensures that the data is free from duplication and redundancy.

Boyce-Codd Normal Form (BCNF): BCNF is an advanced form of normalization that addresses certain limitations of 3NF. It ensures that for every non-trivial functional dependency (X -> Y), X should be a superkey, meaning it uniquely identifies all other attributes in the table.

Fourth Normal Form (4NF): 4NF deals with multivalued dependencies. It states that no non-key column should depend on a set of other non-key columns. It helps in further eliminating redundancy and improving data integrity.

Fifth Normal Form (5NF): 5NF, also known as Project-Join Normal Form (PJ/NF), focuses on eliminating join dependencies. It aims to decompose a table into smaller tables to reduce the complexity of join operations.

These are the commonly recognized normal forms in SQL Server. It's important to note that achieving higher normal forms doesn't necessarily mean better performance, as denormalization might be necessary in some cases for performance optimization. Normalization should be balanced with the specific requirements and performance considerations of the database application.

14. What is Denormalization?

Denormalization is a database optimization technique for increasing a database infrastructure performance by adding redundant data to one or more tables. 

15. What is a JOIN, and mention type of joins?

JOIN is a logical operation used to retrieve data from two or more tables. It can only be accomplished when there is a logical relationship between two tables. Here, data from one table is used to select rows in another table.

There are few types of logical JOIN operations as mentioned below:

• INNER JOIN - The INNER JOIN returns all records from the both tables for which the join condition is true. It is also known as EQUIJOIN.
• LEFT (OUTER) JOIN - The LEFT OUTER JOIN returns all records of left table and matching records of right table. When no match is found, right table columns will be returned with the null values.
• RIGHT (OUTER) JOIN - The RIGHT OUTER JOIN returns all records of right table and matching records of left table. When no match is found, left table columns will be returned with the null values.
• FULL (OUTER) JOIN - The FULL OUTER JOIN returns the result of the combination of left and right outer joins.
• CROSS JOIN - The CARTESIAN JOIN or CROSS JOIN return the data by joining the every row of one table to every row of another table i.e it returns the Cartesian product of two tables.
• SELF JOIN - The SELF JOIN is used to join the table to itself that is why it is known as SELF JOIN.

16. What is a primary, foreign key and unique key?

> The PRIMARY KEY constraint is used to uniquely identify each row in a table. A PRIMARY KEY must contain unique values and it can’t contain a null value. A table can have only one primary key. We can use multiple columns or fields to define a primary key, such a primary key is known as a composite key. 

> The FOREIGN KEY is used to define a relationship between two tables and a FOREIGN KEY in one table points to the PRIMARY KEY in another table.

> A UNIQUE KEY is a set of one or more than one field/column of a table that uniquely identifies a record in a database table. A primary key is a special kind of unique key.

17. Is a NULL value the same as zero or a blank space? If not then what is the difference?

A NULL value is not the same as zero or a blank space. A NULL value is a value which is ‘unavailable, unassigned, unknown or not applicable’. Whereas, zero is a number and blank space is a character.

18. What is the role of indexes?

An SQL index stores important parts of a database table to allow for a quick and efficient lookup. Rather than searching the entire database, users only have to consult the index during data retrieval. Indexes, therefore, help improve performance in an RDBMS.

19. How is the cursor used in SQL?

The cursor allows users to process data from a result set, one row at a time. Cursors are an alternative to commands, which operate on all rows in a result set at the same time. Unlike commands, cursors can be used to update data on a row-by-row basis.

20. How do you select all even or odd numbers in a table?

The MOD function can be used in most RDBMSs as part of the WHERE statement in a select query to retrieve odd or even data entries in a table. 

The formatting is as follows:

> For even numbers, use ‘MOD (column name, 2) = 1’

> For odd numbers, use ‘MOD (column name, 2) = 0’

21. What are some ways to prevent duplicate entries when making a query?

There are several methods to avoid duplicate entries when making a query, such as to:

> Create a unique index

> Add the DISTINCT keyword to the SELECT statement

> Use the NOT EXISTS or NOT IN commands

22. What are the key differences between the DELETE and TRUNCATE SQL commands, DROP and TRUNCATE commands?

The main differences between the DELETE and TRUNCATE commands are:

• DELETE is a DML command, whereas TRUNCATE is a DDL command
• DELETE removes records and records each deletion in the transaction log, whereas TRUNCATE deallocates pages and records each deallocation in the transaction log
• TRUNCATE is generally considered quicker as it makes less use of the transaction log
• Rolling back data after using the delete statement is possible. Truncate will not support the roll back feature. 
• > The command of DROP is used to remove a table completely, and the following can be reverted from the Database.
• > On the other hand, the command of TRUNCATE removes all the rows in the table.

23. What are the different types of Database relationships in SQL?

There are three types of Database Relationship –

• One-to-one – Both tables can have only one record
• One-to-many – The single record in the first table can be related to one or more records in the second table
• Many-to-many – Each record in both the tables can be related to any number of records

24. When do you use SQL Profiler?

SQL Profiler utility allows us to basically track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc.

25. What are the ACID properties in SQL?

ACID is an acronym for Atomicity, Consistency, Isolation, and Durability. These are the four key properties for ensuring data integrity during a transaction. 

The role of each property is as follows:

• Atomicity: Changes to data are performed as a single, unified operation
• Consistency: Data values are consistent at the start and end of the transaction
• Isolation: The intermediate state of a transaction is hidden from other transactions
• Durability: Changes to data remain the same after the transaction is completed

26. What is a stored procedure in SQL Server and its advantages?

In SQL Server, a stored procedure is a named collection of SQL statements and procedural logic that is stored in the database. It is precompiled and stored as a database object, allowing it to be executed repeatedly without recompilation. A stored procedure is typically used to encapsulate a set of commonly performed operations or business logic, providing a convenient and efficient way to execute complex tasks.

> To create a stored procedure in SQL Server, you use the CREATE PROCEDURE statement. The procedure definition includes the SQL statements, input/output parameters, and any procedural logic. Once created, you can execute the stored procedure using the EXECUTE statement or by simply calling its name.

CREATE PROCEDURE ProcedureName

    @Parameter1 DataType,

    @Parameter2 DataType

AS

BEGIN

    -- SQL statements and procedural logic here

END

Advantages of using stored procedures in SQL Server:

• Stored procedures help in reducing the network traffic and latency. It boosts up the application performance.
• Stored procedures facilitate the reusability of the code.
• Stored procedures provide better security for data.
• You can encapsulate the logic using stored procedures and change stored procedure code without affecting clients.
• It is possible to reuse stored procedure execution plans, which are cached in SQL Server's memory. This reduces server overhead.
• It provides modularity of application.

27. Name few types of Stored procedures

• User-defined stored procedures, which are created by users
• System stored procedures are default procedures placed permanently on the system
• Temporary stored procedures are procedures that are dropped when the session is closed
• Remote stored procedures, which are created and stored on remote servers

28. What are the main differences between HAVING and WHERE SQL clauses?

The key differences between HAVING and WHERE SQL clauses are:

• The WHERE clause is used in row operations, whereas the HAVING clause is used in column operations.
• The WHERE clause comes before GROUP BY in a query, whereas the HAVING clause comes after GROUP BY.
• The WHERE clause cannot be used with aggregate functions, contrary to the HAVING clause.

29. How to use LIKE in SQL?

We use the LIKE operator in the WHERE clause if we need to look for a particular pattern in a column.

SELECT * FROM Employees WHERE first_name like ‘Adam’

30. What is ALIAS in SQL?

We use the ALIAS command to give a column in a table or a table itself a temporary name with the purpose of making a column header easier to read.

31. What are SQL injections and how can they be prevented?

An SQL injection is a type of cyber attack in which hackers insert malicious SQL code into the database to gain access to potentially valuable or sensitive information. It’s a fairly common occurrence with web applications or websites that use an SQL-based database.

It’s possible to prevent SQL injections by creating multiple database accounts to limit access or by using a third-party web application firewall.

32. What are some ways to prevent duplicate entries when making a query?

There are several methods to avoid duplicate entries when making a query, such as to:

• Create a unique index
• Add the DISTINCT keyword to the SELECT statement
• Use the NOT EXISTS or NOT IN commands

33. How to restore the database in SQL Server?

First, launch the SQL Server Management Studio app. From the window pane called Object Explorer, click the right mouse button on databases and choose Restore. Your database will be automatically restored.

34. Define Magic Tables in SQL server?

A Table which is automatically created and managed by SQL server internally to store the inserted, updated values for any DML (SELECT, DELETE, UPDATE, etc.) operation, is called Magic tables in SQL server. The triggers preferably use it.

35. What is the FLOOR function in the SQL server?

This function allows returning the largest integer value, which is less than or equal to the specified value.

The syntax for this function is provided as:

FLOOR ( numeric_expression )

36. What is the usage of the SIGN function?

This function is used to determine whether the mentioned number is zero, positive, and negative. So, it will either return 0, +1, -1.

Syntax: SIGN(number)

Example:

SIGN (0)  returns 0

SIGN (9)  returns 1

SIGN (-9)  returns -1

To get certified in the language and start a prosperous career, check out our SQL Server Developer Training. Happy Learning!