Have you ever had 2 columns in a table that you need to select the maximum value from? Not the maximum value from each column, but the biggest value after comparing the columns? Usually where I have this problem, I’d use a CASE statement, but there’s another way. Let’s setup some test data: So how […]
I hit an error recently on a server that caused backups to fail. The database was backing up to a UNC path. Looking in the SQL Log file and Event Viewer, I found the following error: The operating system returned the error ‘121(The semaphore timeout period has expired.)’ while attempting ‘DiskChangeFileSize’ on ‘\\uncpath\folder\databasename.bak’. Looking in […]
I needed to add a Unique Constraint today to a table. We could go ahead and just add a constraint, but the data I wanted to constrain on was already indexed. Could I just make that a Unique Index and be done with it? Let’s find out with the following table: Nice simple table. Let’s […]
If you’ve done work with stored procedures, you are probably aware that stored procedures have parameters, and that the parameters can be defaulted when you declare them. I was recently caught out due to some application code that checked when a parameter was specified for a stored procedure, if the value for the parameter was […]
If you have a complex query, a CTE (Common Table Expression) can be a great way to break down the query into individual parts. What is often overlooked by developers is that CTEs can cause significant performance problems when used incorrectly. A common pattern I have seen is joining to a CTE multiple times. This […]
Recently I needed to diagnose why a weekly, multi-step SQL Agent Job was taking so long. I thought, no worries, I’ll go and look at the history to see which step it was. Except…there was no history. Looking at the Job Activity Monitor, I could see that the Last Run was Saturday morning, and the […]
There’s a common belief that you cannot rollback a truncate statement in SQL Server, but is that true? We can find out with a pretty simple test. First, we need a table we can truncate. Obviously you don’t want to go and try this out on your Production tables, so lets create a test one: […]
In SQL, when you don’t want to change the result set, but you want to provide some messages, for example, when debugging a query in SSMS, it can be really helpful to use the PRINT statement. What you’ll start to notice though is that the print statement doesn’t always print straight away. Usually this is […]
SQL Server Reporting Services uses Windows Authentication to authenticate users when they connect, but it also impersonates the user. Depending on how you’re setup, this often requires the creation of an SPN. SPN’s are awesome when they work, they allow integration with Active Directory, but when they don’t work, it’s a nightmare to fix. Reporting […]
While trying to debug performance for an application the other day, I noticed that the expected behavior for the application was to use Read Committed Snapshot Isolation level, however when a query was run it seems that it is running in isolation level Read Committed. Why the change? Testing the problem I’m using the Stack Overflow database […]
