--1. 删除已存在表.  
IF EXISTS (  
       SELECT 1 FROM sysobjects  WHERE  id = OBJECT_ID(N'test') AND OBJECTPROPERTY(id, N'IsUserTable') = 1  
   )  
BEGIN  
    DROP TABLE test  
END  
GO
--2. 建表
CREATE TABLE [dbo].test( [userId] [int] NOT NULL, [name] VARCHAR(20) NOT NULL,isEnabled BIT)
GO

INSERT INTO dbo.test 
SELECT 1,'aa',0 UNION ALL --注：只能用 union, 不能用 union all. 要不只会有一条记录
SELECT 1,'aa',1 UNION ALL
SELECT 2,'bb',0 UNION ALL
SELECT 2,'bb',0 UNION ALL
SELECT 2,'cc',1 UNION ALL
SELECT 3,'dd',0
GO

--如果有视图先删除
IF EXISTS(SELECT 1 FROM sys.views WHERE name='tmp_view_test')
	DROP VIEW tmp_view_test
GO
--创建视图 (注：PARTITION BY的功能为以xxx列来分组， 有点相当于group by)
--尽可能删除 isEnabled = 0 的记录
CREATE VIEW tmp_view_test AS
SELECT ROW_NUMBER() OVER(PARTITION BY [userId] ORDER BY ISNULL(isEnabled,0) DESC) AS num,*
FROM test
GO

--删除前情况
SELECT * FROM tmp_view_test ORDER BY userid,num

--删除重复
DELETE FROM tmp_view_test WHERE num!=1

--删除后情况
SELECT * FROM tmp_view_test ORDER BY userid,num

不用视图删除：

--1. 删除已存在表.  
IF OBJECT_ID(N'test') IS NOT NULL  
BEGIN  
    DROP TABLE test  
END  
GO
--2. 建表
CREATE TABLE [dbo].test( [userId] [int] NOT NULL, [name] VARCHAR(20) NOT NULL,isEnabled BIT)
GO
SET NOCOUNT ON
INSERT INTO dbo.test 
SELECT 1,'aa',0 UNION ALL --注：只能用 union, 不能用 union all. 要不只会有一条记录
SELECT 1,'aa',1 UNION ALL
SELECT 2,'bb',0 UNION ALL
SELECT 2,'bb',0 UNION ALL
SELECT 2,'cc',1 UNION ALL
SELECT 3,'dd',0
GO
SELECT * FROM test
/*
userId	name	isEnabled
1	    aa	    0
1	    aa	    1
2	    bb	    0
2	    bb	    0
2	    cc	    1
3	    dd	    0
*/

DELETE FROM tmp FROM 
(SELECT ROW_NUMBER() OVER (PARTITION BY userId ORDER BY isEnabled DESC) AS rid,* FROM test) AS tmp
WHERE rid!=1

SELECT * FROM test
/*
userId	name	isEnabled
1		aa		1
2		cc		1
3		dd		0
*/