In this article you will get list all MS SQL server interview questions for beginners at one place. It is designed for beginners and experts too. It is a set of 40+ MS SQL questions which will give you good understanding of SQL concepts.

Let’s check out SQL Server Interview Questions below:

What is SQL?

SQL is abbreviation of ‘Structured query language’ and it is used to write queries which communicate with the relational databases. Below is the example of SQL query in which ‘Student‘ is the name of SQL table in which we’ll perform read /write operations.

Select * from Student
or 
Select [ID], [Name], [Roll-No], [Class] from Student 

What is MS SQL server?

MS SQL server is a “backend system” that supports apps and websites and hold all the data which is displayed on websites. It is more fast, scalable and robust than Microsoft Access.

First MS SQL server version was launched in 1989 and after this version multiple versions came and currently, latest one we have is ‘MS SQL Server 2022’.

Explain the use of default Databases in SQL server?

In SQL by default few databases are created. Let’s checkout below what is the main purpose of these databases.  

• Master –This DB contains system level information such as ‘user accounts’, ‘configuration’ and information of other databases available on current SQL server.
• TempDB – It is used to holds all temporary tables and other temporary storage requirements generated by SQL Server.
• MSDB – It is used for configuring alerts, scheduled jobs and SQL Server agent configuration etc.
• Resources – It contains all system objects and schemas in it. This DB is read-only and does NOT contain and user specific information in it.
• Model – It is a template database and used as a base DB for others. i.e., when you create a new Database at that moment model DB’s structure will be by default copied into new DB.

Explain the difference between TRUNCATE and DELETE in SQL?

What is the use of FLOOR() and CEILING() Function in SQL?

Both the functions are used to return the integer value form decimal value. But they are different, let’s check out how these work

FLOOR() Function:

It is used to return the integer value which is equal or less then decimal value. If we’ll pass string then compile time error will arrear.

declare @val nvarchar(10)
set @val='7.1'

Select  FLOOR(@val) as Num      -- it will result as 7  
Select  FLOOR('7.5') as Num      -- it will result as 7   


--Output will be 7 in both above cases

Select  FLOOR('-7.5') as Num     -- it will result as 8

What if we will pass negative decimal value, it will pick higher value like for example “-7.5” will result as 8.

CEILING() Function:

This function is used to convert decimal value to integer value which is higher than the decimal value.

declare @val nvarchar(100)
set @val='7.1'

Select  CEILING(@val) as Num
Select  CEILING('7.5') as Num

--Output will be 8 in both above cases

In case, if negative value will be passed then CEILING (‘-7.5’) then it will return integer value as 7 i.e. smaller integer value than the decimal value.

How to find second highest salary?

Let’s create a table and then create the query on this

CREATE TABLE [dbo].[Employee](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EmpName] [nvarchar](50) NULL,
	[Salary] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
--Insert the record using below queries

GO
SET IDENTITY_INSERT [dbo].[Employee] ON 
GO
INSERT [dbo].[Employee] ([ID], [EmpName], [Salary]) VALUES (1, N'Mukesh', CAST(10000 AS Numeric(18, 0)))
GO
INSERT [dbo].[Employee] ([ID], [EmpName], [Salary]) VALUES (2, N'Rakesh', CAST(20000 AS Numeric(18, 0)))
GO
INSERT [dbo].[Employee] ([ID], [EmpName], [Salary]) VALUES (3, N'Smith', CAST(25000 AS Numeric(18, 0)))
GO
INSERT [dbo].[Employee] ([ID], [EmpName], [Salary]) VALUES (4, N'John', CAST(15000 AS Numeric(18, 0)))
GO
INSERT [dbo].[Employee] ([ID], [EmpName], [Salary]) VALUES (5, N'Peter', CAST(17000 AS Numeric(18, 0)))
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO

 Simple way to get highest salary

Select *from employee where salary=(select Max(salary) from employee);

Simple way to get Second highest salary

select top 1 *from employee where salary<(select Max(salary) from employee) order by salary desc
select top 1 *from employee where salary not in(select Max(salary) from employee) order by salary desc

In next query we will change the highlighted number and can find any number of salary either second, third, fourth or anymore. 

select top 1 * from
(select top 1 * from employee where salary not in(select Max(salary) from employee)
 order by salary desc) as a order by salary asc

What is the use of COALESCE() Function?

The COALESCE () function is used to return the first non-NULL value. Instead of adding multiple if conditions it is best to use and it return first value which is non null. It can accept any number of parameters in it.

Syntax of COALESCE:

COALESCE (val1, val2, val3 ….…., val_n)

Form below query it will return “codeconfig.in”. As said above query will ignore all null values and return first non-null value which is “Codeconfig.in”

SELECT COALESCE (NULL, NULL, NULL, 'codeconfig.in', NULL);

What will happen if you will pass all null values in “COALESCE”. It will give error as given below

What are indexes? Explain its types.

