NashSQL Meeting: SQL Server 2008 Change Data Capture

Mark your calendars! The Nashville SQL Server User Group, also known as NashSQL, is having our monthly meeting this Friday, May 29th at 11:30am.

This month we’re welcoming Whitney Weaver, Principal Consultant with Magenic Technologies. In his session, Whitney will demonstrate how to implement the new SQL Server 2008 Change Data Capture (CDC) feature as a method for tracking changes made in an instance. The session will cover the implementation of CDC, the pain points removed by the feature, and the process of retrieving captured data.

Food and drinks will are provided and there is no charge for the event. Who says there isn’t such a thing as a free lunch?  But as a courtesy, you’re requested to RSVP.

For more information visit our web site at http://nashville.sqlpass.org/.

For more information on Change Data Capture, visit these resources:

Cheers and I hope to see you there!

Joe

Join me at DevTeach/SQLTeach in Vancouver, BC

VancouverNext month, I’m traveling to beautiful Vancouver, British Columbia, for DevTeach/SQLTeach conference, June 8th – 12th, 2009. If you’ve never been to a DevTeach/SQLTeach conference, I’d highly recommend it. This will be the fifth time for me and I can tell you that it truly is a great conference. (My prior visits to the conference were in Montreal and Toronto; this will be my first trip to Vancouver.)

This year’s speaker line up includes such notables as Kevin Kline, Jessica Moss, Richard Campbell, Peter DeBetta, Bill Graziano, Don Kiely, Brad McGehee, Roman Rehak, Beth Massi, and Ted Neward, just to name a few.

And, I’m delivering four sessions in Vancouver.

Locking and Blocking Made Simple (SQL212)
A good working knowledge of how SQL Server makes use of locking and transaction isolation levels can go a long way toward improving an application’s performance. In this session, we will explore SQL Server’s locking methodology and discover techniques for enhancing query response times.

Identifying Performance Bottlenecks (SQL326)
When a SQL Server application is under performing, how can you isolate where the actual problem is? Would more memory help? What about adding additional processors? Or maybe some indexes are missing? In this session, we will explore how SQL Profiler and Performance Monitor can be used to uncover your SQL Server’s bottleneck.

Creating Data-Driven Subscriptions in SSRS (SQL342)
Providing information to those that need it when they need it is key for many businesses. But, being able to automatically disseminate critical information in a timely fashion can be a challenge. In this session, we will discover how data-driven subscriptions in SQL Server Reporting Services can be used to effectively deliver reports to a varying list of subscribers.

Tips & Tricks for Writing Better Queries (SQL382)
Transact-SQL is not a very difficult language to learn. As long as the syntax is correct, it can be quite forgiving. However to truly get the best performance from your SQL Server, careful consideration should be given to the structure and logic of the queries. In this session, we’ll discuss some Transact-SQL tips and tricks that can be employed to help you write better queries, allowing your server to perform better.

You can see all sessions being offered here.

So, if you’re interested in SQL Server, come on by for one or my sessions or catch in my the hallways; I’d love to talk with you. If you haven’t registered, it’s not too late. Visit the registration page for details.

Cheers!

Joe

I’ll have a #4 combo with a side of wifi

I had Krystal for lunch yesterday. I don’t know anyone who actually seeks out Krystal for a meal, but I did yesterday.

Not since college have I dined on those little square meat-like patties on steamed buns. And even then it was at 2:00am, when nothing else was open, and I needed a shock to my system to keep me going for another couple of hours.

Yet yesterday, I sought out Krystal.

But it was not the delicacy known as Krystal Burgers that I wanted, although that indeed was what I ordered. No; it was something else I was after. It was their free wireless Internet.

Free WifiYou see, yesterday I was visiting a client to perform some system integration testing. Much of the tests were straightforward. But the system has an outward facing portal that allows some users to access it from outside the network. To properly test that aspect, I needed to be outside the network. So I grabbed my laptop and headed for the closest wi-fi hot spot. It happened to be the home of the Krystal Burger.

Two hours and four Krystal Burgers later, my testing was complete.

Equipped for the Road

