Tag Archives: SQL

Removing duplicates from SQL Server 2005

I am currently working on project that is required to remove duplicate entries from Facility table, duplicates are based on address and city being the same. The duplicates would be recorded into an excel file for further examination and later … Continue reading

Posted in Development | Tagged | Leave a comment

SQL Server 2005 Query XML data stored in ntext field

We moved from SQL 2000 to SQL 2005 so we are still storing the XML data in ntext field. Finally I need to mine the XML content and I thought cool let me write some XPath queries and be done … Continue reading

Posted in Development | Tagged | 1 Comment

Using the SQL 2005 Pivot Clause

Let’s say you are querying interview table for the interview id 488 Lets say we are only interested in these 3 disease specific fields Lets get the interview values for the above fields along with the values from the interview … Continue reading

Posted in Development, Uncategorized | Tagged , | Leave a comment

Add Column with a check on acceptable range of values

Here is the code to add a new column to an existing table with a check on range. The following column only allows values of 0,1 or 2. IF NOT EXISTS (SELECT c.name FROM dbo.sysobjects s INNER JOIN dbo.syscolumns c … Continue reading

Posted in Development | Tagged | Leave a comment

Dropping unnamed default constraints

Ron Talmage article on Default constraints explains why we should always name our default constraints. In case you created a table with unnamed default constraint then SQL server automatically assigns one for you, scripting to drop that column would be … Continue reading

Posted in Development | Tagged | Leave a comment

SQL 2005 drop unique constraint

I just realized that we can not use drop constraint to drop a unique constraint on a table. Since unique constraint is implemented as index we need to drop the index. IF EXISTS (SELECT name FROM sys.indexes WHERE name = … Continue reading

Posted in Development | Tagged | Leave a comment