May 132010
 

While I wasn’t explicitly tagged for participation in this meme, Wendy Pastrick (blog | twitter) was kind enough to tag everyone else who hasn’t been tagged already.  Being in need of a first topic for a blog post, this seemed like a good one to start with!

Since a lot of things have already been spoken for, here’s 3 things I particularly hate accompanied by 2 favorites from others.  If someone else has already mentioned my 3, my apologies for apparently not reading your blog!

1.  View Dependencies
Once I discovered this “feature” I quickly learned that it wasn’t reliable, so why include it in the first place?  I say either fix it or get rid of it altogether.  I always got along just fine by querying system tables or system views.  Red Gate SQL Search is also particularly helpful.

2. XML Indexes
I have nothing against XML, and I fully understand that there will always need to import/export XML to/from a database.  What I really hate though is the idea of storing data in the database as XML.  The ability to create indexes on XML data types only encourages this behavior, so it really wouldn’t break my heart to see it go.

3. Indexed Views
I understand the reasons for having indexed views and believe they serve a purpose, but I’ve always thought that the list of restrictions on them was rather comical.  If you’re going to take that much functionality away, why bother including it at all?

4. RECOVERY being the default RESTORE option
This comes from Paul Randal (blog | twitter) and I couldn’t agree more.  Even though I’m well aware you need to specify WITH NORECOVERY, I’ve still managed to bork a restore every now and then and have seen others do the same.   Having to do an extra RESTORE WITH RECOVERY at the end of a restore sequence wouldn’t bother me one bit.

5. Database Diagrams
Courtesy of Brent Ozar (blog | twitter).  Not that I haven’t used these, but I agree with Brent’s statement that it’s an optional feature and really don’t see the point in putting smack at the top of the object explorer tree.

And that’s it!  I don’t believe there’s anyone left to tag at this point, but if you haven’t already written a post about this and want to, go for it!

  3 Responses to “Five Things SQL Server Needs To Drop”

  1. In general I agree with your position on XML in the database, but what if you need to actually store XML, just like you might store images? We have some XML columns because we need to store XML sent to us. We could store them externally to the database, but since we can keep it with its metadata, we do.

  2. Excellent point Karen, and that came to my mind as well. If you’re going to store XML like you’d store an image, then I don’t see why (N)VARCHAR(MAX) couldn’t do the trick. I’m not saying it’s elegant or even a good practice, but it would definitely get the job done in that case.

  3. in SSMS “View Dependencies…” (and sp_depends which is what I think that calls) has been unreliable for a long time. Back at SQL Connections in 2005 I know in one session the speaker asked people to raise their hands if they knew that, and then followed up with if all those people who raised their hands knew because they got burned by it.

    At least in 2008 there are now dmf’s which do work:
    sys.dm_sql_referenced_entities()
    sys.dm_sql_referencing_entities()
    and two dmv’s:
    sys.sql_dependencies
    sys.sql_expression_dependencies
    —-
    re: XML, I don’t have a problem storing xml, nor even storing it as a native type that you can do xpath/xquery over, nor applying a schema for validation. Like many features it just must be tested and deemed appropriate. I’d cringe to see an xml index created to support key lookups via xquery. Keys or common lookup / join conditions should be normalized out as separate fields. Xml indices, of which there are different kinds, I suspect might be useful for people who store truly huge xml and only want to query out specific pieces that you might not know exist ahead of time.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)