Microsoft SQL Server 2008 R2 Unleashed- P175:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1684 CHAPTER 43 Transact-SQL Programming Guidelines Tips and Tricks select @rowcnt @@ROWCOUNT @error @@ERROR if @rowcnt 0 print no rows updated if @error 0 raiserror Update of titles failed1 16 1 return NOTE Error processing was improved in SQL Server 2005 with the introduction of the construct in T-SQL. It provides a much more robust method of error handling than checking @@ERROR for error conditions. The construct is discussed in more detail later in this chapter. De-Duping Data with Ranking Functions One common problem encountered with imported data is unexpected duplicate data rows especially if the data is being consolidated from multiple sources. In previous versions of SQL Server de-duping the data often involved the use of cursors and temp tables. Since the introduction of the ROW_NUMBER ranking function and common table expressions in SQL Server 2005 you are able to de-dupe data with a single statement. To demonstrate this approach Listing shows how to create an authors_import table and populate it with some duplicate rows. LISTING Script to Create and Populate the authors import Table USE bigpubs2008 GO CREATE TABLE au_id NOT NULL au_lname varchar 30 NOT NULL au_fname varchar 20 NOT NULL go INSERT INTO au_id au_lname VALUES 681-61-9588 Ahlberg1 Allan INSERT INTO au_id au_lname VALUES 739-35-5165 Ahlberg Janet INSERT INTO au_id au_lname VALUES 499-84-5672 Alexander Lloyd INSERT INTO au_id au_lname VALUES 499-84-5672 Alexander Lloyd INSERT INTO au_id au_lname VALUES 432-31-3829 Bate W. Jackson au_fname au_fname au_fname au_fname au_fname T-SQL Tips and Tricks 1685 INSERT INTO au_id au_lname au_fname VALUES 432-31-3829 Bate1 W. Jackson1 INSERT INTO au_id au_lname au_fname VALUES 432-31-3829I BateI W. JacksonI INSERT INTO au_id au_lname au_fname VALUES
