Manvendra Singh

SQL Interview questions and answers

October 20, 2021 by

This article will walk you through some SQL interview questions and answers to help you with a job change or if you want to improve your interview skills. I have also tried to attach supporting articles for each question to help you learn more in-depth about the specified topics. I tried to answer each question in a very precise manner which most the interviewers want to listen to these days. If you want to learn more about the topic asked, you can visit the attached link given for that question.

I am not covering generic questions being asked in almost every interview because the answer to these questions is very specific and different from person to person. You must prepare these SQL interview questions as per your best knowledge if you are looking for a job change.

  • Tell me something about yourself and your experience?
  • What is the most challenging project have you delivered?
  • Tell me more about challenges and how did you fix them?
  • Why do you want to change?
  • Name anyone or two projects or activities for which you have been recognized by your client and leaders?

The questions given above are just an example of generic questions being asked in most of the interviews. You must prepare based on your experience. One simple rule will always work, to be honest, while answering each question. If you don’t know the answer to any questions, politely tell them about this.

Let’s go to some technical SQL interview questions and their answers in the below sections.

SQL Interview Questions

What are functions and their benefits in SQL Server?

Functions are database objects which can be used to reduce code complexities for repetitive statements. The main benefits of functions in SQL Server are its reusability. If we are using a repetitive set of statements in a script, then we can create a function and replace those statements with that functions. It will return the result after taking some input parameters. This way functions are very useful in reducing the script length and avoiding repetition of efforts to write the same set of statements again and again. Other benefits of functions are we can use it with SELECT statement along with WHERE or HAVING clauses. They can also be used with expressions like CASE statements. Read more about user-defined functions in SQL Server here, Learn SQL: User-Defined Functions.

Can we evaluate permissions of any SQL Server user on a specific securable using SQL Server functions?

Yes, we can get that by using one of the security-related system-defined functions HAS_Permis_BY_Name where you need to pass few input parameters like securable, securable class, and permission name about which you are evaluating for that user.

  • Securables are specific resources on which a user will need access
  • Securable_class is the name of the class under which securable is defined like Database, Objects, Login, etc.
  • Permission is the name of effective permission which is assigned to securables

Read more about this system-defined function in the attached link.

What is the difference between functions and stored procedures?

Both are two different database object which SQL Server offers to fulfill distinct requirements. Below is the main difference between functions and stored procedures in SQL Server.

  • Stored procedures can have input and output parameters whereas functions can only take input parameters to return their result
  • Exception handling using TRY and CATCH statements can be used in stored procedures whereas functions cannot use these statements
  • Stored procedures can be used with most of the T-SQL statements like INSERT, UPDATE, and DELETE whereas functions can only be used with SELECT statements
  • Stored procedures can call functions whereas functions cannot call stored procedures
  • We can execute stored procedures independently or using EXECUTE statement whereas functions cannot be executed independently, and they must be part of a T-SQL statement

Here is another article where you can learn about Functions vs stored procedures in SQL Server

What are the benefits of using stored procedures in SQL Server?

Here is the list of benefits of using stored procedures in SQL Server:

  • Stored procedures are very easy to use and maintain
  • Stored procedures group multiple SQL statements and execute them as a single batch to reduce network traffic:
  • Stored procedures are fast because they create execution plans the first time you run them and the same execution plan will be used for subsequent executions
  • Reusability is another benefit of stored procedures. The stored procedure can be created for any repetitive database operation and that procedure can be reused during subsequent executions

Read more about this topic in the attached Stored Procedures for beginners.

How can you fetch unique values from a column in SQL Server?

We can fetch unique values from any column using the SELECT DISTINCT statement. Below is a simple example that will return all the unique values from column city.

How many primary keys and foreign keys we can create on a table?

We can create only 1 primary key and a maximum of 253 foreign keys on a table in SQL Server.

What are the options available to track data changes in SQL Server?

