SQLAuthority News – A Conversation with an Old Friend – Sri Sridharan

Sri Sridharan SQLAuthority News   A Conversation with an Old Friend   Sri SridharanSri Sridharan is my old friend and we often talk on GTalk. The subject varies from Life in India/USA, movies, musics, and of course SQL. We have our differences when we talk about food or movie but we always agree when we talk about SQL. Yesterday while chatting with him we talked about SQLPASS and the conversation lasted for a long time. Here is the conversation between us on GTalk. I have removed a few of the personal talks and formatted into paragraphs as GTalk often shows stuff out of formatting.

Pinal: Sri, Congrats on running for the PASS BoD again. You were so close last year. What made you decide to run again this year?
Sri: Thank you Pinal for your leadership in the PASS India Community and all the things you do out there. After coming so close last year, there was no doubt in my mind that I will run again. I was truly humbled by the support I got from the community. Growing up in India for over 25 years, you are brought up in a very competitive part of the world. Right from the pressure of staying in the top of the class from kindergarten to your graduation, the relentless push from your parents about studying and getting good grades (and nothing else matters), you land up essentially living in a pressure cooker. To survive that relentless pressure, you need to have a thick skin, ability to stand up for who you really are , what you want to accomplish and in the process stay true those values.

I am striving for a greater cause, to make PASS an organization that can help people with their SQL Server careers, to make PASS relevant to its chapter members, to make PASS an organization that every SQL professional in the world wants to be connected with. Just because I did not get elected or appointed last year does not mean that these causes are not worth fighting. Giving up upon failing the first time is simply not in me. If I did that, what message would I send to those who voted for me? What message would I send to my kids?

Pinal: As someone who has such strong roots in India, what can the Indian PASS Community expect from you?
Sri: First of all, I think fostering a regional leadership is something PASS must encourage as part of its global growth plan. For PASS global being able to understand all the issues in a region of the world and make sound decisions will be a tough thing to do on a continuous basis. I expect people like you, chapter leaders, regional mentors, MVPs of the region start playing a bigger role in shaping the next generation of PASS. That is something I said in my campaign and I still stand by it.

I would like to see growth in the number of chapters in India. The current count does not truly represent the full potential of that region. I was pretty thrilled to see the Bangalore SQLSaturday happen early this year. I would like to see more of SQLSaturday events, at least in the major metro cities. I know the issues in India are very different from the rest of the world. So the formula needs to be tweaked a little for it to work better in India.

Once the SQLSaturday model is vetted out, maybe there could be enough justification to have SQLRally India. PASS needs to have a premier SQL event in that region. Going to USA or Europe for that matter is incredibly hard due to VISA issues etc. So this could be a case of where PASS comes closer to where the community is.

Pinal: What portfolio would take on if you are elected to the PASS Board?
Sri: There are some very strong folks on the PASS Board today. The President discusses the portfolios with the group and makes the final call on the portfolios. I am also a fan of having a team associated with the portfolios. In that case, one person is the primary for a portfolio but secondary on a couple of other portfolios. This way people on the board have a direct vested interest in a few portfolios.

Personally, I know I would these portfolios good justice – Chapters, Global Growth and Events (SQLSat, SQLRally). I would try to see if we can get a director to focus on Volunteers.  To me that is very critical for growth in the international regions.

Pinal: This is an interesting conversation with you Sri. I know you so long time but this is indeed inspiring to many. India is a big country and we appreciate your thoughts.

Sri: Thank you very much for taking time to chat with me today. Cheers.

There are pretty strong candidates for SQLPASS Board of Elections this year. I know all of them in person and honestly it is going to be extremely difficult to not to vote for anybody. I am indeed in a crunch right now how to pick one over another. Though the choice is difficult, I encourage you to vote for them. I am extremely confident that the new board of directors will all have the same goal – Better SQL Server Community.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Contest – Summary of 5 Day and Additional Information


