TRUNCATE TABLE master..sysdatabases

SQL Server Execution Plan Icons

Here's a handy link that shows all the query operator icons used in graphical SQL Server execution plans.

Your mission, if you choose to accept it, is to write a query or batch that displays all of them. :)

This tape/disk/database will self-destruct in 5 seconds.  Good luck, Jim.

posted @ Thursday, August 26, 2010 2:42 PM | Feedback (2) | Filed Under [ Coolness ]

Use Microsoft DB2 OLEDB Provider On Non-Enterprise SQL Server Editions

Microsoft makes a pretty good OLE DB Provider for DB2 that you can use for SSIS, DTS, and linked servers under SQL Server. It's sometimes difficult to configure but you can get some good starter settings here.

In yet another remarkable but not uncommon blunder on their part, Microsoft's installation package will only install the provider on a server with Enterprise or Developer Edition. Why they do this I don't know, but if you have a Standard Edition SQL Server and want to use this provider, here's a workaround:

1. Install Enterprise or Developer Edition side-by-side on your Standard Edition server as a named instance, only the database engine components.
2. Install the DB2 OLEDB Provider.
3. Uninstall the side-by-side instance. Or keep it if you like it and have a valid license.

• I've only tested this with Standard Edition, but I believe it will work with any edition of SQL Server.
• You may be able to use a trial version of SQL Server for the side-by-side install, I haven't tested it.
• This technique also works for SQL Server 2000 instances, as long as your side-by-side instance is 2005 or higher

If you're wondering why I'm posting this: <rant>

• It was in answer to this Tweet.
• I'm aggravated that Microsoft:
  1. Makes a great product
  2. That they give away for free
  3. And deprive their user base with a ridiculous and unnecessary SKU limitation

Sorry MS if you don't agree, but DB2 integration is NOT an Enterprise-only feature, nor one that should cost $30K+ to get.  If you really think so, then charge $30K for the provider, don't tease your customers with a freebie.

</rant>

posted @ Thursday, August 19, 2010 6:28 PM | Feedback (2) | Filed Under [ Coolness Stupidness ]

How to make your databases better despite Microsoft's Best Practices (AND REDUCE TYPING!)

I added the parenthetical title because no one really cares if they can make their databases better. :)

Based on some recent tweets (one and two) and blogs I've been reading/writing, I thought I'd create an example of some excellent but underutilized features in SQL Server: defaults and user-defined types.

User-defined types (UDTs) are a way of renaming a standard SQL type and additionally providing a NULL or NOT NULL constraint on that type:

CREATE TYPE [USERNAME] AS varchar(64) NULL;

This can be used in a table definition like so:

CREATE TABLE [MyTable] [UserCreated] USERNAME;

It saves some typing, and I get an automatic NULL constraint on the column, which I can override with NOT NULL.  (Normally I do the reverse; allowing nulls is not a "constraint")

Defaults are a deprecated feature that will be removed from SQL Server at a later date.  Why?  Because someone hates this:

CREATE DEFAULT [whoami] AS SUSER_SNAME();

They also hate this:

EXEC sp_bindefault 'whoami', 'USERNAME';

Which allows me to reduce this:

ALTER TABLE [dbo].[MyTable]
    ADD [UserCreated]  VARCHAR (64) CONSTRAINT [DF_MyTable_UserCreated] DEFAULT (suser_sname()) NULL,
        [UserModified] VARCHAR (64) CONSTRAINT [DF_MyTable_UserModified] DEFAULT (suser_sname()) NULL;

to this:

ALTER TABLE [dbo].[MyTable]
    ADD [UserCreated]  USERNAME,
        [UserModified] USERNAME;

Yeah I know, when you add up the default and UDT definitions and bindings, you...still save a decent amount of typing, but not all that much...

...unless you have several hundred tables that use the exact same definition.  To rehash from my blog post:

ALTER TABLE [dbo].[MyTable]
    ADD [DateCreated]  DATETIME     CONSTRAINT [DF_MyTable_DateCreated] DEFAULT (getdate()) NULL,
        [DateModified] DATETIME     CONSTRAINT [DF_MyTable_DateModified] DEFAULT (getdate()) NULL,
        [UserCreated]  VARCHAR (64) CONSTRAINT [DF_MyTable_UserCreated] DEFAULT (suser_sname()) NULL,
        [UserModified] VARCHAR (64) CONSTRAINT [DF_MyTable_UserModified] DEFAULT (suser_sname()) NULL;

With a quick mod to my Tweet:

CREATE TYPE [MYDATE] AS DATETIME;
GO
CREATE DEFAULT [now] AS GETDATE();
GO
EXEC sp_bindefault 'now', 'MYDATE';

I can really beat this horse to death:

ALTER TABLE [dbo].[MyTable]
    ADD [DateCreated]  MYDATE,
        [DateModified] MYDATE,
        [UserCreated]  USERNAME,
        [UserModified] USERNAME;

And reduce my typing by over 60%.  By the way, did you like how the datetime UDT, default definition, and binding all fit in a single 140-character tweet?  I sure did!

I'll leave it up to you to decide if consistent, reusable data types with shorter names and identical defaults are worthwhile.  And in case you're asking, yes, I've had developers argue AGAINST this very sentence.

Rules are another non-ANSI feature that SQL Server inherited from Sybase, and Microsoft also feels are A Bad Thing™ that should be deprecated. I don't have a good example right now but I should have one in a future blog soon. In the meantime you can read some of my thoughts in this forum post.

I can understand Microsoft wanting to remove Sybase cruft, especially if it's non-ANSI standard, but so far I've not heard of any new features to replace them, such as CREATE DOMAIN, which is ANSI standard and neatly combines types, defaults, and rules into one package.

If you feel this is a useful feature to have, and a bad one to lose, please visit Microsoft's Connect site and vote on this item. It shouldn't be that hard for them to implement, since they can just borrow the code from the open-source PostGreSQL project. ;)

posted @ Tuesday, August 17, 2010 4:58 PM | Feedback (2) | Filed Under [ Coolness ]

Regular Expressions are cool...

I recently had to modify tables to add some auditing columns and triggers. Being a good fair to middling crazed, insane DBA and wanting to ensure data integrity and good design, I made the columns not nullable, and therefore also had to add defaults for these new columns:

ALTER TABLE [dbo].[MyTable]
    ADD [DateCreated]  DATETIME     CONSTRAINT [DF_MyTable_DateCreated] DEFAULT (getdate()) NOT NULL,
        [DateModified] DATETIME     CONSTRAINT [DF_MyTable_DateModified] DEFAULT (getdate()) NOT NULL,
        [UserCreated]  VARCHAR (64) CONSTRAINT [DF_MyTable_UserCreated] DEFAULT (suser_sname()) NOT NULL,
        [UserModified] VARCHAR (64) CONSTRAINT [DF_MyTable_UserModified] DEFAULT (suser_sname()) NOT NULL;

Unfortunately by making them not nullable, the ALTER statement has to rewrite all the data pages to insert the default values.  This kinda-sorta locked down the table(s) and made the database unresponsive for about, oh, 2 hours or so.  (Stooooooooopid multi-million row tables!)  It also had the side effect of making months worth of transactions appear as if they were created instantly by the same user.  For some odd reason my boss felt neither circumstance was a good idea to deploy to production.

Happily the solution to both problems was to make the new columns nullable:

ALTER TABLE [dbo].[MyTable]
    ADD [DateCreated]  DATETIME      NULL,
        [DateModified] DATETIME      NULL,
        [UserCreated]  VARCHAR (64)  NULL,
        [UserModified] VARCHAR (64)  NULL;

And add the constraints afterwards:

