Michael J. Swart

October 27, 2009

Apology accepted

Filed under: Tongue In Cheek — Tags: , — Michael J. Swart @ 10:10 am

An open letter to delinquent SQL bloggers.

I accept your apologies which you thoughtfully expressed in posts such as here, here, here, here, here and here. You expressed desires to make amends and reconcile with me and I truly hope you were sincere.

I know that mistakes were made and there are regrets on both sides. But I think pointing fingers would be counterproductive at this stage.

So today, on behalf of SQL Blog readers, I hereby accept your apologies and forgive you all.

Together I think we can get through this difficult time. I think it’s best now if we focus on the present. And I think with patience, time and more consistent blog posts, I will not only forgive, but forget.

Sincerely,
Michael J. Swart
Database Whisperer

All kidding aside. It’s no biggie. I really never miss the lack of posts from any particular author in my RSS feed aggregator. I just mark such apology-posts as read and get on with the day.

October 26, 2009

SQL Snippets is my new favourite thing.

Filed under: Miscelleaneous SQL — Tags: , , — Michael J. Swart @ 8:18 am

Within the last hour, Mladen Prajdić announced a new version of his popular SSMS Tools Pack version 1.7. The main new feature being SQL Snippets. This provides Management Studio an add-in that is very much like the code snippets in other IDEs everywhere.

Also within that last hour, I’ve installed and test-driven the new feature and it is officially my new favourite thing. I’ve got big plans for this feature. I’ve already translated my custom templates to it. In conjunction with templates (and Ctrl-Shift-M), I foresee great things for this feature.

Good job Mladen!

October 14, 2009

A Trick for Aggregating Data Fast

Filed under: Technical Articles — Tags: , , , — Michael J. Swart @ 5:49 pm

I want to explain a trick I came up with that helps an OLTP database do part of the job that OLAP cubes do. (Isn’t that cute, the database engine thinks it’s a cube.*)

If for some reason you find yourself wanting to use an OLTP database (i.e. regular old database) for something that is better suited to an OLAP cube (like Analysis Services for example) then you’re in good company! It seems to be a very common challenge. Even Microsoft seems to recognize this by implementing T-SQL tricks that step over the line into OLAP territory. Grouping sets comes to mind; Also another sign is that CUBE is now a keyword.

Aggregating Data Along a Dimension Before it’s Needed.
One task that is deep in the heart of OLAP territory is reporting the aggregate of a measurement along a dimension. I want to explain how to do this efficiently inside an OLTP db.

In the following examples the OLTP database (and wannabe OLAP cube) will be the standard AdventureWorks database. I’ll be using the Sales.SalesOrderDetail table. The measurement here is the LineTotal field, and the dimension here is the Product.

So we can report on this kind of data easily by maintaining statistics using an indexed view. (BTW, this is a great use-case for indexed views).

Here’s the trick. If n is the measurement, then the only stats we need to store are the sums of n0, n1, n2, grouped by the dimension. In other words, we keep track of the count, the sum and the sum of squares.

CREATE VIEW Sales.v_SalesOrderDetailAggregates WITH SCHEMABINDING
AS
SELECT ProductID,
   SUM(LineTotal) AS Sum1,
   COUNT_BIG(*) AS Count1,
   SUM(LineTotal*LineTotal) AS Sum2
FROM Sales.SalesOrderDetail
GROUP BY ProductID
GO
 
CREATE UNIQUE CLUSTERED INDEX IX_v_SalesOrderDetailAggregates
ON Sales.v_SalesOrderDetailAggregates(ProductID)
GO

Using the Indexed View

So that’s the clustered index I propose we create. As I’ll show later, this is enough. The work of aggregating the data is offloaded to the db. And the work is done once at creation and maintained automatically during any relevant transactions.

In the following code sample, I show how to use this view to report on many common stats (aggregates):

SET STATISTICS IO ON
 
-- a report that aggregates LineTotal over Products
SELECT
   ProductId,
   COUNT(LineTotal),
   SUM(LineTotal),
   AVG(LineTotal),
   STDEVP(LineTotal),
   STDEV(LineTotal),
   VARP(LineTotal),
   VAR(LineTotal)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
ORDER BY ProductID
 
-- the same report (aggregate LineTotal over Products)
-- that uses the indexed view.
SELECT
   ProductID,
   [COUNT] = Count1,
   [SUM] = Sum1,
   [AVG] = Sum1 / Count1,
   [STDEVP] = 1.0/Count1 * SQRT(Count1*Sum2 - Sum1*Sum1),
   [STDEV] = 1.0/SQRT(Count1*(Count1-1)) * SQRT(Count1*Sum2 - Sum1*Sum1),
   [VARP] = (Sum2 - (Sum1*Sum1/Count1))/(Count1),
   [VAR] = (Sum2 - (Sum1*Sum1/Count1))/(Count1-1)
FROM Sales.v_SalesOrderDetailAggregates
WHERE Count1 > 1
ORDER BY ProductID
 
