Top 50 SQL Server Interview Questions and Answers

Top SQL Server Interview Questions and Answers: An Overview

Q.1. What is an SQL Server?

Microsoft SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft. It is designed to run on a central server so that multiple users can access the same data simultaneously. Generally, users access the database through an application.

Q.2. What are the different versions of SQL Server?

There are the following versions of SQL Server have been released at the time of writing this book:

1. Version

   Year

   Release Name

   8.0

   2000

   SQL Server 2000

   8.0

   2003

   SQL Server 2000 (64-bit)

   9.0

   2005

   SQL Server 2005

   10.0

   2008

   SQL Server 2008

   10.5

   2010

   SQL Server 2008 R2

   11.0

   2012

   SQL Server 2012

   12.0

   2014

   SQL Server 2014

   13.0

   2016

   SQL Server 2016

   14.0

   2017

   SQL Server 2017

Q.3. What new features are coming to SQL Server 2019?

At the time of writing this book, SQL Server 2019 RTM was not released. The following features are coming in the SQL Server 2019 release.

1. • The single virtual data layer

   • Data virtualization and Integrating Data

   • No data replication and Managing all data

   • Spark Built-In

   • Unified platform for big data analytics

   • Spark jobs

   • Train machine learning models

Q.4. Can you access or query a remote SQL Server database from a Mac, Linux, or Ubuntu machine?

Yes, you can connect or query your remote SQL Server database from your Mac, Linus, or Ubuntu machines using the Azure Data Studio tool.

Q.5. What is Azure Data Studio?

Azure Data Studio is an alternative way to SQL Server Management Studio (SSMS) which you can run only on Windows machines to query, edit, and data development tasks. Azure Data Studio offers a modern editor experience to connect with a remote SQL Server database. It helps us to query and manage data across multiple sources with IntelliSense.

Q.6. What is the difference between DBMS and RDMS?

DBMS and RDBMS, both are used to store, manage, and query the data. But both have some important differences as listed below-

1. DBMS (Database Management System)

   RDBMS (Relational Database Management System)

   DBMS stands for Database Management System and treats data as files internally.

   RDBMS stands for Relational Database Management System and treats data as relations means tables.

   It defines the relationship between the files programmatically.

   It defines the relationship between the relations called tables at the time of table creation.

   It does not impose any constraints or security concerning data manipulation.

   It imposes constraints or security on data manipulation.

   It does not support distributed architecture.

   It supports distributed architecture

   It does not support Client-Server architecture

   It supports Client-Server Architecture

   Only one user can access data at a time

   Multiple users can access the data at the same time

   It satisfies a maximum of 6 to 7 rules of E.F. Codd (Edgar Frank "Ted" Codd) out of 12 rules.

   It satisfies more than 6 to 7 rules of E.F. Codd out of 12 rules.

   Example- File System, XML, FoxPro, IMS

   Example – SQL Server, Oracle, DB2, MySQL

Q.7. What is normalization?

Normalization or data normalization is a process to organize the data into a tabular format (database tables) keeping two goals in mind.

1. • Reducing data redundancy.

   • Ensuring data dependency.

     A good database design includes normalization, without normalization, a database system may be slow, inefficient, and might not produce the expected result. Normalization reduces data redundancy and inconsistent data dependency.

Q.8. What are the different normal forms?

We organize the data into database tables by using normal form rules or conditions. Normal forms help us to make a good database design. There are the following normal forms:

1. 1. First Normal Form (1NF)

   2. Second Normal Form (2NF)

   3. Third Normal Form (3NF)

   4. BCNF

   5. Fourth Normal Form (4NF)

   6. Fifth Normal Form (5NF)

Generally, we organize the data up to the third normal form. We rarely use the fourth and fifth normal forms.

Q.9. What are the differences between char and nchar?

These data type is used to store characters but these are different in many cases as given below:

char

This is a fixed-length characters data type. It takes one byte per character and is used to store non-Unicode characters. Suppose, you declare a field with char(20) then it will allocate memory for 20 characters whether you are using only 10 characters. Hence memory for 10 characters that is empty will be wasted.

nchar

