Archives

Archives / 2011 / October
  • Convert comma separated string to table and vice versa

    Tags: Performance, Tally Table

    I know that this subject has been blogged about numerous times but I thought I'd post an alternative way of shredding a comma separated string to a table that I come up with today, albeit with help from the very clever Itzik Ban-Gan cross join nested CTE that many of us use to generate large tally tables quickly.

    For sure, in the ideal world we wouldn't have to do this as there isn't an easy and efficient way to do this in T-SQL. There are much better ways of passing this type of data to the … more

  • Finding orphaned database users

    Tags: Admin, Users

    Database users can become orphaned for a few reasons. For example, a database restore to another instance or deleting the corresponding SQL login would have the effect of leaving a database user orphaned. Development/testing SQL servers may also experience this due to the number of ad-hoc updates that may occur over the years. If you are unlucky enough your production servers may even suffer from this.

    SQL provides a procedure that will provide this information on a database level:

    EXEC … more

  • Simple XML exist performance enhancement

    Tags: Xml, Performance

    I came across this really simple best practice from BOL for the xml method exist which when used will give you a much cleaner and faster query plan. If like me you commonly use the name of an element or attribute as part of an XQuery predicate, then you would benefit immediately from this.

    So lets create a table with a single blob of xml and try out two queries to show this in action. The full script can be downloaded from here

    Setup

    --Create example table

    CREATE TABLE XmlTable

    (

    Id INT … more

  • Denali

    Tags: SQL 2012

    Is all set to be officially known as SQL 2012 and will be finally released in....... 2012!

    Announced at the PASS Summit 2011, it is due to be released to manufactoring in the first half of next year.

    The other major announcement from PASS was Microsoft actively supporting the Hadoop project. more

  • SQLBits9

    Tags: SQLBits

    SQLBits9 – “Query across the Mersey” was held in the majestic Adelphi hotel. This was my first SQLBits experience and was fortunate enough to be able to attend all three days. I was not disappointed! Walking through the main door and up the stairs into the main conference hall, I could see that the hotel was steeped in history. Huge open rooms with high ceilings gave you a sense that this was going to be a bit special. The first thing to greet me, other than the guys dealing … more