TRUNCATE TABLE master..sysdatabases

...and other neat SQL Server tricks
posts - 55, comments - 60, trackbacks - 14

August 2010 Blog Posts

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: Install Enterprise or Developer Edition side-by-side on your...

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

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].[MyTable100] ADD [DateCreated] DATETIME CONSTRAINT [DF_MyTable_DateCreated] DEFAULT (getdate()) NOT NULL, [DateModified] DATETIME CONSTRAINT [DF_MyTable_DateModified] DEFAULT (getdate()) NOT NULL, [UserCreated] VARCHAR (64)...

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

Powered by:
Powered By Subtext Powered By ASP.NET