Tidbits I Discovered Today…

I’ve figured out a couple of tidbits today that I wanted to share. First and foremost, I’ve discovered a (new to me) option in SSMS to convert tabs to spaces. I had previously seen the “Untabify Selected Lines” option (under Edit –> Advanced), but this only seemed to remove the tabs at the beginning of the line; it would not remove in-line tabs. I’ve now found another option that will remove the tabs throughout the selected code.

Here’s how you can do it:

Disclaimer: I’m using SSMS 2008 and have not confirmed this in other versions of SSMS.

From your toolbar, right-click in the empty gray space, or click on the drop-down arrow. Navigate to Customize:

Click on the Commands tab, then navigate down to Edit –> Convert Tabs to Spaces.
Note: if you’re one of those weirdos who like tabs instead of spaces, you can convert in the other (wrong) direction, too. ;)

Once you have found the option you want, click on it and drag it to your toolbar.

Cool, huh?

Another little thing I ran into today was a mismatch between nullable and non-nullable columns in related tables. I started to perform a manual audit before I said, “There has to be a faster way.” And you know what? There was. Here’s the little script I used:

SELECT c1.name AS [columnName]
    , c1.is_nullable AS [FactInternetSales_isNullable]
    , c2.is_nullable AS [FactResellerSales_isNullable]
/* replace AdventureWorksDW2008R2 with your database */
FROM AdventureWorksDW2008R2.sys.columns AS c1 
JOIN AdventureWorksDW2008R2.sys.columns AS c2
    ON c1.name = c2.name
WHERE c1.object_id = object_id('dbo.FactInternetSales') -- replace with your table
    AND c2.object_id = object_id('dbo.FactResellerSales') -- replace with your table
    AND c1.is_nullable <> c2.is_nullable; -- find discrepancies

This returned the following results:

columnName             FactInternetSales_isNullable FactResellerSales_isNullable
---------------------- ---------------------------- ----------------------------
RevisionNumber         0                            1
OrderQuantity          0                            1
UnitPrice              0                            1
ExtendedAmount         0                            1
UnitPriceDiscountPct   0                            1
DiscountAmount         0                            1
ProductStandardCost    0                            1
TotalProductCost       0                            1
SalesAmount            0                            1
TaxAmt                 0                            1
Freight                0                            1
(11 row(s) affected)

This script isn’t as polished as my usual, but it’s really just a quick-and-dirty way of checking for mismatches in NULL properties.

Okay, that’s all I have for now, my SQL friends. Oh, and if you know any other cool little tidbits, please feel free to share with me. :)

Back from Vacation!

I’m back from Europe! We had a fantastic time, and saw some incredible sights, but I must say I am glad to be back home. :)

A couple of quick items to mention. First, we now have an official PASS Chapter for the Cedar Rapids area, called I380 Corridor PASS! A special thanks to the folks at PASS for all of their help. If you’re in the Eastern Iowa region and interested in attending or volunteering, please e-mail me at michelle @ sqlfool dot com.

Also, I’ve received a couple of comments about my latest codebox plugin. It appears that in an attempt to fix one issue, I’ve caused another. I plan to work on a solution this weekend, but in the mean-time, if you try to copy code from the codeboxes, you’ll need to do a find/replace on the single quotation marks. I apologize for the hassle and hope to have this resolved soon.

Finally, the very knowledgeable and prolific Mr. Denny wrote a post regarding Wordle and challenged me to post the results of my blog. I’m just now getting caught up from vacation, so I’m a little late in responding, but here it is:



Pretty neat, huh? Thanks, Mr. Denny, for sharing! And to keep the fun going, I’m tagging Brent Ozar and Jonathan Kehayias (The Rambling DBA).

The codebox plugin issue should now be resolved. Please let me know if you have any issues with it.

SQL Quiz – DBA Mistakes

Yesterday, Jason Massie called me out to answer the SQL Quiz begun by Chris Shaw.  The goal is to share a mistake you’ve made and what you’ve learned from it; hopefully, others will learn vicariously through you and avoid the same mistakes.

I didn’t have to think too hard about this one. I’ve made some small mistakes in my time as DBA, but only one really big one.  You may have noticed my fondness for partitioning. My first partitioning project was to transition some very large existing tables to a new partitioning scheme. The plan was to stage the new partitioned schema and the newly partitioned data in the staging database, then perform a database rename.  To make things easier on myself, the script I was using in DEV and TEST first dropped the tables and then rebuilt them. (I’m sure by now you know where this is going…)

One morning, while still in the midst of development, I sat down at my desk, opened up the script, and pressed F5.  My first clue that I was in the wrong environment was the errors about dropping replicated tables.

Ultimately, the failure to drop replicated tables was what prevented this from being a total disaster.  Nonetheless, several small look-up tables and one really large table (>1bil rows) were gone, all in the matter of 2 seconds.

What did I learn?
1) Never skip my morning coffee. Never.
2) I now always prefix my DEV and TEST scripts with something like this:
[cc lang="tsql"]
Use databaseName;

If @@ServerName Not In (‘DEVSERVER’, ‘TESTSERVER’)
RaisError(‘Sorry, this cannot be executed here!’, 15, 1);
/* Execute Code */

I’m calling on Jeff Belina, SQL Developer Extraordinaire. He doesn’t have a blog but that doesn’t mean he can’t post. ;)

P.S. Thanks for the plug, Jason!