SQL SERVER 2014--学习笔记1

2023-08-29 10:23:58

--=======================================================

在SQL SERVER 2014中，最吸引眼球的就是内存表和本地编译存储过程，在MS强大的宣传下，内存表和本地编译存储过程似乎成了能搞定一切的救世主，这是真的吗？让我们一步一步来学习探索吧

--=======================================================

创建数据库

由于内存表数据的存放机制和普通表（基于磁盘的表）完全不同，因此内存表的数据需要一个特别的文件夹（注意不是文件哦）来存放，后续会涉及到数据存放原理，此时先放一放，我们来看下数据库创建脚本：

USE [master]

--创建数据库

CREATE DATABASE [TestDB]

ON  PRIMARY

( NAME = N'TestDB', FILENAME = N'D:\SQL2104\SQLData\TestDB.mdf' ,

SIZE = 204800KB , MAXSIZE = UNLIMITED, FILEGROWTH = 204800KB )

LOG ON

( NAME = N'TestDB_log', FILENAME = N'D:\SQL2104\SQLData\TestDB_log.ldf' ,

SIZE = 204800KB , MAXSIZE = 2048GB , FILEGROWTH = 204800KB )

GO

--创建内存表使用的文件组

ALTER DATABASE [TestDB] ADD FILEGROUP [TestDB_MFG1] CONTAINS MEMORY_OPTIMIZED_DATA

GO

--创建内存表使用的文件夹

ALTER DATABASE [TestDB]

ADD FILE ( NAME = N'TestDB_MDir1', FILENAME = N'D:\SQL2104\SQLData\TestDB_MDir1')

TO FILEGROUP [TestDB_MFG1]

GO

对于每个数据库，只能创建一个内存优化文件组（Memory-Optimized Filegroup），而对于每个内存优化文件组，可以创建多个关联的文件夹。

PS:内存优化文件组基与FILESTREAM文件组，但是无需为内存优化文件组来启用FILESTREAM。

--=======================================================

创建内存表

创建内存表限制比较多：

1. 支持的数据类型：http://msdn.microsoft.com/ZH-CN/library/dn133179(v=sql.120).aspx

2. 内存优化表支持自增，但唯一允许用于 seed 和 increment 的值为 1；(1,1) 是 seed 和 increment 的默认值;

3. 内存优化表不支持CHECK约束，在非hash索引列上也不行;

4. 内存优化表不支持使用的排序规则所具有的代码页并非 1252 的数据类型 char(n) 和 varchar(n);

5. 内存优化表不支持nvarchar(max)或varchar(max)

6. 内存优化表索引不支持未使用 *_BIN2 排序规则的字符列上的索引,在非hash索引列上也不行;

7. 内存优化表上的索引不支持索引键中有可为 Null 的列,在非hash索引列上也不行;

8. 内存优化表必须有至少一个索引或主键

创建内存优化表Demo

CREATE TABLE [dbo].[TB1_IM]

(

    [c1] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY

    NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),

    [c2] [nchar](200)  COLLATE Chinese_PRC_Stroke_90_BIN2 NOT NULL

    INDEX ix_c2 NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),

    [c3] [nvarchar](200) COLLATE Chinese_PRC_Stroke_90_BIN2 NOT NULL,

    [c4] [nvarchar](200) COLLATE Chinese_PRC_Stroke_90_BIN2 NOT NULL,

    INDEX ix_c3_c4 NONCLUSTERED HASH(c3,c4) WITH(BUCKET_COUNT=1000000),

    INDEX ix_c2_c3  NONCLUSTERED (c2,c3)

)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

内存优化表的最大特色就是HASH索引，而HASH索引最大的特色就是点查询比较快，但是对于范围查找就苍白无力啦。

上帝为你打开一扇窗的时候，他肯定顺手关掉一扇门。

--===============================================================================

关于BUCKET_COUNT的值的设置

MS有如下建议：

在大多数情况下，Bucket 计数应该介于索引键中非重复值数目的 1 到 2 倍之间。如果索引键包含许多重复值，且平均而言对于每个索引键值超过 10 行，则改用非聚集索引

您不见得始终都能够预测到某个特定索引键可能具有或将具有多少个值。如果 BUCKET_COUNT 值处于实际键值数目的 5 倍之内，性能就应该是可接受的。

简单理解就是：要尽可能保持每个Bucket里存放数据而又不存放过多数据，如果空闲Bucket过多就会造成浪费，如果单个Bucket中存放过多行数据，就会造成性能问题。

--=================================================================================

关于索引维护和表修改

内存索引表不支持ALTER TABLE 和 ALTER INDEX操作，如果需要修改表或者修改索引，那么SORRY,请重新删除创建！(有没有瞬间无爱的赶脚！)

--=================================================================================

关于数据持续性

对于内存优化表，有两种持续性可以选择：SCHEMA_ONLY（非持久表）和SCHEMA_AND_DATA（持久表），SCHEMA_ONLY 选项会导致数据在实例重启后丢失；而对于SCHEMA_AND_DATA（持久表），又可以设置完全持久行还是延迟持续性，延迟持续性选项允许在事务提交时可以不立即将日志写入磁盘，从而提升性能，当然代价就是发生故障时可能丢失数据。

--===============================================================================

未完待续

码农公寓

相关文章