Category Archives: T-SQL

Behaviour change for delete … output … into in SQL Server 2014

A colleague of mine discovered an interesting behaviour change from SQL Server 2012 to SQL Server 2014. We recently upgraded one of our servers from SQL 2012 to SQL 2014 and a job started failing. (Although I haven’t explicitly tested … Continue reading

Posted in SQL Server, T-SQL | Tagged , , , | 4 Comments

Migrating SQL Server logins

Once upon a migration I would use sp_help_revlogin to migrate logins and remap the SQL server logins using sp_change_users_login. During some recent migration work I realised that SQL Server Management Studio could do most of the work of sp_help_revlogin. I … Continue reading

Posted in Security, SQL Server, T-SQL, Uncategorized | Tagged , | 8 Comments

Rolling back transactions with cursors and local variables.

I’ve been doing some work on a credit card payment system lately. Obviously this needs to be robust and consistent so I’ve been working on error handling and “transactionalising” certain parts of the process. I needed to answer a couple … Continue reading

Posted in Database Theory, SQL Server, T-SQL | Tagged | Leave a comment

Quick Tip: Migrating a SQL Server Mirroring Witness server.

If you’re like me, you might be thinking “I want some more things to think about when I migrate between SQL Servers.” … No? Okay, fair enough. There’s a whole listĀ of things to think about when migrating from one SQL … Continue reading

Posted in Database options, Disaster Recovery, SQL Server, T-SQL | Tagged , , | 3 Comments

Quick tip: Querying AD with xp_logininfo

We all have blind spots in our knowledge. We don’t know they are there and when we are thinking through an issue our mind quickly rejects some plans because of these blind spots. This happened to me recently when I … Continue reading

Posted in Security, SQL Server, T-SQL | 1 Comment

Conditional Aggregates

The Problem: A legacy table contains amounts and a char column indicating whether the amount is a credit or a debit. We want an aggregate (over a given range) of the amounts. We need to add the credits and subtract … Continue reading

Posted in SQL Server, T-SQL | 6 Comments

In Memory OLTP with SQL Server 2014

SQL Server 2014 CTP is available for download and evaluation and it contains a couple of exciting performance enhancements. One of these is OLTP databases optimised to be memory resident. So I wanted to have a play – and I … Continue reading

Posted in Database options, Database Theory, SQL Server, T-SQL | Tagged , | 1 Comment

Wildcards and Indexes

I was asked a good question the other day, one that I wasn’t sure of the answer. The question was “does SQL Server use an index if you have a wildcard in the search predicate?” My initial thought was it … Continue reading

Posted in Database Theory, SQL Server, T-SQL | 1 Comment

Quick tip: Using sp_server_diagnostics

In SQL Server 2012 a new procedure sp_server_diagnostics returns a payload of information about server health and performance. The sproc is leveraged by the Windows Failover Cluster service and is designed to run in repeat mode. However the sproc can … Continue reading

Posted in SQL Server, T-SQL | 3 Comments

Restore database permissions

Introduction: I came across a couple of subtle gotchas around the permissions required to restore a database in SQL Server 2008+ recently. I’m interested in situations where a DBA needs to allow certain users the ability to restore a given … Continue reading

Posted in Database options, Disaster Recovery, Security, SQL Server, T-SQL | Tagged , , | 14 Comments