As I have been walking around Disney World this week, my mind starts to wander to matters of database design. Sad, perhaps, but I will guess that most people who read this blog do the same much the same thing with whatever technology they are good at when they are relaxing also.  It also may actually have helped me come up with an example for my next book (especially if I can double the size of the book!)

Examples are the hardest of all parts of writing for me. Thinking of an example that covers a lot of situations as is really hard, but I think that a theme park might actually be the answer (if it is and you are a writer, don’t steal my idea or I will steal it right back :).  It seems like it actually covers most if not all of the situations that are needed:

• Good entities (People, places, ideas)
• Rich attributes to describe the entities
• All of the different parts of normalization, including 4nf (show, cast member, location)
• Geography data (to locate all of the different locations)
• Geometry data (finger print biometrics)
• Image data (pictures of attractions, menus, etc)
• Subclasses

And many more. 

But it is the Subclasses that got me thinking. Initially I was thinking that a Location entity would be the top level entity, then attraction, restaurant, shop, etc as subclasses.  But is this really a subclass? Thinking about the AdventureWorks database the other day I realized the reason I dislike that design is the way it has a BusinessEntity entity as a superclass to almost EVERYTHING. A subclassed entity isn’t like a generic object in an object oriented design, but rather it should be more of a specific entity with even more specific information tacked on for specific types.  The point being that you would figure to use the superclass quite often as itself, with the subclass giving you some additional information sometimes, but not as a rule exclusively using the subclass entities.

So how alike is a roller coaster, or a restaurant?  Seems that the only thing these share is a position on the globe. So instead of a subclassed entity, why not specify standalone entities for each and create an independent entity for position.  This would be a geographic point, and would serve as the entrance of sorts to the math of determining how far apart the different locations are, as well as (possibly) to predict which place a person would be likely to go next.  This position is not really a parent to the items, but more of an interface (though technically I don’t think an interface doesn’t specify storage…)

So each table would get a relationship to the position entity that would contain all of the different locations of “stuff” that you have. This is not so different from how address is often implemented, except that an attraction shouldn’t need more than one position. It might have a geometric representation of the area it takes up, but I would think that each location would have one single point on the map that would represent it.  Might this get so detailed as to have multiple items per location?  For example, a very large store having multiple entrance points, each so different as to direct a person in a different way?  Sure, and this might be a matter for the other tables to worry with.  Each position would also need to be able to tell the distance from itself to other positions, with knowledge of the terrain.

Just having an interface would make things easier to use the subclass technique for the things that are common.. Like a queue.  There are different types of queues all around a them park, and several different types. A roller coaster uses several of them itself (a special ticket to get you through fast, a just stand here and wait line, single rider, etc) as does a restaurant (just stand here and wait, and in some cases, reservations, or priority seating).  Each of these queues shares commonality, but at the same time some differences are obvious.  And an actual queue for one ride may use all of the different types.

Clearly this is more to flesh out if I use this example…Just food for thought about subclasses.

As a different example, would it seem natural to have a person entity that was then subclassed into guest and cast member? They do share obvious commonality, and (given the right technology) you would want to track all of their movements. But you probably don’t know that much about guests. Maybe guests aren’t considered people as much as tickets (in the database only) and the ticket is associated with the human characteristics of the guest (height, weight, sex, etc so as to help direct them on their way/predict what they might do? Small kids, fluffy bunny ride?)  Then we might be back to guests and cast members sharing an interface for their location. How alike are location and position?  Probably look alike, but you would want a history of movement if you could track a person’s ever move, right?

The goal of a database is to store the information in a manner that is useful for analysis both to predict future events, as well as to look at how current processes work each day.  Getting everything just right is the dream of every designer. Of course all of this tells me that I have to be careful with this example or it will grow REALLY huge.  Hopefully I won’t need to finish this too soon, but who knows when the next major version of SQL Server is.  Much to consider, there is.

As I am easing back into real life from writing the book, I am in search of easy targets for blogging.  My boss mentioned this blog over on Jeff Atwood's Coding Horror Blog and it got me thinking about commenting.  His advice is to only comment "why" the code works.  I can't quite agree, because the code he claims to be acceptable is:

