SQL函数说明大全(三）

排列函数

这些函数被用于以与结果集顺序无关的特定顺序，枚举已排序的或排在前面的结果集。

ROW_NUMBER()函数

ROW_NUMBER()函数根据作为参数传递给这个函数的ORDER BY子句的值，返回一个不断递增的整数值。如果ROW_NUMBER的ORDER BY的值和结果集中的顺序相匹配，返回值将是递增的，以升序排列。

如果ROW_NUMBER的ORDER BY子句的值和结果集中的顺序不同，这些值将不会按顺序列出，但它们表示ROW_NUMBER函数的ORDER BY子句的顺序。如下面的例子和结果所示：

SELECT ProductCategoryID 
    ,Name
    ,ROW_NUMBER() OVER (ORDER BY Name) AS RowNum
FROM Production.ProductCategory
ORDER BY Name

由于ROW_NUMBER()调用中的ORDERBY子句和查询结果的顺序匹配，所以对这些结果按顺序列出，如下图所示：

不过，在函数调用中使用另一个ORDER BY子句时，这些值就是无序的了。

SELECT ProductCategoryID 
    ,Name
    ,ROW_NUMBER() OVER (ORDER BY Name) AS RowNum
FROM Production.ProductCategory
ORDER BY ProductCategoryID

这是了解如何使用ORDER BY子句对结果进行排序的有效方法。如下图所示：

                             

RANK()与DENSE_RANK()函数

这两个函数与ROW_NUMBER()函数类似，因为它们都返回一个基于ORDER BY子句的值。不过这些值不一定永远是唯一的。排列值对于所提供的ORDER BY子句中的重复结果而言也是重复的，而且唯一性是仅仅基于ORDER BY列表中的唯一值的。

这些函数用不同的方法来处理重复的值。RANK()函数保留列表中行的位置序号，对于每个重复的值，该函数会跳过下面与其相邻的值，于是就可以将下一个不重复的值保留在正确的位置上。

其行为类似于短跑比赛中的并列成绩。例如刘翔与Dayron Robles（古巴）在110栏的比赛中都跑出了12’92的成绩，那他们就是并列第一，而其后的一名选手将会获得第三名的成绩。

SELECT ProductID 
    ,Name
    ,ListPrice
    ,RANK() OVER (ORDER BY ListPrice DESC) AS [Rank]
FROM Production.Product
ORDER BY [Rank]

注意在下图的结果列表中，重复的价格值所对应的结果是相同的，而每个连接之后的值都被跳过了。比如，产品"Road-150 Red, 52"和"Road-150 Red, 56"都排在第1，而接下来的行"Mountain-100 Silver,38"就排在第6了。

 

DENSE_RANK()函数的工作方式与RANK()函数相同，不过它不会跳过每个连接后的值，这样就不会有值被跳过了，但是在连接处排列序号位置将会丢失。

SELECT ProductID 
    ,Name
    ,ListPrice
    ,DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS [Rank]
FROM Production.Product
ORDER BY [Rank]

下图的结果重复了排列值，但是不会跳过列中的任何数字。

NTILE(n)函数

这个函数也用于对结果进行排列，并返回一个整型的排列值，但是它不会对结果以唯一的排列顺序进行枚举，而是将结果切分为有限数量的排列组。

比如，一个表有10 000行，使用1000为参数值调用NTILE()函数，即NTILE(1000)，并将结果分成以10为单位的1000个组，每个组赋予相同的排列值。

和本节讨论的其他排列函数一样，NTILE()函数也支持OVER(ORDER BY…)语法。下面的例子根据产品价格，按照从高到低的顺序把Product表分为50组产品：

SELECT ProductID 
    ,Name
    ,ListPrice
    ,NTILE(50) OVER (ORDER BY ListPrice DESC) AS GroupedProducts
FROM Production.Product
ORDER BY GroupedProducts

 

结果为：

安全函数

与安全相关的函数返回SQL Server用户的角色成员和权限信息。这类函数也包括一组管理事件与跟踪的函数。下表显示了这些函数：

函    数	说    明
fn_trace_geteventinfo()	为指定的跟踪ID返回一个填充事件信息的表类型值
fn_trace_getfilterinfo()	为指定的跟踪ID返回一个填充与过滤器有关的信息的表类型值
fn_trace_getinfo()	为指定的跟踪ID返回一个填充跟踪信息的表类型值
fn_trace_getable()	为指定的跟踪ID返回一个填充文件信息的表类型值
HAS_DBACCESS()	返回一个表明当前用户是否有访问指定数据库权限的标志
IS_MEMBER()	返回一个表明当前用户是Windows组用户还是SQL Server用户的标志
IS_SRVROLEMEMBER()	返回一个表明当前用户是否是数据库服务器角色成员的标志
SUSER_SID()	返回指定用户的登录名的安全ID，或者(如果参数被忽略)返回当前用户的安全ID。返回指定用户的用户ID，或者(如果参数被忽略的话)返回当前用户的用户ID
SUSER_SNAME()	返回指定安全ID的登录名。如果不提供任何安全ID，则返回当前用户的登录名
USER_ID()	返回指定用户名的用户ID，或者(如果参数被忽略的话)返回当前用户的用户ID
USER_NAME()	返回指定用户ID的用户名

系统函数与系统变量

本节讨论具有多种用途的工具函数，包括值比较、值类型测试等功能。这个类别的函数也包罗了其他函数：

函    数	说    明
APP_NAME()	返回与当前连接相关联的应用程序的名字
COALESCE()	从以逗号分隔的表达式列表中返回第一个非空值
COLLATIONPROPERTY()	返回一个特定字符集排序规则的特定属性的值。这些属性包括CodePage、LCID、ComparisonStyle
CURRENT_TIMESTAMP()	返回当前日期与时间。和GETDATE()函数是同义的。这个函数的存在只是为了与ANSI-SQL兼容
C1UJRRENT_USER()	返回当前用户的名字。与USER_NAME()函数相同
DATALENGTH()	返回存储或处理一个值所需的字节数。对于ANSI字符串类型，这个函数返回的值与LEN()函数相同，但对于其他数据类型而言就可能不一定相同了
fn_helpcollations()	返回一个填充有由当前SQLSewer版本支持的字符集排序规则的表类型值
fn_servershareddrives()	返回一个填充有服务器共享的驱动列表的表类型值
fn_virtualfilestats()	返回一个填充有包括日志文件在内数据库文件的I/O状态的表类型值
FORMATMESSAGE()	从sysmessages表中为指定的信息代码和以逗号分隔的参数列表返回错误信息
GETANSINULL()	根据ANSLNULL_DFLT_ON与ANSLNULL_DFLT_OFF数据库设置返回数据库的可空性设置
HOST_ID()	返回当前会话的工作站ID
HOST_NAME()	返回当前会话的工作站名
IDENT_CURRENT()	返回最后一个为指定的表生成的标识(ID)值。与会话、范围无关
IDENT_INCR()	返回最后一次创建的标识(ID)列中定义的增量值
IDENT_SEED()	返回最后一次创建的标识(ID)列中定义的种子值
IDENTITY()	用在SELECT…INTO语句中，在一个列中插入自动生成的标识值
ISDATE()	返回一个表明指定的值是否可被转换为日期值的标志
ISNULL()	判断指定的值是否是空值，然后返回一个事先提供的替代值
ISNUMERIC()	返回一个表明指定的值是否可被转换为数字值的标志
NEWID()	返回一个新生成的UniqueIdentifier类型的值。这是一个128位的整型、全球唯一的值，通常以字母或数字十六进制来表示(如89DE6247·C2E242DB-8CE8·A787E505D7EA)。这个类型经常被用作复制的和半连接系统中的主键.
NULLIF()	两个特定的参数的值如果是相同的，则返回NULL
PARSENAME()	返回一个具有4部分对象名的特定部分
PERMISSIONS()	返回一个整型值，该值是一个表示当前用户在指定的数据库对象上权限或者权限组合的位映像
ROWCOUNT_BIG()	与@@RowCount变量一样，这个函数返回被最后一条语句修改或返回的行数量。返回值类型是bigint
SCOPE_IDENTITY()	与@@IDENTIY变量一样，这个函数返回限制在当前会话与范围内的最后一次生成的标识值
SERVERPROPERTY()	返回一个表示服务器属性状态的标记。属性包括Collation、Edition、EngineEdition、InstanceName、IsClustered、IsFullTextInstalled、IsIntegrated- SecurityOnly、IsSingleUser、IsSyncWithBackup、LicenseTYpe、MachineName、NumLicenses、ProcessID、ProductLevel、ProductVersion、ServerName
SESSION_USER	返回当前用户名。调用本函数不需要括号
SESSIONPROPERTY()	返回表示一个会话属性状态的标记。属性包括:ANSL_NULLS，ANSI_PADDING，ANSL_WARNINGS，ARITHABORT，CONCAT_NULL_ YIELDS_NULL，NUMERIC_ROUNDABORT，QUOTED_IDENTIFIER
STATS_DATE()	返回指定的索引统计信息最后一次被更新的时间
SYSTEM_USER	返回当前用户名。调用本函数不需要括号
USER_NAME()	为一个指定的用户ID返回用户名。如果没有提供ID号则返回当前的数据库用户

COALESCE()函数

COALESCE()函数是非常有用的，它返回其参数中第一个非空表达式。它能够节省颇多IF或者CASE分支逻辑。以下例子用产品数据填充一个表，每个产品最多有3种价格:

CREATE TABLE #ProductPrices (ProductName varchar(25), SuperSalePriceMoney NULL, SalePrice Money NULL, ListPrice Money NULL) 
GO 
INSERT INTO #ProductPrices VALUES('Standard Widget', NULL, NULL,15.95) 
INSERT INTO #ProductPrices VALUES('Economy Widget', NULL, 9.95, 12.95) 
INSERT INTO #ProductPrices VALUES('Deluxe Widget', 19.95, 20.95,22.95) 
INSERT INTO #ProductPrices VALUES('Super Deluxe Widget', 29.45, 32.45,38.95) 
INSERT INTO #ProductPrices VALUES('Executive Widget', NULL, 45.95,54.95) 
GO

 

所有的产品都有定价，有些有销售价，有些还有促销价。一项产品的当前价格是所有己有价格的最低价，或者在读取每个价格列时以列出顺序读到的第一个非空值:

SELECT ProductName, COALESCE(SuperSalePrice, SalePrice, ListPrice) ASCurrentPrice
FROM #ProductPrices

 

 这个方法比使用多行分支与判断逻辑要简洁得多，而结果也是同样简单，如下图所示：

DATALENGTH()函数

DATALENGTH()函数返回一个用于对值进行管理的字节数，这有助于揭示不同数据类型间的一些有趣差别。当把varchar类型传递给DATALENGTH()和LEN()函数时，它们将返回相同的值：

DECLARE @Value varchar(20) 
SET @Value = 'abc'
SELECT DATALENGTH(@Value) 
SELECT LEN(@Value)

 

 这些语句的返回值都为3。因为varchar类型使用了3个单字节字符来存储三个字符的值。然而，如果使用nVarchar类型来管理相同长度的值，就要占用多一倍的字节:

DECLARE @Value nvarchar(20) 
SET @Value = 'abc'
SELECT DATALENGTH(@Value) 
SELECT LEN(@Value)

 

DATALENGTH()函数返回值为6，因为每个使用Unicode字符集的字符都要占用2个字节。LEN()函数返回值为3，因为这个函数返回字符数，不是字节数。

以下是一个有趣的测试：要存储一个值为2的整型变量，要占用多少个字节？而如果要存储一个值为20亿的整型变量，又将占用多少个字节呢？试一下：​​​​​​​

DECLARE @Value1 int, @Value2 int
SET @Value1 = 2 
SET @Value2 = 2000000000 
SELECT DATALENGTH(@Value1) 
SELECT LEN(@Value1) 
SELECT DATALENGTH(@Value2) 
SELECT LEN(@Value2)

​​​​​​​

 在这两种情况下，DATALENGTH()函数都返回4。因为int类型不论值是多少，总是使用4个字节。LEN()函数本质上将整型值当成已转换成字符型的数据来处理，所以，在这个例子中，它分别返回1和10，即值的位数。

 

在下表中的全局系统变量都将返回int类型的值。这些变量可用于存储过程和其他实现定制业务逻辑的编程对象。

变    量	说    明
@@ERROR	当前会话最后一次发生的错误代码
@@IDENTITY	当前会话最后一次生成的标识值
@@ROWCOUNT	当前会话中最后一次返回结果集的执行操作所返回的行数
@@TRANCOUNT	当前会话中活动的事务数。这是在执行相关的COMMIT TRANSACTION或者ABORT TRANSACTION语句之前嵌套的多个BEGIN TRANSACTION语句的结果

系统统计变量

下表描述了用于确定数据库系统使用信息与环境信息的管理工具：

变    量	说    明
@@CONNECTIONS	返回打开连接的次数
@@CPU_BUSY	从上次启动服务器开始，SQL Server一共工作的毫秒数
@@IDLE	从上次启动服务器开始，SQL Server一共空闲的毫秒数
@@IO_BUSY	从上次启动服务器开始，SQL Server一共处理I/0的毫秒数
@@PACK_RECEIVED	从上次启动服务器开始，SQL Server一共收到的网络数据包数
@@PACK_SENT	从上次启动服务器开始，SQL Server一共发送的网络数据包数
@@PACKET_ERRORS	从上次启动服务器开始，SQL Server一共收到的网络数据包错误数
@@TIMETICKS	每个时钟滴答有多少毫秒
@@TOTAL_ERRORS	从上次启动服务器开始，SQL Server一共收到的磁盘I/O错误数
@@TOTAL_READ	从上次启动服务器开始，SQL Server一共进行的物理磁盘读取次数
@@TOTAL_WRITE	从上次启动服务器开始，SQL Server一共进行的物理磁盘写入次数

小结

函数用于实现业务逻辑，并且能够将编程功能带入查询中。许多有用而且强大的函数是T-SQL的标准功能。

和面向过程、面向对象语言中的函数一样，SQL函数也将程序功能封装到一个简单的可重用的包中，这就减少了查询设计人员的很多工作。

由于Transact-SQL是面向任务的语言，而不是过程语言。虽然函数可以进行过程编程，可以在查询中构建颇为复杂的逻辑，但是SQL语言的优势在于让设计人员表达出设计意图，而不是完成一项任务的确切步骤与方法。只要使用方法正确，这些步骤和方法都可以由函数来实现。

在T-SQL中，参数用于将值传递给函数，大多数函数的返回结果是一个标量，或者说单一值。函数分为确定性函数与非确定性函数。

在使用相同的参数时，确定性函数总是返回相同的值，而非确定性函数的返回值则与其他资源有关，所以SQL Server必须显式地执行这种函数。因此，在定制的SQL编程对象中，对非确定性函数的使用是有限制的。

SQL函数执行种类繁多的重要任务，包括数学运算、比较、日期解析与操纵、高级字符串操纵等。