This is like a char data type but it takes two bytes per character and is used to store Unicode characters, which means multiple languages (like Hindi, Chinese, etc.) characters in the database.

Q.10. What are the differences between varchar and nvarchar?

There are the following differences between varchar and nvarchar:

varchar

This is a variable length characters data type. It takes one byte per character and can store non-Unicode characters (like English). This data type allocates the memory based on the number of characters inserted. Hence, no waste of memory.

nvarchar

This is like a char data type but it takes 2 bytes per character and is used to store Unicode characters, which means multiple languages (like Hindi, Chinese, etc.) characters in the database.

Q.11. What is SQL key?

A key is a single or combination of multiple fields in a table. It is used to retrieve records or data rows from the data table based on the condition. Keys are also used to create a relationship among different database tables or views.

Q.12. What are the different types of SQL Keys?

There are the following types of SQL Keys –

1. 1. Super Key - The superkey is a set of one or more than one key that can be used to identify a record uniquely in a table.

      For Example, the Primary key, Unique key, and Alternate key are a subset of Super Keys.

   2. Candidate Key - A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as a Primary Key.

      For Example: In the below diagram ID, RollNo, and EnrollNo are Candidate Keys since all these three fields can work as Primary Key.

   3. Primary Key -The primary key is a set of one or more fields/columns of a table that uniquely identify a record in a database table. It cannot accept null, or duplicate values. Only one Candidate Key can be the Primary Key.

   4. Alternate key - An alternate key is a key that can work as a primary key. It is a candidate key that currently is not the primary key.

      For Example:In the below diagram, RollNo and EnrollNo become Alternate Keys when we define ID as Primary Key.

   5. 

   6. Composite/Compound Key -A Composite Key is a combination of more than one field/column of a table. It can be a Candidate key or a primary key.

   7. Unique Key - The unique key is a set of one or more fields/columns of a table that uniquely identifies a record in a database table. It is like a Primary key but it can accept only one null value and it cannot have duplicate values.

   8. Foreign Key -A Foreign Key is a field in a database table that is the Primary key in another table. It can accept multiple null, and duplicate values.

      For Example, We can have a DeptID column in the Employee table which is pointing to the DeptID column in a department table where it is a primary key.

      Note- Practically in the database, we have only three types of keys Primary Key, Unique Key, and Foreign Key. Other types of keys are only concepts of RDBMS that we need to know.

Q.13. What are SQL Commands?

SQL commands are a set of instructions that are used to interact with the database like SQL Server, MySQL Oracle, etc. SQL commands are responsible for creating and doing all the manipulation on the database. These are also responsible to give or take out access rights on a particular database. There are the following types of commands:

1. • Data Definition Language (DDL)

   • Data Manipulation Language (DML)

   • Data Query Language (DQL)

   • Transaction Control Language (TCL)

   • Data Control Language (DCL)

Q.14. What is a Database Table?

An RDBMS stores the data using one than a database table. A database table manages the data in row and column format. Each row in a table has its primary key which uniquely identifies that row or record. The data associated with tables are physically stored in the database memory.

Q.15. What is a transaction?

A transaction is a set of T-SQL statements that are executed together as a unit like a single T-SQL statement. If all of these T-SQL statements are executed successfully, then a transaction is committed and the changes made by T-SQL statements are permanently saved to the database. If any of these T-SQL statements within a transaction fail, then the complete transaction is canceled/ rolled back.

Q.16. Why use transactions in SQL Server?

We use transaction in that case when we try to modify more than one table or view that is related to e each other. Transactions affect SQL Server performance greatly. Since, when a transaction is initiated then it locks all the tables’ data that are used in the transaction. Hence during the transaction life cycle, no one can modify these tables’ data that are used by the transaction. The reason behind the locking of the data is to maintain Data Integrity.

Q.17. What are the different types of transactions in SQL Server?

There are the following types of transactions in SQL Server as given below:

1. • Implicit Transaction

   • Explicit Transaction

Q.18. Write SQL queries to get the nth highest and lowest salary of an employee.

The queries are given below-

