SQL Server 常用內置函數（built-in）持續整理

本文用於收集在運維中經常使用的系統內置函數，持續整理中

一，常用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（登錄）是伺服器主體（Server Principal），有Name和ID屬性，每一個Login都有一個安全標識（SID）。

--返回Login ID，參數是Login Name
SUSER_ID ( ['login'] )            
--返回Login Name,參數是Login ID,         
SUSER_NAME ( [login_id] )
--返回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 

六，DBCC 命令

1，查看資料庫的隔離級別

DBCC USEROPTIONS

參考文檔：

Security Functions (Transact-SQL) 

Metadata Functions (Transact-SQL)

Configuration Functions (Transact-SQL)

System Functions (Transact-SQL)