sql 自定义函数 示例_SQL Server SESSION_CONTEXT（）函数与示例

sql 自定义函数 示例

This article explores the SQL Server session context function, SESSION_CONTEXT() and performs its comparison with the function, CONTEXT_INFO().

本文探讨了SQL Server会话上下文函数SESSION_CONTEXT（）并与函数CONTEXT_INFO（）进行比较。

介绍 (Introduction)

Developers are familiar with the mechanism to store and retrieve session in a programming language such as ASP.Net. Before SQL Server 2016, we use the CONTEXT_INFO function. We retrieve session context values for all active sessions using this CONTEXT_INFO function.

开发人员熟悉以ASP.Net等编程语言存储和检索会话的机制。 在SQL Server 2016之前，我们使用CONTEXT_INFO函数。 我们使用此CONTEXT_INFO函数检索所有活动会话的会话上下文值。

• CONTEXT_INFO returns a single binary value. We need to convert this binary value into a compatible data format
  CONTEXT_INFO返回单个二进制值。 我们需要将此二进制值转换为兼容的数据格式
• The binary value is limited to 128 bytes per connection
  每个连接的二进制值限制为128个字节

SET CONTEXT_INFO 0x123456
select CONTEXT_INFO()

• Users can overwrite value for it anytime. It might lead to security, audit-related issues
  用户可以随时覆盖它的值。 它可能会导致安全性，审计相关问题
• We cannot use this function in the Azure SQL database
  我们不能在Azure SQL数据库中使用此功能
• Users with VIEW SERVER STATE permission and required SELECT permission can use DMV’s sys.dm_exec_sessions and sys.dm_exec_requests to retrieve context value for the session id
  具有VIEW SERVER STATE权限和必需的SELECT权限的用户可以使用DMV的sys.dm_exec_sessions和sys.dm_exec_requests检索会话ID的上下文值

Select CONTEXT_INFO as info from sys.dm_exec_sessions
where session_id=63
    
Select CONTEXT_INFO as info from sys.dm_exec_requests
where session_id=63

SQL Server 2016 introduced a new built-in-function SESSION_CONTEXT() as improvement over the existing CONTEXT_INFO function. It uses key-value pairs for storing the session data. It works with the SQL Azure database as well.

SQL Server 2016引入了新的内置函数SESSION_CONTEXT（）作为对现有 CONTEXT_INFO函数的改进 。 它使用键值对存储会话数据。 它也可以与SQL Azure数据库一起使用。

We set these key-value pairs using the stored procedure sp_set_session_context.

我们使用存储过程sp_set_session_context设置这些键值对。

SQL Server会话上下文函数SESSION_CONTEXT（）的语法 (The Syntax of SQL Server session context function SESSION_CONTEXT())

SESSION_CONTEXT(N'key')

sp_set_session_context的语法 (The syntax of sp_set_session_context)

sp_set_session_context [ @key= ] N'key', [ @value= ] 'value'  ,
[@read_only = ] { 0 | 1 } ]

It requires the following parameters.

它需要以下参数。

• @Key: It is the key that we set. It is of SYSNAME type and can be up to 256 bytes @Key：这是我们设置的密钥。 它是SYSNAME类型，最多可以包含256个字节
• @value: It is a value for the specified key. Its type is sql_variant. Its maximum size is 8,000 bytes
• @value：它是指定键的值。 它的类型是sql_variant。 最大大小为8,000字节
• @read_only: We can specify value for this flag as 0,1 @read_only：我们可以将此标志的值指定为0,1
  • 1: We cannot change the key in the logical connection 1：我们无法更改逻辑连接中的密钥
  • 0: We can change the key value if read_only is set to zero 0：如果read_only设置为零，我们可以更改键值

Let’s explore the SESSION_CONTEXT() using various examples.

让我们使用各种示例来探索SESSION_CONTEXT（）。

示例1使用SESSION_CONTEXT（）进行密钥检索 (Example 1 Use SESSION_CONTEXT() for a key retrieval)

In this example, we configure a CustomerID key and set its value 101. Later, we use the SQL Server session context function SESSION_CONTEXT() and retrieve the key for it.

在此示例中，我们配置一个CustomerID密钥并将其值设置为101。稍后，我们使用SQL Server会话上下文函数SESSION_CONTEXT（）并为其检索密钥。

EXEC sp_set_session_context 'CustomerID', 101
SELECT SESSION_CONTEXT(N'CustomerID') AS CustomerID

示例2：更新会话中键的值 (Example 2: Update value for a key in the session)

In this example, we update a key value in the same session. For updating value, we do not require any other stored procedure. We execute sp_set_session_context with a different value for an existing key.

在此示例中，我们在同一会话中更新键值。 为了更新值，我们不需要任何其他存储过程。 我们对现有键使用不同的值执行sp_set_session_context。

EXEC sp_set_session_context 'CustomerID', 101
 
SELECT SESSION_CONTEXT(N'CustomerID') AS CustomerID
    
EXEC sp_set_session_context 'CustomerID', 999
    
SELECT SESSION_CONTEXT(N'CustomerID') AS CustomerID

