Đang chuẩn bị liên kết để tải về tài liệu:
Microsoft SQL Server 2008 R2 Unleashed- P175

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 releases.Microsoft 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 TRY.CATCH construct in T-SQL. It provides a much more robust method of error handling than checking @@ERROR for error conditions. The TRY.CATCH 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 43.26 shows how to create an authors_import table and populate it with some duplicate rows. LISTING 43.27 Script to Create and Populate the authors import Table USE bigpubs2008 GO CREATE TABLE dbo.authors_import au_id dbo.id NOT NULL au_lname varchar 30 NOT NULL au_fname varchar 20 NOT NULL go INSERT INTO dbo.authors_import au_id au_lname VALUES 681-61-9588 Ahlberg1 Allan INSERT INTO dbo.authors_import au_id au_lname VALUES 739-35-5165 Ahlberg Janet INSERT INTO dbo.authors_import au_id au_lname VALUES 499-84-5672 Alexander Lloyd INSERT INTO dbo.authors_import au_id au_lname VALUES 499-84-5672 Alexander Lloyd INSERT INTO dbo.authors_import 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 dbo.authors_import au_id au_lname au_fname VALUES 432-31-3829 Bate1 W. Jackson1 INSERT INTO dbo.authors_import au_id au_lname au_fname VALUES 432-31-3829I BateI W. JacksonI INSERT INTO dbo.authors_import au_id au_lname au_fname VALUES