As a database consultant, I spend about 50% of my time working from the comfort of my home office. Frankly, that’s where I’m most productive. I can work remotely, focusing on the tasks at hand.

But there are times when it is simply necessary or more convenient to visit a client’s office. On those days, it’s not uncommon for me to have a spare hour or two between appointments. And for the price of a cup of coffee, I can gain admission to the the world wide web.

Oh sure, I have an iPhone with a data package. It’s great! I can wireless sync it with my server, check emails, and browse the web. I can even remote desktop into a client’s server with it. Having this capability allows me to provide much better service to my clients.

But trying to use my iPhone for serious work like developing Reporting Services reports or updating Integration Services packages, well, that would be like trying to launch a leverage buyout using an ATM machine. No for real, sustained work, I need to have my laptop connected to the Internet. And so, I ended up at Krystal.

A Developing Pattern

This wasn’t the first time I’ve done that either; it’s not an isolated event. I’ve eaten at sandwich shoppes just because they offered free Internet access.

For the past nine years, I’ve met with a group of guys at a Nashville coffee shop for a Friday morning Men’s Breakfast. We chose the location, in part, because it offers free wireless Internet for those of us who hang around after the breakfast for a little remote work.

So I’ve developed a pattern. I’ve chosen restaurants and by extension meals, not based on the menu, or quality, or even my taste preferences, but because of something that is not even close to their core business – their Internet access.

In this day, where email is with me wherever I go, and the web is but a finger touch away, my dining experience has been influenced, if not dictated, by the offer of free Internet.

What’s Going On?

Am I crazy? Maybe. But I’m not alone. Why else would Krystal, Panara’s, and McDonald’s offer free Internet to their customers? It used to be that those restaurants would intentionally have uncomfortable seating so they could get you in and out quickly. Now they lure you in and keep you using the world wide web.

So I’m curious. Have you selected a coffee shoppe or restaurant because they offered free Internet? I’d love to hear your thoughts on it.

By the way, I do have an AirCard from a cellular provider, but I don’t carry it with me. It stays at home since it is the home office’s connection to the Internet. And considering the number of times per month I actually visit a coffee shoppe for their Internet connection, it’s far less expensive to occasionally savor a few cups of coffee there than to pay for another AirCard.

Cheers!

Joe

Book Review – Learning SQL Server 2008 Reporting Services


SSRS Book Image
I recently received a copy of “Learning SQL Server 2008 Reporting Services” by Jayaram Krishnaswamy published by Packt Publishing.

If you are relatively new to SQL Server Reporting Services and you’re a Tactile/Kinesthetic learner (one that learns best by doing rather than listening or reading), this may be the book to help you to quickly get up to speed on Reporting Services.

The author teaches by example throughout most of the book, using the Hands-On exercises to deliver much of the book’s content. I’d dare say that 70% to 80% of the book is in the form of Hands-On exercises.

The book is replete with screen shots to help those less familiar with Reporting Services follow along. For example, Hands-On Exercise 1.1 guides you through installing SQL Server 2008. The author goes to great lengths to ensure each step of the process is clear and unambiguous, using screen shots and commentary to guide the reader through each step. Exercise 1.1 is 25 pages.

If you are already well steeped in the SSRS, this book may be a bit on the remedial side for your tastes, though I suspect you’ll be able to glean some useful tidbits of information about the differences from prior version of the product.

The publisher has made a chapter of the book available for download if you’d like to take a peek before making your buying decision. EDIT: The link seemed to be for a limited time only so I’ve removed it from the blog post to eliminate confusion.

If you read this book, I’d love to hear you thoughts in the comments section below.

Cheers!

Joe

My Virtualization Setup

A couple of months ago, I blogged about the benefits of desktop virtualization. Since then, I’ve received several questions about my configuration and why I chose it. So, I thought I’d share with you the choices I’ve made and why I made them.

The Hardware
Let’s start with the hardware. As a consultant, I need to be mobile. I regularly work at different locations. Whether working from home, visiting a client, or traveling 500 miles per hour in a plane at 30,000 feet. I need to have my work-related stuff at my fingertips.

