Category Archives: TSQL Tuesday

My first TSQL Tuesday post on the new blog is an invite by my friend and sql sister Mickey Stuewe (b|t). It is regarding data modeling practices that are well avoided and how problems rising as a result of bad data models can be fixed.

I have several experiences to recount in this regard as my first ever job was with doing data modeling and design. I will pick one of them for this post. Several years ago, I was working for a startup company as a junior DBA. DBAs also had their say in how data models were designed, although we didn’t always get the final word on it. Since it was a new company working off its own software we had some freedom to design things as we wanted to. The issue we had on had was to do with some code tables. Or rather, a code table. We had tables for products, customers and invoices. Now, we had an issue with some other aspects of the business – we needed a few lookup tables. We had a product type, a customer type and an invoice type. In addition to that, we needed payment type, address type and a whole of other ‘types’. The structure of these were more or less similar – a code and a description, in most cases. In a truly 3rd normal design – each of these would have been their own table with a foreign key relationship into the table it was used in. Small tables, no doubt, but specifically intended to store the lookup data in question.The senior DBA to whom I was reporting to had an opinion that a lot of small tables were adding up to the schema getting large and therefore difficult to manage, and that the queries would get too ‘deep’ over time. So he made a call that we would have a generic ‘code table’. A simple denormalized table with three fields – a code, a code type, and a code description. All of our codes – product type, customer type, invoice type, address type – everything would go in that one table, with the code type telling us what it was, and a code description giving some additional information.Programmers were thrilled with the idea, since it meant they had only one table to work with for most of their queries. Initially, this code table only contained about a 100 or so records and seemed like a workable idea. But the business started to grow, and along with it the table too. We started running into issues as below:

1 Almost all the queries in the application were referring to this one table – leading to it getting hit too many times.
2 The queries got increasingly cryptic when they tried to refer to one specific range of records – for example, an address type was indicated by a code A, and their query would say ‘code_type = ‘A”..another query would have code type = ‘I’ and so on..when the programmer left nobody really knew what these letters meant. And yes, there was no third table to decode the meaning.
3 The business wanted to manage the data, and there was no easy way to give them an interface. The data was a jumble of many different things and could not be managed with one screen. The programming effort needed to give them multiple screens was huge.
4 Some of the ‘codes’ started needing additional attributes – phone number type, for example, was initially just cell phone or residential.Then, some customers started asking for a second residential line, so you had to say residential 1 or 2, or cell phone 1 or 2.
5 There were no foreign key relationships defined – so the dependency was on code to enforce the relationship. Code had bugs, and that resulted in bad data getting in very often.

In short, it got so very complicated that the CIO had to approve a project to normalize the whole thing. The rewrite involved creating several smaller tables, putting in the right foreign keys and relationships, and creating interfaces for the business to manage the data. The big lesson that came out of it was that the size of the schema or fewer tables do not really mean a simpler/easier to manager design, in fact it can be the exact opposite.

Good database design is the backbone of a well designed and well performing  application – always try to learn what goes into it. A great article by SQL Server MVP Louis Davidson describes several issues like this and is worth reading.

This TSQL Tuesday is hosted by my friend Wayne Sheffield. The topic is on ‘giving back’..what do you think you can ‘give back’ to the sql community in the upcoming year?

When I was new to this country – I had a friend who was a psychotherapist by profession. Towards the holidays – when everyone took time off, went shopping, decorated homes and so on – she would be working double , sometimes triple shifts. She worked for herself, so nobody really made her do this. But it was the time when business boomed for her. More people were depressed and low towards the holidays causing more therapists to be in business. Now, I come from a country with a lot of poor people. Seeing more people depressed instead of happy during a festive season was news to me – but that is topic for another post. The point as she explained to me was that the need to give without adequate self nourishment made a lot of people feel very depleted and as a result, depressed and low. In some ways , our small community is also reflective of the bigger outside culture – we expect people to give more and more – volunteers need to do more, the people on PASS board need to do this and that, MVPs and friends who are doing well need to recommend jobs…on and on. What are we doing to ensure appreciation and self nourishment within our community?

I don’t mean prepping people up or tweeting about how much we love each other and so on , although those things are important. I mean things like looking out for someone who is out of work. If you find any opportunities that he/she may be interested – let them know. Put in a word of recommendation, if you can do it. Share your own stories, not just of successes, but of failures. Like the time when a technical interview was really hard and you knew in 10 minutes that you were a poor fit. Or the time when you wondered how having 500+ linkedin contacts is not leading to any successful job leads. Or when everyone is talking of the sqlserver job market booming and there is nothing remarkable happening in your neck-of-the-woods. We all have times like that. Sometimes, sharing those stories is the best thing we can do to help someone who is depleted. As I write this, I know of 4 people who are looking for work. They are all smart, hardworking, caring individuals who have given to community and are in need of self nourishment. My goal is to be there for them in whatever way I can. And for people like them, in the year to come.

This TSQL Tuesday is hosted by my good friend Chris Yates – the topic is on something new learned in the recent past. It is a simple topic but can sometimes be hard to articulate. I am a big believer in an open mind and learning anything new that comes my way – and have learned that the older you grow the harder this can be. The one practical lesson I claim to have learned, finally, in the recent past – is to sell myself and my work better. Or..to put it in other words, that publicity for what you learn and do is as important as the work itself.

