For the last few days my inbox, Twitter and Facebook feeds have been full of advice about which words I should stop misspelling. To be fair, in English, I have relatively little problem, and any misspellings I do make may be sloppy, but rarely result in misunderstandings. On the other hand, there are some T-SQL usages that really do cause problems, for myself and others ... 

SELECT

This is perhaps the first word we learn as T-SQL newbies, but there is still some confusion. Some people spell this with a star on the end - this is easy and natural, but it is often wrong and will only help you if you are either too lazy to write out a list of columns, or too intellectually incurious to care about performance.

AUTO_SHRINK

Actually, this is not so much a misspelling as a weirdly archaic word that is simply not acceptable in polite DBA society. Using it will fragment your indexes and your chances of social and professional success with equally devastating effects.

IN

I know what you're thinking. How could someone possibly misspell IN? However, as with English, there are some weird and wonderful things about T-SQL. In some circumstances you would be better to spell IN as EXISTS (especially when preceded by NOT.) The problem is that IN and EXISTS handle NULL values differently.

Jens Suessmeyer fro MS in Germany, came across the problem and gives a good example here: http://bit.ly/520pQM

Nor is this peculiar to T-SQL: it's the same for those in Oracle-land too: http://bit.ly/6fMRP5

In practice, I nearly always come across this problem when someone has changed a column to allow NULLS - they can then discover to their consternation that queries which "worked" previously now return no rows at all.

REPAIR_ALLOW_DATA_LOSS

You could be forgiven for using this strange spelling, as the word has indeed found its way into the language in this form. Although this spelling is correct, it is pronounced REPAIR_ENSURE_DATA_LOSS as you will indeed lose data if you use it. Please note, that using this word in the same sentence as "msdb" is a desperate faux pas, resulting only in pain and embarrassment.

And finally ...

I really could not let this article pass without recording my favorite misspelling, even though it has nothing to do with T-SQL. I once visited a financial services customer who had, just that morning, discovered a small typo in code that was re-implementing a legacy application. After a whiteboard session, where the notes had been left scrawled in an awkward hand, a developer had boldly sallied forth and coded up using RAND in place of ROUND. The result was a series of credit forecasts using a random number with the customers' closing balances as the seed, rather than using their rounded balance. Strange to say, nobody had noticed for ... well, let's just say for long enough. Even stranger, when the error was fixed, several of the financial wonks complained that the numbers were no longer so useful!  

I think I may have set myself an all-but-impossible task: to choose ten bloggers who write about SQL Server, and who have been outstanding in the last year. Nearly impossible, not because I can't find ten, but because there are so many more worthy of recognition.  In addition, many of those I will not be including are friends and colleagues, so the task may be as thankless as it is difficult.

Nevertheless, having set myself the goal, I may as well get on with it. My method was simple enough. I started with those blogs I subscribe to, and, of those, found the ones I bookmark most often. These were neatly objective measures, but I was still left with about 20 blogs to consider. Then I had to find some more subjective criteria: are the blogs helpful, insightful, original, well written, newsworthy, and so on. I excluded official Microsoft blogs, focusing instead on the community blogs, so there is only one Microsoft team member on the list.

Here then are My Top 10 for this last year. To be fair to the others who so narrowly missed out, I'll publish a longer blogroll later of those who I consider to be essential reading. For now, let me know what you think of my top ten, in strictly alphabetical order.

Bob Beauchemin
http://www.sqlskills.com/BLOGS/BOBB/
Like most of the bloggers in this top ten, Bob is an active and excellent speaker and writer. Bob is notably excellent when writing about data access and programmability, areas which require both sound understanding of the database technology and the ability to work with, and explain, the latest programming models. If you're an application developer working with SQL Server, then Bob is essential reading ... and don't miss his conference sessions either!

