I had a user ask me today how they can delete a duplicate row from their database table. They were receiving the following error message when they tried to delete the duplicate row.

The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.

The reason that the user gets this message is because the table did not have a Primary Key or other Unique Index defined. Why, that’s another story and not one I know!

An example table

Some example data

Notice the duplicate row (row 1 and row 3)

So the user in this case would select the first row and hit the delete button

They would then see this message

So how do we get rid of the duplicate row?

Two options

Option #1

Add a new column which contains unique values

We could run the following code to add the new column and populate the new column with unique values

ALTER TABLE Table1
ADD TempID int IDENTITY(1, 1)

After issuing this command we will have something like this.

The user can now select their row and hit delete because SQL Server can uniquely identify this row.

You could then remove the new column

ALTER TABLE Table1
drop COLUMN TempID

But this adds a new column to the table which is not really the best option so on to option #2

Option #2

We can simply issue a simple delete command taking advantage of the Set RowCount command.

SET ROWCOUNT 1

DELETE FROM Table1
WHERE id= 1

What this does is limit the delete command to one record. Since the data is duplicated you do not care which row is deleted as long as only one of the rows is deleted. No adding and removing columns.

WebMatrix includes a complete Web development stack that integrates a Web server (IIS Developer Express), database (Microsoft SQL Server Compact 4.0), programming model (ASP.NET Web pages with Razor syntax), and a tool (WebMatrix Beta) into a seamless experience.  You can use WebMatrix to streamline the way you create an ASP.NET Web site from templates, or by starting a new Web site by using the world’s most popular free and open source (ASP.NET or PHP) apps like DotNetNuke, Umbraco, WordPress, or Joomla!. With WebMatrix you can code your Web sites, customize them, optimize them for good search engine ranking, test them, and deploy them to an Internet hosting company, all through the tool.

Learn more about WebMatrix through:

• What is WebMatrix? 
• Investigate the top 10 features of WebMatrix.
• Download WebMatrix Beta.
• More about IIS Developer Express.
• More about Microsoft SQL Server Compact 4.0.

For additional resources, visit:

• Learn more about WebMatrix Beta. More resources, links to forums and feedback channels.
• Visit the forums on the IIS Web site or the ASP.NET Web site.
• File a bug or suggestion on WebMatrix Beta.
• Learn to program the Web using ASP.NET Web pages with Razor syntax.
• Download applications with WebMatrix and the Web Application Gallery.
• Review the FAQs for Open Source and Free Web applications.

To start I should mention that you can not search the actual data in your tables for text, but you can search your SQL Objects – Tables, Views, Stored Procedures, Constraints, Triggers and Functions for text.

Often you want to find all references to a particular piece of text which could be the name of a table or column or just something that you have in a stored procedure.

The free tool from Red-Gate software fits the bill well, supporting SQL 2005 and above (including the express editions of Management Studio – See below for notes on SQL Express 2005)

There is some advertising links to their other products included in the tool, but they are well worth a look at if you have never used them before – Red-Gate products are great.

Downloading the tool

To download the tool simply go to http://www.red-gate.com/products/sql_search/

Installation
After you install SQL Search you get a new toolbar with a single button   in SQL Server Management Studio. Clicking this button opens the UI which add a new tab to the current set of open tabs and will select the current database as the database to be searched (you can change this from the drop down).

Note: You can only search a single database at a time.

The search is activated by simply typing into the search field and your results are immediately displayed in a grid (no need to hit enter).

You can limit the search to different types of objects such as tables or views if needed.

Clicking on any of the search results will display the full text of the object where it was found with any matching text highlighted.

SQL 2005 Management Studio Express

This statement is included on the requirements page for SQL Search at http://www.red-gate.com/products/sql_search/requirements.htm

* To use SQL Search with SSMS 2005 Express Edition, extensibility.dll must be installed.

But then they give you no indication of what that means or where you get it from.

You can download the extensibility.dll by installing the following tool:
ftp://ftp.red-gate.com/development/sqlprompt/extensibilitymsm.msi