I am overwhelmed with the response of our contest ran earlier this week. Every day we are giving away USD 198 worth give aways to readers in USA and India. If you have not participated so far, I encourage you to participate today itself. Here are links to our 5 day contest. The winner of the contest will be announced on August 20th.

Here are a few important notes related to the contest.

A few people asked me what should they do as they have forgotten to mention their country in the response. Please resubmit with correct data, we will only consider latest entry from one person.

What if you are not from the USA or India?
Participate in the Bonus Quiz. Leave a comment for each of the questions above with your favorite article and you may be eligible for winning something cool.

What if I am winner of two contests out of 5 contests?
Well, in that case, we will send you one set of Combo Kit and Amazon Gift Card of USD 100 for another contest which you won.

Can I exchange my kit with other stuff?
No, if you do not want kit, give it to someone who needs it.

Btw, I strongly suggest that you participate in the Bonus Quiz. There is something cool for everyone!

Reference: Pinal Dave (http://blog.sqlauthority.com)





SQLAuthority News – SQL Server 2012 Upgrade Technical Guide – A Comprehensive Whitepaper – (454 pages – 9 MB)

Microsoft has just released SQL Server 2012 Upgrade Technical Guide. This guide is very comprehensive and covers the subject of upgrade in-depth. This is indeed a helpful detailed white paper. Even writing a summary of this white paper would take over 100 pages. This further proves that SQL Server 2012 is quite an important release from Microsoft. This white paper discusses how to upgrade from SQL Server 2008/R2 to SQL Server 2012. I love how it starts with the most interesting and basic discussion of upgrade strategies: 1) In-place upgrades, 2) Side by side upgrade, 3) One-server, and 4) Two-server. This whitepaper is not just pure theory but is also an excellent source for some tips and tricks. Here is an example of a good tip from the paper:

“If you want to upgrade just one database from a legacy instance of SQL Server and not upgrade the other databases on the server, use the side-by-side upgrade method instead of the in-place method.”

SQLServer2012big SQLAuthority News   SQL Server 2012 Upgrade Technical Guide   A Comprehensive Whitepaper   (454 pages   9 MB)

upgradenow SQLAuthority News   SQL Server 2012 Upgrade Technical Guide   A Comprehensive Whitepaper   (454 pages   9 MB)

There are so many trivia, tips and tricks that make creating the list seems humanly impossible given a short period of time. My friend Vinod Kumar, an SQL Server expert, wrote a very interesting article on SQL Server 2012 Upgrade before. In that article, Vinod addressed the most interesting and practical questions related to upgrades. He started with the fundamentals of how to start backup before upgrade and ended with fail-safe strategies after the upgrade is over. He covered end-to-end concepts in his blog posts in simple words in extremely precise statements.

A successful upgrade uses a cycle of: planning, document process, testing, refine process, testing, planning upgrade window, execution, verifying of upgrade and opening for business. If you are at Vinod’s blog post, I suggest you go all the way down and collect the gold mine of most important links. I have bookmarked the blog by blogging about it and I suggest that you bookmark it as well with the way you prefer.

Vinod Kumar’s blog post on SQL Server 2012 Upgrade Technical Guide

SQL Server 2012 Upgrade Technical Guide is a detailed resource that’s also available online for free. Each chapter was carefully crafted and explained in detail. Here is a quick list of the chapters included in the whitepaper. Before downloading the guide, beware of its size of 9 MB and 454 pages.

Here’s the list of chapters:

Chapter 1: Upgrade Planning and Deployment
Chapter 2: Management Tools
Chapter 3: Relational Databases
Chapter 4: High Availability
Chapter 5: Database Security
Chapter 6: Full-Text Search
Chapter 7: Service Broker
Chapter 8: SQL Server Express
Chapter 9: SQL Server Data Tools
Chapter 10: Transact-SQL Queries
Chapter 11: Spatial Data
Chapter 12: XML and XQuery
Chapter 13: CLR
Chapter 14: SQL Server Management Objects
Chapter 15: Business Intelligence Tools
Chapter 16: Analysis Services
Chapter 17: Integration Services
Chapter 18: Reporting Services
Chapter 19: Data Mining
Chapter 20: Other Microsoft Applications and Platforms
Appendix 1: Version and Edition Upgrade Paths
Appendix 2: SQL Server 2012: Upgrade Planning Checklist