ALTER TABLE [dbo].[MyTable]
    ADD CONSTRAINT [DF_MyTable_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
        CONSTRAINT [DF_MyTable_DateModified] DEFAULT (getdate()) FOR [DateModified],
        CONSTRAINT [DF_MyTable_UserCreated] DEFAULT (suser_sname()) FOR [UserCreated],
        CONSTRAINT [DF_MyTable_UserModified] DEFAULT (suser_sname()) FOR [UserModified];

The problem was turning the original, single statement into the 2 new statements, without doing it by hand.  (Did I mention there were a few hundred tables in 8 different databases?)  Playing around a bit I discovered that this syntax works perfectly:

ALTER TABLE [dbo].[MyTable]
    ADD [DateCreated]  DATETIME      NULL, CONSTRAINT [DF_MyTable_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
        [DateModified] DATETIME      NULL, CONSTRAINT [DF_MyTable_DateModified] DEFAULT (getdate()) FOR [DateModified],
        [UserCreated]  VARCHAR (64)  NULL, CONSTRAINT [DF_MyTable_UserCreated] DEFAULT (suser_sname()) FOR [UserCreated],
        [UserModified] VARCHAR (64)  NULL, CONSTRAINT [DF_MyTable_UserModified] DEFAULT (suser_sname()) FOR [UserModified];

Wow, that's pretty close to the original!  (And it runs instantly since it only requires schema locks on the table)  It's still annoying because I have to copy the new column name and paste it at the end of the DEFAULT definition.

Normally this is where I break out my 1337 4@X0r skillz and INFORMATION_SCHEMA and generate the SQL I need.  Sadly the cruel, cold mistress named CHANGE CONTROL had other plans:  we have to build from source and deploy with zero changes to the script.  Minor changes are allowed but must be exactly repeatable/reproducible.  (And also checked into source control, but that's for another time.)

If you've ever played with regular expressions (regex) you'll know that it's not hard to reformat the first statement into the last.  If you haven't used regex before, or always get frustrated by them, hopefully this example will convince you to spend more time with them.  SQL Server Management Studio has a find/replace feature that can use regex.  Here's what I used to get the job done (each regex follows after the "-> "):

   Find-> {.*}{\[.*\]}{.*}{CONSTRAINT}{.*}{NOT NULL}{.*}
Replace-> \1\2\3 NULL, CONSTRAINT\5FOR \2\7 

The Find expression breaks up each line into the following groups, using { and } to denote each:

1. All characters before left square bracket [ -> {.*}
2. All characters between (and including) square brackets -> {\[.*\]}
3. All characters between right square bracket ] and the word CONSTRAINT -> {.*}
4. The word CONSTRAINT -> {CONSTRAINT}
5. All characters between CONSTRAINT and NOT NULL -> {.*}
6. The words NOT NULL -> {NOT NULL}
7. All characters after NOT NULL -> {.*}

The Replace expression uses backreferences (\1, \2, etc.) to do the following:

1. Rebuild the first 3 captured groups exactly as before -> \1\2\3
2. Add a space and the word NULL ->  NULL
3. Add a comma, space, and the word CONSTRAINT -> , CONSTRAINT
4. Rebuild the 5th captured group (all characters between CONSTRAINT and NOT NULL) -> \5
5. Add the FOR keyword and a space -> FOR
6. Add the 2nd captured group, which is the column name and its enclosing brackets -> \2
7. Add the remaining characters captured after NOT NULL -> \7

Try it out for yourself!  You can, of course, do a lot more with regular expressions than this, but it's a good introduction for a relatively common problem. In case you're wondering, the find/replace ran in about 15-20 seconds, and the original 2 hour deployment now takes less than 1 minute.

One final note:  if you've used regex in Perl or some other language, you'll probably wonder why I'm capturing groups with {} instead of the standard () characters.  I wonder that too; my only explanation is Microsoft are idiots hate Unix and Perl people have their own unexplained reasons for using non-standard characters. It's not the first time.

Enjoy!

posted @ Tuesday, August 17, 2010 11:21 AM | Feedback (1) | Filed Under [ Coolness ]

Handy DBCC Feature

I'm sure most of you use DBCC commands and have experience with the WITH TABLERESULTS option to put their output into a table.  Did you know that most of the DBCC CHECK commands also accept WITH TABLERESULTS:

This isn't documented in Books Online but it works with CHECKALLOC, CHECKDB, CHECKFILEGROUP and CHECKTABLE.  Since the output is the same for all CHECK commands you can establish a fixed table layout to hold the results:

CREATE TABLE [DBCC_Results] (error int, 
level int, 
state int, 
MessageText nvarchar(2000), 
RepairLevel nvarchar(30), 
status int, 
dbid smallint,
objectid int, 
indexid smallint, 
partitionid bigint, 
allocunitid bigint, 
[file] int, 
page int, 
slot int, 
reffile int, 
refpage int, 
refslot int, 
allocation int)

And then do the following:

INSERT [DBCC_Results] EXEC('dbcc checkdb([myDB]) with tableresults')

Now if all you care about are errors, you can add NO_INFOMSGS to the WITH clause:

INSERT [DBCC_Results] EXEC('dbcc checkdb([myDB]) with tableresults, no_infomsgs')

EXEC sp_msforeachdb 'INSERT [DBCC_Results] EXEC(''dbcc checkdb([?]) with tableresults, no_infomsgs'')'

posted @ Friday, June 18, 2010 7:03 PM | Feedback (0) | Filed Under [ Coolness ]

SyFy Channel Original Movie Title Generator

Saw this linked on reddit today and couldn't resist going through all the combinations:

create table #pre(name varchar(20))
create table #post(name varchar(20), pre varchar(10))

insert #pre select 'Dino' union all select
'Alien' union all select
'Shark' union all select
'Raptor' union all select
'Tractor' union all select
'Arachno' union all select
'Cyber' union all select
'Robo' union all select
'Choco' union all select
'Chupa' union all select
'Grizzly' union all select
'Mega' union all select
'Were' union all select
'Sabre' union all select
'Man'

insert #post select 'dactyl','a' union all select
'pus','to' union all select
'conda','a' union all select
'droid',null union all select
'dile','o' union all select
'bear',null union all select
'vampire',null union all select
'squito',null union all select
'saurus','a' union all select
'wolf',null union all select
'ghost',null union all select
'viper',null union all select
'cabra','a' union all select
'yeti',null union all select
'shark',null

select a.name +
case when right(a.name,1) not like '[aeiouy]' and b.pre is not null then b.pre else '' end +
b.name
from #pre a cross join #post b
where a.name<>b.name -- optional, to eliminate the "SharkShark" option
order by 1

Which one is your favorite?  I like most of the -squito versions, especially Chupasquito and Grizzlysquito.

posted @ Wednesday, May 26, 2010 12:45 PM | Feedback (1) |

Fixing Robocopy for SQL Server Jobs

robocopy d:\backups \\BackupServer\BackupFolder *.bak

rem suppress successful robocopy exit statuses, only report genuine errors (bitmask 16 and 8 settings)
set/A errlev="%ERRORLEVEL% & 24"

rem exit batch file with errorlevel so SQL job can succeed or fail appropriately
exit/B %errlev%

posted @ Wednesday, February 17, 2010 3:05 PM | Feedback (2) | Filed Under [ Coolness Command Line ]

According to Steve Ballmer...

posted @ Thursday, November 16, 2006 1:33 PM | Feedback (3) | Filed Under [ Coolness ]

Sometimes it's just too easy...

posted @ Tuesday, March 15, 2005 7:45 AM | Filed Under [ Coolness Stupidness ]

Decisions, Decisions...

I've mentioned REL and VistaDB previously. VistaDB has made some significant improvements since then (including a 60% reduction in engine size, to 400KB!). It's geared towards .Net apps as a replacement for MSDE or light SQL Server duties. Go check it out.

Cloudscape was developed by IBM and they released it under an open source license last year. You can get the download here. It's written in Java and is a relational DB. Looks interesting.

PostGreSQL recently released version 8.0 and now includes a native Win32 version, so no more Cygwin troubles. This one is pretty damn solid and has a bunch of cool things SQL Server doesn't.

FireBird has been around for a bit, it's based upon the Interbase engine that Borland maintained and released to open source. Looks interesting.

Except for VistaDB, all of the above are freely available under various open-source licenses too.

And I won't bother mentioning this one again. :)

posted @ Wednesday, January 26, 2005 2:45 PM | Filed Under [ Coolness ]