享受代码

 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

1,自右向左，自上而上
2，箭头的粗细表示每个图标之间移动的数据行数量以及数据行大小移动所需的相对本。
Table Scan：表扫描，扫描每一行。，就说明数据表上没有聚集索引
Index Seek(索引查找)：索引查找意味着查询优化器使用了数据表上的非聚集索引来查找数据
Clustered Index Seek(聚集索引查找)：这指查询优化器使用了数据表上的聚集索引来查找数据，性能很快。实际上，这是SQL Server能做的最快的索引查找类型
Clustered Index Scan(聚集索引扫描)：在有聚集索引的表上执行扫描，不是聚集查找的原因是WHERE条件限制太少，没用到聚集索引字段。聚集索引扫描与表扫描相似，不同的是聚集索引扫描是在一个建有聚集索引的数据表上执行的。与标准的表扫描不同，聚集索引扫描并不会总是去查找数据表中的所有数据，所以聚集索引扫描一般都会比标准的表扫描要快。通常来说，要将聚集索引扫描改成聚集索引查找，你唯一能做的是重写查询语句，让语句限制性更多，从而返回更少的数据行

避免出现：表扫描，估计I/O开销与估计运算符开销越小越好
估计I/O开销：
用于执行操作的所有 I/O 活动的估计开销。此值应尽可能低。
估计运算符开销：
用于执行此操作的查询优化器的开销。此操作的开销以占查询总开销的百分比的形式显示在括号中。由于查询引擎选择最高效的操作来执行查询或执行语句，因此此值应尽可能低。

EXCEP:返回左结果集不在右结果集中的记录
INTERSEC:返回两个结果集相同的记录

SELECT * FROM (
SELECT 1 id,1 name1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3)m
EXCEPT
SELECT * FROM (
SELECT 1 id,1 name1 UNION ALL SELECT 2,2 UNION ALL SELECT 4,4 )n

SELECT EncryptByPassPhrase(
‘My Password Used To Encrypt This String in 2008.’,
‘hello,word!’)
解密：
SELECT CAST(DecryptByPassPhrase(
‘My Password Used To Encrypt This String in 2008.’,
MySecret) as varchar(max))

 1，FROM阶段
   1)从FROM执行，对于JOIN，两表进行交叉连接，求笛卡尔积得到一个结果集。
   2)根据ON条件，对结果集不满足条件的记录去除
   3)若有外连接，如LEFT,RIGHT等，再将满足条件的加入到结果集中
   4)执行APPLY,PIVOT,UNPIVOT等表达式
2，WHERE阶段：对于1步骤得到的虚表，将where条件为true的保留下来
3，GROUP BY 阶段 对虚拟表进行分组
4，HAVING阶段，对分组后的虚拟表进行筛选
5，SELECT/UPDATE/DELETE阶段，投影取得列数据,若有TOP,则会根据ORDER BY 子句来选择指定数量的行
6，ORDER BY 阶段 对上述结果进行排序，返回游标

由上知：1，GROUP BY 是HAVING的基础，需要先执行GROUP BY 再执行HAVING
        2, WHERE比SELECT先执行，故在SELECT中指定的虚列在WHERE条件中不可用，而SELECT比ORDER BY 先执行，故在SELECT中指定的虚列在ORDER BY条件中可以使用
     SELECT f.UserName,SUM(amount) amo --amo:虚列，指在原有表中不存在的列
     FROM bdOrder
     ORDER BY amo
       3，若使用ORDER BY，则返回的是一个游标，不能用在FROM后面，因为FROM后面跟的是结果集
        SELECT * FROM ( SELECT * FROM frmuser ORDER BY Account )f 这样是错误的
       例外：可通过加TOP解决
    SELECT * FROM ( SELECT TOP * FROM frmuser ORDER BY Account )f
4：当Select未使用Distinct时，Order By可使用Select之前的字段，即：即使在Select中不存在，但在表中存在的字段，Order By也可引用当Select使用Distinct时，Order By只能使用Select中的字段

