The HOBT

The HOBT is Moving!

2009-10-03T23:16:00.001-04:00

Some exciting news today – I’ve been invited to blog over at SQLBlog.com! SQLBlog is the home of some of the brightest minds in the SQL Server world, so I’m still a bit shocked that MVP Adam Machanic thought highly enough of my content to ask me to blog on his site. I’m going to try to keep a low profile over there so he doesn’t realize the mistake that he’s made ;-)

My continual aim has been to reach as many people as possible with the content that I publish on The HoBT, and the decision move to SQLBlog was made with that in mind. The sheer number of talented people that write over there has resulted in a pretty significant following, and I’m of the mind that the more people that I can help or confuse, the better. I thrive on feedback, corrections, and smart-assed comments, so I hope that you’ll update your RSS subscription and pick up the blog at it’s new home over on SQLBlog.com!

RSS Feed: http://sqlblog.com/blogs/aaron_alton/rss.aspx

Main Blog: http://sqlblog.com/blogs/aaron_alton/

So You Want To Learn Business Intelligence, Eh? Part 5: SSAS Deployment and Administration

2009-10-02T09:54:00.000-04:00

The fifth installment in this blog series (this one rather belated – apologies if you’ve been following along) is about SSAS Deployment and Administration.

If you’re from a “core” SQL Server background, you’re in for quite the shock when you try to administer an Analysis Services instance. Very (and I do mean very) little of what you know from traditional database administration is applicable in SSAS. That said, a lot of what we DBA’s spend our time troubleshooting on the relational engine side (locking and blocking, for instance) simply doesn’t exist in Analysis Services.

The authors do a good job of introducing the main tenets of Analysis Services administration. In order, the reader is walked through the following:

Partition Design, which is a bit more involved than the partitioned indices and views of the ~~normal~~ relational world.
Aggregation Design, or the process of finding the balance between query response time and cube processing time/space.
Cube Deployment, and the various methods available to you to deploy your Analysis Services projects.
Processing cube objects. If you’re into that sort of thing.
Locking down SSAS – roles and security assignment. Much simpler than relational engine security, so there’s no excuse not to do it right ;)
High-Availability Strategies and DR. And NO, they’re not the same thing.
Tuning and monitoring.

As is the case in the relational world, administration is a big job and needs to be taken seriously. The self-paced training kit provides a good introduction into SSAS administration, but you need to supplement it with other sources in order to get a decent understanding of the subject. Books Online is (as always) very handy in this respect, as are the many BI blogs that you can find on the intertubes.

Tune in tomorrow for data mining. I mean…. TUNE IN TOMORROW FOR DATA MINING!!!

SQL Server MVP Deep Dives: One-of-a-Kind Content That Supports a Great Cause

2009-09-29T21:57:00.001-04:00

About a year ago, Paul Nielsen proposed that the SQL Server MVPs collaboratively write a book whose proceeds would benefit a globally recognized charity: War Child International. Simply put, the MVPs who collaborated to write this book have donated their time, experience, and wisdom to a great cause. You can see a complete list of the authors involved on the book’s site at http://www.sqlservermvpdeepdives.com

I can’t say enough about the people who were involved in this project. Writing a book, or chapters of a book, is not an insignificant effort. It is a very time-consuming task, and the fact that they did it for charity simply makes it that much more amazing.

I will be picking up a copy, and I hope that you’ll do the same. The e-Book is expected to be available shortly, whereas the printed version is expected later this year. If you purchase your copy through http://www.sqlservermvpdeepdives.com, WarChild will receive an extra 10% of the purchase through the publisher’s affiliate account.

Lousy HA is Not Necessarily Better Than No HA

2009-09-19T13:59:00.000-04:00

In fact, it can be worse. And no, I’m not just being a retentive purist. Hear me out.

High-Availability is the ultimate goal of most corporate IT departments. It’s annoyingly measured and spouted by counting the “number of nines” that your HA solution delivers. And there is no shortage of technologies designed to help you obtain your uptime goals: Multipathed SANs, Mirrored Multipathed SANs, Clusters, Geographically-Dispersed Clusters, ESX Farms….the list goes on. For the most part, the vendors of these technologies aren’t foolish enough to claim that their solutions alone are enough to deliver on the promise of uninterrupted uptime, but somewhere along the line all of the HA-hype has done a number on the collective consciousness of the IT world.

“You’re running a cluster? You’re lucky – you must sleep well at night!”

Hahahoho…

At one time, there was an entry barrier into the world of high-availability that stopped most companies from venturing into this space: cost. Quite simply, it cost a bloody fortune to throw together, for instance, an MSCS cluster. For starters, the entire cluster solution had to be certified for running an MSCS cluster. Not the individual components, but the combination of components, right down to your SAN firmware version. That reduced your choices to a handful of (usually very expensive) solutions. But this wasn’t so bad – it just weeded out “real” availability requirements from “fake” ones. Application owners could request five nines of availability, but when they saw the price tag, suddenly they could somehow afford more than five minutes of downtime per year. That’s not to say that all HA requirements were BS – just that costs and budgets kept requirements in check.

Thanks to the incessant advancement of tecknowledgee, HA solutions are becoming more commonplace. For instance, with Windows Server 2008 Failover Clustering, you no longer need a certified cluster solution. Sure, each component needs to be suitable for a cluster, and the entire cluster needs to pass certain validation tests, but the requirements are significantly relaxed from the days of MSCS clusters.

This is great news, right? It’s the modern day equivalent of a chicken in every pot! A cluster in every server room! What’s that? You don’t have a server room? Well, a cluster in every supply closet!

OK, I might be getting a bit carried away, but you get the picture.

So where’s the problem with all of this? I mean…isn’t cheaper better? And of course it is, I say. But we can’t get ahead of ourselves.

There is no shortcut to high-availability.

Before we start promising to deliver uptime out the wazoo, we have to learn to think for high-availability. From a 40,000 foot view, it’s hard to imagine that implementing a high-availability mechanism can actually cause damage, but it can. Let’s take a look at some of the side-effects of half-assed HA implementations, shall we?

1. You can actually reduce the uptime of your applications
Say what? How can implementing a HA technology reduce the uptime of your applications? Quite easily, actually. Let’s take a typical “dual everything” server. Dual NICs, dual power supplies, dual RAID controllers, a direct-attached RAID array. You can survive the failure of most of the “fragile” bits of your server, replace the offending hardware, and be back in business. But there are a few single points of failure…the OS for one, the motherboard. This isn’t ideal – we’ll never make five nines like this! We need more HA! We need (drumroll please) a cluster!

So you implement a “simple” active/passive 2 node cluster. A couple of servers, or more commonly blades. An iSCSI or fibre channel SAN. You roll it into production, and everything is beautiful. Set it and forget it, right?

Wrong. Do you have anyone qualified to manage that SAN? Do you have anyone who knows a cluster from their elbow? Are you monitoring your new technological valhalla? No?

OK…so let’s say you neglected to enable multipathing on the SAN. Now you have far more single points of failure than you did with your standalone server. Or let’s say that, in trying your darndest to create a new LUN, you end up clearing the SAN configuration. Or a routine firmware flash fails, and your SAN won’t come online. Or let’s say that you’re not monitoring cluster failover events, so you don’t even notice that node 1 went offline in the middle of the night. Trust me…you’ll notice when node 2 does the same.

You see, a wise person once said “complexity is the enemy of security”. An equally wise person ~~stole~~ borrowed the phrase and applied it to availability – for complexity is indeed the enemy of availability. Without the skills and infrastructure to back up a HA solution, the complexity that you’re introducing can actually decrease your availability!

2. You can cripple your DR strategy
Half-assed HA can hurt your disaster recovery strategy as well, in two important ways:
i. “We have (clustering/SAN mirroring/multipathing)! We don’t need a DR strategy!”.
Wrongo. Nothing replaces backups, and even the best HA solution will call on backups from time to time. Don’t believe me? Well, how does your cluster help you when your database is suspect? Toldja so.
ii. “We’re backing up to a network share every five minutes!”. Great! Did you check to make sure that said “network share” isn’t using the same storage subsystem that your production servers are hosted on?

3. You can create false confidence
A business that has faith in it’s “highly available” infrastructure will learn to lean on it more and more. And this is a good thing – it means that the technology we all work so hard to implement and maintain is paying dividends. But to your users, promised HA is the same thing as real HA. They trust that, when you promise them 99.999% uptime and <5 minutes data loss, you know what you’re talking about. So much so that they may choose not to develop backup plans should the unthinkable happen. And when it does happen, the business can be seriously injured (or even destroyed), because they can’t meet their contractual obligations/can’t meet reporting deadlines/can’t ship their product. Think about it before you promise the sun, the moon, and the stars, because…

4. You can be out of work
You can be the CIO’s poster child, but if you promise something you can’t deliver, and the fallout seriously impacts the business, you had better have your CV up to date. Have you really thought about what it takes to deliver any measure of high-availability? Let’s take a typical SQL Server database application. What does it depend upon? For starters, the obvious: the availability of the database. Which depends upon the OS and physical hardware being up and functioning. That’s it, right? Not quite. How about:
- The physical network connecting your application to your clients
- The application servers and/or terminal servers, and all of their dependencies
- DNS, DHCP, and Active Directory (what good is an application if your Windows Authenticated users can’t log in?)
- The security of your application infrastructure (an application that is down because of a hacker or a disgruntled employee is no more available than an application that is down because of an infrastructure failure)
- The power that runs the whole shebang

And there may be more, depending upon your environment. “But wait!” you say, “Those things aren’t my problem! I’m just a DBA!”. True, you may be “just a DBA”, but have you documented your dependencies on items that are outside of your control? Have you obtained SLAs from your network admins, your security admins, your SAN admins…..to support the SLA that you delivered to your application owners? Trust me – the corporate chopping block will be much more sympathetic to your plight if you have already documented your dependencies on external factors, before the proverbial excrement hits the fan. Any amount of finger pointing after the fact comes across as just that – finger pointing. And good managers don’t brook lousy excuses.

Now, let’s not misconstrue the message. I’m not saying for a second that you shouldn’t try to implement highly available infrastructures in your environment. Nor am I saying that you’re an idiot if you have rolled out a half-assed infrastructure. The message I’m trying to impart is that you need to examine your “highly available” solution from every possible angle before a disaster makes you wish that you had.

See you next time.

Surveys, Get Yer Surveys Heah!

2009-09-08T19:59:00.001-04:00

I’m sorry – I just didn’t know any other way to make surveys sound exciting.