private double SquareRootApproximation(n) {
  r = n / 2;
  while ( abs( r - (n/r) ) > t ) {
    r = 0.5 * ( r + (n/r) );
  }
  return r;
}
System.out.println( "r = " + SquareRootApproximation(r) );

I mean, it is better than some code I have seen,  but still, I would like a bit more information about why this works.  Maybe the name of the algorithm used, or at least what to do if this fails to provide the expected results.  Admittedly this is probably something that could be easily found, but most algorithms are not.  Comments in my mind should at least lead you to understand the mindset of the programmer.  What would actually improve this code in my mind is to change the variables to full words (though in this case it might not make sense to do this.)

On an extremely different side of things is this article from "Edgewood Solutions Engineers" on mssqltips.com. Their answer is to explain what the code is doing in simple terms, making sure to comment almost everything.  They have a very elaborate header devised, with dependencies, both users of the object and objects it used.  Most of what is said seems a bit like overkill, but their point here "Comment all of the major code blocks of the code and the critical minor points that can be easily overlooked such as a obscure WHERE clause." is a good one.  I generally pepper my code with comments where I think it will be hard to debug for myself later, with a consideration for others, particularly when those others will call me to explain the code.

Which brings me to my commenting philosophy. I personally think you have to comment to the expected lowest common denominator.  Think of the dumbest person who could have the need to read your code who is also qualified to have their job (otherwise you would have to write instructions on every line of code). If the qualified person can figure out what you are doing just by your naming conventions and , then it doesn't need comments. But if that person would look at the code and reasonable figure it out, then there is no need to comment the code.  What this requires is a few things:

• Naming objects - if your procedures, tables, columns, functions all have meaningful names, you won't have to explain what they mean, saving time
• Good design - if the relationship between objects and the cardinality of those relationships is clear, then you don't need to explain that what you are doing is hack due to poor thinking...
• Naming variables - probably the most important thing to avoid the need for comments is naming stuff.  Name variables with words, not single character values (except sometimes i, x, etc will suffice for obvious typical uses)
• Reasonable code formatting - SQL has no real form, so you could write procedures on a single line.  You could.  You could smash your hand with a hammer too.  Neither action would be very good.  (Consider using Red-Gate's SQL Refactor tool if nothing else.)

However, the fact is, for SQL code, the real problem comes in when you start coming up with cool relational methods of solving problems that most moderately qualified people wouldn't get. For example the trick of using a sequence table to break apart a comma delimited list. Couple that with a join and you get some amazingly cool code, but how do you comment it?

For example, say an architect that shouldn't be an architect designs a table with a comma delimited list like this (didn't I mention good design earlier?  I hate having to say this is a hack, but it is an elegant hack...)

--excerpted from Chapter 7 of Pro SQL Server 2008 Relational Database Design and Implementation
CREATE TABLE poorDesign
(
     poorDesignId int,
     badValue varchar(20)
)

INSERT INTO poorDesign --using 2008 syntax
VALUES (1,'1,3,56,7,3,6'),
            (2,'22,3'),
            (3,'1')

You can "normalize" this set using a table of numbers (in my examples named tools.sequence) and a really cool join:

SELECT    poorDesign.poorDesignId as betterDesignId,
          SUBSTRING(',' + poorDesign.badValue + ',',i + 1,
                CHARINDEX(',',',' + poorDesign.badValue + ',',i + 1) - i - 1)
                               as betterScalarValue
FROM     poorDesign
             JOIN tools.sequence
                    on i >= 1
                      AND i < LEN(',' + poorDesign.badValue + ',') - 1
                      AND SUBSTRING(',' + + poorDesign.badValue + ',', i, 1) = ','

But are there enough pixels available on the planet to make that more understandable to most SQL programmers? Even the reasonably qualified?   I mean, I am still kind of amazed at the technique and the fact that it returns the following:

betterDesignId betterScalarValue
-------------- -----------------
             1                 1
             1                 3
             1                56
             1                 7
             1                 3
             1                 6
             2                22
             2                 3
             3                 1

still impresses me.  Frankly I don't know how to comment that code to make it readable.  In a real situation I would settle for a comment before the SELECT that stated:

