sql server 2005-SQL Server 2005 五个动态管理对象

发布时间：2017-11-06 所属栏目：语言

一 : SQL Server 2005 五个动态管理对象

SQL Server 2005的新功能为动态管理对象，它们是在指定时间返回某个数据库实例的特殊状态信息的数据库视图或函数。这些对象允许数据库管理员或开发者对数据库服务器进行高效监控。动态管理对象取代了SQL Server以前版本中的许多系统表格，但也为数据库管理员提供更多观测手段。

服务器上需要监控的内容，几乎都有对应的动态管理对象。既然有这么多动态管理对象，在这篇文章中逐一进行讨论是不实际的。例如，共有12类动态管理对象，但这里我主要讨论我认为相当重要的五个对象，它们分别来自与SQL Server操作系统、目录和执行有关的类中。

这些对象被分为两类：数据库级别和服务器级别。SQL Server 2005的安全构架有些不同，所以你必须采取一些措施保证你能够使用本文讨论的视图和函数。你必须保证使用这些例子的用户能够查看对象的SERVER STATE和DATABASE STATE。可以应用GRANT语句来完成这一点。

在我开始介绍动态管理对象之前，你应该建立一个可用在所有例子里面的表格。列表A是建立这个表格的代码。

SQL Server操作系统相关动态管理对象

sys.dm_os_performance_counters：这个视图返回的信息列举与SQL Server直接相关的性能统计。返回的信息代表通过Windows性能监控器显示的外部性能计数器。在SQL Server 2000中，这些数据以一个叫做sysperfinfo的系统表格来表示，SQL Server 2005中也有这个表格，但仅用于向后兼容。将来的版本可能会删除这个表格。

要了解如何应用这个视图，而不是运行Windows性能监控器来获取系统信息，请运行列表B中的查询代码。这个查询将返回服务器当前时刻的缓存命中率（Buffer Cache Hit Ratio）。缓存命中率指在内存中所发现的SQL Server请求页面的百分比。如果服务器运转正常，这个值一般在90%以上。如果这个值低于90%，则意味着服务器将会去硬盘那里恢复数据页面，这可能是服务器需要更多内存的信号。

如果你为了同样的目的，准备运行上面的查询，并将得到的结果与运行Windows性能监控器显示的结果进行比较（SQL Server：缓冲器管理器缓存命中率），你会发现这些数据几乎是一样的。稍稍研究一下，就可以根据这个表格开发出你自己的脚本库，而不必总是运行Windows性能监控器。

Caveat：这个视图仅限于SQL Server相关的计数器使用。如果你希望获得SQL Server之外的数据，你还是要使用Windows性能监控器。

目录相关动态管理视图和函数

sys.dm_db_index_physical_stats：这个动态管理函数为数据表格和视图返回相关数据及目录信息。查看列表C。这个函数接受5个参数：数据库的ID、所讨论的表格或视图的ID、表格或视图上的一个特定目录的ID（被我忽略）、分区号（也被我忽略）和一个模式，我将它指定为“DETAILED”。将这些参数提交给这个函数会允许你查看DMVTest表格中的所有目录细节。

在前面定义的表格中运行列表C中的查询，你会发现表格中既没有页面也没有记录。在DMVTest中增加一条记录，再运行查询，就会发现表格中增加了一个页面。

INSERT INTO DMVTest(C2, C3)
VALUES(1,1)

这个函数返回的另一个特别有趣的域为avg_page_space_used_in_percent域，它说明数据页面有多大。从查询中发现数据页面约为0.25%。你可以应用这个数据来开发程序，查看数据库表格，并根据程序中设定的标准，重新为它们编写目录。

执行相关的动态管理视图和函数

sys.dm_exec_requests：这个视图显示发生在SQL Server实例中的每个请求的有关信息。当你研究服务器阻塞时，由此视图提供的信息特别有用。读、写、会话设置和 blocking_session_id是这个视图返回的一些有用数据。blocking_session_id栏指出系统上阻塞数据库请求的会话。稍后，我将举出一个例子，说明如何确定用户运行的阻塞请求的语句。