Peter Saddow asked the SQL community to put a plug out for a few SQL Express surveys that Microsoft has developed. The results of these surveys will directly influence the development of the SQL Express installation experience, so if you work with SQL Express, please take the time to fill them out. Or, as Peter more eloquently said:

Microsoft is committed to making the SQL Server Express installation experience the best in industry. For us to achieve this goal, we need your candid feedback on your experience with installing SQL Server Express. If you have used several different versions of SQL Server Express, focus on the latest version that you have used. When SQL Server 2008 Customer Technical Preview 3 is released, feel free to complete this survey again since several installation improvements have been made. This information will be used to improve our future releases including SQL Server 2008 R2 and SQL Server 11. This survey will be available until the end of November.

Please visit the following blogs that contain instructions:

General survey around installing SQL Server Express

http://blogs.msdn.com/petersad/archive/2009/09/08/general-sql-server-express-survey.aspx

Survey that focuses on embedding SQL Server Express

http://blogs.msdn.com/petersad/archive/2009/09/08/embedding-sql-server-express.aspx

Seriously. Fill them out. And blog about it, or “retweed”, or whatever it is you kids do nowadays.

Treating The Results of a Stored Procedure as a Table Expression

2009-08-25T09:41:00.001-04:00

I stumbled upon a StackOverflow thread this week, where the poster was asking how they could fetch the results of a stored procedure into a temporary table without knowing the resultset’s composition (column layout) in advance. If your first question is “why?”, good for you. If you’re trying to capture the resultset returned by a stored procedure without knowing it’s composition, how in the world can you possibly hope to use said result set?

Whatever the case, the OP clearly wanted to do so. They had gotten as far as concluding that the SELECT…INTO…FROM syntax (which creates the object specified after the INTO keyword) would do the heavy lifting for them, if only they could treat the stored procedure as a table expression (if you have no idea what I’m talking about, check out the examples below). The normal use of the SELECT INTO syntax looks something like this:

SELECT *  --Don't use *.  You didn't see this here.
INTO #MyTempTable --Drop the hash to create a permanent table.
FROM MyRealTable

You can’t substitute a sproc name for your table name, and expect it to work. What you can do is use OPENROWSET or OPENQUERY. Have a look here:

CREATE PROC MyProc
AS
BEGIN
    SELECT * FROM sys.databases  --Or whatever.
END

GO


SELECT * 
INTO #MyTempTable 
FROM OPENROWSET('SQLNCLI', 
                'Server=(local)\SQL2008;Trusted_Connection=yes;',     
                'EXEC MyProc')

SELECT * FROM #MyTempTable

Neat, eh? Keep in mind that you need to have “Ad Hoc Distributed Queries” enabled:

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO

sp_configure 'Ad Hoc Distributed Queries', 1
GO

RECONFIGURE
GO

Here’s the original thread – there are a number of very good contributions on the thread.

Thanks to SQL MVP Rob Farley for putting a name to what the OP was asking for – the title of this article was a direct “yoink” from his comment on my post. I would have called it “How to bring back the results of a stored procedure into a temporary table even though you don’t know what the results look like at design time”. I ran short of breath just writing that one.

See you next time.

So You Want To Learn Business Intelligence, Eh? Part 4 – Extending SSAS Cubes

2009-08-23T20:45:00.001-04:00

Time for more multi-dimensional fun. If you read the previous article in this series, we finally started digging into the cubey goodness that is SSAS. Today we’re going to look at Chapter 6 in our trusty guide (MCTS Self-Paced Training Kit (Exam 70-448): Business Intelligence Development and Maintenance). In this chapter, we start pimping our SSAS cubes; implementing hierarchies, creating KPIs, Actions, Translations, and Perspectives, and creating calculations and queries in (gasp!) MDX.

I introduced hierarchies in the last blog post, but for those who ~~were asleep~~ didn’t catch that post, a hierarchy is….well…a hierarchy. An example is probably better than my weak, recursive definition.

A Time Hierarchy
Year
Quarter
Month
Week
Day
Hour
Minute
Second

Establishing a hierarchy serves two purposes: a usability benefit for the end user (they can browse data at any level in the hierarchy, without having to search for disparate time dimensions), and a performance benefit for SSAS. How’s that? Well, much of the reason why SSAS is able to present aggregated data to the end user so quickly is that it pre-aggregates said data. Usually, the most frequently queried aggregations are stored, and the balance are calculated upon request. By implementing a hierarchy, SSAS can use a lower level aggregation to “roll up” to a higher level aggregation. For instance, if we already have an aggregation at the Day level, a Month aggregation can be calculated very efficiently. As usual, the book did an excellent job of presenting the concept of hierarchies, and of explaining how to implement them.

KPIs are one of the biggest buzzwords associated with modern Business Intelligence. For the initiate, a KPI is a quick indication of the degree of success with which a business is meeting it’s goals. What those goals are depends upon the organization and the KPI consumer, but some easy examples are Customer Profitability, Inventory Turnover, Expenses as they relate to Revenue, etc. In defining a KPI, you provide SSAS with instructions on how to calculate the current value of your metric, your target value (goal), your KPI’s status (value as it relates to the goal – are we well under the target? Approaching the target? Have we exceeded the target?), and the trend (are you trending up, or trending down). There are, of course, more properties available, but the four I just mentioned are the key properties. A KPI is what powers those lovely gauges and stoplights we see so frequently on BI dashboards.

Actions simply allow you to perform additional activities within your cubes, provided that your client software supports said activities. Good examples of cube activities include linking out to a website, drilling into detail data, and linking out to an SSRS report. Chapter 6 walks you through the process of creating a drill-through action in detail.

Translations are…just that. They translate the data – for instance, presenting data in languages other than the cube data’s primary language to international users. Of course, SSAS doesn’t actually perform the translation – your datawarehouse needs to contain translated dimension attributes, but a translation allows the end user to select one of your defined languages and see all of their cube data in the translated language.

You can think of a perspective as a subset of a cube. The book’s authors are quick to note that perspectives are not security mechanisms – they simply exist to allow you to present a relevant subset of a (potentially huge) cube to a business user.

Finally, in lesson 3, we took a run at the dreaded MDX. I won’t even try to explain MDX here, save to say that it is indeed deceptively similar to TSQL. My one beef about the book to date is that the coverage of MDX was sparse and rushed. The authors jumped right into multidimensional syntax without really walking us through the basics of the language. As a result, the reader is left to wonder why a member is expressed as [Product].[Product].[Mountain-100] (the AdventureWorks Mountain Bike 100). Why [Product].[Product]? Why does the member name have two parts? Is this always the case? In their defense, you could probably work with cubes for years without needing to write very much MDX at all: the designers built into BIDS generally “write” all of the MDX for you. And though the exam doesn’t place much weight on MDX, I still believe that you need to understand it in order to really understand SSAS. Unfortunately, you’ll need to spend a bit of time on MSDN before you can really wrap your head around MDX.

And that’s it for this week. See you next time, when we’ll talk about SSAS deployment and administration. Stop yawning. After that, it’s data mining time, baby.

The Transact-SQL OVER Clause: Not Just For Ranking Functions

2009-08-07T11:26:00.001-04:00

If you’ve been following this blog for a bit, you may have seen the post I made back in March on Ranking Functions. In it I introduced ranking functions: RANK(), DENSE_RANK(), and ROW_NUMBER(). I also introduced the OVER clause, and how it is used to order and partition the ranking logic.

Until now, I didn’t realize that the OVER clause could be applied to aggregate functions as well. Earlier this week, I dug up this little nugget from BOL.

SELECT SalesOrderID, ProductID, OrderQty

    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'

    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'

    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'

    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'

    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'

FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);

Holy cow, Batman! How did I miss that? The partitioned aggregates very efficiently perform subtotals based upon the OVER clause that you define. Prior to the OVER clause, we would have needed to create a derived table which GROUPed the query by our partition columns, then joined said table back to our parent query. This method is much cleaner, and much more efficient, as you aren’t running through your results twice (one for the aggregate, and again to fetch back the “rows” themselves).

This opens up a lot of possibilities, so it’s a good tool to have in your belt, so to speak.

Tune in later this week, as we return to our regularly scheduled BI series.

So You Want To Learn Business Intelligence, Eh? Part 3 – Developing SSAS Cubes

2009-07-31T10:37:00.000-04:00

Here comes the fun. Once again, if you’re just joining the party, this blog series walks through the Microsoft Press Training Kit for Exam 70-448: Business Intelligence Development and Maintenance. In previous posts, we’ve worked through SSIS as it pertains to loading data warehouses.

Now we’ve arrived at the fun bit – SQL Server Analysis Services.

Chapter 5 in the self-paced training kit, introduces the concept of an SSAS cube. The whole multi-dimensional concept stymies some people a bit, so let me break it down as well as I can. Picture a load of sales data in a traditional, OLTP database; say, for instance, an order detail table. Such a table contains one line for each item on each sales order. It’s useful for transacting, and for simple analysis queries (for instance, total sales per product), but once we start to get into trickier analysis requirements, an OLTP structure begins to fall down. For instance, let’s say we want to see how sales for a given product is trending with respect to time. Well, to solve that kind of a problem using traditional TSQL, we’d need to do something like this:

Define the time interval that we’re using to “bucket” our sales. For example, we could compare sales from one month to the next.
Write a query to GROUP our order lines by that date interval, by product (GROUP BY YEAR(MyDate), MONTH(MyDate), MyProductID). In said query, summarize order quantity by product (SUM(OrderQuantity))

Fairly straightforward, right? If this was all that we needed to do, and we didn’t care a hoot about performance, a TSQL solution would be perfectly acceptable. Dump the results into Excel, chart it, and voila. Unfortunately, reality is seldom that straightforward. More often than not, business users want a bit of flexibility in their reporting and analysis. Perhaps they might want to drill in on an interesting item, and see how the sales for said item are trending on a week-by-week basis. Or perhaps they want to view sales by region by product over time. With a TSQL analysis solution, you’re going to end up with an awful lot of specialized queries to write and maintain. Add to that the fact that aggregations and grouping in a relational engine are generally anything but quick, and you’ll start to see the business case for OLAP.

Picture a cube as a combination of data values (quantity sold, total purchases, and the like) and their attributes (customer, order date, product, etc). The data values are collectively referred to as measures, and are derived from tables in our data warehouse called “fact tables”. The attributes are termed “dimensions”, and are derived from tables in our data warehouse called “dimension tables”. Dimensions can be organized into hierarchies, further facilitating data analysis. The most common hierarchy is of course time (Year-Quarter-Month-Week….), but many other hierarchies will likely exist in any given set of dimensions (ex. Sales Region; Country-State/Province-City-Zip/Postal). The performance boon kicks in when you factor in aggregations, which are pre-calculated summaries of measures across one or more dimensions.

