Let’s look at some of the frequently asked questions in an SQL Server-based interview. We will also include examples of SQL queries along with the SQL interview questions and answers wherever necessary. 

Check out our free courses to get an edge over the competition 

SQL Server Interview Questions & Answers

Question 1: What is SQL?

SQL or Structured Query Language is the standard computer programming language used to manage and organize Relational Database Management Systems (RDBMS). It is used for storing, manipulating and gaining access to stored data, in other words, communicating with relational databases. Most popular RDBMSs make use of SQL and this includes SQL Server, MySQL  and Oracle. 

Any unit of execution or statement in SQL is known as a query. An SQL query can be used to create, select or modify data.

In Relational Database Management Systems, an organised file of data is stored in the form of tables. Each table consists of columns and rows.

Check out upGrad’s Java Bootcamp

Learn Software Development Courses online from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs or Masters Programs to fast-track your career.

Question 2: What is the SQL query to create a database in SQL Server?

 A database in SQL is an organised collection of data which may consist of tables, code functions, schemas, etc. A programmer can create this database or manipulate it using various query languages. 

To create a new database in the SQL server, the following SQL command is used:

CREATE DATABASE DatabaseName

Read: Full Stack Interview Questions

Question 3: How is a table created in SQL?

A table is an object in SQL that allows users to store and retrieve data. This data is stored in a tabular and each table consists of columns and rows. 

To create a new table in the SQL server, the following SQL command is used:

Create table TableName (columnName1 datatype, columnName2 datatype )

Question 4: What Is an SQL Profiler?

SQL Profiler is a means for a system administrator to keep track of the events in the SQL server. It’s mainly used for the analysis of the captured and saved data files of an event. SQL Profiler is an essential tool empowering system administrators with a holistic understanding of events unfolding in the SQL server. Its main function revolves around monitoring and analyzing data files that capture various events. By harnessing the capabilities of SQL Profiler, administrators can acquire valuable insights into the SQL server’s performance, query executions, and resource consumption. This, in turn, aids in identifying bottlenecks, fine-tuning queries, and ensuring seamless database operations. Offering a user-friendly interface and detailed event logs, SQL Profiler becomes indispensable for effectively managing and troubleshooting SQL server environments.

Question 5: Explain with an example what a recursive stored procedure is.

Recursive stored procedures in SQL Server needn’t be called. It calls by itself until a boundary condition is reached. A maximum nesting level of 32 is possible with stored procedures. This is known as recursion. 

Using the recursive stored procedure, a programmer can use the same batch of code for n times. 

Check out upGrad’s Full Stack Development Bootcamp (JS/MERN) 

To cite an example, if you need to expand a tree relationship or compute the factorial algorithm, you can use reversion in stored procedures. Here is an example on how to calculate the factorial of a number. 

CREATE PROCEDURE [dbo].[Factorial_ap]

(

    @Number Integer,

    @RetVal Integer OUTPUT

)

AS

    DECLARE @In Integer

    DECLARE @Out Integer

    IF @Number != 1

        BEGIN

        SELECT @In = @Number – 1

        EXEC Factorial_ap @In, @Out OUTPUT

        SELECT @RetVal = @Number * @Out

    END

        ELSE

            BEGIN

                SELECT @RetVal = 1

            END

RETURN

GO

Explore Our Software Development Free Courses

Question 6: What List the differences between local and global temporary tables. 

The visibility of local temporary tables lasts as long as there is a connection. Once the connection is closed, the server automatically deletes these tables.

Local temporary tables are denoted by # before the table name.

The syntax to create a local temporary table is:

CREATE TABLE #<tablename>

( 

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  …

);

Global temporary tables are accessible and visible to all users. SQL Server deletes them once the SQL Server session ends. This happens when every user referencing table has disconnected from the session.

Local temporary tables are denoted using ## before the table name

The syntax to create a global temporary table is

CREATE TABLE ##<tablename>

( 

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  …

);

Question 7: Explain pattern matching in SQL?

Pattern Matching in SQL allows programmers to use the underscore sign (_) for the purpose of matching a single character. It also enables you to use the percentage sign (%) for matching an arbitrary number of characters. This also includes any zero characters. It should be noted that SQL patterns are case-insensitive in MySQL.

Pattern matching in SQL is a powerful feature that enables programmers to perform flexible and efficient searches in databases. By using wildcard characters like the underscore (_) and percentage sign (%), programmers can construct search patterns that match specific text patterns within data. The underscore represents a single character, while the percentage sign matches an arbitrary number of characters, including none. This versatility makes it easier to find relevant data without specifying the exact values.

Explore our Popular Software Engineering Courses

Question 8: What is a Trigger? How many types of triggers are there? 

Triggers are a special kind of stored procedure that are used to check a batch of SQL code. They are executed or “triggered” automatically when a table’s data is modified.

There are two types of Triggers. They are: 

1. Data Manipulation Language (DML) and
2. Data Definition Language (DDL)

When either of these queries are used to modify data, a set of commands are triggered. With DML command events, Insert, Delete, Update and Instead of are fired up. With DDL, the triggers Create, Alter and Drop are triggered. 

Also Read: PHP Interview Questions & Answers

Question 9: Define COALESCE?

The first non-null expression within a function’s arguments is returned using COALESCE. It can read one or more columns in its arguments to check for a non-null expression.

