sql server2016中的内存优化表/内存表

use master

go

--[1.0] create database

create database test3;

--[1.1] create filegroup contains memory_optimized_data;

alter database test3

add filegroup memory_FG CONTAINS memory_optimized_data;

go

--[2.0]

use master;

--create a store folder for memory_fg

alter database test3

add file

(

name='memory_on_file',

filename='D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\memory_fg_file'

)

to filegroup memory_FG

go

--[3.0] create a memory table

use test3;

create table memory_test

(

id int not null primary key nonclustered hash with(bucket_count=1024)

,st1 nvarchar(100)

,st2 nvarchar(100)

)

with (memory_optimized=on,durability=schema_and_data);

insert into memory_test values(1,'a','b');

go

use test3;

create table AATB1DU

(

[DIInc] [int] IDENTITY(1,1) NOT NULL primary key nonclustered hash with(bucket_count=1024)

,[AID] [numeric](9, 4) NULL,

[ASub] [numeric](9, 4) NULL,

[EID] [int] NULL,

[DID] [int] NOT NULL,

[DNo] [varchar](50) NOT NULL,

[DNoX] [varchar](50) NULL,

[DName] [nvarchar](250) NULL,

[DQCode] [varchar](10) NULL,

[DDescribe] [nvarchar](250) NULL,

[PDID] [int] NULL,

[DSeq] [tinyint] NULL,

[CDT] [datetime] NULL,

[MDT] [datetime] NULL,

)

with (memory_optimized=on,durability=schema_and_data);

----

--create view V_AATB1DU

--as

--select DIInc, AID, ASub, EID, DID, DNo, DNoX, DName, DQCode, DDescribe, PDID, DSeq, CDT, MDT

--from QIRM3021C.dbo.AATB1DU

---delete from AATB1DU_

select DIInc, AID, ASub, EID, DID, DNo, DNoX, DName, DQCode, DDescribe, PDID, DSeq, CDT, MDT

into AATB1DU_ from QIRM3021C.dbo.AATB1DU

--delete from AATB1DU_

--DROP TABLE [dbo].[AATB1DU_]

--GO

insert into AATB1DU_ ( AID, ASub, EID, DID, DNo, DNoX, DName, DQCode, DDescribe, PDID, DSeq, CDT, MDT

)

select AID, ASub, EID, DID, DNo, DNoX, DName, DQCode, DDescribe, PDID, DSeq, CDT, MDT

from QIRM3021C.dbo.AATB1DU

select * from AATB1DU_

---插入内存优化表

insert into AATB1DU ( AID, ASub, EID, DID, DNo, DNoX, DName, DQCode, DDescribe, PDID, DSeq, CDT, MDT

)

select AID, ASub, EID, DID, DNo, DNoX, DName, DQCode, DDescribe, PDID, DSeq, CDT, MDT

from AATB1DU_

delete from AATB1DU