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怎么把查詢的數據在新建一個表、 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 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值將在發生任何故障的情況下恢復模式和數據。
修改表數據的sql語句、 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倍。
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現在允許使用它。
sql一個表的字段更新另一個表的值、 Also, many other features are supported now in SQL Server 2016 such as
此外，SQL Server 2016現在還支持許多其他功能，例如
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??
GOYou 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:
sql日志查出的語句都有變量。 之后，我們將通過打開該表上的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,CONSTRAINT [PK_MemoryOptTempTable]??PRIMARY KEY NONCLUSTERED
(ID ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
GOWe 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
)
)
GOIn 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)
GOAnd 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:
sql語句where條件是變量、 為了完成整個場景，我們將創建五個存儲過程，并使用簡單的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 #NorTempTableEND
GOCREATE PROCEDURE dbo.MemoryOptTableTest --- Memory Optimized SQL temp table
AS
BEGIN
SET NOCOUNT ON;INSERT INTO MemoryOptTempTable (First_Name,Last_Name) VALUES ('Sanya','J')END
GOCREATE PROCEDURE dbo.PhysicalTableTest --- Physical SQL temp table
AS
BEGIN
SET NOCOUNT ON;INSERT INTO [PhysicalTempTable] (First_Name,Last_Name) VALUES ('Sanya','J')END
GOCREATE PROCEDURE dbo.VariableTable --- normal variable TableASBEGIN
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
GOCREATE PROCEDURE dbo.MemoryOptVarTable --- Memory Optimized variable TableASBEGIN
SET NOCOUNT ON;DECLARE @MemOptVarTab MemoryOptTabVar ;INSERT @MemOptVarTab (First_Name,Last_Name) VALUES ('Sanya','J')
END
GOTo 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，如下所示：
?
USE SQLShackDemo
GOEXEC dbo.TempDB_TempTableTest
GO 20000WAITFOR DELAY '00:00:05'
GO
EXEC dbo.MemoryOptTableTest
GO 20000WAITFOR DELAY '00:00:05'
GO
EXEC dbo.PhysicalTableTest
GO 20000WAITFOR DELAY '00:00:05'
GO
EXEC dbo.MemoryOptVarTable
GO 20000It is clear from the following PerfMon window that the normal SQL temp table consumes the CPU and I/O resources and took long time to finish compared to the other tables types. You can also derive from the same image that the Memory-Optimized SQL temp table and the Memory-Optimized Variable Table consumed the minimum I/O and CPU resources compared with the rest types of tables:
從下面的PerfMon窗口中可以清楚地看到，與其他表類型相比，普通SQL臨時表占用了CPU和I / O資源，并且花了很長時間完成。 您還可以從同一張圖得出，與其余表類型相比，內存優化SQL臨時表和內存優化的變量表消耗的I / O和CPU資源最少。
sql語句給變量賦值、
The previous script can be modified to show the duration of each stored procedure’s execution as below:
可以修改前面的腳本，以顯示每個存儲過程的執行時間，如下所示：
?
USE SQLShackDemo
GO
CREATE TABLE #StartEndTime(TableType nvarchar(100),StartTime datetime, EndTime Datetime)
INSERT INTO #StartEndTime VALUES('TempTable', GETDATE(),NULL)
GO
EXEC dbo.TempDB_TempTableTest
GO 20000??UPDATE #StartEndTime SET EndTime =GETDATE() WHERE TableType ='TempTable'WAITFOR DELAY '00:00:05'
GO INSERT INTO #StartEndTime VALUES('MemoryOptTable', GETDATE(),NULL)
GO
EXEC dbo.MemoryOptTableTest
GO 20000UPDATE #StartEndTime SET EndTime =GETDATE() WHERE TableType ='MemoryOptTable'WAITFOR DELAY '00:00:05'
GO INSERT INTO #StartEndTime VALUES('PhysicalTable', GETDATE(),NULL)
GO
EXEC dbo.PhysicalTableTest
GO 20000UPDATE #StartEndTime SET EndTime =GETDATE() WHERE TableType ='PhysicalTable'WAITFOR DELAY '00:00:05'
GO INSERT INTO #StartEndTime VALUES('VariableTable', GETDATE(),NULL)
GO
EXEC dbo.MemoryOptVarTable
GO 20000UPDATE #StartEndTime SET EndTime =GETDATE() WHERE TableType ='VariableTable'WAITFOR DELAY '00:00:05'
GOINSERT INTO #StartEndTime VALUES('Memory Opt VariableTable', GETDATE(),NULL)
GO
EXEC dbo.MemoryOptVarTable
GO 20000UPDATE #StartEndTime SET EndTime =GETDATE() WHERE TableType ='Memory Opt VariableTable'SELECT TableType , DATEDIFF (SS ,StartTime,EndTime) AS Duration FROM #StartEndTimeThe result of the previous script will show us that the SP that used the normal SQL temp table is taking the longest execution time compared to the ones using other tables types, and the ones that use the Memory-Optimized SQL temp table and the Memory-Optimized required small period of time to insert 20000 records. Although the SP that uses the Variable table required the minimum time, it consumes the I\O and CPU resources:
上一個腳本的結果將向我們顯示，與使用其他表類型的SP，使用內存優化SQL臨時表和Memory-Optim的SP相比，使用普通SQL臨時表的SP執行時間最長。優化所需的短時間來插入20000條記錄。 盡管使用變量表的SP需要最少的時間，但它會消耗I \ O和CPU資源：
sql如何定義變量，
In-Memory OLTP is a new feature, introduced in SQL Server 2014 and enhanced in SQL Server 2016 by addressing most of the limitations, that stores the data directly to the memory, eliminating the locks and latches occurred during the reading process and providing the best data access performance. From the testing scenario we performed, it is clear that using the SCHEMA_ONLY Memory-Optimized Table and the Memory-Optimized Variable Table excelled on the rest of tables types in having the best performance that can be seen by the minimum CPU and I/O resources consumption and the minimum execution time required to fill these two tables types compared with the other tables, making it the best alternatives to normal SQL temp tables and variable tables.
內存中OLTP是一項新功能，在SQL Server 2014中引入并在SQL Server 2016中得到了增強，通過解決大多數限制，該功能將數據直接存儲到內存中，消除了讀取過程中發生的鎖和閂鎖，并提供了最佳功能數據訪問性能。 從我們執行的測試方案來看，很明顯，使用SCHEMA_ONLY內存優化表和內存優化變量表在其余表類型上表現出色，這是由最少的CPU和I / O資源所能看到的最佳性能與其他表相比，填充這兩個表類型所需的資源消耗和最短執行時間使其成為普通SQL臨時表和變量表的最佳替代方案。
翻譯自: https://www.sqlshack.com/using-memory-optimized-tables-to-replace-sql-server-temp-tables-and-table-variables/
sql語句用變量替換表名
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间：8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态
![[Wayland] (二) 代码结构 [FW]](https://images2015.cnblogs.com/blog/753004/201607/753004-20160720100837341-320217114.png)
![[大数据行业应用发展前景分析] 阿里潘永花报告：大数据产业将成为新的煤和石油介绍...](http://avatar.aliresearch.com/Uploads/Editor/2017-02-15/58a3b29492dff.png)