In the output above, we see the updated value in the 2^nd SESSION_CONTEXT() statement output.

在上面的输出中，我们在^第二个 SESSION_CONTEXT（）语句输出中看到更新后的值。

示例3：更新会话中只读键的值 (Example 3: Update value for a read-only key in the session)

System procedure sp_set_session_context accepts three parameters, as shown above in the syntax. We have not used the third parameter @read_only in the previous example. By default, SQL Server uses the default value 0 for the @read_only parameter. Let’s set it to 1 using the following query.

系统过程sp_set_session_context接受三个参数，如上面的语法所示。 在上一个示例中，我们没有使用第三个参数@read_only。 默认情况下，SQL Server对@read_only参数使用默认值0。 让我们使用以下查询将其设置为1。

EXEC sp_set_session_context @key='CustomerID', @value=101, @read_only=1
 
SELECT SESSION_CONTEXT(N'CustomerID') AS CustomerID

It sets the CustomerID key value to 101. Now, let’s try to update the key value. We get the message that it cannot set key value in the session context because it is set to read-only as shown in the following screenshot.

它将CustomerID键值设置为101。现在，让我们尝试更新键值。 我们收到一条消息，它无法在会话上下文中设置键值，因为它设置为只读，如以下屏幕截图所示。

示例4：检查SQL Server会话上下文函数SESSION_CONTEXT（）的输出，但不设置键的值 (Example 4: Check the output of SQL Server session context function SESSION_CONTEXT() without setting value for the key)

In this example, we do not define any key using sp_set_session_context procedure. It means that the key is not valid. If we try to access the key-value using SESSION_CONTEXT(), it always returns a NULL value.

在此示例中，我们没有使用sp_set_session_context过程定义任何键。 这意味着密钥无效。 如果尝试使用SESSION_CONTEXT（）访问键值，则该键值始终返回NULL值。

USE [master]
GO
SELECT SESSION_CONTEXT(N'MySystem')

示例5：定义不带N前缀的键 (Example 5: Define a key without N prefix)

In this example, we define a CutomerID key and set its value to 101

在此示例中，我们定义一个CutomerID密钥并将其值设置为101

EXEC sp_set_session_context @key='CustomerID', @value=101, @read_only=1

Let’s try to get the session context value without specifying the N prefix in the key name.

让我们尝试获取会话上下文值，而不在键名中指定N前缀。

SELECT SESSION_CONTEXT('CustomerID') AS CustomerID

It returns an error message about invalid data type varchar().

它返回有关无效数据类型varchar（）的错误消息。

It clearly shows that the SQL Server session context function SESSION_CONTEXT() function requires a NVARCHAR data type for its key.

它清楚地表明，SQL Server会话上下文函数SESSION_CONTEXT（）函数的键要求使用NVARCHAR数据类型。

示例6：在存储过程中使用SQL Server会话上下文函数SESSION_CONTEXT（） (Example 6: Use of SQL Server session context function SESSION_CONTEXT() in a stored procedure)

In the previous example, we defined a key and its value directly in a session. In this example, we will use the session context in a stored procedure.

在前面的示例中，我们直接在会话中定义了键及其值。 在此示例中，我们将在存储过程中使用会话上下文。

This procedure checks the session context for a key Mango. We require the output in a VARCHAR data type, so we use the SQL CONVERT function to change the data type from NVARCHAR to VARCHAR.

此过程检查会话上下文中是否存在关键芒果。 我们要求输出为VARCHAR数据类型，因此我们使用SQL CONVERT函数将数据类型从NVARCHAR更改为VARCHAR。

CREATE PROCEDURE [CheckSecurityContext] AS
BEGIN
    DECLARE @Fruit varchar(20) = CONVERT(varchar(20), SESSION_CONTEXT(N'FRUIT'))
    SELECT CheckSecurityContext = @Fruit
END

Execute this stored procedure and it returns NULL value in the output because we did not specify value for the key using the sp_set_session_context stored procedure.

执行此存储过程，由于未使用sp_set_session_context存储过程为键指定值，因此它在输出中返回NULL值。

Now, execute the stored procedure again after setting the key and its value.

现在，在设置键及其值之后再次执行存储过程。

EXEC sp_set_session_context @key = N'Fruit', @value = N'Mango'
 
EXEC CheckSecurityContexts

We get the expected output, as shown below.

我们得到预期的输出，如下所示。

The key value is persistent throughout the session. We can execute the stored procedure multiple times, and it returns the same value in each execution.

关键值在整个会话中都是持久的。 我们可以多次执行存储过程，并且每次执行都返回相同的值。

If we execute this procedure in a different session, it returns NULL value because we did not set the key value for this session.

如果我们在不同的会话中执行此过程，则它会返回NULL值，因为我们没有为此会话设置键值。

These keys are independent in different sessions. For example, in the following screenshot, we have different values for the fruit key in session 1 and session 2.

这些密钥在不同的会话中是独立的。 例如，在以下屏幕截图中，我们在会话1和会话2中具有不同的水果键值。

We retrieve the session context in the particular session and get the key-value set in that session only. It does not override from the other session.

