享受代码

制约查询速度的有两方面，一是找到，二是取出，分别对应的是扫描数据与IO吞吐，扫描数据的提升通过建立合适的索引解决，而提升IO可以考虑将大表进行分区，分到多个物理磁盘上，通过并行方式提升IO能力。

一个新的SQL Server 2005安装总是包括四个数据库：master、model、tempdb和msdb。还包含第五个“隐藏的”数据库Resource

1，model 数据库是 Microsoft SQL Server 创建其他数据库（包括 tempdb 数据库和用户数据库）时使用的模板。创建数据库时，model 数据库的全部内容（包括数据库选项）都会被复制到新数据库中。

2，tempdb 系统数据库是连接到 SQL Server 实例的所有用户都可用的全局资源，它保存所有临时表和临时存储过程。

3，master:账号、服务器信息，在 SQL Server 2005 中，系统对象不再存储在 master 数据库中，而是存储在 Resource 数据库中

4，Resource:Resource数据库的物理文件名为 Mssqlsystemresource.mdf,默认情况下，此文件位于 x:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Mssqlsystemresource.mdf。
SQL Server 系统对象（例如 sys.objects）在物理上持续存在于 Resource 数据库中，但在逻辑上，它们出现在每个数据库的 sys 架构中。Resource 数据库不包含用户数据或用户元数据。

SQL Server 不能备份 Resource 数据库。Resource 数据库依赖于 master 数据库的位置。如果移动了 master 数据库，则必须也将 Resource 数据库移动到相同的位置。

5，msdb，包括计划信息、备份与还原历史记录信息、邮件信息
backupmediafamily --备份文件地址
backupset         --备份操作历史
restorefile		  --还原文件地址                  --
restorehistory    --还原操作历史
--备份详细
SELECT b.database_name,b.backup_start_date,b.backup_finish_date,b2.physical_device_name
FROM backupset b
JOIN backupmediafamily b2 ON b.media_set_id = b2.media_set_id
ORDER BY b.backup_start_date DESC

sysjobs --job
sysjobhistory --job执行历史

SELECT * FROM sysjobs
SELECT * FROM sysjobschedules
SELECT * FROM sysjobsteps
--job详细
SELECT a.job_id,a.name,s.next_run_date, s.next_run_time,
s2.step_name,s2.command
FROM sysjobs a
JOIN sysjobschedules s ON a.job_id = s.job_id
JOIN sysjobsteps s2 ON a.job_id = s2.job_id

--job状态
--取出在执行的job
 exec msdb.dbo.sp_help_job @Category_Name = N'REPL-Merge',@execution_status = 1
execution_status:
1 正在执行。
2 正在等待线程。
3 在两次重试之间。
4 空闲。
5 挂起。
7 正在执行完成操作
--不过，若想将存储过程执行的结果放到一临时表中，上述存储过程将报嵌套错误，此时，应用master.dbo.xp_sqlagent_enum_jobs
exec master.dbo.xp_sqlagent_enum_jobs 1,hello  
/*p_sqlagent_enum_jobs <is sysadmin (0 or 1)>,
                        <job owner name>
                       [, <job id>]

    The first parameter identifies whether you want to return information about all jobs on the server, 
    or just jobs owned by a particular job owner. If you specify "0" for this first parameter, 
    it means you want to return job information for a particular job owner. If you specify a "1," 
    it means you want information for all jobs. The second parameter identifies the job owner. 
    This parameter is required on all calls to this XP but is only used when you specify "0" for the 
    first parameter. The third and last parameter only needs to be provided if you want to return 
    information about a particular job_id. 
*/
参考：http://www.databasejournal.com/features/mssql/article.php/10894_3491201_2/Detecting-The-State-of-a-SQL-Server-Agent-Job.htm

--开关job
msdb.dbo.sp_stop_job @job_id = 'adfsdfs-sdfsdf'
msdb.dbo.sp_start_job @job_id ='adfsdfs-sdfsdf'

sysmail_mailitems --邮件明细

DECLARE @TempTable table(UserID int , UserName nvarchar(50));
insert into @TempTable (UserID,UserName) values (1,'a')
insert into @TempTable (UserID,UserName) values (1,'b')
insert into @TempTable (UserID,UserName) values (1,'c')
insert into @TempTable (UserID,UserName) values (2,'d')
insert into @TempTable (UserID,UserName) values (2,'e')
insert into @TempTable (UserID,UserName) values (2,'f')

--以XML节点式一行存数据，节点名即是列名
select UserID,UserName from @TempTable FOR XML PATH

--PATH中的内容指定根结点名称，若无，则默认为root
select UserID,UserName from @TempTable FOR XML PATH('lzy')

--指定顶级根结点名称
select UserID,UserName from @TempTable FOR XML PATH('lzy'), root ('Root')

--若结果集中无列名，那么节点名也就没有，数据就显示到了一行
select convert(varchar(50),UserName) +','? from @TempTable s WHERE s.UserID = 1

--stuff用来去掉多余的逗号
SELECT DISTINCT t.UserID,
STUFF((select ',' + convert(varchar(50),UserName)? from @TempTable s WHERE s.UserID = t.UserID FOR XML PATH('')),1,1,'')
FROM @TempTable t

 为结果集构造行号，实际上是为结果集指定顺序，要排序的话就要知道排序规则：Order By
如：SELECT ROW_NUMBER() OVER(ORDER BY id DESC)AS rownum,
       departname  FROM mdDepartment md
应用：mysql的limit即可使用：
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY id DESC)AS rownum,departname
FROM mdDepartment md
) AS a
WHERE a.rownum>=3 AND a.rownum<=5      --返回3到5行

CTE: Common Table Express 指定临时命名的结果集

1, 当sql语句比较大时，可以用CTE将小结果集拆分，以便阅读
    WITH mycre(id,name) AS ( select id ,name from mddepartment )
    SELECT * FROM frmuser JOIN mycre ON frmuser.DepartmentID= mycre.id
 注：1）CTE 之后必须直接使用，间隔sql语句就会失效
        2）在用with时并不会执行查询，只是凑sql语句，只有当with后面的select *引用cte时才会执行

2，递归使用，依次引用自身。递归 CTE 定义至少必须包含两个 CTE 查询定义（一个定位点成员和一个递归成员）
 WITH departments(id,Departname,depth) as(
    SELECT id,Departname,1 depth FROM mdDepartment md
    WHERE md.ParentID = 2   --找到广告商务部的直接子部门 定位点成员，depth指定深度
    UNION ALL
    SELECT md.id,md.Departname,depth+1 FROM mdDepartment md --找到子部门的子部门 递归成员
    JOIN departments d ON md.ParentID = d.id
   
)
SELECT * FROM departments
    注：1）定位点成员之间必须使用UNION ALL、UNION、INTERSECT、EXCEPT集合运算符，最后一个定位点成员与递归成员之间必须使用UNION ALL，递归成员之间也必须使用UNION ALL连接，定位点成员和递归成员中的字段数量和类型必须完全一致，递归成员的FROM子句只能引用一次CTE对象。
           2）递归成员中不允许出现下列项
　　            SELECT DISTINCT
　　            GROUP BY
　　            HAVING
　　            标量聚合
　　            TOP
　　            LEFT、RIGHT、OUTER JOIN(允许出现 INNER JOIN)
　　            子查询