SQL SERVER - Little things can save you a big headache

Recently I came across a situation where someone was running a query, basically copying data from one table to another.

The query was simple enough. Basically it was using a fast forward cursor, copying one record from one table into another table. Using SCOPE_IDENTITY, the query retrieved the identity value of the newly inserted record and then inserted another record in a child table, using newly generated identity key. There were several hundred millions of records in the table from which the data was being moved to a new table. Whether this could have been done another way is not the topic for today.

In this particular instance, the query was running in Query Analyzer and it will run for a while, but then will throw an out of memory exception. Since the query didn't track what records were inserted already in the new table before out of memory exception was thrown, the developer truncated the new table and started all over again, only to hit the same issue again.

The reason query was throwing out of memory exception was because the query didn't have SET NOCOUNT ON clause at the beginning. As a result, the query was outputting messages for each statement in this query, not only resulting in memory consumption but also degrading the performance and increasing the network traffic. SET NOCOUNT ON basically prevents the sending of DONE_IN_PROC messages to the client.

The query need not run in a loop like above or in query analyzer to make use of SET NOCOUNT ON. If you don't need messages being returned from the SQL Server or aren't capturing them, be sure to add this clause to your stored procedures.

Thank you.

Database Normalization - Second Normal Form

In previous post we discussed the concept of Normalization and First Normal Form. Today, we will review Second Normal Form. You may want to refresh previous post before continuing on.

Second Normal Form (2NF)

2NF takes this concept  a bit further. This rule states that any data that is in multiple rows of the same table should be moved to a new table and the two tables should be joined via a foreign key. Basically, the idea is to reduce data redundancy by extracting redundant data and moving it elsewhere. Let's imagine you have a customers table that has the following columns

Customers

• CustomerID
• FirstName
• LastName
• Address
• City
• State
• PostalCode
• Country

Sure, you will have more than one customer from the same city, even more from the same state and definitely more from the same country. You may possibly have many customers from the same postal (zip) code. As you enter customers in this table, you are duplicating all this data i.e. City, State, PostalCode, Country etc. This table is not in 2NF. To make it 2NF compliant, create a new table and store City, State, PostalCode, Country. Let's call this table Addresses. This table has the following columns

Addresses

• City
• State
• PostalCode
• Country

What columns do you think could be redundant here? Sure PostalCode won't be, but what about City? A city could have multiple postal codes? Definitely a State can have multiple cities and Country will have multiple states. A good schema for all the tables may be the following...

Customers

• CustomerID
• StreetAddress
• CityID

CityAddress

• CityID
• City
• PostalCode
• StateID
• CountryID

States

• StateID
• State

Countries

• CountryID
• Country

Notice, one table resulted in 4 different tables. It is definitely more complex but more flexible. You can even pre-populate States and Countries in advance.

This schema also satisfies second rule of 2NF i.e. the related tables should be related by foreign key. CityID is a foreign key in Customers, StateID, CountryID are foreign keys in CityAddress table.

We will discuss 3NF in future post.

Thank you.

Database Normalization - First Normal Form

So far we focused on database administration and management. Moving forward, we will focus on database design, development and database performance in terms of using proper data query etc.

Today, we will review database normalization. Everyone familiar with RDBMS is probably also familiar with database normalization, but it is good to review it before we embark on database design.

Normalization:
Normalization is a way to arrange your database schema in such a way as to minimize data redundancy and duplication. For example, if you have two tables in your database - Customers and Orders. If your database is not normalized, you may store Customer Name in both Customers and Orders tables to relate the order to a customer. Apart from storing the same information twice, you also have a real problem where data could get out of sync. If your customer gets married and changes her last name, you must update her name in both tables. One of the key strength of relational databases over flat flies such as text file is their ability to relate the data across tables without having to duplicate it, hence the term "Relational".

Types of Normalization
The inventor of relational model, Edgar F. Codd defined the first, second and third normal form or 1NF, 2NF and 3NF respectively. Later on, Codd and Boyce defined the Boyce-Codd Normal Form (BCNF) also called 4NF. 5th and 6th normal forms (5NF, 6NF) were defined later on. Generally, most database adhere to 3rd Normal Form and in most cases a 3NF will also adhere to 4NF and 5NF (but not always).

When you are designing a database, care should be taken to design your database as normalized as possible. However, normalization comes at a cost, and you may have to selectively de-normalize a few tables. In some schemes such as data warehousing, you keep your design denormalized for performance reasons and primarily because you are typically not updating the data in a data warehouse.

