Sql-Interview

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

UNION and UNION ALL are commonly used to combine two sets of rows into a single set of rows.

EXCEPT is another set operator.

Can you explain what it does?

Answer:

EXCEPT is used to remove any rows in the first set of rows, if the same rows appear in the second set.

2021-04-29

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Server Tuning Level: Advanced

Question:

SQL Server 2008 introduced a server option for optimize for adhoc workloads.

Can you explain the problem that it is designed to solve, what most commonly causes the problem, and what impact this option has when you enable it?

2021-04-27

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Intro

Question:

Consider the following code:

SELECT CustomerID, CustomerName 
FROM Sales.Customers
WHERE BusinessCategory = 'Cafe'

UNION

SELECT CustomerID, CustomerName
FROM Sales.Customers
WHERE City = 'Toronto';

A: What would be the difference in results if the query was changed to use a UNION ALL instead of the UNION?

2021-04-22

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

When you add a column to an existing SQL Server table, the column is added to the end of the table. The column will end up with the highest column_id.

If a developer asks you how a column can be added to the middle of a table instead, what is your advice and how would you approach the request?

2021-04-21

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

When you define a column default with code like below:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.Customers;
GO

CREATE TABLE dbo.Customers
(
    CustomerID int IDENTITY(1,1)
        CONSTRAINT PK_dbo_Customers PRIMARY KEY,
    TradingName nvarchar(100) NOT NULL,
    CreatedDate datetime2(3) NOT NULL DEFAULT (SYSDATETIME())
);
GO

the system will define the name of the default. Can you give examples of why specifying the name of the default instead of letting the system supply it would be a good practice?

2021-04-15

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

Consider the following code:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.Customers;
GO

CREATE TABLE dbo.Customers
(
    CustomerID int IDENTITY(1,1)
        CONSTRAINT PK_dbo_Customers PRIMARY KEY,
    TradingName nvarchar(100) NOT NULL,
    CreatedDate AS SYSDATETIME()
);
GO

INSERT dbo.Customers (TradingName)
VALUES (N'Customer A');
GO

SELECT * FROM dbo.Customers;
GO
SELECT * FROM dbo.Customers;
GO

Will the CreatedDate column return the same value in both SELECT statements?

2021-04-13

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Security Level: Intro

Question:

Many applications connect to SQL Server databases with users that are members of the db_datareader and/or db_datawriter roles?

Why should you avoid using those roles?

Answer:

db_datareader and db_datawriter are fixed database roles that were provided for convenience. They aren’t a good option from a security perspective.

2021-04-09

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Intro

Question:

Many versions of SQL require every SQL statement to be terminated with a semicolon.

Since it was created, T-SQL has listed statement terminators as optional.

Are there any situations where they are required, and not optional?

2021-04-08

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Intro

Question:

Look at the following multi-row INSERT statement:

INSERT Sales.CustomerGroups
(
    CustomerGroupID, CustomerGroupName
)
VALUES (1, 'Group A'),
       (2, NULL),
       (3, 'Group C');

The column CustomerGroupName is defined as NOT NULL so the second row cannot be inserted.

2021-04-06

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Intro

Question:

If you create a table using the following code, what would the name of the primary key constraint look like?

CREATE TABLE Sales.CustomerGroups
(
    CustomerGroupID int IDENTITY(1, 1) PRIMARY KEY,
    CustomerGroupName nvarchar(50) NOT NULL
);

How could you specify the name?

2021-04-01