概述
因为每天需要审核程序员发布的SQL语句,所以收集了一些程序员的一些常见问题,还有一些平时收集的其它一些问题,这也是很多人容易忽视的问题,在以后收集到的问题会补充在文章末尾,欢迎关注,由于收集的问题很多是针对于生产数据,测试且数据量比较大,这里就不把数据共享出来了,大家理解意思就行。
步骤
大小写
大写T-SQL 语言的所有关键字都使用大写,规范要求。
使用“;”
使用“;”作为 Transact-SQL 语句终止符。虽然分号不是必需的,但使用它是一种好的习惯,对于合并操作MERGE语句的末尾就必须要加上“;”
(cte表表达式除外)
数据类型
避免使用ntext、text 和 image 数据类型,用 nvarchar(max)、varchar(max) 和 varbinary(max)替代
后续版本会取消ntext、text 和 image 该三种类型
查询条件不要使用计算列
例如year(createdate)=2014,使用createdate>=’ 20140101’ and createdate<=’ 20141231’来取代。
IF OBJECT_ID('News','U') IS NOT NULL DROPTABLE News GOCREATETABLE News (IDINTNOTNULL PRIMARY KEYIDENTITY(1,1), NAMENVARCHAR(100) NOTNULL, Createdate DATETIME NOTNULL ) GOCREATE NONCLUSTERED INDEX [IX1_News] ON [dbo].[News] ( [Createdate] ASC ) INCLUDE ( [NAME]) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GOGOINSERTINTO News(NAME,Createdate) VALUES( '新闻','2014-08-20 00:00:00'),( '新闻','2014-08-20 00:00:00'),( '新闻','2014-08-20 00:00:00'),( '新闻','2014-08-20 00:00:00')
—使用计算列查询(走的是索引扫描)
SELECTID,NAME,Createdate FROM News WHEREYEAR(Createdate)=2014

—不使用计算列(走的是索引查找)
SELECTID,NAME,Createdate FROM News WHERE CreateDate>='2014-01-01 00:00:00'and CreateDate<'2015-01-01 00:00:00'

对比两个查询显然绝大部分情况下走索引查找的查询性能要高于走索引扫描,特别是查询的数据库不是非常大的情况下,索引查找的消耗时间要远远少于索引扫描的时间,如果想详细了解索引的体系结构可以查看了我前面写的几篇关于聚集、非聚集、堆的索引体系机构的文章。

请参看:http://www.cnblogs.com/chenmh/p/3780221.html

请参看:http://www.cnblogs.com/chenmh/p/3782397.html
建表时字段不允许为null
发现很多人在建表的时候不会注意这一点,在接下来的工作中当你需要查询数据的时候你往往需要在WHERE条件中多加一个判断条件IS NOT NULL,这样的一个条件不仅仅增加了额外的开销,而且对查询的性能产生很大的影响,有可能就因为多了这个查询条件导致你的查询变的非常的慢;还有一个比较重要的问题就是允许为空的数据可能会导致你的查询结果出现不准确的问题,接下来我们就举个例子讨论一下。
T-SQL是三值逻辑(true,flase,unknown) IF OBJECT_ID('DBO.Customer','U') IS NOT NULL
DROPTABLE DBO.Customer
GOCREATETABLE DBO.Customer (Customerid
intnotnull ); GO IF OBJECT_ID('DBO.OrderS','U') IS NOT NULL
DROPTABLE DBO.OrderS
GOCREATETABLE DBO.OrderS (Orderid
intnotnull, custid
int); GO
INSERTINTO Customer
VALUES(
1),(
2),(
3);
INSERTINTO OrderS
VALUES(
1,
1),(
2,
2),(
3,
NULL);
SELECT Customerid
FROM DBO.Customer
WHERE Customerid
NOTIN(
SELECT custid
FROM OrderS);
SELECT Customerid
FROM DBO.Customer
WHERE Customerid
NOTIN(
SELECT custid
FROM OrderS
WHERE custid
isnotnull);
SELECT Customerid
FROM DBO.Customer A
WHERENOTEXISTS(
SELECT custid
FROM OrderS
WHERE OrderS.custid=A.Customerid );
SELECT Customerid
FROM DBO.Customer
WHERE Customerid
IN(
SELECT custid
FROM OrderS);
–增加整形字段可以这样写
ALTER TABLE TABLE_NAME ADD COLUMN_NAME INT NOT NULL DEFAULT(0)
–增加字符型字段可以这样写
ALTER TABLE TABLE_NAME ADD COLUMN_NAME NVARCHAR(50) NOT NULL DEFAULT(”)
### **分组统计时避免使用count(*)**
IF OBJECT_ID('DBO.Customer','U') IS NOT NULL
DROPTABLE DBO.Customer
GOCREATETABLE DBO.Customer (Customerid
intnotnull ); GO IF OBJECT_ID('DBO.OrderS','U') IS NOT NULL
DROPTABLE DBO.OrderS
GOCREATETABLE DBO.OrderS (Orderid
intnotnull, custid
int); GO
INSERTINTO Customer
VALUES(
1),(
2),(
3);
INSERTINTO OrderS
VALUES(
1,
1),(
2,
2),(
3,
NULL); 例如:需要统计每一个顾客的订单数量
SELECT Customerid,
COUNT(*)
FROM Customer TA
LEFTJOIN OrderS TB
ON TA.Customerid=TB.custid
GROUPBY Customerid ;

