6,129
社区成员
发帖
与我相关
我的任务
分享
--======================================================
-- Add MEMORY_OPTIMIZED_DATA Filegroup and Create Memory Optimized Table Template
-- Use the Specify Values for Template Parameters command (Ctrl-Shift-M) to fill in the parameter values below.
-- This template adds a MEMORY_OPTIMIZED_DATA filegroup to the database, and creates a memory optimized table and indexes on the memory optimized table.
-- The database must have a MEMORY_OPTIMIZED_DATA filegroup before the memory optimized table can be created.
--======================================================
USE ppqpks
GO
--Add MEMORY_OPTIMIZED_DATA filegroup to the database.
ALTER DATABASE ppqpks
ADD FILEGROUP mem_database_filegroup CONTAINS MEMORY_OPTIMIZED_DATA
--Add file to the MEMORY_OPTIMIZED_DATA filegroup.
ALTER DATABASE ppqpks
ADD FILE
( NAME = mem_database_filegroup_file1,
FILENAME = N'e:\Database\mem_database_1')
TO FILEGROUP mem_database_filegroup
--Drop table if it already exists.
IF OBJECT_ID('dbo.mtbKeyValue','U') IS NOT NULL
DROP TABLE dbo.mtbKeyValue
GO
--Create memory optimized table and indexes on the memory optimized table.
CREATE TABLE dbo.mtbKeyValue
(
fid int NOT NULL,
fkey nvarchar(100) NOT NULL,
fvalue nvarchar(3900) NOT NULL INDEX index_mtbkeyvalue_fvalue NONCLUSTERED (fvalue),
CONSTRAINT PK_sample_memoryoptimizedtable PRIMARY KEY NONCLUSTERED (fid),
-- See SQL Server Books Online for guidelines on determining appropriate bucket count for the index
INDEX hash_index_memtable_fkey HASH (fkey) WITH (BUCKET_COUNT = 131072)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
报错:
消息 12328,级别 16,状态 102,第 28 行
内存优化表上的索引 不支持未使用 *_BIN2 排序规则的字符列上的索引。
消息 1750,级别 16,状态 0,第 28 行
无法创建约束或索引。请参阅前面的错误。
[/quote]
sqlserver内存表和oracle12c的内存表选件使用上差太多档次了。
oracle的内存表使用几乎没有任何限制,几乎和普通表一样。一个简单的命令转换即可。而sqlserver的简直就没法用。
--======================================================
-- Add MEMORY_OPTIMIZED_DATA Filegroup and Create Memory Optimized Table Template
-- Use the Specify Values for Template Parameters command (Ctrl-Shift-M) to fill in the parameter values below.
-- This template adds a MEMORY_OPTIMIZED_DATA filegroup to the database, and creates a memory optimized table and indexes on the memory optimized table.
-- The database must have a MEMORY_OPTIMIZED_DATA filegroup before the memory optimized table can be created.
--======================================================
USE ppqpks
GO
--Add MEMORY_OPTIMIZED_DATA filegroup to the database.
ALTER DATABASE ppqpks
ADD FILEGROUP mem_database_filegroup CONTAINS MEMORY_OPTIMIZED_DATA
--Add file to the MEMORY_OPTIMIZED_DATA filegroup.
ALTER DATABASE ppqpks
ADD FILE
( NAME = mem_database_filegroup_file1,
FILENAME = N'e:\Database\mem_database_1')
TO FILEGROUP mem_database_filegroup
--Drop table if it already exists.
IF OBJECT_ID('dbo.mtbKeyValue','U') IS NOT NULL
DROP TABLE dbo.mtbKeyValue
GO
--Create memory optimized table and indexes on the memory optimized table.
CREATE TABLE dbo.mtbKeyValue
(
fid int NOT NULL,
fkey nvarchar(100) NOT NULL,
fvalue nvarchar(3900) NOT NULL INDEX index_mtbkeyvalue_fvalue NONCLUSTERED (fvalue),
CONSTRAINT PK_sample_memoryoptimizedtable PRIMARY KEY NONCLUSTERED (fid),
-- See SQL Server Books Online for guidelines on determining appropriate bucket count for the index
INDEX hash_index_memtable_fkey HASH (fkey) WITH (BUCKET_COUNT = 131072)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
报错:
消息 12328,级别 16,状态 102,第 28 行
内存优化表上的索引 不支持未使用 *_BIN2 排序规则的字符列上的索引。
消息 1750,级别 16,状态 0,第 28 行
无法创建约束或索引。请参阅前面的错误。