In-Memory Demo

-- DB생성
CREATE DATABASE DBNull 
ON PRIMARY (NAME = N'DBNull' ,FILENAME = 'C:\MSSQL_DATA\DBNull.MDF',SIZE = 500MB ,MAXSIZE = UNLIMITED ,FILEGROWTH = 100MB) 
LOG ON (NAME = N'DBNull_Log' ,FILENAME = 'C:\MSSQL_LOG\DBNull.LDF' ,SIZE = 100MB ,MAXSIZE = 1024GB ,FILEGROWTH = 100MB) 




-- 언어확인
SELECT DATABASEPROPERTYEX('DBNull','Collation')



/* IN-MEMORY DB설정
이 명령어를 실행하고, DBNull의 데이터베이스 파일그룹 정보를 확인하면, 메모리 엑세스에
최적화된 데이터라고 나오는 것을 확인 할 수 있습니다.
*/
ALTER DATABASE DBNull ADD FILEGROUP [IN_MemoryGroup] CONTAINS MEMORY_OPTIMIZED_DATA


/* IN-MEMORY 폴더 추가
해당 구문을 실행하면, 폴더와 함께 파일이 생깁니다. 
* SQL SERVER가 재시작하여도 메모리 테이블의 데이터를 계속 파일에 저장합니다. 
FILESTREAM에 저장된 데이터와 트랜잭션 로그를 사용해서 재부팅 시 RECOVERY 과정을 진행합니다.
이 과정은 메모리 테이블에 대한 RECOVERY 과정이며, 저장된 데이터를 메모리에 다시 로드하는 과정입니다.
*/
ALTER DATABASE DBNull 
ADD FILE(NAME = IN_MEMORY,FILENAME='C:\IN_MEMORY') TO FILEGROUP [IN_MemoryGroup]


/*=====================================================================================================================================*/


USE DBNull
GO
-- [SCHEMA_ONLY] 테이블 테스트
CREATE TABLE [SCHEMA_ONLY](
 col1 INT			NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000000)
,col2 NVARCHAR(100)	COLLATE Korean_Wansung_BIN2 NOT NULL INDEX[HX_TBL_1_1]
,col3 NVARCHAR(50)	NULL
,col4 INT			NULL)
WITH (MEMORY_OPTIMIZED = ON			-- 메모리 테이블
	 ,DURABILITY = SCHEMA_ONLY) -- 테이블의 유형 (SCHEMA_AND_DATA: 비휘발 / SCHEMA_ONLY:휘발)
GO

INSERT INTO [SCHEMA_ONLY] VALUES (1,N'TEST','C개발자',100)
SELECT * FROM [SCHEMA_ONLY]




-- 테이블 생성
-- 테이블 속성을 열면 정보를 확인 할 수 있습니다.
-- C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\xtp
CREATE TABLE [SCHEMA_AND_DATA](
 col1 INT			NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000000)
,col2 NVARCHAR(100)	COLLATE Korean_Wansung_BIN2 NOT NULL INDEX[HX_TBL_1_1]
,col3 NVARCHAR(50)	NULL
,col4 INT			NULL)
WITH (MEMORY_OPTIMIZED = ON			-- 메모리 테이블
	 ,DURABILITY = SCHEMA_AND_DATA) -- 테이블의 유형 (SCHEMA_AND_DATA: 비휘발 / SCHEMA_ONLY:휘발)
GO
/*
메시지 12329, 수준 16, 상태 103, 줄 51
1252 이외의 코드 페이지를 가진 데이터 정렬을 사용하는 데이터 형식 char(n) 및 varchar(n)는 메모리 액세스에 최적화된 테이블에서 지원되지 않습니다.
*/
INSERT INTO [SCHEMA_AND_DATA] VALUES (1,N'TEST1','DBA',100)
SELECT * FROM [SCHEMA_AND_DATA]

/*
Foreign Key, Check 제약조건
LOB, CLR, XML 데이터 형식
파티션, 압축, TDE
복제, 미러링
DML 트리거
인덱스 컬럼에는 null 허용(null 허용 안함)
* ALTER TABLE: 테이블 삭제 후 다시 생성
* 인덱스 추가 삭제: 테이블 삭제 후 다시 생성
*/