问题： 取出公司销售员工最近的订单编号及订单日期
 老办法，多次取值。实际是重复查询，查询一次返回一个值，不能做到查询一次得到所有结果
   SELECT a.UserName,
             (SELECT TOP 1 OrderNo
                FROM bdorder b
                WHERE b.SalesStaff = a.account
                  AND b.OrderDate >='2011-02-01'
                ORDER BY b.OrderNo DESC
              ) OrderNo,
             
                (SELECT TOP 1 OrderDate
                 FROM bdorder b
                 WHERE b.SalesStaff = a.account
                   AND b.OrderDate >='2011-02-01'
                 ORDER BY b.OrderNo DESC
                 )OrderDate 
                
 FROM frmuser a

新方法：查询一次返回一个结果集
SELECT a.UserName,b.OrderNo,b.orderdate
FROM frmuser a(NOLOCK)
  OUTER  APPLY(
      SELECT TOP 1 OrderNo,OrderDate
      FROM bdorder b
      WHERE b.SalesStaff = a.account
      AND b.OrderDate >='2011-02-01'
      ORDER BY b.OrderDate DESC
    )b
OUTER APPLY：相当于表中的LEFT JOIN
CROSS APPLY：相当于表中的JOIN 

为使用2005提供的新功能如行列转换pivot将将数据库兼容性由80提升到90，
sp_dbcmptlevel @dbname=’brm_lvjian’, @new_cmptlevel=90
报错：如果指定了 SELECT DISTINCT，那么 ORDER BY 子句中的项就必须出现在选择列表中。
语句为：
SELECT distinct a.orderno,
CONVERT(varchar(10), OrderDate, 20) AS OrderDate
FROM bdorder a
WHERE a.OrderDate >=’2011-02-01′
ORDER BY a.OrderDate
原因在80（sqlserver2000） 中对于ORDER BY 将忽略列前缀。故上面的ORDER BY a.OrderDate执行的是ORDER BY OrderDate
而在90（2005）中是不忽略列前缀的，上面的ORDER BY a.OrderDate因在select中不存在，故报错！
注：查看数据库兼容级别：数据库 – 属性 – 选项

1，分区作用：将数据分放到多个物理磁盘上，利用并行，多个磁盘同时吞吐以提升IO能力，同时每个分区数据量小，也减少了数据的扫描，提升了命中速度。实际上表逻辑上并未拆分，只是分散存储于不同的物理文件上，相当于把一张表大数据无限极细化到多张表上，多个驱动上，但是访问时却还是一样的访问，因为本身并未新建任何表。
注：即使放到一块磁盘上，因每个分区数据量小，扫描范围减少，也能提高速度。
表分区方法：1，按时间分区：好处，查询速度快。坏处：因当前数据一直写到一块分区上，故写不快。
                       2，HASH分区：数据均匀的分散到各分区，好处：因当前数据写到不同分区，故写快。但在查询方面，要联合多个分区，故查询不快。
注：分区分为硬件与软件分区，此页是软件分区。硬件分区指用磁盘阵列的方式分区，硬件负责将数据将到不同磁盘上，查询与写入速度都会提高。
操作：指定数据要分区的位置-创建文件组及对应文件， 数据以何种规则适移到分区上-创建分区函数， 进行数据迁移。

操作：指定数据要分区的位置-创建文件组及对应文件， 数据以何种规则适移到分区上-创建分区函数， 进行数据迁移。
步骤：
/*1,指定数据存放位置:增加文件组,并指定文件存放位置*/
ALTER DATABASE BRM_LVJIAN
ADD FILEGROUP [gf_bdorder_50]
ALTER DATABASE BRM_LVJIAN
ADD FILEGROUP [gf_bdorder_100]
ALTER DATABASE BRM_LVJIAN
ADD FILEGROUP [gf_bdorder_150]

ALTER DATABASE BRM_LVJIAN
ADD FILE ( NAME = N'gf_bdorder_50',
FILENAME = N'g:\Mssql_Filegroup\bdorder_50.ndf' , SIZE = 300MB , FILEGROWTH = 10% )
TO FILEGROUP [gf_bdorder_50]

