Feeds:
Posts
Comments

Archive for the ‘Memory optimized table’ Category

‘How to implement default constraints in the memory optimized table’ seems very simple to me and I tried to implement in the same way, as we usually do it in the disk based table. But the output was not as per the expectation. Let me show you the output.

USE hkNorthwind
GO
CREATE TABLE tbl_Product_Master
(
 [Product ID] INT not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Product Name] NVARCHAR(100),
 [Creation Datetime] datetime default Getdate()
) WITH (MEMORY_OPTIMIZED = ON)
GO

Msg 10770, Level 16, State 13, Line 3
The feature ‘default constraints’ is not supported with memory optimized tables.

Oooops.. I am unable to create a default constraint in memory optimized table.

So, can we create a default constraint column in memory optimized table ? The answer is No & Yes.

Why No: Because you cannot create a default constraint column in memory optimized table.
Why Yes:Because you can implement the default constraint functionality in memory optimized table using stored procedure.

Let me create the table & stored procedure to implement default constraints in the memory optimized table.

Step 1 :
First of all, you need to create a memory optimized table without any default constraint.
Given below is the script.

USE hkNorthwind
GO
CREATE TABLE tbl_Product_Master
(
 [Product ID] INT not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Product Name] NVARCHAR(100),
 [Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON)
GO

Step 2 :
Once you have created the memory optimized table, create a stored procedure to insert records in it.

USE hkNorthwind
GO
CREATE PROCEDURE usp_Insert_Product_Master
@ProductID int,
@ProductName nvarchar(100)
AS
INSERT INTO tbl_Product_Master
([Product ID],[Product Name],[Creation Datetime])
VALUES (@ProductID,@ProductName,getdate())

Step 3 :
Insert the records using stored procedure in the memory optimized table.
Given below is the script.

USE hkNorthwind
GO
EXEC usp_Insert_Product_Master 1, 'SQL Server 2012'
GO
EXEC usp_Insert_Product_Master 2, 'SQL Server 2014'

Step 4 :
Just browse the table and check whether the default constraint is implemented or not.

USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO
--OUTPUT

implement_deafult_constraints.1.1

Note : You can download the hkNorthwind database from here.

Reference : MSDN

Read Full Post »

Recently, I was doing research on memory optimized table and I found that most of the data types are supportive and few of them are non supportive as well. But even though few data data types are non supportive there are works around to deal with those data types as well. In my earlier article, I wrote about how to deal with BLOB data types even though they are non supportive. Rest of the non supportive data types work around, I will share in my upcoming posts.

Given below is the list of Supportive data types:

  • bigint
  • int
  • tinyint
  • smallint
  • int
  • real
  • smallmoney
  • money
  • float
  • decimal
  • numeric
  • bit
  • uniqueidentifier
  • smalldatetime
  • datetime
  • binary
  • nchar
  • sysname

Given below is the list of Non-supportive data types:

  • image
  • text
  • sql_variant
  • ntext
  • varbinary(Max)  (Limitation is 8060 bytes per row)
  • varchar(Max)  (Limitation is 8060 bytes per row)
  • timestamp
  • nvarchar(Max) (Limitation is 8060 bytes per row)
  • xml

Let me know if you have work around for the non supportive data types.

Read Full Post »

In my earlier article, I wrote a solution to create memory optimized table in a new database. But the question is how to create memory optimized table in an existing database. Let me explain it step by step.

Step 1 :
In this step we need to create a simple database to demonstrate. Skip this step if you already have a database but make sure its compatibility level is 110.

USE master;
GO
CREATE DATABASE test
ON
( NAME = Sales_dat,
    FILENAME = 'C:\DATA\test.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
    FILENAME = 'C:\DATA\test_log.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;
GO
--OUTPUT

Step 2 :
Create a memory optimized table.

USE Test
GO
CREATE TABLE tbl_sample_Memory_table
(
 [ID] integer not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Name] varchar(200) not null,
 ) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Msg 41337, Level 16, State 0, Line 3
The MEMORY_OPTIMIZED_DATA filegroup does not exist or is empty. Memory optimized tables cannot be created for database until it has one MEMORY_OPTIMIZED_DATA filegroup that is not empty.

Ooopps…… I am unable to create memory optimized table in TEST database (existing database).

So, what’s the remedy for it ?

Step 3 :
Create a file group, add file in it containing memory optimized data option. Given below is the script.
Do not forget to change the path in the script.

