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.

  • 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