The training kit digs into these concepts and much more in chapter 5. There’s quite a lot of information in there, so I went slowly and made sure that I internalized the information as I went. Given that it’s a pretty complex topic to tackle, I feel that the chapter did a very good job introducing the reader, and bringing them up to speed on the basics of SSAS cubes.

Next time, we’ll be looking at some of the more advanced aspects of cube design: KPIs, Actions, Translations, and the like.

So You Want To Learn Business Intelligence, Eh? Part 2 – Deploying and Administering SSIS Packages

2009-07-30T17:18:00.001-04:00

In case you’ve missed the first two posts, this series is about working through the curriculum for exam 70-448: Microsoft SQL Server 2008 Business Intelligence and Maintenance. You can find the introduction here. The first two chapters of the MSPress Training Kit are covered here.

In this post, we’re going to cover off on chapters 3 and 4, which cover the deployment and administration of your completed SSIS packages. Now if you’re anything like me, you’re probably asking yourself…where’s the BIeef? I didn’t think this was an SSIS curriculum. And if you are anything like me, you probably need to chillax a bit too. ETL is a necessary part of any Business Intelligence project, and SSIS is a particularly slick ETL toolset. That said, the next chapter is the start of the SSAS portion of the curriculum, so the coolness is right around the corner.

Chapter 3 focused on the deployment options available in SSIS. Though it may be tempting (especially in smaller environments) to write off formal package deployments as “unnecessarily complex”, this would be a mistake. Deployment via the Deployment Wizard or by using DTUtil allows you to standardize the deployment process, thus reducing the possibility for operator-induced error when deploying a package to multiple environments (say, test and production). It also allows for a more streamlined deployment process should you need to need to recreate your packages for any reason (think DR). As with the previous chapters, I found Chapter 3 to be very well written and comprehensible. The exercises are particularly well-written, so make sure that you take the time to go through them.

Chapter 4 discussed the administration of SSIS packages. It introduced the SSIS service, as well as the key folders and files required for SSIS to properly function. It then moved on to securing SSIS packages (sadly, an often-overlooked aspect of SSIS administration), and encrypting sensitive data (for example, connection strings). The authors then introduced DTExec and DTExecUI, and the various parameters and switches that we can use to control package execution.

Overall, the SSIS section was very well written, and almost completely error-free. This isn’t always the case with technical publications, so this resource kit is a refreshing change so far.

Come back tomorrow where we’re going to start to tackle the fun bit: SQL Server Analysis Services.

Simple Change Data Tracking in SQL Server – Presentation Tomorrow!

2009-07-14T20:55:00.000-04:00

In case you aren’t following along on Twitter, Jeremiah Peschka somehow managed to twist my arm into delivering a presentation for the AppDev Virtual Chapter tomorrow, from 2 to 3 PM. The subject: change data tracking in SQL Server. I’ll be going over two of the most common methods for tracking data changes – DML triggers and SQL Server Change Data Capture (CDC).

It will be the event of the ~~year~~ ~~week~~ day, so don’t be left out. I’ve been told by a liar that I sound like Barry White – you wouldn’t want to miss that, would you?

Registration is not required, so you don’t need to worry about being spammed – just follow the link below to join the fun.

>>Where You Want To Be Spending a Wednesday Afternoon<<

So You Want To Learn Business Intelligence, Eh? Part 1 – SSIS Development, Error Handling, and Logging

2009-07-09T20:48:00.001-04:00

In case you missed my post last week, I have begun to work on preparing for exam 70-448 (SQL Server 2008 – Business Intelligence Development and Maintenance). This all came about because a number of people in the SQL Server community expressed a desire to “learn BI”, myself included. Being of not entirely sound mind and judgment, I volunteered to take point. Hopefully I don’t get shot in the process.

Our guidebook for this adventure is the MSPress Self-Paced Training Kit, and I hope you’ll follow along. The book is broken down into thirteen chapters, and I’ll be covering one or two per week. Probably two. I’m impatient that way.

Anyhow, without further ado – let’s dig into the material. If you’re not familiar with MSPress training kits, they’re a pretty sweet deal. For $55 CAD (roughly $8.95 USD) you get the manual itself, a suite of MeasureUp practice questions, and a discount code for 15% off your exam registration fee. 15% off of $125 USD is a discount of just under twenty bucks, so the $55 purchase price is really a no-brainer.

Of course, the value really depends upon the quality of the material. Fortunately, this book’s authors (Erik Veerman, Teo Lachev, and Dejan Sarka) really have it together. Chapters 1 and 2 were very well written – they’re logically structured, and written in very plan, easy to understand language.

Chapter 1 introduces SQL Server Integration Services. There won’t be any surprises here for the seasoned SSIS user, but if you haven’t had the chance to use SSIS yet, this chapter will serve as a very nice introduction. It starts out with an introduction to Business Intelligence Development Studio, and shows you how to create a new SSIS package within BIDS. The chapter then moves on to the creation of Project Data Sources, which are a method of centralizing connection information for the data sources and destinations used in the package. At the end of the first lesson, the author covers off on creating Package Connections from your Project Data Sources. If you think of a package data source as an object, and a package connection as an instance of said object, you won’t be too far off the beaten track. You can have package connections that don’t reference Project Data Sources though, so make sure that you understand the difference.

The second lesson in chapter 1 is about creating and editing control flow objects. If your most recent ETL experience is with DTS, you might start cursing at this point. Simmer down. SSIS is a completely different animal. I cursed the first time that I opened up SSMS in SQL Server 2005 and couldn’t find my precioussss DTS. In fact, I cursed the first few times. Of course, the SQL Server engineers didn’t make SSIS complex for kicks – it’s complex because it allows for immense flexibility. Control flow objects perform actions in your SSIS packages – anything from sending an email to transferring a file via FTP. Control flows also provide for the use of control flow containers, which allow you to control transactional consistency and implement looping structures. Control flows also contain data flow tasks, which are themselves containers for all of the elements that make up a data flow – for instance, connecting to a data source, extracting information, manipulating it, and loading it into a destination. Data Flow adapters and transformations are covered in chapter 3.

Chapter 2 discusses the methods available to you for debugging and error handling in SSIS. Error handling is very robust, with a full array of logging and data redirection options available to you. One of the most useful error handling features in SSIS is the ability to use package checkpoints. Checkpoints save your package’s state should an error occur, allowing it to “pick up where it left off” once you correct the error. This chapter also goes over the ins and outs of transactions, which (like their TSQL cousins) allow for atomic behaviour in your SSIS packages. One thing that I have found to be particularly useful about SSIS transaction handling is the ability to exempt portions of your package from a transaction rollback – for instance, the tasks that are responsible for logging our the reasons why your package is rolling back in the first place.

…

Altogether, the self-paced training kit has been a treat to read so far. I’m anxious to get to data mining and cube design, which is what really motivated me to work towards this certification, but it’s good to flesh out your knowledge in all of the supporting technologies first.

Stay tuned for more BI goodness next week.

So You Want To Learn SQL Server Business Intelligence, Eh?

2009-06-25T01:27:00.001-04:00

So do I. In fact, in Tim Ford’s recent chain blog “Give Me a Coconut and Six Months” (Tim’s post, my response), almost all of the bloggers that participated indicated that they’re interested in learning about SQL Server Business Intelligence.

Why is that? Well, Business Intelligence carries with it the promise of unlocking tremendous amounts of value in the data that we gather and maintain every day. As database administrators, we are naturally very interested in anything that promises to squeeze additional benefit out of the information that we work so hard to protect. Plus, all of those BI guys keep telling us about the fame, fortune, and delicious food that the magic of Business Intelligence brings.

I just placed my order for the MCTS Self-Paced Training Kit (Exam 70-448): Microsoft SQL Server 2008 – Business Intelligence Development and Maintenance. Why do I all of a sudden feel a little nauseous?

Maybe it’s the thought of all that denormalizing going on in the analysis services cubes, or the concept of learning MDX (Multi-Dimensional Expressions), which I’m given to understand is an unholy marriage between my beloved Transact-SQL and a Escher illusion.

MDX
=
T-SQL
+

I’m kidding, mostly. I do have some experience with SSRS, and I regularly maintain SSAS installations, so those parts of the curriculum at least won’t be entirely new. I’m looking to dig into the nitty-gritty details surrounding BI design and implementation, and this certification should be a good start.

Anyway, the reason why I wrote this post isn’t because I was bored and thought I’d share my latest purchase with you. It was actually to invite you along for the ride; to whatever extent you would like to go. I’m going to blog every week about my experiences with the curriculum so far – I’ll include a high-level overview of the chapter(s) that I’ve covered, any difficulties or inaccuracies that I noted, as well as links to other resources that I found useful. If you have the time and inclination, go ahead and grab a copy of the book – it’s always better to have somebody to bounce your observations or problems off of, and I’m happy to be that someone. If you aren’t quite ready to dive headfirst into SQL Server Business Intelligence, feel free to follow along anyway. While I won’t be regurgitating the curriculum word-for-word, I hope to provide enough insight into BI that it’ll take some of the mystery away for those who would like to dig into BI at some point in the future.

Wish me luck!

A Publisher’s Responsibility

2009-06-24T14:23:00.001-04:00

Over the last few weeks, I have closely followed a couple of conversations on Twitter, wherein various people were debating the responsibility of a publisher when it comes to ensuring that the content that they put out is correct and conforms to best practices. On each occasion, the conversation started because a major technical resource site (two different sites, actually) published an article that advocated behaviour that doesn’t correspond to established SQL Server best practices.

Two interesting (and very incompatible) perspectives were raised. In each case, the site’s representative believed that it was within the community’s power to judge the validity of an article by using the voting tools supplied on the site. They conjectured that, by down-voting a poor article, said article could do no harm as anyone reading it would take the advice with a few grains of salt. Also in each case, the people questioning the content believed that it was the publisher’s responsibility – not the community’s – to validate that the advice being provided on their site was sound and accurate. Both sides stuck to their guns, and not much seemed to come out of the whole debate.

Without getting into details or naming names, the first site’s representative has since gone on to state that they are now screening articles more critically than they did before. The second site’s representative said…not much…so we’ll have to see whether or not they take steps to keep out the riffraff.

The whole issue has been consuming idle threads at the back of my mind ever since, so I thought I’d blog my thoughts and offer a chance for others to comment.

I believe that the responsibility for ensuring that published content is accurate and valid belongs to the publisher. Here’s why I believe so strongly that this is the case:

