Sql-Server

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

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 query the sys.databases view and notice that the value in the physical_database_name column for a database is different to the name column.

Can you give an example of why this might occur?

2025-04-04

In a previous post, I did an INSERT followed by a SELECT. When I do that, the table needs to already exist.

But what if I want to take the rows returned from a SELECT statement and use them to create a new table?

That’s what a SELECT INTO statement does. In the example above, I’ve taken a distinct list of OrderID and OrderContact values and used them to create a new dbo.OrderContacts table. If the table already exists, the statement will fail.

2025-04-03

Another way you can get rows to insert into a table is by executing a stored procedure or, in fact, even by executing dynamic SQL. Either way, these both use an EXEC statement.

In the example shown, I’m doing an INSERT into the dbo.CinemaGroups table. I’ve declared the two columns that are being inserted. The stored procedure returns the rows that I want to insert.

This is again fairly easy to troubleshoot because the EXEC clause can usually be executed by itself, and you can see the rows coming back.

2025-04-01

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. I do a lot of reviewing of database designs and one thing I always check for is the use of data types, including when they’ve been used in views. So we added a tool that can help to find these. It’s called ListViewColumnsAndDataTypes.

The procedure takes four parameters:

@DatabaseName is the database to process (not used in the Azure SQL DB version) @SchemasToList is a comma-delimited list of schema names or ALL @ViewsToList is a comma-delimited list of view names or ALL @ColumnsToList is a comma-delimited list of column names or ALL

2025-03-31