Download SQL Server 2012 Upgrade Technical Guide [454 pages and 9 MB]

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – expressor Studio 3.4 Rules Editor – ETL Graphical Coding Tool

New in the expressor Studio 3.4 release is the rules editor.  This graphical coding tool replaces the transform editor of earlier versions.  The rules editor works in concert with the newly introduced attribute propagation functionality to minimize the amount of data mapping and coding you need to provide.  The expressor folks are telling me that in a future release we will be able to save and reuse rules, which will make everyone’s  application development tasks even simpler and less prone to errors.

So what’s attribute propagation?  expressor’s starting point observation is that in any transformation most values are either copied from the input record to the output record without any changes or dropped from the output record.  What attribute propagation does is transparently perform these operations without a requirement for coding.  Consequently, within an operator performing a transformation, your only responsibility is to manipulate the values that do require modification.  And this is where the rules editor becomes involved.

Let’s consider an example.  A company wants to give any employee whose tenure with the company started before January 1, 1995 a 5% salary increase.  The application will read a list of employees from a SQL Server database table, determine if the employee’s hire date was before January 1, 1995, and if appropriate calculate a new salary.

Of course expressor offers many ways to develop this application, but I’ll just highlight one approach as it’s the rules editor that I want to discuss.

ruleseditor1 SQL SERVER   expressor Studio 3.4 Rules Editor   ETL Graphical Coding Tool

With expressor, this application can be developed using three operators: a SQL Query operator to select the qualified employees from the SQL Server database table, a Transform operator to carry out the salary calculation, and a Write Table operator to write the modified record back to the database table.  The SQL Query operator executes a SELECT statement that limits the result set to only those employees who qualify for the raise.

SELECT * FROM employees WHERE HIRE_DATE < CONVERT(VARCHAR(23), ‘1995-Jan-01′ , 121)

To continue developing the application, I open the rules editor of the Transform operator.

ruleseditor2 SQL SERVER   expressor Studio 3.4 Rules Editor   ETL Graphical Coding Tool

In the left-hand panel is a listing of the attributes in each incoming record.  The names of these attributes are identical to the table column names, although expressor allows you to easily rename if desired.

The right-hand panel shows the effect of attribute propagation.  That is, each incoming attribute has been transferred to the outgoing record.  The right-facing arrow before each attribute name indicates that the attribute will be automatically initialized and that no coding is required.

To alter the value assigned to the outgoing record’s SALARY attribute, start by using the mouse to drag a connection between the input and output attributes.  An expression rule is displayed.  The default code simply transfers the incoming value to the output.  Also note that the symbol before the output attribute’s name has changed from the right-facing arrow to a diamond, indicating that this attribute is initialized through code.

ruleseditor3 SQL SERVER   expressor Studio 3.4 Rules Editor   ETL Graphical Coding Tool

All I need to do to complete the application is modify the default code, applying the salary increase and, since all salaries are stored as whole dollars, apply a rounding, which I can easily do by clicking the Math button in the Edit tab of the ribbon bar and selecting round from the drop down list of functions.

ruleseditor4 SQL SERVER   expressor Studio 3.4 Rules Editor   ETL Graphical Coding Tool

But what happens when my processing logic is more complex, for example, I only want to give the raise to employees whose commission percentage is smaller than 0.35%.  Then I replace the expression rule with a function rule, where I have full access to the underlying code and I can use the filter helper function to enrich my logic.

ruleseditor5 SQL SERVER   expressor Studio 3.4 Rules Editor   ETL Graphical Coding Tool

