1,Update不仅可以给字段赋值，直接给变量赋值也行
DECLARE @str VARCHAR(50)
UPDATE AA
SET aa.c3 = ‘hello’,@str = c3
FROM tmp aa
WHERE aa.c2 = ‘aa’

2, 赋值的保留值，即执行 SELECT ,UPDATE给变量赋值，若没有结果，则保留原值。若执行的是count,则变量赋值为0，若执行的是sum,则变量赋值为空，即对于聚合函数，除count返回为0外，其它如sum, max均返回NULL。
执行 SELECT ,UPDATE给变量赋值，若没有结果，则保留原值，对于聚合函数，都是返回有值的，只是对于count,若没有结果，那么计数为0，即返回值是0，而于sum,max若没有结果，那么聚合的结果是null,null也会返回，覆盖原变量值。

3，聚合函数中， SUM/AVG/COUNT中的NULL会被忽略，对sum,avg,还没什么关系，但要注意的是COUNT,NULL被忽略意味着若count(字段)，此字段有一行为NULL,则数量会少一行，他忽略的NULL就是()里的字段，若为常量或*，都不是NULL,也就没有忽略可言。

drop table t_count
create table t_count
(
c1 varchar(10) null,
c2 varchar(10) null
)

insert into t_count values(null,null)
insert into t_count values('a','b')
insert into t_count values('a','b')
insert into t_count values('c','d')
insert into t_count values('c','d')

select COUNT(1) from t_count --5
select COUNT(c1) from t_count --4
select COUNT(distinct c1) from t_count --2

--正常
select count(*) from (select distinct c1,c2 from t_count) t --3
--有NULL参与了运算，所以表达式值为NULL
select count(distinct c1+c2) from t_count --2

4，当用SELECT,UPDATE赋值时，加个TOP,找到即返回，防止遍历所有行！
注：select top 与 select相同点都会将所有满足条件的数据筛选出来然后进行赋值，top并不是筛选到一条就返回，若是将有满足条件的数据筛选出来后，赋值一个即返回，故赋值select top不会比select 性能提高多少。
DECLARE @username VARCHAR(50),@updateusername VARCHAR(50),
@count INT,@sum INT,@max int
SET @username = ‘tt’
SET @updateusername = ‘cc’
SET @count = 22
SET @sum = 33
SET @max = 44
SELECT @username = f.UserName
FROM frmuser f
WHERE 1=2
UPDATE f
SET @updateusername = f.username
FROM frmuser f
WHERE 1=2
SELECT @count= COUNT(1)
FROM frmuser f
WHERE 1=2
SELECT @sum= SUM(f.ID)
FROM frmuser f
WHERE 1=2
SELECT @max= max(f.ID)
FROM frmuser f
WHERE 1=2
SELECT @username –tt
SELECT @updateusername –cc
SELECT @count –0
SELECT @sum –null
SELECT @max –null

看了下SQL SERVER2005 关于TOP的定义，发现有一些用法还没掌握
1,取前40% select top (40) percent from table1 –若计算出的行数是小数，则最终行数会向上取整
2,declare @i int
set @i=1
select top @i, * from table1
在存储过程中得不到执行，因为top时后面只能是常量。 之前这样认为，并用了set rowcount @i解决，
但其实可用：select top (@i) * from table1来解决

3，在UPDATE,DELETE 中使用TOP
UPDATE TOP (2) frmuser
SET LastEditDate = GETDATE()

DELETE TOP (2) FROM frmUser

4,select top (@i)使用：对于sys.objects数据库，若类型为s则取2条，v 1条，p,3条
WITH lib AS(
SELECT ‘s’ TYPE ,2 showcount –将规则固定化
UNION ALL
SELECT ‘v’,1
UNION ALL
SELECT ‘p’,3
)

SELECT lib.*,o.*
FROM lib
CROSS APPLY(SELECT TOP (lib.showcount) NAME,create_date
FROM sys.objects o
WHERE o.[type] = lib.type
ORDER BY o.create_date)o
ORDER BY lib.type,o.create_Date

栏位用id代替实际内容，
一是进行封装，降低依赖,更改方便。
二是降少了每行的数据量，一个数据页可存储更多记录，IO每次按数据页为单位读取数据时，将能读出更多记录，提升了IO。

数字辅助表：一个1到N的表 应用：连续范围的结果处理，利用group by会有缺失范围！

创建此函数，每次调用 select dbo.fn_nums(24)即可：
CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
  SELECT n FROM Nums WHERE n <= @n;
--注对于n级，最大的行数为2的（2的n次幂）次幂 如n=5时，最大行数为：2的32次方共4294967296行数据！
GO

应用：1，一段日期范围内的订单数，可以用group by，但得不到连续的日期，即若某天没有业绩，就显示不出来，所以用group by有缺陷
数字辅助表：
DECLARE @OrderDateBegin VARCHAR(20),@OrderDateEnd VARCHAR(20)
SET @OrderDateBegin = '2011-01-01'
SET @OrderDateEnd = '2011-02-01'
DECLARE @Span INT
SET @Span = DATEDIFF(dd,@OrderDateBegin,@OrderDateEnd)
--SELECT @Span

SELECT DATEADD(dd,fn.n-1,@OrderDateBegin),
(SELECT COUNT(1) FROM bdorder b(NOLOCK)
 WHERE b.OrderDate>=DATEADD(dd,fn.n-1,@OrderDateBegin)
       AND b.OrderDate < DATEADD(dd,fn.n,@OrderDateBegin) )
FROM dbo.fn_nums(@Span) fn

2，与上面类似，但查询一天每个时间段的数据时或每几个时间段的数据时，也可通过上面的方法处理！

对于子查询，名称解析处理是从内部嵌套层向外进行的，若当天表中没有select引用的列，则会从上一层中引用出来，若上层都没有，就会报错。
SELECT *
FROM frmuser a(NOLOCK)
WHERE a.Account NOT IN( SELECT account –
FROM mdDepartment a1(NOLOCK)
)
-account在mdDepartment中不存在，会引用上层frmUser的account,故语句不会报错，为避免这种情况，最好在字段前加别名。

如查找用户名称仅由数字、字母组成的集合。
定义集合A
每一位都是字母、数字
对应：若元素有一位不是字母、数字则它就不属于A,对每一位上的限定用 NOT LIKE ‘%[限定范围]%’
SELECT * FROM frmUser b(NOLOCK)
WHERE b.WorkNo NOT IN(
SELECT a.WorkNo
FROM frmuser a(NOLOCK)
WHERE a.WorkNo NOT LIKE ‘%[0-9,a-z]%’)

Distinct不仅可用在SELECT中，也可用在COUNT内
如：查询出所有在一线、二线部门都出过单的客户
方法：按客户ID分组，对成单的部门进行DISTINCT,次数大于2的即是
SELECT a.CustomerID
FROM bdOrder a(NOLOCK)
JOIN frmuser f ON a.SalesStaff = f.Account
JOIN mdDepartment md ON f.DepartmentId = md.ID
WHERE md.DepartType IN('first','second')
AND a.OrderDate >='2011-01-01'
GROUP BY a.CustomerID
HAVING COUNT(DISTINCT md.DepartType) >=2

存钱罐，平时存放5毛，1毛，1块的零钱，现在找出所有面值为5毛的。
方案1：存钱罐只有一个，所有零钱都在其中，在里面翻找全部零钱，找出所有5毛的。对应全表扫描。
方案2：将面值分类，存放到不同的存钱罐中，找时直接拿出即可。只是要增加额外的存钱罐，并且在放零钱时要注意放到对应的存钱罐中。对应数据库索引的存储空间，及在插入更新时要对索引进行操作，不过换来的是查询速度的提升。