The publisher has built a reputation by providing sound content. By virtue of the fact that an article is published on SomeBigSQLSite.com, the unsuspecting reader may very well assume that said content has already been vetted. While the seasoned SQL professional may be able to separate fact from fiction, a novice likely would not.
Comments and ratings are easily glossed over. If I’m looking for a quick bit of information, I’m likely to miss the ratings altogether. If, on the other hand, an article that consistently got poor ratings was surrounded by a very prominent warning, indicating to the reader that they should proceed with caution, one could almost argue that the “community moderation” theory holds water. Except for…
…those poor few keeners who read the article before somebody in the know has the chance to downvote it. I believe that published advice should be safe for anyone to follow from the minute it is published, not just after the hours or days required to accumulate enough downvotes for the content to be considered “suspsect”.
The community doesn’t get paid to edit or moderate a site’s content – the revenue stays with the site owner, and so should the responsibility. If it doesn’t, I think I’ll register SomeBigSQLSite.com and start accepting submissions from anyone with a heartbeat and a cheap asking price ;-)

So what do you think? I’m interested in hearing your perspective on the issue, so please feel free to share it below. No references to actual sites or people, though – I hope that publishers will listen to common consensus (whatever that may be) without having their noses rubbed in it.

Speak up!

Tricky Updates: Weird, Wacky, and Wonderful UPDATE Syntax Variations in SQL Server

2009-06-22T12:32:00.001-04:00

If you’ve been working with SQL Server for any length of time, you’ve probably seen a number of different variations of the UPDATE command. From the miraculous to the mundane, the UPDATE command has as many variants in TSQL as….something with a lot of varieties of stuff has things. Damn, it’s late.

Anyway, today’s blog post is more like a public service announcement. It seems that all too many developers are still dangerously unaware of the ability to create very ambiguous UPDATE statements in TSQL.

What am I blithering on about? Well, here’s a couple of simple update statements (using AdventureWorks2008):

--Update the comment field on ALL orders

UPDATE Sales.SalesOrderHeader

SET    Comment = 'Unqualified Update'



--Update the comment field on all orders worth more than 10k

UPDATE Sales.SalesOrderHeader

SET    Comment = 'Qualified Update - High Dollar Orders'

WHERE  TotalDue > 10000.00

Straightforward, right? The first one sets the “Comment” field to “Unqualified Update” for all rows. That’s a bit scary in and of itself, and more than one person has suggested a compiler option to warn about performing unqualified updates. Unqualified updates are today’s topic though. The second script performs a qualified update, setting Comment to a new value for all orders worth more than 10k.

Now how about this?

--Update the comment for orders without a PO Number

UPDATE Sales.SalesOrderHeader

SET    Comment = 'This order is for Territory 1'

FROM   Sales.SalesOrderHeader

JOIN   Sales.Customer ON Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID

WHERE  Sales.Customer.TerritoryID = 1

Now we’ve added in a FROM clause, and we’re joining in another table (Sales.Customer) to facilitate updating the comment for customers from a single territory only. Still no rocket science, right? Good.

Howsabout this one?

--Update the comment for orders that contain red products

UPDATE Sales.SalesOrderHeader

SET    Comment = 'This order contains red products!'

FROM   Sales.SalesOrderDetail sod

JOIN   Production.Product prod ON sod.ProductID = prod.ProductID

WHERE  prod.Color = 'Red'

Same thing, right? We’re joining to the SalesOrderDetail table, to update the comments for orders containing red products. Or are we? If you’re following along at home, try running the update. Note how many rows were updated (I get 31465 rows affected). Now see how many records are in Sales.SalesOrderHeader. Same number, right? Maybe red is really popular.

Maybe not. I have about 6k orders with red products on them. So what happened? Notice that I didn’t reference Sales.SalesOrderHeader in the FROM clause? Ruh-roh. We effectively coded an all-or-nothing update – if ANY of the order had red products on them, they ALL got updated. And that’s where we get into trouble. An UPDATE doesn’t really demand anything of the FROM clause that a SELECT statement doesn’t demand. Technically, you don’t need to specify the table being updated in the FROM clause, as I just demonstrated.

There are a great many examples of this kind of ambiguous UPDATE syntax. Sometimes the unrestricted UPDATE syntax can be a boon, but all too often it’s a source of error, when developers don’t realize that they’ve made an omission. MVPs Adam Machanic, Steve Kass, Itzik Ben-Gan, and Erland Sommarskog seem to be particularly skilled at finding these kinds of disasters in the making – each of them has blogged or commented about them at great length in the past.

One of the aforementioned fine gentlemen (and I honestly can’t remember who – I think it was Adam) suggested a method for preventing this kind of issue – to always use aliases in the update statement. Here’s an example:

--Update the comment for orders that contain red products

UPDATE soh

SET    soh.Comment = 'This order contains red products!'

FROM   Sales.SalesOrderHeader soh

JOIN   Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID

JOIN   Production.Product prod ON sod.ProductID = prod.ProductID

WHERE  prod.Color = 'Red'

By aliasing Sales.SalesOrderHeader, I can “update the alias”, rather than “updating the table”. In “updating the alias”, I make it impossible to omit the table being updated from the FROM clause.

I now use the alias syntax exclusively, when coding an UPDATE with a FROM clause. Unless there is a very good reason not to do so, I recommend that you do the same – unless of course, you really like red products ;-)

So Apparently I’m On a Deserted Island…

2009-06-17T09:53:00.000-04:00

I’ve never really met Tim Ford, but apparently he’s an ingrate. About a week or so ago, he ended up on a deserted island. Something about moonlighting for FedEx and running out of fuel over the Atlantic….

Tim After a Week on the Island

Now, being upstanding members of the SQL community, we all set out in scout planes looking for him. Bad idea.

You see, like I said before, Tim is a bit of an ingrate. If a bunch of people got together and tried to save me, I’d be pretty grateful. Not Tim…he started shooting us down. I have no idea how he got a catapult thrown together that quickly, but he did.

Anyway, in the ensuing melee, at least a dozen good SQL Server people have crash-landed on the island. And then there’s me.

I know that I’ve seen the following people here, and I’m sure there are more (my RSS reader is currently sitting at 89 unread):

OK, not quite a dozen. Like I said, I’m sure there are more. Be quiet.

Anyway, every night on the island we sit around the fire and talk shop. Tonight Brent Ozar kicked off a discussion on how to keep sand out of your SAN…it was riveting, to say the least. Tim has this little game he likes to play after the SQL gossip dies down where he hands you a conch and asks what you’d spend your time learning if you were on this island for the next six months. When he asked me, I didn’t even have to think about it:

How to disassemble a catapult
How to swim back to the shore
How to avoid sharks while swimming back to the shore

Yeah…that didn’t go over so well. Once I regained consciousness, he handed me the conch again and asked for a list of SQL SERVER topics that I’d spend my time on. Oh, fine….

Business Intelligence
- Surprise, surprise. Everyone else said this too. In fact, I’m not waiting for a desert island – I’m starting to work on it now. I’ve decided that sleep is largely discretionary.
SQL Server Internals
- Yes, I’ve read (and own) Kalen Delaney’s unparalelled “Inside SQL Server 2005: The Storage Engine”, but I’ve yet to read the 2008 edition, which brings in the talents of Paul Randal, Kimberly L. Tripp, Adam Machanic, Conor Cunningham, and Ben Navarez. I’m sure that the 2008 edition is even better than 2005.
CLR
- I haven’t yet had much cause to delve deeply into the CLR (Common Language Runtime), but I am continually amazed by the performance of some CLR solutions, particularly where regular expressions are involved.

Like I said, it seems like everyone is on the island now, so I’m afraid I can’t “tag” anyone. Unless you’re not on the island – in which case, consider yourself tagged.

Defensive Programming – Assumptions Must be Guaranteed or Tested

2009-06-10T13:13:00.001-04:00

Every ~~couple of weeks~~ so often, I come across a well-intentioned piece of Transact-SQL code that simply wasn’t designed with 100% reliability in mind. I won’t go into detail about what makes a piece of TSQL well-intentioned – let’s just say that it doesn’t look malicious or anything. Anyway, the type of code I’m talking about is the sort of thing that can easily go undetected through the application’s design, all through QC and acceptance testing, and look deceptively innocent for months – even years – until one day it breaks.

Confused yet? Good. Here’s an example of what I’m talking about:

DECLARE    @BatchToProcess INT

SET        @BatchToProcess =    (/*

                           Select the next batch to process.

                           Only one batch can ever have (ProcessState=2)

                           at a time, so this will always return a single

                           BatchNumber

                           */

                           SELECT  BatchNumber 
                           FROM    QueuedBatches

                           WHERE   ProcessState = 2

                           )

Does this look familiar? For the more junior coders in the room, this code will run fine and dandy as long as there genuinely is never more than one batch with ProcessState = 2. The first time there is more than one however, your application will fail, and will generally not do so in a very nice way. As database professionals, there are a good many tools available to us to maintain the integrity of our applications and our application data – constraints, triggers, and the like. When you use schema objects such as these to guarantee the validity of assumptions made in your application code, you’re coding defensively. When you test any assumptions that aren’t otherwise guaranteed, you’re also coding defensively. When you do neither of the above, you’re coding very, very optimistically, and you’ll end up soaked on the first rainy day.

I liken this type code to a bomb ticking on a time-delayed fuse – it may not go off today, and it may not go off tomorrow. Heck, it may never go off. But if and when it does, it’s almost certainly going to be a royal pain in the Heineken to clean up. Clean up enough of them, and you’ll understand that it’s easier to prevent this kind of occurrence than it is to clean up the mess after the fact.

In order to avoid errors in the code written above, I need to guarantee that my assumption is valid, or test to make sure that it is correct. Hence the title of this article. I know, it was inspired.

Now how can I guarantee that the condition I have assumed is valid? Off hand, I can think of two different ways – one works on any recent version of SQL Server, and the other will work on 2008+ only. The first involves creating a trigger to roll back any transactions that try to insert a row with ProcessState = 2, or update a row’s ProcessState to 2, when a row already exists in this state. The code for such a trigger might look something like this:

CREATE TRIGGER QueuedBatches_RestrictProcessState

ON QueuedBatches

FOR INSERT, UPDATE

AS

