Coolness
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.
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...
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...
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)...
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:
Error
Level
State
MessageText
...
Robocopy is one of, if not the, best life-saving/greatest-thing-since-sliced-bread command line utilities ever to come from Microsoft. If you're not using it already, what are you waiting for?
Of course, being a Microsoft product, it's not exactly perfect. ;) Specifically, it sets the ERRORLEVEL to a non-zero value even if the copy is successful. This causes a problem in SQL Server job steps, since non-zero ERRORLEVELs report as failed.
You can work around this by having your SQL job go to the next step on failure, but then you can't determine if there was a genuine error. Plus you still see annoying...
Yet another fair and balanced Slashdot discussion about XML databases.
Bored with SQL Server? Looking for something new, and/or cheaper? Got a bunch of interesting alternative databases:
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...
I've set up a new category on my blog where I plan to post some articles about using command-line utilities to perform various tasks for, or in support of, SQL Server. I'll go over the basics like bcp, osql, DOS commands, and hopefully go more in-depth on some other utilities from the Unix/Linux world that are incredibly useful. I'm hoping to even post a full case study where an existing monolithic, mission-critical VB app is replaced with command-line utilities (depends upon workload and office politics though) I won't post all of them in the main SQL Team blog page, so you...
Dare Obasanjo has an article called the XML Litmus Test to determine if XML is appropriate for certain applications. It seems to be an elaboration on a response he made to my earlier rant, although I doubt that was the entire motivation. He demonstrates an INAPPROPRIATE use of XML, which I hope really raises the ire of some of the knee-jerk “XML will cure cancer“ type people out there. :)
Although, I have to say his example is one of those esoteric things that most people will never get into anyway, and therefore not particularly relevant to the discussion. It's still a...
OK, the review is kinda lame, but the comments on this Slashdot book review are pretty good, many of them really funny...but oh so true. I won't go into my opinions of MySQL...ooops, too late.
And I just found this link for VistaDB, version 2.0 of which was recently released. I haven't heard of it before, anyone try it yet? Gotta admit, some of the praise ("I...cannot stop being thrilled with the speed") sounds pretty corny though. Don't know how it stacks up to REL, but at least it has PROPERLY FUNCTIONING foreign key constraints.
Lastly, if you...
I will use Bill Graziano, Ken Henderson, and Fernando Guerrero as my examples for this:
Pick a good topic, cover it thoroughly, and try to provide more than what's expected (Graz, Ken, Fernando)
Create good slides that are descriptive enough so that notes aren't required (Graz, Ken, Fernando)
Provide handouts anyway, in case you want to take notes (Graz, Ken)
Allow for a Q&A session (Ken), or allow attendees to ask as you present (Graz). Reward questions with food (Graz). :)
Complement, but don't repeat verbatim, your previously published material (Graz, Ken, Fernando)
Practice speaking clearly and authoritatively on the topic (Graz, Ken, Fernando)
DEMONSTRATE, don't just...
If you want to learn things about SQL Server that you'll never find anywhere else, you must read Ken Henderson's Guru's Guides to SQL Server. I just got his most recent book, The Guru's Guide to SQL Server Architecture and Internals, and like his previous books it is amazing. This one is totally packed with everything that wasn't included in the earlier volumes: how SQL Server uses WinAPI calls, thread scheduling, virtual memory management, how the different network libraries operate, how to “debug” the internal processes, how to discover undocumented calls, more XML not previously covered, Notification Services, replication, and...
...but endlessly cool nevertheless:
http://www.rednova.com/news/stories/1/2003/10/02/story001.html
As Damian pointed out, you can do a lot of cool things with DOS command-line functions. Lately I'm particularly enamored of the for command.
The DOS for command works like a for...next loop in Basic, C/C++/C#, Java(Script) etc., only you can have it traverse a filespec (like dir *.txt would), a directory tree, or even the contents of a file. To modify Damian's example a little, you could actually execute each individual SQL script with the following:
for %a in (*.sql) do osql -E -Sserver <%a
The %a is a DOS replaceable parameter, and it holds the complete file name of each file in the *.sql...
Figured no one would remember this blog next year during the presidential election... :)
So now our rampant SQL Team egos can rant unchecked by the need to remember we too were once SQL n00bs who didn't know anything! MWUH HAHAHAHAHAHAHAHA!!!!
I sense graz dreading the moment he gave me a blog here, and sympathize deeply.
My current blog title (TRUNCATE TABLE master..sysdatabases) may change in the future, unless overwhelmingly positive feedback or threats ask that it remain, so if you like it, let me know!
Thanks graz! Once again you've put another great thing on an otherwise dull Internet!