Sep 17, 2013

Create memory optimized table in ssms in sql server 2014

Creating memory optimized database by using sql server management studio (SSMS) in sql server 2014

Step 1: Right click databases and choose New Database as shown in the following image:

Memory optimized database 
Step 2: In the New Database window click on the filegroups tab:

Memory Optimized File Group
In the memory optimized data section click on Add Filegroup. In the name textbox write any name of file group. In this example our filegroup name is Memory_File_Group

Step 3: Go back to general tab:

Memory Optimized database File
Database Name: Write your database name. In this example it is Memory_DB
Now click on add button to add files in the file group.
Logical files:  Write your memory file name. In this example it is Memory_File_1
File Type: Choose FILESTREAM Data
File Group: Chose the file group which we have just created it. In this example it is Memory_File_Group
Now click on OK button to create database.

Creating memory optimized table by using sql server management studio (SSMS) in sql server 2014

Create a new query page and write following script to create a very simple table:

USE Memory_DB

CREATE TABLE tblEmployee
(
     ntEmpID BIGINT NOT NULL PRIMARY KEY  NONCLUSTERED HASH  WITH (BUCKET_COUNT = 1024),
    vcName VARCHAR(200),
     moSalary MONEY
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY =SCHEMA_AND_DATA)

Memory Optimized Table Sql server 2014 ssms
Click on execute button to create this procedure.

No comments:

Post a Comment