1. Query to get nth (3rd) highest Salary:

   Select TOP 1 Salary as '3rd Highest Salary' 
   from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary DESC) 
   a ORDER BY Salary ASC
   

   Query to get nth (3rd) lowest Salary:

   Select TOP 1 Salary as '3rd Lowest Salary' 
   from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary ASC) 
   a ORDER BY Salary DESC 
   

Q.19. Write an SQL query to get the field name, data type, and size of a database table.

The query is given below-

1. SELECT column_name as 'Column Name', data_type as 'Data Type',
   character_maximum_length as 'Max Length'
   FROM information_schema.columns
   WHERE table_name = 'tblUsers' 
   

   

Q.20. What are SQL Joins?

SQL joins are used to retrieve data from two or more data tables, based on a join condition. A join condition is a relationship among some columns in the data tables that take part in SQL join. In SQL Server, there are three types of joins as given below:

1. • Inner Join

   • Outer Join

   • Cross Join

Q.21. Explain different data types in SQL Server.

The various data type classifications are as follows:

• Exact numerics: Whole numbers (integers) and decimal numbers are stored in these data types. Decimal, numeric, money, smallmoney, bit, tinyint, smallint, int, bigint, and so on are among them.
• Approximate numerics: These data types are used to record numbers with fractional parts, however, they are less precise than exact numerics. Real and float are among them.
• Date and time: Timestamps, dates, and times are stored in these data types. Time, date, datetime2, datetimeoffset, smalldatetime, and date are among them.
• Character strings: Text data is stored in these data types. Text, char, varchar, nchar, nvarchar, & ntext are among them.
• Binary strings: Binary data, such as sounds or pictures, are stored in these data formats. They consist of picture, varbinary, and binary.
• Others: Data types that don't fit into any of the other categories are included in this category. They consist of spatial geometry types, XML, cursor, hierarchyid, sql_variant, table, rowversion, and uniqueidentifier.

Q.22. Define DDL Statements in SQL Server.

A type of SQL command called DDL (Data Definition Language) is used to define data structures and alter data. Tables, views, indexes, and users are just a few of the database objects that it may create, modify, and remove. DDL statements include concepts like CREATE, ALTER, DROP, and TRUNCATE.

Q.23. Explain DML Statements in SQL Server.

Data Manipulation Language is shortened to DML. consists of a group of computer languages that are specifically used to alter databases using CRUD activities (create, read, update, and delete data). use the commands INSERT, SELECT, UPDATE, and DELETE.

Q.24. What are DCL Statements in SQL Server?

A subset of SQL commands called Data Control Language (DCL) is used to manage who has access to what data in a database. In multi-user database setups, in particular, DCL plays an essential part in guaranteeing security and appropriate data management. It includes GRANT & REVOKE

Q.25. What are the functions in SQL Server?

Database objects known as SQL Server functions comprise a collection of SQL statements that work together to carry out a certain task. A function receives input, processes it, and outputs the result. It is important to remember that functions always return a table or a single result.

Q.26. Define Views in SQL Server.

In SQL Server, a VIEW is comparable to a virtual table that holds information from one or more tables. It does not physically exist in the database and does not contain any data. A view name in a database should be unique, much like a SQL table. It includes a list of pre-written SQL queries to retrieve information from the database.

Q.27. What are indexes in SQL Server?

The table or view's columns can be used as building blocks for keys in an index. Because these keys are kept in a structure called a B-tree, SQL Server can locate the row or rows that are connected to the key values with speed and efficiency.

Q.28. Explain the Stored Procedure.

An SQL code that has been prepared and saved for later use is called a stored procedure. To avoid writing the same SQL query twice, consider saving it as a stored procedure that can be called whenever needed. A stored procedure can also receive parameters, which allows it to take action based on the parameter value or values that are supplied.

Q.29. What do SQL Server triggers do?

You can designate SQL operations that should be carried out automatically when a particular event takes place in the database by using an SQL trigger. A trigger, for instance, can be used to update a record in one table automatically each time a record is added to another table.

Q.30. What do SQL Server cursors mean?

In SQL Server, a cursor is a database object that lets us access and modify individual rows one at a time. All that a cursor is is a pointer to a row. It is always utilized with a SELECT statement attached.

Q.31. What is security in SQL Server?