sys.dm_exec_sessions：SQL Server上每个得到验证的会话由它返回一行。在你希望找出连接到数据库服务器的所有用户时，可用到这个视图。它返回session_id、连接通过它建立会话执行的读和写的程序、以及其它一些会话设置。这个视图还包含一个叫做is_user_process的BIT域，在查询这个表格时你可能会用到它。数值1表示会话为用户会话而非系统定义的会话。

sys.dm_exec_sql_text：这是一个根据sql操作返回SQL Server语句的动态管理函数。对某些动态管理函数而言，SQL Server 2005将正在执行的sql语句以散列值存储。这个散列值可由sys.dm_exec_sql_text动态管理函数解析。但是，任何时候你希望将一个函数与另一个对象连接起来，并从表格或视图中提交一个域值时，就会需要用到新的APPLY运算符。SQL Server 2005以前的版本并不具备这一功能。（欲了解SQL Server 2005中APPLY运算符的详细信息，请参阅这篇文章。）

现在我们讨论一下如何应用这三个动态管理函数。列表D中的查询列举出数据库中所有当前用户请求执行的会话、阻塞会话、程序、主机和SQL Server语句。

注意正在使用dm_exec_sql_text函数，并从 sys.dm_exec_requests视图中提交sql_handle域。由sys.dm_exec_requests和 sys.dm_exec_sessions视图返回的每一行，都将调用sys.dm_exec_sql_text函数。

测试

对它们进行测试是学习这些新型动态管理对象的最佳方法。SQL Server在线书籍提供了使用这些对象的一些资料，但动手使用要比阅读学到更多内容。投入一点努力，你就会发现许多应用对象的新方法，不仅包括这个例子中列出的对象，还包括SQL Server 2005中的动态管理对象。

二 : SQL Server 2005 standard Edition各版本功能