In this example, I demonstrated the use of an expression rule and a function rule in the Transform operator.  These rule types are also available in the Join operator.  Within the Transform operator you can also work with a lookup expression rule and a lookup function rule.  These are the rules you use to access the lookup table artifact, another new feature of expressor Studio 3.4.  And in the Aggregate operator you can use an aggregate expression rule, which allows you to select an aggregating function from a drop down control, or an aggregate function rule that allows you to completely manage the processing logic.

While the example developed in this article was quite simple, I think you will agree that attribute propagation and the new rules editor provide a high level of support when your applications require coding.  I continue to be intrigued by the many innovations expressor is putting into their desktop ETL tool.  You can download their tool by following this link.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Community Tech Days – TechEd on The Road – Ahmedabad – June 11, 2011

TechEd on Road is back! In Ahmedabad June 11, 2011!

techedonroad1 SQLAuthority News   Community Tech Days   TechEd on The Road   Ahmedabad   June 11, 2011Inviting all Professional Developers, Project Managers, Architects, IT Managers, IT Administrators and Implementers of Ahmedabad to be a part of Tech•Ed on the Road, on 11th June, 2011. We have put together the best sessions from Tech•Ed India 2011 for you in your city. Focal point will be technologies like Database and BI, Windows 7, ASP.NET.


Venue: Ahmedabad Management Association (AMA)
Dr. Vikram Sarabhai Marg,
University Area,
Ahmedabad, Gujarat 380 015

Time: 9:30AM – 5:30PM

The biggest attraction of the event is session HTML5 – Future of the Web by Harish Vaidyanathan. He is Evangelist Lead in Microsoft and hands on developer himself. I strongly urge all of you to attend his session to understand direction of the web and Microsoft’s take on the subject.

I (Pinal Dave) will be presenting on the session of SQL Server Performance Tuning and Jacob Sebastian will be presenting on T-SQL Worst Practices. Do not miss this opportunity. Those who have attended in the past know that from last two years the venue is jam packed in first few minutes. Do come in early to get better seat and reserve your spot.

We will have QUIZ during the event and we will have various gifts – Watches, USB Drives, T-Shirts and many more interesting gifts.

Refer the agenda today and register right away. There will be no video recording so come and visit the event in person.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – DBA or DBD? – Database Administrator or Database Developer

Earlier this month, I had poll on this blog where I asked question – Are you a Database Administrator or Database Developer? The word DBA (Database Administrator) is very common but DBD (Database Developer) is not common at all. This made me think – what is the ratio of the same.

Here the result of the poll:

Database Administrator 36.6% (254 votes)
Database Developer 63.4% (440 votes)
Total Votes: 694

This is open poll, if you want you can still participate here.

Vote your Voice – DBD or DBA?

I think it is the time when DBD word for Database Developer gets place in our dictionary.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server

I was recently called to attend the Query Tuning Project. I had a very interesting experience in this event. I would like to share to you what actually happened.

Note: If you are just going to say that shrinking database is bad, I agree with you and that is the main point of this blog post. Please read the whole blog post first.

The problem definition of the consultation was to improve the performance of the database server. I usually fly to the client’s location a day before, so the next day I am all fresh upon reaching the client’s office after a relaxing night’s sleep. Due to a fair availability of the flight, I reached the location earlier that day, at around 2 PM. I headed to the client’s location to familiarize myself with the place. I met the resident DBA and we talked for a few minutes. We looked at the index fragmentation during our conversation. It was the heaviest fragmentation that I have ever seen in my life. I asked the DBA if he could rebuild or reorganize indexes. He replied that he has never done it in the past one year. One year is really a long time; I could not believe that they have no maintenance task scheduled. I even wondered how they managed without index maintenance for the entire year. Anyway, I quickly handed my index script over him.

We waited until the end of their business hours, at about 5PM. After this, there are hardly any activities in the database. We executed the task on their production server after checking a few settings. The script ran for about 1.5 hours until it is finished. Afterwards, I checked the fragmentation of the indexes; it was very descent. In fact, some of the large tables were showing the fragmentation near to zero. I was pleased with this while the DBA confirmed that few reports even ran faster than before. Satisfied with the work done on Day 0, I left the location and went to the hotel where I stayed.

