Blocking and deadlocking are not things you typically want to do deliberately. But sometimes you need to test error handling or prove a point and then you need to do the unusual.
A few months ago I wrote a post about the fact that I had actually submitted a session to PASS Summit. Well as I’m sure you are aware the selected sessions have been announced. They were officially announced Friday but I actually received my result email on Thursday evening.… Read more
Earlier this week I was scripting out a table with some legacy columns (InsertDate and InsertBy for example). If you have created these type of columns before you have probably put a default on each. InsertDate is pretty easy, probably something like GETDATE() or GETUTCDATE(). But what do you… Read more
I got my first real world experience with a TDE (transparent data encryption) database recently. For those who don’t know TDE encrypts all of the data at rest. So the database files are encrypted and the backup is encrypted. The data is automatically decrypted as you read it and encrypted… Read more
Recently Adam Machanic (b/t) was telling me about an interesting effect of the top operator. When I asked for an example he pointed me to a few of his sessions from various Pass Summits. They are amazing and I recommend watching them, but they… Read more
Renaming a database isn’t something you do frequently but it does have bit of a gotcha. First of all let’s go over how you actually rename the database. It’s pretty simple.
Right click on the database name in the object explorer and select rename or highlight the name and… Read more
TLDR; Search for and modify the sqlfile.sql file.
During a recent T-SQL Tuesday Boris Hristov (b/t) taught me about SET NOEXEC ON in his post Teaching and Learning. The NOEXEC Thing. When this setting is turned on queries in the current window are compiled but not… Read more
It’s the second Tuesday of the month and that means T-SQL Tuesday time! T-SQL Tuesday is a blog party started by Adam Machanic (b/t) over 5 years ago. Each month a different blogger will present an idea to blog on. Side note: If you are having… Read more
Recently we have been doing a number of instance moves as part of a large upgrade project. And as anyone who’s done many of these types of moves knows, orphans are a big pain. You backup your database from one server and restore it to another and all of a… Read more
I blogged in the past about two simple commands that can be a big help in performance tuning SET STATISTICS IO and SET STATISTICS TIME. Well I learned a new one recently. Client Statistics Unfortunately there is no t-sql command to turn it on but it’s not terribly difficult.
Query->Include… Read more
A question came up at work the other day, mostly as a mind game, but you never know it might actually come up at some point. So here is the idea.
- UserA is a member of Group1, Group2 and Group3.
- Group1 is set up as a server principal with a…
The default database is one of the options when creating a login in SQL Server. This is the initial database that the login will connect to when logging in. Unless of course you specify a different database in your connection string.
-- Creating a login with a…
I do a lot of testing with security in SQL Server. And of course to do a thorough job of it I need not just SQL Server logins but Windows logins. And that means I need to be able to create and delete windows users and groups. I could do… Read more
Ever wanted to put a comment on a table? Or maybe even a column? How about an expiration date on a object? Well as it happens you can. Extended properties allow you to add a name/value property to a number of the objects in SQL Server. In fact if you… Read more
I went to a SQL Saturday recently and saw a number of great sessions. If you haven’t been to a SQL Saturday before I highly recommend it. It’s a day of free training and networking put on by Pass. The training is done by members of the community who… Read more
If a user is going to call me about problem, I’d much rather know about it ahead of time. In fact my favorite problems are the ones I’ve already fixed before the users ever notice it. In order to manage this amazing feat you have to monitor your instances, your… Read more
As you may know I’m preparing to write my first presentation. I have a great abstract and an outline of the presentation itself. Next step is creating the Powerpoint slides. Then practicing the presentation itself. So while going to my latest SQL Saturday I decided to take the opportunity… Read more
The other day Tom Roush (b/t) and Tim Radney (b/t) were having a discussion on twitter about using scheduled windows tasks to run SQL Processes. Now why would you ever want to do this? Well if you are running SQL Server Express… Read more
I’ve only recently started to play with Powershell (PoSH) but even I’ve begun to discover what a huge number of tasks it can perform. Currently I’m working on a task to run SSIS packages remotely by using Invoke-Command to run DTEXEC on a remote machine. I’ll be posting the final… Read more
When SSIS first came out there were huge numbers of jokes and posts about all of the problems and how everyone thought DTS was much better and did they really have to switch? Over the years I’ve come to appreciate SSIS. It is a very powerful and useful tool that… Read more