Top 20 SQL Server Interview Questions and Answers
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. This article contains the top 20 SQL Server interview questions and answers, in order to prepare you for the interview.
What is 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.
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:
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 SQL Server 2019 release.
The single virtual data layer
Data virtualization and Integrating Data
No data replication and Managing all data
Unified platform for big data analytics
Train machine learning models
Can you access or query remote SQL Server database from a Mac, Linux or Ubuntu machine?
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, editing, 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.
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-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 with regard to data manipulation.It imposes constraints or security with regard to data manipulation.It does not support distributed architecture.It supports distributed architectureIt does not support Client-Server architectureIt supports Client-Server ArchitectureOnly one user can access data at a timeMultiple users can access the data at the same timeIt satisfies maximum 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, IMSExample – SQL Server, Oracle, DB2, MySQL
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.
Reducing data redundancy.
Ensuring data dependency.
A good database design includes the normalization, without normalization, a database system may slow, inefficient and might not produce the expected result. Normalization reduces data redundancy and inconsistent data dependency.
What are the different normal forms?
We organize the data into database tables by using normal forms rules or conditions. Normal forms help us to make a good database design. There are following normal forms:
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Fourth Normal Form (4NF)
Fifth Normal Form (5NF)
Generally, we organize the data up to the third normal form. We rarely use the fourth and fifth normal form.
What are the differences between char and nchar?
These data type is used to stores characters but these are different in many cases as given below:
This is a fixed length characters data type. It takes one byte per character and 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 which is empty will be wasted.
This is like as char data type but it takes two bytes per character and used to store Unicode characters means multiple languages (like Hindi, Chinese, etc.) characters in the database.
What are the differences between varchar and nvarchar?
There are the following differences between varchar and nvarchar:
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 wastage of memory.
This is like as char data type but it takes 2 bytes per character and used to store Unicode characters means multiple languages (like Hindi, Chinese, etc.) characters in the database.
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.
What are different types of SQL Keys?
There are following types of SQL Keys –
Super Key - the Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table. For Example Primary key, Unique key, Alternate key are a subset of Super Keys.
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 Primary Key. For Example: In the below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.
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, duplicate values. Only one Candidate Key can be Primary Key.
Alternate key - A Alternate key is a key that can be work as a primary key. Basically, it is a candidate key that currently is not the primary key. For Example: In the below diagram, RollNo and EnrollNo becomes Alternate Keys when we define ID as Primary Key.
Composite/Compound Key - Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.
Unique Key - the Unique key is a set of one or more fields/columns of a table that uniquely identify 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.
Foreign Key - Foreign Key is a field in a database table that is Primary key in another table. It can accept multiple null, 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 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.
What are SQL Commands?
SQL commands are a set of instructions that are used to interact with the database like SQL Server, MySQL and Oracle etc. SQL commands are responsible to create and to do 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:
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Query Language (DQL)
Transaction Control Language (TCL)
Data Control Language (DCL)
What is Database Table?
An RDBMS store the data using one than a database table. A database table manages the data in row and columns format. Each row in a table has its own primary key which uniquely identifies that row or record. The data associated with tables are physically stored in the database memory.
What is a transaction?
A transaction is a set of T-SQL statements that are executed together as a unit like as a single T-SQL statement. If all of these T-SQL statements executed successfully, then a transaction is committed and the changes made by T-SQL statements permanently saved to the database. If any of these T-SQL statements within a transaction fail, then the complete transaction is cancelled/ rolled back.
Why use transaction in SQL Server?
We use transaction in that case when we try to modify more than one table or views that are 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.
What are different types of transaction in SQL Server?
There are following types of transactions in SQL Server as given below:
Write SQL queries to get the nth highest and lowest salary of an employee?
The queries are given below-
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
Write SQL query to get field name, data type, and size of a database table?
The query is given below-
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'
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:
I hope these questions and answers will help you to crack your SQL Server Interview. These interview Questions have been taken from our new 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's would be equally helpful in your real projects or to crack your SQL Server Interview.