SQL Server Bible

Covering Indexes

2006-06-08T19:43:00.000-06:00

A Covering Index is one of my favorite performance techiques. B ascially - a covering index is one that completely covers the needs of the query without having to resort tot eh dreaded bookmark lookup. This last weekend I made a ScreenCast about Covering Indexes. Enjoy! And if you have any suggestiosn or requests for more SQL Server ScreenCasts, email me.

Carbonite!

2006-06-07T19:54:00.000-06:00

I've been playing with a new utility - Carbonite! - and I love it. Bascially it's a background automatic file backup utility that slowly backs up all your files over the internet. If you're connected it's backing up. I can't tell it's running. This solves a huge problem for me since making backups is such a pain for independent IT workers. So I encourge you to try it. The first 15 days are free, then it's $5 a month no matter how many files it backs up.

Colorado PASS Camp

2006-05-17T17:00:00.001-06:00

What a Day! 87 attendees, 16 speakers, 2 MVPs, 20 presentations, 25 large pizzas, an XBox 360, an iPod, and an MSDN subscription all at the Microsoft officies in Denver celebrating the SQL Server community in Colorado.

It's Done

2006-05-02T15:13:00.000-06:00

SQL Server 2005 Bible is done; I turned in the last element last Friday and danced around the house. 54 chapters. 1418 pages. A publishing innovation I'm playing with is online ScreenCasts to augment the book's text and screenshots (Wiley's been great about letting me try new ideas). These are short 2-5 minutes videos that demonstrate a specific task or concept. I've posted the first one - Viewing Deadlocks using Profiler. About 50 ScreenCasts are scheduled in all. And if you have any suggestions or requests, send'em this way.

A glutton for punishment, I just signed to write another book. This one's on another passion of mine - merging object-oriented technology with the relational database engine. I've put about a year into developing an O/R Hybrid design in SQL Server. It has polymorphism, inheritance, code generation, complex association mapping, and first class objects - all hidden behind a T-SQL façade. The book will be out at the end of this year by Manning Publications, but you can play with the code as I work through the iterations.

An exciting project is the new Advanced Design and Optimization workshop's that's been through one beta round and about to go live. I love working with a small group and I've packed all my favorite database ideas into this one workshop.

May is a busy month and if you're in Colorado I'd love to see you at the Colorado PASS Camp, May 17th. I'm giving the keynote presentation on Performance Decisions. O/R Hybrid Designs is my topic at the Front Range Code Camp. And I'm presenting Data Modeling Key Decisions and Optimization Theory at DevTeach in Montreal. If you can't make it to one of these events, visit you local PASS Chapter.

As always, if you want to talk about SQL Server or database architecture, send me an email. I'd love to hear from you.

Complexity Kills

2006-03-28T15:12:00.000-07:00

In today's New York Times there's a story about Microsoft's problems and Vista's delays, Windows Is So Slow, but Why? In the middle of the story is a quote that caught my eye, In an internal memo last October, Ray Ozzie, chief technical officer, who joined Microsoft last year, wrote, "Complexity kills. It sucks the life out of developers, it makes products difficult to plan, build and test, it introduces security challenges and it causes end-user and administrator frustration." I couldn't agree more. I strongly believe the number one reason why database projects fail, and the number one reason for poor performance, is an overly complex schema. In the name of normalization, data modelers who can't write a query to save their life develop horrid schemas with several times the necessary tables. Chapter one of my next book is about the Information Architecture Principle. In unpacking the principle, I believe the first attribute of a database is simplicity. It may seem an oxymoron to begin a 1400 page book about such a complex development tool as SQL Server 2005 by writing about simplicity, but without simplicity as a primary goal, I believe your database project , and the DBA who come after you, will suffer. When designing database schemas, every table you can eliminate and still meet the requirements is a victory to be celebrated. Sorry to get so passionate, but I've seen too many friends labor with systems that should be out of our misery. It's a shame. Truer words have not been said, Mr. Ozzie, Complexity Kills

Colorado PASS Camp is filling up

2006-03-28T15:07:00.000-07:00

There's lots of great news about the upcoming Colorado PASS Camp! We heard today from Microsoft that the Colorado PASS Camp registration is almost full - 85 of the 100 slots have been spoken for. SQL Sentry has donated an XBox 360 for the solutions contest. And,the speaker schedule is almost full with an excellent line-up of local experts, I expect to be announce the speakers and their topics by the end of this week.

The Terminator;

2006-03-02T07:22:00.000-07:00

SQL Server 2005 - big changes - little changes. One of the little changes that's been a big change getting used to is the statement terminator, the semicolon. I've been forcing myself to place semicolons after every statement in my code, and going back through old code and adding the semicolons. They say that 21 days with a new behavior creates a habit. Maybe;

I have learned a few things about statement terminators;

1) You must place a semicolon at the end of the statement before a Common Table Element (CTE), otherwise the parser doesn’t know the WITH is starting a CTE and the batch will blow up like a bad action movie pun;