Rob Collie
http://powerpivotpro.com/
Rob is the only Microsoft employee on My Top 10 list because his blog is really very independent and hosted with a quite separate presence and identity. Rob has set out to create a compelling blog for the new PowerPivot product and he does a great job synthesizing his years of experience in the Excel world with his detailed knowledge of the PowerPivot technology. Even better, Rob presents compelling, easy-to-understand scenarios with a great sense of humor. If you're interested in PowerPivot, you need to follow this blog.

Kasper de Jonge
http://business-intelligence.kdejonge.net/
This blog has been a revelation to me this year. Kasper works in the Netherlands and blogs on BI topics. One outstanding feature of his blog is his use of copious screenshots. Often, with a new product just out in public like PowerPivot or the new Report Builder, Kasper sedulously records his experience with setup and first impressions, all captured with useful screens and comments. Even I learn stuff about setting up our BI products here! It's not just about installation either: Kasper explores many new features with the same careful approach.

Andy Leonard
http://sqlblog.com/blogs/andy_leonard/default.aspx
I really enjoy Andy's blog, not just for the technical posts (especially about SSIS), but for the way he writes with a perceptiveness and passion about the community of SQL users. Andy persuades, cajoles and encourages SQL Server users to get out and be part of something bigger: whether blogging, or simply attending a conference or event. Even better, Andy is always very clear about how community support fits in to an often challenging and difficult career path. 

Sean McCown
http://www.infoworld.com/blogs/sean-mccown
Now this is a kick-ass blog. In fact, often times you get the impression that Sean's key motivation in sitting down to blog for the day is just to kick some ass. But he chooses his victims well! Whether it is Microsoft's product teams, officious auditors, or even himself (for delivering a bad presentation), Sean is typically forthright and on target. Sean is also, like Andy Leonard, excellent at supporting DBAs in their career and personal development, with advice in the last year on technical skills, interview techniques and even office politics!

Adam Machanic
http://sqlblog.com/blogs/adam_machanic/default.aspx
The sheer breadth and depth of Adam's posts are testaments to his knowledge of SQL Server. I have only two things to say: read this blog, and try every code sample Adam posts. You'll be better for it.

Paul Randal
http://www.sqlskills.com/BLOGS/paul/
Paul is a former Microsoftie who often draws on his detailed understanding of the relational engine's internals to give unique insights on his blog. As an expert on DBCC, this blog is simply essential reading if you are interested in recovery or repair  - it's title is, in fact,"In Recovery." Even better, if you want to avoid recovery and repair, you need to read this. Paul also writes very entertainingly, which really helps with the often deeply technical matter.

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson/
Jamie's previous blog used to be called "SSIS Junkie." I don't think he has quite kicked the habit, as his technical posts about SSIS are always excellent, but there is certainly a wider range of interests on display here from data warehousing to SQL Azure.

Kimberly Tripp
http://www.sqlskills.com/blogs/kimberly/
Kimberly is inimitable, both on stage and in her blog. I wouldn't know where to start recommending her work - and if I started I could hardly stop. Let me take one example. Want to know about indexing? Read this blog - for the examples, the technical detail, the good humour, and the sheer practicality of the advice. And that's only one topic. Read the blog, every post.

Chris Webb
http://cwebbbi.spaces.live.com/
Chris is an OLAP guy, and if you know OLAP (whether in the form of SQL Server Analysis Services or any other vendor) you really should subscribe to Chris's blog for its breadth. For those specifically in the SQL Server sphere, Chris's posts on the MDX query language, and more recently on the use of PowerPivot DAX, are not only practical and perceptive, but help to stretch your skills and cover challenging scenarios.

So that's the list. What do you think? Anyone I missed out that you feel really needs to be there? And if so, who would you remove? I'd be fascinated to hear from you.

One of these days I really should blog about "dumb questions I have been asked at customer briefings." There's a danger in that, of course: namely, that a customer could equitably blog about "dumb answers I received from Donald Farmer." Nevertheless, the other day, a CIO asked me an excellent question. "Aside from core skills, which we always invest in, if we had an hour or so a week for DBA training in new areas, what would you suggest?" Here's what I recommended to her in email, as we followed up.