A well normalized database not only reduces the data anomalies and redundancies, it also makes future modifications to the database easier. For example, let's assume you have a table that stores 4 phone numbers and all numbers are stored in columns of a single row, i.e. one column for each phone number. What would happen if you have to now record 5th phone number? You have to modify your table and add a 5th column to store the 5th number and since not all users will have 5 numbers, you will have a null values in several columns for most of your users.

First Normal Form (1NF)
1NF has two rules - First rule says that we do not duplicate data in the same row of a table. Recall the above example, i.e. you store 4 phone numbers for every user in 4 columns of the same row. But not all users will have all 4 numbers resulting in lot of null values (hence duplicate data). Adding a 5th phone number will require table schema modification. So how do we solve this? Well, you can create a table called PhoneNumbers with two columns

• Name
• PhoneNumber

Now you can have as many phone numbers per user. But, this is not 1NF yet. The second rule states that each row in a related table should have a unique (primary key). You could say that we can make PhoneNumber a primary key. But what if the same phone number is shared by two users? How about making Name and Phone Number (composite key) as a primary key? Well, close but what if two users who share the same phone number happen to have the same name? To make this table 1NF, we have to have a truely unique key. How about adding an identity field and making it a primary key? That will ensure every record has a unique key and will make our table 1NF.

In next post, we will discuss Second Normal Form (2NF).

Thank you.

SQL Server Locking Hints

                                                                SQL Server Locks - Part I, Part II, Part III, Part IV

We have been discussing locking mechanism in SQL Server since last three posts. In previous posts we covered locks, deadlocks and isolation levels. Today we will discuss ways to control how SQL Server locks the data by passing locking hints.

Generally leaving SQL Server engine to handle locks works as expected, but there are circumstances where you may want to use locking hints to lock or not lock certain transaction.

Locking Hints

• HOLDLOCK- When you pass this hint to your select queries, shared locks are held until the entire transaction completes instead of releasing the lock as soon as the read is complete and the required lock (table, row or page) is no longer needed. HOLDLOCK is same as SERIALIZABLE isolation level.
• NOLOCK - When you pass this hint, SQL doesn't issue shared locks for reading data and doesn't honor exclusive locks. In other words, if there is an exclusive lock on row, page or table, query with NOLOCK hint will still execute instead of waiting for exclusive lock to be released. With this hint, dirty reads are possible. This hint only applies to SELECT statements.
• PAGLOCK - Forces SQL Server to issue page lock instead of a default table lock that may be taken.
• READCOMMITTED -Same as READ COMMITTED isolation level, i.e. only read committed data.
• READPAST - When reading data, if a row is exclusively locked, read past that row, i.e. any locked rows are skipped. Beware, your results may not have all the data if you use this hint. Also, you can only use this hint if isolation level is set at READ COMMITTED. It also only applies to SELECT.
• READUNCOMMITTED - Same as NOLOCK hint.
• REPEATABLEREAD - Same as REPEATABLE READ isolation level, i.e. once a shared lock is acquired, it isn't released until the entire transaction completes. 
• ROWLOCK - Forces SQL to issue row level locks instead of page and table level locks.
• SERIALIZABLE - Same as SERIALIZABLE isolation level and same as HOLDLOCK. All transactions are blocked until the current transaction completes.
• TABLOCK - Forces SQL to issue a table lock instead of row or page level lock. This lock is only held until the executing statement completes. However if you are running under SERIALIZABLE isolation level or also pass HOLDLOCK, the lock will be held until the end of the transaction.
• TABLOCKX - Issues an exclusive lock on the table, preventing others from reading or updating the table. 
• UPDLOCK - This is a special lock used when you are updating data. When you are updating data, data must be first read and then updated. Ordinarily when data is read, a shared lock is issued which will then be converted to exclusive lock to update the data. But what if a transaction acquires a shared lock and another transaction acquires an exclusive lock on the same data at the same time? The data just read could get out of sync. To prevent this, SQL Server issues an update lock instead of shared lock. This allows other transactions to read the data while this transaction with UPDLOCK is reading the data but no other transaction can obtain an exclusive lock on this data. The UPDLOCK is then converted to exclusive lock to update the data. This ensures data integrity since you can be sure that the data hasn't been updated since this transaction read it.
• XLOCK - A exclusive lock which will be held until the end of the transaction. You can specify it with PAGLOCK or TABLOCK, indicating whether to exclusively lock page or a table.

