The End of an Era – SQLFool scripts are now open-sourced

Hello, dear readers. It’s been a while.

For those who haven’t yet heard the news, I have switched to the dark side roles. I’m still kicking ass at GoDaddy and I’m still technical, but I now lead a team of big data engineers. As you can imagine, this means that I spend the majority of my time with Hadoop and its related ecosystem (Pig, Hive, Spark, etc.). This is very different than the performance-focused SQL Server role I had when I first created this blog. For those of you who make your living primarily or solely with SQL Server, please rest assured… SQL Server is still awesome, is still relevant, and still has a place in most enterprise data platform strategies. In fact, I’m presently working on egress automation of our Hadoop data warehouse to SQL Server for serving BI reports. Unfortunately, my change in technology focus means that my SQL Server blogging has largely come to an end, and with it, new posts on this blog. But while this blog may end, my blogging activities do not. For those interested, I have launched a new blog at Hadoopsie.com that chronicles my adventures in a non-Microsoft world with Hadoop, open-source, and development on a Mac. :)

But back to the reason for this post. Recently, I saw a trackback comment from SQLBlimp that listed my index defrag script as #3 on its SQL Server Helpfulness Hall of Fame. This filled me with mixed emotions; while it made me happy that the script has been so helpful to so many people, I couldn’t help but feel sad because I knew I would no longer be keeping it updated. I have spent countless hours on this script, which has won numerous awards and has been mentioned in blogs, presentations, online publications, & books. It’s also still extremely popular; I stopped paying attention to its metrics when it broke 100,000 downloads 3 years ago. Similarly, I’ve been surprised to observe that sqlfool.com, which broke 1 million visits in April 2014, now receives more web traffic today than it ever did at the height of my blogging activities.

For this reason, I have made two decisions. First, I will keep this blog online so that people can continue to stumble across blog posts that, while a couple of years old, are still largely relevant.

Secondly, I have decided to open source all of my SQL Server procedures & scripts on GitHub. I haven’t yet had a chance to upload all of my scripts, but I started with the most popular ones (based on traffic stats). These scripts are available in a single repository to make downloads easy. And, if you are so inclined, feel free to contribute back to the repo. For the index defrag script in particular, I have some automated regression tests that I can run against any pull requests to aid in merge approvals.

One final comment. This may be the end of my SQLFool era, but I hope to maintain the friendships I’ve made along this incredible journey. I wholeheartedly believe that the SQL Server community is the best damn technical community in the world. Thank you ALL for your camaraderie, friendship, help, support, and awesomeness. Keep kickin’ ass, my friends. :)

Love,

Michelle

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:

Wordle.com

Wordle.com

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).

Update:
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;
Go

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

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!