当存钱量上升到多倍时，找5毛的硬币所费时间并没有增长多少，所花费的时间在于取出数量。即：数据量增加，查找时间并不会增长多少，只是增加了I/O时间。

重启SQL Server服务时，最好使用配置管理器，它会自动进行一些额外的配置，如在Windows注册表中设置权限使新的账号能够读取SQL Server的设定。使用配置管理器来修改密码会立即生效，无需重启服务。

SQL Server体系结构由四大组件构成：协议、关系引擎（查询处理器）、存储引擎、SQLOS.
协议（秘书）：进行翻译与传达工作，它将接收到的请求转换成关系引擎能够识别的形式。并将关系引擎处理的最终结果转换成客户端能够理解的形式返回到客户端。
关系引擎（管理者）：接受SQL批处理，以及决定如何处理，对SQL进行解析、编译及优化、执行，若需要数据，它会发送一个请求到存储引擎。
存储引擎（文件柜）：负责管理所有的数据访问，包括基于事务的命令和大批量操作。
SQLOS：负责各层之间的沟通、例如线程调度、死锁检测等。

协议：在SQL Server的配置管理器中可以看到。
1，共享内存（本机访问）：客户端通过该协议连接到本地计算机上的SQL Server运行实例
2，命名管着（局域网访问）：为局域网而开发的协议。应用于局域网内因为它要求客户端必须具有访问服务器资源的权限。
3，TCP/IP（网络访问）：在网络之间连接，可以用来在不同的硬件体系结构和操作系统的计算机网络之间进行通讯。
4，虚拟接口适配器（VIA）:它是一种与VIA硬件一起使用的专门化的协议。
通常，TCP/IP 在慢速 LAN、WAN 或拨号网络中效果较好。而当网络速度不成问题时 Named Pipes 则是更好的选择，因为其功能更强、更易于使用并具有更多的配置选项。
经测试：只开TCP/IP,本机、局域网都可访问
只开Named Pipes:本机可访问，局域网不行，局域网要有访问本机的权限，设置后才可访问
只开共享内存：都不能访问
重要性：TCP/IP > Named Pipes > 共享内存

1，SQL SERVER 存储数据的最小单位是页，大小为8KB,它可以包含表或索引数据、分配视图、可用空间信息等。
1）它是SQL SERVER可以读写的最小I/O单位，所以即使访问一行，SQL SERVER也要把整个页加载到缓存，再从缓存中读取数据。
2）页的大小是8KB，即8192字节，故行的大小不会超过8192字节，最大值是8060字节，因为8192-96（标头信息）- 2（页尾维护的行指针）- 34（保留字节） = 8060
3）在2005之前的版本中行不能跨多页，而2005之后是可以的，当行大小超过8060字节后，这些类型的值将被移动到一个称为行溢出分配单元的页中，而在原始页上保留一个24字节的指针，指向行外的数据。

2，8个物理上连续的页组成的单元称为区。

3，表没有索引时组织为堆，有时组织为B树，堆是无序的，给定一个值，在对于节点处不知是在此节点的左子节点还是右子节点，而B树是有序的，给定一个值是可以知道是向左走还是右走。
概念：堆是一种特殊的二叉树，每个接点的关键值都大（或小于）它的左右孩子接点，一般指最大堆或最小堆
具备以下两种性质
1）每个节点的值都大于（或者都小于，称为最小堆）其子节点的值
2）除最后一层外每一层都是填满的，并且最后一层的树叶都在最左边

B树 ：二叉树，每个结点只存储一个关键字，等于则命中，小于走左结点，大于走右结点；
二者区别：在于子结点上，B树是子结点比父结点小，右结点比父结点大，而堆没有这种性质。

