sql语句⽤变量替换表名_使⽤内存优化表替换SQL临时表和表
变量
sql语句⽤变量替换表名
TempDB usage can be considered as a performance bottleneck for workloads that use SQL temp tables and table variables intensively resulted in heavy IO usage. A valuable alternatives for the SQL temp table and table variable are SCHEMA_ONLY Memory-Optimized tables and the Memory-optimized Table Variable, where the data will be completely stored in the memory without the need to touch the TempDB database, providing the best data access performance. SCHEMA_ONLY Memory-Optimized table and the Memory-optimized Table Variable are stored only in the memory with no related component in the disk. It involves no IO activity or TempDB utilization. It can also participate in the transactions without the need to log the transactions.
对于使⽤SQL临时表和⼤量使⽤表变量导致IO⼤量使⽤的⼯作负载，可以将TempDB使⽤视为性能瓶颈。 SCHEMA_ONLY内存优化表和内存优化表变量是SQL临时表和表变量的宝贵替代品，其中数据将完全存储在内存中，⽽⽆需触摸TempDB数据库，从⽽提供最佳的数据访问性能。 SCHEMA_ONLY内存优化表和内存优化表变量仅存储在内存中，⽽磁盘中没有相关组件。 它不涉及IO活动或TempDB利⽤率。
它也可以参与交易，⽽⽆需记录交易。
In this article, we will prove practically that the SCHEMA_ONLY Memory-Optimized Table and the Memory- Optimized Variable Tables are the best replacements for the SQL temp tables and variable tables with better CPU, IO and execution time performance.
在本⽂中，我们将实际证明SCHEMA_ONLY内存优化表和内存优化变量表是SQL临时表和具有更好CPU，IO和执⾏时间性能的变量表的最佳替代。
SQL Server In-Memory OLTP, also known as ‘Hekaton’, is a new in-memory processing technology that was introduced in SQL Server 2014, integrated into the SQL Server Database Engine and optimized for an Online Transaction Processing (OLTP) workload.
SQL Server内存中OLTP，也称为“ Hekaton”，是SQL Server 2014中引⼊的⼀种新的内存中处理技术，已集成到SQL Server数据库引擎中，并针对联机事务处理（OLTP）⼯作负载进⾏了优化。
In-Memory OLTP introduces three new data structures;
内存中OLTP引⼊了三种新的数据结构：
Memory-Optimized Tables that store its data into memory in multiple versions without causing any locks or latches on the data, as it reads from the underlying disk only during the database recovery, achieving the best performance and the most efficient data access. 内存优化表 ，可以将其数据以多种版本存储到内存中，⽽不会导致数据上的任何锁或闩锁，因为它仅在数据库恢复期间从基础磁盘读取数据，从⽽实现了最佳性能和最有效的数据访问。
The second data structure is Natively-Compiled Stored Procedures that are compiled to native code (DLL) when they is created and interact only with Memory-Optimized Tables, where any call to that Natively-Compiled Stored Procedure is a call to its related DLL, providing the most efficient query processing and business logic execution.
第⼆种数据结构是本机编译的存储过程 ，该过程在创建时会编译为本机代码（DLL），并且仅与内存优化表进⾏交互，其中对该本地编译的存储过程的任何调⽤都是对其相关DLL的调⽤，提供最有效的查询处理和业务逻辑执⾏。
Memory-Optimized Table Variable that is created using the Memory Optimized Table Type. SQL Server generates a DLL for each created Memory-Optimized Table Type that includes the functions required for accessing the indexes and retrieving data from the related Memory-Optimized Table Vari
able. When that table variable is created using that table type, an instance of the table type components will be created on that table. 变量 。 SQL Server为每个创建的内存优化表类型⽣成⼀个DLL，其中包括访问索引和从相关的内存优化表变量中检索数据所需的功能。 当使⽤该表类型创建该表变量时，将在该表上创建表类型组件的实例。
组态 (Configuration)
Memory-Optimized tables can be configured as Durable to recover data in the case of any failure from the transaction logs, which is the default setting, or configured as Non-Durable where the data will not be recovered in the case of failure as the memory buffers will be flushed. The durable setting is used for tables with many updates on daily basis, where any change is required. And the Non-Durable setting is useful in the case of the staging tables that are used for temporary calculations and truncated on daily basis. Setting the Durability option to SCHEMA_ONLY value will recover the table schema only in the case of any failure, where the SCHEMA_AND_DATA Durability value will recover both the schema and the data in the case of any failure.
内存优化表可以配置为“ 持久”以在事务⽇志出现任何故障的情况下恢复数据（这是默认设置），也可以配置为“ ⾮持久” ，其中在发⽣故障的情况下⽆法恢复数据作为内存缓冲区将被刷新。 持久设置⽤于
每天需要多次更新的表，需要进⾏任何更改。 对于⽤于临时计算并每天被截断的登台表，“⾮持久”设置很有⽤。 将Durability选项设置为SCHEMA_ONLY值将仅在发⽣任何故障的情况下恢复表模式，⽽SCHEMA_AND_DATA Durability值将在发⽣任何故障的情况下恢复模式和数据。
Another option that should be set when defining non-clustered indexes, while creating the Memory-Optimized Tables, taking into consideration that the clustered index is not supported in the Memory-Optimized Tabled, is the BUCKET_COUNT. The Microsoft recommended value for that option is between 1.5 to 2 times the estimated number of unique values for the column involved in the non-clustered index.
考虑到内存优化表中不⽀持聚集索引，在创建⾮内存索引时创建内存优化表时应设置的另⼀个选项是BUCKET_COUNT 。 Microsoft对于该选项的建议值是⾮聚集索引中涉及的列的唯⼀值的估计数量的1.5到2倍。
局限性 (Limitations)
There are some Memory-Optimized OLTP features limitations in SQL Server 2014 that are resolved in SQL Server 2016, such as the supported collation for the string columns involved in the non-clustered index, where the only supported collation was the *_BIN2 collation. Now SQL Server 2016
supports all collation types for that kind of columns.
SQL Server 2014中有⼀些内存优化的OLTP功能限制，SQL Server 2016中已解决这些限制，例如，⾮聚集索引所涉及的字符串列的受⽀持的排序规则，其中唯⼀受⽀持的归类是* _BIN2排序规则。 现在，SQL Server 2016⽀持此类列的所有排序规则类型。
Another limitation in SQL Server 2014 is that you can’t ALTER any Memory-Optimized Table after creating it, but it is allowed now in SQL Server 2016.
SQL Server 2014中的另⼀个限制是创建表后不能更改任何内存优化表，但是SQL Server 2016现在允许使⽤它。
Also, many other features are supported now in SQL Server 2016 such as
此外，SQL Server 2016现在还⽀持许多其他功能，例如
ALTER the Natively-Compiled Stored procedures,
更改本机编译的存储过程，
Transparent Data Encryption,
透明数据加密
DML triggers in the Memory-Optimized Tables,
内存优化表中的DML触发器，
Natively-compiled scalar user-defined functions,
本机编译的标量⽤户定义函数，
Indexes on null-able columns,
可空列的索引，
Parallel plan execution for the operations that access the Memory Optimized tables,
访问内存优化表的操作的并⾏计划执⾏，
the use of LEFT and RIGHT OUTER JOIN, UNION and UNION ALL, SELECT DISTINCT,
使⽤LEFT和RIGHT OUTER JOIN，UNION和UNION ALL，SELECT DISTINCT，
OR and NOT statements,
OR和NOT语句，
Nested SELECT statements and stored procedures calling and Large Objects (LOB) usage such as VARCHAR(MAX), NVARCHAR(MAX) or VARBINARY(MAX).
嵌套的SELECT语句和存储过程调⽤以及⼤对象（LOB）⽤法，例如VARCHAR（MAX），NVARCHAR（MAX）或
VARBINARY（MAX）。
The Memory-Optimized OLTP feature is available in SQL Server 2014 Enterprise Edition only, but in SQL Server 2016 SP1, it is available now in the Standard, Web and Express Editions too.
内存优化的OLTP功能仅在SQL Server 2014 Enterprise Edition中可⽤，但在SQL Server 2016 SP1中，现在在Standard
Edition，Web Edition和Express Edition中也可⽤。
In this article’s demo we will compare the performance of using five tables types; the SCHEMA_ONLY Memory-Optimized Table, the normal SQL temp table, the physical table, the normal variable table and the Memory- Optimized Variable Table.
在本⽂的演⽰中，我们将⽐较使⽤五种表类型的性能。 SCHEMA_ONLY内存优化表，常规SQL临时表，物理表，常规变量表和内存优化变量表。
We will start our demo by preparing the SQLShackDemo test database to host Memory-Optimized tables. This can be achieved by adding a new filegroup that will contain the Memory_Optimized_Data and create a data file under that filegroup. Once the filegroup and database data file created, we will enable the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT option that is used to lessen the need to have table hints on the Memory-Optimized Tables:
我们将通过准备SQLShackDemo测试数据库来托管内存优化表来开始演⽰。 这可以通过添加⼀个新⽂件组来实现，该⽂件组将包含Memory_Optimized_Data并在该⽂件组下创建⼀个数据⽂件。 创建⽂件组和数据库数据⽂件后，我们将启⽤
MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT选项，该选项⽤于减少在内存优化表上具有表提⽰的需求：
USE SQLShackDemo
GO
ALTER DATABASE SQLShackDemo ADD FILEGROUP MemoryOpt_FG CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE SQLShackDemo ADD FILE (name='MemoryOptDataF',
filename='D:\Data\MemoryOptDataF') TO FILEGROUP MemoryOpt_FG
ALTER DATABASE SQLShackDemo SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
GO
You can verify that the Memory-Optimized filegroup is added or add it from the Filegroups page of the Database Properties window as follows:
您可以验证是否已添加“内存优化”⽂件组，或从“数据库属性”窗⼝的“⽂件组”页⾯中添加它，如下所⽰：
After that, we will create the Memory-Optimized Table by turning on the MEMORY_OPTIMIZED option on that table and set the Durability to SCHEMA_ONLY as mentioned previously to simulate the case of SQL temp table, where the table schema will be recovered only in the case of restart or crash:
之后，我们将通过打开该表上的MEMORY_OPTIMIZED选项来创建“内存优化表”，并将持久性设置为SCHEMA_ONLY（如前所述），以模拟SQL临时表的情况，其中表模式仅在以下情况下才能恢复：重新启动或崩溃：
USE SQLShackDemo
GO
CREATE TABLE [MemoryOptTempTable]
(
[ID] INT IDENTITY(1,1) NOT NULL ,
[First_Name] nvarchar(10) NULL,
[FLast_Name] nvarchar(10) NULL,
sql语句优化方式CONSTRAINT [PK_MemoryOptTempTable]  PRIMARY KEY NONCLUSTERED
(
ID ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
GO
We will create also a normal physical table that will be used to store the data temporarily:
我们还将创建⼀个普通的物理表，该表将⽤于临时存储数据：
USE SQLShackDemo
GO
CREATE TABLE [PhysicalTempTable]
(
[ID] INT IDENTITY(1,1) NOT NULL ,
[First_Name] nvarchar(10) NULL,
[Last_Name] nvarchar(10) NULL,
CONSTRAINT [PK_PhysicalTempTable]  PRIMARY KEY NONCLUSTERED
(
ID ASC
)
)
GO
In order to create the Memory-Optimized Variable Table, we should create a Memory-Optimized Table Type using the CREATE TYPE statement as below:
为了创建内存优化的变量表，我们应该使⽤CREATE TYPE语句创建⼀个内存优化的表类型，如下所⽰：
USE SQLShackDemo
GO
CREATE TYPE MemoryOptTabVar as TABLE (
[ID] INT IDENTITY(1,1) NOT NULL ,
[First_Name] nvarchar(10) NULL,
[Last_Name] nvarchar(10) NULL,
INDEX IX_MemoryOptTabVar_ID NONCLUSTERED (ID)
) WITH (MEMORY_OPTIMIZED = ON)
GO
And to complete the full scenario, we will create five stored procedures, with simple INSERT statements to fill the five tables types mentioned previously. In the case of the normal SQL temp table, variable table and Memory-Optimized Variable Table, the table definition will be included within the stored procedure itself as in the below script:
为了完成整个场景，我们将创建五个存储过程，并使⽤简单的INSERT语句填充前⾯提到的五种表类型。 对于普通SQL临时表，变量表和内存优化的变量表，表定义将包含在存储过程本⾝中，如以下脚本所⽰：
USE SQLShackDemo
GO
CREATE PROCEDURE dbo.TempDB_TempTableTest --- Normal SQL temp table
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #NorTempTable (ID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, [First_Name]
nvarchar(10) NULL,  [Last_Name] nvarchar(10) NULL)
INSERT INTO #NorTempTable (First_Name,Last_Name) VALUES ('Sanya','J')
DROP TABLE #NorTempTable
END
GO
CREATE PROCEDURE dbo.MemoryOptTableTest --- Memory Optimized SQL temp table
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO MemoryOptTempTable (First_Name,Last_Name) VALUES ('Sanya','J')
END
GO
CREATE PROCEDURE dbo.PhysicalTableTest --- Physical SQL temp table
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [PhysicalTempTable] (First_Name,Last_Name) VALUES ('Sanya','J')
END
GO
CREATE PROCEDURE dbo.VariableTable --- normal variable Table
AS
BEGIN
SET NOCOUNT ON;
DECLARE @variableTab TABLE (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, [First_Name]
nvarchar(10) NULL,  [Last_Name] nvarchar(10) NULL) ;
INSERT  @variableTab (First_Name,Last_Name) VALUES ('Sanya','J')
END
GO
CREATE PROCEDURE dbo.MemoryOptVarTable --- Memory Optimized variable Table
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MemOptVarTab MemoryOptTabVar ;
INSERT @MemOptVarTab (First_Name,Last_Name) VALUES ('Sanya','J')
END
GO
To perform our testing, we will execute the five stored procedures and use it to insert 20000 records each time, leaving 5 seconds delay between each execution to make it easy for us to get the performance difference between it. We will use the PerfMon performance counters monitoring tool to check the performance of these tables, where we will monitor the Processor and Log Bytes Flushed/Sec counters to get the CPU and I/O of each execution as follows:
为了执⾏测试，我们将执⾏五个存储过程，并使⽤它每次插⼊20000条记录，每次执⾏之间留有5秒的延迟，以使我们能够轻松地获得它们之间的性能差异。 我们将使⽤PerfMon性能计数器监视⼯具来检
查这些表的性能，在这⾥我们将监视处理器和⽇志字节刷新/秒计数器，以获取每次执⾏的CPU和I / O，如下所⽰：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。