Indexes are usually created on SQL Tables to boost the retrieval of data form the table. It is created on parameter used in the ‘WHERE’ clause. When any index is created on table a logically reordering of that data is done and stored and when required this logically stored data is shared and this finally speed up the data retrieval. Basically, Indexes are of two types:

Clustered Index: It is always created on primary key, and it reorders the data as it is physically stored.

Non clustered index: We can have multiple non clustered indexes. It is created using WHERE clause parameters and it will do logical reordering of the data as per the index and same logically stored data is returned when SQL query is execution for the same data. Logically stored data is different than the physical stored order of the rows in table.

Note: Clustered indexes makes the fast retrieval of data from DB. i.e., select query run faster whereas insert query become slow with the same table. Now question arises why the insert is performed slowly, answer is- each insert, table have to re-indexes itself again, this is performed after each insert into table.

Important points of Indexes:

• We should create Indexes on keys those we use in WHERE clause and in our join statements.
• Multiple non-cluster index should be avoided because it can reduce the performance rather than boosting. Specially your insert/update/ delete operation will slow down because of the logically reordering of the data.
• If required, multiple non-cluster Indexes should NOT be made on columns that are frequently modified i.e., columns in which INSERT/UPDATE is occurring frequently.
• Non-cluster Indexes should be made on ordering key values (like ASC or DESC order). This will enhance your query performance.
•  Indexes should be made on WHERE clause and other columns which we use using AND operator within the query to filter data.

What is Sub Query?

Sub query is the query within another query. Usually in WHERE clause we can write another query which is known as sub-query.

• A subquery is always enclosed with in parenthesis.
• A query can contain more than one sub-query.
• ORDER BY is not supportive in subquery.
• Outer query output depends upon sub query results. If sub query did not return anything, then parent query will also not return anything.
• Sub query can return one row or multiple rows as per its structure.

Types of subqueries:

• Single row subquery – it returns single row as output.
• Multiple row subquery- it can return multiple rows and multiple columns too.
• Correlated row subquery- If sub query refers any column from outer query, then it is known as correlated query.
• Nested Subquery- Id subquery again nest one sub query in its where clause then it is known as nested sub queries. But be noted that multiple level of nesting slower the performance.

What are constraints? Explain different types of constraints?

Constraints are applied on SQL tables, and these are set of rules which ensure the data integrity and because of this we get the unique data in our table. Constraint can be applied on one column of on a group of columns too. There are five types of constraint which are as:

• Primary Key
• Unique key
• Foreign Key
• Check Data Validation
• Null / Not Null

Define Primary Key.

• Only one ‘Primary key’ can be applied on one table.
• The primary key column is used to uniquely identify each row of the table.
• Primary key does not accept NULL value.

Define Unique Key.

• It can be applied on one column or multiple columns of table.
• Unique key column can accept a null value.

Define Foreign Key.

• Foreign key is the primary key of another table.
• It is used to link two or multiple tables.
• Table which contains foreign key is known a child table and other table whose PK is referred is known as parent table.

Define Check for data validation.

• CHECK syntax is applied on the Column of table.
• CHECK is used for data Validation whether it is in correct format or not.
• CHECK can be applied on column for accepting ‘Male’ and ‘Female’ or for valid email format etc.

Define NULL/ not NULL constraint.

• Using this we can restrict column for accepting Null or not NULL values.

Define Joins?

A Join is used to combines columns and data from two or more tables. When we to fetch data from multiple tables then joins comes into picture. There are multiple types of joins as:

Self Join:

• Self-join means joining one table with itself again to get the required data.
• In self-join two copies of the same table are used.

Cross Join:

A cross join perform Cartesian product of the tables involved in the join. Cartesian product result set is the cross product of number of rows in the first table and second table.

Outer join:

Outer joins return all rows from at least one of the tables or views, as long as those rows meet any WHERE or HAVING search conditions.

Left join:

Left join will return all rows from the left most table. Matching rows will have data and other may contain null.

Right join:

Right join will return all rows from the right most table. Matching rows will have data and other may contain null.

What the difference between UNION and UNION ALL?

• Union will remove the duplicate rows from the result set.
• Union all doesn’t remove duplicate rows.

What is the difference between SET and SELECT?

Both SET and SELECT can be used to assign values to SQL variables. One must use ‘SET’ for assigning value in variable rather than ‘SELECT’. Example of SET is given below:

Examples:

declare @i int set @i=1 'This is used to assign constant value.

Select @i=max(user_id) from Users

What is the difference between char, varchar and nvarchar?

These are different ‘DataTypes’ which we assign to column.

1.Char DataType: Char datatype which is used to store given length of characters in specific column. In below example ‘Name’ column have 50 lengths. We store string ‘Rakesh’ into which is of 6 length, next 44 ‘chars’ memory will be wasted. Char will reserve the 50 ‘chars’ memory at the beginning and after storage remaining space will remain as wasted.

CREATE TABLE User(Id int ,Name VARCHAR(50), Gender CHAR(10));
INSERT into Student VALUES(1,'Rakesh', 'Male');

