Aaron Bertrand

Bad habits to kick : relying on undocumented behavior

2010-02-09T03:01:00Z

In my last post in this series , I talked about the common habit of creating an IDENTITY column on every single table. Today I want to talk about a more broad concept: relying on undocumented (and therefore probably undefined, and certainly far from guaranteed) behavior and objects. ORDER BY in a view This is probably the most infamous of all SQL Server undocumented behaviors. In SQL Server 2000, users learned to create views with a built-in ordering, such as follows: CREATE VIEW dbo.MyView AS SELECT...(read more)

Bad habits to kick : putting an IDENTITY column on every table

2010-02-08T05:33:00Z

Back in October, I started a series of blog posts called " Bad Habits to Kick ," and thought I would revive the theme. I've worked with developers that dabble in SQL, and they tend to have a few common traits. I'm not sure where they come from, but one that I find rather distracting is the tendency to place an IDENTITY column on every single table. Usually this is done because this is an "easy" way to add a column to the table that allows you to identify a single row. Now don't get me wrong, I am...(read more)

Injection is not always about SQL

2010-02-07T18:38:00Z

I think anybody even remotely involved with databases these days has seen the xkcd comic about Bobby Tables : http://xkcd.com/327/ Basically, the comic warns against SQL injection, and reminds you to sanitize your database inputs rather than blindly append incoming data to queries. There are more elaborate discussions about this concept in these articles on MSDN, and of course your favorite search engine will have many results as well: Books Online : SQL Injection MSDN Mag : Stop SQL Injection Attacks...(read more)

Connect Digest : 2010-02-06

2010-02-06T23:32:00Z

Upgrading a database with read-only filegroups Earlier today, I complained that I should be able to upgrade a database with read-only filegroups. In this case an upgrade from, say, 2000 to 2005 is blocked because the engine can't update the system schema / structure. Since I am trying to protect the data, not the system schema, I think it should be okay that the engine ignores the read-only flag during the upgrade process. The workaround is rather cumbersome : get exclusive access to the original...(read more)

When bad error messages happen to good people

2010-02-04T15:27:00Z

Over the years I have largely been amused by the variance in error messages that come out of SQL Server. Some are very verbose and some even border on provide too much information, but the ones that irk me are the ones that leave you scratching your head. I wanted to point out a few of these, and ask if you have any misleading or unhelpful error messages that you see a lot? Msg 8152, Level 16, State 14, Line 5 String or binary data would be truncated. What string or binary data? Could you be a bit...(read more)

Your laptop may be ready for SSDs, but are your SQL Servers?

2010-02-01T21:16:00Z

Brent Ozar ( blog | twitter ) recently made some comments about the FusionIO SSD drives . Basically, he was able to break three drives in a row - simply by doing load testing against them ( using SQLIO ). The symptom is simple: the drives go offline and disappear from the O/S, and need to be physically pulled from the machines. Kind of scary. I am not trying to be Debbie Downer here ... SSDs sound great, and my next MacBook will definitely come with one. I am sure they are not far from production...(read more)

Management Studio Tips & Tricks, version 1.0

2010-01-30T22:00:00Z

Today I presented "Management Studio Tips & Tricks" at SQL Saturday #34 in Waltham, MA. I had an audience of close to 100 (my biggest talk yet!), and unless they were stroking my ego, I achieved my primary goal for the session: to make sure that every single person in the room learned at least one new thing about SSMS. I learned some things today too: I am still horrible at repeating audience questions before answering them. I know before and after delivering a presentation that it's exactly...(read more)

Sometimes it's the small things : match column names in subqueries

2010-01-27T17:12:00Z

The behavior of column matching in subqueries is a little peculiar, to say the least. If you've been bitten by this behavior once, you're unlikely to have been bitten a second time, but for some of us it just takes a while to sink in. This morning I wasted a good five minutes "troubleshooting" a query that wasn't working out the way I thought it should. I have a core table called Users (simplified for brevity): CREATE TABLE dbo.Users ( UserID INT PRIMARY KEY , -- ... other columns ... ); Against...(read more)

SQLFool has updated her index maintenance scripts and needs testers!

2010-01-26T21:47:00Z

Michelle Ufford ( @SQLFool ) has just announced some shiny new updates to her popular index maintenance scripts. Read about them and contact her if you're interested in beta testing: http://sqlfool.com/2010/01/index-defrag-script-updates-beta-testers-needed/ Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!...(read more)

Decrypting : A question of morals, ethics, or both?

2010-01-25T03:33:00Z

In essence, encryption of code within the engine is more like obfuscation than anything else. Unlike passwords and other sensitive data, the engine has to be able to read the code, so there has to be a way to reverse the so-called "encryption." While the engine inherently has the ability to do this internally, there is no direct way for us to do it ourselves out of the box (so if you play with the WITH ENCRYPTION option, be warned; you need to ensure you keep a copy of your module code in source...(read more)

Speaking at SQLSaturday #34 in Boston, 2010-01-30

2010-01-24T21:56:00Z

Just a quick reminder that I will be presenting a Management Studio Tips & Tricks talk at SQLSaturday #34, this Saturday, January 30th at the Microsoft offices in Waltham, Massachusetts. I go on at 9:30 AM. There are plenty of other great speakers, lunch will be provided, and best of all, the event is free. There are still openings so if you are going to be in the area and haven't registered, now is the time! You can find out more about the event, review the schedule, and register at the SQL...(read more)

Connect Digest : 2010-01-22

2010-01-22T19:47:00Z

Give us easier to read execution plans Michelle Ufford ( @SQLFool ) recently asked for help pinpointing the most expensive node(s) in a complicated execution plan. Mladen Prajdic ( @MladenPrajdic ) has a useful workaround; he coded up a quick query to parse the showplan XML and order results by cost descending. The Connect item that would make this workaround unnecessary was filed by "Ewan1": #477390 : Rank cost of graphical execution plan components in SSMS Give us more SARG intelligence in the...(read more)

Mis-steps in the publication of Cumulative Updates

2010-01-20T17:31:00Z

It used to be very difficult to obtain hotfixes for SQL Server (sometimes even to learn about their existence), and they were often unsupported. They have made extremely great strides in this area, and in general, I find the new procedure much more convenient : just go to the KB article, select the fix(es) you want, and you get an almost immediate e-mail with download links and brief instructions. No longer do I have to raise an issue with customer support and prove to them that I am both affected...(read more)

"What three events brought you here?"

2010-01-19T21:03:00Z

I was tagged by Tim Mitchell ( twitter ) in a recent meme launched by Paul Randal ( twitter ), entitled, "What three events brought you here?" Well, I've lied told stories about this in the past. If you've read my previous posts about my career (in particular, this one ), you'll know that I'm Canadian, gave up NHL dreams early, took Economics in College, started earning a living with computers in desktop publishing and later web design, and soon after graduating moved to Rhode Island (where I still...(read more)

New Cumulative Updates for SQL Server 2008 RTM & SP1

2010-01-19T14:23:00Z

Late last night, the SQL Server Release Services team announced new cumulative updates for both SQL Server 2008 RTM and SQL Server 2008 SP1. There are too many fixes to mention, but I once again notice the trend that the "newer" branch (SP1) has more fixes than the RTM branch. I suppose they are serious about their commitment to end support for SQL Server 2008 RTM on April 13th of this year . If you haven't already planned upgrading your instances to SP1, it is definitely time to start thinking about...(read more)