我们检索特定会话中的会话上下文，并仅获取该会话中设置的键值。 它不会覆盖其他会话。

SQL Server会话上下文中键和值对的限制 (Limitation of key and values pairs in the SQL Server session context)

As highlighted at the beginning of this article, we have the following limitations for the key-value pairs.

如本文开头所述，键值对具有以下限制。

• Key can hold a maximum of 256 bytes (128 Unicode characters)
  键最多可容纳256个字节（128个Unicode字符）
• A value can hold up to 8000 bytes
  一个值最多可容纳8000个字节
• The total size of a key-value pair in the security context cannot exceed beyond 1 MB
  安全上下文中的键值对的总大小不能超过1 MB

These limitations are in SQL Server 2019. We can have different values for it in SQL Server 2016 and 2017.

这些限制在SQL Server 2019中。在SQL Server 2016和2017中，我们可以具有不同的值。

Let’s see these restrictions using examples. In the following query, we use a REPLICATE() function to replicate the value A 129 times in the @text variable.

让我们使用示例查看这些限制。 在以下查询中，我们使用REPLICATE（）函数在@text变量中将值A复制129次。

DECLARE @text NVARCHAR(129)= REPLICATE(N’A’, 129);

DECLARE @text NVARCHAR（129）= REPLICATE（N'A'，129）;

DECLARE @text NVARCHAR(129)= REPLICATE(N'A', 129);
EXEC sys.sp_set_session_context 
     @key = @text, 
     @value = N'A';

We get the following error message because the size of the key exceeded 256 bytes.

因为密钥的大小超过256个字节，所以我们收到以下错误消息。

Similarly, the total size of the key and value in a session cannot exceed 1 MB limit. The following code sets a few key and value until it is in 1 MB range. Once it crosses the limit, you get the highlighted error message shown below.

同样，会话中键和值的总大小不能超过1 MB的限制。 下面的代码设置一些键和值，直到它在1 MB范围内。 一旦超过限制，您将获得如下所示的突出显示的错误消息。

DECLARE @Replicate NVARCHAR(4000)= REPLICATE(N'X', 4000), @i INT= 1, @Key SYSNAME;
WHILE @i <= 60
    BEGIN
        SET @Key = N'Iteraction' + CONVERT(VARCHAR(20), @i);
        EXEC sys.sp_set_session_context 
             @key = @Key, 
             @value = @Replicate;
        SET @i+=1;
    END;  

监视SQL Server会话上下文的会话上下文内存使用情况 (Monitor session context memory usage for SQL Server session context)

We can use the dynamic management view sys.dm_os_memory_cache_counters to monitor cache memory usage from all sessions.

我们可以使用动态管理视图sys.dm_os_memory_cache_counters监视所有会话中的缓存内存使用情况。

SELECT *
FROM sys.dm_os_memory_cache_counters
WHERE TYPE = 'CACHESTORE_SESSION_CONTEXT';

Let’s close all sessions and specify a key, value using the following query.

让我们关闭所有会话，并使用以下查询指定键值。

EXEC sp_set_session_context @key = N'Fruit', @value = N'Mango'
    
Select SESSION_CONTEXT(N'Fruit')

Now, execute the DMV and check for the cache memory usage. The below screenshot shows currently we have a single key, and it is using 8 KB.

现在，执行DMV并检查高速缓存的使用情况。 下面的屏幕截图显示了当前我们只有一个密钥，它正在使用8 KB。

• pages_kb: It is the amount for the memory (in KB) allocated in the cache pages_kb：这是缓存中分配的内存量（以KB为单位）
• entries_count: It is the number of entries in the cache entry_count：这是高速缓存中的条目数

Now, execute the following query in a new query window for generating 10 keys and values using a WHILE loop.

现在，在新的查询窗口中执行以下查询，以使用WHILE循环生成10个键和值。

DECLARE @Replicate NVARCHAR(4000)= REPLICATE(N'X', 4000), @i INT= 1, @Key SYSNAME;
WHILE @i <= 10
    BEGIN
        SET @Key = N'Iteraction' + CONVERT(VARCHAR(20), @i);
        EXEC sys.sp_set_session_context 
             @key = @Key, 
             @value = @Replicate;
        SET @i+=1;
    END;

Once we query the DMV, it shows memory usage from both sessions. The first session is using one key while another session is using 10 keys.

一旦查询DMV，它就会显示两个会话的内存使用情况。 第一个会话使用一个键，而另一个会话使用10个键。

结论 (Conclusion)

In this article, we explored the session context function SESSION_CONTEXT() to manage the session variable in SQL Server. It is an enhancement over CONTEXT_INFO() and available from SQL Server 2016 onwards. We can also explore its use cases with Row-level security feature in SQL Server.

在本文中，我们探讨了会话上下文函数SESSION_CONTEXT（）来管理SQL Server中的会话变量。 它是对CONTEXT_INFO（）的增强，可从SQL Server 2016开始使用。 我们还可以使用SQL Server中的行级安全功能来探索其用例。

翻译自: https://www.sqlshack.com/sql-server-session_context-function-with-examples/

sql 自定义函数 示例