Haven’t posted anything useful in a while, so I figured I should.  This was something I was building for a coworker the other day, and I find the best way to flesh out a solution is to think about how it will work for the thousands who might see this post, and the hundreds that might actually read it. My next post figures to be an updating of my calendar table queries here on my sqlblog.com blog (since I have pretty much abandoned my spaces blog for now.  Another new feature I am going to start is to include a download of the pertinent source code for posts like this. So the create table and insert statement will be in a link at the end of the post.

One of the posts that I am kind of proud of was my initial post on how to build a date table here. I use that script any time I want to create a date dimension for a warehouse, or for some other application. The last use was a time entry system, where I extended it to include work_week, which instead of being Saturday - Sunday, as a normal week is, is defined as Monday - Sunday (Yeah, that way extra time on the weekend is part of the same week.) But I digress.

Recently, the need came up to do groupings of data on the hour of the day, for call center tracking. When I heard the person who would be doing the coding start to say something about using datepart and such, I said, "I know a better way". If you can calculate the number of minutes past midnight, then I could do the rest with relative ease, much like their current experience with the date dimension.

In the end, the hardest part of using the date dimension is the calculating of seconds past midnight. Not hard so much that it is really difficult, but hard to explain.  Basically you use the old stripping the time off of the datetime trick:

DATEADD(DAY, 0, DATEDIFF(DAY, 0, <referencedColumnName>))

This is a neat trick, datediff(day,0 returns a number of days since the base date for the type, either smalldatetime or datetime, and dateadd(day,0 converts the integer to a date value. Quite fast, and you end up with a datetype without having to cast the value (I have to explain this to everyone I introduce it to, so if you already got it, I am sorry).  Then use datediff to see how many seconds have passed since midnight:

DATEDIFF(mi,DATEADD(DAY, 0, DATEDIFF(DAY, 0, <referencedColumnName>)), <referencedColumnName>)

Simple enough, really, just a bit untidy to type.  In reality, if this is a commonly used value for a table (particularly if you have a datetime value in one of your dimension tables), you can just add a computed column to your dimension (probably essential to use the PERSISTED keyword to ensure it is only calculated once).

alter table <tableName>
                add <columnName> as (datediff(mi,DATEADD(DAY, 0, DATEDIFF(DAY, 0, <referencedColumnName>)), <referencedColumnName>)) persisted

Now, the basics are a table that has a key of the number of seconds past midnight, with the types of attributes you might typically calculate, as usual, it is very likely that you will have some more you might want, or less, depending on your organization. Like if everyone had a fixed lunch time, you could add lunch_time, and include the minutes of the lunch time in it.  Then you could compare, say internet bytes/second during lunch versus work hours.

First create your table.  I have called it time_of_day, using my typical naming convention of having reporting tables named with underscores. I have commented on each column as to its meaning.

set nocount on
go
create table time_of_day
(
     time_of_day_key smallint primary key,
     hour_of_day_24 tinyint,                --0-23, military/European time
     hour_of_day_12 tinyint,                --1-12, repeating for AM/PM, for us American types
     am_pm char(2),                         --AM/PM
     minute_of_hour tinyint,                --the minute of the hour, reset at the top of each hour. 0-59
     half_hour tinyint,                     --1 or 2, if it is the first or second half of the hour
     half_hour_of_day tinyint,              --1-24, incremented at the top of each half hour for the entire day
     quarter_hour tinyint,                  --1-4, for each quarter hour
     quarter_hour_of_day tinyint,           --1-48, incremented at the tope of each half hour for the entire day
     string_representation_24 char(5),      --military/European textual representation
     string_representation_12 char(5)       --12 hour clock representation sans AM/PM
)
go

Then we load the table from

--digits gives you a set of 10 numbers 0-9
with digits (i) as(
        select 1 as i union all select 2 as i union all select 3 union all
        select 4 union all select 5 union all select 6 union all select 7
        union all select 8 union all select 9 union all select 0)
--sequence produces a set of integers from 0 - 9999
,sequence (i) as (
        SELECT D1.i + (10*D2.i) + (100*D3.i) + (1000*D4.i)
        FROM digits AS D1 CROSS JOIN digits AS D2 CROSS JOIN digits AS D3 CROSS JOIN digits AS D4)
insert into time_of_day(time_of_day_key, hour_of_day_24, hour_of_day_12, am_pm, minute_of_hour, half_hour, half_hour_of_day,
     quarter_hour, quarter_hour_of_day, string_representation_24, string_representation_12)

--calculates the different values for the time table
SELECT i as time_of_day_key
      ,datepart(hh, dateval) as hour_of_day_24
      ,datepart(hh, dateval) % 12 + case when datepart(hh, dateval) % 12 = 0 then 12 else 0 end as hour_of_day_12
      ,case when datepart(hh, dateval) between 0 and 11 then 'AM' else 'PM' end as am_pm
      ,datepart(mi, dateval) AS minute_of_hour
      ,((i/30) % 2) + 1  AS half_hour --note, I made these next 4 values 1 based, not 0. So the first half hour is 1, the second is 2
      ,(i/30) + 1  AS half_hour_of_day  --and for the whole day value, they go from
      ,((i/15) % 4) + 1     AS quarter_hour
      ,(i/15) + 1  AS quarter_hour_of_day
      ,right('0' + cast(datepart(hh, dateval) as varchar(2)),2)+ ':' + right('0' + cast(datepart(mi, dateval) as varchar(2)),2) as string_representation_24
      ,right('0' + cast(datepart(hh, dateval) % 12 + case when datepart(hh, dateval) % 12 = 0 then 12 else 0 end as varchar(2)),2)+ 
                                                                  ':' + right('0' + cast(datepart(mi, dateval) as varchar(2)),2) as string_representation_12
FROM (    SELECT dateadd(minute,i,'20000101') AS dateVal, i
        FROM sequence AS sequence
        WHERE i between 0 and 1439 --number of minutes in a day = 1440 
       ) as dailyMinutes
Go

You can see the values by using

SELECT top 5*
FROM   time_of_day

returns:

time_of_day_key hour_of_day_24 hour_of_day_12 am_pm minute_of_hour half_hour half_hour_of_day quarter_hour quarter_hour_of_day string_representation_24 string_representation_12
--------------- -------------- -------------- ----- -------------- --------- ---------------- ------------ ------------------- ------------------------ ------------------------
0               0              12             AM    0              1         1                1            1                   00:00                    12:00
1               0              12             AM    1              1         1                1            1                   00:01                    12:01
2               0              12             AM    2              1         1                1            1                   00:02                    12:02
3               0              12             AM    3              1         1                1            1                   00:03                    12:03
4               0              12             AM    4              1         1                1            1                   00:04                    12:04

To use the values, I will create a test table, in this case to simulate a phone call for a call center:

create table testPhoneCall
(
    TestPhoneCallId int identity primary key,
    callType  varchar(10),
    callTime    datetime --the point in time of the call
)

Obviously a real table would have more columns with more descriptive information, but this will do for our purposes.  Using the following insert, I will load the table. Basically I am trying to set about 70 percent of calls to incoming, and then at some random time of day, within 2000 days of 20010101. The go 1000 will load the table with 1000 rows.

insert into testPhoneCall(callType,callTime)
select case when rand()> .7 then 'Incoming' else 'Outgoing' end ,dateadd(minute, rand() * 1439, dateadd(day,(rand() * 2000),('20010101')))
go 1000

Taking a look at the data

select top 10 *
from   testPhoneCall

You should see something along the lines of:

TestPhoneCallId callType   callTime
--------------- ---------- -----------------------
1               Outgoing   2002-10-23 06:46:00.000
2               Outgoing   2004-03-23 14:27:00.000
3               Outgoing   2001-01-13 11:45:00.000
4               Incoming   2002-04-29 03:41:00.000
5               Outgoing   2005-06-30 01:19:00.000
6               Outgoing   2002-06-30 13:15:00.000
7               Outgoing   2005-11-13 06:03:00.000
8               Outgoing   2003-01-01 04:56:00.000
9               Outgoing   2004-09-04 12:26:00.000
10              Outgoing   2005-09-28 08:47:00.000

To use this data with our time_of_day table, we need to get the number of seconds past mighnight the data is.  Using the trick to get the
date without time: DATEADD(DAY, 0, DATEDIFF(DAY, 0, <datevalue>)), I datediff by minutes the timeless date, and the date value with time. You can
do this in a query, or a view, or in my case, I am choosing to add this to the table as a computed column (which is what I am doing with
my data warehouse table that sparked this need:

alter table testPhoneCall
                add callTimeOfDay as (datediff(mi,DATEADD(DAY, 0, DATEDIFF(DAY, 0, callTime)), callTime)) persisted
                                                                            --persisted means it calculates at save time, not run time

Now, we can join to the time_of_day table with our new callTimeOfDay columm, and find out how many calls were in the AM, and which were in the PM

select time_of_day.am_pm, COUNT(*) as numCalls
from   testPhoneCall       
         join time_of_day
                on time_of_day.time_of_day_key = testPhoneCall.callTimeOfDay
group by time_of_day.am_pm

And since we used a random number, it should be about even, and it seems to be:

am_pm numCalls
----- -----------
AM    494
PM    506

And you can mix in data from your table, like the call type, and then see what percentage of calls were in the which half of the hour
and if they were incoming or outgoing calls:

select testPhoneCall.callType, time_of_day.half_hour, COUNT(*) as numCalls
from   testPhoneCall       
         join time_of_day
                on time_of_day.time_of_day_key = testPhoneCall.callTimeOfDay
group by testPhoneCall.callType, time_of_day.half_hour

This returns:

callType   half_hour numCalls
---------- --------- -----------
Incoming   1         157
Outgoing   1         352
Incoming   2         155
Outgoing   2         336

It is a simple as that. Keep in mind that using a table to do time calculations will not always be faster. Using the functions can be faster, though
whenever you are doing groupings/joins a time/date table will often be faster but will always be more convienient.

To download the code for creating and loading the time_of_day table, follow this link: timeTableCreateAndLoad.sql

Darn it, when I get my reviews back from a conference, I want them to say “Perfect”, “Wonderful”,”The best conference I have ever gone to, bar none”, and no matter how many times I offer to fill in the evaluation forms from people, well, let’s just say that there are always a few people who don’t say these things.  I saw Grant Fritchey’s post, and started mine, then saw Marco Russo and Andy Leonard’s while mine stewed overnight… So I am clearly not the pioneer here.

In reality, this was probably my best set of evals ever. One year, I was the fifth lowest in the list. Fifth…lowest. I was very much on the verge of quitting at the time.  But frankly my friend Wayne Snyder gave me the best advice ever. Who cares what they think, just speak about what you care about. (At least I think it was Wayne, we have had a few memorable conversations over time, and I think we were standing in the front of a bus at the time, but I digress).  As a person of (ahem) alternate shape, I have a lot of self consciousness pent up left over from my high school years, and to be honest, I often get that fear of people laughing at me going around and around in my head. In the end I realized that I wanted to try to end the poor database craptacular explosion that (from the examples I have seen) has been spreading throughout the world. Imagining that the audience is all dressed in bright blue bunny eared parkas doesn’t hurt either.

In the end I know I write better than I speak, and one comment was “'Work on the delivery; otherwise, good content.”.  I am just glad this person wasn’t leaving comments five years ago :)

The session was database design, and on a five scale, the ratings this time were (giving a 1 to “very poor” and a 5 to “excellent”:

Not shabby, and I am certainly happy that the highest score was for knowledge :). A few “poor” ratings, and one very poor (a 1) for the usefulness..Fair enough. Lots of excellent. I am never quite sure what people are expecting from a topic of Database Design, and I generally talk at a high level, briefly cover normalization, give some examples, and “bam!” an hour is gone. I tried to do more in the past, but we always got hung up and never made enough progress. At SQL Saturday events (which I have done two, and am doing the Richmond event Jan 30) I add a patterns session where I talk about the patterns I like to use to solve common problems. Still high level stuff, admittedly.

I have commonly used a lot of jokes/lists to keep the mood light and keep people from leaving the room after they realize they had gone to sleep and were drooling on their conference materials. (That is really not cool!) And to be honest, the old adage of making be laugh as a deflection is quite a useful tool.  But, a couple of the comments were: "'Funny but a lot of stop and go." and "I would get rid of our 'lists.'" So they are gone! Okay, not gone, but I have been thinking of de-emphasizing them for a while. I am possibly doing a keynote later this year and I want to replace some of the lists with a more sermon-esque half-rant about the value of database design. I certainly am not going to lose the humor, but lowering the content will be good.

In any case, thank you to everyone who fills out your evals at conferences in a reasonable manner and leaves useful comments. We do read them, and we listen when they are not shouting/whining/insane. Like the person who commented “I hate to give all 5's, but Louis deserves them.”, that is the kind of constructive criticism a person likes to hear.

In any case, hope to see you in November, or at least sometime soon at an event.

Ever since the early days of laptops, I have looked for the “perfect” device. Something with excellent battery life, very portable, and useful for writing. I like to write, you might have noticed. I also like gadgets. The goal being that whenever I get an idea, I can get it down quick.

My first try was to acquire some form of PDA device. I have tried almost ever version of Pocket PC, including the one the looked a lot  like a PC, though much much smaller (It was made by Phillips, I think it was called a Handheld PC).  None of these has ever exactly hit the mark, though they all have had good points. My current Samsung i760 phone does pretty well, but not great for writing long stuff though it does a very good job at quick note-taking/short writing device because it is with me 99% of the time. I can whip out a few paragraphs, and on rare occasion a full blog post, but no matter how good of a thumb typist I might be, it is generally overly tedious to do more than a page or so on the device. Include the fact that the form factor makes a little text seem much larger than it actually is, and it just isn’t even vaguely a good enough writing device. (I am hoping the next version of Windows Mobile 6.5 or 7 will give me more satisfaction, but who knows.)

On the laptop side, I have always used one as my work computer. The problem there has always been weight to power to battery life ratios, I have a DELL M4400 with a 3 cell internal and a 12 cell slice battery, and I still only get about 4 hours, tops.  All that battery makes it heavy. Really heavy, like Van Gogh with a side of Escher mixed with a bit of Dal.. oh wait, that is a different kind of heavy. I mean it weighs like 9+ pounds. The Dell is a very solid workhorse, dual core, 4GB, 1920X1080 video, but it is just a big old thing to lug around (and no, quad core was just too much when I got it a year ago). The only real feature that makes it a great mobile device is backlit keys. This is the feature I truly love, (and the awesome resolution) but it is just too much trouble to lug this thing around just to write a blog post…It is great when I travel for business, as the 1920X1080 video rivals even decent workstation video, and at home I have a writing tablet that it is attached to that make it a great workstation (plus I use it to remote control a quad core 8GB workstation a lot of the time.)

So recently I resolved to get a machine that is small, powerful enough to run Word, Live Writer and an edition of SQL Server that I could use all day long. So I started looking towards the mini / netbook class machines. When I started this post, I had just purchased a HP MINI, 110-1125NR netbook as the answer. I was pretty impressed with it, but I struggled a bit with the resolution at 1024 x 600. Even the keyboard was quite nice and I got used to it in a few hours (I never thanked my dad nearly enough for making me take typing!) I installed Office 2010 pre-release, SQL Server 2008 Express Edition, Windows Live tools, and it all ran pretty nicely.  I carried it around PASS for the most part, though I didn’t have everything completely set up on the new mini so I had to lug the monster around.  I did do most of my Powerpoint editing on the device which worked pretty nicely.  And this was with 1 GB and Windows 7 Starter edition, which had one limitation that irked me… the inability to change your desktop theme. That is going to annoy many netbook users, as their entire goal is a status symbol thing. I do like having vacation location pictures on my desktop, but I would have lived with that…

When I got home, the wife coveted the new device, and as I was keen to up the resolution, I gave it to her and I got to search again. My theory is that way more than 50% of the delight in any task is the anticipation, and so it was fun.  I narrowed it down to a 11.6’ Gateway machine with an Athlon processor, the same HP device, just upgraded to the HD video, and a Toshiba Satellite T115-S1105 (The Dell machines seemed pretty nice too, but still had to order it custom to get what I wanted.) Battery life on the Gateway was just 5 hours, and I wanted more battery.  The HP had to be ordered, and to get it right made it more like 450 than the original 350, then tax and shipping made it too expensive. The Toshiba won out as it had ridiculous 9 hours + battery, DDR3 memory, 2 GB of ram (with an open slot available for another 2 GB), N wireless, and a 250 GB hard disk, 1366x768 video, HDMI output (excellent for hotels with LCD TVs) and Win 7 Home Premium instead of Starter. All for 479 from Amazon.

The keyboard is very nice, and I didn’t have to get used to it much at all. It has a nice trackpad, a Pentium class processor (single core, low power… A Celeron Edition is available for ~30 less too), and runs quite nicely.  I don’t have any really large databases on the machine, as I mostly test syntax and stuff for forum posts and blogs when I use SQL Server on the machine. The only con is size. The step up in video size makes the machine seem almost laptop sizes rather than a mini PC size. It only weighs 3.5 pounds with a 6 cell battery, and they make a 12 cell…18 hours between charges? Might have to get into that. I am even getting 6 hours with the addition of my USB device from Verizon. The lack of a DVD drive is an expectation of something with 9 hours of battery on 6 cells, and frankly with the excellent MagicCD tool and the fact that everything I need I generally get from the web either in Isos (from MSDN) or installers from the web.

Since a device like this will often need to entertain you as well as let me write (sometimes simultaneously), I have tested a lot of the video sites. It does streaming video great from the Move family of players (ESPN360, ABC), as well as Silverlight (Netflix) in excellent HD quality. Hulu video is decent, but not at the highest quality (low quality is smooth, and looks good enough on the 11.6 screen). With VideoLAN and Zune both, video looks great.

Like all computers, it of course comes with a bunch of weird bloatware apps that almost immediately get removed, but also a few useful ones.. The coolest that is worth sharing is the hard disk protection service. It includes a 3D viewer that lets you watch the heads of the drive move, and shake the PC a bit and the heads/platters move to a safe position:

For any of you who attended the Quest breakfast at PASS (and anyone else really) I wanted to go ahead and post the code and give a little wrap up/explanation. (I will post the stuff on perf counters later. They are cool, but this is really what I wanted to make sure people saw.)

I had a plan for my presentation, but time was less than I expected, and the good stuff kinda fell off. So here is the stuff around the Disk IO DMV section that I really messed up and didn’t show enough. I read a few of the comments that my presentation wasn’t that useful to you, and that is clearly on me.  If the index ones are useful, these are too, and can be used to help spread load around your disks.

So, here is the slide I presented:

By knowing the amount of data written to a database file, you can see how busy the file is, the filegroup is, the database, the disk drive, etc, by aggregating the data in the following query in various way.  The query is pretty simple and will return all files in all databases (there are parms on the function to change that, but check BOL for more info):

--since reboot
SELECT db_name(mf.database_id) as databaseName,
            mf.physical_name, divfs.num_of_reads,
            divfs.num_of_bytes_read, divfs.io_stall_read_ms, 
            divfs.num_of_writes, divfs.num_of_bytes_written,
            divfs.io_stall_write_ms,
            divfs.io_stall,size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats(null,null) as divfs
            JOIN sys.master_files as mf
                ON mf.database_id = divfs.database_id
                     and mf.file_id = divfs.file_id

The “stall” columns give you how long any processes have waited on your disk drives to serve up data for this file.  This information plus the information in _bytes_written and _bytes_read columns can give you how much data was moved, and how long your CPU had to wait for the data before it could get working on it.  Very powerful stuff, and can really give you a look at opportunities for performance tuning.

That query gives you data from the last reboot of the server, but usually you want to see up to date values. So you have to get a baseline of values, then compare them at a later point.  You can build tools that do this, and you can use the performance data warehouse in 2008, and I will demonstrate the former.  Using a temp table and a WAITFOR DELAY command, I build a quick tool to see waits and stats over 20 seconds. Note that I use a #temp table to allow you to run the last query over and over to see the comparison to the baseline until you want to drop the #temp table.

--over the following delay
declare @delay varchar(10) = '00:00:20'

SELECT db_name(mf.database_id) as databaseName,
       mf.physical_name, divfs.num_of_reads,
       divfs.num_of_bytes_read, divfs.io_stall_read_ms,
       divfs.num_of_writes, divfs.num_of_bytes_written,
       divfs.io_stall_write_ms,
       divfs.io_stall,size_on_disk_bytes,
       getdate() as baselineDate
INTO #baseline
FROM sys.dm_io_virtual_file_stats(null,null) as divfs
            JOIN sys.master_files as mf
                ON mf.database_id = divfs.database_id
                     and mf.file_id = divfs.file_id

WAITFOR DELAY @delay

;WITH currentLine as (  
    SELECT  db_name(mf.database_id) as databaseName,  
            mf.physical_name,num_of_reads, num_of_bytes_read,
            io_stall_read_ms, num_of_writes,
            num_of_bytes_written, io_stall_write_ms,
            io_stall,size_on_disk_bytes,
                getdate() as currentlineDate
     FROM sys.dm_io_virtual_file_stats(null,null) as divfs
                JOIN sys.master_files as mf
                        ON mf.database_id = divfs.database_id
                            and mf.file_id = divfs.file_id)

SELECT  DATEDIFF(s,#baseline.baselineDate,currentlineDate),
        currentLine.databaseName,
      left(currentLine.physical_name,1) as drive,
      currentLine.physical_name
     ,currentLine.io_stall - #baseline.io_stall as io_stall_ms
     ,currentLine.io_stall_read_ms -
               #baseline.io_stall_read_ms as io_stall_read_ms
     ,currentLine.io_stall_write_ms -
              #baseline.io_stall_write_ms as io_stall_write_ms
     ,currentLine.num_of_reads - #baseline.num_of_reads as
                                               num_of_reads

    ,currentLine.num_of_writes - #baseline.num_of_writes
                                            as num_of_writes
   ,currentLine.num_of_bytes_written -
        #baseline.num_of_bytes_written as num_of_bytes_written
FROM currentLine
      join #baseline
          on #baseLine.databaseName = currentLine.databaseName
             and #baseLine.physical_name =
                              currentLine.physical_name
go
--drop table  #baseline

The most interesting part of this query is often the stall columns.  When I see really bad IO, the number of seconds waited can end up being way more than the actual number of seconds. Disk IO is more or less serial (particularly on my mini HP with Win7 Starter Edition), so if you have many queries running concurrently, every one of them needs data from the same IO source, and if one query used the disk for 1 second, the other processes might wait for 1 second each. When wait stats are really low, that means the disk were ready, willing, and able to serve all processes.

On your test server, you can use a query like:

--create database DMV
--go
use DMV
go
create table testDMV
(
    testDMVid int identity,
    bigun     char(8000)
)
go
set nocount on
insert into testDMV(bigun)
select REPLICATE('a',8000)
go 5000

--drop table testDmv

select *
from   testDMV

To create some data.  Note that data will be written to the disk in various counts.  I set all of the rows to take a full data page, and each of the rows is an individual transaction.  But it won’t be a 1-1 match with the number of rows written or read. SQL Server flushes data to the disk in the manner it wants to, and some manner of caching will change the number over time.  Run the select * from testDMV query over and over and you will not see any change in the disk IO, because it is in cache now.

I hope this is better. Sometimes as a presenter I am a good writer, and frankly the lack of bacon really threw me off this morning. Feel free to email me at louis@drsql.org or tweet me at drsql and I will be happy to share more.  All of this will be in our RedGate book that will be coming out before the end of the millennium, at the very minimum.

The final numbered post in this version of my “pillar” series of posts ends in the most contestable part of the design/implementation process.  Encapsulation. The concept of encapsulation is not contested (or even contestable by sane programmers in any field of the art of system creation. Every time you use a Windows API call you are participating in encapsulation. Every language that doesn’t look like:

0101000101001010101010100101010101010101010101010101010100000001110010101

is using some level of encapsulation to make life easier.  The problem isn’t encapsulation, it is who do we allow to perform the encapsulation.

It is at this point in the process that I have to make something clear.  As a writer in a non-religious instructional topic, you need to be reasonably open to other people’s ideas and concepts and ideas, and no where in database design does this get contentious like when talking about encapsulation. I defined encapsulated in this concepts in the following terms: “Changes to the structures cause only changes to usage where a table/column directly accessed it”

The obvious answer to any long term dba is that by establishing a solid layer of stored procedures to allow others to access the data is the easiest way to achieve these goals.  Just like the Windows API that you call to get a message box on a 64 bit server or this little mini laptop I am typing on right now, stored procedures give you an API the encapsulates all of the joins, inserts, etc away from the other layers of the application. Now the data programmer can adjust the API if desired, but can also remove the entire db and replace all the tables with new tables, if desired.  Rarely does this occur, but it is not uncommon to have multiple apps using the same database structures, and sometimes the needs differ (perhaps one app only needs one address for a person, the other can use all of them…).

So at this point, the common argument is “why go through all of this when I can let my ORM deal with the database?” and it is a not uncompelling argument. As a bonafide lazy person, I hate to do more work than necessary. The problem is, the future.  I learned a long time ago to honor future-me in as many ways as I can (well, eating right and exercising not withstanding) and future-me wants to hang out and admire all of the work current-me did and either relax or build something new for even later in the future me. I hate doing rework.  The motto of my website (drsql.org) “an ounce of design is worth a pound of rework” is a direct reflection of that.  Design now…enjoy later. Some of my favorite production moves were done on stored procedure access systems.  I had built and tested the heck out of the database, and was ready for nearly anything, the other layers not so much. I sat around and taunted them mostly, though I am not perfect, the problem is far easier to manage on the data layer.

It is at this point we pause to admit that the database layer is so much easier to do right than the other layers.  There are fewer moving parts, fewer things to go wrong, and honestly, the SQL Server Dev team gives us a platform that just freaking works. And the foundational principals that Codd came up with 30 years ago constantly amaze me at how well they just work. And look, the code we wrote 16 years ago will still run with minor modifications (that code can be enhanced with new features, but T-SQL strongly resembles the code we wrote all those years ago.

That having been said, to be honest, I said that the argument for ORM’s was not uncompelling, and with some reservations, I find it all too frequent that I have to give in to the ORM craze. The only concern I have is that the database is becoming less of a platform that we code to, and more of a storage platform only.  While initially this sounds like I am trying to protect my job, it really isn’t that at all. My problem is that there are a few consumers of the data that ORM's won’t do an adequate job for unless the database is built for the database server, and not for the ORM.

• Reporters – Best case, we have a BI solution such that users don’t have to access the OLTP database. But someone has to write queries to get the data out
• Future ORMs – No one likes to get nailed down to one programming methodology other than SQL programmers. Most of the products that were around when we were doing 4.21 programming are gone now or morphed into something bizarrely different (like VB). What is good for ORM A might not be good for ORM B.  So now we need to do data conversion? Too costly.
• Importers/Outside sources – Poorly normalized databases make it harder to import data from outside sources without using the API, and most shops are going to do bulk loading of data at some point.

Note I said poorly normalized databases. Is this guaranteed to be the case? No, but any database I have seen designed for the needs of the screen/programmer always looks vastly “different” than a database that was designed for the data platform.

So, unlike the other pillars, this one turned into an op-ed piece, rather than straight facts. Sorry, but it is just that type of problem. There are good points on both sides of the equation, but the real problem is that where data platform pays off is over time. The average turnover for a programmer (any type) is said to be around 2 years. Who cares about the future when you plan to be somewhere else?  Personally I have been with my current employer for 12 years, and hope to go another 20+. And I see/feel the mistakes of the past, while many of my coworkers are somewhere else, grousing about how horrible someone else’s code is…

Final comments on this topic. Any solution that meets the criteria “Changes to the structures cause only changes to usage where a table/column directly accessed it”, or even one that recognizes the problem is a major step in the right direction. I always joke with people in my sessions that if you  are attending my session, you probably care about doing things right. Same with this concept.  If you are paying attention to the future, at the very least you probably won’t leave future-you in a bind, even if future-you is a totally different person.

As I have mentioned in all of the previous posts, basic functionality is the foundation of any system. So it goes without saying that if you have just implemented a payroll system, everyone is getting paid.  To meet the basic bar that EVERYONE agrees upon, to be useful things have to work. Frankly, this is generally the only criteria which needs to be met for most systems to be considered complete, and since I don’t want get off on a rant, that is all that I will say (for now at least, it will be in the final introduction to the pillars when I get around to finishing them up.)  So as we move on to this first implementation pillar, we assume that even the worst system out there does what it is expected to do.  I realize this is probably a stretch in reality, but the best part of writing is that while reality figures into it, the goal is a practical version of perfection, not strictly attainable, but certainly approachable by any half decent programmer who knows the rules.

This next pillar deals with keeping eyes out of the places where eyes shouldn’t be. This pillar is Secure. In basic terms, everything is hunky dory until the Janitor finds out that the CEO is making 2.5 million dollars a month because he clicks on the wrong place in the app, or that “devious” employee who is quitting starts digging around and gives himself a raise…and it is impossible to track who did it.

In the initial post, I defined Secure simply as users being able to only see data they are privy to. Sounds easy enough, but when you start to consider what it means it is considerable more complicated than you might imagine.  In my books, I take the “easy” way out and simply cover security internal to a single database, and even that is not easy.  For the most part, the applications that I see/hear about tend to leave security to the applications that are written, and just give a lot of power to the application.  Is that a good idea?  Does it sound like a good idea as you read it?

No. And if you said yes, then you probably either have no responsibility for the security of your data, nor should you.  The fact is, even when using the application to enforce security, you are going to most likely need tables, columns, and coordination.  As an example, a time entry system is an easy thing to implement, until you hit security.  Everyone (except perhaps one person) has someone they report to. So rights to see everyone who works for you seems like a given, but this starts to be a hierarchy.  Then people have dotted line bosses, people who enter time for them (like an assistant or timekeeper), and if you do any cost analysis, all of these people probably have some entry as to how much they are costing to work there, particularly if they get any overtime. 

In the end you have to build tables and queries that enforce lots of row-level security that is not easily done, and if you try to do this without tables and data, it is completely inflexible….so then you have to deal with overrides….

Let’s imagine then, that you get the app straight, and anyone who legitimately gets into the application is not accidentally or purposefully seeing data they shouldn’t.  You are finally done, right? Well, of course not.

Security needs to be considered in terms of access from external sources, and internal sources. Meaning not just a novice hacker who works for you who might just whine about his salary in comparison to a coworker, but now you have to consider that Harry the Hacker out there gets access to the data, what then?  Could your company fall apart because they get your customer list? It doesn’t matter if you are top secret company, if Mr Hacker sends all of your customer an email stating “I have your name because I stole ________’s database,” how many more times will you give your name to that company? When I said I took the easy way out by only considering database security I meant it.

To get it right, you have to build an impenetrable fortress around your data, making completely certain that while friends can use the data from inside the fortress and outside too, (like customers accessing their data on a portal, or you with VPN), baddies can’t get in from any layer.  How you do that is a book in and of itself, and certainly cannot be covered in a post like this, or a book like mine.  In the end, you just have to work with system administrators and network guys and make sure that you are protected, all activity logged, etc, etc.

Of course, if enough administrators get together, you cannot stop or track them. When you control the logging, and the access, you control the world.  So the greater your need for security, the greater emphasis you had best be placing on checking background and checks and balances so that enough people are required to turn off logging/security that it would be impossible to get that many people involved without someone informing on the crowd.

Does this make you feel less secure? It should, unless the security of your data scares the crud out of you, you are not thinking hard enough.  But if you take the time to make sure your implementation design considers the needs of the users and their rights to access data while keeping others out, you will be fine…probably.

A project that I worked on a while back is finally coming to fruition, and it is one of the coolest around.  Usually when I see a book that has 5+ authors, I am a bit concerned about quality.  In this case however, there are 53 authors and even if the quality was not all that great (which it will be excellent, by the way), the proceeds will be going to a good charity (WarChild.org).

At last year’s MVP Summit, the annual gathering of people with too much free time on their hands and a desire to help out other Microsoft using technologists with that free time, Bill Gates offered us a challenge to “do philanthropy where you are.”  So an idea was hatched that we would all write a book and give the proceeds to charity.

In the end 53 MVPs, largely from the SQL Server community (but not all) participated and produced a book that covers a lot of ground. Paul Nielsen and I wrote the first chapter called “Louis and Paul's 10 relational database design rules” based on our 24 Hours of PASS presentation, and I wrote the second chapter entitled “SQL Server tools for maintaining data integrity”. Then a lot of people wrote 57 other chapters on topics from design, development, administration, tuning, and even BI development.

This is an all-volunteer book. All author proceeds are going to WarChild.org – an organization that helps children traumatized by war. Because this is a book for charity, Manning Publications wanted to also donate and gave us a higher than normal royalty. In addition, if you purchase the book through this link: www.SQLServerMVPDeepDives.com then the purchase will also count toward Warchild's Manning affiliate account and Warchild will receive an extra 10% of the purchase.

As an additional bonus, you can save 50%: purchase from the website by the end of Sept (that is the day after I am posting this, so HURRY!) and use the code pop0928 at the checkout

If you’re going to PASS: The first printed copies will be shipped directly to the PASS Summit Bookstore. We’re planning an official book launch at PASS. If you want to get your copy signed by every author at PASS then you must purchase a copy at the PASS Bookstore.

And hey, if you get a copy and want to have it signed the slow way, one author at a time as you bump into us on the street, I will be happy to sign one any time. For the rest you will have to do some world travelling as the authors come from the whole world around.  And some of them are always travelling to boot.  So good luck and thanks for making this a success!

First off, let’s just say that Devlink (www.devlink.net) is a fantastic conference. It returned to the Lipscomb campus this year, and that was a great blessing.  MTSU last year was not as nice of a venue, and some of the way it had to be organized was not as nice as both years I have been at Lipscomb. Part of it is that it is a private college, but another part is that it is a smaller college.  The food that I ate was good, the seating was nice, and the layout was awesome.  The first day we had comedian Rik Roberts during lunch, and two years ago he was the party.  The party this year was a baseball game, with a small amount you had to pay extra.  I actually missed the party to go to a different gathering, but still it was a good idea for the party. Probably the only thing negative I could say about the conference was that it was harder to find a drink, and the refrigerators weren’t as cold as the barrels of ice from previous years. And if that is the worst thing I can think of, it was a great conference. 

Second, the sessions I attended ranged from great to excellent. Paul Nielsen and I kicked off the conference for the database track (after lots of last minute prep…worst misstep were a few slides that had black text on blue).  In the afternoon I attended Tommy Norman’s Scrum/XP in Team System Deep Dive session and learned more and more about Scrum and some things you can do with Team System (I learned more about Scrum and the Team System was kind of out of my league…Why can’t we use management studio yet for Team System connectivity?). After the first day of expanded sessions, I attended the Opening Keynote by Josh Holmes, a developer evangelist with Microsoft whose topic was whether or not software development has gotten too complex.  That was the first thing that stuck in my mind.

Over the next two days, I attended lots of sessions by Brad McGehee, Kevin Kline, Joe Webb, Jessica Moss, Craig Berntson and Wally McClure. All of the sessions were great, but one of them was the second thing that really stuck in my mind. In addition, saw Tim Ford,  Jeremiah Peschka, Robert Cain, Stephen Russell and many others.

Finally, we had a taping of DotNetRocks Live. Carl Franklin  and Richard Campbell led their show (http://www.dotnetrocks.com/) “live” in front of a studio audience. Again, complexity was the topic…

And that is when I decided that I had to do a bigger write up than what I had gotten to so far. Complexity? Complexity? Complexity?!? As a data architect/programmer, this started to bug me…  In the data driven design session, Craig Berntson had really started to get me cranked up by starting to imply that data wasn’t that important. Design “process” first.  His words sounded like every developer who I have know that wanted to build the database themselves. I think we eventually go to the point where he liked having strong data assistance, but one thing I think that was presented was this:

In my database design sessions, one of the most important things I try to get through to people is that you cannot have a screen that maps to a class that maps to a table perfectly.  If you do, your database will stink, or your UI will stink.  Your classes are also going to be a mess…But wait you say…this is exactly what we do.  It is what most people do because it takes less work.  He was coming from the class side of the equation, as most people design the data first, then everything else. I would note that this is not a good idea, even if you are by trade the data architect. First comes the processes and needs of the client, then the data and everything shakes out from there.

Of course, as a data guy, I want to put up a few layers to deal with this.  One a set of stored procedures that encapsulates all interaction, and then a solid layer of software that works with the stored procedures.  Many business rules (those without any override for sure) would reside minimally in the data structures, and again in the UI to make the user happy (we all need users to be happy), and any rules that aren’t naturally handled in the structure of the data or in simple constraints, moved out to the functional classes.

Many non-data programmers would prefer that all the database did was hold the data. Even things like UNIQUE constraints that fail whenever duplicated data is inserted are too much.

What I really find interesting is that while stored procedures have been reasonably steady for 15 years, I couldn’t keep up with the number of different languages that were bantered around in the 4 or 5 hours of non-data topics I heard over the week. Ruby, PHP, VB 6, VB.NET, C#, Ajax, Python, F#, ASP, AUGH! I mean, seriously. And these are just a few. We have T-SQL. Maybe a little VB or C# for a CLR object now and again, but basically T-SQL has been around forever and is just getting better over time.  Databases that we create in 4.21 would pretty much work now.  And the stored procedures we created could have been upgraded in minutes.

The problem is partially because it seems like it is really hard to change the database, but that is more about the fact that databases have state, unlike code.  Why do you think there is COBOL code still in use?  Because the code is so hard to replace?  No, because the code is strongly coupled with data, which is hard to change because it is so valuable.  But even if the data store could not be changed, if a proper encapsulation layer had been created, well, we might have a COBOL data access layer, but would we have a text terminals still in use? I doubt it.

So if we could just work together and fortify the database and an encapsulation layer, replacing the code wouldn’t be so big of a deal.  Look, I am all about getting things done fast.  But it should say a lot that as data architects we want to do MORE work. I mean, in the short run, just getting the data normalized is win enough.  And putting on CHECK constraints, UNIQUE constraints, and Triggers in some cases would be win enough (and very little work.)  But if we do the more work up front, then later we can save our data administrating brothers (and sisters!) from having to demand code changes to optimize a query.  Which we can’t  because, based on Josh Turner’s keynote, the average tenure of a programmer is about 18 months and no one seems to understand how the code works anymore.

So, if the definition of a great conference is to get people thinking… well, I guarantee you it was a great conference for me.