USE [master]
GO
ALTER DATABASE [test] ADD  FILEGROUP [test_fg]
CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE [test] ADD FILE
( NAME = N'test_fg', FILENAME = N'C:\DATA\test_fg.ndf')
TO FILEGROUP [test_fg]
GO
USE [test]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups
WHERE is_default=1 AND name = N'test_fg')
ALTER DATABASE [test] MODIFY FILEGROUP [test_fg] DEFAULT
GO
--OUTPUT

Step 4 :
Once the file group is created, just create the memory optimized table and this time it will be created successfully.

USE Test
GO
CREATE TABLE tbl_sample_Memory_table
(
 [ID] integer not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Name] varchar(200) not null,
 ) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Command(s) completed successfully.

Step 5 :
Now, browse the table and check whether it is created or not.

USE Test
GO
SELECT * FROM tbl_sample_Memory_table
--OUTPUT

memoryoptimizedtables_exisiting1.1

Conclusion :
Whenever you create a memory optimized table, you must consider two things before creating it.
1- The database must have the compatibility level 110
2- Database must have a file group having CONTAINS MEMORY_OPTIMIZED_DATA

Read Full Post »

In my earlier article, I had discussed about how to implement identity column in the Memory optimized table. Now, lets try to create a BLOB data type column in memory optimized table.

Given below is the script that we usually use to implement BLOB data type columns in the disk based table (normal table). Lets try the same script for memory optimized table.

USE hkNorthwind
GO
CREATE TABLE tbl_sample
(
 [ID] integer not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Name] varchar(200) not null,
 [Address] varchar(Max)
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Msg 10770, Level 16, State 94, Line 3
The type ‘varchar(max)’ is not supported with memory optimized tables.

Oh no.. I am unable to create a BLOB data type column in memory optimized table and as we know, BLOB data types are one of the most commonly used data types in the database.

So what is the solution?

The solution is very simple. What you need to do is, actually split the above table into two tables and one as memory optimized table and 2nd one should be disk based table. And the logic behind this is, we will keep all the data types columns except BLOB in memory optimized table and will put all the BLOB data types column in disk based tables. But do not forget to keep the same ID in both tables.
Given below is the script.

USE hkNorthwind
GO
CREATE TABLE tbl_sample_Memory_table
(
 [ID] integer not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Name] varchar(200) not null,
 ) WITH (MEMORY_OPTIMIZED = ON)
GO

CREATE TABLE tbl_sample_Normal_Table
(
 [ID] integer not null,
 [Address] varchar(Max)
)
GO
--OUTPUT

Command(s) completed successfully.

As you can see that, both tables are created successfully.

Note : You can download the hkNorthwind database from here.

Reference : MSDN

Read Full Post »

Memory optimized table is a new feature introduced in SQL Server Hekaton.  As it is new for all of us, I am doing my research with different aspects of memory optimized table. In this article, we will create an identity field in the memory optimized table.

Given below is the script that we usually use to implement identity in the disk based table (normal table). Lets try the same script for memory optimized table.

USE hkNorthwind
GO
CREATE TABLE tbl_sample
(
 [ID] integer identity(1,1) not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Name] varchar(200) not null
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Msg 10771, Level 16, State 8, Line 3
The feature ‘identity column’ is not yet implemented with memory optimized tables.

Oooops.. I am unable to create identity column in memory optimized table.

So, how can we create an identity column in memory optimized table ? Here comes the SEQUENCE object solution to the rescue.

Let me create an identity column in memory optimized table in sequential steps.

Step 1 :
First of all, you need to create a memory optimized table without any identity column.
Given below is the script.

USE hkNorthwind
GO
CREATE TABLE tbl_sample
(
 [ID] integer not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Name] varchar(200) not null
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Step 2 :
Once you have created the memory optimized table, create a Sequence object.

USE hkNorthwind
GO
CREATE SEQUENCE [Seq_Sample]
 AS [int]
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 1000
GO

Step 3 :
Insert the records using sequence in the memory optimized table.
Given below is the script.

USE hkNorthwind
GO
INSERT INTO tbl_sample ([ID],[Name])
VALUES (NEXT VALUE FOR [Seq_Sample],'raresql-1')
GO
INSERT INTO tbl_sample ([ID],[Name])
VALUES (NEXT VALUE FOR [Seq_Sample],'raresql-2')
GO

Step 4 :
Just browse the table and check whether identity is implemented or not.

USE hkNorthwind
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

identity_field_for_memory_optimized_table.1.1

Note : You can download the hkNorthwind database from here.

Reference : MSDN

Read Full Post »

« Newer Posts