SQL Fool Happenings

It’s not plagiarism if I copy myself
I’m now a content contributor for the SQLServerPedia wiki!  So you may now begin to see some of my content on the wiki as well. If you’ve not heard of SQLServerPedia, you should check it out and consider contributing.

It’s like a party, but for geeks
I’m trying to start a PASS Chapter for the Eastern Iowa region. So if you’re in Cedar Rapids, Iowa City, or Waterloo, and are interested in attending or assisting with PASS, e-mail me at michelle @ sqlfool.com for more information.

I didn’t realize people still used IE
Thanks to Peter for informing me of the image distortion issue. Apparently Internet Explorer
is inferior to doesn’t handle image resizing as well as FireFox. The only solution I’ve found is to make 2 versions of each image: a smaller thumbnail and a larger “pop-up” image. I’ll work on updating past posts this week.

The more people that follow me, the better I feel about myself
Jason Massie rocked my world with his post, SQL Twitter n00bs #SQLPASS. I’ve now been using it for a couple of weeks and have found a lot of benefit in it. It helps that I try to only follow SQL or other techy, non-spamming people. If you’re new to Twitter, check out Brent Ozar‘s Twitter 101, which really helped shed some light on some of Twitter’s idiosyncrasies. Oh, and if you’d like to follow me on Twitter, you can find me at http://twitter.com/sqlfool.

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!

Scientific Notation with IsNumeric()

Be careful when using the built-in IsNumeric() function. When presented with a character string formatted as “number+D+number” or “number+E+number”, the function will return a value of 1 (true). This is because SQL Server evaluates [number][d,e][number] as scientific notation.

For example,

[cc lang="tsql"]
Declare @dNotation char(10)
, @eNotation char(10)
, @standardText char(5);

Select @dNotation = ’1d1′
, @eNotation = ’1e1′
, @standardText = ‘abcde’;

Select IsNumeric(@dNotation) As ‘dNotation’
, IsNumeric(@eNotation) As ‘eNotation’
, IsNumeric(@standardText) As ‘standardText’;

… will return:

dNotation   eNotation   standardText
1           1            0

I’ve seen a couple of different ways to approach this, and each have their merits. The most easy and elegant solution I’ve seen came from Dave C. — PatIndex(‘%[^0123456789]%’, columnName):

[cc lang="tsql"]
Select PatIndex(‘%[^0123456789]%’, @dNotation)
, PatIndex(‘%[^0123456789]%’, @eNotation)
, PatIndex(‘%[^0123456789]%’, @standardText);

I’ve also seen Replace() and user-defined functions used with great effect.

Thanks to Dave C. for sharing his solution! :)