I wanted to add this to my blog because I had some difficulty finding out (via Google) how to add a UNIQUE constraint to a SQL column that already existed. To bring you up to speed, UNIQUE constraints prevent duplicate row entries in a column.

Note: This pertains to Microsoft SQL Server 2000.

To add a UNIQUE constraint to a table column use the following SQL query.

ALTER TABLE [dbo].[UserProfile]
ADD CONSTRAINT [UniqueEmail]UNIQUE ([Email])

If you try to add a UNIQUE constraint to a column with duplicate rows SQL will throw an error. So clean up those duplicates first. Also, keep in mind that having unique rows means you cannot have duplicate NULL values.

To remove a UNIQUE constraint use the following SQL query.

ALTER TABLE [dbo].[UserProfile]
DROP CONSTRAINT [UniqueEmail]

An interesting thing happened to me yesterday. A coworker came up to me and asked, “Did you write this SQL statement?” After looking at it I realized it was a statement I had written about a year ago. In this statement, I had come up with a clever way of creating what is essentially a SQL identity column. The first thought that came to my mind was “What an idiot! I could have just added an identity column to the table!” But what my coworker noticed, that I did not, is that this statement has powerful implications.

alter table temp_your_table
add Reference_Number int

declare @intCounter int
set @intCounter = 1000
update temp_your_table
SET @intCounter = Reference_Number = @intCounter + 1

The neat thing about this statement is that it can be used, instead of a loop, to mathematically manipulate a column. For example you could add a column of Fibonacci numbers! I’m not sure why a person would need a column of Fibonacci numbers, but it’s empowering to know that you could. 😉 There are probably several other neat applications that could be done with this approach.

Truth be told, things like this happen when a college grad with limited SQL knowledge, but an abundance of C++ education, begins learning SQL.

** UPDATE **
** 5/16/2006 **

Well I found a great use for the SQL statement above. For a recent project I needed to create a region specific drop down list of languages. If a user is visiting our Spanish site their drop down list needs to include the Spanish spellings of our language options in alphabetical order, BUT with Español on top of the list. Likewise, if the user is visiting our Portuguese site they need to see the list of languages with the Portuguese spelling, but with Portuguese on top.

To do this I sorted my list of languages in alphabetical order into a temp table with an “OrderNumber” column. Then I updated the “OrderNumber” column with an incrementing integer starting with 1. Next I updated my temp table, setting the “OrderNumber” of the default language to 0. Finally, I return the temp table ordered on my “OrderNumber” column. The whole procedure looks like the following. (I highlighted the “Haunted SQL Statement” from above in red.):

CREATE procedure GetLocalLanguageNameList @LanguageCode varchar(8) as

set nocount on

-- Find the ID of the Language the Page is Requesting
declare @LocalLanguageId as int
set @LocalLanguageId = (select LanguageId from Language where LanguageCode = @LanguageCode)

-- Get a list of alternative languages in the native tongue of the user
-- Add OrderNumber so we can use it to put the original Page Language ontop
-- Sore the alternative languages in alphabetical order
select l.LanguageCode, lln.LanguageName, 0 as OrderNumber
into #tempLangs
from dbo.LocalLanguageName lln
 join dbo.Language l on lln.LanguageId = l.LanguageId
where lln.LocalLanguageId = @LocalLanguageId
order by lln.LanguageName

-- Number our languages in alphabetical order
declare @intCounter int
set @intCounter = 1
update #tempLangs
SET @intCounter = OrderNumber = @intCounter + 1

-- Update the placement of the current pages language to the front of our list
update #tempLangs
set OrderNumber = 0
where LanguageCode = @LanguageCode

-- Display the list in our final order
select LanguageCode, LanguageName
from #tempLangs
order by OrderNumber

Thought I’d add a small nugget of SQL knowledge I recently acquired to the blog; the difference between truncate table and delete from table. Both are ways to remove all the rows from a table, but which one should you use?

First off, a quick SQL language lesson. SQL statements are divided into two categories: the Data Definition Language (DDL) and the Data Manipulation Language (DML). DDL statements are used create and manipulate data structures. DML statements are used to modify data in those structures. DDL statements take place immediately. DML statements take place in memory first until they are committed to the database.

Truncate table is a DDL statement. Delete from table is a DML statement. This means that you must have ALTER permissions on the table if you want to use truncate.

The differences breakdown:

Truncate Table	Delete From Table
Data Definition Language (DDL) Statement	Data Manipulation Language (DML) Statement
Truncate table writes to the log file only once.	Delete from writes to the log for every row.
Truncate table uses a table lock.	Delete from locks each row.
Truncate table resets the identity counter.	Delete from does not reset the identity counter.
You can’t truncate a table that is referenced by foreign keys.	You can use delete from on a table with foreign keys.
Truncate doesn’t activate triggers.	Delete from will activate triggers.

If you know any more differences drop me a comment.