Let's see a lock in action...

SELECT FirstName, LastName FROM CUSTOMERS WITH (NOLOCK)

SELECT FirstName,LastName FROM CUSTOMERS WITH (ROWLOCK)

Hope this helps you understand how you can use various hints available to you. This was a long series but hopefully it will be a good refresher to understand how locking mechanism works in SQL Server.

Thank you.

SQL Server Deadlocks

                                                                   SQL Server Locks - Part I, Part II, Part III, Part IV

Deadlocks
In previous post we discussed locking mechanism in SQL Server and what are different types of locks and how you can control default locking mechanism by using various hints with your queries.

Today, we will discuss deadlocks. Locking is a necessity for any DBMS to work in a multi-user environment and to maintain data consistency. Deadlocks occur when two transactions are waiting on each other to release the lock. Eventually SQL Server chooses one of the transaction as victim and kills it.

Let's explain this using an example -

Suppose you have a Customers table and you run the following queries in two separate transactions.

Transaction A
--statement 1
SELECT * FROM Customers WHERE Customer ID = 1
--statement 2
UPDATE Customers SET EmailAddress='123@mail.com' WHERE CustomerID = 2

Transaction B
--statement 1
SELECT * FROM Customers WHERE Customer ID = 2
--statement 2
UPDATE Customers SET EmailAddress='123@mail.com' WHERE CustomerID = 1

Let's assume one user calls a routine that executes transaction A and another use calls a routine that executes transaction B concurrently.

Transaction A will issue a shared lock while executing statement 1 on row 1 and transaction 2 will issue a shared lock while executing statement 1 on row 2.

Now  transaction  A will request an exclusive lock on row 2, while transaction B will request an exclusive lock on row 1.

Transaction A cannot complete because it is waiting on transaction B while transaction B cannot complete because it waiting on transaction A.

This condition is also called cyclic dependency. SQL Server engine deadlock monitor periodically checks for cyclic dependency and when it detects a dependency, it chooses one of the transaction as a victim and terminates it. The transaction chosen as a deadlock victim will terminate but the other transaction will complete successfully.

Remember, deadlocking is different than blocking. Blocking occurs when a transaction requests a lock on a resource which is exclusively locked by another transaction, in that case the requesting transaction will wait for the lock to be released.

Minimizing Deadlocks
Deadlocks are a fact of life, but you can minimize them by following some basic steps...

• Objects should be accessed in the same order
• All concurrent transactions should access the objects in the same order. For example, If there are two tables - Orders and Order Details, all concurrent transactions should request locks in the same order - For example first on Orders table and then on Order Details table. If all lock requests flow in the same direction, the subsequent requests will be blocked until a lock is released from the previous request, but deadlock should not occur.
• Avoid prompting for User input when a transaction is running
• A transaction should be completed as fast as possible so that the locks are acquired and released as quickly as possible. Requiring user input delays the transaction, hence resources will be locked for longer period of time.
• Keep Transactions Short and in one batch
• Keeping transactions short and in one batch will minimize resources, resulting in faster transaction completion.
• Use a lower Isolation Level
• We will discuss isolation levels in more detail in subsequent post but lower the isolation level, less locking. For example if you implement read committed isolation level, it allows a transaction to read data previously read by another transaction without waiting for that transaction to complete.
• Set READ_COMMITTED_SNAPSHOT to ON
• When this database option is ON, a transaction that is running under read committed isolation level uses row versioning instead of shared lock during read operations. Row versioning does not acquire shared lock for read operations. You must also set ALLOW_SNAPSHOT_ISOLATION to ON in addition to read committed snapshot for it to work.
• Use bound connections
• If your application opens two or more connections, you can bound them together such that one is a primary connection while other is a secondary connection. Under this scenario, any locks that are acquired by the primary connection are treated as they are also acquired by the secondary connection and vice versa.

This concludes our topic for today. There are some other aspects of locking such as providing locking hints with your queries or setting up different isolation levels. We will discuss them in subsequent posts.

Thank you.

SQL Server Transactions

Transactions in SQL Server allows you to batch multiple inserts/updates/deletes in one single process and an error in one sub-step will invalidate or roll back the entire transaction. This ensures the data integrity and atomicity.

Let's assume for example, you have a set of three tables in a SQL database

• A Employee Profile Table
• An Earnings table recording the salary, taxes paid etc.
• A Tax rate table that records an employee's tax rate.