BEGIN

   --Check for inserts or updates that set more than 1 row's ProcessState to 2

   IF 1 < (SELECT COUNT(*)

           FROM    INSERTED

           WHERE   ProcessState = 2)

   BEGIN

       PRINT 'You cannot insert more than one record with ProcessState=2'

       ROLLBACK TRAN

   END

   
   --Check for inserts or updates that set a row's ProcessState to 2, when

   --a row already exists in that state

   IF 1 < (   SELECT  COUNT(*) 
               FROM    QueuedBatches

               WHERE   ProcessState = 2)

   BEGIN

       PRINT 'QueuedBatches may only ever have a single record where ProcessState=2.

    A record already exists in this state.' 
       ROLLBACK TRAN

   END

END

GO

Of course, you would replace the PRINT statements with whatever error handling mechanism you’re employing.

The second method, applicable on SQL 2008 and greater, would employ a unique filtered index to guarantee that there is never more than one record with ProcessStatus = 2. Said index would look like this:

CREATE UNIQUE INDEX QueuedBatches_ProcessState_Unique_IX

ON QueuedBatches (ProcessState)

WHERE ProcessState = 2

The former method is a bit more flexible, but the latter is almost certainly more efficient.

If (for some reason) we are unable to implement constraints in the schema to guarantee the state of our QueuedBatches data, we can still handle the condition in code. Rather than creating a variable and blindly selecting in (hopefully one) batch number, we could either check for duplicate batches before doing the variable assignment:

IF 1 < (SELECT COUNT(*)

        FROM   QueuedBatches

        WHERE  ProcessState = 2)

   BEGIN

     PRINT 'QueuedBatches data is inconsistent - more than one record with BatchState = 2'

     ROLLBACK TRAN

   END

or we could fetch the TOP BatchNumber into our scalar variable (provided that doing so will not adversely affect our application):


SET        @BatchToProcess =    (
                           SELECT  TOP 1 BatchNumber 
                           FROM    QueuedBatches

                           WHERE   ProcessState = 2

                           )

At the risk of sounding like a broken record, whatever the method you choose, you need to either guarantee or test your assumptions. Any of the above methods is better than the “wing and a prayer” strategy that seems to have become so prevalent nowadays, and only you can decide which method is right for your applications.

Remember – that poor schmuck who gets dumped on by the user community when the app breaks and business grinds to a halt – well, that poor schmuck just may be you.

Full-Text Search vs Denormalized Tables vs Link Tables: Handling Tags in SQL Server

2009-06-09T13:45:00.001-04:00

“Tags” seem to be a very popular way of categorizing content nowadays. And by “nowadays” I mean “for the last 20 years or so”. But they’re especially popular today, with apps like Flickr and Picasa, sites like the MSDN Forums and StackOverflow, and even the blog post that you’re reading, all using tags to categorize content.

So how can we handle tags efficiently in SQL Server? Well, let’s take a look at the problem first:

Tags will fit into a conventional datatype definition. In fact, most tags generally consume 50 characters or less.
The relationship between an item and the number of tags that item has is not consistent. In most applications, an item can have as few as zero, and as may as ten different tags.

The first attribute is OK, but the second one presents a bit of a storage dilemma, doesn’t it? I can think of three (reasonable) solutions to handle this, because ultimately a tag/item relationship is not too different from a parent/child relationship. Given that assumption, we could use:

A classic parent/child relationship, supported with a link table:

--"Tags" table used for the "link table" tag reference method.

--We'll also use it to populate the full text table (see next method)

CREATE TABLE Tags

   (

   TagID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

   TagName VARCHAR(50)

   )

GO 


--Table for use with Link Table

CREATE TABLE ItemsWithoutTags

   (

   ItemID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

   ItemText VARCHAR(2000)

   )

GO

--Link Table

CREATE TABLE ItemTagXRef

   (

   ItemID INT NOT NULL FOREIGN KEY REFERENCES ItemsWithoutTags(ItemID),

   TagID INT NOT NULL FOREIGN KEY REFERENCES Tags(TagID),

   CONSTRAINT PK_ItemTagXRef_ItemID_TagID PRIMARY KEY NONCLUSTERED(ItemID, TagID)

   )

CREATE INDEX CX_ItemTagXRef_ItemID ON ItemTagXRef(ItemID)

GO

This solution stores items in one table (pictures, blog posts, forum entries, etc), tags in a second table, and uses a link table to store item/tag relationships.

A single column with a full-text index:

CREATE TABLE ItemsWithFTSTags

   (

   ItemID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,

   ItemText VARCHAR(2000),

   Tags VARCHAR(200),

   )

CREATE UNIQUE CLUSTERED INDEX PK_ItemsWithFTSTags_ItemID ON ItemsWithFTSTags(ItemID);

CREATE FULLTEXT CATALOG FTCatDemo AS DEFAULT;

CREATE FULLTEXT INDEX ON ItemsWithFTSTags(Tags) KEY INDEX PK_ItemsWithFTSTags_ItemID;

GO

To employ this type of solution, we would need to delimit the tags, for instance: <Tag1><Tag2><Tag3>

A denormalized items table with one column for each possible tag:

CREATE TABLE ItemsWithInlineTags

   (

   ItemID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

   ItemText VARCHAR(2000),

   Tag1 VARCHAR(50),

   Tag2 VARCHAR(50),

   Tag3 VARCHAR(50),

   Tag4 VARCHAR(50)

   )

There are probably more, but these three are what 90% of designers will come out with. Each solution has it’s strengths and weaknesses. The Item/Tag/Link solution allows for a true 0-n relationship, without necessitating schema modification as the number of tags increases. This may not be a requirement for every application, but it’s good to be able to increase the number of permitted tags without having to modify the schema. The Full Text Search solution uses only one table (remember, joins aren’t free), and allows us the flexibility of the full-text operators to do our tag search (though that flexibility may not be required in this situation). The denormalized table requires only one table, and doesn’t have a dependency on full-text search.

The next logical question would be: how do they perform? While I was designing a test to answer this very question, it struck me that the denormalized table solution was…well…stupid. I was sitting trying to figure out the best way to index four columns, each of which is potentially nullable, each of which could be the target of a given search (WHERE Tag1 = ‘Java’ OR Tag2 = ‘Java’ OR ….). The solution started to feel like a stretch. One of our challenges as developers and designers is to learn when an idea, no matter how immaculate it seemed when it was conceived, needs to be killed off. When the only thing providing a solution with legitimacy is my desire for it to be a good solution, it's time to kick it to the curb. And that’s just what I did here.

And then there were two…

