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.
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.