If the tax rate changes, you would want to update the TaxRate table first, then update the Earnings table to reflect the taxes paid (based on new tax rate). For example, if the earnings were $100,000 and the tax rate was 20%, the taxes paid in the Earnings table would be $20,000. Now, if the tax rate jumps to 25%, TaxRate table would be updated to reflect tax rate of 25% and the Earnings table would be reflected to show the updated tax of $25,000.

Now lets assume you are performing these operations as individual updates without wrapping them in one transaction.

      --1st you update tax rate table

       UPDATE TaxRate SET rate=0.25 WHERE EmployeeID=1

       GO

      -- now you update taxes paid 

      UPDATE Earnings SET TaxesPaid = 

      (SELECT Salary * 0.25 FROM Earnings WHERE EmployeeID=1)

      WHERE EmployeeID=1

      GO

Now, lets imagine first update executed successfully but a deadlock or some other error occurred before second update could be performed (more on deadlocks in future posts), resulting in out of sync data. TaxRate table will show the tax rate of 25% but Earnings table will show only $20,000 taxes paid.

You want to ensure that either both tables are updated together or none at all. You can wrap both updates in one transaction, and if an error occurred, entire transaction will rollback, ensuring the data integrity.

Here is how you would wrap the above updates in a transaction.

DECLARE @errorcode INT

BEGIN TRAN

    --1st you update tax rate table

       UPDATE TaxRate SET rate=0.25 WHERE EmployeeID=1

       GO

       -- check if an error occurred

       If @@Error <> 0 GO TO ERROR  -- @@Error would trap any error that may occur

      -- now you update taxes paid 

      UPDATE Earnings SET TaxesPaid =

          (SELECT Salary * 0.25 FROM Earnings WHERE EmployeeID=1)

          WHERE EmployeeID=1

      GO

     -- check again if an error occurred

       If @@Error <> 0 GO TO ERROR

COMMIT TRAN

ERROR:    --- GO TO ERROR statement will jump here anything an error occurred.

PRINT ('An error occurred')

ROLLBACK TRAN

This allows you to declare transaction at the SQL Script level - in a stored procedure for example. You can achieve the same effect by using ADO.NET Transaction or TransactionScope object in code when executing two or more separate stored procedures or in-line SQL.

Using Transaction

public void updateTaxInformation (string connectstring)

{

   using (SqlConnection conn =  new SqlConnection(connectstring))

{

      conn.Open();

      SqlCommand cmd = connection.CreateCommand();    // create sql command

      SqlTransaction trans;   // create a new sql transcation

      // start the transaction

      trans = conn.BeginTransaction("TransName");

     //assign connection and transaction to the command

     cmd.Connection = conn;

     cmd.Transaction = trans;

     try

     {

          cmd.commandText = "UPDATE TaxRate SET rate=0.25 WHERE EmployeeID=1;

          cmd.ExecuteNonQuery();

          //second update

          cmd.commandText = "UPDATE Earnings SET TaxesPaid =

                                             (SELECT Salary * 0.25 FROM Earnings WHERE EmployeeID=1)

                                             WHERE EmployeeID=1;

          cmd.ExecuteNonQuery();

          // commit transaction

         trans.commit();

      }

    catch (Exception ex)

    {

        Console.WriteLine("Exception Occurred: " + ex.Message);

        trans.Rollback();

     }

  }

}

Using TransactionScope

Beginning in .NET 2.0, Microsoft introduced TransactionScope which basically does the same thing as above, except a few subtle differences. TransactionScope simplifies some of the code and takes care of enrolling SQL commands into a transaction autmatically. The above code can be simplified using TransactionScope 

Using (TransactionScope transscope = New TransactionScope())

  {

     using (SqlConnection conn =  new SqlConnection(connectionstring))

       {

          conn.Open();

          SqlCommand cmd = connection.CreateCommand();    // create sql command

          cmd.commandText = "UPDATE TaxRate SET rate=0.25 WHERE EmployeeID=1;

          cmd.ExecuteNonQuery();

          //second update

          cmd.commandText = "UPDATE Earnings SET TaxesPaid =

                                             (SELECT Salary * 0.25 FROM Earnings WHERE EmployeeID=1)

                                             WHERE EmployeeID=1;

          cmd.ExecuteNonQuery();

          transscope.Complete();

         }

    }

       

That's all there is to it. While a transaction has its advantages such as maintaining data integrity, you have to be careful when creating a transaction. Large transactions could result in dreaded deadlocks. We will discuss deadlocks in future posts.

Thank you and your comments are welcome.