SQL Server 2005 是一个全面的数据库平台，使用集成的商业智能 (BI) 工具提供了企业级的数据管理。[www.61k.com］SQL Server 2005 数据库引擎为关系型数据和结构化数据提供了更安全可靠的存储功能，使您可以构建和管理用于业务的高可用和高性能的数据应用程序。

SQL Server 2005 数据引擎是本企业数据管理解决方案的核心。此外 SQL Server 2005 结合了分析、报表、集成和通知功能。这使您的企业可以构建和部署经济有效的 BI 解决方案，帮助您的团队通过记分卡、Dashboard、Web services 和移动设备将数据应用推向业务的各个领域。

与 Microsoft Visual Studio、Microsoft Office System 以及新的开发工具包（包括 Business Intelligence Development Studio）的紧密集成使 SQL Server 2005 与众不同。无论您是开发人员、数据库管理员、信息工作者还是决策者，SQL Server 2005 都可以为您提供创新的解决方案，帮助您从数据中更多地获益。

SQL Server 2005 Enterprise Edition（32 位和 64 位）
Enterprise Edition 达到了支持超大型企业进行联机事务处理 (OLTP)、高度复杂的数据分析、数据仓库系统和网站所需的性能水平。Enterprise Edition 的全面商业智能和分析能力及其高可用性功能（如故障转移群集），使它可以处理大多数关键业务的企业工作负荷。Enterprise Edition 是最全面的 SQL Server 版本，是超大型企业的理想选择，能够满足最复杂的要求。该版本还推出了一种适用于 32 位或 64 位平台的 120 天 Evaluation Edition。

SQL Server 2005 Standard Edition（32 位和 64 位）
Standard Edition 是适合中小型企业的数据管理和分析平台。它包括电子商务、数据仓库和业务流解决方案所需的基本功能。Standard Edition 的集成商业智能和高可用性功能可以为企业提供支持其运营所需的基本功能。Standard Edition 是需要全面的数据管理和分析平台的中小型企业的理想选择。

SQL Server 2005 Workgroup Edition（仅适用于 32 位）
对于那些需要在大小和用户数量上没有限制的数据库的小型企业，Workgroup Edition 是理想的数据管理解决方案。Workgroup Edition 可以用作前端 Web 服务器，也可以用于部门或分支机构的运营。它包括 SQL Server 产品系列的核心数据库功能，并且可以轻松地升级至 Standard Edition 或 Enterprise Edition。Workgroup Edition 是理想的入门级数据库，具有可靠、功能强大且易于管理的特点。

SQL Server 2005 Developer Edition（32 位和 64 位）
Developer Edition 使开发人员可以在 SQL Server 上生成任何类型的应用程序。它包括 SQL Server 2005 Enterprise Edition 的所有功能，但有许可限制，只能用于开发和测试系统，而不能用作生产服务器。Developer Edition 是独立软件供应商 (ISV)、咨询人员、系统集成商、解决方案供应商以及创建和测试应用程序的企业开发人员的理想选择。Developer Edition 可以根据生产需要升级至 SQL Server 2005 Enterprise Edition。

SQL Server 2005 Express Edition（仅适用于 32 位）
SQL Server Express 是一个免费、易用且便于管理的数据库。SQL Server Express 与 Microsoft Visual Studio 2005 集成在一起，可以轻松开发功能丰富、存储安全、可快速部署的数据驱动应用程序。SQL Server Express 是免费的，可以再分发（受制于协议），还可以起到客户端数据库以及基本服务器数据库的作用。SQL Server Express 是低端 ISV、低端服务器用户、创建 Web 应用程序的非专业开发人员以及创建客户端应用程序的编程爱好者的理想选择

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/Tercel99/archive/2008/10/04/3015848.aspx

扩展：standard edition / standard x64 edition / standard edition one

三 : SQL Server 2005中的CONVERT（）函数

SQL Server 2005中的CONVERT（）函数

最近在做机房收费系统时发现一个很怪异的问题，就是只要设计日期或者时间的查询就会出
错，不是查不出来，而且查出的结果总是“不按套路出牌”。

各种调试代码都没有问题，最后发现是数据库中数据类型的问题，我把时间和日期的数据
类型都设置为了Char型，结果在比较的时候他们比较的ASCII码的大小，所以才会造成“不按套
路出牌”的结果。然后我就直接去数据库中修改数据类型，结果发现我用的SQL2005里面只有
一个DateTime数据类型，而没有分开的Date和Time类型。可我却需要将时间和日期分开显示，
这里就用到一个函数：CONVERT（）。

下面我们详细的说一下这个函数。

SQL Server里面给它的定义是：是把日期转换为新数据类型的通用函数，可以用不同的格式显示日期/时间数据。

语法格式：CONVERT(data_type(length),data_to_be_converted,style)

参数解释：data_type(length) 规定目标数据类型（带有可选的度）。data_to_be_converted 含有需要转换的值。style 规定日期/时间的输出格式。

其中可用的Style值有： www.2cto.com

Style ID

Style 格式

100 或者 0

mon dd yyyy hh:miAM （或者 PM）

101

mm/dd/yy

102

yy.mm.dd

103

dd/mm/yy

104

dd.mm.yy

105

dd-mm-yy

106

dd mon yy

107

Mon dd, yy

108

hh:mm:ss

109 或者 9

mon dd yyyy hh:mi:ss:mmmAM（或者 PM）

110

mm-dd-yy

111

yy/mm/dd

112

yymmdd

113 或者 13

dd mon yyyy hh:mm:ss:mmm(24h)

114

hh:mi:ss:mmm(24h)

120 或者 20

yyyy-mm-dd hh:mi:ss(24h)

121 或者 21

yyyy-mm-dd hh:mi:ss.mmm(24h)

126

yyyy-mm-ddThh:mm:ss.mmm（没有空格）

130

dd mon yyyy hh:mi:ss:mmmAM

131

dd/mm/yy hh:mi:ss:mmmAM www.2cto.com

举两个刚用的例子：一般Select getdate（）得到的时间是：2012-10-27 14:44:50.360

select convert(varchar(10), getdate(),120) 得到的是 2012-10-27

select convert(char(8),getdate(),108) 得到的是时间 14:44:50

select datepart(year,getdate()) 只有年份 2012

同时还有一些其他的时间函数，比如：datediff，Datepart ，datename等。

扩展：sql server convert / sqlserver convert / sqlserver中convert

四 : SQL SERVER 2005 中 Apply的用法

SQL Server 2005 新增 cross apply和 outer apply联接语句，增加这两个东东有啥作用呢？

我们知道有个 SQL Server 2000 中有个 cross join 是用于交叉联接的。（www.61k.com)实际上增加 cross apply 和 outer apply 是用于交叉联接表值函数（返回表结果集的函数）的，更重要的是这个函数的参数是另一个表中的字段。这个解释可能有些含混不请，请看下面的例子：