--Uses a table of numbers to parse the comma delimited list into a SQL acceptable format.
--If you don't understand this code, read this article: http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum

Opinions? What do you use for a comments in your code?  Do you have commenting policies?

Next week is Tech Ed Developers, and I will be there working at the OLTP demo station from:

Tuesday 11:45 – 2:45
Wednesday 2:30 – 6:00
Thursday 11:45 – 2:45
Friday 11:45 – 2:45

All times Eastern Daylight Saving Time and a little bit approximate at this point.  Please stop by and say howdy!  I don't exactly yet know what I will be doing completely, but it will be fun, never the less.

After that I have a few other speaking engagements coming up:

Nashville SQL User's Group - Late Summer - Doing some session in concert with my book release (hopefully doing some special stuff that time too.  Stay tuned for that announcement)
Devlink (www.devlink.net) - August 22, 23 - Doing a double session on Database Design with Paul Nielsen
PASS (www.sqlpass.org) - Nov 19-21 - Doing the same session with Paul, just condensed into a single session

I will be back in the forums soon, and on this blog and my personal website (drsql.org) too as the principal writing for the book will be finished tomorrow, and rewrites in a week or so.  After that, it is a downhill journey to the finish line.

So come by next week and let me know you are reading the blog, book, or whatever. 

(cross-posted to drsql.spaces.live.com)