表设计中增加列，在列的计算所得列的规范中录入公式即可
持久代表值是否实际存储起来还是每次查询时重新生成
如：ALTER TABLE dbo.tmp ADD cs_Pname AS CHECKSUM(c1);
为列计算校验值，然后在此列上加上索引，则比一般的查询速度要快
应用：1,字符串比较没有数字比较来得快，故可增加一列存字符串的校验值，在此值上加索引进行比较，速度将提升很多

2,SELECT CHECKSUM(NEWID()) 返回随机数1到n： abs(CHECKSUM(NEWID()))%n + 1

3,CHECKSUM(NEWID()) 返回8-10位数字，故要求11位以上的随机不重复数，可采用：
SELECT right(right(abs(CHECKSUM(NEWID())),3) + CONVERT(varchar(50),abs(CHECKSUM(NEWID()))),11)

4,随机时间：
SELECT DATEADD(mi,ABS(CHECKSUM(NEWID())%(1+DATEDIFF(mi,’2011-05-14 13:00′,’2011-05-14 14:00′))),’2011-05-14 13:00′)

5,比较两行多个列是否相同
SELECT CHECKSUM(1,2,4)
SELECT DISTINCT OBJECT_ID,NAME,TYPE
FROM sys.tables tb
WHERE CHECKSUM(OBJECT_ID,NAME,type)
IN(
SELECT CHECKSUM(OBJECT_ID,NAME,type)
FROM (
SELECT OBJECT_ID,NAME,TYPE FROM sys.objects o
)obj
)
注： checksum不区分大小写，若要区分，要用BINARY_CHECKSUM

32位机能够寻址为：2^32次方 = 4G,而Windows系统本身会将2GB的地址空间留做已用。故所有应用程序最大内存空间为2G,SQL SERVER若想超过2G，需要开启AWE!
/3g开关，只是将用户可用物理内存调整为3g核心系统内存调整为1个g，提高用户程序的执行性能
/pae开关，只是用于在大于4g的物理内存时 识别出更多内存使用
awe 是帮助应用程序能寻址大于4g的空间
pae是开awe的前提。 pae是整体上开到4G以上，awe是让sql server可以大于4g以上，只开awe，因整体不允许超过4G,故开AWE也要开PAE

通过SQL Server?>Tools?>SQL Server Profiler启动
注：激活SQL Profiler跟踪约损耗1/7的性能，所以要谨慎使用。
1，通用页：跟踪的记录有两种保存方式:保存到文件和保存到表。通常选择保存到文件，不要把跟踪直接写入到表，这样会严重影响性能，把文件写到与数据库位置不同的磁盘是最快的方案。
2，模块选择：
Standard：创建跟踪的通用起点。捕获所运行的全部存储过程和 Transact-SQL 批处理。用于监视常规数据库服务器活动。
TSQL：捕获客户端提交给 SQL Server 的所有 Transact-SQL 语句及其发出时间。用于调试客户端应用程序。
TSQL_Duration：捕获客户端提交给 SQL Server 的所有 Transact-SQL 语句及其执行时间（以毫秒位单位），并按持续时间对其进行分组。用于识别执行速度慢的查询。
TSQL_Grouped：捕获提交给 SQL Server 的所有 Transact-SQL 语句及其发出时间。信息按提交语句的用户或客户端分组。用于调查某客户端或用户发出的查询。
TSQL_Locks：捕获客户端与异常锁事件一起提交到 SQL Server 的 Transact-SQL 语句。用于排除死锁、锁超时和锁升级事件的故障。
TSQL_Replay：捕获重播跟踪所需的 Transact-SQL 语句的详细信息。用于执行迭代优化，例如基准测试。

3，事件选择：若多是存储过程，则跟踪SP:Completed事件，若存储过程有很多操作，查看明细要跟踪SP:StmtCompleted.
若程序多是sql语句，则跟踪SQL:BatchCompleted与 SQL:StmtCompleted事件
在事件选择版块上点击列名，可以进行筛选，如指定数据库筛选：DataBaseName 类似于brm_lvjian_new.

