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 ]

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. SELECT ORDINAL_POSITION ,COLUMN_NAME ,DATA_TYPE ,CHARACTER_MAXIMUM_LENGTH ,IS_NULLABLE ,COLUMN_DEFAULTFROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Product' ORDER BY ORDINAL_POSITION ASC; Do you have a different way of doing this that you'd like to share? If so, feel free to leave a comment,...

posted @ Sunday, April 27, 2008 9:05 AM | Feedback (24) | Filed Under [ T-SQL ]

Tips & tricks for writing better queries presentation

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. Some of your questions and comments at the end gave some some great ideas for future presentations. Thanks! And thanks to Quest and RHT for sponsoring our meeting meeting today. If your in the Nashville areas, I'd encourage you to come on out for our next meeting, most likely sometime in May. As promised, here's a link to the presentation materials, both the PowerPoint presentation and the demonstration code. Cheers!...

posted @ Friday, April 04, 2008 2:30 PM | Feedback (5) | Filed Under [ T-SQL SQL Server Current Events ]

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.00.3054.00 on an Intel X86 processor. It may also appear that I've installed SQL Server SP2, however that is rather misleading. The "Service Pack 2" text in the above image refers to the operating system, not the SQL Server system. To determine SQL Server's service pack level, use the built-in SERVERPROPERTY function with appropriate parameters -...

posted @ Tuesday, April 01, 2008 5:27 PM | Feedback (8) | Filed Under [ T-SQL SQL Server ]

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. With the house so quiet, I didn't realize I was whispering until I heard it mixed with Buck's projecting and charismatic dialog. Here's a link to the podcast. I'd love to hear what you think and if you'd like to hear more of these. If you're not familiar with Buck Woody's work in the SQL Server community, I would encourage you...

posted @ Tuesday, March 25, 2008 6:34 AM | Feedback (3) | Filed Under [ T-SQL SQL Server ]

Does order matter in a JOIN clause?

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?" The comment, submitted through Plaxo Pulse, is not available outside the Pulse community so I've included it below since it's the genesis of today's post. "What if you were using join statements? Does it matter what order your columns are used in the ON statement? ~ Ariel M." There are several derivatives of the original question: Does the order of tables referenced in the ON clause of...

posted @ Friday, February 29, 2008 9:15 AM | Feedback (21) | Filed Under [ T-SQL SQL Server ]

Does the order of criteria the WHERE clause matter?

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. If the search criteria omits the first column and includes only subsequent columns, the index is of no use to SQL Server for resolving that query. To see the proof, refer to this post. The example I used in the prior post included the following query as a basis for the discussion. SELECT *...

posted @ Friday, February 22, 2008 10:37 AM | Feedback (11) | Filed Under [ T-SQL SQL Server ]

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. The index doesn't actually contain the information on DBCC INPUTBUFFER, it has a pointer to the page in the book where the command is described. So, what do you do? You turn to that page and read all about how DBCC INPUTBUFFER may be used. This...

posted @ Wednesday, February 13, 2008 10:43 AM | Feedback (24) | Filed Under [ T-SQL SQL Server ]

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. So, 10 - 20 million rows of data will likely suffice for this demo. The question is: how to create all of that data? Obviously, a simple INSERT...VALUES statement is good for creating up to a few dozen rows of data, but 10 - 20 million rows of data is out of the question for such a simple technique. Fortunately, SQL Server has...

posted @ Wednesday, February 06, 2008 10:14 AM | Feedback (16) | Filed Under [ T-SQL SQL Server ]

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. In this post, I'd like to combine the two topics and show how the sp_MSforeachtable stored procedure command may be used in conjunction with the DBCC CLEANTABLE command to reclaim space from all tables after one or more columns were...

posted @ Friday, January 25, 2008 3:13 PM | Feedback (3) | Filed Under [ T-SQL SQL Server ]

Reclaiming unused space from a table

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. However under certain circumstances, SQL Server does not automatically reclaim space once used. If a table definition is altered to drop one or more variable length columns, the space consumed by those columns is not immediately made available for reuse by SQL Server. But that's to say that the space is forever lost. We can employee a DBCC utility reclaim the newly-freed space. To illustrate this behavior...

posted @ Monday, January 14, 2008 5:23 PM | Feedback (12) | Filed Under [ T-SQL SQL Server ]

Do I need to compact my SQL Server database?

A question that occasionally comes up from people who have made the switch to Microsoft SQL Server from Microsoft Access is "If I delete a bunch of rows, do I need to compact my SQL Server database?" It's been many years since I've used Access, but I still remember the reason for their concern. Microsoft Access would continually add rows to the end of the table. If some, or even all, of the rows were deleted from the table, Microsoft Access wouldn't reuse the space. It just kept adding rows to what was the very end of the table and...

posted @ Thursday, January 10, 2008 7:46 AM | Feedback (4) | Filed Under [ T-SQL SQL Server ]

SQL Server error messages

  If you've worked with Microsoft SQL Server in a production environment for any length of time, you've undoubtedly been exposed to a number of different error messages. For example, many of us are probably familiar, too familiar perhaps, with error 1205 - the dreaded "you've been chosen as the victim of a deadlock" message. Or maybe you are more of a developer than a DBA. So you may be more familiar with error messages like 2714 - "there is already an object named this in the database." There may be many messages that we feel that we know by heart....