To create a decent test, I decided to load about 500 tags and 10,000 items. I “borrowed” the tags from a list of programming languages on Wikipedia (truncated so the post wouldn’t turn into a miniseries, but feel free to contact me or leave a comment if you'd like the script):

--First, the Tags table.  I need a decent dataset, so I'm passing a list

--of programming languages that I "borrowed" from 
--Wikipedia (http://en.wikipedia.org/wiki/List_of_programming_languages)

--567 rows in all.

INSERT Tags(TagName) SELECT 'A# (Axiom)'

INSERT Tags(TagName) SELECT 'A# .NET'

INSERT Tags(TagName) SELECT 'A+'

INSERT Tags(TagName) SELECT 'A++'

INSERT Tags(TagName) SELECT 'A-0'

INSERT Tags(TagName) SELECT 'ABAP'

INSERT Tags(TagName) SELECT 'ABC'

INSERT Tags(TagName) SELECT 'ABC ALGOL'

.....

INSERT Tags(TagName) SELECT 'XQuery'

INSERT Tags(TagName) SELECT 'XSLT - See XPath'

INSERT Tags(TagName) SELECT 'Y'

INSERT Tags(TagName) SELECT 'YACC'

INSERT Tags(TagName) SELECT 'Yorick'

INSERT Tags(TagName) SELECT 'Z'

INSERT Tags(TagName) SELECT 'Z notation - like UML.'

INSERT Tags(TagName) SELECT 'Zonnon'

INSERT Tags(TagName) SELECT 'ZOPL'

INSERT Tags(TagName) SELECT 'ZPL'

INSERT Tags(TagName) SELECT 'ZZT-oop'

I then populated ItemsWithoutTags, ItemTagXRef and ItemsWithFTSTags using a bit of loop logic and RAND(). I know, every time you loop in SQL Server, an angel loses it’s wings. I’m sorry, but RAND() only evaluates once per statement, which necessarily meant that I had to run the statement 10,000 times. Here’s the script:

--Now for the items being tagged.  10k for each table,

--with 1-4 tags per item.

--2500 items each with 1,2,3 and 4 tags.  (Hence the modulo logic below)

--I'm using a standard numbers table for enumeration

INSERT ItemsWithoutTags (ItemText)

SELECT REPLICATE('A', 50)

FROM   Numbers

WHERE  Number < 10000



--Crude, but effective tagging script

--You'll get a few errors for duplicate tags

--on a given item, but only a few - statistically

--1/567 * the number of tags for a given item,

--* the number of items



DECLARE    @CurrentTagItem INT

DECLARE    @NumberOfTags INT

DECLARE    @LoopCounter INT



SET        @NumberOfTags = (SELECT COUNT(*) FROM Tags)

SET        @CurrentTagItem = 1

SET        @LoopCounter = 1



WHILE  @CurrentTagItem <= 10000

BEGIN



   WHILE   @LoopCounter <= (@CurrentTagItem%4) + 1

   BEGIN

       INSERT  ItemTagXRef(ItemID, TagID)

       SELECT  @CurrentTagItem, 
               CAST(RAND() * @NumberOfTags AS INT)

       SET @LoopCounter = @LoopCounter + 1

   END

   
   SET @CurrentTagItem = @CurrentTagItem + 1

   SET @LoopCounter = 1  
END



--Now to populate the full-text table.

--Again, 2500ish each with 1, 2, 3, and 4 tags.

--We'll delimit the tags as such: ztagz

SET        @NumberOfTags = (SELECT COUNT(*) FROM Tags)



SELECT @LoopCounter = 1, @CurrentTagItem = 1

DECLARE    @TagString VARCHAR(250)

SET        @TagString = ''



WHILE  @CurrentTagItem <= 10000

BEGIN

   WHILE   @LoopCounter <= (@CurrentTagItem%4) + 1

   BEGIN

       SET @TagString = @TagString + 
                       (SELECT 'z' + t.TagName + 'z'

                       FROM    Tags AS t

                       WHERE   t.TagID = CAST(RAND() * @NumberOfTags AS INT))

       SET @LoopCounter = @LoopCounter + 1

   END

   INSERT ItemsWithFTSTags(ItemText, Tags)

   SELECT  REPLICATE('a', 50), @TagString

   
   SET @LoopCounter = 1

   SET @TagString = ''

   
   SET @CurrentTagItem = @CurrentTagItem + 1

END

Still with me? OK, nobody has dozed off – must be doing something right.

Notice the bit of a hack required to store the tags in the full text table? I’ve delimited the tag with a “z” on either end. By default, querying full text search via the CONTAINS operator will find any block of text containing a given word, so a search for “Java” will find not only the “Java” tag, but also “General Java”, “Join Java”, and the like. “Z” isn’t the best choice of delimiters, however you’ll find that SQL Server strips out most symbols when it indexes a column, so (for instance) storing the tags separated by vertical pipes won’t do you any good. Score 1 for the ItemXRef solution – we don’t need to muck around with it in order to get it to work, which leaves far more time for intelligent development and optimization.

Now let’s try querying both tables, for items tagged with the tag “Java”:

SELECT i.*

FROM   ItemsWithoutTags i

JOIN   ItemTagXRef itx ON i.ItemID = itx.ItemID

JOIN   Tags t ON itx.TagID = t.TagID

WHERE  t.TagName = 'Java'





SELECT i.*

FROM   ItemsWithFTSTags i

WHERE  CONTAINS(i.Tags, '"zJavaz"')

The full-text syntax is a little painful, but I can live with it.

Of course, a key factor in the suitability of any solution is it’s performance, so I wrapped each query in a loop, and executed them 100x apiece for five iterations each. Here’s what I found:

Pretty comparable, in the end. Of course, YMMV when two solutions are this close to each other, so if you’re considering applying one of these techniques, I recommend testing it in your environment with realistic sample data. If the difference is negligible, the XRef solution will most likely tip the scales as a result of it’s flexibility and ease of coding.

Until next time, that's all folks.

Microsoft DPE Releases SQL Server 2008 Training Kit

2009-06-04T10:45:00.000-04:00

One of the best things about working with Microsoft technologies is the absolute wealth of information provided, in many cases for free, to developers and IT professionals. A great example of this is the SQL Server 2008 Training Kit, which was released by Microsoft DPE (Developer and Platform Evangelism) just a couple of weeks ago.

The training kit is a set of code samples, presentations, and demo scripts designed to facilitate quick and easy presentations on some of the fun new features in SQL Server 2008.

The download is about 50 megs, and installs within a matter of minutes. The content is accessed via an html page which is included in the installer. It breaks the content down into a number of sections, each accessible via the menu bar at the top of the screen:

Drill into a topic, such as T-SQL, and you’re faced with a listing of all of the content in that subject matter area:

The presentations are provided as PowerPoint files (Office 2007 – pptx), and the demos include both a demo script (in html or Word format) and a source code listing (for the TSQL examples, the source code is actually provided as a .SQL file).

The demo scripts take you through an entire presentation on the subject at hand. For smaller topics, such as Row Constructors, you’re basically doing through the entire subject, from start to finish. Larger topics (SQL Server Spatial, for instance) obviously aren’t covered in their entirety, but a good introduction is provided, and it is enough to pique a group’s interest, or to provide an introduction into some of the cooler features in SQL Server 2008.

These presentations and demos are great for user groups, internal training events, or even for personal use as a quick introduction to a SQL 2008 feature. The download is very well presented and clearly represents a lot of effort on the part of DPE.

Support your own geekiness – download the training kit and have fun!

Easy On The Updates There, Sparky

2009-05-26T10:00:00.001-04:00

Ahh, the UPDATE statement. Do you ever stop to think of where we would be without the UPDATE statement? Can you imagine writing a DELETE followed by an INSERT every time we needed to modify a data row? I thought not. The very word “update” rings of timeliness, relevance, and light. Unfortunately, as is the case with all things, it’s all too easy to go overboard, and you can have too much of a good thing.

You see, to the unsuspecting database developer, it may seem that some operations in SQL Server are more or less “free”. Let’s clear the air on that one – nothing is free, ever. Or if it is, it usually has a 30 day limit. It’s easy to forget this, because when you’re working with something like SQL Server, it’s hard to imagine that a sub-second response time can hide anything of significant concern.

A SQL Server update is actually a relatively “expensive” operation, and as such needs to be done only when necessary. Wondering what I’m blathering on about? OK, let me ask you a question – which of the following two statements is more expensive?

UPDATE Customer

SET    AccountStatus = 0

WHERE  LastPurchaseDate < '20090101'

AND    CurrentBalance = 0

UPDATE Customer

SET    AccountStatus = 0

WHERE  LastPurchaseDate < '20090101'

AND    CurrentBalance = 0

AND    AccountStatus <> 0

(For those of you who haven’t yet had your coffee today, the second statement has an extra line).

Your choices are:
- 1
- 2
- It Depends

How many of you chose “it depends”? It’s the safe answer, so I’ll wager that most of you did. And I’m sure you’re right, because there are edge cases for everything. But let’s get real and talk about the 99.99% of cases that you should concern yourself with.

The correct answer is “1”.

Don’t believe me? OK, let’s dig a bit deeper. The first batch is, in plain English, setting the “status” flag for all customers who haven’t purchased in this calendar year, and don’t owe us anything, to “0” (0 = Inactive, which you’ll see when I post the sample data script). The second batch is setting the “status” flag for all customers who haven’t purchased in this calendar year, and don’t owe us anything, and who aren’t already inactive, to zero.

So what’s the big deal? If it’s already set to “0”, SQL Server will just pass it by, won’t it?

No, no it won’t. SQL Server performs every update in exactly the same manner, regardless of whether or not the “new” value is any different from the “old” one. The following is an oversimplification of what happens:

SQL Server acquires an Intent Exclusive (IX) lock on the rows or pages being modified. Note that an IX lock is NOT compatible with a shared (S) lock, so your update will block readers, even if it isn’t “changing” the value. This also means that your update can be blocked by readers, even if it doesn’t really need to update those rows. When SQL Server is ready to perform the update, it converts the IX lock into an Exclusive (X) lock.
The actual update occurs. SQL Server implements an update internally as a “delete” followed by an “insert”. ALL rows covered by the UPDATE statement are deleted and inserted, regardless of whether or not the value has actually changed.

Any update triggers which are defined on the table fire.

What’s that? You don’t believe that SQL Server actually updates the rows, even if the value hasn’t changed? Check it out for yourself. In the following example, we create a table called “Customer”. No comments on the schema – this isn’t a database design post, and it’s an easy example to work with. On said table, we create a FOR UPDATE trigger, which will fire any time an update is made. Have a look at the results:

--Create a table for testing

CREATE TABLE Customer

   (

   CustomerID          INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

   CustomerName        VARCHAR(200),

   LastPurchaseDate    datetime NULL,

   CurrentBalance      decimal(18,6),

   AccountStatus       bit NOT NULL DEFAULT 0  -- 0 = Inactive, 1 = Active

   )

GO



--Create a trigger on our test table. 
--This trigger fires whenever an update is actually performed

CREATE TRIGGER Customer_Update

ON     Customer

FOR    UPDATE

AS

BEGIN

       DECLARE @RowsDeleted INT, @RowsInserted INT

       SET     @RowsDeleted = (SELECT COUNT(*) FROM DELETED)

       SET     @RowsInserted = (SELECT COUNT(*) FROM INSERTED)

      
       PRINT   'Rows Deleted: ' + CAST(@RowsDeleted AS VARCHAR(20)) + CHAR(10) + CHAR(13) +

               'Rows Inserted: ' + CAST(@RowsInserted AS VARCHAR(20))

END



GO



--Insert a test row.  Note that the AccountStatus is 0

INSERT Customer (CustomerName, LastPurchaseDate, CurrentBalance, AccountStatus)

SELECT 'Trapper Sam''s House of Peameal Bacon', '20081215', 0, 0



GO



--Perform an update

UPDATE Customer

SET        AccountStatus = 0

WHERE  LastPurchaseDate <= '20090101'

AND        CurrentBalance = 0





/*

Zut Alors!

Rows Deleted: 1

Rows Inserted: 1

(1 row(s) affected)

*/

I hate to say I told you so…. ;-)

Now if you’re following along at home, don’t drop that table quite yet – we’re not done with it.

Now note that, in order to prove to you that the rows were updated, we also demonstrated that any triggers defined on the table are fired. In the event of an update that actually changes 0 rows, but updates >0 rows, the trigger will fire unnecessarily.

Lock Escalation
Remember lock escalation? Basically, SQL Server consumes resources in order to place and track locks. For a large query, the resources (memory and CPU, in specific) required to maintain a significant number of locks is not insignificant. Ideally, row locks are most conducive to concurrency, as the chances of one request blocking another is reduced. Unnecessarily updating rows can cause the lock manager to select page locks over row locks, or to escalate your row/page locks to table locks!

Transactional and Merge Replication
Although they employ different mechanisms, both transactional and merge replication will replicate all updates on published articles – with no regard to whether or not the row’s data was actually updated. This isn’t a failing on the part of the replication agents – it’s simply the most efficient way for them to work. If you’re “updating” rows unnecessarily, you’re replicating useless updates – you didn’t “change” anything, but it’s published anyway!

Database Mirroring
Do you use database mirroring? If you do, I’m sure you know that mirroring uses the SQL Server transaction log to keep the mirror database in sync with the primary. Just as with replication, if you’re performing unnecessary updates, you’re now adding to your mirror load as well.

Database Snapshots
Database Snapshots use a copy-on-write mechanism to maintain a point-in-time record of a database’s pages. When a page in the source database is written to, the page is first copied to the snapshot. As a result of this copy-on-write mechanism, you’re pushing rows that weren’t really changed into the snapshot! (Thanks to Paul Randal for confirming this).

So how do we prevent this kind of carnage? Well, as the code example above shows, all we need to do is add the column(s) being updated to the WHERE clause. Keep in mind that application code often doesn’t need to deal with this, because the data access components only pass back updates for rows that were actually changed. When in doubt it’s best to make sure that the application is doing this kind of “intelligent updating”, as it can have a significant impact on your application’s throughput.

Ladies and Gentlemen, that’s all for today. Please sign the guestbook on your way out.

Memorial Day Book Review: Juno Beach (And Making a Habit of Thinking Every Day)

2009-05-26T09:40:00.000-04:00

I don’t normally do book reviews – more than anything because I’m not entirely sure people will want to read them. I’m making an exception this time though, in honor of Memorial Day. Memorial Day is an American holiday, and I’m Canadian – you’ll see in a bit why that doesn’t much matter.

I happened to be at one of the local University libraries a few weeks ago and noticed a book called Juno Beach: Canada’s D-Day Victory: June 6, 1944. Normally I would have just kept browsing, but something appealed to me about this book. I picked it up and looked it over, and the most remarkable thing was that this 414 page paperback was written entirely about a single day, the day of the allied landings in Normandy.

For those who aren’t aware, the allied assault was divided into five areas, and the beaches that they landed on were named accordingly: the Americans took Omaha and Utah, the British had Sword and Gold, and finally the Canadians were assigned to Juno beach.

I won’t go into exhaustive detail about the book, but one thing should be mentioned: the author (Mark Zuehlke) combined official record with veteran interviews to compile his work. Where multiple, independent veterans agreed on a detail that did not agree with the official record, the author favoured the veterans’ accounts. This is an important detail, for nobody knows what happened better than the ones who lived through it, and official record is often coloured by the desire to protect the reputations of the people involved. As these veterans disappear, accounts like this will be all we have to remember what they went through.

Now just to give you an idea of what the men who were assigned the task of taking Juno (and the other D-Day beaches) faced, let me relay just two points.

First, in order to make it safe(r) for the assault on the beach to proceed, a fleet of minesweeping vessels was assigned to clear the water leading up to said beach. These minesweepers were to steam toward the beach in a staggered line, and each ship cleared the path in front of the ship behind it. This meant that the leading ship had no cover whatsoever – it was sailing directly into a heavy minefield. Once the ships approached the beach, they were to turn at 90 degrees and sweep laterally across the water, a manoeuvre which exposed the broad side of the ships to the formidable German batteries onshore. Prior to setting out across the English channel, the men crewing the ships were given an assessment of their chances. Headquarters had forecasted that 75% of the ships (and their entire crew) involved in the clearing operation would be lost as a result of enemy mines or land-based batteries. The leading ship’s crew was told that there was very little chance that they would survive the clearing operation. Each ship was informed that if they were disabled or damaged, the Navy destroyers waiting behind would be forced to blast the ship out of the water, so that it didn’t impede the landing crafts’ progress. After the odds were communicated to the men, they were each given the opportunity to stand aside, and be assigned to a safer detail in England. Not_a_single_one_of_them chose to stand aside.

The second detail that I’d like to convey is about the landing itself. In the first hour of the assault, the Canadians who waded ashore onto Juno faced a full 50% casualty rate. That is to say, if you and the person standing beside you stepped off the landing craft, one of you would more than likely not make it through the next three or four minutes. These people had trained together for years in England, and all the while had been away from their families and their homes. They had become like family, and yet they had to keep going, even when they watched their fellow solders get “cut to pieces” as they waded ashore. In the end they succeeded in their efforts and won the day, but they could never be the same after living through what they did.

Now why did I feel it was necessary to bring those points up? Well, about a week ago I was driving to work along a semi-residential road. The speed limit was 50km/h, and of course traffic was moving at closer to 65 km/h. The road narrowed from two lanes in either direction to one, and I found myself behind a vehicle that was going about 40km/h. I was just starting to get annoyed when I looked down at the license plate – it was the Ontario Veteran Graphic License Plate:

It struck me as kind of sad that my initial reaction was annoyance, before I even took the time to really look at what was in front of me. I know I’m not alone, because while I gave the gentleman space and followed behind him patiently, a line of cars behind me was angrily jostling and tailgating, trying to get me to speed up. In a few moments, the veteran pulled off into the parking lot of one of our municipal greenhouses – likely one of the few things he really looks forward to at this point in his life. I wondered whether the people that were pushing behind me would have given him the distance and respect that I did. Then I wondered whether or not I would have if I hadn’t been reading about Juno Beach. Neither of those are very comfortable thoughts, are they?

I hope that this doesn’t come across as preachy, as that’s the furthest thing from my mind, but how often do you really think about the lives that were lost, and continue to be lost, by people who believe in something greater than themselves, and are willing to give their lives to protect it? I know that for me it has always meant nothing more than a poppy on my collar and a few minutes of silence once a year. Sadly, Canadian schools focus the majority of their time and effort in history class on the settling of Canada, and precious little time on the events that are close enough and still real enough to honestly give us pause. I believe that everyone should be taught about the sacrifices that went into making our world what it is today. If they did, maybe people would have a little more respect for the place that they call home, and the people that they meet as they go through their lives. After all, if our world was worth dying for, isn’t it worth at least pausing to think about, just once in a while?

Identifying Items with Matching Join Sets In SQL Server

2009-05-21T12:21:00.001-04:00

Here’s a neat problem: Identify the records in Table A which join to the same set of records in Table B. That is to say, produce a listing of records from TableA, grouped by the set of records that they join to in Table B.

This problem came up on StackOverflow the other day, and it was actually quite a lot of fun to resolve. Let’s see if I can somehow relay the convoluted thinking that led me to my conclusion:

What a weird requirement. I wonder if this is a homework question.
A homework question would have better table names.
Or at least I would hope so.
But I’d likely be wrong.
Geez..in the time I’ve spent debating whether or not to solve this question, I could have solved this question.
Time to don the Serious Hat.
If I can somehow summarize the joins that an item makes into a single, discrete value, I can compare that item with the balance of the items to identify matches.
If I can then consolidate like items into a single data value, I can produce a listing to show “items by join set”.
But how do I consolidate items and joins?

I thought about this for a minute, and then I remembered SQL Server MVP Arnie Rowland’s excellent article on the Transact-SQL Wiki: Create a Comma-Delimited List From a Column in a Table. I’ve actually used his solution in so many different situations now that I’ve reserved a special place in my (limited) memory bank for it. If you haven’t read it, take a few minutes to do so.

So here was my solution. First, let’s have a look at the sample data I used. The poster had two tables – Area, and AreaToProduct. The former contained the Area master record, while the latter contained relationships between areas and products.

CREATE TABLE Area

   (

   AreaID INT NOT NULL PRIMARY KEY CLUSTERED,

   Name VARCHAR(50)

   )

CREATE TABLE AreaToProduct

   (

   AreaID INT NOT NULL FOREIGN KEY REFERENCES Area(AreaID),

   ProductID INT NOT NULL

   )

GO



INSERT Area(AreaID, Name)

SELECT 1, 'SectionA'

UNION ALL

SELECT 2, 'SectionB'

UNION ALL

SELECT 3, 'SectionC'

UNION ALL

SELECT 4, 'SectionD'





INSERT AreaToProduct(AreaID, ProductID)

SELECT 1, 1

UNION ALL

SELECT 1, 2

UNION ALL

SELECT 1, 3

UNION ALL

SELECT 2, 1

UNION ALL

SELECT 3, 1

UNION ALL

SELECT 3, 2

UNION ALL

SELECT 3, 3

UNION ALL

SELECT 4, 2

UNION ALL

SELECT 4, 3

GO

So based upon this sample data, Sections A and C should have “matching” join sets, while Sections B and D should stand alone. Here’s what I worked out:

;WITH AreaProductSets

AS

--This CTE aggregates the joins

(SELECT a.AreaID, a.Name,                         
       SUBSTRING(

                   (SELECT (', ' + CAST(ap.ProductID AS VARCHAR(20)))

                   FROM AreaToProduct ap

                   WHERE ap.AreaID = a.AreaID

                   ORDER BY ap.ProductID

                   FOR XML PATH ('')), 3, 2000) AS ProductArray

FROM            Area AS a)



--The main query uses the aggregated joins to produce an aggregation

--of the Area names

SELECT  SUBSTRING(

                   (SELECT     (', ' + CAST(aps2.Name AS VARCHAR(20)))

                   FROM        AreaProductSets aps2

                   WHERE       aps2.ProductArray = aps.ProductArray

                   ORDER BY aps2.AreaID

                   FOR XML PATH('')), 3, 2000) AS MatchingAreas,                        
                   aps.ProductArray

FROM               (SELECT DISTINCT ProductArray FROM AreaProductSets) AS aps

So, as the code comments say, the Common Table Expression creates a listing of Product Set arrays, using Arnie’s pattern. The main query then produces a listing of areas for each distinct product set. The results are just what I was aiming for:

You can use this kind of logic in data cleansing as well, to identify duplicate elements based upon their relationships.

So there you have it - a neat solution for matching sets, and a whirlwind tour through the strange and sometimes frightening mind of a SQL Server Geek ;-)