2) You can not place a statement terminator after the IF condition. Just treat the IF Condition -Next Statement as one long continuous statement;

3) You can not place a statement terminator between a END TRY and a BEGIN CATCH. Those two statements seem function as one single statement;

Right now statement terminators are optional, will they become required, maybe. I hear that one reason we don't have intellisense is that it's difficult to parse half written T-SQL without a statement terminator. Given the choice - I'd take required semicolons if that means we get intellisense;

I'll be back;

The Where Clause

2006-03-01T07:53:00.000-07:00

This morning Chuck Boyce invited me to co-host his podcast show, the Where Clause. What a blast! We talked about a few cool web pages , articles, and blogs, and then Chuck asked me about my O/R dbms research, and Optimization Theory. Look for the March 1st episode of the Where Clause

In other ground breaking news, today is March 1st - the public announcement for the Colorado PASS Camp - May 17th in Denver. We'll have a signup web page soon. Many thanks to Chris Shaw and Mike White for their help organizing this day of SQL.

Tom Rizzo's book Pro SQL Server 2005 with co-author Adam Machanic, is a good book if you already know SQL Server 2K and you want to get up to speed quickly. I recommend it.

Today, I'm wrapping up the author review for the Management Studio chapter for SQL Server 2005 Bible. After this only 10 chapters left to review. The end is in sight. If I get time I'd like to create a Camtasia screen cast on Using Management Studio.

The Cursor is Dead! Long Live the Cursor!

2006-02-28T08:23:00.000-07:00

Adam Machanic is one of my favorite MVPs - I like his books, he's always willing to help in the newsgroups, he speaks well at the conferences, and when he has an opinion, I pay attention.

SQL Server 2005 Bible will have a chapter called "Kill the Cursor!" that works through a complex logic problem and solves it 7 different ways - 4 cursors and 3 set-based solutions. For complex logic, set-based solutions rule; but not so for cumulative totals.

In some text for a future publication, a tech reviewer disagreed with me when I said that cumulative totals (running sums) were best done with a cursor. The tech reviewer listed what appears to be an elegant correlated subquery that does in fact do running sums. The assumption was that set-based was always faster than a cursor.

Adam was on the thread and volunteered to run the tests...
Adam's Blog

SQL Down Under

2006-02-26T09:00:00.000-07:00

At the European PASS Conference last week in Barcelona I sat in on Greg Low's session on Avoiding Recompiles in SQL Server 2005. Greg is the host of SQL Down Under and true SQL Server guru.

One of the key enhancements in SQL Server 2005 is statement level recompiles. I wrote about this in my TechNet article. Greg made the observation that with SQL Server 2005 you may see more recompiles but each recompile is much cheaper. He demonstrated recompiles and missed cache hits using SQL Profiler and explained in detail what causes recompiles and cache misses.

If you haven't listened to Greg Low and his SQL Down Under net radio show - I invite you to take the time to enjoy Greg and his guests. Ausie Ausie Ausie Oy Oy Oy!

Barcelona!

2006-02-23T13:37:00.000-07:00

I'm here in Barcelona for the PASS European Conference. The city is wonderful - full of Gaudi architecture and easy to navigate. My wife, Edie, has been exploring the city while I'm at the conference. I enjoy hearing the mixture of the languages and meeting folks from all over Europe.

The PSS Boot Camp pre-con day had deep presentations by Ken Henderson, Bart Duncan, and Bob Ward. I especially enjoyed Bart's session on Query Optimization. Tomorrow morning I'm presenting What's New in T-SQL.

On an edible note, the profteroles at Il Golfo di Napoli are a light cream pastry covered in melted chocolate. Amazing. The best desert we've had in Barcelona - right around the corner from the conference hotel. We held our Board dinner at Los Caracoles in the Gothic quarter - very cool resturant!

Of Endpoints, CTPs, and Virtual PC

2005-08-19T11:09:00.000-06:00

I’ve spent the better part of this week working with HTTP.sys endpoints in Yukon – well trying to. On my notebook nothing I tried would create the endpoint. On other machines the code would run great but not on my machine. I learned that XP SP2 still uses IIS 5.1 which listens directly and therefore can conflict with the kernel HTTP.sys when also listening on the same port. Nevertheless, switching ports did not solve the problem. I suspect that the problem is that I’ve uninstalled and reinstalled various CTP versions of Yukon one too many times. So running a clean new copy of Windows Server 2003 and the June CTP under Virtual PC is the only way I’m moving forward. And in this configuration, the HTTP endpoints run smoothly.

Building 35 and the O/R dbms