The syntax is –

Select COALESCE (emp_num, emp_name, salary) from employee;

In-Demand Software Development Skills

Question 10. What is CDC?

A feature of SQL Server 2008, the CDC or Change Data Capture can be used to capture the data that has been recently modified.

It provides a way to track and capture data changes in a database, allowing users to identify and access recently modified data. This feature proves particularly useful for auditing purposes, data synchronization, and data integration scenarios. By recording changes efficiently, CDC enables developers and administrators to stay informed about alterations made to the database, ensuring data accuracy and enabling timely actions based on updated information.

Question 11: What are the queries used to get the count of the number of records in a SQL table?

We can get the count of records in a table using the following queries:

Select * from <tablename> 

Select count(*) from <tablename> 

Select rows from sysindexes where id=OB

JECT_ID(tablename) and indid<2

”
upGrad’s Exclusive Software Development Webinar for you –

SAAS Business – What is So Different?

”

Question 12: State the difference between SUBSTR and CHARINDEX functions in the SQL Server?

The SUBSTR function returns the specified portion of a string as instructed by the programmer. The CHARINDEX function, on the other hand, is used to return the position of a specified  character in a given string.

For example:

SUBSTRING(‘Apple’,1,4)

– The output will be Appl

CHARINDEX(‘l’, ‘Apple’,1)

– The output will be 4 since the character l is in the 4th position in the specified string

Question 13: What is SQL injection?

SQL Injection is one of the vulnerabilities of the database where an SQL server is attacked by users using a malicious code that is inserted within the strings of a code. The intent is to send the malicious code parsing and execution. Even parameters are at risk of attack thus, all statements need to be reviewed for vulnerabilities.

SQL Injection is a serious security vulnerability in databases where malicious users insert harmful code into code strings to attack an SQL server. The intention is to manipulate the server into executing the malicious code. Even parameters in the code are at risk, so all statements should be thoroughly reviewed for potential vulnerabilities to prevent such attacks. Ensuring proper validation and parameterization in SQL queries is crucial to safeguard against SQL Injection and maintain the integrity of the database.

Question 14: What are the methods that can be used to  avoid an SQL injection attack?

The following four methods can be employed to protect an SQL server from an SQL injection attack: 

– Since Parameters are at a lower risk of an SQL injection attack, it is recommended to use them for Stored Procedures. 

– It is a good measure to filter input parameters. 

– Parameter collection with Dynamic SQL can be used. 

– Make use of escape characters in Like clauses. 

Question 15: State the two authentication modes in SQL Server. How can they be changed? 

The two authentication modes in SQL server are:

– Windows Mode

In Windows Mode, SQL Server authentication relies on Windows user accounts for access. Users can log in using their Windows credentials, simplifying the authentication process and leveraging the existing security infrastructure.

– Mixed Mode

In SQL Server’s configuration settings, under the Security Page, there is a tools menu to change the modes. Mixed Mode combines both Windows and SQL Server authentication methods. This mode allows users to connect using either their Windows credentials or SQL Server-specific usernames and passwords, offering flexibility and accommodating different authentication requirements.

Question 16: What are the different types of commands in an SQL database?

Answer: There are four types of commands in the SQL Server. They are categorized as: 

1. Data Definition Language (DDL)
2. Data Control Language Transaction Control Language (TCL)Transaction Control Language (TCL)
3. Data Manipulation Language (DML)
4. Transaction Control Language (TCL)

Q17. Explain the concept of deadlock in SQL Server

In a deadlock scenario, each transaction holds a resource that another transaction needs to complete its task, creating a circular dependency. SQL Server’s deadlock detection mechanism identifies this situation and intervenes to break the deadlock. The selected victim transaction is rolled back, and its resources are released, allowing the surviving transactions to continue. It is crucial for developers and database administrators to design and optimize queries, use appropriate isolation levels, and handle exceptions to minimize the occurrence of deadlocks in the SQL Server database.

Q18. Explain the concept of collation in SQL Server

Collation determines how string comparison and sorting operations are performed in the database. It defines the rules for character set representation, case sensitivity, and accent sensitivity. Choosing the right collation is crucial to ensure proper data handling and sorting in multilingual environments, as it affects how data is stored and retrieved. Furthermore, collation impacts the results of queries involving string comparisons and order by clauses. Different collations can yield distinct results, so it’s essential to select the appropriate collation that aligns with the specific language and sorting requirements of the database and application.

Q19. What are Indexes and how do they improve query performance?

Indexes are database structures that improve query performance by creating a quick lookup path to locate specific data. They work like a table of contents, allowing the database engine to find data more efficiently, reducing the need for full table scans. By using indexes on frequently searched columns, such as primary keys or columns involved in JOIN and WHERE clauses, query execution time can significantly improve.

Read our Popular Articles related to Software Development

Conclusion 

We hope our SQL Server Questions and Answers guide is helpful. We will be updating the guide regularly to keep you updated.

If you’re interested to learn more about SQL, full stack development, check out upGrad & IIIT-B’s Executive PG Program in Full-stack Software Development which is designed for working professionals and offers 500+ hours of rigorous training, 9+ projects, and assignments, IIIT-B Alumni status, practical hands-on capstone projects & job assistance with top firms.