In the coming year, you'll see a new release, SQL Server 2008 R2, which is, we hope, full of goodies for your DBAs. As ever, there will some features immediately relevant to your business, some that will enable you to do new things over time, and some that you may not plan to use, but which may yet be of interest. So, in general I recommend using that extra training time to do three things: extend your current skills; expand your range with new skills; and explore and incubate some experimental projects.

Extend your current skills

I have rarely met a DBA with time on their hands, and I know that in your business they now manage more physical servers than ever, and with virtualization and consolidation, more instances and more databases, with more data, than ever.  So, if I may suggest one feature that you need to learn thoroughly in SQL Server 2008 R2 it is our multi-server and application management improvements. There is a great whitepaper here, from our team, on this topic: http://bit.ly/6yVmOL You'll find this really is an essential feature to save money and to manage a healthy environment in the coming years.

Expand with new skills

When we first talked about SQL Server together, back in 2005, we remarked then on how the scope of the DBAs role was changing. Not only did they manage databases, but with SQL Server your DBAs were also managing reporting systems, OLAP servers, and the ETL process. I know that your DBAs thought this was, and is, a good thing. They not only "owned the data" but all the surrounding services that integrated, enhanced and gave meaning to the data. In SQL Server this was relatively easy, as the development and management environments for all these services are highly integrated. However, there is another area which I suggest your DBAs should delve into : SharePoint. That is a new administration experience, so there is more to learn. But it will be a worthwhile investment of time. Here's why ...

Not only is SharePoint our fastest growing server product, it is also the heart of our collaboration platform; and, as such, SharePoint is fast becoming critical to Business Intelligence. You know about PowerPivot, of course. (See www.powerpivot.com for more on that, especially you can try the hands-on lab.) I expect that in your organization, your adoption of PowerPivot will be departmental -  I don't think you can hold the marketing guys back! In this case, I can see your DBAs getting very involved, not only provisioning data, but managing the infrastructure. There's are a couple of great blogs out there already exploring PowerPivot for SharePoint: www.powerpivotgeek.com and www.powerpivottwins.com - and if you want to give your DBAs a head-start on SharePoint there are excellent training links here on Arpan Shah's blog: http://bit.ly/5Ez7xT

Explore and incubate

Finally, I always think it is good to experiment. Even if you have no immediate plans to use a technology, learning more about it can often uncover useful cases, and prepares the team well for the day when the CEO, fresh from reading his latest business magazine asks "Shouldn't we be doing this?" This year, he'll be asking about the cloud. I can just about guarantee it. Fortunately, SQL Azure, the first significant relational database technology for the cloud, is easy to experiment with - in fact, the development and admin tools are basically the same as you are used to. See the team site for more information: http://bit.ly/7zdfAJ I'm not suggesting yet that you port any applications to the cloud - but we'll help all we can if you want to, just let me know. However, I am sure you and your team will find plenty of opportunities to host experimental applications and incubations. We'll be pleased to help with that too.

So, in short, those are my recommendations for those extra training hours in 2010. It is going to be a good year for SQL Server, and it's great to have you aboard.

 

It's getting close to that time of year when you're going to start seeing lots of "the year in review" specials on television. I started in my new role working with our customers last December, so it seems only fitting that I take a moment and go over some of the highlights in the SQL Server community in the last year - what I've seen, what I've learned, and what has hit the headlines. I have a wonderful vantage point, working with our partners, our clients, and with the SQL Server team here in Redmond. I've traveled to several states, participated in lots of user groups, presentations and conferences, and I've learned a lot about how people use SQL Server in their organizations and what we've done to make that a better experience.

Most companies started the year with a big emphasis on cost-saving and getting the most value out of SQL Server. I've helped lots of organizations figure out how they can migrate applications to SQL Server, and how to consolidate those servers onto fewer Instances - saving on hardware and software costs. This is a two-edged sword - you have to carefully plan these migrations and consolidations out, and understanding the right process to use (database stacking, Instance stacking and Virtualization) is vital to keeping the organization happy. Microsoft announced they would support using SQL Server in a Virtualized environment, and also began work on SQL Server 2008 R2 - which has even more options for consolidation.