实际情况customerid=3是没有订单的,数量应该是0,但是结果是1,count()里面的字段是左连接右边的表字段,如果你用的是主表字段结果页是错误的。
SELECT Customerid,COUNT(custid) FROM Customer TA LEFTJOIN OrderS TB ON TA.Customerid=TB.custid GROUPBY Customerid;

### **子查询的表加上表别名**
IF OBJECT_ID('DBO.Customer','U') IS NOT NULL DROPTABLE DBO.Customer GOCREATETABLE DBO.Customer (Customerid intnotnull ); GO IF OBJECT_ID('DBO.OrderS','U') IS NOT NULL DROPTABLE DBO.OrderS GOCREATETABLE DBO.OrderS (Orderid intnotnull, custid int); GO INSERTINTO Customer VALUES(1),(2),(3); INSERTINTO OrderS VALUES(1,1),(2,2),(3,NULL);
大家发现下面语句有没有什么问题,查询结果是怎样呢?
SELECT Customerid FROM Customer WHERE Customerid IN(SELECT Customerid FROM OrderS WHERE Orderid=2 );

正确查询结果下查询出的结果是没有customerid为3的值
为什么结果会这样呢?
大家仔细看应该会发现子查询的orders表中没有Customerid字段,所以SQL取的是Customer表的Customerid值作为相关子查询的匹配字段。
所以我们应该给子查询加上表别名,如果加上表别名,如果字段错误的话会有错误标示
正确的写法:
SELECT Customerid FROM Customer WHERE Customerid IN(SELECT tb.custid FROM OrderS tb WHERE Orderid=2 );
### **建立自增列时单独再给自增列添加唯一约束**
USE tempdb CREATETABLETEST (IDINTNOTNULLIDENTITY(1,1), orderdate dateNOTNULLDEFAULT(CURRENT_TIMESTAMP), NAMENVARCHAR(30) NOTNULL, CONSTRAINT CK_TEST_NAME CHECK(NAMELIKE'[A-Za-z]%' ) ); GO INSERTINTO tempdb.DBO.TEST(NAME) VALUES('A中'),('a名'),('Aa'),('ab'),('AA'),('az'); INSERTINTO tempdb.DBO.TEST(NAME) VALUES('中'); GO SELECTIDENT_CURRENT('tempdb.DBO.TEST'); SELECT * FROM tempdb.DBO.TEST; INSERTINTO tempdb.DBO.TEST(NAME) VALUES('cc'); SELECTIDENT_CURRENT('tempdb.DBO.TEST') SELECT * FROM tempdb.DBO.TEST; SET IDENTITY_INSERT tempdb.DBO.TEST ONINSERTINTO tempdb.DBO.TEST(ID,NAME) VALUES(8,'A中'); SET IDENTITY_INSERT tempdb.DBO.TEST OFFSELECTIDENT_CURRENT('tempdb.DBO.TEST'); SELECT * FROM tempdb.DBO.TEST; SET IDENTITY_INSERT tempdb.DBO.TEST ONINSERTINTO tempdb.DBO.TEST(ID,NAME) VALUES(8,'A中'); SET IDENTITY_INSERT tempdb.DBO.TEST OFFSELECTIDENT_CURRENT('tempdb.DBO.TEST') SELECT * FROM tempdb.DBO.TEST; DROPTABLE tempdb.DBO.TEST;
### **查询时一定要制定字段查询**
l 查询时一定不能使用””来代替字段来进行查询,无论你查询的字段有多少个,就算字段太多无法走索引也避免了解析””带来的额外消耗。
l 查询字段值列出想要的字段,避免出现多余的字段,字段越多查询开销越大而且可能会因为多列出了某个字段而引起查询不走索引。
创建测试数据库
CREATETABLE [Sales].[Customer]( [CustomerID] [int] IDENTITY(1,1) NOTFORREPLICATIONNOTNULL, [PersonID] [int] NULL, [StoreID] [int] NULL, [TerritoryID] [int] NULL, [AccountNumber] AS (isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]),'')), [rowguid] [uniqueidentifier] ROWGUIDCOL NOTNULL, [ModifiedDate] [datetime] NOTNULL, CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED ( [CustomerID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
创建索引
CREATE NONCLUSTERED INDEX [IX1_Customer] ON [Sales].[Customer] ( [PersonID] ASC ) INCLUDE ( [StoreID], [TerritoryID], [AccountNumber], [rowguid]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
查询测试
SETSTATISTICS IO ONSETSTATISTICSTIMEONSELECT * FROM [Sales].[Customer] WHERE PersonID=1; SETSTATISTICSTIMEOFFSETSTATISTICS IO OFF

由于建的索引‘IX1_Customer’没有包含ModifiedDate字段,所以需要通过键查找去聚集索引中获取该字段的值
SETSTATISTICS IO ONSETSTATISTICSTIMEONSELECT CustomerID, [PersonID] ,[StoreID] ,[TerritoryID] ,[AccountNumber] ,[rowguid] FROM [Sales].[Customer] WHERE PersonID=1; SETSTATISTICSTIMEOFFSETSTATISTICS IO OFF

由于查询语句中没有对ModifiedDate字段进行查询,所以只走索引查找就可以查询到需要的数据,所以建议在查询语句中列出你需要的字段而不是为了方便用*来查询所有的字段,如果真的
需要查询所有的字段也同样建议把所有的字段列出来取代‘*’。
使用存储过程的好处
- 减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别,可是如果存储过程包含上百行SQL语句,那么其性能绝对比一条一条的调用SQL语句要高得多。
- 执行速度更快。有两个原因:首先,在存储过程创建的时候,数据库已经对其进行了一次解析和优化。其次,存储过程一旦执行,在内存中就会保留一份这个存储过程缓存计划,这样下次再执行同样的存储过程时,可以从内存中直接调用。
- 更强的适应性:由于存储过程对数据库的访问是通过存储过程来进行的,因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动,而这些改动不会对应用程序造成影响。
- 布式工作:应用程序和数据库的编码工作可以分别独立进行,而不会相互压制。
- 更好的封装移植性。
- 安全性,它们可以防止某些类型的 SQL 插入攻击。
PROCEDURE [dbo].[SPSalesPerson] (@option varchar(50)) AS BEGINSET NOCOUNT ONIF @option='select'BEGINSELECT [DatabaseLogID] ,[PostTime] ,[DatabaseUser] ,[Event] ,[Schema] ,[Object] ,[TSQL] ,[XmlEvent] FROM [dbo].[DatabaseLog] ENDIF @option='SalesPerson'BEGINSELECT [BusinessEntityID] ,[TerritoryID] ,[SalesQuota] ,[Bonus] ,[CommissionPct] ,[SalesYTD] ,[SalesLastYear] ,[rowguid] ,[ModifiedDate] FROM [Sales].[SalesPerson] WHERE BusinessEntityID<300ENDSET NOCOUNT OFFEND
EXEC SPSalesPerson @option='select' EXEC SPSalesPerson @option='SalesPerson' DBCC FREEPROCCACHE----清空缓存 ---测试两个查询是否都走了缓存计划 SELECT usecounts,size_in_bytes,cacheobjtype,objtype,TEXT FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st; --执行计划在第一次执行SQL语句时产生,缓存在内存中,这个缓存的计划一直可用,直到 SQL Server 重新启动,或直到它由于使用率较低而溢出内存。 默认情况下,存储过程将返回过程中每个语句影响的行数。如果不需要在应用程序中使用该信息(大多数应用程序并不需要),请在存储过程中使用 SET NOCOUNT ON 语句以终止该行为。根据存储过程中包含的影响行的语句的数量,这将删除客户端和服务器之间的一个或多个往返过程。尽管这不是大问题,但它可以为高流量应用程序的性能产生负面影响。
### 判断一条查询是否有值
SETSTATISTICS IO ONSETSTATISTICSTIMEONDECLARE @UserType INT ,@StatusINTSELECT @UserType=COUNT(c.Id) FROM Customerfo t INNERJOIN Customer c ON c.Id=t.CustomerId WHERE c.customerTel='13400000000'IF(@UserType=0) BEGINSET @Status = 2 PRINT @StatusENDSETSTATISTICSTIMEOFFSETSTATISTICS IO OFFgoSETSTATISTICS IO ONSETSTATISTICSTIMEONIFNOTEXISTS(SELECT c.Id FROM Customerfo t INNERJOIN Customer c ON c.Id=t.CustomerId WHERE c.customerTel='13400000000') BEGINDECLARE @StatusintSET @Status = 2 PRINT @StatusENDSETSTATISTICSTIMEOFFSETSTATISTICS IO OFFgoSETSTATISTICS IO ONSETSTATISTICSTIMEONIFNOTEXISTS(SELECT top 1 c.id FROM Customerfo t INNERJOIN Customer c ON c.Id=t.CustomerId WHERE c.customerTel='13400000000'ORDERBY NEWID() ) BEGINDECLARE @StatusintSET @Status = 2 PRINT @StatusENDSETSTATISTICSTIMEOFFSETSTATISTICS IO OFFGOSETSTATISTICS IO ONSETSTATISTICSTIMEONIFNOTEXISTS(SELECT1FROM Customerfo t INNERJOIN Customer c ON c.Id=t.CustomerId WHERE c.customerTel='13410700660' ) BEGINDECLARE @StatusintSET @Status = 2 PRINT @StatusENDSETSTATISTICSTIMEOFFSETSTATISTICS IO OFF 这里说一下SELECT1,之前因为有程序员误认为查询SELECT1无论查询的数据有多少只返回一个1,其实不是这样的,和查询字段是一样的意思只是有多少记录就返回多少个1,1也不是查询的第一个字段。
**理解TRUNCATE和DELETE的区别**
IF OBJECT_ID('Table1','U') IS NOT NULL
DROPTABLE Table1
GOCREATETABLE Table1 (
IDINTNOTNULL, FOID
INTNOTNULL)
GOINSERTINTO Table1
VALUES(
1,
101),(
2,
102),(
3,
103),(
4,
104)
GOIF OBJECT_ID(
'Table2',
'U')
ISNOTNULLDROPTABLE Table2
GOCREATETABLE Table2 ( FOID
INTNOTNULL)
GOINSERTINTO Table2
VALUES(
101),(
102),(
103),(
104)
GOSELECT *
FROM Table1
GOSELECT *
FROM Table2
GO 在Table1表中创建触发器,当表中的数据被删除时同时删除Table2表中对应的FOID
CREATETRIGGER TG_Table1
ON Table1
AFTERDELETEASBEGINDELETEFROM TA
FROM Table2 TA
INNERJOIN deleted TB
ON TA.FOID=TB.FOID
ENDGODELETEFROM Table1
WHEREID=
1GOSELECT *
FROM Table1
GOSELECT *
FROM Table2
TRUNCATETABLE Table1
GOSELECT *
FROM Table1
GOSELECT *
FROM Table2
CHECKPOINT GO SELECT * FROM fn_dblog(NULL,NULL) GODELETEFROM Table2 WHERE FOID=102GOSELECT * FROM fn_dblog(NULL,NULL)

TRUNCATETABLE Table1 GOSELECT * FROM Table1 GOSELECT * FROM Table2

CHECKPOINT GO SELECT * FROM fn_dblog(NULL,NULL) GODELETEFROM Table2 WHERE FOID=102GOSELECT * FROM fn_dblog(NULL,NULL)

在第四行记录有一个lop_delete_rows,lcx_heap的删除操作日志记录
CHECKPOINT GO SELECT * FROM fn_dblog(NULL,NULL) GOTRUNCATETABLE Table2 GOSELECT * FROM fn_dblog(NULL,NULL) GO

TRUNCATE操作没有记录删除日志操作
主要的原因是因为TRUNCATE操作不会激活触发器,因为TRUNCATE操作不会记录各行的日志删除操作,所以当你需要删除一张表的数据时你需要考虑是否应该如有记录日志删除操作,而不是根据个人的习惯来操作。
事务的理解
IF OBJECT_ID('Table1','U') IS NOT NULL DROPTABLE Table1 GOCREATETABLE Table1 (IDINTNOTNULL PRIMARY KEY, Age INTNOTNULLCHECK(Age>10AND Age<50)); GO IF OBJECT_ID('Table2','U') IS NOT NULL DROPTABLE Table2 GOCREATETABLE Table2 ( IDINTNOTNULL) GO
1.简单的事务提交
BEGINTRANSACTIONINSERTINTO Table1(ID,Age) VALUES(1,20) INSERTINTO Table1(ID,Age) VALUES(2,5) INSERTINTO Table1(ID,Age) VALUES(2,20) INSERTINTO Table1(ID,Age) VALUES(3,20) COMMITTRANSACTIONGOSELECT * FROM Table1 所以并不是事务中的任意一条语句报错整个事务都会回滚,其它的可执行成功的语句依然会执行成功并提交。

2.TRY…CATCH
DELETEFROM Table1 BEGIN TRY BEGINTRANSACTIONINSERTINTO Table1(ID,Age) VALUES(1,20) INSERTINTO Table1(ID,Age) VALUES(2,20) INSERTINTO Table1(ID,Age) VALUES(3,20) INSERTINTO Table3 VALUES(1) COMMITTRANSACTIONEND TRY BEGIN CATCH ROLLBACKTRANSACTIONEND CATCH SELECT * FROM Table1 SELECT XACT_STATE() DBCC OPENTRAN ROLLBACKTRANSACTION
TRY…CATCH不会返回对象错误或者字段错误等类型的错误
想详细了解TRY…CATCH请参考http://www.cnblogs.com/chenmh/articles/4012506.html
3.打开XACT_ABORT
SET XACT_ABORT ONBEGINTRANSACTIONINSERTINTO Table1(ID,Age) VALUES(1,20) INSERTINTO Table1(ID,Age) VALUES(2,20) INSERTINTO Table1(ID,Age) VALUES(3,20) INSERTINTO Table3 VALUES(1) COMMITTRANSACTIONSET XACT_ABORT OFFSELECT * FROM Table1

SELECT XACT_STATE() DBCC OPENTRAN 未查询到有打开事务 当 SET XACT_ABORT 为 ON 时,如果执行 Transact-SQL 语句产生运行时错误,则整个事务将终止并回滚。 当 SET XACT_ABORT 为 OFF 时,有时只回滚产生错误的 Transact-SQL 语句,而事务将继续进行处理。如果错误很严重,那么即使 SET XACT_ABORT 为 OFF,也可能回滚整个事务。OFF 是默认设置。 编译错误(如语法错误)不受 SET XACT_ABORT 的影响。
所以我们应该根据自己的需求选择正确的事务。
修改字段NOT NULL的过程
在Address表中的有一个Address字段,该字段允许为NULL,现在需要将其修改为NOT NULL. BEGINTRANSACTIONSET QUOTED_IDENTIFIER ONSET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITBEGINTRANSACTIONGOCREATETABLE dbo.Tmp_Address ( IDintNOTNULL, Address nvarchar(MAX) NOTNULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GOALTERTABLE dbo.Tmp_Address SET (LOCK_ESCALATION = TABLE) GOIFEXISTS(SELECT * FROM dbo.Address) EXEC('INSERT INTO dbo.Tmp_Address (ID, Address) SELECT ID, Address FROM dbo.Address WITH (HOLDLOCK TABLOCKX)') GODROPTABLE dbo.Address GOEXECUTE sp_rename N'dbo.Tmp_Address', N'Address', 'OBJECT'GOCOMMITAlterTable Address AddTypesmallintNotNullDefault (1)
### 条件字段的先后顺序
你平时在写T_SQL语句的时候WHERE条件后面的字段的先后顺序你有注意吗?
IF OBJECT_ID('TAINFO','U')IS NOT NULL DROPTABLE TAINFO GOCREATETABLE [dbo].[TAINFO]( IDINTNOTNULL PRIMARY KEYIDENTITY(1,1), OIDINTNOTNULL, Stats SMALLINTCHECK (Stats IN(1,2)), MAC uniqueidentifier NOTNULL ) ON [PRIMARY] GOINSERTINTO TAINFO(OID,Stats,MAC) VALUES(101,1,'46B550F9-6E24-436D-9BC7-F0650F562E54'),(101,2,'46B550F9-6E24-436D-9BC7-F0650F562E54'),(102,1,'46B550F9-6E24-436D-9BC7-F0650F562E54'), (102,2,'46B550F9-6E24-436D-9BC7-F0650F562E54'),(103,2,'46B550F9-6E24-436D-9BC7-F0650F562E54'),(103,2,'46B550F9-6E24-436D-9BC7-F0650F562E54'), (103,1,'46B550F9-6E24-436D-9BC7-F0650F562E54'),(103,1,'46B550F9-6E24-436D-9BC7-F0650F562E54') GO
如果这是你的写的查询语句
SELECTID,OID,Stats MAC FROM TAINFO WHERE MAC='46B550F9-6E24-436D-9BC7-F0650F562E54'AND STATS=1ANDOID=102
我现在根据你的查询语句创建一条索引
CREATEINDEX IX2_TAINFO ON TAINFO(MAC,STATS,OID)
分别执行三条查询语句
SELECTID,OID,Stats MAC FROM TAINFO WHERE MAC='46B550F9-6E24-436D-9BC7-F0650F562E54'AND STATS=1ANDOID=102SELECTID,OID,Stats MAC FROM TAINFO WHEREOID=102AND STATS=1SELECTID,OID,Stats MAC FROM TAINFO WHERE STATS=1
执行计划分别为



从上面三天查询语句可以看出,只有第一条语句走的是索引查找,另外两条语句走的是索引扫描,而我们从字段的名称应该可以看的出OID字段应该是该表的一个外键字段也是经常会被用作查询的字段。
接下来我们重新换一下索引顺序
DROPINDEX IX2_TAINFO ON TAINFO GOCREATEINDEX IX1_TAINFO ON TAINFO(OID) INCLUDE(STATS,MAC) GO
依然执行前面的三条查询语句分析执行计划



分析执行计划前面两条查询语句都走的是索引查找,第三条查询的是索引扫描,而根据一般单独用第三条查询的业务应该不会常见,所以现在一条索引解决了两个常用查询的索引需求,避免了建两条索引的必要(所以当你建索引的时候索引的顺序很重要,一般把查询最频繁的字段设第一个字段,可以避免建多余的索引)。
为什么要把这个问题提出来呢,因为平时有遇到程序员在写查询语句的时候对于同一个查询条件每次的写法都不一样,往往是根据自己想到哪个字段就写哪个字段先,这样的习惯往往是不好的,就好比上面的例子如果别人看到你的查询条件建一个索引也是这样写的话往往一个表会出现很多多余的索引(或许有人会说DBA建好索引的顺序就好了,这里把这个因素排除吧),像后面的那个索引就解决了两个查询的需求。
所以这里我一般是这样规定where条件的,对于经常用作查询的字段放在第一个位置(比如上面例子的OID),其它的字段根据表的实际字段顺序排列,这样往往你的查询语句走索引的概率会更大。
理解外连接
IF OBJECT_ID('DBO.OrderS','U') IS NOT NULL DROPTABLE DBO.OrderS GOCREATETABLE DBO.OrderS (Orderid INTNOTNULL, custid INTNOTNULL, stats INTNOTNULL); GO IF OBJECT_ID('DBO.Customer','U') IS NOT NULL DROPTABLE DBO.Customer GOCREATETABLE DBO.Customer (Customerid INTNOTNULL ); GO INSERTINTO OrderS VALUES(1,101,0),(2,102,0),(3,103,1),(4,104,0); GO INSERTINTO Customer VALUES(101),(102),(103); SELECT TA.Orderid,TA.custid,TA.stats,TB.Customerid FROM OrderS TA LEFTJOIN Customer TB ON TA.stats<>'1'AND TA.custid=TB.Customerid WHERE TB.Customerid ISNULL

看到这结果是不是有点疑惑,我在连接条件里面写了TA.stats<>’1′,为什么结果还会查询出。
接下来我们换一种写法吧!
SELECT TA.Orderid,TA.custid,TA.stats,TB.Customerid FROM OrderS TA LEFTJOIN Customer TB ON TA.custid=TB.Customerid WHERE TA.stats<>'1'AND TB.Customerid ISNULL

接下来我就解释一下原因:对于外连接,连接条件不会改变主表的数据,即不会删减主表的数据
对于上面的查询主表是orders,所以无论你在连接条件on里面怎样设置主表的条件都不影响主表数据的输出,影响主表数据的输出只在where条件里,where条件影响最后数据的输出。而对于附表Customer 的条件就应该写在连接条件(on)里而不是where条件里,这里说的是外连接(包括左连接和右连接)。
对于inner join就不存在这种情况,无论你的条件是写在where后面还是on后面都是一样的,但是还是建议写在where后面。
谓词类型要与字段类型对齐
IF OBJECT_ID('Person','u')IS NOT NULL DROPTABLE Person GOCREATETABLE Person (IDINTNOTNULL PRIMARY KEYIDENTITY(1,1), Phone NVARCHAR(20) NOTNULL, CreateDate DATETIME NOTNULL ) INSERTINTO Person(Phone,CreateDate) VALUES('13700000000',GETDATE()),('13700000000',GETDATE()),('13800000000',GETDATE()) CREATEINDEX IX_Person ON Person(Phone,CreateDate)
1.谓词类型与字段类型不一致
SELECTIDFROM Person WHERE Phone=13700000000ANDDATEDIFF(DAY,CreateDate,GETDATE())=0

由于定义表的phone字段类型是字符型,而上面的查询条件phone写成了整形,导致执行计划走了索引扫描,且执行计划select也有提示。
2.谓词类型与字段类型一致
SELECTIDFROM Person WHERE Phone='13700000000'ANDDATEDIFF(DAY,CreateDate,GETDATE())=0

第二种查询phone谓词类型与字段类型一致,所以查询走了索引查找
在日常的语句编写过程中需要注意这类问题,这将直接影响性能。