I'd love to see what kind of solutions you can drum up to address this problem...

Wolfram|Alpha: Move Over Google? I Think We’re Missing The Point.

2009-05-19T22:01:00.001-04:00

In case you’ve been living under a rock for the past week (or offline – same thing in this day and age), the highly anticipated WolframAlpha was released on Friday. If it sounds like Ich spreche Deutsch, have a look here for the excellent introductory screencast by Stephen Wolfram.

Soon after it’s release, the “blogosphere” lit up with articles both proclaiming, and disclaiming, WolframAlpha as the next Google killer. It’s a reasonable comparison – after all, Google is our current standard for knowledge acquisition on the internet. If we want to see a trailer for the new Star Trek movie, we start on Google. If we want to find out the syntax, pros, cons, and performance characteristics for a SQL construct, we start on Google. If we want to find out the molecular composition, basic properties, and thermodynamic characteristics of Carbon Monoxide, we start on Google. In fact, if you’re looking for just about anything on the internet, you’re almost certainly going to start on Google. And so, with this broad brush of generalization, we pitch WolframAlpha into the ring with Google. With the same unfair broad brush of generalization, I’ll summarize the majority of the blog posts that were written on this modern-day David vs Goliath matchup:

The Pro-Google Perspective

Google is the internet standard
WolframAlpha doesn’t work for most common search topics

The Pro-WolframAlpha Perspective

WolframAlpha synthesizes, aggregates, and presents relevant information on a search topic, saving time and “making sense” of related information.

Sure, they were more long-winded than that, but you get the idea.

Which brings me to the point of this article – comparing Google to WolframAlpha is like comparing Apples to Ostriches, and bears roughly the same chance of producing intelligent insights.

WolframAlpha isn’t designed to be a “Google”, so calling it a “Google Killer” is doing it a grave disservice. WolframAlpha is billed as a “computational knowledge engine”, which is geek for “thingamajig that makes sense of data and facts”. This isn’t what Google does. Google’s main purpose is to find relevant content. It doesn’t (at this point) aim to “slice and dice” data or produce pretty charts and extrapolations – indeed, it’s only purpose of note is to find the most relevant content for a given input string, a purpose which it fulfills very competently.

W0lframAlpha also fulfills it’s purpose very competently. For instance, take a look at that Carbon Monoxide search I mentioned before. In order to get that information via the conventional (Google) method, I’d need to visit at least a half a dozen sites, and I still wouldn’t get the diagrams that WolframAlpha produces. Concise, quick, and palatable access to knowledge is what WolframAlpha is designed to do. Look up Carbon Monoxide on Wikipedia, and you’re going to waste an awful lot of time just trying to extract the relevant bits that WolframAlpha produced within a few seconds.

