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 become 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 2023
Let us begin with the top 35 SQL server interview questions (specially curated to help you crack your next interview)
You can also go through SQL interview questions and answers here.
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)
FREE Course: Introduction to Data Analytics
Learn Data Analytics Concepts, Tools & SkillsStart Learning
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]
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:
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
Full Stack Web Developer Course
To become an expert in MEAN StackView Course
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.
Did you know MS SQL plays a vital role in backend web development? Learn more about it here.
Advance your career as a MEAN stack developer with theFull Stack Web Developer - MEAN Stack Master's Program. Enroll now!
What is the difference between DDL and DML commands in SQL? Why do we use Escape characters in SQL queries? What is the difference between Primary key and Unique key in SQL? What is the difference between INNER join and OUTER join in SQL?
The core component of Microsoft SQL Server is the SQL Server Database Engine, which controls data storage, processing and security. It includes a relational engine that processes commands and queries and a storage engine that manages database files, tables, pages, indexes, data buffers and transactions.
So try to memorise the following consecutive statements: SELECT→FROM→WHERE. Next, remember that the SELECT statement refers to the column names, the FROM keyword refers to the table/database used, and the WHERE clause refers to specific conditions that are investigated by the user.
Unfortunately, these interviews can be intimidating, tricky, and tough — especially if you're just learning SQL for the first time. From my experience doing hundreds of SQL interviews, this article covers the 5 most common mistakes that I've seen.
1. HackerRank. From software engineering to data analytics, HackerRank is one of the best platforms for practicing coding interview questions. HackerRank's SQL practice suite has hundreds of questions available for you to practice.
Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).
The SELECT TOP statement in SQL shows the limited number of records or rows from the database table. The TOP clause in the statement specifies how many rows are returned. It shows the top N number of rows from the tables in the output.
While Microsoft Access is made up of seven components, this text will focus on the main objects: tables, forms, queries and reports. Together, these objects allow users to enter, store, analyze and compile data in various ways.
Not achieving my full potential is one of the biggest fears in my life. I know I am a creative person with ideas, aspirations, and skills, but I still fear that I will fail to utilize my skills to achieve my professional goals.
According to Delete Duplicate Rows in SQL, you can also use the SQL RANK feature to get rid of the duplicate rows. Regardless of duplicate rows, the SQL RANK function returns a unique row ID for each row. You need to use aggregate functions like Max, Min, and AVG to perform calculations on data.
There are 3 main types of commands. DDL (Data Definition Language) commands, DML (Data Manipulation Language) commands, and DCL (Data Control Language) commands.
A primary key is the column or columns that contain values that uniquely identify each row in a table. A database table must have a primary key for Optim to insert, update, restore, or delete data from a database table. Optim uses primary keys that are defined to the database.
The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint.
You can retrieve or exclude rows that contain a null value in a specific row. You can use a WHERE clause to retrieve rows that contain a null value in a specific column. You can also use a predicate to exclude null values. You cannot use the equal sign to retrieve rows that contain a null value.
Unique Key is a column or set of columns that uniquely identify each record in a table. All values will have to be unique in this Key. A unique Key differs from a primary key because it can have only one null value, whereas a primary Key cannot have any null values.
The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN returns all rows from multiple tables where the join condition is met.
SELECT is probably the most commonly-used SQL statement. You'll use it pretty much every time you query data with SQL. It allows you to define what data you want your query to return. For example, in the code below, we're selecting a column called name from a table called customers .
The select command in SQL is the simplest, yet one of the most important SQL queries within the suite of SQL commands. It's considered a best practice to write your reserved SQL syntax in uppercase, as it makes the select command easy to read and understand.
Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.
SQL Server supports various data types for storing different kinds of data. These data types store characters, numeric, decimal, string, binary, CLR and Spatial data types. Once you connect to a database in SSMS, you can view these data types by navigating to Programmability-> Types->System Data Types.
These are mostly used for very large datasets, such as those on large websites where relational databases would not be ideal. Some examples of these databases are Cassandra, Hadoop and MongoDB. But by and large, SQL is still a must-have skill for any serious DBA.
To view the error log using SQL Server Management Studio, Open SSMS Connect to SQL Server instance Expand SQL Server Agent node Expand Error Logs Click on Current. In the Error log file viewer, expand SQL Server and select on Current Log. In the list of the events, you can view the SQL server instance uptime.
SQL is a query language, while SQL Server is a database management system. SQL is a query language for working with a relational database, while SQL Server is proprietary software that performs SQL queries.
“I feel there can be a real sense of achievement working in SQL, from solving broken queries to designing new ones! There is so much to learn working with SQL, it's an exciting and growing coding language and I want to expand my query design abilities through this role.”
How Long Does it Take to Learn SQL? Because SQL is a relatively simple language, learners can expect to become familiar with the basics within two to three weeks.
SQL developers, like many roles in the tech industry, have the potential to earn a good living. In the United States., SQL developers can typically make a median salary of $98,860, according to the Bureau of Labor Statistics [1].
1 Answer. ORDER BY id ASC; In the above query, we used subquery with the TOP clause that returns the table with the last 5 records sorted by ID in descending order. Again, we used to order by clause to sort the result-set of the subquery in ascending order by the ID column.
10) How would you rate yourself on a scale of 1 to 10? I will rate myself 8 out of 10 because I would never like to think that there should be a room left for putting in more efforts. That thought will create an interest in learning the things. Thank you very much for giving me this wonderful opportunity.
Everyone's different, but learning basic SQL statements can take anywhere from a couple of hours to a couple of weeks. It can take months to master them, but once you understand the concepts behind statements like INSERT, UPDATE, and DELETE, you'll be very well placed to use those statements in the real world.
It should take an average learner about two to three weeks to master the basic concepts of SQL and start working with SQL databases. But in order to start using them effectively in real-world scenarios, you'll need to become quite fluent; and that takes time.
Anyone can learn SQL. It's not as hard as you think! In today's world, even those in non-technical jobs need some technical skills. And you don't have to be a hard-core nerd to get these skills.
So try to memorise the following consecutive statements: SELECT→FROM→WHERE. Next, remember that the SELECT statement refers to the column names, the FROM keyword refers to the table/database used, and the WHERE clause refers to specific conditions that are investigated by the user.
Introduction: My name is Arline Emard IV, I am a cheerful, gorgeous, colorful, joyous, excited, super, inquisitive person who loves writing and wants to share my knowledge and understanding with you.
We notice you're using an ad blocker
Without advertising income, we can't keep making this site awesome for you.