Ok, so I was looking around for a blurb about plan guides for my anti ad hoc SQL section of my book (ok, maybe not completely anti- but that isn't the point,) when I found this blog: http://geekswithblogs.net/Sreeblog/articles/117576.aspx that basically just lists some new features in 2008.  One that I hadn't seen I feel the need to mention. 

Transact-SQL Error List Window:
SQL Server Management Studio includes an Error List window that displays the syntax and semantic errors generated from the IntelliSense code in the Transact-SQL Query Editor.

If you haven't seen this, it is really kind of neat.  Say you type:

select *
from sys.object
whe re name = 'fred'

You can see the obvious errors, since this is a very small batch, but IntelliSense underlines sys.object and re in the batch.  Go to the View menu and show the Error List, and you will see something like this:

Double-click on the error, it takes you to the error in the query window.  Obviously I have just discovered this, so please comment if you know more about this topic (particularly if it has been helpful to you or not) please chime in.  And thanks to Sreenivas Mogullapalli for the cool post, it had a few other things I didn't remember too (just don't make me have to pronounce your name...I am pretty sure I wouldn't get it right.)

In 2005, rebuilding a table that was a heap (no clustered index) wasn't easy.  You could copy it to a different table, or you could add a clustered index and then drop it.

In 2008, this is a far easier thing to do.  They have added to the ALTER TABLE command a method to rebuild the table, which is the same as rebuilding the clustered index for a clustered table, but for a HEAP, it is the only way to go. 

In the following code sample, I create a heap, load it will "little data", then expand every row to much larger values.  The result is a little bit of fragmentation, but more than that, tons of forwarding pointers (when a row won't fit on the same heap page, it gets moved to a different page, but the pointer to the row does not change in the indexes.)  Rebuilding the heap is now really simple:

create table heapDemo
(  
    value varchar(1000)
)
GO
set nocount on
insert into heapDemo
select 'hi'
go 10000
--Expand the values to 500 times the size they were
update heapDemo
set value = replicate('hi',500)

Now, check the stats of the table (using the index stats dmv, no less)

select index_type_desc, fragment_count, page_count, forwarded_record_count 
from sys.dm_db_index_physical_stats(db_id(),default,default,default,'DETAILED')
where object_id = object_id('heapDemo')

This returns:

index_type_desc       fragment_count       page_count           forwarded_record_count
--------------------- -------------------- -------------------- ----------------------
HEAP                  6                    1443                 9961

Now, you can rebuild the heap with the command:

alter table heapDemo rebuild

Check the values now:

select index_type_desc, fragment_count, page_count, forwarded_record_count
from sys.dm_db_index_physical_stats(db_id(),default,default,default,'DETAILED')
where object_id = object_id('heapDemo')

This shows no that the forwarded_record_count is 0, which is the idea situation for your heap.

Nice new addition!

Tonight, as I was creating my sample database for my chapter on implementing the database, I learned something new, that existed in 2005.  I had always used sp_changedbowner to change the owner of a database, but I was reading in another section about ALTER AUTHORIZATION earlier to change the owner of an object in a schema, so I said to my self "Louis" (I call myself that) "Louis, wonder if ALTER AUTHORIZATION works on other stuff?"

Bizarre and surreal conversations with myself aside, the answer was Yes, you sure could.  The syntax is:

ALTER AUTHORIZATION ON Database::<databaseName> TO <serverPrincipal>

To demonstrate, first, I will create a login:

create login test with password = 'like, password, dude'
go

Then a database just taking all of the defaults.  Setting an owner is not a part of the CREATE DATABASE syntax anyhow:

create database showChangeOwner
go

Then, check the owner:

select SUSER_SNAME(owner_sid)
from   sys.databases
where  name = 'showChangeOwner'

This will return the login that you resolve to when you created the database. 

MYDOMAIN\LBDAVI

Next, run the following statement to change the owner:

ALTER AUTHORIZATION ON Database::showChangeOwner TO test

Then run the previous statement to see that the database is now owned by login: Test .

As an aside, if you use EXECUTE AS to change context to a different login, any database you create will be created as the user you are EXECUTING AS, so this could technically be the way to set the owner at creation time.  I will give the test login rights to create a database then execute as it. (My new favorite permission is "create any database".  I looked, but despite the connotation, there is not a "create almost any database", "create just this one database", or even "create database named fred" rights.

grant create any database to test
execute as login = 'test'
create database showChangeOwner2

Checking the owner again:

select SUSER_SNAME(owner_sid)
from   sys.databases
where  name = 'showChangeOwner2'

Will show that this is owned by user "test". ALTER AUTHORIZATION will actually let you change the owner of quite a few different things.  For more information, please visit your public library...wait, no this is 2007, not 1970...  Check here in books online.

Don't forget to clean up!

drop database showChangeOwner, showChangeOwner2

(did you know you could do that?  Saved me having to type drop database two times.  Wow, the time I saved by not typing drop database.  Wait, I typed drop database even more times after that.  And yes, this obvious lack of material is why I haven't been blogging of late...)

It isn't that I don't like a challenge, really it isn't.  I like puzzles, mazes, Suduko, video games with challenging levels where I have to really think about a problem to solve it.  So why don't I like poorly designed databases, where finding a value that you want can be just as challenging, or maybe more?  Glad you asked (or maybe you didn't, what do I care, this is my blog :)   And for any of you who might be asking: "Shouldn't I be able to use SQL Server like I want to?"  It is a good thing you didn't actually ask that out loud, or I would have to ban you :)

When someone creates a puzzle, maze, or video game, they design in the challenge purposefully.  Itzik Ben-Gan frequently sets up SQL puzzles on SQL Server Magazine that can be kind of fun.  But when someone builds a poorly designed database, then don't leave you breadcrumbs to find your way around.  Quite the opposite.  If they had spent time trying to think about how other people might get around in their database, they would have naturally started normalizing.   No, usually a poorly designed database is an act of "selfishness" or "ignorance" by a person who is not thinking at all.  Or even worse, was.

This all comes up because I spent a good amount of my week this week digging through a database. First I would identify some data, go back to some other people with some knowledge of the data, have a meeting, then go back and try again.   What should have been a quick, painless task to identify 20 columns from this database took much longer than necessary.  Not 100s of tables worth, no, just a few measly columns.  So what was wrong?

• Columns that had meaning in one row, but not another
• Columns named FieldName1, FieldName2...
• Two related tables that were not related by key, but by a concatenation of columns
• One database per day of activity, not a key that denotes a different day
• Data with embedded values, and not always the same format. 
• Domain value with no apparent meaning

I could go on, but I would just be making stuff up.  And frankly this is enough mess for a Sunday afternoon.  The problem is, had someone taken the time to design this database using proper normalization techniques, none of these problems would be evident.  So what is the side effect?

Documentation

Not that it is impossible to build an incomprehensible normalized database (lots of companies do stuff to make it hard for competitors to understand their data (like having column names be non-sensical without their data dictionary).  But the problem here is that yet another person spent time creating a "general purpose" database.  It never ceases to amaze me the lengths that people will go to never change tables in SQL.  So they do all of this nasty mapping in their code. 

But you know what.  It turns out that SQL Server has a lot of really cool stuff that lets you customize data storage.  For example:

ALTER TABLE allows you to add columns

CREATE TABLE allows you to create tables

sp_addextendedproperty allows you to add documentation to these properties

Note that I am not necessarily suggesting that the answer to all "open schema" type problems necessarily should (or even could) be solved by simply adding to the schema.  In many cases you would not want that at all.  But in this case, I am not talking about that situation.  Why?  Because the code had to change...

If the code that accesses the data needs to change based on the structure, extend the data structures.  It will save you code if your database is cleanly created and normalized.  Use SQL to do the job it was made for.  Then, it will be easy to go to one table that has a name that matches what I think I want, see what it is used for, then go to related tables, and to their related tables and so on.  Legal values for a column will be documented and checked.  Names given to columns will reflect their meaning.  You might even have descriptions stored in extended properties.

And then a job that took a week could have taken an hour.  (On the other hand, I get another anecdote for my presentation next weekend at devlink (www.devlink.com) and for my next book.  And this blog.  Maybe I do like poorly normalized databases...Nah!)

I have already given a rundown of most everything PASS here on my personal blog, including a series of posts with pictures too over the days of PASS) but since this blog is all about things technical with SQL Server, I wanted to just rundown of some of the cool stuff I learned about SQL Server 2008 that I hadn't heard before...

• You can index a sql_variant (script in this post)- possibly the least important piece of information is the most interesting.  A person asked Dejan Sarka in his session if you could index them, and no one really expected that you could.  Turns out, you can....
  
• Spatial Datatypes are going to rock! - Really cool stuff.  (good high level rundown here on the Virtual Earth team blog).  They will support a flat earth model (like you use on a road map) and a round earth (well, ellipsoidal really) model as well, for more precise modeling of large spaces. Initially I thought this was going to be a fringe datatype and while it still might be one day in the future, I saw a lot of ideas I could use this for, even in an OLTP database. 
  
  For example, if you have the longitude and latitude of the address of a customer/constituent, it is going to be easy to join that point to the shape that represents a time zone, a country, the zip code, a voting district, etc, etc.  Or you could also do it at a zip code level, and join the shape of a zip code to the the other shapes it intersects with.  Of course, just like XML, these values will not always be exactly relational in nature (though you can make a case these types of datatypes represent a scalar value (a shape) while internally they might be made up of tons of points.
  
• Intellisense is a coming - Yes, without an add-in, SQL Server 2008 tools will have intellisense.  Can't wait to see just how this works out for them.  Red Gate's version is ok, but it can be kind of clumsy to work with. Might even still be useful to have both...who knows.  The folks at Red-Gate are smart cookies.
  
• Analysis Services 2008 will be evolutionary, not revolutionary - This is a good thing really.  The change from 2000 to 2005 was big, huge, major.  The problem was, it was so huge that it was hard to figure out all of the bits and pieces.  Part of this was the tools were version one, and 2008 will take the tools to the next level.  The biggest neat stuff in there surrounded having the engine able to give warnings about how your cubes are designed.  Blue squiggly lines (Donald Farmer claimed this to be a new technical term: "squiggly" in his session on AS 2008) will show you warnings right in the UI, and red ones show errors.  Warnings can be ignored, errors not so much.  Apparently you can use the new tools to modify 2005 cubes too, though you won't be able to use any new functionality that does get added to 2008.
  
• T-SQL is making small amounts of progress forward - As a person who basically makes his living on relational database design, and T-SQL, I am a bit disappointed by how little T-SQL is changing.  I am particularly desirous of lots of usability tweaks being done to T-SQL (like why do something about delayed name resolution on procedures or especially CREATE OR ALTER syntax for creating objects).  To me, the biggest wins for DBA could be done at what should be the least cost to the dev team.  Both of the ideas stated would only matter to the compiler, right? So nothing