Sql-Server

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: Administration Level: Advanced

Question:

You need to write code to avoid the impact of indexes during data loads.

You could DROP and CREATE the indexes, or you could DISABLE and REBUILD them.

Which should you choose and why? How would the performance compare between the two options?

2025-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: Administration Level: Medium

Question:

You are using linked servers to refer to tables in a database on another server.

You are concerned that the name of the server appears in three-part table names throughout your code. If the linked server name changes, it would be very messy to locate and correct all references to it.

2025-04-13

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. One common request that we’ve had, is that users need to be able to extract the component parts of a URL.

In the example above, you’ll see that we have added a table-valued function called URLComponents.

It supports protocols, host names, path names, search terms, and anchors.

In case, you need to process individual components, to help with that, we’ve added a number of scalar functions: URLToProtocol, URLToHostName, URLToPathName, URLToSearchTerms, and URLToAnchor.

2025-04-12

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:

You are reading a T-SQL script. One MERGE statement merges data from TableA into TableB, and contains two clauses:

WHEN NOT MATCHED BY SOURCE

and

WHEN NOT MATCHED BY TARGET

What is the difference between these? And what is the most common operation that’s executed in each of these clauses?

2025-04-11

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. One common request that we’ve had, is that users need to be able to extract the component parts of a file path.

In the example above, you’ll see that we have added a table-valued function called FilePathComponents.

It supports both drive paths and UNC paths.

In case, you need to process individual paths, to help with that, we’ve added a number of scalar functions: FilePathToFileName, FilePathToFolderName, FilePathToFileExtension. You can see them in action here:

2025-04-10

Sometimes, you want to insert a row if it’s missing but update it if it’s already there. We had asked Microsoft for an UPSERT statement as that’s what it’s called in other databases.

What we got in SQL Server 2008 instead was a MERGE statement. It’s more flexible than an UPSERT statement.

In the example above, I’ve said I want to merge into the dbo.CinemaGroups table. Note that this statement also has an optional INTO word, just like INSERT does. That’s then considered the target table. Only one table can be modified in a single query, including with a MERGE query.

2025-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: Administration Level: Medium

Question:

You have a database attached to a SQL Server 2022 server.

You need a copy of the database on a SQL Server 2017 server.

How can you do that?

Answer:

It’s messy.

You cannot restore a backup from a later version to an earlier version, even if the database compatibility level is at the lower version.

2025-04-08

An UPDATE statement is the way we change data in an existing row or multiple rows of a table. In the example above, I’ve said I want to update dbo.OrderContacts; that’s the table.

I asked SQL Server to set the OrderContact column to Terry Jones, but only where the OrderID is 1. That’s how we modify values in a table.

If I needed to modify more than one column, after Terry Jones, I could just put a comma, and then I don’t have to use the word SET again.

2025-04-07

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. A while back, we added a tool to do date difference without counting weekends. Someone asked for one that just excludes Sundays, so we added a tool called DateDiffNoSundays.

The procedure takes two parameters:

@FromDate - the starting date @ToDate - the ending date

Find out more

You can see it in action in the main image above, and in the video here:

2025-04-06

Deleting rows from a table can take quite a long time because there is a lot of work going on under the covers. One way that you can just completely empty a table is by executing a TRUNCATE TABLE statement.

While this is fast, there are two issues with it:

First, if the table had delete triggers, and I used a DELETE statement, those triggers would fire. But if I say TRUNCATE TABLE instead, it just quickly nukes the entire contents of the table, by deallocating all the storage used for table rows. But those triggers wouldn’t fire.

2025-04-05