SQL Server offers two features “Change Data Capture” and “Change Tracking” to track data changes of databases. Application developers don’t need to create a custom solution to track changes for their databases. They can save a lot of effort and time by adopting these solutions and focusing on other areas which are more critical for your development. Both features can provide end-to-end solutions. Even there is an in-built clean-up mechanism that performs automatically in the background.

What is the difference between Change Data Capture and Change Tracking?

Change Data Capture (CDC) captures both the data before and after changes were made whereas Change Tracking only keeps changed data. It does not keep any information about older data that was changed with the new one. Change Data Capture uses an asynchronous mechanism to track changes it means changes are available to post DML operations whereas Change Tracking uses a synchronous mechanism to capture changes and because of this all-change information is available immediately.

Describe full-text search in SQL Server?

Full-Text Search is a SQL Server feature that allows us to run full-text queries against character-based data stored in tables. There are 3 basic steps we need to follow to set up a full-text search in SQL Server.

  1. Install full-text search component, as it is not installed by default. You need to run SQL Server setup to install this component if had not been installed previously
  2. Create a full-text catalog
  3. Create a full-text index in tables

Once you follow the above steps in sequence, you are ready to go ahead and run full-text queries on texts stored in the columns on which you have created a full-text index.

How is a full-text search different from the LIKE predicate?

Full-text search and LIKE predicate are designed to fulfill different requirements. Full-text search is more efficient and powerful while running complex searching operations with the help of their in-built predicates CONTAINS and FREETEXT and the rowset-valued functions CONTAINSTABLE and FREETEXTTABLE with a SELECT statement whereas LIKE predicates work efficiently for character-based search patterns using liner scan process and find all matching terms. LIKE predicate works inefficiently when you have to search a string or character in very large unstructured text data whereas full-text search will return its output very quickly on such data sets.

You need to install a full-text search component to use this feature whereas there is no such requirement to use LIKE predicate.

What is the difference between full-text index and normal SQL Server indexes?

Full-text indexes are used by full-text queries. We can create only one full-text index per table whereas you can create multiple normal indexes per table. Full-text indexes must belong to their full-text catalogs whereas there are no such requirements for regular indexes.

Explain partitioning in SQL Server and what are its benefits?

As its name suggests, partitioning divides a table or index into multiple subparts to increase the data manageability and query performance by accessing less amount of data belongs to the respective partition. There are many benefits of using the partition, some of them are given below.

  • It improves query performance by accessing only a small subset of data. You don’t need to look into a whole set of data to get your result
  • Faster and quicker and more control on Data maintenance activities. We can run indexing operations on one or more partitions if required and not the whole set of table data
  • Better Data manageability. We can place a partition of tables or indexes on one or more filegroups and then even we can assign a specific filegroup to a specific storage tier

You can read the attached article to understand more about Database table partitioning in SQL Server.

What is CASE expression in T-SQL language?

CASE expression is used when you have to evaluate various conditions and want to return one out of numerous probable expressions. It evaluates conditions sequentially and stops when the first condition is satisfied. We can use CASE expression in two formats:

  • Simple CASE expression
  • Searched CASE expression

Read more about its basics and various use cases of this here, CASE statement in SQL.

What is the difference between CHAR and VARCHAR data types in SQL Server?

CHAR datatype is used if you want to store data of fixed length whereas VARCHAR datatype is used to store data string of variable length. CHAR data type has better performance as compared to VARCHAR data type. CHAR data type uses 1 byte for each character whereas VARCHAR uses 1 byte for each charter and additional bytes for other information like length info, etc.

Conclusion

I have listed some SQL interview questions and answers in this article. You can go ahead and read them to crack SQL Server jobs if you are looking for new opportunities. Please let us know your feedback in the comment section.

Manvendra Singh
Latest posts by Manvendra Singh (see all)

Related posts:

  1. Top 50 collection of articles on SQL interview questions
  2. Top SQL Server Books
  3. SQL interview questions
  4. Hands on Full-Text Search in SQL Server
  5. Database Backup and Restore process in SQL Server – series intro
168 Views