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 ...
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
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.
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.
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
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 Beaucheminhttp://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 Colliehttp://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 Jongehttp://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 Leonardhttp://sqlblog.com/blogs/andy_leonard/default.aspxI 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 McCownhttp://www.infoworld.com/blogs/sean-mccownNow 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 Machanichttp://sqlblog.com/blogs/adam_machanic/default.aspxThe 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 Randalhttp://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 Thomsonhttp://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 Tripphttp://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 Webbhttp://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.
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.
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
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".
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!"
A few countries
around the world have a day set aside for giving thanks - and some do it all
year long. We stop to give thanks to those who have made us what we are, and
those in the past and present who have given us the benefits that we enjoy.
From teachers to family, we owe them a lot.
I've noticed that
several technology specialists, especially those that work with SQL Server, go
even further. They practice "Active Thankfulness", where they donate their own
time, money and effort to give back to the community. The Professional
Association of SQL Server, or PASS, is staffed with volunteers, and at the
recent PASS conference you could see this spirit of giving all over the event.
People lent a helping hand to setting up, organizing and staffing many
different activities, including those to help folks with their SQL Server
questions. Microsoft donated the entire Customer Advisory Team (CAT) to the
event, answering questions and delving deep into technical issues for free.
Many of the SQL Server product team members came over to staff the "Ask the
Experts" area, and other database professionals gave of their time to handle
the "Birds of a Feather" tables - again, all free, all volunteer.
And then there were
the SQL Server "Most Valuable Professionals", or MVP's, that donated not only
their time but an amazing amount of effort to create a huge book called "Deep Dives", with
all of the chapters and even the production costs donated. The money raised by
this book go straight to "War Child International",
a charity that aids children whose lives have been devastated by war. In the
introduction, the MVP's explain that the impetus for this book was a response
to Bill Gate's challenge to "do philanthropy where you are." And that's good
advice - each of us has learned from someone, whether that's a college teacher
or from a data professional that took the time to show us the ropes. You might
not be able to find that person, but you can pay it forward by helping someone
else. It can be volunteering at a SQL Saturday event, helping out at your local
user group, or volunteering your time to help a charity with their technology
needs. When you practice this kind of active thankfulness, you'll find the
rewards far outweigh the level of work you put in.