April 2004 Blog Posts

  • A Time based RI constraint OR A business rule changes and nobody notices..

    After the introduction of our Booking table and its overlap constraint, all is going well when the rules are suddenly shifted. The requirement now is that each resource (Room) has a certain availability and can only be booked during those times. Each room will have its own unique available times expressed as a Day of the week and the start time and end time during that day. Non consecutive times are all so permitted.As an example Room 1 is available between 9AM and 5PM Monday to Friday, but Room 2 is only available on a Monday and there is a 1...

  • The Migration

    It looked like a normal day. The cars where smoking, the birds where coughing and the daily caffeine war had already begun. "Coffee making is an art" read the sign on the footpath. Then why is a machine making it? The users don't care, as long as it tastes good. The security guard nods at my ID, the elevator beeps pleasantly when I wave the card under its nose. The door clicks when I show it my ID. Sit, insert smart card, apply password.. I'm in. The Boxes have done a good job. The Mail Box has destroyed all the...

  • Views are good

    I recently came across a comment on a web page that said something to the effect of.. "I don't use views because of temporary tables, table variables and UDF's." Well tickle me pink.. but what the hell does that mean? I suspect that either the author has only ever worked on a single front-end/single database/single security account scenarios or has absolutely no clue what views are for. Besides the security goodness, abstraction of underlying complexity and its data integrity features, I suppose you could say they are useless.... :-) Usually I ignore these comments but some current application changes screamed for the use...

  • Free Range SQL - It tastes better.

    A common requirement from users is the ability to be able to search on the columns they want. In affect they would like to roll there own queries. As an example take the Employees table in Northwind. The user wants to be able to search on any/all fields. If your development environment only allows stored procedures then the usual solution is to use the ISNULL or COALESCE for each column in the WHERE clause. SELECT * from Employees WHERE EmployeeID = ISNULL(@EmployeeID, EmployeeID) Problem is, performance sucks..At best you will get an index scan... There are some ways to build the predicate that can...