Skip to content

Data Storage

Little SQL Server Tricks: The STRING_AGG() Function

When it comes to reporting, we often get some special requirements to transform data into a specific form. For one report we had a Workforce table with data that looks like this:

EmployeeId FirstName LastName DepartmentId
1001 John Smith 10
1002 Jane Doe 10
1003 Max Miller 20

For the report, we need to group the employees by DepartementId, and combine all employees with their EmployeeId into this form:

DepartmentId EmployeeFormatted
10 John Smith (1001), Jane Doe (1002)
20 Max Miller (1003)

Little SQL Server Tricks: How to Fix Misaligned Log IOs

While installing SQL Server on a new SSD, I run into a problem with the installer. Everything worked, except the add-on features I selected failed. Strange. But then SQL Server did not start, and I found this entry in the Event Viewer:

There have been 256 misaligned log IOs which required falling back 
to synchronous IO. The current IO is on file C:\Program Files\Microsoft SQL 
Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\master.mdf.

Little SQL Server Tricks: Rename Default Constraints

One would assume that renaming a default constraint should work the same way as renaming a primary key. For some unknown reason that was not the case as I had to fix the (dynamically generated) names of default constraints. The format that worked at last was this one:

1
exec sp_rename N'SCHEMA.NAME_OLD', N'NAME_NEW' , N'OBJECT';

How to Calculate the Time Between Two Rows in SQL Server

I had an interesting problem to solve: How long did a task take to complete? The challenge was that our task history table contains one row for the start and another one for the end of a task:

Id TaskId Action Timestamp
1 100 Start 2022-08-01 08:00:00.000
2 101 Start 2022-08-02 07:30:00.000
3 100 Stop 2022-08-03 17:00:00.000
4 102 Start 2022-08-04 08:00:00.000
5 102 Stop 2022-08-04 17:00:00.000
6 101 Stop 2022-08-05 18:00:00.000