/*=====================================================================================================================================*/



-- Collation 관련
-- https://technet.microsoft.com/ko-kr/library/ms186356(v=sql.105).aspx
SELECT *
--COLLATIONPROPERTY('name','Codepage') AS CODEPAGE,NAME
FROM SYS.fn_helpcollations ()
WHERE COLLATIONPROPERTY ('name','Codepage') = 1252
GO

-- 문자열 컬럼에 인덱스를 지정할 경우 언어 선정
SELECT COLLATIONPROPERTY(name, 'codepage') AS CODEPAGE, NAME
from sys.fn_helpcollations()
where name like '%bin2%'
    and name like '%korean%'


-- Collation에 관련된 알아야 할 점 ORDER BY
CREATE TABLE dbo.ORDER_CI_AS(
    ID int primary key nonclustered identity
    , CI_AS nvarchar(100) collate korean_wansung_ci_as
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

-- Collation에 관련된 알아야 할 점 ORDER BY
CREATE TABLE dbo.ORDER_BIN2(
    ID int primary key nonclustered identity
    , BIN2 nvarchar(100) collate korean_wansung_bin2
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

INSERT INTO ORDER_CI_AS VALUES( N'가')
INSERT INTO ORDER_BIN2 VALUES( N'가')
INSERT INTO ORDER_CI_AS VALUES(N'1')
INSERT INTO ORDER_BIN2 VALUES(N'1')
INSERT INTO ORDER_CI_AS VALUES(NULL)
INSERT INTO ORDER_BIN2 VALUES(NULL)
INSERT INTO ORDER_CI_AS VALUES(N'A')
INSERT INTO ORDER_BIN2 VALUES(N'A')
GO

SELECT 'CI_AS' as 'collate',* FROM ORDER_CI_AS ORDER BY CI_AS
SELECT 'BIN2' as 'collate',* FROM ORDER_BIN2 ORDER BY BIN2
GO
 
-- 조인 
SELECT * FROM ORDER_CI_AS AS A
INNER JOIN ORDER_BIN2 AS B
ON A.CI_AS = B.BIN2




/*=====================================================================================================================================*/




-- 네이티브 프로시저
-- C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\xtp\5
-- EXEC INSERT_PROC_1 1,'형권','DBA',100
-- SELECT * FROM [TBL_1]

ALTER PROC DBO.INSERT_PROC_1
@col1 INT
,@col2 NVARCHAR(200)
,@col3 NCHAR(100)
,@col4 INT
	WITH 
		NATIVE_COMPILATION,
		SCHEMABINDING,
		EXECUTE AS OWNER
	AS
	BEGIN ATOMIC
		WITH 
		(TRANSACTION	ISOLATION LEVEL = SNAPSHOT,
		 LANGUAGE = N'KOREAN')

--SELECT * FROM [TBL_1]
--SELECT col1, col2, col3, col4 FROM TBL_1 WHERE col1 = col1 OR col2 = @col2
--INSERT INTO [dbo].[TBL_1] VALUES (@col1,@col2,@col3,@col4)

	END
GO
/* 네이티브 프로시저 제약사항
서브쿼리 불가능
case when문 불가능
outer join 불가능 (left, full...)
apply 불가능
where에 in,or,like 조건 불가능
select * 불가능
union 불가능
temp table , @table 불가능
CTE 불가능
Cursors 불가능
select into 불가능
*/


/*=====================================================================================================================================*/


USE AdventureWorks2014
GO
-- IN-MEMORY 테이블의 올바른 버킷 수 결정 
-- 전체 행수
SELECT COUNT(*) AS [전체 행 수] 
FROM Sales.SalesOrderDetail

-- 복합인덱스 일 경우 행사
SELECT COUNT(*) AS [복합 인덱스로 걸 경우 고유값]
FROM 
   (SELECT DISTINCT SpecialOfferID, ProductID 
    FROM Sales.SalesOrderDetail) t




-- ALTER DATABASE [AdventureWorks2014] ADD FILEGROUP [IN_MemoryGroup_1] CONTAINS MEMORY_OPTIMIZED_DATA
--SP_DETACH_DB [AdventureWorks2014]
--SP_ATTACH_DB [AdventureWorks2014],'C:\MSSQL_DATA\AdventureWorks2014_Data.mdf','C:\MSSQL_LOG\AdventureWorks2014_Log.ldf'
-- https://msdn.microsoft.com/ko-kr/library/dn511655(v=sql.120).aspx
-- 테이블 생성
CREATE TABLE [Sales].[SalesOrderHeader_test]
(
   [SalesOrderID] [uniqueidentifier] NOT NULL DEFAULT (newid()),
   [OrderSequence] int NOT NULL,
   [OrderDate] [datetime2](7) NOT NULL,
   [Status] [tinyint] NOT NULL,

PRIMARY KEY NONCLUSTERED HASH ([SalesOrderID]) WITH ( BUCKET_COUNT = 262144 ),
INDEX IX_OrderSequence HASH (OrderSequence) WITH ( BUCKET_COUNT = 20000),
INDEX IX_Status HASH ([Status]) WITH ( BUCKET_COUNT = 8),
INDEX IX_OrderDate NONCLUSTERED ([OrderDate] ASC),
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

-- 값 삽입
SET NOCOUNT ON
DECLARE @i int = 0
BEGIN TRAN
WHILE @i < 262144
BEGIN
   INSERT Sales.SalesOrderHeader_test (OrderSequence, OrderDate, [Status]) VALUES (@i, sysdatetime(), @i % 8)
   SET @i += 1
END
COMMIT
SET NOCOUNT OFF
GO



-- 올바른 버킷수를 확인하기 위한 스크립트
SELECT 
   object_name(hs.object_id) AS 'object name', 
   i.name as 'index name', 
   hs.total_bucket_count,
   hs.empty_bucket_count,
   floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) AS 'empty_bucket_percent',
   hs.avg_chain_length, 
   hs.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats AS hs -- 버킷 수를 볼수 있음
   JOIN sys.indexes AS i 
   ON hs.object_id=i.object_id AND hs.index_id=i.index_id
WHERE I.name IN('IX_Status','IX_OrderSequence','PK__SalesOrd__B14003C33D8879E4')



/*=====================================================================================================================================*/


-- 백업 테스트
BACKUP DATABASE [DBNull] TO DISK = 'C:\MSSQL_BACKUP\DBNull.BAK'
-- 복원 테스트
RESTORE FILELISTONLY FROM DISK  = 'C:\MSSQL_BACKUP\DBNull.BAK'

-- 복원
RESTORE DATABASE [DBNull_2] from DISK ='C:\MSSQL_BACKUP\DBNull.BAK'
WITH MOVE 'DBNull' TO 'C:\MSSQL_DATA\DBNull_1.MDF',
MOVE 'DBNull_Log' TO 'C:\MSSQL_LOG\DBNull_1.LDF',
MOVE 'IN_MEMORY' TO 'C:\IN_MEMORY_2'

-- 데이터 확인 및 프로시저 확인
-- C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\xtp
USE DBNull_2 
GO



SELECT * FROM [dbo].[TBL_2]
SELECT * FROM [dbo].[TBL_1]

EXEC [dbo].[INSERT_PROC_1] 2,'주형권','개발자',200



/*=====================================================================================================================================*/
-- 메모리 관리
CREATE RESOURCE POOL [DBNull_MEMORY] WITH (MAX_MEMORY_PERCENT = 70);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

EXEC sp_xtp_bind_db_resource_pool 'DBNull', 'DBNull_MEMORY'
GO

ALTER DATABASE DBNull SET OFFLINE
GO
ALTER DATABASE DBNull SET ONLINE
GO

-- 메모리 할당량 확인
USE [DBNull]
go
SELECT OBJECT_NAME(object_id), *
FROM sys.dm_db_xtp_table_memory_stats;

-- SQL SERVER 2014 IN-MEMORY 성능수집 관련 
-- https://msdn.microsoft.com/ko-kr/KO/library/dn511015.aspx