SQL Server 常用内置函数（built-in）持续整理

共计 3957 个字符，预计需要花费 10 分钟才能阅读完成。

本文用于收集在运维中 SQL Server 经常使用的系统内置函数，持续整理中

一，常用 Metadata 函数

1，查看数据库的 ID 和 Name

db_id(‘DB Name’),db_name('DB ID')

2，查看对象的 ID 和 Name，对象的 Schema，对象的定义

OBJECT_ID ('schema_name . object_name','object_type' ) 
OBJECT_NAME (object_id [, database_id ] ) 
OBJECT_SCHEMA_NAME (object_id [, database_id ] )
OBJECT_DEFINITION (object_id )

3，查看 Schema 的 ID 和 Name，通过对象 ID 获取对象的架构名（Schema）

SCHEMA_NAME ([ schema_id ] )  
SCHEMA_ID ([ schema_name ] ) 
OBJECT_SCHEMA_NAME (object_id [, database_id ] )

4，查看 Column 的 Name

COL_NAME(table_id,column_id)

二，常用 Security Function

1，当前数据库 User 的 Name（Database User）

CURRENT_USER

2，查看数据库 User 的 Name 和 ID（Database User）

USER_NAME ([ id ] )
USER_ID ([ 'user' ] )
USER_SID()
DATABASE_PRINCIPAL_ID ('principal_name' )

3，查看服务器 User 的 Name 和 ID（Server User，即 Login）

--返回 Login ID，参数是 Login Name
SUSER_ID (['login'] )            
--返回 Login Name, 参数是 Login ID,         
SUSER_NAME (  此处含有隐藏内容，登录后即可查看！ )
--返回 SID（security identification），参数是 Login Name
SUSER_SID (['login'] [ , Param2 ] )    
--返回 Login Name，参数是 SID
SUSER_SNAME ([SID] )

4，SID 和 ID 的区别

当创建一个 SQL Server Login 时，从 sys.server_principals 系统视图中，能够看到该 Login 被指定一个 ID 和 SID，在 SQL Server 实例中，ID 和 SID 都是唯一的，不同之处是，ID 标识 Login，将 Login 作为一个 Securable，SID 标识 Login 的 Security Context。一般来说，ID 可以重用，但是 SID 一般是不会重用的。当将同一个 Login 重复创建时，其 ID 可能发生变化，但是其 SID 不变。

不仅 Login 有 ID 和 SID，Database User 也有。当一个 database users 创建时，从 sys.database_principals 中，能够看到该 User 被指定一个 ID 和 SID。在数据库级别，ID 是唯一的。如果 User 是从 SQL Server Login 创建的，那么 User 和 Login 的 SID 相同。详情，请参考《SIDs and IDs》

三，常用全局变量

1，SQL Server 的 Name，ServiceName 和版本

@@SERVERNAME,@@SERVICENAME,@@VERSION

2，返回当前 module 的 ID，module 包括：SP，UDF，Trigger

@@PROCID

--获取当前 Module Name
declare @ObjectName sysname;
select @ObjectName=object_name(@@ProcID)

3，返回当前 Session 的 ID，当前的 RequestID

@@SPID
CURRENT_REQUEST_ID()

4，在当前 Session 中，返回上一条 Query 影响的数据行数量

@@ROWCOUNT 
ROWCOUNT_BIG ()

5，当前 Connection 中，返回已开启，但未结束的事务数量，查看当前事务的 ID，和事务的状态（1，0，-1）

XACT_STATE() 函数返回事务的状态，1 表示有 Active Transaction，0 表示没有 Active Transaction，- 1 表示有 Active Transaction，但是有错误发生导致该事务未被提交。

@@TRANCOUNT 
CURRENT_TRANSACTION_ID( ) 
XACT_STATE()

6，查看当前机器（Host）的名字（Machine Name 和 ID）

HOST_NAME () ,HOST_ID()

四，使用 GZIP algorithm 压缩 数据和解压缩数据

COMPRESS (expression) 
DECOMPRESS (expression)

在插入数据时，压缩数据，压缩之后的数据类型是 varbinary(max)

INSERT INTO player (name, surname, info)  
VALUES (N'Ovidiu', N'Cracium', COMPRESS(N'{"sport":"Tennis","age": 28,"rank":1,"points":15258, turn":17}'));

在查询数据时，解压缩数据，将数据从 varbinary(max)强转为原始类型

SELECT _id, name, surname, datemodified, CAST(DECOMPRESS(info) AS NVARCHAR(MAX)) AS info  
FROM player;

五，调试函数

在 TSQL 中，使用 try 和 catch 编写异常处理代码，在 catch 子句中，使用 debug 函数，能够获取异常信息

--返回发生错误的代码行号（LineNumber）
ERROR_LINE ( ) 
--返回错误号（ErrorNumber）
ERROR_NUMBER ( ) 
@@ERROR 
--返回错误消息（ErrorMessage）
ERROR_MESSAGE ( ) 
--返回发生错误的 SP Name
ERROR_PROCEDURE ( ) 
--返回错误的严重度（Error Severity）
ERROR_SEVERITY ( ) 
--返回错误的状态（Error State）
ERROR_STATE()

在进行调试时，可以以下示例脚本代码，将异常信息记录在数据表中，以便进行代码的故障排除

-- SET XACT_ABORT ON will render the transaction uncommittable when the constraint violation occurs.
SET XACT_ABORT ON;  
  
BEGIN TRY  
    BEGIN TRANSACTION;  
        -- A FOREIGN KEY constraint exists on this table. This statement will generate a constraint violation error.
        DELETE FROM Production.Product  
        WHERE ProductID = 980;  
    -- If the delete operation succeeds, commit the transaction. The CATCH block will not execute.
    COMMIT TRANSACTION;  
END TRY  
BEGIN CATCH  
    -- Test XACT_STATE for 0, 1, or -1.  
    -- If 1, the transaction is committable.  
    -- If -1, the transaction is uncommittable and should be rolled back.
    -- XACT_STATE = 0 means there is no transaction and a commit or rollback operation would generate an error.
  
    -- Test whether the transaction is uncommittable.
    IF (XACT_STATE()) = -1  
    BEGIN  
        --Logging Exception info, as the transaction is in an uncommittable state. Rolling back transaction.
        SELECT  
            ERROR_NUMBER() AS ErrorNumber,  
            ERROR_SEVERITY() AS ErrorSeverity,  
            ERROR_STATE() AS ErrorState,  
            ERROR_PROCEDURE() AS ErrorProcedure,  
            ERROR_LINE() AS ErrorLine,  
            ERROR_MESSAGE() AS ErrorMessage;   
        ROLLBACK TRANSACTION;  
    END;  
    -- Test whether the transaction is active and valid.  
    IF (XACT_STATE()) = 1  
    BEGIN  
        --'The transaction is committable. Committing transaction.'  
        COMMIT TRANSACTION;     
    END;  
END CATCH;  
GO