Category Archives: Strange but True

Dates & times: it’s worse than I thought

The BBC has a rather sobering article about time zones here: http://www.bbc.co.uk/news/world-12849630. Fascinating stuff that explains how time zones work and how dates and times came to be so complicated.
Next time somebody gives you an earful about how your query gave them the wrong data because of some subtlety about the time portion of a datetime, a time zone difference, or daylight saving time, you could always point them to this. It might make you look less bad. (I’m just saying.)

Developers are strange

Why do developers always use the GUI tools in SQL Server? I’ve always found this irritating and just vaguely assumed it’s because they aren’t familiar with SQL syntax. But when you think about it it, it’s a genuine puzzle. Developers type code all day – really heavy code too like generics, lamda functions and extension methods. They (thankfully) scorn the Visual Studio stuff where you drag a table onto the class and it pastes in lots of code to query the table into a DataSet or something. But when they want to add a column to a table, without fail they dive into the graphical table designer. And half the time the script it generates does horrible things like copy the table to another one with the new column, delete the old table, and rename the new table. Which is fine if your users don’t care about uptime. Is ALTER TABLE ADD <column definition> really that hard? I just don’t get it.

Strange but True: sp_ tables

I came across a bit of an eye-opener the other day in the SQL Server 2008 Internals book. You know how you can create your own stored proc in master with a name that starts with ‘sp_’ and then you can call it from any other database? Well if you create a table in master with a name that starts with ‘sp_’ you can read and write to it from any database too. Try this out:

use master;
go

create table dbo.sp_temp
(
        tempid        int                not null
        , tempdata    varchar(10)        not null
);
go

use <someuserdatabase>;
go

insert  dbo.sp_temp
        (tempid, tempdata)
values  (1, 'One');

select  *
from    dbo.sp_temp;
go

use master;
go

drop table dbo.sp_temp;
go

It doesn’t work if the table name doesn’t start with ‘sp_’. The example in the book (page 190) was about capturing the output from DBCC LOGINFO for all databases into a table for further analysis. This tool probably shouldn’t become your new hammer for use on every screw in sight but it’s very handy in certain circumstances.