So, a desktop replacement laptop is my computer of choice. Mine is not necessarily the lightest laptop around and I must admit that when someone near me is sporting a 3 pound mini laptop I occasionally look on with a bit of envy. But, I take comfort in knowing that I have more computational power in my laptop than exists on the Space Shuttle. Plus I get a little exercise when carrying it.

For 10+ years, I’ve used Toshiba laptops. They’ve been rock solid and I’ve never had a hardware related problem with one. The only downside, and it’s significant if you travel, is that they have a horrible battery life. I’ve had a case of the hiccups that lasted longer than my Toshiba laptop battery.

For my current laptop, however, I choose a Dell Inspiron because I could custom configure it rather than having to take a prepackaged configuration from Toshiba. So far, it’s been good. Knock, knock, knock.

It’s nothing fancy. It’s a Dual Core Centrino laptop running at 2.0 GHz. It has 4GB of RAM and a 7200 RPM 160 GB hard disk drive. Certainly not top of the line, but it meets my needs.

My Operating System
For reasons I mentioned in the prior blog posting, I moved into a virtualized environment several years ago while using Windows XP as my operating system of choice.

Once I made that move, I quickly realized that the only activities that remained in my host operating system were email, blogging, writing, and other office related endeavors. All of these could easily be done in most any operating system. So I made the switch to Ubuntu Linux.

Using the LiveCD, the switch as very easy. With a LiveCD, you can boot your computer from the CD or DVD and run the operating system from there without installing anything on your computer, testing it to make sure Ubuntu will work properly with your hardware. If you like it, you can click a button the the desktop to install the operating system on your hard drive. It recognizes your configuration and asks what you’d like to do. For instance, it recognized that I had Windows installed and asked if I wanted to keep that installation and dual boot or replace it. When I chose dual boot, it asked about resizing my partitions to make room for Ubuntu. (It didn’t even mock me for wanting to dual boot.) Overall it was a very nice installation.

I started with dual booting my laptop for a while but over time realized that I didn’t need the Windows partition anymore so I got rid of it. Now I only have Ubuntu on my laptop.

My Virtualization Software
Although there are some open source applications available, I pay to license VMWare’s Workstation for my virtualization. It’s very nice. It allows you to manage your virtual machines and their settings pretty easily. You can clone VMs, take snapshots of them, etc.

I have several VMs that I use for different purposes, include some for development and administration, some for demonstration purposes, some for billing and invoicing, and even one for my iTunes account. Anything that I want to keep isolated for whatever reason, gets its own VM.

Other software applications
But I don’t do everything in Virtual Machines. I use quite a bit of open source software that is very good.

  • Office software – I use OpenOffice for my word processing and spreadsheet needs. It’s completely interchangeable with their Microsoft counterparts. For example, when I write magazine articles, I do it in OpenOffice and save them in Microsoft Word format. When I submit them to my editors, they never know the difference. We even send the articles back and forth using the Track Changes feature. It works great for 2003 and earlier formats, but only Ok for 2007 formats. I expect that’ll get better over time.
  • Email, calendering, etc – There are number of applications for Linux that are similar to Microsoft Outlook. Evolution, Thunderbird, and others can do most everything that I need, but I’ve opted for a completely online experience using web-based mail, calendaring, and task and contact management. I use Google for the first two, Remember The Milk for task management, and Plaxo for contact management. Having all of that available to me online, regardless of the computer happen to be using is nice. Plus it makes sync’ing with my iPhone unnecessary. It’s all available to me automatically.
  • Remote Desktop – Ubuntu comes with a good Remote Desktop software that allows me to RDP into Windows computers and servers.
  • Twittering - Adobe Air is available for Linux and I use it to run TweetDeck.
  • Text Editing – Emacs is my text editor or choice on either Windows or Linux.
  • SQL Client - RazorSQL is a java-based SQL client that works with SQL Server and other RDBMS’.
  • Graphics – Gimp, just as powerful a Photoshop, yet free.
  • Newsreader – Pan. It’s somewhat similar to Forte Agent, but doesn’t seem to be as powerful or flexible.
  • Instant Messaging – Pidgin works with all of the major IM protocols including MSN, Yahoo, etc.
  • Music – RhythmBox Music Player. Similar to iTunes.
  • Blogging – I’m currently testing ScribeFire, an add-on for Firefox for my blogging client.