shrinkfun SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server

When I arrived to the location the next day, I had a meeting with the company director. He mentioned that he was updated by Dr. DBA regarding my de-fragmentation of indexes, and he wanted to see the status of the indexes. I ran my script once again to check the fragmentation of index. To my surprise, it was just like yesterday. I got confused and speechless. I checked the server instances and a few other things, but still nothing. The Sr. DBA also had no explanation at all. He started to mumble something I did not understand. Well, to make the long story short, I had a long face and did not feel comfortable. I was pretty sure that I had run the script of de-fragmentation and it worked fine.

Before I continued, I tried to check all the existing stored procedures and jobs. Finally, just like magic, I found the following code.

Before running the following code. Read the whole blog post.


This code shrinks the whole database on a single SQL Server Instance. I instantly figured out where this code was used, and then I removed it. After I got rid of the code, I rebuilt and reorganized indexes. For the next 5 days, I faced no problem at all. Well, this is another reason not to shrink the database. Shrinking the database causes heavy fragmentation of the tables and reduces the performance. After shrinking, it seems that rebuilding indexes is necessary. But again, there should not be any real need to shrink the database. Do NOT shrink your database.

Reference: Pinal Dave (http://blog.sqlauthority.com), Image source unknown.

SQLAuthority News – Author’s Birthday – 5 SQL Server Best Practices

Today is my birthday. For those who are curious, I have now completed 31 years in my life. I have learned lots of new things during the course of the past few years. Every year I feel that I am getting better as a human being. Since today is my birthday, I have decided NOT to work and NOT to blog. I will do something really fun!

I am going to clean up my personal servers as well and finish a few tests, as well as complete my benchmark projects. My personal data is also stored in one gigantic database. Because it is my personal server, no one pays me and thus I hardly maintain it.

happybirthday SQLAuthority News   Authors Birthday   5 SQL Server Best Practices

Today I am going to do the following maintenance tasks on it:

  1. Backup Master. I am going to have a backup of the database using script; however, the backup script has not been updated for a long time now. Last time, I noticed that I was not getting the master database’s backup. And no matter what one suggests, I still think it is very important to have a backup of the master database. I am going to alter the script to have a correct backup of the master database.
  2. Online Indexing. I have upgraded my server from SQL Server 2005 Standard to SQL Server 2008 Enterprise. On the other hand, I never changed the index rebuilding script. One of the tables is very huge and when indexes are rebuilt in it, it often gets broken. I plan to change the index rebuild script to include the option of (ONLINE = ON). This will be good to avoid the time outs.
  3. Restarting the Server. I have the server connected to UPS and even though we have lots of very short power outages, my server is not affected, fortunately. I almost forgot when I really restarted it. Although, it is not necessary to restart server at all, this procedure is intended for my windows box, which has many updates installed but I have not rebooted it in order for these updates to take effect. This may not be the best technique, but it works enough for me because my data is not that critical. If you are maintaining a high-availability solution, I suggest that you have fail over solution implemented before restarting the server.
  4. Wait Stats. I recently learned about Wait stats. I am eager to know what Wait Statistics really are, and what they can do for my server. Due to the fact that I have not upgraded the hardware for many years now (although I have upgraded the OS, SQL Server version and huge data growth), I could find out which of my hardware needs an upgrade.
  5. Fresh Air. I will make sure that after all of the above tasks are completed I am going to go out with my family and breathe some fresh air.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – 2 Sessions at TechInsight 2010 – June 29 – July 1, 2010

Earlier this month, I got the opportunity to visit Malaysia for community sessions on June 29 – July 1, 2010 at Kuala Lumpur, Malaysia, which I would consider as valuable experience. I presented two different sessions at the event. The event was extremely popular in local community, and I had great time meeting people in Malaysia. I must say that the best thing about Kuala Lumpur is the people and their response.

Malaysia Twin Towers
Malaysia Twin Towers

Techinsights is a major technology conference to network with like-minded peers and also up-skill your knowledge on latest technologies. An event that offers opportunity to dabble in hardcore technologies with in-depth and hands-on demonstration by Microsoft MVPs and industry experts local and abroad. This three-day event will challenge what you think you already know. You’ll return to the office with cutting-edge insights and expertise that will make life easier for you (and everyone else) at work. This round, we have a special highlight on new technologies such as SharePoint 2010, Visual Studio 2010, SQL Server 2008 R2, Silverlight 4, Windows 7, Windows Server 2008 R2 and many more. TechInsight is an event created by techies for techies. There is no marketing involved. It is indeed an experience to rediscover the uber-geek within you. Sign up today to secure your seat.

Techinsight - 2 Sessions
Techinsight – 2 Sessions

I presented two sessions there. Both of my sessions were in the TOP 5 sessions of Development track. Additionally, my session on Join got the highest ranking ever in Dev Track.

1) My Join, Your Join and Our Joins – The Story of Joins

Joins are very mysterious; there are many myths and confusions. This session will address all of them and also tell the story of how they act when it is about performance. Does the order of table in Join matter? Does the right or left join any different to each other? Does the Join increase IO? When is an outer join not an outer join and inner join? All these questions are answered and many more stories of Joins are included. Learn the simple tricks to get the maximum out of this tool.

Session Evaluations

Overall session rating 7.5
How valuable was the content presented 7.467741935
How effectively did the presenter communicate the content 7.596774194

KL Malaysia (4) SQLAuthority News   2 Sessions at TechInsight 2010   June 29   July 1, 2010

2) Spatial Database – The Indexing Story

The world was believed to be flat but no more. Now SQL Server supports the spatial datatypes and many more functions. This session addresses the most vital part of Spatial datatypes and talks about how to improve the performance for the application, which is already blazing fast. We will look at how indexes are behaving with different spatial datatypes and how they can help to improve the performance and also learn the pitfalls to avoid them affecting performance.

Session Evaluations

Overall session rating 7.237288136
How valuable was the content presented 7.322033898
How effectively did the presenter communicate the content 7.457627119

KL Malaysia (5) SQLAuthority News   2 Sessions at TechInsight 2010   June 29   July 1, 2010

I must express my special thanks to all the organizers of the event – Ervin, Walter, Raymond, and Patrick (in no particular order). They did an excellent job, and all the attendees of the event had great time as well. The food was awesome, and the response was excellent. After one month, when I am writing this review, I am still thinking of the wonderful experience I had from this event. This makes me want to not miss this event any year.

Techinsight - Event Organizers
Techinsight – Event Organizers

This one event is truly TechEd quality event in Malaysia. Kudos to the organizers and Microsoft.

Techinsight - Kuala Lumpur, Malaysia
Techinsight – Kuala Lumpur, Malaysia

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Enumerations in Relational Database – Best Practice

Marko Parkkola

Marko Parkkola

This article has been submitted by Marko Parkkola, Data systems designer at Saarionen Oy, Finland. Marko is excellent developer and always thinking at next level. You can read his earlier comment which created very interesting discussion here: SQL SERVER- IF EXISTS(Select null from table) vs IF EXISTS(Select 1 from table). I must express my special thanks to Marko for sending this best practice for Enumerations in Relational Database. He has really wrote excellent piece here and welcome comments here.

Enumerations in Relational Database

This is a subject which is very basic thing in relational databases but often not very well understood and sometimes badly implemented. There are of course many ways to do this but I concentrate only two cases, one which is “the right way” and one which is definitely wrong way.

The concept

Let’s say we have table Person in our database. Person has properties/fields like Firstname, Lastname, Birthday and so on. Then there’s a field that tells person’s marital status and let’s name it the same way; MaritalStatus.

Now MaritalStatus is an enumeration. In C# I would definitely make it an enumeration with values likes Single, InRelationship, Married, Divorced. Now here comes the problem, SQL doesn’t have enumerations.

The wrong way

This is, in my opinion, absolutely the wrong way to do this. It has one upside though; you’ll see the enumeration’s description instantly when you do simple SELECT query and you don’t have to deal with mysterious values. There’s plenty of downsides too and one would be database fragmentation.

Consider this (I’ve left all indexes and constraints out of the query on purpose).

CREATE TABLE [dbo].[Person]
[Firstname] NVARCHAR(100),
[Lastname] NVARCHAR(100),
[Birthday] datetime,
[MaritalStatus] NVARCHAR(10)

You have nvarchar(20) field in the table that tells the marital status. Obvious problem with this is that what if you create a new value which doesn’t fit into 20 characters? You’ll have to come and alter the table. There are other problems also but I’ll leave those for the reader to think about.

The correct way

Here’s how I’ve done this in many projects. This model still has one problem but it can be alleviated in the application layer or with CHECK constraints if you like.

First I will create a namespace table which tells the name of the enumeration. I will add one row to it too. I’ll write all the indexes and constraints here too.

CREATE TABLE [CodeNamespace]
[Id] INT IDENTITY(1, 1),

INSERT INTO [CodeNamespace] SELECT 'MaritalStatus'

Then I create a table that holds the actual values and which reference to namespace table in order to group the values under different namespaces. I’ll add couple of rows here too.

[CodeNamespaceId] INT NOT NULL,
[Description] NVARCHAR(100) NOT NULL,
[OrderBy] INT,
CONSTRAINT [PK_CodeValue] PRIMARY KEY CLUSTERED ([CodeNamespaceId], [Value]),
CONSTRAINT [FK_CodeValue_CodeNamespace] FOREIGN KEY ([CodeNamespaceId]) REFERENCES [CodeNamespace] ([Id])
-- 1 is the 'MaritalStatus' namespace
INSERT INTO [CodeValue] SELECT 1, 1, 'Single', 1
INSERT INTO [CodeValue] SELECT 1, 2, 'In relationship', 2
INSERT INTO [CodeValue] SELECT 1, 3, 'Married', 3
INSERT INTO [CodeValue] SELECT 1, 4, 'Divorced', 4

Now there’s four columns in CodeValue table. CodeNamespaceId tells under which namespace values belongs to. Value tells the enumeration value which is used in Person table (I’ll show how this is done below). Description tells what the value means. You can use this, for example, column in UI’s combo box. OrderBy tells if the values needs to be ordered in some way when displayed in the UI.

And here’s the Person table again now with correct columns. I’ll add one row here to show how enumerations are to be used.

CREATE TABLE [dbo].[Person]
[Firstname] NVARCHAR(100),
[Lastname] NVARCHAR(100),
[Birthday] datetime,
[MaritalStatus] INT
INSERT INTO [Person] SELECT 'Marko', 'Parkkola', '1977-03-04', 3

Now I said earlier that there is one problem with this. MaritalStatus column doesn’t have any database enforced relationship to the CodeValue table so you can enter any value you like into this field. I’ve solved this problem in the application layer by selecting all the values from the CodeValue table and put them into a combobox / dropdownlist (with Value field as value and Description as text) so the end user can’t enter any illegal values; and of course I’ll check the entered value in data access layer also.

I said in the “The wrong way” section that there is one benefit to it. In fact, you can have the same benefit here by using a simple view, which I schema bound so you can even index it if you like.

CREATE VIEW [dbo].[Person_v]
p.[Firstname], p.[Lastname], p.[BirthDay], c.[Description] MaritalStatus
FROM [dbo].[Person] p
JOIN [dbo].[CodeValue] c ON p.[MaritalStatus] = c.[Value]
JOIN [dbo].[CodeNamespace] n ON n.[Id] = c.[CodeNamespaceId] AND n.[Name] = 'MaritalStatus'
-- Select from View
FROM [dbo].[Person_v]

enum1 SQL SERVER   Enumerations in Relational Database   Best Practice

This is excellent write up byMarko Parkkola. Do you have this kind of design setup at your organization? Let us know your opinion.

Reference: Pinal Dave (http://blog.SQLAuthority.com)