I grew up in a culture that did not think too well of people – in particular women, being very extroverted. That combined with the fact that I was home-schooled for health reasons did not make me a huge extrovert by nature. My good friend Kevin Kline once did a survey of geeks and explained that most people who take to computers in a big way are, in some ways introverted. I don’t think being introverted has anything to do with self esteem, or even being shy or anti social. I just think these are preferences around how we are – that we prefer quality company over numbers, and are somewhat deliberate in our thoughts and actions. I do think though that it does impact how we sell ourselves – or publicise our work. For someone to know you you need to get out there and show them who you are – or it is more likely that those people who know far less than you do and drum up their work better will get the right chances to get ahead.

In my 13 years of attending the PASS Summit – I have always tried to spend time in classes, attending the occasional evening party. This time, I did something different. I spent most of my time networking – hanging out in community zone, asking people if they’d like to do lunches or dinners where we shared each other’s cares and concerns, and introducing myself to many SQL celebrities I had only known by name. I found the experience very rewarding – I got a lot of insight into options for career advancement, upcoming trends and changes in nature of work (more telecommute, more data analytics, more ‘unlimited’ vacation…) – not to mention the emotional support from a  huge number of #sqlhugs. I was selective, of course – in some ways, like I have always been. I usually hang with people whom I feel are genuine at some level, not just all about fluff, and are intuitively easy to relate to. That is something that I will want to keep.

My good friend Grant Fritchey once remarked  to someone who was a very strong techie and yet very humble and unassuming – ‘you need to be more assuming’ (he meant that you need to sell yourself more/better). That , in one sentence has been what I learnt recently – to be more ‘assuming’ while being true and genuine, at the same time.

I decided to resume my effort at consistent blogging again..after more than a year..with this week’s TSQL Tuesday Invite – this invite is by Tracy McGibben and is on ”Heroes”. There are many heroes in the SQL world who have inspired me and continue to. But there are two among them whom I wish to single out for this post.

1 Joseph Sack – I met Joe during a Sqlskills Immersion Event at Florida. I had known him to be the former director of MCM program before he joined SQLSkills, and I had a mental image of him to be atleast twice as older than he really was. Joe was much younger, very smart and thorough in what he knew, kind, funny and very easy to be around with. Soon after that event Paul Randal put out the yearly mentoring program that they do, and the candidates each of them had picked to mentor. I was long looking for a mentor – and was a bit saddened that I did not make it. I sent out a personal request to Joe – asking if he would mind mentoring me. Honestly did not expect to hear anything in the affirmative but he responded immediately – yes, he would be happy to. I spent a year on mentoring program with Joe – talking every month on things that mattered to me and asking for his advice, suggestions and like. After a few months we would up good friends who shared each other’s cares and worries rather than just a mentor and mentee. What impressed me about him initially are the same qualities that impress me still – humble,  down-to-earth, hard working and always willing to lend a helping hand. (Unfortunately Joe is no longer on social media or blogosphere for me to add any links). Proud to know you and be your friend, Joe.

2 Wayne Sheffield – I met Wayne as a speaker for one of my SQL Saturdays. That particular SQL  Saturday was not really a great event for me and my team. Two of my most senior volunteers had to depart to attend to some personal duties – I was very short on help and struggling to pull together with many things falling apart. One of the things that ‘fell apart’ were speaker shirts, which were misplaced. I was unable to find Wayne’s shirt – he had come with expectation that he would get one and needless to say, it was not the best experience for either of us(The shirt was found later). But the year after – I went on SQL Cruise Alaska, and joined Wayne and his family the day before for a tour of Seattle underground. During the cruise I got to know him much better and we are good friends. I have since followed his journey towards the MCM and various job changes too.He has  always been there to help me – with SQL advice, professional help, or just lend a listening shoulder. Like Joe – the qualities I admire with Wayne are his patience, humility, hard work, down-to-earthiness and willingness to help.

‘A person is known by the friends they keep’ is an old saying – so if people know me by my friends such as these, am sure I am known well 🙂 As a last word – would like to recommend everyone to find  people they vibe well with, not just people who are celebrities or big names. Find people who understand who you are, and are willing to support, encourage and motivate you. And do the same for them. Attend programs like SQL Skills or the SQL Cruise – they are not just for learning, I  have found some of my best friends this way.

 This is in response to Aaron Nelson’s call for T SQL Tuesday – on the subject of Logging. There are many things that come to a DBA’s mind while talking of logging. For this I chose an incident that happened some years ago. I had joined a new job, and the dba who had taken my spot had been there for several years before she left. She had set up most of the maintenence jobs on existing servers, most of which were on SQL Server 2000 then.

On the second day of my job I was asked to restore a backup as of a certain date/time to a development server to recover some data for an application manager. I was directed to the place where backups were stored. I found the full backup, and hourly transaction logs following that. As I set about restoring, I got a certain log that gave me a missing LSN error. The log job was set to run hourly, and the timestamps showed all logs were there and there were no errors on the job. I looked further to see if anything else was going on at that time when the log was backed up – I found a reindexing job in which the recovery model was being set to simple and back to full again. In 2005 and above if this change is done – the log chain is broken and one gets an error that a full backup is needed before restarting it. But in 2000 there was no such thing. The log chain was still broken but the job was not throwing any errors to show that. The backups were just not usable.
The process had been in place for more than 4 years, and luckily there had been no requirements to use the transaction log backups during this time interval. They even had a testing mechanism for testing backups, but they did not test backups during this particular sequence of operations. After that episode I also changed the testing process to work through job cycle – through all jobs and maintenence windows, in case there was any issue. It was a great lesson for me on backup testing and how many issues can remain dormant for years until one gets them accidentally. Luckily it was not a crisis situation when it was discovered but it might very well have been. Test your backups, and through jobs and processes as necssary, not just during small windows.