I got some comments about the Last_Day-funktion. Mainly "Why all this stringconverts" and so on. My only answer for this is "Why not?" and the main reason for the post was to describe how easy it is creating functions, working with different datatypes and manipulate strings. Well, I guess it also showed how easy it is to get something wrong. There are many ways of doing this and I have a bunch of them. I'm sorry for publish "bad" code... We all know that if we read it on the net it's true... *smiles

Anyway, to reach the last date in current month you can do this:

SELECT getdate(), DATEADD(day, 0 - DATEPART(dd, DATEADD(month, 1, getdate())), DATEADD(month, 1, getdate()))

It's the bold part that's interesting. Lets break it up!

DATEADD(month, 1, getdate()) is just one month ahead. Let's call it {today_add_one_month}

DATEPART(dd, DATEADD(month, 1,  {today_add_one_month})) gives us the actual daynumber of next month. Could be called {daynumber_next_month}

Using 0 - {daynumber_next_month} as an offset, {date_offset}

DATEADD(day, {date_offset}, {today_add_one_month})

So, there you have it. I guess this is the fastest way of doing it. But with functioncalls you'll never know.

I'd like to share some samples from the class "T618 Informationsbehandling med SQL". This is the design we discussed the last day.

For all you others, this is an example on how to do it based on the techDays-session. There are some minor adjustments in the script to be fixed, but it works  good enough. For example the fillfactor settings need to be calculated based on actual load and so on.

 Anyway, enjoy!

OrdersDB_PART1_V1.sql (9,20 kb)

As promised! Here is it, the session called "Från 30 sekunder till ett ögonblick - Data Driven Database Design". For those who don't recall reading swedish there might be a translated version in the future. Scripts for illustrating the slidedeck are under production and will be available later. For those not attending the session, briefly it's about how to gain high performance in the data layer by using normalization, generalization and de-normalization to structure the data based on the data itself instead of the straight forward application driven database design, how to get the data more symmetric and distributed, and how to optimize data access by chosing the right indexing strategy.

The slidedeck do not utilize animations, that's why there are so many slides. The content is owned by me and may not be reproduced without my permission. All you need to do is just ask.

And remember what I said on stage: "Normalization rocks, but you need to put some common sence into it."

TD09-DAT-001.pdf (472,48 kb)