Try a Database Design Exercise At Your Next SQL User Group

Tried something different for the January 2013 meeting of the Baton Rouge SQL Server User Group (brssug.org), which meets every second Wednesday at the Louisiana Technology Park (latechpark.com).

I think this was one of the most enjoyable meetings we've ever had, and certainly the most interactive.  As the chapter president, this took very little preparation (printing, writing implements) and really required me to do nothing more than lead the discussion.  My most excellent user group colleagues did all the heavy lifting, with lots of smiles.

I took my Relational Database Design exercise from the October 2012 Regional AITP Conference, printed out a bunch of copies, and handed them out to the attendees.  I'd recommend this to anyone looking for a change-up in their user group.  We couldn't find a speaker for January, so I thought this would be good content for our first meeting of the year.

For about 20 minutes, they worked with pens (not pencils, we're professionals!) on scratch paper for rough drafts of their design.  I reminded them that knowledge of American college football was not required, and that if you were making design decisions based on sports knowledge, you're probably not on the right track.

I hooked up my laptop to the big screen TV and started up a blank database diagram in SQL Server Management Studio 2012, which many of them had not seen.  The Database Diagrams tool built into SSMS is perfect for an exercise like this.  I hit "New Table" and said, "What table is first?"  Later, "Any more columns we need here?"  "What's the next table?" "What would be the best data type for this?" While on the keyboard, I tried to make as few decisions as possible, and encourage discussion of everyone's ideas and suggestions.  A couple times, we backed up the design after changing our minds.  No worries!

The 15 of us got through most (not all) of the design in about 80 minutes, time which flew by.  We had a great mix of very friendly folks.  Some had experience back to SQL 4.2, some with lots of Business Intelligence experience, and some .net devs with only accidental SQL experience.  We covered a ton of topics that were educational for everyone, and stumbled across too many design choice point-counterpoint discussions to remember.  

Do we need audit fields?  What attributes do we need to store per year, per player, or per game?  Are attributes dependent on the primary key?  How should we handle static student data vs student data that is measured annually?  The storage of games, schedules and rosters was a big source of conversation - always polite and professional and with lots of jokes and side conversations.  While most of our design conversation was vendor agnostic, we also touched on data types and indexes in SQL Server.

Along the way, added indexes, unique constraints, computed columns (to record if our football team won/lost/tied), foreign keys, identity columns and more, using only the Management Studio Database Diagrams.

The point of the user group meeting was not to complete the exercise - there wasn't enough time - but to raise all the kind of database design decisions that would come up in "real life."  With more time, we could have completed the design, filled in sample data, or even created some basic reports and a data warehouse to serve them.  

Here is a link to the problem statement for the contest, which I suppose now I can't ever use again for a conference competition:

Here's a second panoramic I took, with much less success.  My apologies to the folks whose heads were vanished by the Pano app on my android phone.

Thanks to everyone who attended, see you in February!

On the Advantages of DateTime2(n) over DateTime

Starting with SQL 2008, we database developers started becoming more familiar with datetime2.  Sometimes folks need convincing though, so here goes.

Here's a brief review of how the precision of the datetime2 data type converts from a varchar representing a time value out to one ten-millionths of a second.  Run this script yourself or view the results in the image below:

declare @datetime varchar(50) = '01/01/2012 11:11:11.1111111'

select        @datetime

select        convert(datetime2(0), @datetime)

select        convert(datetime2(1), @datetime)

select        convert(datetime2(3), @datetime)

select        convert(datetime2(4), @datetime)

select        convert(datetime2(7), @datetime)

select        convert(datetime2, @datetime) --default is 7

Want to do the same conversion with datetime or smalldatetime?  Can't.

Msg 241, Level 16, State 1, Line 10

Conversion failed when converting date and/or time from character string.

The old data types can't handle that much precision.  Gotta dial it down for the old datetime and smalldatetime types.  How quaint.

declare @datetime varchar(50) = '01/01/2012 11:11:11.111'

select        convert(datetime, @datetime)

select        convert(smalldatetime, @datetime)

select        convert(datetime2(0), @datetime)

select        convert(datetime2(1), @datetime)

select        convert(datetime2(3), @datetime)

Note how the old data types are incapable of storing precision out to one one-thousandth of a second.

How about date ranges?

datetime: 1753-01-01 through 9999-12-31
smalldatetime: 1900-01-01 through 2079-06-06
datetime2: 0001-01-01 through 9999-12-31


Now the kicker. What's the cost to storing all that extra precision in datetime2? None. You can get more precision than datetime and fewer bytes per row per field by specifying a precision value for columns declared as datetime2(n).

For example, datetime(2) stores one hundreds of a second - realistically the same precision as datetime, which rounds the third place to the right of the decimal. And datetime(2) is two bytes smaller than datetime, making it ideal.

Don't need seconds, just hours and minutes? Stick with smalldatetime, 4 bytes, as opposed to datetime2(0) at 6 bytes.

Storage requirements 

smalldatetime:
4 bytes - precision to the minute (seconds are always :00)

datetime2(n):
6 bytes for precisions less than 3 - precision up to one hundredth of a second
7 bytes for precisions 3 and 4 - precision up to one ten thousandth of a second
8 bytes for precisions > 4 - precision up to one ten millionth of a second (within 100 nanoseconds)

datetime:
8 bytes - precision to one hundredth of a second, rounded precision to three thousands of a second

Clearly, datetime2 is an upgrade in range of values, precision (no rounding!) and storage size over datetime.

And that's only if you need to store date and time info. Since SQL 2008, we've also been able to store mm/dd/yyyy data in the date data type (3 bytes), and discrete hh:mm:ss in the time data type (5 bytes).

Oh yeah, and even though datetime is not deprecated, this friendly yellow box might make you think so.

Note
Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

.net developers?  Datetime and Datetime2(n) both map to System.DateTime.  No worries there.  More info here: http://msdn.microsoft.com/en-us/library/bb675168.aspx

HOWEVER, you should probably be using datetimeoffset, not just datetime2. DateTimeOffset works similarly to datetime2, but adds 2 bytes to store a +-hh:mm data. While this doesn't store the time zone (CDT, EST, etc.) it does store the offset. I've given a joint presentation on this topic with a colleague that goes into more detail. (update 7/31/2017)

Management Studio Database Diagram Owners

If you're working in an environment as a developer without sysadmin privileges, and you are creating database diagrams using Management Studio underrated diagram tool, but not the database owner or a sysadmin, you'll see your created diagrams look like this

where the diagram is owned by the developer using the sql login 'jdoe'.  With many diagrams created by multiple developers, this can be ugly, confusing and just nonsensical.  Sysadmins don't have this problem, which is why like me you may have used Database Diagrams for years without encountering this issue.

There is no way to change this or rename it from the Management Studio GUI, but a simple script can fix the problem.

Find the diagram you want to rename, and the new principal you want to be the "owner".

select * from dbo.sysdiagrams

select * from sys.database_principals

(abbreviated results shown below)

name principal_id diagram_id

Diagram_0 1 1

name principal_id type type_desc

dbo 1 S SQL_USER

jdoe 6 S SQL_USER

This script can be executed by the developer to change the owner of the diagram from jdoe to dbo.

  update dbo.sysdiagrams

  set  principal_id  = 1 --dbo

where  principal_id  = 6 --jdoe

and    diagram_id    = 1

And now the diagram isn't owned by one of your developers.