Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

T-SQL

Transact-SQL programming tips and tricks
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. Frequently these changes affect more than one database in your environment. The development, test, and eventually even the production databases must have their definitions updated to reflect the newly implemented changes. For cases like this, I generally prefer to script out the necessary changes rather than using the Management Studio tools. Scripting reduces the likelihood of an unintentionally errant...

posted @ Thursday, January 15, 2009 8:59 AM | Feedback (23) | Filed Under [ T-SQL ]

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. According to SQL Server Books Online, we can disable constraints using the ALTER TABLE statement.Here's an excerpt from SQL Server Books Online that describes it. { CHECK | NOCHECK } CONSTRAINT     Specifies that constraint_name is enabled or disabled. This option can only be used with FOREIGN KEY and CHECK constraints. When NOCHECK is specified, the constraint is disabled and future inserts or updates...

posted @ Wednesday, October 01, 2008 10:57 AM | Feedback (20) | Filed Under [ T-SQL ]

Using Covering Indexes to Improve Query Performance

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. Cheers! Joe

posted @ Tuesday, September 30, 2008 8:14 AM | Feedback (1) | Filed Under [ T-SQL SQL Server ]

The undocumented sp_MSforeachdb procedure

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. The examples I gave were executing DBCC CHECKTABLE and EXECUTE sp_spaceused() for each table in a given database. But there's another undocumented stored procedure designed for iteration. This one allows us to iterate through each database in a SQL Server instance. It's called sp_MSforeachdb. It's usage is very similar to that of sp_MSforeachtable; simply execute the stored procedure, passing it the command that you'd like to execute as...

posted @ Wednesday, August 27, 2008 5:28 PM | Feedback (14) | Filed Under [ T-SQL SQL Server ]

DevLink 2008 Presentation

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. As promised, here is a link to the presentation slide deck and demo scripts that I used today. In addition to the formal feedback that you may have provided to DevLink, I'd love to hear from you. So feel free to either drop me an email directly, or to add comments to the section below. Cheers! Joe

posted @ Saturday, August 23, 2008 2:58 PM | Feedback (0) | Filed Under [ T-SQL Current Events Community ]

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. I'd like to share a slightly modified version of it with you; you can read his original postings here. Let's consider a very simple table in tempdb. Use SQL Server Management Studio to execute the following query: USE tempdb ; GO --create a table...

posted @ Thursday, August 14, 2008 7:59 AM | Feedback (11) | Filed Under [ T-SQL SQL Server ]

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. However, since few of us are responsible for just one database, it's sometimes difficult to gain, much less maintain, that level of familiarity with our data. Fortunately for us, however, we have the metadata available to us. We can use...

posted @ Friday, June 27, 2008 8:57 AM | Feedback (11) | Filed Under [ T-SQL ]

The 33 languages of SQL Server

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.messages table - the table where SQL Server messages are stored. I blogged about this system table a few months ago. Afterward I started playing around some more with the sys.messages table. I started with the following basic query.   The first thing to notice is that each message_id has one or more languages associated with it. Message_id 21, for example, has a row for language_id 1033, 1031, 1036,...

posted @ Friday, May 30, 2008 3:54 PM | Feedback (6) | Filed Under [ T-SQL SQL Server ]

SQLTeach - Writing better queries presentation

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. To everyone who attended the session - thanks for spending a part of your day with me. I hope it was worth your while. Please feel free to email me if you have any additional questions or comments. And please remember to login to your SQLTeach account and submit your session evals. Cheers! Joe

posted @ Thursday, May 15, 2008 9:37 AM | Feedback (9) | Filed Under [ T-SQL Current Events Community ]

Finding Notification Services Instances

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. I'll probably devote a blog to it's abrupt deprecation at some point in the future. In the meantime, if you're preparing to upgrade to SQL Server 2008, you should verify that you don't have any rogue SSNS instances running on any of your servers. I hope your environment is more controlled than that, but I've seen more...

posted @ Tuesday, May 06, 2008 3:31 PM | Feedback (0) | Filed Under [ T-SQL Notification Services ]

Full T-SQL Archive

Powered by:
Powered By Subtext Powered By ASP.NET