posted @ Friday, December 07, 2007 4:52 PM | Feedback (6) | Filed Under [ T-SQL SQL Server ]

Copying and pasting rectangular blocks of code in Visual Studio and Management Studio

One of the things I really like about teaching technical classes is getting to know and talk with students, learning of the challenges they face and how they've addressed them. Frequently, I can learn from their experiences, too. So, it works out well for both student and instructor. In a custom Visual Studio/SQL Server class last week, I made a passing comment about Visual Studio being a very good text editor, but that it lacked one feature that I really like in other editors - block copy and paste. I lamented that the Visual Studio editor doesn't allow you to select a rectangular...

posted @ Tuesday, November 06, 2007 7:45 AM | Feedback (17) | Filed Under [ C# T-SQL Other Technologies SQL Server ]

The undocumented sp_MSforeachtable procedure

As database administrators or developers, sometimes we need to perform an action on all of the tables within a database. For example, we may want to disable check constraints for all tables prior to loading a bunch of data; or perhaps we need to run DBCC CHECKTABLE for every table in our database to ensure the integrity of all pages and structures that make up the tables. We could, of course, create a script that uses a cursor to loop through all of the tables in the database to do our bidding through dynamic SQL. Yuck! Fortunately, there's a better way. It's the...

posted @ Tuesday, October 23, 2007 11:21 AM | Feedback (21) | Filed Under [ T-SQL SQL Server ]

When was a SQL Server table or view last altered?

In a recent post, I demonstrated how the sys.procedures catalog view could be used to determine the date and time that a stored procedure was last modified in SQL Server 2005.  A similar technique can be used to determine the last time a user table was altered. The following query illustrates this technique. SELECT [name] ,create_date ,modify_dateFROM sys.tables The modify_date column is updated whenever a column is added or altered for the table. It's also updated if the clustered...

posted @ Thursday, October 11, 2007 7:58 AM | Feedback (18) | Filed Under [ T-SQL SQL Server ]

Enabling and disabling logins in SQL Server 2005

In SQL Server, much like other services with users, login accounts can be enabled or disabled. An enabled login can be authenticated and allowed access to database resources. A disabled login is not allowed to establish a connection to the SQL Server instance. For example, let's create a login called JoeUser. CREATE LOGIN JoeUser WITH PASSWORD = 'P@$$w0rd!' ,DEFAULT_DATABASE = AdventureWorks USE AdventureWorks;CREATE USER JoeUser In the first statement, the JoeUser login was created with a fairly complex password and a default database of AdventureWorks. Afterward, we switch to the AdventureWorks database and...

posted @ Wednesday, October 10, 2007 7:04 AM | Feedback (12) | Filed Under [ T-SQL SQL Server ]

How to tell when the SQL Server service was started

Ever wondered if it is possible to programmatically determine when the SQL Server service for an instance was last started?  It would be nice to have a built-in function that explicitly reports the time of the last service start, but one doesn't exist. But we can still estimate the time of the service start to within a few seconds, which should be good enough for anything less than the exact calculations required for most strict Service Level Agreement (SLA). Since we know that tempdb is recreated each time the SQL Server service starts, we can look to the creation date of that system database to...

posted @ Wednesday, October 03, 2007 6:21 AM | Feedback (13) | Filed Under [ T-SQL SQL Server ]

When were the statistics for a table last updated?

When SQL Server receives a new query, it attempts determine the best possible plan for resolving that query. It considers a number of different factors as it analyzes the query and maps out a way in which to retrieve the information requested. Whether or not the query optimizer deems an index to be useful in resolving a query largely depends on the information contained in the statistics for that index. If the statistics are outdated and do not accurately represent the distribution of values within the table, the query optimizer may not produce an optimal plan for resolving the query. Misleading statistics may result in...

posted @ Friday, August 31, 2007 6:37 PM | Feedback (15) | Filed Under [ T-SQL SQL Server ]

"Nothing has changed" - Determining when a procedure has been altered

As a consultant, I regularly need to determine when a stored procedure was last altered. Without having implemented a series of DDL triggers, how can this be accomplished? In Microsoft SQL Server, you can easily retrieve this information from the sys.procedures catalog view. The following query demonstrates this. SELECT [name] ,modify_date ,create_date ,*FROM sys.procedures   Of course you can take it a step further by limiting the results to a period of time where you know that no changes should have been made. For example, the following...

posted @ Wednesday, August 29, 2007 1:30 AM | Feedback (6) | Filed Under [ T-SQL SQL Server ]

Come out, come out, wherever you are: Finding SSNS instances using T-SQL

As database professionals, we may responsible for dozens, if not scores, of SQL Servers throughout our department or enterprise. Now that Microsoft has announced that Notification Services will not ship as part of the SQL Server 2008 product, how can you readily identify which of the servers in your charge have SSNS instances installed? Fortunately for us it's rather easy. SSNS 2005 registers each installed instance in the msdb system database. The following query returns a list of every SSNS instance for the SQL Server instance. SELECT         * FROM         msdb.NS90.NSInstanceInfo To retrieve a list of all SSNS applications for the...

posted @ Thursday, August 23, 2007 8:59 AM | Feedback (0) | Filed Under [ T-SQL Notification Services SQL Server ]

Powered by:
Powered By Subtext Powered By ASP.NET