And some organizations wanted to have even more flexibility, so 2009 saw the release of SQL Server Azure, the "database in the cloud". Each month I've seen more and more chatter on this offering, from small organizations that don't want to manage a server all the way up to huge companies that want the flexibility to rapidly create, deploy and manage their databases. Far from removing the need for a DBA, the data professionals are finding that their role is to help with their organization's data strategy, explaining how and when to use these kinds of offerings to reach the business goal.

This year has also been called "the year of the community", with the SQL Saturday movement becoming wildly popular, as well as an amazing turnout at the PASS conference. Almost 40% of the attendees to PASS this year were first-timers - and from the comments I heard, it won't be their last time either. At PASS the SQL Server Most Valuable Professionals (MVP's) wrote a book (which I'm still reading) called "Deep Dives" - with all of the proceeds donated to War Child, an international charity. They literally took Bill Gates at his word when he said to "give back".  Amazing.

Along with consolidation, many data professionals are focusing on performance tuning. They need to get the most out of the systems they already have. I predict that the consolidation efforts will continue, as well as the emphasis on perf tuning. I've taught several performance tuning seminars this year, and I've been asked to do several more next year as well.

So where will 2010 take us? Well, a new release of SQL Server, Visual Studio, new modeling languages, developer tools and administration needs. Look for a bigger emphasis on PowerShell - it allows you to manage almost any Microsoft product, and talks equally well to other platforms and database systems.  I also think that you'll see a pent-up demand for new projects as inventories run low and companies ramp up to supply demand. So buckle in. It's going to be a busy time.

One evening last week I was hanging out with a friend who is a professional photographer. As so often happens on such occasions, we whiled away some time comparing new toys, for we both had new cameras. Mine is small and perfectly formed (an Olympus, if you must know) and he had a high-end Nikon of such weight that I suspect it is mostly recommended by chiropractors looking for new work. However, my friend always carries a small point-and-shoot in his pocket, because, as he always reminds me: "The best camera is the one you have in your hand." It's no use having a great camera at home, if it's not with you when an opportunity arises; and, when the opportunity does arise, the camera to hand is indeed best.

Last week I also had six separate customer briefings in the Executive Briefing Center at Redmond. Now that the SQL Server and Office teams have just released their November CTPs, these were great opportunities to advise customers on what is coming in our next release and how to prepare for it. PowerPivot is by far and away the most popular feature, but I also had some surprising discussions around Master Data Services, our first foray into Master Data Management.

What surprised me, was that two of my customers, independently, said "We have needed Master Data for a while, but we could not find tools that we like. We'll certainly wait for Microsoft's solution."

Now, I'm flattered that they want to see Microsoft's offering, but really, if you have problems with master data you need to be looking at a solution, tools or no tools. (If you're new to the concept of Master Data Management or MDM, William McKnight has a couple of great articles, here and here.)

Fortunately, for these particular customers, even they can get started with MDM with the November CTP. For all SQL Server Enterprise Edition customers, Microsoft's MDS will be the tool to hand for Master Data, and therefore, as the photographers would say, the best tool for the job. Indeed, MDS is quite a comprehensive toolset, featuring: a master data hub based on the SQL Server relational engine; a thin-client stewardship portal for managing master data entities, and all their related hierarchies and versioning requirements; workflow integration and extensible extensible business rules; and role-based security.

During the briefings, we all agreed that we would start to review the tools technically, and to review the customers systems, and governance needs, as a matter of urgency.

To understand just how urgent the need is, I must return to my photographer friend. After comparing notes on cameras, our conversation turned to his finances. In particular, he was fuming about confused, duplicate and sometimes outdated information he was getting from a service provider following a merger: a classic master data problem. I am sure you have guessed already: he is an unhappy customer of one of my customers: and I know the advice he would give them. "The best tool for the job, is the tool you have. Just get on with it!"