Development

How to change the schema of a table

The other day I set out to performance tune a database deployment. The company was getting ready to implement almost a year’s worth of database schema drift where major restructuring had occurred. One of the first things that I noticed…

Dynamically create table results from non-query

Have you ever executed a command with a result set but you didn’t know the exact schema being returned? Have you ever wanted to create a temporary table from a query but don’t want to look up all of the…

Query Object Dependencies

SQL Server Management Studio has a decent interface for viewing object dependencies, check out TechNet for those procedures. But, we all know the value of being able to run queries for the information that we need. There are two methods…

Broken ownership chain – Dynamic SQL

Today I had a co-worker come to my desk wondering why his service was getting a permissions error when selecting from a table. His confusion was rooted in the fact that his service has been using this stored procedure for…

Update zero base column to one base

Problem: A co-worker requested help with a query today… Derik, I have an Email table with a list of email addresses per person record. There is an ordinal column that is base zero but I decided to make the index in the…

Avoid GUID clustered index write problem

Avoid GUID clustered index write problem

A globally unique identifier (GUID) is a great way to identify a record in your database. It can be easily created at the database or application layer and, when generated, is guaranteed to be unique regardless of other records already…

T-SQL Command Line Options – Part 1

Every SQL Server DBA is intimately familiar with SQL Server Management Studio (SSMS) and/or previous tools such as Query Analyzer. What some of us are less familiar with are the various options of executing T-SQL via command line or from…

Temporary Stored Procedures

We are all familiar with table variables, local temp tables, and global temp tables but what some people over look is that stored procedures and functions can also utilize tempdb. I imagine that the reason for this oversight is because…