2005-08-14T12:12:00.000-06:00

I had the pleasure of speaking for the Pacific Northwest SQL Server User’s Group a few evening ago. It meets in Building 35 – the home of the SQL Server team, the temple of SQL, the database monster garage. Kalen, Fernando, and Randy were there. Several Microsofties including the authors of the SQL Server BOL were there. I had a blast.

My topic was my O/R dbms layer and afterward the group sparked some good questions and ideas. The conversation even moved into the differences between modlign a relational database vs. modeling an O/R dbms.

If you’ve eve wondered about how to store classes and objects in SQL Server including inheritance, polymorphism, and associations then download the files from the files page on http://www.sqlserverbible.com/. In my next book, SQL Server 2005 Bible, I’m planning to include the O/R dbms schema as a chapter.

The Island Query

2005-07-31T13:25:00.000-06:00

Yesterday I saw the movie, The Island. SPOILER WARNING. When the facility director realizes that some of the clones are beginning to develop in ways he did not anticipate, he asks one of the security guards to get a list of all clones from the Echo generation who have not yet been harvested. Which got me to thinking, Do security guards know SQL in the year 1019? Do they use some other ad-hoc reporting tool? Did he have to submit a request to the IT dept? Hmmm.

Dell XPS & Training Visual Environment Tools

2005-02-04T20:36:00.000-07:00

I'm using a new Dell Inpiron XPS notebook for my database development wor, testing Yukon, and writing my next book. Wow! What a machine. SQL scripts literally run 10 times faster than my old Dell Latitude notebook (1.4 Gb, 1Gb RAM). The XPS has a 3.4 Gb hyperthread CPU, 1 Gb of dual ported DDR memory, a 7200 rpm 60 Gb hard drive, DVD burner, and an ATI 9800 graphics card.

While I've not had any time for gaming, I did try Rise of Nations, which displays the frame rate. The old Latitude did 6-7 frames per second. The XPS is a steady 75.
The display looks perfect. The images are bright, with vibrant color and can be seen even with indirect sunlight. The resolution is 1920 x 1200, so the fonts are way too small, but setting Windows to large font, and turning on clear font makes it a pleasure to use even with my aging eyes.

One problem was that the resolution made the mouse pointer incredible tiny - about 1 mm in size. And the normal colors of the Windows cursor made it impossible to see. The solution was to buy Cursor XP. This utility is worth the price. The freeware version can load ten strange freeware designed cursors, but the full copy can also alter the cursor size and color. So now I have a very large, bold bright yellow cursor. It has the impact of a yellow bulldozer, but I never have to search for it.

As a trainer, I'm always looking for ways to improve the training experience. Cursor XP also has the cool ability to make a small subtle circle animate from the pointer on clicks or double-clicks, letting the students visually see when I'm clicking somewhere. (At times, I've said, "click" just so students can more easily follow a demo.)

When I was a Learning Tree instructor, they used a way-cool system with three instructor touch screen monitors, and two projectors. It had the freedom to draw or write anywhere on the screen. CP Chalkboard, form Milori.com is a slick transparent surface that covers the whole screen when it's enabled with a hot-key. Then, the instructor can write, draw, highlight, point, or even spotlight to visually enhance the training. It's dirt simple to use, and looks great.
I'm also using a new DELL MP 2300 projector - DLP 1024x768. This thing is significantly brighter and crisper than nearly every other high-end projector I've seen.

Between PC Chalkboard, Cursor XP, and the Dell projector I'm now satisfied with my live-code training environment.

New Endeavors

2005-01-28T18:00:00.000-07:00

Today marks a new beginning for me. After 2 1/2 years with Compassion International as Enterprise Data Architect, I'm returning full-time to the role of independent consultant / author / trainer. I'm excited about developing courses, working on several projects at a quick pace, and I enjoy teaching SQL Server. So the move makes sense. I'm also thrilled to be working alongside the other MVPs with Connected Innovation. Between Yukon, Connected Innovation, developing the new SQL Server Optimization course, and writing SQL Server 2005 Bible, the rest of this year is going to be a whirlwind.

Bulk Insert and Hex Codes

2005-01-26T21:00:00.000-07:00

I've been working with an airline to redesign their reservation data mart. All has been going well until tonight's data extract from their reservation vendor - all of the bulk insert sprocs failed. The error pointed to data length problem, so my first thought was that the data format had changed without warning. It turns out that the row termination character had changed and bulk insert wasn't seeing the end of the last column. But the new terminator wasn't obvious. In notepad, the text data looked normal. After Googling to find a good hex editor (it's been a while since I got to use a hex editor for data detective work),and downloading XVI32 hex editor, I discovered that the new row terminator was hex 0x0A. Setting bulk insert's terminator value to the hex value worked wonderfully.