THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Arnie Rowland

Discussion of issues related to SQL Server, the MSDN SQL Support Forums, the complex interplay between Developers and SQL Server Administrators, and our sometimes futile attempts to have a 'normal' life.

  • Is it time for SQL Server to have Isolated Transactions?

    SQL Server allows nesting of Transactions –in a sense. In truth, there is only one Transaction, with each ‘nested transaction’ serving to increase (and decrease) the @@TRANCOUNT indicator. The transaction can be committed or rolled back at any point that the @@TRANCOUNT value is equal to one. Error conditions may or may not cause the transaction to rollback –depending upon the specific nature of the error or the XACT_ABORT setting. (In this discussion, I am not going to address the situations where the @@TRANCOUNT is not properly decremented and also when commit or rollback is not accomplished –leaving a hung transaction.)

    And for many usages, the implementation works just fine. All of the work, or none of the work –at least that is what we hope for.  Sometimes it is necessary to jump through seemingly unnecessary ‘hoops’ in order to get it working as we wish.

    In the case of Logging, I need to log the activities and be assured that the log entries persist even if the parent transaction fails. With the current implementation, the activities of a ‘nested transaction’ are rolled back if the parent transaction rolls back, even when calls are made to stored procedures –all is lost if the parent transaction rolls back. Yes, there is a work-around. It is common to create a Table Variable prior to starting the parent transaction, populate it with values during the transaction scope, and then after the transaction is completed (either committed or rolled back) to then insert the rows of the Table Variable into the logging table. –BUT, if server power were to fail just before the inserts, any records of the transaction activity would be forever lost. And it makes it impossible to create a self contained logging procedure that can be executed with only one line of code in the transaction.

    I suggest that there is a need for a completely ‘Isolated Transaction’. That is, a transaction that can be started in the midst of a ‘regular’ transaction (parent), and that will be durable regardless of the outcome of the parent transaction. Using this ‘Isolated Transaction’, once a commit occurs, even a rollback of the parent transaction will not undo the saved work. The activity log is properly persisted and durable.

    In some situations, it may be necessary to force the parent transaction to fail and rollback if entries to the log cannot be accomplished. (I’m thinking of a security implementation where any activity, even unsuccessful activity must be logged.) That can be accomplished by the parent transaction examining the error that caused code execution to fall into the CATCH block and taking appropriate action, or perhaps, proper specification of the XACT_ABORT option.

    Consider the following:



             {do some work}


                {do some work}

                {Create Log Entries}


             {do more work}

          END TRY




          END CATCH




    I suggest that in the above scenario, the ISOLATED TRANSACTION should automatically commit (and persist) upon successful completion, or ABORT and exit upon any failure, reporting an error condition to the parent transaction. The parent transaction could (depending upon the XACT_ABORT setting) determine whether to continue or abort itself. IF the isolated transaction successfully completes, it is committed and durable regardless of the action taken by the parent transaction.

    Now this scenario seems to satisfy my issues of being able to reliably log activities in a transaction, and have those log entries persist if the transaction is rolled back.

    But additional questions arise.

    Stored Procedures

    What should be done IF the isolated transaction exists within a stored procedure that is called from the parent transaction code? How should the procedure handle an error that causes the isolated transaction to rollback? The RAISERROR statement in SQL Server has various quirks and should not be relied upon to communicate with the parent transaction –RAISERROR may itself cause the parent transaction to rollback and not allow the parent transaction to evaluate the error condition in its CATCH block.

    Should the procedure be totally autonomous and not attempt to communicate with the parent transaction? How would the procedure best communicate an error condition, either in the isolated transaction or not? Would it be enough if failure in the Isolated Transaction caused an XACT_ABORT error to be passed to the parent transaction?

    Dynamic SQL

    It is possible to make a compelling argument for an Isolated Transaction in dynamic SQL. How would/should the dynamic SQL communicate to the parent transaction if the Isolate Transaction were to fail and rollback? Should the parent transaction even care?

    Service Broker

    Having an isolated transaction could solve some issues related to Service Broker implementations –such as starting a batch process, guaranteed messages, and/or mail delivery. Is this a use case scenario that makes sense for you?

    Additional Considerations

    Some have posited that the isolated transaction ‘should NOT’ automatically rollback on error, but instead require a specific ROLLBACK statement. Something like this:


          {do some work}

          IF {condition}


          {do some other work}



    But this raises all sorts of questions and issues. What happens after the ROLLBACK if there is an attempt to INSERT/UPDATE/DELETE? Is a new isolated transaction started? Is it the same transaction? Is it an error condition? What happens if the ROLLBACK is omitted and an error condition occurs?

    Personally, I’m in favor of a simple approach to the isolated transaction –not at all bound by any previous SQL Server transaction implementation. Automatic COMMIT on successful completion, automatic ROLLBACK on error, and appropriate error messaging down the call stack. What is your opinion?

    Your help is needed!

    In order to best prepare and lobby for such a suggested ‘improvement’, it is important to have a clear understanding of the use case scenarios that we are attempting to solve with the isolated transactions.

    So I ask you, if you see benefit from having an ISOLATED TRANSACTION in SQL Server, what is your use case? How would you use it? What are the pitfalls?

  • MVP Summit 2009 -A Most Excellent Adventure

    For many of us, one of the high points of being an MVP is attending the annual MVP Summit hosted in Seattle by Microsoft. Yes, there is a lot of flash and hoopla, good food, and a party or two. But the real value comes from connecting and re-connecting with MVPs from around the world. And then being given  'inside' information from members of the product groups. Product group members answer our questions and give us insights into future developments for the product/technology that holds our interest. And, to their credit, they put up with a fair amount of pushing and pulling from MVPs –which, thankfully, is accepted in the spirit of 'the loyal opposition'. Everyone recognizes that we all have the same interests -the improvement of the products we use and support.

    So I guess that I could sum up the event as one of Relationships. Establishing, building and renewing relationships between MVPs, AND between MVPs and product group members. Relationships that will serve us well -now and in the future. Relationships that are as diverse and complex as the products we support. Relationships that are both intense and fleeting. Relationships that will persist even when only renewed at the next summit. Relationships that will strengthen our interactions in newsgroups, forums, blogs, and even Twitter. Relationships that continually improve the SQL Server community.

    A few MVPs were acknowledged for their outstanding participation in the SQL Server community over the past year.

    For finding and reporting Bugs –overall:

    1. Aaron Bertrand
    2. Steve Kass
    3. Dan Guzman

    The most ‘critical’ bug was found and reported by:

    Teo Lachev

    For Newsgroup participation:

    1. Erland Summarskog
    2. Plamen Ratchev
    3. Uri Dimant

    For MSDN Forum participation:

    1. Jonathan Kehayias
    2. Jacob Sebastian
    3. Mangal Pardeshi

    The 'MVP Choice Award' was presented to give special recognition for MVP contributions to the SQL Server community over the past year. The SQL Server MVPs that attended the summit could offer five nominees each. Those selected received a plurality of nominations. Summit attendance was not required, and two folks were not eligible since they were previously acknowledged. (See above.)

    The MVP Choice Award was awarded to:

    1. Paul Nielson
    2. Arnie Rowland
    3. Itzik Ben-Gan
    4. Kalen Delany
    5. Pinal Kumar Dave (tie)
    5. Jessica Moss (tie) 

    (I wish to humbly thank those MVPs that thought I should be included in such great company.)

  • You HAVE to Trust, you MUST Verify -and that may still not be enough!

    In Brian Kelly's recent blog post, he makes an excellent case outlining why there are few options but to 'Trust' SQL Server Administrators. And then he goes into excellent detail explaining that it may be impossible to completely 'prohibit' disruptive behavior, and that one should establish a robust auditing of security events.

    And it is not just the SQL Server Administrators, or the network administrators that require ‘trust’. It is anyone that has access to the ‘wire’.

    A while back I was working on a project that had to meet a HS/FIPS standards that mandated that all data in transit be encrypted. I recall sitting in a meeting where, in response to my request for the establishment of encryption, (possibly IPSec) between the web farm and the data cluster, the director of the infrastructure teams bluntly stated that it would not happen because 'we trust our people'. There was continued resistance to finding any alternatives to meet the encryption requirement. The network administrators were firmly opposed to having packets on ‘their wires’ that they could not ‘look into’. There were attempts to find some manner of ‘waiver’ from the standards. My arguments about the difficulty involved in discovering passive sniffers, or that anyone with access inside the firewall could easily install an ‘unknown sniffer’ were summarily dismissed as ‘overly concerned’. My team continued moving ahead in preparation to the time when encryption deadline became inescapable.

    A few months later, all IT infrastructure staff were required to undergo new background security checks. I was not surprised that some of the 'trusted people' abruptly resigned or were terminated. (I've noticed that about 15% of IT staff seem to either refuse to submit to, or fail security checks. Sometime termination is for issues that would not have prevented the initial hire, but became mandated since the issues were not disclosed on the application. Sometimes just 'youthful indiscretions'...

    And the data in transit was finally encrypted.



  • What’s in a Name?

    Why do folks continue to use outdated and unnecessary naming conventions? Specifically, the ridiculous ‘tbl’ prefix on tables, and the equally meaningless ‘usp’ for stored procedures. I think that it is because that is what they see illustrated in books, magazines, white papers and on-line 'example' code.

     So the real question is really: Why do authors continue to follow outmoded and wasteful practices? I mean, if the characters (keystrokes) do not provide ‘value’, why invest the time and effort? Why continue demonstrating something that has little or no value? Are 'old habits' really that hard to change?

    In my teaching, I often explain that all characters in an object name ‘should’ be meaningful; if a character does not add value, then it is a waste of time and effort. For example, if an object appears after the keyword FROM, it is very safe to assume that it is a data object (table, view, or table valued function.) Therefore, using the prefix ‘tbl’ (or ‘tbl_’) as a prefix to a table name is a complete waste of keystrokes (time and effort). However, It can be useful to use a suffix, such as '_vw' for VIEW, or '_tv' for a Table Valued Function. From context, it is almost always possible to identify the object being referred to as a data object. Three (or four) extra characters that have to be typed every time the table is referenced in both database and application code. What a waste.

    The same goes for stored procedures. Contextually, it is possible to know that an object is a stored procedure, therefore using any prefix is a waste of keystokes (time and effort) that does not provide value. Granted, for code objects that are distributed with applications, it may be sagacious to have a somewhat unique prefix in order to reduce potential naming conflicts with the clients’ use of the database. But in the absence of a ‘third party' products, I contend that stored procedure prefixes, especially the somewhat lame ‘usp’ is a complete waste of effort.

    I often work with very large projects, with very large development teams. It is not at all unusual for a single database to have 1000, 2000, even more, stored procedures. Without a sensible naming convention, folks will unintentionally duplicate effort. In one project, as we were nearing deployment, I found as many as 14 stored procedures that had the exact same functionality –just variations in naming. I ended up removing over thirty percent (30%) of the stored procedures (and forcing application code changes). In the ‘morass’ of thousands of T-SQL code objects in the source store, developers simply gave up attempting to find and reuse existing code because there was no consistent naming policy that allowed ‘finding’ what was needed with reasonable effort.

    In a later post, I will make a case for selective and succinct ‘suffixes’ on certain objects –but most definitely NOT prefixes. I will also delve into naming conventions that seem to really work in any size environment.

    What do you think about using naming 'prefixes'?

  • When Do You Know That You Know

    Why, when you can help others, that's when! 

    In my role as a senior Moderator with the MSDN SQL Forums, I am often asked "How do I become a Moderator on the Forums?"

    I am quite appreciative and heartened when folks indicate, and then demonstrate, their interest in helping others. Volunteering to assist on the MSDN Forums is an excellent 'give-back' for all of those times when someone offered us a hand, gave a suggestion, helped us solve our pressing problems.

    Participants typically arrive at the Forums the first time seeking help, and when they find the environment is supportive, and they are met with grace, they come back the next time they are stuck. After awhile, some begin offering suggestions to questions and problems that are posted. For many, it can become an exhilarating experience to help others that are in a difficult situation; it becomes a validation that we actually 'know' something. Many of us actually are 'pushed' to learn more, dig deeper, search for things we had not considered, re-consider solutions that we thought we had down pat, and reexamine nuances. For those that regularly visit and help on the Forums, the Forums become a source of self satisfaction -a place where we are 'validated', a place where we learn, and a place where we teach.

    So to the question of 'How to become a Forum Moderator?', let me offer the following:

    • When you respond to a question, don't just provide an answer -help others understand why your suggestion works.
    • Make an effort to offer an explanation for your suggestion. Offer a demonstration. Provide enough demo code so that a reader can easily reproduce your suggestion; make it a 'teaching' moment.
    • In any code that you provide, take a few extra seconds and follow 'Best Practices' -naming, formatting, etc. Remember, you are 'teaching' by example. How you do something often carries more long term significance than the specifics of what you do.
    • Many others will read the question and answers so try to be as 'generic' as possible in your suggestion and demonstration so that those additional readers can easily extrapolate and adapt to their problems.
    • Beware of keeping a positive attitude. Remember that once you were a beginner too. Assume that everyone is doing the best that he/she can at that moment.
    • Sometimes folks will be unappreciative, perhaps even 'snippy' toward you. That happens. Don't take it personally, just ignore it and move on to a different thread. Let someone else handle the post and the attitude.
    • If you find that you are annoyed by a post, let it go, move on to a different thread, let someone else handle it.
    • When someone offers a better suggestion than yours, complement them. Sometimes the original poster doesn’t know that it is a better suggestion and when you so indicate , they will more likely select the 'better' option.
    • When someone adds to or clarifies your suggestion, positively acknowledge their contribution, for it really is a group effort.
    • When it becomes obvious that you 'blew it', either from misreading the question, or responding before coffee, or just being in too much of a hurry in life, accept it and move on. It just seems petty to attempt to defend or offer excuses for our mistakes.
    • And most importantly, keep posting, keep contributing, keep helping others.

    In the MSDN SQL Forums, we (Moderators) notice those that are actively and positively contributing to the Forums. We have developed a process where we invite folks to be a designated 'Answerer', where we then actively mentor those 'Answerers' and help them hone their presence and skills and may eventually acknowledge that persistence with promotion to Forum Moderator.

    And it is a lot of hoopla about little. We don't get paid. We don't get free passes. But we do feel good about helping others. And we have some public acknowledgement for our efforts. And let me tell you, reading those two words 'Thank You' can make my day!

    So keep posting and helping ...

  • Through the Looking Glass: Elegant -or Not?

    SQL Server as an EAV Database -Good Idea? 

    It seems like I am getting more and more inquires from potential clients asking for help solving performance related issues with what, after some investigation, is using SQL Server as an EAV database.

    First, for those unfamiliar with the concept, an Entity-Attribute-Value (EAV) database seems disarmingly simple and seductive to many developers. In its simplest form, an EAV database requires only one table, and that table has only four columns: an Identifier, the Entity, the Attribute, and the Value of the Attribute. The entity might be a Customer, the Attribute ‘Customer Name’, and the Value would be the actual customer's name. The same table could also contain Products, or Invoices, or Employees, etc.

    The beauty of the design is that the developer is free from ever having to consider a table schema. Need another column, that’s just another Attribute/Value pair. Need another table -just a different type of entity. Add as many new entities, or new columns as is desired. One never has to negotiate with a DBA again. There is never a need to have to write that messy and sometimes problematic ALTER TABLE code again. Wonderful concept if you have to distribute changes to the data model in situations where the user (or application) does not have permissions to muck around with the database, the schema, and all of that DBA stuff. There is only one data model, and it is only one table that has four columns. What could go wrong with that?

    And, of course, it is possible to select the ATTRIBUTE/VALUE pairs for an ENTITY (or group of entities) and PIVOT the data to simulate a table. It seems simple enough –doesn’t it? (If you want more information, just search for Common Table, EAV Database, Table of Tables, etc.)

    In fact, the beauty is in the elegance of the model. Frankly, before the advent of SPARSE columns, you could save a lot of storage when there was a long list of potential attributes (columns), and most entities would only use a small portion of the potential attributes. And it works wonderfully for many situations: obviously, property ‘bags’, somewhat small and static tables –you know, the types of data that will be used to populate various UI controls, or be used for data validation, or internal processes.

    I’m not going to address the issues of the increasing complex code required to pivot the faux tables –I’ll leave that to later discussions. Today, I’m interested in performance issues. One client has an EAV database that is approaching two billion rows in that single table –and the performance is starting to become quite troublesome. They really didn’t like my even broaching the subject of a data model redesign –too much ego involved in this elegant design.

    I’m interested in your experiences, cautions, and concerns surrounding performance issues of using SQL Server as an EAV database.

    So tell me, when does something that is elegant from one perspective become burdensome and inelegant from another?


  • To Blog or Not to Blog -Is that Really the Question?

    Over the past couple of years, the chorus of voices asking, requesting, even demanding that I present my thoughts in a blog has grown so loud I can no longer ignore. As a long time teacher and trainer, I have great respect for the 'written word' -and even more for personal presentation. I thrive on dialogue, I refine and clarify my thoughts when challenged, I crave respectful debate. For I strongly believe that in debate, if we truly listen to each other, we both are changed. For too long, for professional reasons, I have chosen to keep my public voice silent. That now stops.

    So dear reader (not to be confused with the Dear Leader), expect to be challenged, to be cajoled, perhaps to even be confused as I open this pandora's box of exposition. My thoughts will certainly ramble at times, for I cannot easily function in an environment where technology exists free of worldly influences, where 'answers' and 'solutions' are the last word. The only answer to all questions is "It Depends". I freely acknowledge that I will never have 'the answer' -but I will often propose a potential solution in order to direct focus to a possible process that may lead to a temporal resolution.

    Life is not about obtaining 'the answer', but about learning to enjoy the journey. My wife, Carol, in the early days of our 'courtship', taught me an extremely valuable lesson. She seems to never get lost -often she is just having a different adventure than the one she set out on, discovering new things, experiencing life in all of it's spectacular beauty.

     I shall embark upon a journey where we shall have a 'most excellent adventure', so if you choose to accompany me, I welcome your presence.


More Posts « Previous page
Privacy Statement