Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

Adding a column to every table in a database

Despite the best laid plans, sometimes circumstances or project scope change and the definition of one or more tables in your database must be altered after they have been created. It's just a fact of life for a DBA. Read more →

How to: Disable constraints on a table

Sometimes it's useful to disable one or more constraints on a table, do something significant, and then re-enable the constaint(s) after you're done. This is most often done to improve performance during a bulk load operation. Read more →

SQL Server locks: a sleeping process still holds them

Fellow SQL Server enthusiast and online friend of mine, Jonathan Kehayias is very active in the MSDN Forums. He frequently provides good, insightful information to those seeking advice. In response to a recent question in the Database Engine Forum, Jonathan provided a good example how a process marked as sleeping can still hold locks on a SQL Server resource. Read more →

Finding IDENTITY columns

As database administrators and developers, it's our job to know the in's and out's of the data in our charge. Whether we're designing entity relationships to support underlying business requirements, optimizing indexes to achieve the best response times, or writing a query to retrieve the right data in the most efficient manner, having an intimate knowledge of the database is key. Read more →

Getting column information using T-SQL

Ocassionally, I see a question in the MSDN Forums where someone wants to know how to retrieve a list of column information for a table using only T-SQL. Although there are several ways in which to approach this, I like the following method. Read more →

What SQL Server service pack do I have installed?

If you've used SQL Server for a while, you're probably familiar with the @@version function. Selecting the function in a query window produces the following results on my system. From this, you can quickly determine that I'm running SQL Server 2005 Developer Edition version 9. Read more →

Real world DBA podcast

I recently worked with Buck Woody to turn one of my blog posts into a podcast. It's my first podcast! :) I recorded it about midnight one evening after everyone else in my family had retired for the evening. Read more →

Does the order of columns in an index matter?

A single column index is fairly straightforward to understand. You may have heard it compared to the index in the back of a technical book. When you want to find some information in the book, say DBCC INPUTBUFFER, you turn to the index in the back of the book and look up DBCC INPUTBUFFER. Read more →

Using CROSS JOIN to generate test data

To prepare for an upcoming technical presentation, I need to create some test data to use in a demo. I'm planning to show how different T-SQL programming techniques directly affect SQL Server's efficiency in resolving the query. Read more →

Using sp_MSforeachtable and DBCC CLEANTABLE to reclaim space

In a prior posting, I demonstrated how the undocumented sp_MSforeachtable stored procedure can be used to iterate through each of the tables within a database. In a separate posting earlier this year, I also demonstrated how the DBCC CLEANTABLE command can be employed to reclaim space that was once consumed by variable length character columns that have been subsequently dropped. Read more →