May13

My 5 SQL Server Changes

Tags: , | Categories: Database Related
Permalink | Comments (2) | Post FeedComment feed by author Jason Short

This is an interesting thought experiment posted on Paul Randel’s blog – What 5 things should SQL Server get rid of?

I like it, the idea that features should be removed. I love SQL Server, it is awesome. But SQL Server is also HUGE, some of the features are legacy dating all the back when Sybase owned the product.  I mean do we really still need all that baggage?  I somehow seriously doubt that those people using those legacy features are upgrading their database to SQL 2008 R2.

I decided to do my own take on the list…

GUIDS on a Clustered Index

Yep – STOP THAT. Well known problem. It should not be allowed to happen without a LOT of knowledge from the user.  I mean come on, I have to go check 3 boxes to turn off the silly warning that updating this schema will alter other schemas too, but I can set a clustered GUID without a whisper from the system.

Yes, there are now Sequential Guids to solve that issue.  Really, I am not making that up.  I understand the intent, but shouldn’t we just accept that some designs don’t work for all situations?

Drop GO

That’s right.  It is not actually supported in the engine anyway.  It is only used by the tools external to the engine like SSMS.  But I see developers all the time try to write SQL to the server scattered with GO blocks in it thinking it will somehow do something for them.

Add a batch concept if that is what you think people need, don’t let them use GO.

Generate Scripts should show best practices

Let’s face it.  Most people using SQL Server generate Script to a new query window could not have written it from scratch.  They are relying on this auto generated script to tell them what to do.  So stop showing a drop all from sys.objects just to see if the object exists.  Really, there is a TRY CATCH concept in SQL Server.  TRY to add it, if it fails you know it is there.

Actually support ADO.net in the tools

Did you know you cannot actually import data into SQL Server from a fully compliant Ado.net provider?  Yep, it’s true.  The tools are all hardcoded to only Microsoft products.  As soon as you select a third party tool you are limited to grabbing the rows from a single table.  Good luck on all those foreign key problems you are going to have.

Come on, this is Microsoft.  What is the purpose of having a standard if you don’t implement it? 

Standardize T-SQL

I don’t really care what standard you use (honest).  But make sure you follow it everywhere.  Not one version for SQL Server without this service pak, and another for the Enterprise version, and yet another one for SQL CE.

Put some standards into place, force people to follow them.  You don’t really need 12 ways to do the same thing just because Sybase implemented that one syntax in the dark ages.

While you are it enforce some of your own standards today.  Stop letting people turn on compatibility mode back to 1990 in the middle of a script, then change it back.  I know SQL Server 2008 R2 has addressed some of this, but there needs to be more.  Things like semicolon should NOT be optional.  Either make them part of the standard or not.

Summary

I guess most of my pain points were around the tools side of the database, not the actual engine.  Hmmm. 

I am sure if I thought about it could come up with 5 just for the engine, and 5 T-SQL syntax changes, and 5 for the tools… But that might be a bit overboard, sorry.

What about you?  What’s your list?

Comments (2) -

14/05/2010 12:54 #

Tim

What's wrong with a clustered index on a GUID field?  Suppose you have a mostly static table that you want to do a lot of lookups by GUID on?  A clustered index scan is faster than a non-clustered index scan plus table-lookups.  I know it is pretty dumb for MANY cases to have a clustered index on a GUID field, but why take it away from engineers who have a good use for it?  Let's face it, there are a lot of people out there designing database schemas and don't know the difference between a clustered and a non-clustered index.

Since you asked, MY wish list for SQL Server:
1. More options for formatting when casting values to a string-- like the string formatting .net provides.  The analog of String.Format would be slick, but I'd settle for the formatting options of the various .ToString() formatting strings.

2. Wow about the ability to cast a string to a numeric and provide a default if conversion fails?  CASE WHEN ISNUMERIC(@Test) = 1 THEN CAST(@Test as integer) ELSE 0 END works, unless @Test happens to be '.' or something else that ISNUMERIC says is numeric but CAST disagrees with (in which case your query errors out).  Why not a CAST(@Test as integer, 0)?  I know TRY...CATCH works if it is a proc, but what about a select statement with lots of rows?  

3. I would really like to see the ability to use MIN() and MAX() aggregate functions in an indexed view, even if updates and deletes to the source table would be costly for the engine-- what if I don't intend to delete rows or update the values used in the MIN/MAX function?

4. My personal pet-peeve, how about giving us back the ability to backup and restore to/from a named pipe?  I know it is a security risk if the programmer doesn't set ACLs on the named pipe correctly-- but taking away abilities just because there are so many developers who don't understand security out there is really annoying.


Tim

14/05/2010 02:51 #

js_vistadb

Hi Tim,

"What's wrong with a clustered index on a GUID field"

Every insert, every update, anything causes the index to be totally reordered on disk.  So you add a new record, WHAM - the entire database has to reorder the rows on disk.  That is why the sequential guid was created - to put a bandaid on a bad idea in the first place.

If you want a GUID to be the PK, AND you want it clustered, AND you know the data is 99.9% of the time static... Still don't do it.  Put another index as the clustered PK, and just setup an index on that GUID.  There is probably a 0.01% use case when they make great sense.  Is it really worth having it when 99.9% of people using it are getting it wrong?  Sometimes removing options also reduces confusion.

2 - Conversion in general should be radically changed.  I forgot about that one for my list.  The fact that SOME types can convert a blank string to a default and others blow up is just bad design.  Some types can take null, others can't.  It is not like they have value and reference types...  That matrix on MSDN showing what can be converted to what and how is super scary.  Talk about a classic hard coded list of conversion nightmare.

4 - Named pipe backup?  Ouch.  No, can't agree with you there.  They should expose those endpoints through oData and other ways that are secured by the system admin and may be consumed by more than one client type.

Thanks for your list!

js_vistadb

Comments are closed