Other things that I really like
I use Compiz as my windows manager in Linux. It has great graphics capabilities, including allowing me to view my four workspaces as a cube. If you’re not familiar with the workspaces concept, think of it as 4 different monitors or desktops in the Windows environment. Each is separate. You can open windows or applications in each of the workspaces without cluttering up any one desktop. I typically have email opened in one workspace, a virtual machine in another, TweetDeck in a third, and perhaps a browsers in the fourth.

Some things that I miss
Although there are Open Source equivalents in Linux for most things windows, there are some applications that are just far better in Windows. Some that come to mind are:

  • PowerPoint 2007 – It’s simply far better than it’s OpenOffice equivalent.
  • LiveWriter – Is probably one of the best blogging clients around.

Using Wine, you can theoretically run programs designed for Windows in Linux. I haven’t tried this myself, but I hear it has mixed results depending on the application itself.

Well there you have it. My virtualized work environment.

Are you virtualized? I’d love to hear about it. And if not, why not?

Cheers!

Joe

Does order matter in a JOIN clause?

Note: This is a repost of a popular article I wrote just over a year ago on my SQLTeam blog site. The original posting received quite a few comments and I’ve gotten more than a few direct emails about it. Many people were asking about the answer to questions #2 and #3, which I never did get around to posting. I recently received another request for the follow up posts so I pledge to get those posts written and posted soon. In the meantime, I thought I’d share the original article with you here.

A very astute SQL Server professional and reader of this blog posed the following question in a comment to a prior posting entitled “Does the order of criteria the WHERE clause matter?” The comment, submitted through Plaxo Pulse, is not available outside the Pulse community so I’ve included it below since it’s the genesis of today’s post.

“What if you were using join statements? Does it matter what order your columns are used in the ON statement? ~ Ariel M.”

There are several derivatives of the original question:

  1. Does the order of tables referenced in the ON clause of the JOIN matter?
  2. Does the order of tables referenced in the FROM clause matter?
  3. Does the order of columns referenced in the ON clause of the JOIN matter when multiple columns are used in the JOIN?

I’ll address questions 2 and 3 in another post; for now let’s consider the first question. To paraphrase and exemplify the question: Will the following two queries produce differing execution plans and thus varying performance? Or does the Query Optimizer take this into account as it analyzes a statement before choosing an execution plan?

--Query #1
SELECT
     c.Customer_ID
    ,c.Last_Name
    ,c.First_Name
    ,s.Order_ID
    ,s.Order_Date
FROM
    dbo.Customers AS c JOIN
    dbo.Sales_Orders AS s
	ON s.Customer_ID = c.Customer_ID
--Query #2
SELECT
     c.Customer_ID
    ,c.Last_Name
    ,c.First_Name
    ,s.Order_ID
    ,s.Order_Date
FROM
    dbo.Customers AS c JOIN
    dbo.Sales_Orders AS s
        ON c.Customer_ID = s.Customer_ID

Notice that the only difference in the two queries lies in the ON clause of the JOIN. The first query references Sales then Customers while the second reverses the order.

To answer this question, let’s look at the execution plan as provided by SQL Server Management Studio. The first query produces the following execution plan.


In this query, SQL Server has chosen a Clustered Index Scan of the Sales_Orders table and an Index Scan for the Customers table. The two tables are joined using a Hash Match Inner Join.

Now, let’s look at the execution plan for the second query.


Query #2 produced the exact same execution plan! So, we can conclude from this simple example that the order of tables referenced in the ON clause of a JOIN doesn’t affect the performance of a query.

Cheers!

Joe

It’s never to early to learn

Recently, friend and fellow MVP, Louis Davidson (aka Dr. SQL) emailed me a picture of his grand baby. She’s a fine looking little girl. And she’s got brains, too, as evident by her choice of reading material. For those without great eyesight, she’s thumbing through a copy of one of my books. According to Louis, she picked it up herself.

DrSQLGranddaughter-2009-04-01

I don’t know how to break it to her, but Notification Services was canned before she was even born.

Follow

Get every new post delivered to your Inbox.