So why won’t WolframAlpha ever kill the mighty Google? Well, according to The Unofficial Book of Random Speculation, the vast majority of internet searches are trivial. See if you can find stats to prove me wrong, but I don’t think I am. Unless Stephen Wolfram has perfected Artificial Intelligence (and if he has, let me be the first to say “All Hail the Machines”), his engine requires actual people to aggregate, validate, and present subject matter. He may have figured out a smarter and more automated way of doing it, but it still requires human intervention. Now using the oh-so-handy Google Trends, let’s have a look at the top search topics for today:

harvard shooting
beverly center shooting
harvard crimson
bacherlorette
note to god lyrics
pooch hall
if i wrote a note to god
warped tour
24 season finale
hosea chanchez

Notice anything? 1 through 3 are current events. 4 and 9 revolve around TV shows. 6 and 10 revolve around a TV show that I’ve never even heard of. 5 through 8 relate to music or music events that I probably want nothing to do with.
The point is, it’s all current, fleeting, and very….pop-ish. All that matters for the people who are searching for those terms is current, relevant search results. No human required, no aggregation possible…or at least, any aggregation would likely miss a large part of what the searching public is looking for. What is the specific heat of combustion of The Bachelorette? No..seriously, I think we’d all be better off if that kind of show would just hurry up and combust.

So will anybody ever kill Google? Probably – nothing lasts forever. But it won’t be WolframAlpha. I wouldn’t rule out Microsoft. Live Search is still miles away from Google IMO, but Microsoft has a history of taking on titans (<ahem>Oracle</ahem>) and winning. And who knows what Kumo has in store for us.

Anyway, time to go figure out who Pooch Hall is.

sp_ExecuteSQL Doesn’t Validate Parameter Names

2009-05-12T09:24:00.002-04:00

I came across something interesting on the MSDN Forums a few weeks ago, and I thought I’d share it here. This is about as close to SQL Server trivia as I like to get, so I’ll keep it short ;-)

If you aren’t familiar with sp_ExecuteSQL, it is an extended stored procedure that can be used to parameterize and execute dynamic SQL. According to BOL, the general syntax for using sp_ExecuteSQL is as follows:

DECLARE        @MySQLStatement NVARCHAR(2000),

               @MyParams NVARCHAR(2000)

              
SELECT         @MySQLStatement = 'SELECT * FROM sys.databases WHERE [name] = @name',

               @MyParams = '@name sysname'

              
EXEC sp_ExecuteSQL @stmt = @MySQLStatement,

                   @params = @MyParams,

                   @name = 'msdb'

So bit by bit, the first parameter (@stmt) accepts the SQL statement. It has to be a Unicode type (ntext/nchar/nvarchar), so you need to either use a variable (as I did), or you need to prefix your constant string with a capital “N”. The second parameter accepts a comma-delimited listing of the dynamic SQL’s parameters, and also needs to be Unicode. The parameters following the second parameter should correspond to the parameters that you listed in the second parameter. Can I possibly say parameter one more time? I just did!

Now if you should happen to use the wrong type for one of the first two params, SQL Server will whine loudly:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Notice anything odd? In the original sp_ExecuteSQL call above, I called the first param “@stmt”. And it worked. But the error message gripes about a parameter called @statement. Is the error message wrong? Try the first example again, but substitute @statement for @stmt. It still works, doesn’t it? That isn’t standard stored procedure behaviour:

CREATE PROC MyTestProc

   (

   @SQLServer VARCHAR(50)

   )

AS

BEGIN

   SELECT @SQLServer

END



--This works

EXEC MyTestProc @SQLServer = 'I am SQL - hear me ROAR!'



--This doesn't.  No matter how many people say it does.

EXEC MyTestProc @MySQL = 'I am free.  That should count for something, no?'

In fact, you can use anything you want for the first two parameter names when using sp_ExecuteSQL – it runs on ordinal position, not variable name. Everything after the second parameter is validated. Weird, eh?

The change from standard SQL Server behaviour is a result of the fact that this sp is implemented as an extended stored procedure, using the XP API. Thanks to Michael Coles and Steve Kass for helping me crack the riddle.

The Many Flavours of Common Table Expressions in SQL Server

2009-05-06T20:32:00.001-04:00

One of the coolest features introduced in SQL Server 2005 was the Common Table Expression, or CTE for short. In the simplest sense, a CTE is a “virtual result set” that is defined within a given SQL statement, and used throughout that statement as if it were a real object.

En Anglais? Here’s an example, using AdventureWorks2008:

--A regular CTE

;WITH EmployeeSeniority AS

(

SELECT emp.LoginID, p.FirstName, p.LastName, emp.HireDate,

       DENSE_RANK() OVER (ORDER BY emp.HireDate ASC) AS Seniority

FROM   HumanResources.Employee emp

JOIN   Person.Person p ON emp.BusinessEntityID = p.BusinessEntityID

)

SELECT LoginID, FirstName, LastName, HireDate, Seniority

FROM   EmployeeSeniority

As you can see, we have effectively “named” a result set by using the syntax:

WITH <ResultSetName> AS (MyQuery)

Using the simple example above, I also demonstrated the ability of a CTE to encapsulate a column from the SELECT list, so we can treat aliased columns as “real” ones. I built a column called “Seniority” inside the CTE itself, and then used said derived column in the main query’s SELECT statement. Coolio.

Let’s take it one step further. By placing a comma after the CTE’s defining query, we can add in another query:

--Two CTE's.

;WITH EmployeeSeniority AS

(

SELECT emp.LoginID, p.FirstName, p.LastName, emp.HireDate, emp.BusinessEntityID,

       DENSE_RANK() OVER (ORDER BY emp.HireDate ASC) AS Seniority

FROM   HumanResources.Employee emp

JOIN   Person.Person p ON emp.BusinessEntityID = p.BusinessEntityID

),

EmployeePay AS

(

SELECT eph.BusinessEntityID, eph.Rate, eph.PayFrequency,

       ROW_NUMBER() OVER (PARTITION BY eph.BusinessEntityID ORDER BY ModifiedDate DESC)

       AS IsCurrent

FROM   HumanResources.EmployeePayHistory eph

)



SELECT es.LoginID, es.FirstName, es.LastName, es.HireDate,

       ep.PayFrequency, ep.Rate

FROM   EmployeeSeniority es

JOIN   EmployeePay ep   ON es.BusinessEntityID = ep.BusinessEntityID

                       AND ep.IsCurrent = 1

Here, we’ve defined two “virtual result sets”, and in the main query we join the two together, just as we would with regular tables or views. Now the real power of a CTE starts to reveal itself when you understand that one CTE can reference another. For instance, I can rewrite the above query as:

--The previous two CTE's, rewritten so the second references the first.

;WITH EmployeeSeniority AS

(

SELECT emp.LoginID, p.FirstName, p.LastName, emp.HireDate, emp.BusinessEntityID,

       DENSE_RANK() OVER (ORDER BY emp.HireDate ASC) AS Seniority

FROM   HumanResources.Employee emp

JOIN   Person.Person p ON emp.BusinessEntityID = p.BusinessEntityID

),

EmployeePay AS

(

SELECT eph.BusinessEntityID, eph.Rate, eph.PayFrequency,

       ROW_NUMBER() OVER (PARTITION BY eph.BusinessEntityID ORDER BY ModifiedDate DESC)

       AS IsCurrent, es.LoginID, es.FirstName, es.LastName, es.HireDate

FROM   HumanResources.EmployeePayHistory eph

JOIN   EmployeeSeniority es ON eph.BusinessEntityID = es.BusinessEntityID

)

SELECT ep.LoginID, ep.FirstName, ep.LastName, ep.HireDate,

       ep.PayFrequency, ep.Rate

FROM   EmployeePay ep

WHERE  ep.IsCurrent = 1

We’ve actually joined the first CTE to regular tables, within the second CTE’s definition. Now the “main” query only has to reference one CTE!

OK, let’s kick it up one more notch (please don’t sue me, Emeril). The recursive CTE. This one was the trickiest for me to understand, but now that I do understand it I’ll try to make it easier for you. Have a look at this query (stolen, with attribution, from MSDN):

--A recursive CTE

--Example from http://msdn.microsoft.com/en-us/library/ms186243.aspx

USE AdventureWorks;

GO

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)

AS

(

-- Anchor member definition

    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 
        0 AS Level

    FROM HumanResources.Employee AS e

    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh

        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL

    WHERE ManagerID IS NULL

    UNION ALL

-- Recursive member definition

    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,

        LEVEL + 1

    FROM HumanResources.Employee AS e

    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh

        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL

    INNER JOIN DirectReports AS d

        ON e.ManagerID = d.EmployeeID

)

-- Statement that executes the CTE

SELECT ManagerID, EmployeeID, Title, Level

FROM DirectReports

INNER JOIN HumanResources.Department AS dp

    ON DirectReports.DeptID = dp.DepartmentID

Now we’re starting to get into weird science. First, we’ve added column name definitions between WITH CTEName and AS. This essentially defines the name and number of the columns to be returned by the CTE. It is an optional construct, so you don’t need to use it if you’re explicitly aliasing columns in your CTE.

Second, have a look a the query itself. What we’re trying to do here is print a listing of employees, but indicate for each employee which level of the company’s hierarchy they reside in. The first part of the query (labeled the “anchor definition”) returns nothing more than a listing of the “uppermost parent” in the hierarchy. In our example, this is one record – the CEO – but it could very well entail a number of records.

The second part of the query (the “recursive member definition”) is brought into the same result set by the use of a UNION ALL statement. Have a closer look at the recursive member though – notice that it actually refers back to the CTE that it is a part of? Cool, eh? For the first iteration, it will join back to the CEO’s record, and return both the CEO and any of his or her direct reports. For the second iteration, it will find the “third level” employees, or the ones who report to the ones who report to the CEO (say that 5 times fast…). It will continue on and on until either there are no more “child” records, or the MAXRECURSION level for the CTE is hit. Since we didn’t specify a MAXRECURSION level, it will default to 100. The largest setting for MAXRECURSION that you use is 32,767 – anything even close to that and your server will likely be crawling anyway. If we want to specify a MAXRECURSION hint, we can do so immediately after the CTE name. For instance, in the query above, we would change

FROM DirectReports
into
FROM DirectReports OPTION (MAXRECURSION 1000)

Now one last thing you should know about recursive CTEs is that there are certain restrictions on the DML that they can contain. For further detail on these restrictions, have a look here.

Until next time, Keep Your Stick on the Ice, and have fun.