4,1） 文件导出脚本，包装成一个存储过程 ，在此存储过程中 print @traceid
   2）执行此存储过程，根据打印出的＠traceid来控制跟踪的执行与停止，之所以不用profiler的GUI界面，第一是profiler不便于分析，并且需要将数据写入到GUI，性能也不高。运行：EXEC [WorkTraceStart] 'brm_lvjian_new',@tracefile='d:\mytrace'
   3）控制跟踪：执行： EXEC sp_trace_setstatus @traceid, [0|1|2]    0: 停止 1：启动 2：删除
       注：在执行2）的存储过程后，里面有一个 EXEC sp_trace_setstatus @traceid, 1; 将启动跟踪。
       查看跟踪：sys.traces

4,分析保存的跟踪文件
1）导入
select CAST(textdata as nvarchar(max)) as tsql_code,duration
into Workload
from sys.fn_trace_gettable('C:\test\performancetrace_20100802.trc',NULL) as TT

2）分析
select convert(varchar(60),tsql_code),sum(duration) s,avg(duration) t from workload
where duration >=3000  --一般是执行时间在3秒以上的
group by convert(varchar(60),tsql_code)
order by s desc

5, 查询事件由哪些情形引发：
如查询执行计划重新编译由哪些引发：
SELECT e.name,v.subclass_name,v.subclass_value,tc.name,tc.[type_name]
FROM sys.trace_events e
JOIN sys.trace_subclass_values v ON e.trace_event_id = v.trace_event_id
JOIN sys.trace_columns tc ON v.trace_column_id = tc.trace_column_id
WHERE e.name = 'sp:recompile' --AND v.subclass_value < 100

6,使用：在图形界面上修改表的属性与通过tsql修改表的属性，如增加表字段长度，通过Profiler观察发现：
通过图形方式：是先转移全部数据到临时表，再删除表，然后重命名临时表为原来表名称，速度比较慢，而通过tsql发现只执行一条sql语句即可，故优先使用tsql

 1,取出所有日期中订单最大金额的前三名
  SELECT * FROM (
    SELECT CONVERT(VARCHAR(10),bo.OrderDate,120) date1, orderno,amount,
    ROW_NUMBER() OVER(ORDER BY bo.amount desc) AS numbers
    FROM bdOrder bo(NOLOCK)
    WHERE bo.OrderDate >='2011-03-19'
)m
WHERE m.numbers <=3
2，若想取出每日的前三名，就需要对数据进行按日期分组，不能用GROUP BY ,因为OVER中有amount,必须要在GROUP BY 中加上amount,也有失去了分组的意向。
    方法：在行级分组,利用强大的OVER,在行级对日期进行分组
    SELECT * FROM (
           SELECT CONVERT(VARCHAR(10),bo.OrderDate,120) date1, orderno,amount,
           ROW_NUMBER() OVER(PARTITION BY CONVERT(VARCHAR(10),bo.OrderDate,120) ORDER BY bo.amount desc) AS numbers
           FROM bdOrder bo(NOLOCK)
           WHERE bo.OrderDate >='2011-03-19'
    )m
     WHERE m.numbers <=3

 SQL中的多个运算是同时进行计算的，即同时操作，all-at-once operation，与在select列表中的前后位置无关
如： update a
        set a.value1 = a.value2, a.value2 = a.value1  --同时发生，即时交换
        from tmpTable
由上知：1）select中的别名不能在同一select列表中使用。 如select max(id) mid, mid+1 from tmpTable 是错误的
             2）UPDATE t
                  SET t.c4 = convert(varchar(20),GETDATE(),120)
                  FROM tmp2 t
                  因执行是同时进行的，故t.c4得到的日期是相同的
             3）update a set a.value1 = 100 + (select max(c1) from tmpTable)    因执行是同时的，故最大值c1不会改变

4）应用：得到某一值，并将此值加1
                  update a set @value = value, value = value+1 from tmpTable