我觉得这个例子比较好，看看，有函数，触发器等
--自已做标识列的例子:--创建得到最大id的函数
create function f_getid()
returns int
as
begin
declare @id int
select @id=max(id) from tb
set @id=isnull(@id,0)+1
return(@id)
end
go--创建表
create table tb(id int default dbo.f_getid(),name varchar(10))
go--创建触发器,在删除表中的记录时,自动更新记录的id
create trigger t_delete on tb
AFTER delete
as
declare @id int,@mid int
select @mid=min(id),@id=@mid-1 from deleted
update tb set id=@id,@id=@id+1 where id>@mid
go--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')--显示插入的结果
select * from tb--删除部分记录
delete from tb where name in('张五','张七','张八','张十')--显示删除后的结果
select * from tb--删除环境
drop table tb
drop function f_getid
--自已做标识列的例子:--创建得到最大id的函数
create function f_getid()
returns int
as
begin
declare @id int
select @id=max(id) from tb
set @id=isnull(@id,0)+1
return(@id)
end
go--创建表
create table tb(id int default dbo.f_getid(),name varchar(10))
go--创建触发器,在删除表中的记录时,自动更新记录的id
create trigger t_delete on tb
AFTER delete
as
declare @id int,@mid int
select @mid=min(id),@id=@mid-1 from deleted
update tb set id=@id,@id=@id+1 where id>@mid
go--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')--显示插入的结果
select * from tb--删除部分记录
delete from tb where name in('张五','张七','张八','张十')--显示删除后的结果
select * from tb--删除环境
drop table tb
drop function f_getid
解决方案 »
- 如何搜索出两条完全一样的数据
- 关于别名，自连接
- SQLServer2005中关于设置外键关系的帮助在哪里？联机手册没找到。
- 新建的用户，局域网内登陆不了，只有本机才能登陆。
- 用sql时间探查器，跟跟踪sql语句为什么有的sql语句的textdata字段为null
- 我可以在sqlserver中定義一個系統變量（或常量也可）？
- 请教一个sql查询
- 应用软件为什么在windows XP版本下无法连接数据库 开发工具为PB
- ADO更新令人实在费解，请各位高手相助
- 我的SQL server中的datetime字段取出放在DatetimePicker控件，怎么老出错？
- SQL Server中的基本问题
- SQL语句的问题，郁闷中,没办法只好求教大虾们.......
创建用户定义函数，它是返回值的已保存的 Transact-SQL 例程。用户定义函数不能用于执行一组修改全局数据库状态的操作。与系统函数一样，用户定义函数可以从查询中唤醒调用。也可以像存储过程一样，通过 EXECUTE 语句执行。用户定义函数用 ALTER FUNCTION 修改，用 DROP FUNCTION 除去。语法
标量函数CREATE FUNCTION [ owner_name.] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS scalar_return_data_type[ WITH < function_option> [ [,] ...n] ] [ AS ]BEGIN
function_body
RETURN scalar_expression
END内嵌表值函数CREATE FUNCTION [ owner_name.] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS TABLE [ WITH < function_option > [ [,] ...n ] ] [ AS ]RETURN [ ( ] select-stmt [ ) ]多语句表值函数CREATE FUNCTION [ owner_name.] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE < table_type_definition >[ WITH < function_option > [ [,] ...n ] ] [ AS ]BEGIN
function_body
RETURN
END< function_option > ::=
{ ENCRYPTION | SCHEMABINDING }< table_type_definition > ::=
( { column_definition | table_constraint } [ ,...n ] )
参数
owner_name拥有该用户定义函数的用户 ID 的名称。owner_name 必须是现有的用户 ID。function_name用户定义函数的名称。函数名称必须符合标识符的规则，对其所有者来说，该名称在数据库中必须是唯一的。@parameter_name用户定义函数的参数。CREATE FUNCTION 语句中可以声明一个或多个参数。函数最多可以有 1,024 个参数。函数执行时每个已声明参数的值必须由用户指定，除非该参数的默认值已经定义。 如果函数的参数有默认值，在调用该函数时必须指定"default"关键字才能获得默认值。这种行为不同于存储过程中有默认值的参数，在存储过程中省略参数也意味着使用默认值。使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个函数的参数仅用于该函数本身；相同的参数名称可以用在其它函数中。参数只能代替常量；而不能用于代替表名、列名或其它数据库对象的名称。scalar_parameter_data_type参数的数据类型。所有标量数据类型（包括 bigint 和 sql_variant）都可用作用户定义函数的参数。不支持 timestamp 数据类型和用户定义数据类型。不能指定非标量类型（例如 cursor 和 table）。scalar_return_data_type是标量用户定义函数的返回值。scalar_return_data_type 可以是 SQL Server 支持的任何标量数据类型（text、ntext、image 和 timestamp 除外）。scalar_expression指定标量函数返回的标量值。 TABLE指定表值函数的返回值为表。在内嵌表值函数中，通过单个 SELECT 语句定义 TABLE 返回值。内嵌函数没有相关联的返回变量。在多语句表值函数中，@return_variable 是 TABLE 变量，用于存储和累积应作为函数值返回的行。 function_body指定一系列 Transact-SQL 语句定义函数的值，这些语句合在一起不会产生副作用。function_body 只用于标量函数和多语句表值函数。在标量函数中，function_body 是一系列合起来求得标量值的 Transact-SQL 语句。在多语句表值函数中，function_body 是一系列填充表返回变量的 Transact-SQL 语句。select-stmt是定义内嵌表值函数返回值的单个 SELECT 语句。ENCRYPTION指出 SQL Server 加密包含 CREATE FUNCTION 语句文本的系统表列。使用 ENCRYPTION 可以避免将函数作为 SQL Server 复制的一部分发布。SCHEMABINDING指定将函数绑定到它所引用的数据库对象。如果函数是用 SCHEMABINDING 选项创建的，则不能更改（使用 ALTER 语句）或除去（使用 DROP 语句）该函数引用的数据库对象。函数与其所引用对象的绑定关系只有在发生以下两种情况之一时才被解除： 除去了函数。
在未指定 SCHEMABINDING 选项的情况下更改了函数（使用 ALTER 语句）。
只有在满足以下条件时，函数才能绑定到架构： 该函数所引用的用户定义函数和视图也已绑定到架构。
该函数所引用的对象不是用两部分名称引用的。
该函数及其引用的对象属于同一数据库。
执行 CREATE FUNCTION 语句的用户对所有该函数所引用的数据库对象都具有 REFERENCES 权限。
如果不符合以上条件，则指定了 SCHEMABINDING 选项的 CREATE FUNCTION 语句将失败。注释
用户定义函数为标量值函数或表值函数。如果 RETURNS 子句指定一种标量数据类型，则函数为标量值函数。可以使用多条 Transact-SQL 语句定义标量值函数。如果 RETURNS 子句指定 TABLE，则函数为表值函数。根据函数主体的定义方式，表值函数可分为行内函数或多语句函数。如果 RETURNS 子句指定的 TABLE 不附带列的列表，则该函数为行内函数。行内函数是使用单个 SELECT 语句定义的表值函数，该语句组成了函数的主体。该函数返回的表的列（包括数据类型）来自定义该函数的 SELECT 语句的 SELECT 列表。如果 RETURNS 子句指定的 TABLE 类型带有列及其数据类型，则该函数是多语句表值函数。多语句函数的主体中允许使用以下语句。未在下面的列表中列出的语句不能用在函数主体中。 赋值语句。
控制流语句。
DECLARE 语句，该语句定义函数局部的数据变量和游标。
SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。
游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以 INTO 子句向局部变量赋值的 FETCH 语句；不允许使用将数据返回到客户端的 FETCH 语句。
INSERT、UPDATE 和 DELETE 语句，这些语句修改函数的局部 table 变量。
EXECUTE 语句调用扩展存储过程。
函数的确定性和副作用
函数可以是确定的或不确定的。如果任何时候用一组特定的输入值调用函数时返回的结果总是相同的，则这些函数为确定的。如果每次调用函数时即使用的是相同的一组特定输入值，返回的结果总是不同的，则这些函数为不确定的。不确定的函数会产生副作用。副作用是更改数据库的某些全局状态，比如更新数据库表或某些外部资源，如文件或网络等（例如，修改文件或发送电子邮件消息）。
尽管在用户定义函数主体中不允许有不确定函数，这些用户定义函数在调用扩展存储过程时仍会产生副作用。由于扩展存储过程会对数据库产生副作用，因此调用扩展存储过程的函数是不确定的。当用户定义函数调用会对数据库产生副作用的扩展存储过程时，不要指望结果集保持一致或执行函数。 从函数中调用扩展存储过程
从函数内部调用时扩展存储过程无法向客户端返回结果集。任何向客户端返回结果集的 ODS API 都将返回 FAIL。扩展存储过程可以连接回 Microsoft® SQL Server™；但是，它不应尝试联接与唤醒调用扩展存储过程的函数相同的事务。与从批处理或存储过程中唤醒调用相似，扩展存储过程在运行 SQL Server 的 Windows® 安全帐户的上下文中执行。存储过程的所有者在授予用户 EXECUTE 特权时应考虑这一点。函数调用
在可使用标量表达式的位置可唤醒调用标量值函数，包括计算列和 CHECK 约束定义。当唤醒调用标量值函数时，至少应使用函数的两部分名称。[database_name.]owner_name.function_name ([argument_expr][,...])如果用户定义函数用于定义计算列，则该函数的确定性同样决定了是否可在该计算列上创建索引。只有当函数具有确定性时，才可以在使用该函数的计算列上创建索引。如果在输入相同的情况下函数始终返回相同的值，则该函数具有确定性。可以使用由一部分组成的名称唤醒调用表值函数。[database_name.][owner_name.]function_name ([argument_expr][,...])对于 Microsoft® SQL Server™ 2000 中包含的系统表函数，唤醒调用时需在函数名的前面加上前缀"::"。SELECT *
FROM ::fn_helpcollations()对于导致语句停止执行然后从存储过程中的下一语句继续执行的 Transact-SQL 错误，在函数中的处理方式不同。在函数中，这类错误会导致函数停止执行。这反过来使唤醒调用该函数的语句停止执行。权限
用户应具有执行 CREATE FUNCTION 语句的 CREATE FUNCTION 权限。CREATE FUNCTION 的权限默认地授予 sysadmin 固定服务器角色和 db_owner 和 db_ddladmin 固定数据库角色的成员。sysadmin 和 db_owner 的成员可用 GRANT 语句将 CREATE FUNCTION 权限授予其它登录。函数的所有者对其函数具有 EXECUTE 权限。其他用户不具有 EXECUTE 权限，除非给他们授予了特定函数上的 EXECUTE 权限。若要创建或更改在 CONSTRAINT、DEFAULT 子句或计算列定义中引用了用户定义函数的表，用户还必须对这些函数有 REFERENCES 权限。
A. 计算 ISO 周的标量值用户定义函数
下例中，用户定义函数 ISOweek 取日期参数并计算 ISO 周数。为了正确计算该函数，必须在调用该函数前唤醒调用 SET DATEFIRST 1。 CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
AS
BEGIN
DECLARE @ISOweek int
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END下面是函数调用。注意 DATEFIRST 设置为 1。SET DATEFIRST 1
SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'
下面是结果集。ISO Week
----------------
52B. 内嵌表值函数
下例返回内嵌表值函数。USE pubs
GO
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM sales s, titles t
WHERE s.stor_id = @storeid and
t.title_id = s.title_id)C. 多语句表值函数
假设有一个表代表如下的层次关系： CREATE TABLE employees (empid nchar(5) PRIMARY KEY,
empname nvarchar(50),
mgrid nchar(5) REFERENCES employees(empid),
title nvarchar(30)
)表值函数 fn_FindReports(InEmpID) 有一个给定的职员ID，它返回与所有直接或间接向给定职员报告的职员相对应的表。该逻辑无法在单个查询中表现出来，不过可以实现为用户定义函数。 CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30))
/*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
AS
BEGIN
DECLARE @RowsAdded int
-- table variable to hold accumulated results
DECLARE @reports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30),
processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee
INSERT @reports
SELECT empid, empname, mgrid, title, 0
FROM employees
WHERE empid = @InEmpId
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
/*Mark all employee records whose direct reports are going to be
found in this iteration with processed=1.*/
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees ed 1.
INSERT @reports
SELECT e.empid, e.empname, e.mgrid, e.title, 0
FROM employees e, @reports r
WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
SET @RowsAdded = @@rowcount
/*Mark all employee records whose direct reports have been found
in this iteration.*/
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
-- copy to the result of the function the required columns
INSERT @retFindReports
SELECT empid, empname, mgrid, title
FROM @reports
RETURN
END
GO-- Example invocation
SELECT *
FROM fn_FindReports('11234')
GO
Baya Pavliashvili and Kevin Kline
http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409你们大部分人可能已经在SQL SERVER中建立自己的用户定义函数(UDF),但是你知道么?微软公司已经集成了大量自己的UDFs,特别是在最新发布的SP3中.在这篇文章中 Baya Pavliashvili和Kevin Kline系统地研究了关于SQL SERVER跟踪部分的UDF.你们中的一些人也许想阅读以前SQL Server Professional的一篇关于传统UDFs的文章,比如Andrew Zanevsky's 2000年9月的专栏 ("Granting Wishes with UDF"), Andrew Zanevsky and Anton Jiline's 2001年10月的文章 ("UDF Performance… or Lack of It"), 或 Jimmy Nilsson's 2003年7月的文章("Another UDF: Global Constants").
UDFs是SQL Server 2000期待已久的附加功能, UDFs典型的应用是DBAs和开发者用来模块化代码和间或用来提高性能.在这篇文章中,我们将从零开始了解SQL SERVER系统提供的UDFs,可以允许DBA进行跟踪管理.
虽然系统提供的用户定义函数听起来有一点矛盾,但微软还是集成大量的内部的UDFs(只读,系统提供).同时,尽管UDFs这个特性在SQL SERVER 2000最初发布是就提供了,不过我们发现只是在SP3中微软才因为自己的目的而大量使用,所有系统提供的UDFs函数都是以’fn_’开始并且保存在master数据库中.
比较系统提供的和标准的UDF
如果你熟悉UDFs,你也许知道UDF是不能修改固定表的记录,典型的应用是:读取数据,修改表变量的数据,返回数据.而且UDFs可以运行扩展存储过程和系统提供的自定义函数.事实上有很多的系统提供的自定义函数只是简单的调用一个扩展存储过程.[ 扩展存储过程通常是有C++写的DLL文件,你可以看Paul Storer-Martin's在2002年7月和8月的文章"Playing the ODS"],因此阅读用T-SQL写的UDF相同功能的代码不是更好么? 系统提供的自定义函数和用户的自定义函数在运行时有轻微的差别:典型的自定义函数(UDFs)可以向这样调用:
SELECT column_list
FROM owner_name.UDF_name (@parameter1, … @parameterN)
系统提供的自定义函数需要在FROM后面加二个冒号(::),同时你不必指定该功能的所有者:
SELECT column_list
FROM :: fn_SystemSuppliedUDF
(@parameter1, … @parameterN)
比如: 系统提供的自定义函数fn_helpcollations()可以返回SQL SERVER 2000支持的所有字符集,我们可以这样执行:
SELECT * FROM :: fn_helpcollations()
用于跟踪的自定义函数(UDFs)
一条跟踪捕获的T-SQL语句发给(或运行一个存储过程在)指定的SQL SERVER的实列并且保存为一个*.TRC的文件.SQL SERVER的跟踪可以通过Profiler工具或运行系统的存储过程sp_trace_create建立并且可以指定许多过虑的标准来限制输出文件.在这篇文章中,我们主要针对跟踪功能的系统提供的自定义函数.
fn_trace_gettable
fn_trace_gettable() 需要二个参数: 初始化的跟踪文件名(.TRC)和跟踪文件的个数.当你建立一个跟踪,你可以配置SQL SERVER限制跟踪文件的大小.当跟踪文件到达指定的大小,SQL SERVER会字段产生一个新的”滚动的”跟踪文件. fn_trace_gettable()函数的第二个参数是”滚动的”跟踪文件的个数,这是在指定第一个参数时开始的.
如果你喜欢将跟踪的新年保存在数据库中,你可以简单地运行一个查询,通过fn_trace_gettable 将跟踪文件保存为一个数据表,比如:
SELECT *
INTO dbo.my_trace_table
FROM :: fn_trace_gettable
('c:\trace_file.trc', default)
而且,可以非常方便直接查询,搜索一些特殊含义的字符串.在我们的测试环境中,所有的用户定义的存储过程以”USP”开始,因此我们可以运行一个查询,搜索持续时间超过3000ms的记录:
SELECT TextData, duration
FROM ::
fn_trace_gettable('c:\trace_file.trc', default)
WHERE TextData LIKE '%usp%'
AND duration > 3000
通过更加复杂的查询,我们可以精练SELECT语句来确定哪些查询一致运行地比较慢还是只在高峰期.
fn_trace_getinfo
这个系统提供的自定义函数可以得到一个跟踪的高级别信息或在一个SQL SERVER上运行的所有正在运行的跟踪.这个函数只有一个参数—跟踪的编号(TRACE ID)
为了限制一个跟踪的信息,你必须指定跟踪标志符.你也可以指定DEFAULT或”0”,作为跟踪标志符,这样可以获得所有的运行的跟踪信息.SQL SERVER在建立跟踪时给每一个跟踪分配一个跟踪标志符,如果你不指定你要查询的跟踪标志符,简单的以参数”0”运行该系统函数,然后你可以限制跟踪输出你感兴趣的内容. fn_trace_getinfo系统函数的输出描述如表一:
表 1. f fn_trace_getinfo的输出.
列名 描述
TraceID 此跟踪的 ID.可以被用来通过系统存储过程来管理跟踪
Property 跟踪的属性，由下列整数表示：
1 – 跟踪选项（请参见 sp_trace_create 中的 @options）
2 – FileName
3 – MaxSize
4 – StopTime
5 – 当前跟踪状态
Value 有关指定跟踪的属性的信息。
跟踪的选项可以通过系统存储过程sp_trace_create来指定(看表2)
表 2. 跟踪的选项可以通过系统存储过程sp_trace_create来指定
选项名 选项值 描述
Trace_produce_rowset 1 跟踪将产生一个行集
Trace_file_rollover 2 当达到 max_file_size 时，将关闭当前跟踪文件并创建新文件.SQL SERVER 会自动为每个文件增加序列编号(1,2,3….)
Shutdown_on_error 4 如果不能将跟踪写入文件，则 SQL Server 将关闭。
Trace_produce_blackbox 8 如果这个选项被选中,SQL SERVER的最后 5 MB 跟踪信息记录将由服务器保存
下面我们看一个实列来了解fn_trace_getinfo是如何工作的.设想我们通过下面的查询建立一个跟踪:
/* declare a variable to hold trace ID */
DECLARE @trace_id INT
/* create the trace */
EXEC sp_trace_create
@traceid = @trace_id OUTPUT,
@options = 2 ,
@tracefile = N'e:\trace_file.trc' ,
@maxfilesize = 5,
@stoptime = NULL
/* start the trace we just created.
by default the trace is stopped at creation
*/
EXEC sp_trace_setstatus @trace_id, 1
/* return the trace identifier*/
SELECT 'trace ID is: ' + CAST(@trace_id AS VARCHAR(4))
--Result:
-------------------------
trace ID is: 2
现在我们可以用fn_trace_getinfo 来获得相应跟踪的信息
SELECT * FROM :: fn_trace_getinfo(2)
查询的结果在表 3.
表3. fn_trace_getinfo查询的结果.
traceID Property Value
2 1 2
2 2 e:\trace_file.trc
2 3 5
2 4 NULL
2 5 1
这个输出告诉我们,有一个正在运行的跟踪,自动增长到5MB后会自动产生另外一个文件.没有指定跟踪停止时间( property = 4 ) ,因此该跟踪会运行直到SQL SERVER服务停止或通过系统存储过程sp_trace_setstatus停止跟踪.fn_trace_getfilterinfo 和 fn_trace_geteventinfo
这些函数可以用来检索一个跟踪的元数据(这些函数的输出的含义是模糊的,如果你不熟悉跟踪的事件标志符和过虑的列的标志符.你可以看看SQL SERVER的在线帮助 “sp_trace_setevent”)
这2个函数都是将跟踪标志符作为唯一的参数. fn_trace_getfilterinfo返回指定跟踪的过虑后的信息.举例:假设我们限制跟PUBS数据库,因为我们试图解决该数据库中一个运行很长时间的查询的故障,我们可以如下运行该函数:
SELECT * FROM :: fn_trace_getfilterinfo(1)
--Results:
columnID logical_operator comparison_operator value
---------- ---------------- ------------------- ---------
35 0 6 pubs
根据你应用过虑的类型,你可以改变比较操作符.在许多案例中,你可以使用 LIKE或 NOT LIKE.而且,如果你限制跟踪指定的处理或指定的数据库,那么你可以指定"=", ">", "< >", "<", "> =", or "< ="等逻辑操作符.
备注:如果你通过SQL SERVER Profiler工具建立一个跟踪,这个跟踪会自动增加一个过滤器,不包括通过Profiler自己产生给SQL SERVER的跟踪过滤语句.如果你喜欢了解Profiler工具,只要简单的将过滤器设置为off.
fn_tracegeteventinfo 可以返回一个跟踪的事件信息,有很多的跟踪事件你可以指定.你指定的事件越多,就会有更多的信息被整理,因此要仔细.我劝告大家要了解每一个事件的含义并且选择那些和你解决问题密切相关的事件. fn_tracegeteventinfo 是一个非常有用的函数,当你因为不同的目的要模拟运行多个跟踪.
要获得我们建立的跟踪事件的标志符,我们可以如下运行fn_tracegeteventinfo函数:
SELECT DISTINCT eventid
FROM :: fn_trace_geteventinfo(1)
--Results:
eventid
-----------
12
37
40
41
42
43
这个结果告诉我们,这个跟踪在检测以下事件:
• SQL: BatchCompleted—event id of 12
• SP: Recompile—event id of 37
• SQL: StatementStarting—event id of 40
• SQL: StatementCompleted—event id of 41
• SP: Starting—event id of 42
• SP: Completed—event id of 43
同样的,我们可以运行相同的函数,进行很小的改动就可以获得一个跟踪的所有数据列
SELECT DISTINCT columnid FROM :: fn_trace_geteventinfo(1)
--Results:
columnid
-----------
1
10
11
12
13
14
16
17
18
这里我们收集的典型数据用来性能调整,包括一个查询的:正文数据,程序名,登陆名,SPID,持续时间,开始结束时间,读取,写入和CPU占用.
使用系统提供的UDFs
现在你指定一些系统提供的UDFs,你可以通过这些系统函数建立自己的自定义函数.其中有一个主要的限制是自定义函数无法调用存储过程.但是自定义函数可以调用其他的自定义函数.下面的UDF初始化fn_trace_geteventinfo函数,使结果更方便阅读:
CREATE FUNCTION dbo.fn_GetTraceColumns (@trace_id INT)
RETURNS @TraceColumns TABLE (
column_id INT,
column_name VARCHAR(155)
)
AS
BEGIN
INSERT @TraceColumns (
column_id)
SELECT DISTINCT columnid FROM ::
fn_trace_geteventinfo(@trace_id)
UPDATE @TraceColumns
SET column_name =
CASE column_id
WHEN 1 THEN 'TextData'
WHEN 3 THEN 'DatabaseID'
WHEN 4 THEN 'TransactionID'
WHEN 6 THEN 'NTUserName'
-- similar statements omitted here - see Source
ELSE 'other'
END
RETURN
END
This function can be executed as follows:
SELECT * FROM dbo.fn_getTraceColumns(3)
概要
在这篇文章中,我介绍了SQL SERVER 2000提供的非常有用的系统自定义函数,希望大家喜欢并且研究其他的系统提供的自定义函数.
下载: TRACEUDFS.SQL
参考资源:
• 284790 INF: How to Create a SQL Server 2000 Trace
• 283786 INF: How to Monitor SQL Server 2000 Traces
• 270599 BUG: fn_trace_gettable Function Cannot Read Rollover Files 273972 Generated by SQL
• ProfilerHOW TO: Programmatically Load Trace Files into Tables
• 268591 PRB: ODBC Tracing to SQL.LOG Can Slow SQL Server or Consume All Disk Space
• 307786 INF: Tracing to Network Drive May Reduce SQL Server Throughput
• 286239 BUG: Replay Tool Uses LoginName Column for SETUSER Instead of DatabaseUserName Column
• Andrew Novick's "Find Out What They're Doing with fn_get_sql"—www.databasejournal.com/features/mssql/article.php/2189761
—kw
To find out more about SQL Server Professionaland Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57
Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.
This article is reproduced from the August 2003 issue of Microsoft SQL Server Professional. Copyright 2003, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. SQL Server Professionalis an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.