2.varchar DataType: Varchar is used to store non-unicode characters. It will allocate the memory based upon the number of characters inserted in the column. Unlike ‘CHAR’ it does not reserve memory in the beginning but occupy as per the data length only. For example – as in above example 44 char memory was wasted but this will not be in case of varchar. It will use only 6 char memory and rest will be free.

3.nvarchar DataType: nvarchar and varchar datatype are similar in storing the data and only difference is ‘nvarchar’ used to store Unicode characters. ‘nvarchar’ must be used when we have to insert special characters and mostly it is used to store multiple languages in database. ‘nvarchar’ datatype will take twice space to store characters as compared to varchar so it can hold 4000 chars whereas varchar can hold 8000 chars. Hence, if we are not using other languages then it’s better to use varchar datatype instead of nvarchar.

What is the difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT?

1.@@IDENTITY: Identity returns the last identity value generated for any table in the current session. across all scopes.

2. SCOPE_IDENTITY: Scope_Identity returns the last identity value generated for any table in the current session and the current scope.

3. IDENT_CURRENT: IDENT_CURREN returns the last identity value generated for a specific table in any session and any scope.

What are SQL keywords used to set and remove permissions?

GRANT and REVOKE

Define candidate key, alternate key, composite key?

Candidate key: A candidate key is one that can identify each row of a table uniquely. Generally, a candidate key becomes the primary key of the table.

Alternate Key: If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

Composite Keys: A key formed by combining at least two or more columns is called composite key.

What is the use of DBCC commands?

DBCC stands for database consistency commands. DBCC command are used to check the consistency of the databases, i.e., reseed identity and shrink DB logs can be performed using DBCC commands.

Following is the example of DBCC command: It will accept 3 parameters. First parameter is ‘Table Name’, second is Fixed keyword and will remain same and third parameter is the numeric value what you want to reseed your table identity.

DBCC CHECKIDENT ('User', RESEED, 0); 
' It will Re-seed user table with identity ZERO.

How is data physically stored in a database, explain with example.

We all know that SQL server stores data in tables, if we think next step where table’s data internally get stored then SQL pages come into picture. Check more details HERE

Yes, SQL data internally get stored in the form of pages and each page is of 8kb. So, when you insert any data into a SQL Server database, it saves the data to a “series of 8 KB pages” inside the data file. When data of any column increases then next data is saved on next new page and these pages are internally referenced to each other.

Below is the example of page which contains three main sections as:

• Page header
• Data Row
• Row Offset  

Can you give an example of SQL transaction in query?

Below is the sample example of SQL transaction.

Declare @RETURNS int;
BEGIN TRAN
               /* Add all insert/update queries here. */
               UPDATE Employee SET Salary=50000 WHERE EMPID=101     
IF (@@ERROR>0)
   BEGIN
       @RETURNS=-1        /* Fail to insert/ Update, please Rollback */
       ROLLBACK
   END
ELSE
    BEGIN 
      @RETURNS=0         /* insert/ Update successful & can commit now*/
      COMMIT
    END

What is Fill Factor in SQL Server?

Fill factor is used to decide how the data will get stored on SQL 8KB pages. We all know that in SQL our data is stored in the form of pages and each page is of 8192 bytes. 8060 bytes are used for data rest of bytes are used by Header and Footer. Now, fill factor decide how much bytes of this page will be used for storing data.

Fill factor is defined in percentage like if we will set it to 50% then only 50% of page bytes (50% of 8060 bytes) will get used for data storage and rest empty space will stay as it is for future data addition/ updates on the same page. You can learn more on Fill factor HERE.

What are different ways of SQL query optimization?

There are multiple ways using which we can optimize or SQL queries. For more details you can check out our detailed article HERE.

What is the difference between SQL IF and IIF?

IIF is new function which was added in SQL 2012. For more details you can check out our detailed article HERE.

Can we nest SQL Case statement, explain with Example ?

Yes, we can nest SQL case statement, along with this there are multiple ways to use this. Please check out our detailed article to check all HERE.

How to Search SQL Object in Complete Database?

In our day-to-day routine work some time we need to search object in SQL DB without any external software or add on etc. We have explained all possible ways using which you can search the required object in your DB. Please check details HERE.

What is the use of QUOTENAME Function in SQL?

This Function was first introduced in SQL Server 2012. Main use of this function is to add square brackets to the starting and ending of a string. For more information on QUOTENAME you can check out our detail article HERE.

How to Generate Complete Database Script?

To generate full database script using SSMS, go to Object Explorer, right click on a database for which want to generate scripts; from the context menu under the Task sub-menu, choose the “Generate Script” option. For detailed information please check out our article HERE.

How to Import one SQL Database into another?

To start the “SQL Server Import and Export Wizard” using SSMS, go to Object Explorer, right click on a database for which want to export or import data; from the context menu under the Task sub-menu, choose the Import Data or Export Data option. For detailed information with screen shot please check out our article HERE.

I hope above list of SQL Server questions has done valuable add on to your knowledge bank. For more information you can visit HERE.

For knowledge of ADO.NET interview questions, you can check this section HERE