You’ll find a lot of people, consultants and peers, liberally dispensing plenty of “best practices” on the Interwebs and in real life. Heck, I’d say that’s pretty much all I do on this blog. But when should you go with the best practice and when should you make your own road?
Category: Basics
Speaking at the Group By conference!
This past Friday, I had the great privilege of speaking at the on-line Group By conference. Group By is a community-driven conference where anyone can submit an abstract. Site visitors will then rate sessions as well as help you build and improve your abstract.
My presentation was about various tips and tricks in SQL Server Management Studio, some of which I’ve already covered in previous articles on this blog.
Practical uses of binary types
The binary datatype of SQL Server is one of those features most developers don’t really use that often, but it turns out there’s more to binary values than just storing large, non-relational blobs.
Have you tried sp_ctrl3?
I frequently need to look up object definitions when I’m developing or query tuning. You could use Object Explorer in SSMS, but that takes a lot of time and clicking. Then there’s the Alt+F1 shortcut, which will trigger the sp_help stored procedure. That however, comes with a lot of annoying built-in limitations, so a few years ago I started building and maintaining a “better Alt+F1” of sorts.
I decided to call it “Ctrl+3“. But I suppose you could assign it to any keyboard shortcut you want.
The compelling case for using heaps
I’m an outspoken advocate of always using a clustered index on each and every table you create as a matter of best practice. But even I will agree that there’s a case for using the odd heap now and then.
Not giving a shit about performance is tech-debt
For practically every piece of code you develop, there will be trade-offs. Sometimes, you can combine the best of two worlds, other times it comes down to some hard choices. For T-SQL developers, it typically boils down to a few key questions:
- How much time can you spend perfecting code instead of just shipping?
- Can we just fix it when it becomes a problem?
- Is buying more hardware cheaper than paying for developers to tune their code?
- Is better code harder to read, and will a junior developer be able to work with it?
Start Management Studio with alternate Windows credentials
If you’re a consultant connecting to remote client servers, or if you have a heterogenous network environment with different Active Directory forests without established trust relationships, you’ll have a few extra challenges connecting to SQL Server using Windows authentication, and SQL Server authentication may not be available.
Copying data with foreign keys and/or identity columns
In a sense, you could call me lazy. If there’s a script that will perform a task for me, I’d rather use that script than reinvent another wheel. Then again, if needs be, I’d rather spend a day writing such a script, rather than spending ten minutes just getting the job done.
Somehow, that makes me a happier developer.
Selectively disable “Include actual execution plan”
The “include actual execution plan” feature in SQL Server Management Studio is an invaluable tool for performance tuning. It returns the actual execution plan used for each statement, including actual row counts, tempdb spills and a lot of other information you need to do performance tuning.
But sometimes you want to run a series of statements or procedures where you only want the execution plan for some of the statements. Here’s how:
Detaching a database also alters file permissions
Moving a database or some of its files from one drive to another or from one instance of SQL Server to another is as simple as detaching it and re-attaching it again. This is actually pretty smart, compared to backup–restore, because you only perform one I/O operation (moving the file), as opposed to two (backing up, restoring).
But when you try to attach the database, you might get something like
Msg 5120, Level 16, State 101, Line 3 Unable to open the physical file "E:\Microsoft SQL Server\SQL2014\MSSQL\Data\Playlist.mdf". Operating system error 5: "5(Access is denied.)".
The reason, as I found out the hard way, is that SQL Server can actually modify the file permissions of the .mdf and .ldf files when it detaches a database.