ALTER DATABASE BRM_LVJIAN
ADD FILE ( NAME = N'gf_bdorder_100',
FILENAME = N'I:\Mssql_Filegroup\bdorder_100.ndf' , SIZE = 300MB , FILEGROWTH = 10% )
TO FILEGROUP [gf_bdorder_100]

ALTER DATABASE BRM_LVJIAN
ADD FILE ( NAME = N'gf_bdorder_150',
FILENAME = N'I:\Mssql_Filegroup\bdorder_150.ndf' , SIZE = 300MB , FILEGROWTH = 10% )
TO FILEGROUP [gf_bdorder_150]

/*2,指定数据如何存放:创建分区函数*/

--LEFT,RIGHT指定"="是在最左边还是右边
--LEFT: <=1, 1<10<=20, >20
--RIGHT: <1,1<=10<20, >=20
CREATE PARTITION FUNCTION [partitionFunc_bdOrder] (int)
AS RANGE LEFT FOR VALUES ('500000','1000000')

select max(id) from bdorder

/*3,数据的迁移,应用分区位置与函数,为了方便,将二者合二唯一,提出分区架构的概念*/
CREATE PARTITION SCHEME [partitionScheme_bdOrder]
AS PARTITION [partitionFunc_bdOrder]
TO ([PRIMARY],gf_bdorder_50,gf_bdorder_100,gf_bdorder_150)

--数据迁移到分区
ALTER TABLE dbo.bdOrder DROP CONSTRAINT PK_BDOrder
ALTER TABLE  dbo.bdOrder add CONSTRAINT PK_BDOrder PRIMARY KEY CLUSTERED (ID)
ON [partitionScheme_bdOrder](ID)

--恢复成原来的默认状态，因原来的分区架构是<span style="color: #ff0000;">PRIMARY</span>,故修改表即可，如下：
ALTER TABLE dbo.bdorder DROP CONSTRAINT PK_BDOrder
ALTER TABLE  dbo.bdorder add CONSTRAINT PK_BDOrder PRIMARY KEY CLUSTERED (ID)
ON [PRIMARY]

/*查询*/
--分区
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('dbo.bdOrder')

--数据所在分区
SELECT  $PARTITION.[partitionFunc_bdOrder](1000000)

SELECT top 20 id,orderno,orderdate,$PARTITION.[partitionFunc_bdOrder](id)  FROM bdOrder
ORDER BY newid()

--数据分布

SELECT $PARTITION.partitionFunc_bdOrder(id) AS Partition_num,
MIN(Id) AS Min_value,MAX(Id) AS Max_value,COUNT(1) AS Record_num
FROM dbo.[bdorder]
GROUP BY $PARTITION.partitionFunc_bdOrder(id)
ORDER BY $PARTITION.partitionFunc_bdOrder(id)

select * from
sys.partition_range_values

/*若数据量增大，需要增加分区，扩大范围，操作如下*/
操作方法：增加分区，增加范围

ALTER DATABASE [D] ADD FILEGROUP [GF2]
ALTER DATABASE [WSBOOK] ADD FILE ( NAME = N'GF2', FILENAME = N'E:\E\E1\DGF2.ndf' , SIZE = 5MB , FILEGROWTH = 10% )
TO FILEGROUP [GF2]

为架构添加范围
ALTER PARTITION SCHEME [D_PARTITION_SHEME]
NEXT USED GF2
这句话就是让下一个分区使用和现在已经存在的分区GF2分区中，

2.添加一个范围
ALTER PARTITION FUNCTION [D_PARTITIONFUNC]()
SPLIT RANGE ('700000')

可以合并一个范围，其实就是<span style="color: #ff0000;">删除分区</span>，如MERGE RANGE(要删除的原分区界点)

ALTER PARTITION FUNCTION [D_PARTITIONFUNC]() MERGE RANGE ('400000')
3.查询分区分布

SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('dbo.B')

4.删除分区
DROP PARTITION SCHEME [D_PARTITION_SHEME]
DROP PARTITION FUNCTION [D_PARTITIONFUNC]