Data can be shielded from unauthorized access using SQL security. Users can only access the features or capabilities they need. A whole database instance, a table, and a database are the three security levels you can select when setting up a SQL server.

Q.32. What does SQL Server authentication mean?

Authentication with SQL Server works similarly. Users connect to the database, which verifies details like port number, domain and instance names, and user account credentials.

Q.33. What is SQL Server auditing?

Monitoring and recording system events is a necessary part of auditing a SQL Server server or database. A single instance of server-level or database-level activities, as well as sets of actions, are gathered by the SQL Server Audit object for monitoring. The SQL Server instance level is being audited.

Q.34. What does SQL Server backup and recovery mean?

In general, backup and recovery refer to the several approaches and procedures used to safeguard your database from data loss and restore the data if it does occur.

Q.35. Explain replication in SQL Server.

A group of technologies known as replication is used to copy and distribute database objects and data from one database to another, synchronizing afterward to preserve consistency across databases. Replication can be used to send data over dial-up, wireless, local and wide area networks, the Internet, and other networks to users who are remote or mobile.

Q.36. Describe SQL Server clustered indexes.

Based on their key values, clustered indexes sort & store the data rows in the table or view. The columns that make up the index definition are these key values. Because the data rows themselves can only be stored in a single order, each table can only have one clustered index.

Q.37. What are non-clustered indexes in SQL Server?

Search processes are also speeding up by using a non-clustered index. A non-clustered index does not physically specify the sequence in which records are added to a table, unlike a clustered index. In actuality, the data table and the non-clustered index are kept apart.

Q.38. What are unique indexes in SQL Server?

An index that guarantees that the values in the indexed columns are distinct throughout the table is known as a unique index in SQL Server. This indicates that the values in the columns that comprise the unique index cannot be the same in two rows.

Q.39. What is query optimization in SQL Server?

Query optimization is the practice of improving SQL queries to minimize resource usage, speed up system execution, and enhance overall system performance. The main goals of this optimization are to reduce pointless data retrieval, streamline join processes, and make better use of available resources.

Q.40. What is an execution plan in SQL Server?

The set of instructions known as the SQL Server execution plan, or query plan, specifies the steps that must be taken for the database engine to carry out a query. The primary objective of the query optimizer, which creates the query plans, is to produce the most inexpensive and efficient query plan.

Q.41. Can you define all the keys in a database table?

Practically in a database table, you can have only three types of keys: Primary Key, Unique Key, and Foreign Key. Other types of keys are only concepts of RDBMS that you need to know.

Q. 42. What are the differences between the Primary Key and the Unique Key?

Q. 43. What is the difference between Primary Key and Foreign Key?

Q. 44. What are TCL Commands?

Q. 45. What are DQL Commands?

Q. 46. What are Grant and Revoke Commands?

• Grant Command: This command is used to permit specific users on specific database objects like table, view, etc.
• Revoke Command: This command is used to take out permission from specific users on specific database objects like table, view, etc.

Q.47. What are SQL Constraints or SQL Integrity Constraints?

1. Column Level: These constraints are defined with the column definition inside a CREATE TABLE statement.
2. Table Level: These constraints are defined after the table is created using the ALTER TABLE statement.

Q. 48. What is the need for a SQL view?

Q. 49. What are Simple View and Complex View?

• Simple View - When we create a view on a single table, it is called a simple view. In a simple view, we can insert, update, and delete data. We can only insert data in a simple view if we have a primary key and all not null fields in the view.

• Complex View - When we create a view on more than one table, it is called a complex view. We can only update data in the complex view. We can't insert, or delete data in the complex view.

Q. 50. What are the different types of SQL Server Views?

1. Simple View
2. Complex View

Summary

I hope these questions and answers will help you to crack your SQL Server Interview. These interview Questions have been taken from our newly released eBook SQL Server Interview Questions & Answers. This book contains more than 100+ SQL interview questions.

This eBook has been written to make you confident in SQL Server with a solid foundation. Also, this will help you to turn your programming into your profession. It would be equally helpful in your real projects or to crack your SQL Server Interview.

Buy this eBook at a Discounted Price!

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE