20 Sql Server Developer Interview Questions & Answers
Below is a list of our Sql Server Developer interview questions. Click on any interview question to view our answer advice and answer examples. You may view 5 answer examples before our paywall loads. Afterwards, you'll be asked to upgrade to view the rest of our answers.
1. What is a linked server?
This question focuses on the knowledge of SQL configuration.
A linked server object allows you to create distributed queries where the required data sources exist in disparate systems.
For example, data for your query may exist in a database on one server and data from a database on another server. You can even join to a non-SQL Server datasource, provided there is an appropriate data provider for the external database. Through SSMS, you'll need to create a linked server through the Object Explorer or through T-SQL.
-- In this example, you can use the OPENQUERY() syntax as follows:
select * From openquery(NAMEOFLINKEDSERVER,'select * from SQLSERVERTABLEONLINKEDSERVER')
-- You can also use the following:
-- LINKEDSERVERNAME.DATABASENAME.SCHEMANAME.TABLENAME
select * from ORDERLINKEDSERVER.WIDGETORDERSDB.DBO.ORDER
Written by Edward Danganan on April 13th, 2021
2. What is a view and how is it used?
This question focuses on SQL terminology and actions related to it.
A view is composed of a query on one or more tables and is saved in the database as a separate object.
Views can be used if you find yourself creating the same query repeatedly. It provides a way of abstracting complex logic so that the user of the view needs only be concerned with the output and not necessarily the complexities of the underlying query.
Views also provide a means of security by adding read-only permissions to views, disallowing access to the underlying tables, and exposing only the required fields.
/**This is an example of creating a view of customer orders
You have the opportunity of exposing only the required fields to an application, by restricting the application to only the view, since either table may contain
sensitive information that the application doesn't necessarily need access to**/
CREATE VIEW [dbo].[CustomerOrders]
AS
SELECT dbo.Customer.LastName, dbo.[Order].OrderAmount, dbo.[Order].OrderDate
FROM dbo.Customer INNER JOIN
dbo.[Order] ON dbo.Customer.CustomerID = dbo.[Order].CustomerID
GO
Written by Edward Danganan on April 13th, 2021
3. How would you return the first 100 records from a table based upon the order of a numerical value?
This question focuses on using SQL to obtain specific data.
The TOP expression allows you to return a limited number or percentage of rows from a query. It is typically used in conjunction with the order by clause.
/** In an [Order] table, the following query will return the top 100 orders based upon the total cost of an order, in descending order. **/
select top 100 [OrderID], [OrderAmount], [CustomerLastName]
from Orders
order by OrderAmount desc
Written by Edward Danganan on April 13th, 2021
4. Define SSIS.
This question concentrates on SQL terminology.
SSIS stands for SQL Server Integration Services, formerly DTS (Data Transformation Services). SSIS is a separate platform for allowing ETL processes and data integrations. A common use case is pulling data from a disparate data source (Excel worksheet, CSV, or a delimited text file) into a database. It is a robust tool, providing numerous transformation functions and invocation of custom code like VBScript and C# assemblies. SSIS jobs can be called from the SQL Server Agent process and can therefore be scheduled as well.
In answering this question it shows the technical interviewer that you are aware of real life issues that can be resolved using existing tool sets. It also shows that do not attempt to "reinvent" the wheel when there are off the shelf solutions available. This demonstrates knowledge and experience.
Written by Edward Danganan on April 13th, 2021
5. Describe the process of replication.
This interview question shows the developer's knowledge of SQL server data transfers.
There are different types of SQL Server replication.
Transactional replication enables real-time data integration from one database to another database. In this scenario, the requirement may be for transactional data on one server to be replicated to a database on another server.
Merge replication is intended for the distribution of data to mobile applications or distributed server applications. This approach is typically used to address data conflicts across systems. Snapshot replication provides an initial data source for a transactional or merge application.
In summary SQL server replication is used to copy and distribute data and database objects from one database to another then synchronizing between databases to maintain consistency of data.
Written by Edward Danganan on April 13th, 2021
6. What is a SQL Server Credential?
This question shows a developer's ability to connect to non-SQL servers.
A Credential is a server-level SQL Server object that contains authentication information (username/password). This object enables a SQL Server user to connect to a resource outside of the SQL Server environment. For example, a credential object can be used to connect to other domain resources on the network.
If you are using a credential to connect to a Windows resource, the credentials used are a user's Windows credentials.
Written by Edward Danganan on April 13th, 2021
7. What are server roles and how are they used? Name some fixed database roles.
This interview question targets the developer's knowledge of the SQL server roles and the authority/permissions related to them.
SQL Server roles are similar to Active Directory groups on Windows. They enable a server or database administrator to assign database-wide function(s) to an individual or group. There are fixed database roles and user-defined roles, which can be customized.
Examples of fixed-database roles are:
db_owner
db_securityadmin
db_accessadmin
db_backupoperator
db_ddladmin
db_datawriter
db_datareader
db_denydatawriter
db_denydatareader
Written by Edward Danganan on April 13th, 2021
8. What is the function of the SQL Agent? Provide a use case and TSQL code.
This question concentrates on the developer's knowledge of performing actions on the SQL server.
SQL Agent is a component of SQL Server, which allows the scheduling of SQL jobs. You can execute SQL statements, run stored procedures, or run a batch scripts using the CMD line. A typical use case could be to make a nightly backup of a database and copy it to a secured location on your network.
T-SQL stands for Transact SQL. This is a set of extensions from Microsoft that adds several features to SQL including transaction control, error handling and row processing to name a few.
/**Creating a Sql Agent job in T-SQL is as follows:**/
EXEC dbo.sp_add_job
@job_name = N'NameOfYourJob' ;
GO
--Then, create a job step within the job
EXEC sp_add_jobstep
@job_name = N'NameOfYourJob',
@step_name = N'NameOfYourStep',
@subsystem = N'TSQL',
@command = N'ALTER DATABASE SALES SET READ_ONLY',
@retry_attempts = 5,
@retry_interval = 5 ;
GO
-- Create a schedule
EXEC dbo.sp_add_schedule
@schedule_name = N'RunOnce',
@freq_type = 1,
@active_start_time = 233000
-- Attach your schedule to the job
EXEC sp_attach_schedule
@job_name = N'NameOfYourJob',
@schedule_name = N'RunOnce';
GO
-- Add your job to the server
EXEC dbo.sp_add_jobserver
@job_name = N'NameOfYourJob';
GO
Written by Edward Danganan on April 13th, 2021
9. In a SQL query, how would you perform pattern matching?
This question focuses on a developer's ability to select data in SQL.
Pattern matching is associated with the 'like' expression in your where clause and contains the percent sign (%) symbols.
This allows you to search for patterns in data even if you do not know the exact word or phrase you are seeking.
In SQL, patterns are case-insensitive by default.
/**In an Authors table, for example, you are tasked with finding all authors, whose last name starts with 'Sm'. The query would be:**/
select * from Authors where LastName like 'Sm%'
/**The % sign acts like a wildcard character so that all records where the authors last name starts with 'Sm' are returned, regardless of what follows the 'Sm'. For example, Smith, Smithereens, Smack, will be returned.**/
Written by Edward Danganan on April 13th, 2021
10. What is a group by clause and how would you use it?
This question shows how a developer can sort data based on values they are searching for. This is an important skill for a developer as it shows that you have both the knowledge of the syntax and the ability to write efficient code that can return specific data of interest.
The group by clause is a SQL server expression that is used to group rows that have the same values. It is often used with aggregate functions, such as sum(), count(), max(), min(), avg() to categorize rows according to another column in your table.
/** You are tasked with determining how much each customer in an Orders table has spent.
The following query will return those rows and total amount spent on all orders per CustomerID**/
SELECT
[CustomerID]
,sum([OrderAmount]) as Total_of_all_orders
FROM [dbo].[Order]
group by [CustomerID]
Written by Edward Danganan on April 13th, 2021
11. In SSMS, how do you know if a query is performing a table scan?
This interview question shows knowledge of basic queries and how they work. SSMS stands for Server Management Studio. This tool is used by developers to configure, manage and administer all components with Microsoft SQL.
A table scan is when an SQL server has to scan the data to find the appropriate record.
In SSMS, copy the SQL syntax into a new query window and select the 'Include Actual Execution Plan' option. This will show a diagram representing your query and will display 'Table scan' above the offending table(s). There may be other issues at play regarding database performance, like I/O, or CPU/Memory usage. When a query performs a table scan, each row of a table is read sequentially and the columns of those tables are evaluated according to the criteria in your where clause.
A table scan is not necessarily a bad thing in small tables as performing a table scan in some cases may be faster than utilizing an index, but if there is a noticeable performance issue querying a table that is doing a table scan, adding an index may be beneficial.
A table scan is the opposite of a seek. A seek uses the index to pinpoint the records that are needed to satisfy a query.
This question offers the opportunity to elaborate and demonstrate your knowledge to the technical interviewer.
Written by Edward Danganan on April 13th, 2021
12. What is a correlated subquery? Provide an example.
This interview question focuses on a developer's understanding of SQL.
A correlated subquery is a query that is tied to an outer query, typically used in a self-join. A subquery can be used anywhere an expression is allowed.
There are three types of subquery:
1. Single Row - Subquery which returns a single row output
2. Multi Row - Subquery which returns a multi row output
3. Correlated - Subquery depend on the data provided by the outer query
-- In this example, the subquery and outer query references the same table (self-join) and returns records from the products table where the product prices are equal
-- to the highest priced item from the same table.
SELECT
product_name,
list_price,
category_id
FROM
products p1
WHERE
list_price IN (
SELECT
MAX (p2.list_price)
FROM
products p2
WHERE
p2.category_id = p1.category_id
GROUP BY
p2.category_id
)
ORDER BY
category_id,
product_name;
Written by Edward Danganan on April 13th, 2021
13. What is a temporary table and what is it used for?
This interview question shows the developer's ability to understand SQL usage.
A temporary table is a table that exists temporarily in the database for the purpose of holding data in a staging area during the execution of a query. These tables are stored in the system database, tempdb. The data is only available to the current session in which the query was executed. It is commonly used in loop operations.
-- you can predefine a temporary table as such:
-- note the '#' symbol, which denotes a temporary table
create table #temp_table
(variable1 int null,
variable2 int null)
-- or you can create one on the fly, by selecting data into it:
-- Note that the column names and datatypes will be the same as the columns from the source table
select OrderID,CustomerID
into #temp_table
from [Order]
Written by Edward Danganan on April 13th, 2021
14. What is a cursor? Provide the syntax for creating one.
This interview question tests the developer's knowledge of both SQL terminology and also how to put it into action.
A cursor is a sql server object intended to allow iteration of a result set and processing of each row one at a time instead of all at once in TSQL. You may need to interrogate each row for specific criteria and perform some action based upon the row that meets that criteria. In some cases, cursors may negatively affect performance so care must be taken before creating and using one. A while loop may be an appropriate option.
-- cursors are typically created and invoked within a stored procedure
create procedure DoSomeProcessing as
DECLARE @TableName VARCHAR(50) -- table name from 'Customer' schema
DECLARE @Param VARCHAR(50) -- parameter to be used for further processing
DECLARE @Select VARCHAR(50)
SET @Select = 'SELECT * FROM '
-- declare the cursor
DECLARE db_cursor CURSOR FOR
SELECT TABLE_NAME FROM information_schema.tables
WHERE TABLE_NAME like '%Customer%' and TABLE_TYPE='TABLE TYPE'
-- allocate the cursor
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @TableName
-- process rows until there are no more to process
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @SQL_STRING VARCHAR(50)
--concatenate each table name in a variable,add it to the sql statement
SET @Param='Customer.'+@TableName
SET @SQL_STRING = CONCAT(@Select,@Param)
-- Perform some action on the current table in the loop
-- Just do a select on each table
EXECUTE sp_executesql @SQL_STRING
FETCH NEXT FROM db_cursor INTO @TableName --gets the next table name
END
CLOSE db_cursor
DEALLOCATE db_cursor
Written by Edward Danganan on April 13th, 2021
15. What is a trigger and how many different types are there?
This question tests how familiar a developer is with SQL functions and abilities.
A trigger is a type of stored procedure that automatically executes when an event occurs on tables to which it is applied or a change in the database.
There are triggers that respond to insert, update, and delete events on a table (DML - data manipulation language triggers) and DDL triggers (DDL - data definition language), those that respond to the changes in the database structure, for example, events that correspond to sql statements that start with the keywords (CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS).
--The syntax for creating an insert trigger in Sql Server is:
CREATE TRIGGER trigger_name ON table_name
FOR INSERT
AS
-- insert statement(s)
-- Other sql statements as needed
Go
Written by Edward Danganan on April 13th, 2021
16. What is stored procedure and what is the basic syntax for creating one? How and why would you use one?
This question concentrates on the developer's knowledge of SQL procedures and the ability to implement them.
A stored procedure is a series of sql statements combined together as a unit of statements which can be select, insert, update or delete.
Stored procedures are compiled and stored in the database and are therefore more efficient to execute as they can take advantage of a cached query plan.
If you find that your application repeatedly depends upon the same sql statements, it may be advantageous to combine them into a stored procedure. If the table structure in your query changes, you may need to change your query in a limited number of stored procedures. If you've called sql in numerous places in your code, you may need to invest significantly more time to make those changes since the sql may be referenced in random places in your application code.
The technical interviewer wants to see if you are able to write efficient code that is not verbose. When you are working as part of a development team it is important that you write code that is efficient and clear as this will make it easier for other members of the team to use and edit. It also helps with debugging if a potential issue occurs.
--In Sql Server, the syntax is as follows:
CREATE PROCEDURE name_of_stored_procedure (list of parameters) AS
-- select, insert, update, delete sql statements
Written by Edward Danganan on April 13th, 2021
17. Describe the difference between an outer and inner join.
This interview question tests a developer's basic knowledge of SQL conditions. Joins are an integral part of SQL and the interviewer wants to determine how familiar you are with using joins and can you recognise when to use a particular type.
An inner join between two tables expects that there is a common key between them and a query including that key in the join condition will only return those matching rows.
An outer join between two tables will return all the rows from the left table (in the case of a left join) and only those matching rows from the right table.
The converse is true for a right outer join.
For example in the case of author and publication tables with the AuthorID as the common key, a left join between the author and publication table will result in all rows from the author returned and only rows with a matching key from the publication table. An author may be in the process of publishing a book so she may not have a row in the publication table yet.
Written by Edward Danganan on April 13th, 2021
18. What is a cartesian product?
This interview question focuses on the developer's knowledge of SQL terminology and types of join. A join clause in SQL combines columns from one or more tables into a new table.
The cartesian product occurs when you attempt to join two tables where there is no relationship between them (there is no common key). In this case, all rows of one table are paired with all rows of the second table. This is also known as a cross join condition. In general, this situation should be avoided, especially when large tables with millions for rows are involved. If this occurs, the server CPU and/or memory may be affected which will degrade performance of your database server and even block incoming database requests.
Written by Edward Danganan on April 13th, 2021
19. What is meant by referential integrity?
This interview question focuses on the developer's knowledge of SQL terminology and the use of foreign keys.
Referential integrity is a property of a database that states that all of its references are valid. If a primary key/foreign key relationship exists between a parent and child table, each foreign key table must have a matching primary key row in the parent table. This relationship prevents inadvertent changes being made to tables leading to data discrepancies.
For example, if you have an author and publication table with AuthorID as the primary and foreign key, you will not be able to delete a row from the author table if there are related rows in the publication table without violating referential integrity and resulting in an error.
Written by Edward Danganan on April 13th, 2021
20. How do you determine which columns to add in an index and what command would you use to create it?
This interview questions the developer's ability to prioritize columns and how to add them to the index. The technical interviewer is attempting to determine how familiar you are with the SQL syntax and how you can write clean and efficient code.
Unique or not null value columns that are part of the where clause are generally good candidates, as well as columns having integer values. It is advantageous to create your index columns in the order in which they appear in your where clause. You can have only one clustered index per table, but up to 99 non-clustered indexes per table, in SQL Server.
--An example of creating a non-clustered index on the table, Order would be:
CREATE NONCLUSTERED INDEX [OrderSearch_IDX] ON [dbo].[Order]
(
[PartnerID] ASC,
[OrderDate] ASC,
[OrderStatusID] ASC
)
GO
Written by Edward Danganan on April 13th, 2021