THE SQL Server Blog Spot on the Web

in Search

James Luetkehoelter


  • A note to those going to the PASS conference

    For those of you lucky enough to be able to go to the PASS conference in Seattle this week, I have some advice (which you may take graciously or throw aside with contempt - up to you): try not to learn too much.

    The great thing about the PASS conference is that there are so many sessions, so many great speakers and so many topics that you are quite litereally unable to find a time where you couldn't be attending a session, doing online labs or visiting Ask The Experts areas. If you've been there before, I think you'll agree it can be overwhelming. If you haven't been there before, hang on for the ride.

    With so much information, it actually can be difficult to take away concrete knowledge that you can use in your everyday job. Here's some advice:

    When you go to a session, write down the numbers 1) 2) and 3). Find 3 items in that session that really spark something in your thinking or touch on something that hasn't occurred to you. If you learn more, great. However, getting a good solid 3 points a session is nothing to sneeze at. If you try to take it all in, less will stick. Instead, find the hidden gems that really stick in you head and hold on to them - they rest as they say is gravy.

  • An open letter to all IT Managers/CIOs: Invest in training, please

    To whoever manages SQL Server DBAs\developers\BI specialists:






  • Who out there writes perfect code?








  • How *not* to handle a compensation step on failure in an SSIS package

    Just stumbed across this where I'm working. Someone created a global error handler for a package that included this SQL step:

     DELETE FROM Table
       WHERE DateDiff(MI, ExportedDate, GetDate()) < 5

    So if the package runs for longer than 5 minutes and fails, nothing gets cleaned up. Please people, don't do this...

  • Hilarious

    I don't know how many of you know about this site, but it raises my spirits on a daily basis. I found today's entry oddly familiar...


  • I can't believe I fell for this


  • Survey: Do you write custom SQL CLR procedures/functions/etc

    I'm quite curious because despite the great capabilities of writing CLR-based stored procedures to off-load those nasty operations TSQL isn't that great at (like iteration, or complex math), I'm continuing to see a wealth of SQL 2008 databases with complex stored procedures and functions which would make great candidates. The in-house skill to create the CLR code exists as well, but there is flat out resistance to use it. In one scenario I was told "Oh, iteration isn't a problem because we've trained everyone to use WHILE loops instead of CURSORs" (um, still iteration folks...find a set-based solution or get that 11-hour processing stored procedure out of TSQL!).

    So, who uses them? What do you use and what kinds of problems have you solved?

  • Survey: Do you find surveys annoying?

    I'm sorry I couldn't resist...
  • Survey: How much data do you work with?

    Andy isn't the only one that can ask a survey question. This is something I really curious about because many of the answers or recommendations or rants in blogs are not universably applicable to every database - small databases must sometimes be treated differently, and uber databases are just a pain (and fun at the same time).

    So, how would you classify most of the databases you work with:

    1) Up to 50GB

    2) 50-500GB

    3) 500GB - 2TB


  • Now I've seen everything: A database with a Y10 bug...

    ...this is 100% serious. I have encountered a system that has a single digit year identifier that rolls every decade...I will now start working at McDonalds...
  • A counter-counter-counter(?) argument: Specialization vs Generalization

    This post is a reply to a reply to a post from Brent Ozar from K. Brian Kelly. The basic argument going on is that you if you want to be successful in the future, you should:

    1) Specialize in a specific area


    2) Generalize and gain as much exposer to as many technologies as possible

    (This are overy simplifications of both views, I do this just for the sake of the argument)

    My own view is that both viewpoints are incorrect. The key to maintaining your marketability in the future is being both a generalist AND a specialist. And to make the argument more confusing, the specialist part will shift continually.

    Example 1: The pure specialist

    Let's say person A is extremely adept at RGP programming for an AS400. Right now, that remains an excellent skillset as far as hirability. However, perhaps 5 years from now IBM abandons the AS400 platform altogether and moves to a Unix-based, or MS-based or 3rd-party OS to go along with its hardware. RGP is gone. Now everything is writting in IBM-SQL, or IBM-Java, or something completely different. Now person A must completely retool just to get a job. Or, change professions all together. Yes, there will be the legacy shops that are reluctant to change, but that work will last only so long. And with today's economy, can anyone rely on retirement anymore?

    Example 2: The generlist

    I fall a bit into this area. I know an aweful lot about datababase systems, network, servers, business process, etc. (and I mean etc., I have a wide range of exposure). However, without focusing on specific area, I limit my usefulness. Let's say I'm hired to work with SSIS. Yes, I know it, and pretty well. But the really esoteric knowledge - know - I have to look it up. It doesn't matter to my employer that I'm answer questions to others in IT about SQL Server in general, suggesting different security schemes, interacting (successfully) with other business groups, identifying concrete business rules that previously remained ephemeral - if fix the very specific issues with SSIS quickly (which doesn't rule out Google, but knowing what to look for - Google now returns so much information to wade through, you need to know your subject someone before using it), I'm fired. Period. Fair enough.

    Example 3: The generalizing specialist

    So let's take how I try to focus, not always successfully. I consider myself a data specialist (as in data quality, efficient reporting, storage, etc) on the SQL platform. I also know a significant amount about programming, SSIS, SSAS, SSRS, operating systems (Windows and Unix), hardware, network, business process, etc. Theoretically I could jump into any job in any area and be productive from the start and become a specialist very shortly. I may need to change my specialization very quickly at any point to maintain my marketability. Right now SQL Server maintains a very competitive piece of the database market. I'm specializing more and more in SSAS, SSIS, and SSRS (also known as business intelligence, a term I despise). But let's say in 10 years Oracle wins the database wars over IBM, MS and open source. I also know the Oracle platform and continue to try to follow it. By generalizing a significant amount of my skills, I keep a foothold into other technology areas to have the tools to cross over faster than the COBOL program in Example 1. And unlike the Example 2, I've demonstrated the ability to be a specialist and have more credibility if I build my Oracle skills ( in this fictional example :) ) and proclaim myself an expert. Heck, I can probably take many of the things I've learned as an expert into the new area.

    I believe Example 3 is the way everyone should view their career. Another great example is language. Most of us on this site are "experts" (if you're from Great Britain you'd debate that) in English. But what if most of the business out there comes from China? Wouldn't be in my best interest to become as proficient in Mandarin as possible? What if most of the work moves to India, and India now decides that business should be done in Hindi rather than English as it is now? Shouldn't I learn Hindi? How about Quebec conquers Canada, and becomes a superpower? Shouldn't I learn French (or as the French would say, Quebecois)? Adaptability is important. Now if I had to learn these languages from scratch as things change, I have a huge hill to climb. Luckily I study languages, including French, Hindi and Mandarin (and 3 others - I'm trying to surpass my father who spoke 9 languages - for the most part, fluently). Now right now I stink at most of them (French is my most fluent), but I have an advantage over others should China take over the world. Don't I?

    Specialization and generalization can *not* be mutually exclusive. I would advise those entering the industry to learn as much as possible, while attempting to focus on a specific area. Yes, you may not have the depth in a specific technology like our RPG programmer in Example 1, but you should be able to handle 90% of what comes your way and have the knowledge to find the answers to what you don't know off the top of your head.

    That's my take. Am I wrong? Crazy? Just drunk?

  • Book Review: Pro Full-Text Search in SQL Server 2008

    Those of you that know me know that I don't endorse anything lightly. MVP Michael Coles sent me a number of his books in exchange for mine (still coming Michael), and I just finished perusing the first one on my list: Pro Full-Text Search in SQL Server 2008. I highly endorse this book to anyone jumping into the Full-Text arena - and my hope is that more and more are, it is a great technology. Michael and Hilary Cotter (who contributed, not sure where, but it matters not) did a fine job with it. Make no mistake, this is a difficult topic to tackle and a complicated feature in SQL Server 2008. Even if you have worked with Full-Text Search in 2005, there's a lot to learn since it changes dramatically in SQL Server 2008. It's well written, organized and very detailed. Will it tell you everything you need to know about Full-Text Search - of course not. But it covers quite a bit.

    This is not a book for a novice. The first few chapters focus on the basics of administration, but the book quickly moves into some fairly complicated ways in which the technology may be used. One of my favorite sections is a discussion on multilingual databases. This is an extremely important chapter - if you don't work with multilingual data yet, trust me, you will. Even for languages with a Latin character set can create confusion for Full-Text Search if it isn't aware of how the language uses accent marks. For example, in French there is the accent a gu (or aigu) - é and the accent grave - è. There can be words that are very similar in spelling, but use different accents. How does FTS know how to decide what matches? And those same accent symbols in Spanish may have completely different rules. Now through in languages like Arabic (reverse order) and Chinese, and the ability to truly match words and phrases can be very difficult. This book shows how this can be accomplished with real examples.

    More practical examples follow showing how to full-text-index blob data, blogs, create custom thesauri, etc. It even covers the dynamic management views for Full-Text Search. I highly recommend this book, which I don't say often about anything :)

  • An open letter to Microsoft: Listen to the real world please

    Before I start, I want to say I have a lot of respect for the MS development people, and many of those in MSC. But Microsoft, you have a fundamental disconnect with what is real and what you're moving towards.

    After hearing of the layoffs and the the end of Performance Point:Planning (I'm not entirely pleased with Performance Point as an end-user multi-dimensional data consumption tool), I started thinking more about the product and features MS is marketing. I've also been recalling encounters with MCS people brought in to provide an optimal "solution" or fix an issue with something with SQL Server or Sharepoint or Biztalk (the areas that I know). In almost every instance (almost), I see MS marketing, product development and consulting very out of touch with what people really need. Initially when Bill et alia started MS, no one knew what to do with computers, so they needed to very much steer the technology to show how it can drive business. I believe we've reached the turning point with technology and business. I understand that when MS comes in to a company, they're focused on their product and their product alone. What needs to change - look at the business.

    So what does a buiness really need (and we'll stick with databases since this is SQLBlog):

    • A business usually needs dire help organizing, managing and understanding its own data: One truism that most of us know is what a business asks for and what they really need are often two very, very different things. This entails not simply performing tasks but asking questions like "Why do you need this data? What questions are you trying to answer or what are you hoping to learn?" Then the true need often reveals itself.
    • A business needs people that truly understand their data: I'm not talking just about the data structure of the database, but the business meaning of the data, and what I usually refer to as the "application" meaning - how is this used or exposed in an application? This starts us down the road of Master Data Management, which MS is far, far behind in facilitating tools for this. Yes, there is something they acquired, but it is slow in coming and looks to me to be "cookie-cutter". Right now my best tool would be Excel or another SQL database...
    • Instead of buying a really generic tool like Sharepoint or Biztalk (or SQL and all it entails), a business needs to understand how these tools can be used to improve their business: I don't know how many times I've run into a SQL BI implementation with no thought into how it can really help the business - the assumption is the business knows. The same goes for Sharepoint (an information nightmare if not organized consistently) or Biztalk (usually overkill). Seldom does anyone selling or installing the product take the time to discover what might be useful for the business.
    • A business needs a way to quickly consume their data: I'm sorry PerformancePoint (and Proclarity) - you aren't cutting it. PerformancePoint is basically Proclarity rehashed - I believe if Proclarity was still in business they would have a had a new, more intuitive, useful version of the product. And things look now that PerformancePoint is close to the chopping block. Excel? Excell services? Um, no. If anyone has used some other data visualization products, you can see what is really possible from multi-dimensional data.

    What's the common theme here? Business understanding is necessary for a successful product. It's time MS starts thinking this way and stop trying to assume a business process with any of their products, or assume a business understands how to use their own information (or even what it means). Does that mean MS needs to have a business consulting wing? Maybe. But the days of "here's our product, buy it and all will be well" are long, long gone.

    Anyone disagree? Am I drunk or insane?

  • 5 Reasons why I think MDX is the Devil

    First, an up front apology to Mosha - this is half in jest, half real. MDX is a powerful and useful language, but I find that its very existence has made my life very difficult when it comes to developing a solid Analysis Services solution. That's not to say I don't use it, or endorse it, but I worry greatly about its overall usage.

    1) It tempts you by looking like SQL, but once your brought into it you realize how complex it is (if you don't have Mosha's MDX Studio, get it).

    2) MDX allows you to do TOO much when it comes to a multi-dimensional structure, without first understanding multi-dimensional concepts. I've seen code that creates filters or subset cubes that overlap or end up cutting out measures implicitly. You have no warning about possibly undermining previous dimensional modeling - that would be a nice addition to see.

    3) Along those lines, it enables sloppy dimensional modeling in the first place. "Throw it together, will add in things later". With a multi-dimensional database, it is very important to model it correctly, just like you would a relational structure. Unfortunately there are no Codd/Date rules of normalization, and often very little work done up front understanding the actually relationships and what the data is meant to represent. For example, instead of using a many-to-many cube dimension (what I call the dimesion to measure tie) to represent something like a bill of materials, I often see this done afterwards somehow via MDX calculations and such. Instead of going back to the dimensional modeling, the answer is usually "we can do this in MDX later".

    4) Yet another language with its own syntactical oddities - damn you syntax, you will be the death of me!

    5) I can't count the number of calculations I've seen in a cube that could have been done before the cube is loaded in the first place. An MDX calculation that does something like PRICE * QUANTITY = TOTAL SALES is just a waste of processing on the cube side. Remember, MDX is post aggregation - so the large the cube, the more dimensions it has and the level of drill down it allows all play a roll in the speed of things.

    Those are 5 to start - anyone have anymore?

  • A response to Linchi Shea's excellent posts on fragmentation




More Posts Next page »
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement