Information System

In this blog entry, I will talk about what are Information Systems? what are the different types of Information Systems and what benefits do they provide? More to follow shortly. Stay tuned for more...

SQL Server EXCEPT Clause

A few days back I had performance issues with a query in SQL Server 2005. The query was a nested query similar to the following (details omitted for brevity):

SELECT
   EmployeeID, DateAttended
FROM
   CourseDetails
WHERE
   EmployeeID NOT IN
   (
      SELECT 
         EmployeeID 
      FROM
         CourseDetails
      WHERE
         isActive = 0
   )

The above query would return all data rows where [Courses] are 'Active'. Due to the slow query, the web application was timing out. After doing some research, I finally came across the wonder EXCEPT clause in SQL Server 2005.

The EXCEPT clause returns all rows from the first query which are not present in the second query. It's more like a LEFT JOIN where rows from the left tables are returned. It is important to note that both the first and second query must have same data columns with similar data types.

Using the EXCEPT clause, we can now rewrite the above query as following:

SELECT
   EmployeeID, DateAttended
FROM
   CourseDetails
EXCEPT
   SELECT 
      EmployeeID 
   FROM
      CourseDetails
   WHERE
      isActive = 0

To my joy, the performance of the query was lightening fast. It really worked like a charm. So my advice would be to use the EXCEPT clause wherever possible and avoid nested queries.

I hope you found this blog use. Stay tuned for more.

Using Surrogate Key in Databases

I prefer using surrogate key (a non business key used as an identifier e.g. Instead of using a StudentID as an identifier, we may use a separate RowID column to identify each student record) whenever I can. Different people have different opinions about using surrogate keys in databases. Some will always use a surrogate key while others won’t. It all comes down to the personal preference of a database developer.

However, based on my experience working with surrogate keys, I am off the opinion that surrogate keys are both good and bad. Don’t get me wrong when I call them bad. They don’t do any harm rather add to the working load on behalf of the developer. Following are some of the pros and cons of using a surrogate key:


PROS

1. Primary keys (or natural keys) are hard to change. In programming, there are times when we may need to change the primary key e.g. we can change a Student-ID from type char (6) to char (8) to accommodate more students. If Student-ID is defined as a primary key, it must have links in several tables. This means, the change must be reflected in all the associated tables (a nightmare for a developer). However, if a separate surrogate key is defined, Student-ID type can be changed without affecting multiple tables.

2. A surrogate key can assist developers in programming e.g. suppose a table has a surrogate key defined while the unique key is defined by the combination of three columns. If the data in this table is displayed in a gridview in asp.net, the developer can easily write code to Update and Delete records based on the primary key. In this case, he doesn’t have to worry about all three keys which define the unique key.

3. There is no locking contention since a surrogate key is generated by the database and cached making them highly scalable.


CONS

1. Data integrity becomes the responsibility of the developer. Whenever a record is inserted or updated, the developer must check if a similar natural key already exists to avoid duplication. If the natural key was defined as the primary key, the database would take care of data integrity.

2. Excessive joins are needed since joins are dependent on keys with business value and not a surrogate key e.g. to get details of a student from different tables, a developer would prefer to use his Registration-ID and not a surrogate key.

3. An separate index must be defined on the natural key.

4. From a programming point of view, surrogate keys cannot be used as a search key.

I hope you find this blog as useful. Stay tuned for more...

I start writing again - finally!

I have been away from blogging for quite some time. This can be attributed to my busy schedule (and some what laziness :-). However, I now plan to write on regular basis. I have some interesting topics in mind to start with. I also plan to finish my incomplete 'LINQ Explained' series. Honestly, LINQ is one topic which requires thorough and deep understanding of concepts before talking about it. In coming days, I also plan to blog about SharePoint Server, Enterprise Concepts and ASP.NET. So stay tuned for more...