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 →
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 →
My latest Simple-Talk article is live. It's entitled Using Covering Indexes to Improve Query Performance. Let me know what you think and if you'd like to see more articles/postings on similar topics.
Read more →
Late last year, I posted a blog that described the undocumented sp_MSforeachtable stored procedure in Microsoft SQL Server and how it could be used to execute commands on each table within a database.
Read more →
Thanks to everyone who came to my Writing Better Queries session at DevLink this afternoon! I truly appreciate that you took time of your busy schedule to attend! I hope it was worth your while.
Read more →
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 →
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 →
I was recently perusing the the MSDN SQL Server forums and noticed a question about localization of Microsoft SQL Server messages. I replied to the post with a mention of the sys.
Read more →
Here's a presentation that I gave yesterday afternoon at DevTeach/SQLTeach. It's called Tips & Tricks for Writing Better Queries. It's very similar to the presentation I gave a month or so ago at the Nashville SQL Server User Group meeting.
Read more →
As most you already know, Notification Services is not part of Microsoft SQL Server 2008. I think this is a shame since SSNS is really a great product. It may be rather complex at first glance, but it's a great product nonetheless.
Read more →
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 →
Thanks to all 60+ people who braved the flood warnings in Nashville to attend the SQL Server User Group meeting earlier today. I hope you found it to be worth your while.
Read more →
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 →
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 →
A very astute SQL Server professional and reader of this blog posed the following question in a comment to a prior posting entitled "Does the order of criteria the WHERE clause matter?
Read more →
Recently, I demonstrated that the order of columns in a composite index greatly determines its usefulness. Microsoft SQL Server can efficiently resolve queries using a composite index if the search criteria includes the first column in the index.
Read more →
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 →
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 →
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 →
In a recent post, I demonstrated how SQL Server will reuse space made available when rows are deleted from a table. This occurs automatically without any outside intervention on our part.
Read more →