-- 1. cross join 联接两个表

select *  from TABLE_1 as T1 cross join TABLE_2 as T2

【此句实际上是求笛卡尔积】
-- 2. cross join 联接表和表值函数，表值函数的参数是个“常量”

select *  from TABLE_1 T1 cross join FN_TableValue(100)

-- 3. cross join 联接表和表值函数，表值函数的参数是“表T1中的字段”

select *  from TABLE_1 T1 cross join FN_TableValue(T1.column_a)

Error：

Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "T1.column_a" could not be bound.

最后的这个查询的语法有错误。在 cross join 时，表值函数的参数不能是表 T1 的字段，为啥不能这样做呢？我猜可能微软当时没有加这个功能：），后来有客户抱怨后，于是微软就增加了 cross apply 和 outer apply 来完善，请看 cross apply, outer apply 的例子：

-- 4. cross apply

select *  from TABLE_1 T1 cross apply FN_TableValue(T1.column_a)

-- 5. outer apply

select *  from TABLE_1 T1 outer apply FN_TableValue(T1.column_a)

cross apply 和 outer apply 对于 T1 中的每一行都和派生表（表值函数根据T1当前行数据生成的动态结果集）做了一个交叉联接。cross apply 和outer apply 的区别在于：如果根据 T1 的某行数据生成的派生表为空，cross apply 后的结果集就不包含 T1 中的这行数据，而 outer apply 仍会包含这行数据，并且派生表的所有字段值都为 NULL。

下面的例子摘自微软 SQL Server 2005 联机帮助，它很清楚的展现了 cross apply 和 outer apply 的不同之处：

-- cross apply

select *  from Departments as D cross apply fn_getsubtree(D.deptmgrid) as ST

deptid deptname deptmgrid empid empname mgrid lvl ----------- ----------- ----------- ----------- ----------- ----------- ------ 1 HR 2 2 Andrew 1 0 1 HR 2 5 Steven 2 1 1 HR 2 6 Michael 2 1 2 Marketing 7 7 Robert 3 0 2 Marketing 7 11 David 7 1 2 Marketing 7 12 Ron 7 1 2 Marketing 7 13 Dan 7 1 2 Marketing 7 14 James 11 2 3 Finance 8 8 Laura 3 0 4 R&D 9 9 Ann 3 0 5 Training 4 4 Margaret 1 0 5 Training 4 10 Ina 4 1 (12 row(s) affected)

-- outer apply

select *  from Departments as D outer apply fn_getsubtree(D.deptmgrid) as ST

deptid deptname deptmgrid empid empname mgrid lvl ----------- ----------- ----------- ----------- ----------- ----------- ------ 1 HR 2 2 Andrew 1 0 1 HR 2 5 Steven 2 1 1 HR 2 6 Michael 2 1 2 Marketing 7 7 Robert 3 0 2 Marketing 7 11 David 7 1 2 Marketing 7 12 Ron 7 1 2 Marketing 7 13 Dan 7 1 2 Marketing 7 14 James 11 2 3 Finance 8 8 Laura 3 0 4 R&D 9 9 Ann 3 0 5 Training 4 4 Margaret 1 0 5 Training 4 10 Ina 4 1 6 Gardening NULL NULL NULL NULL NULL (13 row(s) affected)

注意 outer apply 结果集中多出的最后一行。当 Departments 的最后一行在进行交叉联接时：deptmgrid 为 NULL，fn_getsubtree(D.deptmgrid) 生成的派生表中没有数据，但 outer apply 仍会包含这一行数据，这就是它和 cross join 的不同之处。

下面是完整的测试代码，你可以在 SQL Server 2005 联机帮助上找到：

扩展：sql server apply / sqlserver apply / sqlserver exists用法