/*
results:
 
(266 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 485, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
(266 row(s) affected)
Table 'v_SalesOrderDetailAggregates'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/

So why not expand the indexed view to include everything from the beginning? Well, there are a couple problems with that. First is that you’ll get an error message like:

Msg 10125, Level 16, State 1, Line 2
Cannot create index on view "AdventureWorks.Sales.v_SalesOrderDetailAggregates" because it uses
aggregate "STDEV". Consider eliminating the aggregate, not indexing the view, or using alternate
aggregates. For example, for AVG substitute SUM and COUNT_BIG, or for COUNT, substitute
COUNT_BIG.

And so what I’ve shown here is that you can use a calculation involving COUNT_BIG, SUM(n) and SUM(n*n) to calculate common statistical aggregates like AVG, STDEV and VAR.

Besides, this strategy also takes the least space, and for a little extra work, you can aggregate on a grouping higher up in the dimension. That is, by using the entries in the index as subtotals in order to generate totals for larger groupings.

*– I’m probably missing an opportunity to include an awesome illustration for this post; something involving databases, cubes and square and round pegs.

October 4, 2009

Spotlight on Brad Schulz Y'all

Filed under: Miscelleaneous SQL — Tags: , , , , — Michael J. Swart @ 5:46 pm

Every now and then, I come across an idea where I think “Man, I wish I thought of that”

Reading Brad Shulz’s blog post The Age-Old SELECT * vs. SELECT 1 Debate was one of those times. In that blog post, he explains how relevant the select expressions are when used inside an EXISTS’s subquery. By looking at the official docs here: http://technet.microsoft.com/en-us/library/ms188336.aspx you’ll learn enough to get by. But after reading Brad Shulz’s article, you’ll never forget.

Brad is relatively new to SQL Server. He spent much of his career so far focused on Visual FoxPro and was a Visual Foxpro MVP for years. SQL Server is a new focus for him and he’s already moderating the MSDN T-SQL forum. It won’t be long before he’ll be able to create test database servers with one hand tied behind his back (Interesting trick: Sit on your right hand and try typing “create test database servers”.)

His blog http://bradsruminations.blogspot.com/ deserves more exposure. I was first introduced to the articles he writes in September as he syndicated his blog at SQLServerPedia.

His style is sometimes hilarious, and funny most of the time, but always clever. I’m always surprised when SQL Server writers manage to keep their articles engaging because of the subject matter. Brad seems to keep up just fine.

Also Must-Read (if only for the laughs)

Brad recently agreed to answer a number of questions I had. (Huge thanks Brad for taking the time!)

Michael J. Swart: What skills in Visual FoxPro (VFP) transfer easily to SQL Server? (Any surprising ones?)

Brad Schulz: Just working with the concepts of databases, tables, columns, relations, and indexes for many years in VFP makes the transfer easy.  And basic SQL syntax has been part of the VFP language since 1991, so that eliminates an enormous learning curve right away… We VFPers have been doing INSERT, UPDATE, DELETE, and SELECT for almost 20 years.

That being said, though, we would still do a lot of one-row-at-a-time processing, because it was easy and it was lightning fast.  But the equivalent of that in SQL (i.e. cursor) is slow as molasses, and I’ve learned a lot of set-based techniques in SQL Server that I had never used before.  Thank goodness that I started playing with T-SQL recently… I think I would have gone bananas if I had started with SQL2000 with no CROSS APPLY or CTE’s or window functions (i.e. the OVER clause).  How did you guys stay sane before SQL2005 came out?

Michael J. Swart: A common story is that many SQL Server professionals got their start as “accidental DBAs”. How did you get your start? Or how did you come to choose SQL Server as a focus?

Brad Schulz: My story’s not of the accidental variety.  I got a B.S. in Computer Science from USC (University of Southern California) in 1981.  I actually started out as a Building Design major, which is a combination of Architecture and Civil Engineering, but I dropped the Architecture class about a month into my freshman year, because I could see that I had no drawing talent whatsoever.  To fill that void, I ended up taking a FORTRAN class independent study with a Computer Science professor.  I gobbled it up, and finished the class in about 2 weeks.  The professor asked me to be a tutor for others taking the regular class.  I changed my major to Computer Science just before my sophomore year.

When I graduated from college, I immediately started working as a float in my parents’ pool.  But after a couple of months, a friend of the family recommended me to a pear-packing company in Kelseyville, California (I found out later they were the largest pear-packer in the state).  They had a computer system that no longer worked and the software company that custom wrote it for them was out of business.  To make a long story short, they hired me as a consultant (God knows why), and I read the manual on the programming language (a form of BASIC on an HP-250 machine) and ended up re-writing their entire system.  From that point, I got work by word-of-mouth, and I’ve been an independent developer ever since.

I worked in dBase II in the early 1980’s, and then eventually moved to dBase III, Quicksilver, Clipper, and then FoxBase+ and FoxPro.  I’ve found plenty of FoxPro work for more than 20 years, but it’s not going to last forever, so SQL Server seemed like the logical choice to move to.

Michael J. Swart: What motivates you to moderate the MSDN T-SQL Forum?

Brad Schulz: Back in the 1990’s I participated in the FoxForum on CompuServe, answering questions about FoxPro.  I enjoyed helping people, and I learned a lot just by helping others, and I learned new ideas from experts who hung out at the forum.  I wanted to duplicate that experience at the MSDN T-SQL Forum.  I’ve learned an incredible amount from some very talented people there.

Michael J. Swart: What motivates you to blog?

Brad Schulz: For one thing, I wanted to get myself known a little more in the SQL community.  Some of my blogs are humorous or tongue-in-cheek, and that’s just a release for the crazy creative side of me.  But I also enjoy writing “how-to” and “discovery” articles… they force me to practice and experiment more with T-SQL.  I wrote several articles for FoxTalk and FoxPro Advisor in the 1990’s, but the turnaround time took forever, from submission through the editing process, and then waiting months for eventual publication.  In contrast, I like the immediate publication of blog posts and getting immediate feedback.

Michael J. Swart: What’s one thing you can say about yourself that would make the SQL Server crowd say “Nifty”?

Brad Schulz: What’s a word that means “stylish; smart; fine; excellent” and rhymes with “thrifty”?  Okay, scratch that.

Let me see… I’m an adopted child whose birth-mother was German Catholic and who played the violin.  I always knew I was adopted.  My birth-mother had requested I be placed with a German Catholic family (which I was, though my mother was the Catholic and my father was the German).  My parents knew about the violin, and figured there was musical talent in me, so they set me up with piano lessons when I was 5.  I’ve been playing piano ever since (45 years).  The weird, spooky part is that I asked for more detailed non-identifying information from the adoption agency when I was about 25 years old, and when I received it, I was floored when it said that my birth-mother worked in and had a talent for “data processing” (whatever that meant in the 1950’s).  Talk about “nature” vs “nurture”… I think the “nature” argument wins hands-down!

I also have a passion for the stock market, which I discovered in 1998.  You can read more about that at my (now-on-hiatus) website http://www.stockciphering.com/whatis.htm.  The stock market is just one huge database of stocks and daily prices, isn’t it?  And so I wrote a suite of Visual FoxPro (VFP) programs to calculate technical analysis figures for stocks and produce buy signals for them, and I created the website to try to ca
pitalize on it.  The website never really set the world on fire, but in “eating my own dog food” and using the data for my own trading, I did very well… From Jan2003 to Mar2009, my IRA (where I do my trading) more than quadrupled in value, compared to the S&P500 which lost 25% of its value during that same period.

 Michael J. Swart: Thanks Brad

October 1, 2009

The ABCs of Management Studio Shortcuts

Filed under: Technical Articles — Tags: , , — Michael J. Swart @ 10:39 am

Today I was watching a webcast hosted by Brent Ozar and Tim Ford (congrats on the MVP awards guys). One topic came up was “which key do you press to execute a query in SSMS?.” F5 seemed to be the default key. However I found out that Ctrl+E did the same thing. This prompted me to enumerate the alphabet and to see what each shortcut combination accomplishes.

I’m also giving the shortcuts a rating from 0-5 based on how often I personally use them. 0 means never. 5 means I’d rather give up my spacebar than this shortcut.

BTW, I’m also not mentioning ALT keys, or SHIFT keys, that comes later. But first “CTRL, CTRL you must learn CTRL!”

Ctrl+A: Select all text (a standard text editor command) : 4
Ctrl+C: Copy (a standard text editor command) : 5
Ctrl+D: Show results in grid : 4
Ctrl+E: Execute (aka F5, aka Alt+X) : 0 but maybe that will improve now that I know about it.
Ctrl+F: Quick Find: 3
Ctrl+G: Goto line number: 0 I don’t use it, but then I don’t have line numbers turned on.
Ctrl+H: Quick Replace: 2
Ctrl+I: The best hidden treasure ever. Hit Ctrl+I and then type some text, you go straight to the next occurence of that string. Try it now, I’ll wait… Wasn’t that so cool? : 5
Ctrl+J: Intellisense’s list members (2008 only) : 3
Ctrl+K: First key of a two key shortcut that can help do lots of things. This is a doorway into cool stuff. : 3
Ctrl+L: Show execution plan. : 2
Ctrl+M: Include Actual Execution Plan : 2
Ctrl+N: New Query window with the same connection as the current one. : 2
Ctrl+O: Open file (pretty standard) : 3
Ctrl+P: Print (Still there for nostalgia purposes) : 0
Ctrl+R: Hide Results Pane : 4 A big help for screen real estate.
Ctrl+S: Save File : 3
Ctrl+T: Show results as text : 3
Ctrl+U: Puts focus on the choose database control (Mnemonic: U is for USE) : 4
Ctrl+V: Paste (a standard text editor command) : 5
Ctrl+X: Cut (a standard text editor command) : 4
Ctrl+Y: Redo (a standard text editor command) : 4
Ctrl+Z: Undo (a standard text editor command) : 4

So there you go, Seemingly still up for grabs is Ctrl+Q and Ctrl+B. These don’t appear to do anything.

Powered by WordPress