select * from
(select * from T1 union all
select * from t2) a where
1 --- charindex('H',field_1)=7
2 --- field_1 like '______H%'
3 --- substring(field_1,7,1)='H'这三种条件哪种效率最好??
(select * from T1 union all
select * from t2) a where
1 --- charindex('H',field_1)=7
2 --- field_1 like '______H%'
3 --- substring(field_1,7,1)='H'这三种条件哪种效率最好??
(select * from T1 union all
select * from t2) a where
1 --- charindex('H',field_1)=7
2 --- field_1 like '______H%' field_1如果有索引的话,应该是第二种
2那样的like也不能使用索引
--建立测试表TBIF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GOCREATE TABLE TB(
COL1 VARCHAR(50) ,
COL2 VARCHAR(50)
)--第一种索引方式
CREATE CLUSTERED INDEX col1_index ON TB (COL1)
CREATE NONCLUSTERED INDEX col2_index ON TB (COL2)--第二种索引方式
/*CREATE NONCLUSTERED INDEX col1_index ON TB (COL1)
CREATE NONCLUSTERED INDEX col2_index ON TB (COL2)*/
--插入测试数据
go
INSERT INTO TB
SELECT 'D','A' UNION ALL
SELECT 'E','A' UNION ALL
SELECT 'F','A' UNION ALL
SELECT 'G','A'
GO 1000--用第一种索引,计划里是索引查找
--用第二种索引,计划里是表扫描
SELECT col1 FROM TB WHERE
CHARINDEX('D',COL1)>0 OR CHARINDEX('D',COL1)>0
OR COL1 LIKE '%D%'
OR SUBSTRING(COL1,1,1)=''
--删除索引
DROP INDEX col1_index ON TB
DROP INDEX col2_index ON TB 77可以用我这个测一下
结果大概是这样的:
COL1聚集索引:
CHARINDEX,LIKE '%A%',SUBSTRING都能用到索引
COL1非聚集索引:
SELECT COL1 FROM TB --SELECT 1 FROM TB
WHERE CHARINDEX('A',COL1)>0
这两种情况可以用到索引SELECT COL2 FROM TB --SELECT * FROM TB
WHERE CHARINDEX('A',COL1)>0
这样用不到索引SELECT * FROM TB
WHERE COL1 LIKE 'A%'
这样能乃至索引------------------------------
所以要根据SELECT的不同来区别对待