database

一、设计

1，数据类型尽量使用数字型，数字型的比较比字符型的快很多

2，数据类型尽量小，预测可以满足未来需求的前提

3，尽量建表时字段不允许为null，除非必要，可以用NOT NULL+DEFAULT代替

当你需要查询数据的时候你往往需要在WHERE条件中多加一个判断条件IS NOT NULL,这样的一个条件不仅仅增加了额外的开销，而且对查询的性能产生很大的影响，有可能就因为多了这个查询条件导致你的查询变的非常的慢；还有一个比较重要的问题就是允许为空的数据可能会导致你的查询结果出现不准确的问题，

----如果整形字段可以赋0，字符型可以赋值空（这里只是给建议）这里的空和NULL是不一样的意思
--增加整形字段可以这样写
ALTER TABLE TABLE_NAME ADD  COLUMN_NAME  INT NOT NULL DEFAULT(0)

--增加字符型字段可以这样写
ALTER TABLE TABLE_NAME ADD  COLUMN_NAME  NVARCHAR(50) NOT NULL DEFAULT('')

4，少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用

5,建立自增列时单独再给自增列添加唯一约束

如果要保证ID是唯一的，单单只设置自增值不行，需要给字段设置主键或者唯一约束

6，建立索引

可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引

A、根据数据量决定哪些表需要增加索引，数据量小的可以只有主键

B、根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段

C、把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面

D、一个表不要加太多索引，因为索引影响插入和更新的速度

建立索引后，并不是每个查询都会使用索引，在使用索引的情况下，索引的使用效率也会有很大的差别。只要我们在查询语句中没有强制指定索引，索引的选择和使用方法是SQLSERVER的优化器自动作的选择，而它选择的根据是查询语句的条件以及相关表的统计信息，这就要求我们在写SQL语句的时候尽量使得优化器可以使用索引。

二、编码

1，查询条件不要使用计算列

例：查询条件为 year(createdate)=2014

优化：createdate>='20140101' and createdate<='20141231'

原因：使用计算列查询，是通过[索引扫描]方式查找

不使用计算列，走的是索引查找

绝大部分情况下索引查找的查询性能要高于索引扫描，特别是查询的数据库不是非常大的情况下，索引查找的消耗时间要远远少于索引扫描的时间,相关知识[聚集、非聚集、堆的索引]

2，分组统计时避免使用count(*)

if OBJECT_ID('Customer') is not null drop table [Customer]
go
create table [Customer] (CId int not null,Name nvarchar(20));
go
if OBJECT_ID('Order') is not null drop table [Order]
go
create table [Order] (OId int not null, CusId int);
go
insert into Customer values(1,'小米'),(2,'大米'),(3,'mini')
insert into [Order] values(1,1),(2,2),(3,NULL),(4,1)

--例如:需要统计每个顾客的订单量
--使用count(*)
select CID,count(*) from Customer left join [order] on Customer.CId=[order].CusId group by CId

实际情况CusId=3是没有订单的，数量应该是0，但是结果是1,count()里面的字段是左连接右边的表字段，如果你用的是主表字段结果页是错误的。

--正确的方法是使用count(CusId)
select CID,count(CusId) from Customer left join [order] on Customer.CId=[order].CusId group by CId

3，子查询的表加上表别名

4，查询时使用*

查询时一定不能使用”*”来代替字段来进行查询，无论你查询的字段有多少个，就算字段太多无法走索引也避免了解析”*”带来的额外消耗。

查询字段值列出想要的字段，避免出现多余的字段，字段越多查询开销越大而且可能会因为多列出了某个字段而引起查询不走索引。

5，使用存储过程的好处

减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL语句，那么其性能绝对比一条一条的调用SQL语句要高得多。
执行速度更快。有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程缓存计划，这样下次再执行同样的存储过程时，可以从内存中直接调用。
更强的适应性：由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。
布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。
更好的封装移植性。
安全性，它们可以防止某些类型的 SQL 插入攻击。

默认情况下，存储过程将返回过程中每个语句影响的行数。如果不需要在应用程序中使用该信息（大多数应用程序并不需要），请在存储过程中使用 SET NOCOUNT ON 语句以终止该行为。根据存储过程中包含的影响行的语句的数量，这将删除客户端和服务器之间的一个或多个往返过程。尽管这不是大问题，但它可以为高流量应用程序的性能产生负面影响。

6，判断一条查询是否有值

IF NOT EXISTS/IF EXISTS 优于 COUNT(*)

7, 理解TRUNCATE和DELETE的区别

TRUNCATE操作没有记录删除日志操作

主要的原因是因为TRUNCATE操作不会激活触发器,因为TRUNCATE操作不会记录各行的日志删除操作,所以当你需要删除一张表的数据时你需要考虑是否应该如有记录日志删除操作，而不是根据个人的习惯来操作。

8，事务的理解

XACT_ABORT

---查询是否有打开事务
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 的影响。

9,条件字段的先后顺序

对于经常用作查询的字段放在第一个位置,其它的字段根据表的实际字段顺序排列，这样往往你的查询语句走索引的概率会更大。

10,避免使用长字节字段排序

order by Id 优于 order by CreateTime