Introduction
If you are applying for a SQL Server Administration or Development job, you may need to study and practice for the interview. It is a common mistake to go for an interview without studying.
In this article, we will show some common SQL interview questions that may help you to secure a good job.
Getting started
What are the new features in SQL Server version X?
A common question is asked about the new features of SQL Server. Here you have a list of SQL Server versions:
At the time this article was published, the last version was SQL Server 2019. Here you have the new features:
For older versions, you can check these links:
What is a stored procedure?
Stored procedures are used all the time in SQL Server, they are more commonly used than the functions. A stored procedure is a piece of code that you can use and reuse multiple times. It is a best practice to use stored procedures instead of queries for security reasons. Also, it is more efficient to send just the stored procedure with a few parameters than the entire select statement from the client to the server.
In order to create a stored procedure, you need to use T-SQL. Here you have the tutorial with examples to create your own stored procedures. One of my fellow authors, Ranga has created a pretty nice article about this topic:
There are several system stored procedures. Here you have the list:
The system stored procedures help you to administrate your database. You do not need to memorize all of them. They are a lot. You may need to check the most common stored procedures like the sp_who, to check current users, sessions. Also, the kill command to kill sessions. The sp_monitor is also a nice stored procedure to check statistics. Another one that I use a lot is the sp_server_info to get Server information.
What is T-SQL?
If it is a T-SQL Developer job, you will need to study T-SQL. It is the query language used by SQL Server. The following article includes a great introduction article about T-SQL that will help you to a great extent in the SQL interview:
Learning T-SQL takes time. To start, we recommend to start here:
How do you tune a query?
Once that you are familiar with T-SQL, you will need to learn about Indexes, query plans and performance in general. I recommend checking the ApexSQL tools for performance. There are pretty nice third-party tools that may be useful for tuning queries. The set of sql inverview questions will include questions related to performance.
Basically, you will tune the query by checking the query plan, then, you can check if it requires an index, a hint or maybe change the way that the query is made.
What is an index and what type of indexes do you know?
The cluster and non-cluster indexes are the most commonly used indexes, however, there are spatial, full-text indexes, XML and filtered. The following article provides excellent questions and answers about indexes:
What is the SQL Server Agent?
The agent allows us to automate SQL Server Tasks and schedule them. The following article provides excellent information to learn how to use it:
What is Replication?
In the group of sql inverview questions, you may have some questions about replication. In SQL Server, you can replicate your data using different methods like the snapshot, merge, transactional and other types.
You will have questions about the replication types, the definition of each type
One of my fellow authors in SQLShack, Ahmad has created an excellent article about this topic and common questions related:
What is Always on Availability Groups?
This feature is common during a session of SQL interview questions. It is a disaster feature to make sure that your server is available. It is a more efficient alternative than the database mirroring (which will be removed soon).
We created a nice bunch of questions for you here:
What are the common questions about backups?
Backups and restoration are crucial in a disaster recovery plan it is 90 % of the time included in the set of sql inverview questions. It is a cheap solution, but if the database is big, it requires a strategy to recover the information fast. The types of backups are common questions, restoring pages are FAQ.
Here are the nice articles related to backups:
- SQL interview questions on database backups, restores and recovery – Part I
- SQL interview questions on database backups, restores and recovery – Part II
- SQL interview questions on database backups, restores and recovery – Part III
What are the common questions about SSIS?
SSIS is a different world. You can be an SSIS developer. This is a visual tool that is used to extract and export data. It is commonly used for ETLs, but you can use it to export and import data any time or automate several tasks combined with the SQL Agent to schedule the time to execute the task.
In SQL Shack, we also created an article about the SSIS interview questions:
What are the common questions related to SSAS?
The Business Intelligence world (BI) requires SSIS skills and SSAS. Sometimes you will require reporting skills. SSAS includes a Multidimensional database that is very different than the relational database used in SQL Server. It is an entirely new world of Multidimensions that requires a lot of knowledge. We also created a nice article about the common questions in SSAS:
Conclusion
In this article, we learned about the most common sql interview questions. Depending on the area, the questions may be different. Some will focus on SQL Administration, others on development. I hope you enjoy the article and write to us if you think more areas should be added.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023