-- create Employees table and insert values IF OBJECT_ID('Employees') IS NOT NULL DROP TABLE Employees GO CREATE TABLE Employees ( empid INT NOT NULL, mgrid INT NULL, empname VARCHAR(25) NOT NULL, salary MONEY NOT NULL ) GO IF OBJECT_ID('Departments') IS NOT NULL DROP TABLE Departments GO -- create Departments table and insert values CREATE TABLE Departments ( deptid INT NOT NULL PRIMARY KEY, deptname VARCHAR(25) NOT NULL, deptmgrid INT ) GO -- fill datas INSERT INTO employees VALUES (1,NULL,'Nancy',00.00) INSERT INTO employees VALUES (2,1,'Andrew',00.00) INSERT INTO employees VALUES (3,1,'Janet',00.00) INSERT INTO employees VALUES (4,1,'Margaret',00.00) INSERT INTO employees VALUES (5,2,'Steven',00.00) INSERT INTO employees VALUES (6,2,'Michael',00.00) INSERT INTO employees VALUES (7,3,'Robert',00.00) INSERT INTO employees VALUES (8,3,'Laura',00.00) INSERT INTO employees VALUES (9,3,'Ann',00.00) INSERT INTO employees VALUES (10,4,'Ina',00.00) INSERT INTO employees VALUES (11,7,'David',00.00) INSERT INTO employees VALUES (12,7,'Ron',00.00) INSERT INTO employees VALUES (13,7,'Dan',00.00) INSERT INTO employees VALUES (14,11,'James',00.00) INSERT INTO departments VALUES (1,'HR',2) INSERT INTO departments VALUES (2,'Marketing',7) INSERT INTO departments VALUES (3,'Finance',8) INSERT INTO departments VALUES (4,'R&D',9) INSERT INTO departments VALUES (5,'Training',4) INSERT INTO departments VALUES (6,'Gardening',NULL) GO --SELECT * FROM departments -- table-value function IF OBJECT_ID('fn_getsubtree') IS NOT NULL DROP FUNCTION fn_getsubtree GO CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS TABLE AS RETURN(  WITH Employees_Subtree(empid, empname, mgrid, lvl)  AS (  -- Anchor Member (AM)  SELECT empid, empname, mgrid, 0  FROM employees  WHERE empid = @empid UNION ALL  -- Recursive Member (RM)  SELECT e.empid, e.empname, e.mgrid, es.lvl+1  FROM employees AS e  join employees_subtree AS es  ON e.mgrid = es.empid  )  SELECT * FROM Employees_Subtree ) GO -- cross apply query SELECT * FROM Departments AS D  CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST -- outer apply query SELECT * FROM Departments AS D  OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST -------------------中国风整理的两个小例子------------------------------- create table #T(姓名 varchar(10)) insert into #T values('张三') insert into #T values('李四') insert into #T values(NULL ) create table #T2(姓名 varchar(10) , 课程 varchar(10) , 分数 int) insert into #T2 values('张三' , '语文' , 74) insert into #T2 values('张三' , '数学' , 83) insert into #T2 values('张三' , '物理' , 93) insert into #T2 values(NULL , '数学' , 50) --drop table #t,#T2 go select * from #T a cross apply  (select 课程,分数 from #t2 where 姓名=a.姓名) b /* 姓名 课程 分数 ---------- ---------- ----------- 张三 语文 74 张三 数学 83 张三 物理 93 (3 行受影响) */ select * from #T a outer apply  (select 课程,分数 from #t2 where 姓名=a.姓名) b /* 姓名 课程 分数 ---------- ---------- ----------- 张三 语文 74 张三 数学 83 张三 物理 93 李四 NULL NULL NULL NULL NULL (5 行受影响) */

扩展：sql server apply / sqlserver apply / sqlserver exists用法

五 : SQL Server 2005数据库用户权限管理的设置

SQL Server 2005数据库中，我们可以设置每个用户对应的权限，以提高数据库的安全性。这类用户权限设置的应用非常广泛。比如设置某个用户访问某个表的权限，甚至是CRUD的权限，更小粒度的还可以去到某几个字段的访问权限等等。其实这一设置过程是很简单的，接下来就让我们来一起学习吧。

一、操作步骤

1. 首先进入数据库级别的【安全性】-【登录名】-【新建登录名】

SQL Server 2005数据库用户权限管理的设置_权限设置