Primary and Unique keys

Today one of my colleagues expressed difficulty in ensuring uniqueness for certain columns in a table.

Characteristics of these columns are

1. There are not necessary (allow-null) columns for inserting a row in a table in which they are defined
2. But when inserted they must form a unique combination

What could have been done so easily with Unique Key became so difficult because of lack of referential integrity.

These columns could have been defined in a different table and referenced with a foreign  key relationship to primary table.

Such a simple thing could not be corrected as we are so tightly coupled with its structure. Awful! a classic example of making life miserable by knowingly living with self made mistakes.

Built-in functions of Transact-SQL

Many a times, we end up spending lot of time doing something that made available to us just due to lack of awareness.

Today I spent 30 minutes to get date as string from datetime column in database. Doing this from code is quite a trivial task.

But here I want to bind a source to UI control. DATENAME function solved my problem just like that. It is built-in function of SQL.

Here are some more references for built-in functions from MSDN

Functions (Transact-SQL)

• Aggregate Functions (Transact-SQL)
• Configuration Functions (Transact-SQL)
• Cryptographic Functions (Transact-SQL)
• Cursor Functions (Transact-SQL)
• Date and Time Data Types and Functions (Transact-SQL)
• Mathematical Functions (Transact-SQL)
• Metadata Functions (Transact-SQL)
• Ranking Functions (Transact-SQL)
• Replication Functions (Transact-SQL)
• Rowset Functions (Transact-SQL)
• Security Functions (Transact-SQL)
• String Functions (Transact-SQL)
• System Functions (Transact-SQL)
• System Statistical Functions (Transact-SQL)
• Text and Image Functions (Transact-SQL)

Using Set operations (SQL) to detect differences between two tables

Identifying differences between two tables can become quite a daunting task.

Using SQL set operations like {UNION,INTERSECT,EXCEPT} can greatly simply this is process.

Any attempt to use compare operations on .Net runtime turns out to be very costly affair for these operations.

 

I tried creating a set operations. Here my intention is to pass two tables which share same schema, then pass on ID field which

is anchor. This results a single table specifying Addedd/Changed/Deleted/UnChanged records. That makes whole set.

SELECT t1.*, 'Added'FROMTable1 t1
LEFT JOINTable2 t2 ONt1.ID = t2.ID
WHEREt2.ID IS NULL

union

SELECTt1.*,'Changed'
FROMTable1 t1
INNER JOINTable2 t2 ONt1.ID = t2.ID
except
SELECTt2.*,'Changed'
FROMTable1 t1
INNER JOINTable2 t2 ONt1.ID = t2.ID

union

SELECTt2.*, 'Deleted'
FROMTable1 t1
RIGHT JOINTable2 t2 ONt1.ID = t2.ID
WHEREt1.ID IS NULL

union

SELECTt1.*,'UnChanged'
FROMTable1 t1
INNER JOINTable2 t2 ONt1.ID = t2.ID
INTERSECT
SELECTt2.*,'UnChanged'
FROMTable1 t1
INNER JOINTable2 t2 ONt1.ID = t2.ID

ORDER BY XX

I tried this with two tables each having 100 columns and 5000 rows. I could detect above mentioned changes like Added/Changed/Deleted/UnChanged within 18 seconds.

Thus I believe set operations can offer much cleaner and simpler solution in such scenarios.