creative developer(SQL-DOTNET)

I often get requests from blog reader for T-SQL script to rename database table column name or rename table itself. Here is a video demonstrating the discussion The script for renaming any column : sp_RENAME  'TableName . [OldColumnName]'  ,  '[NewColumnName]' ,  'COLUMN' The script for renaming any object (table,  sp   etc ) : sp_RENAME  '[OldTableName]'  ,  '[NewTableName]' This article demonstrates two examples of renaming database object. Renaming database table  column to new  name. Renaming database table to  new name . In both the cases we will first see existing table. Rename the object. Test object again with  new name . 1. Renaming database table  column to new  name. Example  uses AdventureWorks database. A small table with name “ Table_First ” is created.  Table  has two fields ID and Name. Now, to change the Column Name from “Name” to “ NameChange ” we can use ...

I had previously penned down two popular snippets regarding deleting duplicate rows and counting duplicate rows. Today, we will examine another very quick code snippet where we will delete duplicate rows using CTE and ROW_NUMBER() feature of SQL Server 2005 and SQL Server 2008. This method is improved over the earlier method as it not only uses CTE and ROW_NUMBER, but also demonstrates the power of CTE with DELETE statement. We will have a comprehensive discussion about it later in this article. For now, let us first create a sample table from which we will delete records. /* Create Table with 7 entries - 3 are duplicate entries */ CREATE TABLE  DuplicateRcordTable  ( Col1  INT ,  Col2  INT ) INSERT INTO  DuplicateRcordTable SELECT  1 ,  1 UNION  ALL SELECT  1 ,  1  --duplicate UNION  ALL SELECT  1 ,  1  --duplicate UNION  ALL SELEC...