Data is getting bigger day by day, and it has a significant role in decision-making. To handle this data, there is a need for a database and database management system. One of the most popular database management systems is MS SQL Server. Knowing MS SQL opens the door to becoming an SQL Specialist and an SQL Developer. To ace the SQL server interview, one needs to be prepared to face SQL server interview questions. In this article, we look at the most commonly asked MS SQL server interview questions. Let us dive in.
Top SQL Server Interview Questions for 2024
Let us begin with the top 35 SQL server interview questions (specially curated to help you crack your next interview)
1. What is the Windows Authentication Mode in SQL Server?
This mode connects the server via a Windows account. The server uses the username and password for authentication. In this mode, SQL server authentication is disabled.
2. Give an example of a function in an SQL server that returns the first non-null expression from more than one column in arguments.
Select COALESCE(sid, sname, marks) from the student;
3. Explain the one-to-many relationship in the SQL Server database.
When a single column value in one table has a minimum of one dependent column value in some other table, a one-to-many relationship exists.
4. What is the significance of CHECK in SQL Server?
CHECK constraint limits the values that can be placed inside a table’s column. This maintains integrity. The constraint is used column-wise to give specific values to that column. Example: CONSTRAINT CHK_Student CHECK (age<20)
5.How to find the 3rd highest marks from the Student table?
SELECT TOP 3 marks FROM (SELECT DISTINCT TOP 3 marks FROM student ORDER BY marks DESC) a ORDER BY marks
6. What is a trigger?
When a table event occurs, such as INSERT, DELETE, or UPDATE, triggers allow executing an SQL code batch. Triggers are managed by DBMS and can also execute stored procedures. [2] For example, when a record is inserted in a database table, a trigger can be set.
7. When can records be deleted from a view in SQL Server?
Records can be deleted in a ‘simple’ view as it contains data from one table only.
8. List down some of the features of MS SQL Server.
- It provides an easy and straightforward Syntax.
- MS SQL uses transact SQL.
- Query optimization is not supported.
- The transaction process does not allow rollbacks
- Clustering is not supported
- Statements are executed serially.
9. Which command can be used to get the version of SQL Server?[3]
To get the version of SQL Server, use:
Select SERVERPROPERTY('productversion')
10. In SQL Server, what is a user defined function?
A user defined function allows users to write their logic as per need. The advantage is that it is not limited to pre-defined functions and writing functions, simply complex SQL code. The return type is a table or a scalar value.
Example: Create function sample(@sid int)
returns table
as
return select * from s where Id = @sid
11. Explain types of replication in SQL Server.
There are three types of replication as follows:
- Transactional replication- It is a process of data distribution from publisher to subscriber. Transactional replication can be used when data is changed frequently.
- Merge replication- It groups the data to a single centralized database from various sources. Merge replication is used in cases where central and branch databases need to update information simultaneously.
- Snapshot replication- This replication is the best way to replicate data that changes infrequently, and it is easiest to maintain. Example: Snapshot replication can be used for lists that are updated once per day and needs to be distributed from main server to branch servers.
12. Define referential integrity.
Every foreign key value must have a corresponding primary key value. The maintenance of this consistency between foreign and primary keys is known as referential integrity.
13. What are TCL Commands? and List down the TCL Commands available on SQL Server?
TCL or Transactional Control Language commands are used to manage different transactions taking place in a database. The three TCL commands are as follows:
- Rollback- This is used to restore the database to the last committed state
- Save Tran- This saves the transaction, and the transaction can be rolled back to this point.
- Commit- Saves the transaction permanently in the database
14. Write a SQL Server Query to get the letter ‘e’ in the name ‘Jenna’ from the student table.
Select CHARINDEX('e',NAME,0) from student where name='Jenna'
15. As a SQL developer, how will you ensure that SQL server-based applications and databases perform well?
The volume of data, type of information stored, and data to be accessed must be checked. When a system is being upgraded, the present data should be analyzed, and the methods of accessing data should be checked to help understand problem design. Keeping the information about data is necessary when using a new system.
16. When should Server-based cursors be used?
When you require to work on one record at any instance of time, instead of taking all the data from the table as bulk. Cursors’ performance is affected when large volumes of data are present.
17. Tell us about the working of the FLOOR function.
FLOOR function rounds the given non-integer value to the previous least integer—for example, FLOOR(5.6) returns 5
18. What do you know about scheduled tasks in SQL Server?
Scheduled jobs or tasks automate processes that can be run at a prescribed time at a regular interval. By scheduling tasks, human intervention is reduced, and tasks can be carried out at any time in the order that the user wants.
19. Mention a query that returns the list of triggers in a database.
Select * from sys.objects where type='tr'
20. Differentiate between rollback and commit.
When COMMIT is executed, all statements between BEGIN and COMMIT become persistent to the database. Whereas, when ROLLBACK is executed, all statements between ROLLBACK and BEGIN are reverted to the state.
21. Explain how to create a table in SQL.
The following query is used to create a SQL table:
Create table name_of_table( column1 datatype, column2 datatype )
For example:
create table Student
(
Name varchar(20),
DOB date,
Marks nvarchar(5),
Subject varchar(20) )
22. What is the function of a foreign key in a database?
A foreign key is used to define a relationship between the parent and child table connected by columns. The foreign key is a constraint that ensures that the values of the child table appear in the parent table. The foreign key of one table is the primary key of the other, and a table can have several foreign keys. For example:
student {ID, Name, Age, Contact, Gender, Add}
teacher{Teach_ID, Name, ID}
Here, ID is the foreign key for the teacher table.
23. What is the importance of views in a database?
There are scenarios where we need to look for a view to getting the solution, such as:
- Aggregating data for performance
- Customizing the schema and data for a set of users
- Controlling access to columns and rows of data
24. Tell us the steps to hide SQL Server Instances.
To hide the SQL Server Instances, we need to make changes in SQL Server Configuration Manager, and to launch it, the following steps are needed:
- Select instance of SQL server
- Select properties after right-clicking
- Set Hide Instances to Yes and click on APPLY
- Post changes, restart the instance of SQL Server
25. Explain the DBCC command and its use.
Database Consistency Checker (DBCC) checks the consistency of the database; It helps in reviewing and monitoring the maintenance of database, tables, and operation validation. For example:
- DBCC CHECKALLOC checks all pages in the database to ensure they are correctly allocated.
- DBCC CHECKDB makes sure that indexes are correctly linked in the tables of the database.
- DBCC CHECKFILEGROUP checks all file groups for damage.
26. Describe the SIGN function.
The SIGN function is used to specify a number as positive, zero, or negative. It returns the following: SIGN (number)
Returns – 1 if number <0, +1 if number>0 and 0 if number=0
27.Define alternate key.
When a table has more than one candidate key (i.e., candidate for primary keys), one becomes the primary key, and the rest are the alternate keys.
28. Define Join. What are the different types of joins?
Joins are used in SQL queries to describe how different tables are related. They also allow users to select data from one table depending on the data of the other table. The different types of joins are:
- INNER Joins
- OUTER Joins- LEFT OUTER, RIGHT OUTER, FULL OUTER
- CROSS Joins
29. Tell about the use of UPDATE STATISTICS.
UPDATE STATISTICS is used to update information about the distribution of the key values for one or more statistic groups/collections in the indexed view or specified table.
30. Define Full backup.
The most common type of backup in SQL server is the complete backup of the database. It also includes part of the transaction logs for recovery.
31. In SQL, what is meant by the identity column?
In SQL, an identity column generates numeric values automatically. These columns need not be indexed, and we can define the start and increment value of the identity column.
32. Explain the UNIQUE KEY constraint.
The UNIQUE constraint maintains the uniqueness of records in the set of columns to ensure there are no duplicate values. This constraint enforces entity integrity.
33. Define the process of de-normalization.
The process of de-normalization adds redundant data to a database in order to enhance the performance. This technique moved from higher to lower normal forms of the database. This speeds up the database access.
34. Show how table type constraint can be applied to a table.
Alter Table Name_of_the_Constraint
Alter Table Constraint_1
35. Differentiate between derived persistent attribute and derived attribute.
A derived attribute is obtained from values of other existing columns as its values do not exist on their own. A derived attribute that can be stored is a derived persistent attribute.
Conclusion
These SQL server interview questions must have given you an insight into various essential SQL topics. Do not forget to practice them before going for your next SQL server interview. If you are looking to build a strong base in SQL, do not forget to check out the SQL certification training course by Simplilearn.
If you are looking to enhance your software development skills, we would recommend you to check Simplilearn's Post Graduate Program in Full Stack Web Development. This course can help you hone the right skills and make you job-ready in no time.
If you have any questions, feel free to post them in the comments section below. Our team will get back to you at the earliest. Start learning now!