Hello everyone! It's that time again, time for T-SQL Tuesday, the wonderful blog series started by Adam Machanic (b|t).
This month we are hosted by Matt Velic (b|t) who asks the question, "Why So Serious?", in celebration of April Fool's Day. He asks the contributors for their dirty tricks. And for some reason that escapes me, he and Jeff Verheul (b|t) seem to think I might be able to write about those. Shocked, I am!
Nah, not really. They're absolutely right, this one is gonna be fun!
I took some inspiration from Matt's suggestions, namely Resource Governor and Login Triggers. I've done some...
While preparing for a recent SQL Saturday presentation, I was struck by a crazy idea (shocking, I know): Could someone import the content of SQL Server Books Online into a database and apply full-text indexing to it? The answer is yes, and it's really quite easy to do.
The first step is finding the installed help files. If you have SQL Server 2012, BOL is installed under the Microsoft Help Library. You can find the install location by opening SQL Server Books Online and clicking the gear icon for the Help Library Manager. When the new window pops up click the...
SQLTeam's favorite Slovenian blogger Mladen (b | t) had an interesting question on Twitter: http://www.twitter.com/MladenPrajdic/status/347057950470307841
I liked Kendal Van Dyke's (b | t) reply: http://twitter.com/SQLDBA/status/347058908801667072
And he was right! This is one of those pretty-useless-but-sounds-interesting propositions that I've based all my presentations on, and most of my blog posts.
If you read all the replies you'll see a lot of good suggestions. I particularly like Aaron Bertrand's (b | t) idea of going into the Unicode character set, since there are over 65,000 characters available. But how to find an illegal character? Detective work?
I'm working on the premise that if SQL Server will...
A few weeks ago, AtlantaMDF offered scholarships for each of our upcoming Pre-conference sessions at SQL Saturday #220. We would like to congratulate the winners!
SQL Server Security
We Want YOU…To Learn!
AtlantaMDF and Idera are teaming up to find...
Like most SQL Server users I'm rather frustrated by Microsoft's insistence on making the really cool features only available in Enterprise Edition. And it really doesn't help that they changed the licensing for SQL 2012 to be core-based, so now it's like 4 times as expensive! It almost makes you want to go with Oracle. That, and a desire to have Larry Ellison do things to your orifices.
And since they've introduced Availability Groups, and marked database mirroring as deprecated, you'd think they'd make make mirroring available in all editions. Alas…they don't…officially anyway. Thanks to my constant poking around in places...
If you love SQL Server training and are near the Atlanta area, or just love us so much you're willing to travel here, please come join us for:
SQL SATURDAY #220!
The main event is Saturday, May 18. The event is free, with a $10.00 lunch fee. The main page has more details here: http://www.sqlsaturday.com/220/eventhome.aspx
We are also offering pre-conference sessions on Friday, May 17, by 5 world-renowned presenters:
Despite the efforts of American Airlines, this past weekend I attended the first SQL Saturday in the UK! Hosted by the SQLCambs Chapter of PASS and organized by Mark (b|t) & Lorraine Broadbent, ably assisted by John Martin (b|t), Mark Pryce-Maher (b|t) and other folks whose names I've unfortunately forgotten, it was held at the Crowne Plaza Hotel, which is completely surrounded by Cambridge University.
On Friday, they presented 3 pre-conference sessions given by the brilliant American Cloud & DBA Guru, Buck Woody (b|t), the brilliant Danish SQL Server Internals Guru, Mark Rasmussen (b|t), and the brilliant Scottish Business Intelligence Guru...
Welcome everyone to T-SQL Tuesday Episode 34! When last we tuned in, Mike Fal (b|t) hosted Trick Shots. These highlighted techniques or tricks that you figured out on your own which helped you understand SQL Server better.
This month, I'm asking you to look back this past week, year, century, or hour...to a time when you COULDN'T figure it out. When you were stuck on a SQL Server problem and you had to seek help.
In the beginning...
SQL Server has changed a lot since I started with it. <Cranky Old Guy> Back in my day, Books Online was neither. There...
Mike Fal (b | t) is hosting this month's T-SQL Tuesday on Trick Shots. I love this choice because I've been preoccupied with sneaky/tricky/evil SQL Server stuff for a long time and have been presenting on it for the past year. Mike's directives were "Show us a cool trick or process you developed…It doesn’t have to be useful", which most of my blogging definitely fits, and "Tell us what you learned from this trick…tell us how it gave you insight in to how SQL Server works", which is definitely a new concept. I've done a lot of reading and watching...
I have a few speaking engagements coming up in July. I will be getting my Revenge on twice this week, first at the Steel City SQL User Group in Birmingham, Alabama July 17, 2012:
New Horizon Computer Learning Center
601 Beacon Pkwy. West, Suite 106
Birmingham, AL 35209
6-8 pm CST
Not content with that, with my hands behind my back, I will pull the same thing from my hat at SQL Saturday 122 in Louisville, KY on July 21, 2012: Schedule Register
These include Revenge: The SQL Parts 1 AND 2! New and improved with the...
This month's T-SQL Tuesday is being hosted by Aaron Nelson [b | t], fellow Atlantan (the city in Georgia, not the famous sunken city, or the resort in the Bahamas) and covers the topic of logging (the recording of information, not the harvesting of trees) and maintains the fine T-SQL Tuesday tradition begun by Adam Machanic [b | t] (the SQL Server guru, not the guy who fixes cars, check the spelling again, there will be a quiz later). This is a trick I learned from Fernando Guerrero [b | t] waaaaaay back during the PASS Summit...
We've just finished up a fantastic event at SQLBits X in London! If you've never been to SQLBits and you can make it to the UK, I highly recommend it. If you didn't attend, here's what you missed.
Meanwhile, for those who attended the Lightning Talk sessions and were disappointed that I ran out of time, here's the last part that you would have seen:
/* How to Lose Friends and Irritate People...With Unicode!
SQLBits X - London - March 31, 2012
-- some sexy SQL
DECLARE @oohbaby TABLE(i INT NOT NULL UNIQUE,
uni_char AS NCHAR(i),
hex AS CAST(i AS BINARY(2)))
INSERT @oohbaby VALUES(664),(1022),(1023),(1120),(1150),(8857),(11609),(42420),(42427)
Allen White (blog | twitter), marathoner, SQL Server MVP and presenter, and all-around awesome author is hosting this month's T-SQL Tuesday on sharing SQL Server Tips and Tricks. And for those of you who have attended my Revenge: The SQL presentation, you know that I have 1 or 2 of them. You'll also know that I don't recommend using anything I talk about in a production system, and will continue that advice here…although you might be sorely tempted. Suffice it to say I'm not using these examples myself, but I think they're worth sharing anyway.
Some of you have seen or...
(Yeah yeah, technically it's in Alpharetta, but it's close enough.)
Saturday…Saturday….Saturday…. September 17th. TWO THOUSAND ELEVEN!
OK, it's not a tractor pull, but it's even better: FREE SQL SERVER TRAINING! They have a bunch of great speakers lined up, and for some reason, me. (Protip: be good friends with the program committee, have sufficient bribe funds, and if all else fails, lots of alcohol, drugs and a camera. Ba-ZING! You too can speak at SQL Saturday!)
I will be presenting Revenge: The SQL! in a new and improved SQL Saturday themed presentation. Actually, it's the same ol' presentation, I just updated the slide...
24 Hours of Pass (or 24HOP) is a great program offered by PASS to provide free, online training for anyone who wants to learn more about SQL Server. They routinely have the best SQL Server presenters available for these sessions, and attract hundreds, perhaps even a thousand attendees from around the world. This is definitely one of the best things they've started doing in the past few years, and every session I've attended has been excellent. So why am I so grumpy about it? I'm not really, pretty much everything here is a minor annoyance that I can...
As a follow-up to my earlier post, I found yet another great free resource that the "professor" and the poor students taking that class should look at. I found this via the excellent material Stanford provides for their open course on Databases.
You'll notice how the example ER diagrams look nothing like the one the "professor" created. They're clear, readable, have descriptive text, and use standard UML notation. They also have accompanying SQL to show how the two languages relate and translate to one another.
And if anyone is wondering (because I've been asked):
I don't know if I'll...
Yeah, it was 2 weeks ago, but I'm finally blogging about something!
I presented Revenge: The SQL! at SQL Saturday #77 in Pensacola on June 4. The session abstract is here, and you can download the slides from that page too. You can see how I look in the speaker's shirt here.
Overall it went pretty well. I discovered a new bit of evil just that morning and in a carefully considered, agonizing decision-making process that was full documented, tested, and approved…nah, I just went ahead and added it at the last minute. Which worked out even better than (not) planned, since...
For Part 2 of the Handy SQL Server Function Series I decided to tackle parsing useful information from the @@VERSION function, because I am an idiot. It turns out I was confused about CHARINDEX() vs. PATINDEX() and it pretty much invalidated my original solution. All is not lost though, this mistake turned out to be informative for me, and hopefully for you.
Referring back to the "Version" view in the prelude I started with the following query to extract the version number:
SELECT DISTINCT SQLVersion, SUBSTRING(VersionString,PATINDEX('%-%',VersionString)+2, 12) VerNum
I used PATINDEX() to find the first hyphen "-" character in the string, since...
Just a quick post I should've done yesterday but I was recovering from SQL Saturday #48 in Columbia, SC, where I went to some really excellent sessions by some very smart experts. If you have not yet attended a SQL Saturday, or its been more than 1 month since you last did, SIGN UP NOW!
While searching the OBJECT_DEFINITION() of SQL Server system procedures I stumbled across the DEFAULT_DOMAIN() function in xp_grantlogin and xp_revokelogin. I couldn't find any information on it in Books Online, and it's a very simple, self-explanatory function, but it could be useful if you work in a multi-domain environment....
In Part 1 of the series I wrote about 2 lesser-known and somewhat undocumented functions. In this part, I'm going to cover some familiar string functions like Substring(), Parsename(), Patindex(), and Charindex() and delve into their strengths and weaknesses.
I'm also splitting this part up into sub-parts to help focus on a particular technique and/or problem with the technique, hence the Part 2.0. Consider this a composite post, or com-post, if you will. (It may just turn out to be a pile of sh_t after all)
I'll be using a contrived example, perhaps the most frustratingly useful, or usefully frustrating, function in...
I've been preparing to give a presentation on SQL Server for a while now, and a topic that was recommended was SQL Server functions. More specifically, the lesser-known functions (like @@OPTIONS), and maybe some interesting ways to use well-known functions (like using PARSENAME to split IP addresses) I think this is a veritable goldmine of useful information, and researching for the presentation has confirmed that beyond my initial expectations.I even found a few undocumented/underdocumented functions, so for the first official article in this series I thought I'd start with 2 of each, COLLATIONPROPERTY() and COLLATIONPROPERTYFROMID().COLLATIONPROPERTY() provides information about (wait for...
Have you ever scripted a stored procedure? Or dealt with indexed views? Or wondered why Management Studio says you can't index or alter a table column? Then you've been bitten by one or more of the the ANSI_NULLS, ANSI_PADDING, or QUOTED_IDENTIFIER settings! These settings determine how (and sometimes if) SQL Server can process a query, and the wrong settings can cause a terminal error. Wouldn't it be nice to know what your current settings are? With SQL Server 2005, the sys.dm_exec_sessions DMV can provide nearly all of them. Since it's documented in Books Online I'll let the reader practice...
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].[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)...
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:
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:
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!