SQL server 内存数据库
来源:互联网 发布:linux telnet命令端口 编辑:程序博客网 时间:2024/05/18 02:58
下面是一个SQLserver 内存数据库的简单例子
注意:C盘根目录需建立一个data目录
--1.创建库 内存表 CREATE DATABASE imoltpGO ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA ALTER DATABASE imoltp ADD FILE (name='imoltp_mod1', filename='c:\data\imoltp_mod1') TO FILEGROUP imoltp_mod ALTER DATABASE imoltp SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON GO USE imoltp GO --2 create a durable (data will be persisted) memory-optimized table -- two of the columns are indexed CREATE TABLE dbo.ShoppingCart ( ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000), CreatedDate DATETIME2 NOT NULL, TotalPrice MONEY ) WITH (MEMORY_OPTIMIZED=ON)GO CREATE TABLE dbo.UserSession ( SessionId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=400000), UserId int NOT NULL, CreatedDate DATETIME2 NOT NULL, ShoppingCartId INT, INDEX ix_UserId NONCLUSTERED HASH (UserId) WITH (BUCKET_COUNT=400000) ) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY) GO -- 3 insert data into the tables INSERT dbo.UserSession (UserId, CreatedDate, ShoppingCartId) VALUES (342, SYSDATETIME(), 4)INSERT dbo.UserSession (UserId, CreatedDate, ShoppingCartId) VALUES (65, SYSDATETIME(), NULL) INSERT dbo.UserSession (UserId, CreatedDate, ShoppingCartId) VALUES (8798, SYSDATETIME(), 1)INSERT dbo.UserSession (UserId, CreatedDate, ShoppingCartId) VALUES (80, SYSDATETIME(), NULL)INSERT dbo.UserSession (UserId, CreatedDate, ShoppingCartId) VALUES (4321, SYSDATETIME(), NULL) INSERT dbo.UserSession (UserId, CreatedDate, ShoppingCartId) VALUES (8578, SYSDATETIME(), NULL)INSERT dbo.ShoppingCart (UserId, CreatedDate, TotalPrice) values (8798, SYSDATETIME(), NULL) GO -- 4 创建内存表存储过程 if exists( select 1 from sysobjects where name = 'usp_AssignCart' )begindrop procedure usp_AssignCartendGO -- natively compiled stored procedure for assigning a shopping cart to a session CREATE PROCEDURE dbo.usp_AssignCart @SessionId int, @test int output WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') DECLARE @UserId INT, @ShoppingCartId INT SELECT @UserId=UserId, @ShoppingCartId=ShoppingCartId FROM dbo.UserSession WHERE SessionId=@SessionId IF @UserId IS NULL THROW 51000, N'The session or shopping cart does not exist.', 1 UPDATE dbo.UserSession SET ShoppingCartId=@ShoppingCartId WHERE SessionId=@SessionId select @test = @ShoppingCartIdEND GO --2.老存储过程中调用,内存表存储过程use mydb goprint 'Create procedure sp_do_action_test... 'goif exists( select 1 from sysobjects where name = 'sp_do_action_test' )begin drop procedure sp_do_action_testendgocreate procedure sp_do_action_test @l_action_in int-- with encryptionasset rowcount 0set transaction isolation level read uncommitted-- 调用内存表存储过程 beginif @l_action_in = 1beginexec imoltp..usp_AssignCart 1,@error_no outputselect @error_info = '[500901]l_action_in[' + convert(varchar, @l_action_in) +']测试调用内存表存储过程'end -- 调用内存表存储过程 endset transaction isolation level read committedselect @error_no as 'error_no', @error_info as 'error_info'return @error_nogo--老存储过程测试exec mydb..sp_do_action_test 1
0 0
- SQL server 内存数据库
- 优化SQL Server数据库服务器内存配置
- SQL Server数据库内存增加问题分析
- 优化SQL Server数据库服务器内存配置
- 有关SQLServer内存问题(收藏)-数据库专栏,SQL Server
- 检测SQL SERVER数据库CPU瓶颈及内存瓶颈
- 检测SQL SERVER数据库CPU瓶颈及内存瓶颈
- 检测SQL SERVER数据库CPU瓶颈及内存瓶颈
- SQL Server 2014新特性探秘(1)-内存数据库
- SQL Server 2014新特性探秘(1)-内存数据库
- SQL SERVER 2012下数据库内存异常回收
- 理解内存----SQL Server内存
- sql server 内存表
- SQL Server内存分页
- sql server 内存表
- sql server 内存管理
- Sql Server 内存释放
- SQL SERVER读书笔记:内存
- nginx 实现js跨越
- map2
- 常见异常整理之二SQL语句执行正常前台页面没有值
- c语言中怎样用scanf()读入带空格的字符串
- <canvas>学习笔记——绘制弧和圆
- SQL server 内存数据库
- opencv中轮廓内部的像素点怎么提取
- MAC编译android studio的JNI .h文件
- Android App卡顿问题分析
- 使用 NSURLSession 开发一个支持后台下载和断点续传的下载工具
- HDU 2829 Lawrence(DP+四边形不等式优化)
- SoapUI测试WSDL文件
- poj3259(最短路)
- Android Cursor自动更新的实现和原理