多种方法从SQL Server中去除重复记录
Different strategies for removing duplicate records in SQL Server
Written By: Arshad Ali -- 1/8/2010
from：http://www.mssqltips.com/tip.asp?tip=1918
The level of compression is amazing.
Free SQL Server Books ----- Learn fast with our tutorials!
Problem
In data warehousing applications during ETL (Extraction, Transformation and Loading) or even in OLTP (On Line Transaction Processing) applications we are often encountered with duplicate records in our table. To make the table data consistent and accurate we need to get rid of these duplicate records keeping only one of them in the table. In this tip I discuss different strategies which you can take for this, along with the pros and cons.
Solution
There are different methods for deleting duplicate (de-duplication) records from a table, each of them has its own pros and cons. I am going to discuss these methods, prerequisite of each of these methods along with its pros and cons.
Using correlated subquery
Using temporary table
Creating new table with distinct records and renaming it..
Using Common Table Expression (CTE)
Using Fuzzy Group Transformation in SSIS
Using MERGE Statement
1. Using correlated subquery
If you already have a identity column on your table, your work is half done. You can use a correlated subquery to get rid of the duplicates.
First let me briefly tell you how a correlated subquery works. In a correlated subquery, first outer query is evaluated, the result from the outer query is used by an inner sub query for its evaluation, whatever the outcome of the inner sub-query is again used by the outer query to get the final resultset. To learn more about correlated subqueries, you can click here.
In the example below, for the data deletion I am joining the inner query columns with the outer query to find the record with
相关文档：
QL分类：
DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)
DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)
首先,简要介绍基础语句：
1、说明：创建数据库
CREATE DATABASE database-name
2、说明：删除数据库
drop database dbname
3、 ......
CREATE PROCEDURE dbo.OutputData
@tablename varchar(200)
AS
declare @column varchar(1000)
declare @ ......
ORACLE SQL性能优化大总结
1） 选择最有效率的表名顺序(只在基于规则的优化器中有效)：
" ?; J) n+ v8 J- v" q Oracle的解析器按照从右到左的顺序处理from子句中的表名，from子句中写在最后的表(基础表 driving table)将被最先处理，在from子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表� ......
在SQL语句优化过程中，我们经常会用到hint,现总结一下在SQL优化过程中常见Oracle HINT的用法：
1. /*+ALL_ROWS*/
表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN from BSEMPMS WHERE EMP_NO='SCOTT';
2. /*+FIRST_ROWS*/
表明对语� ......
