Posted in Development

Limitation of In-Memory OLTP in SQL Server 2016 – Sp_rename not allowed

Hi friends, one more limitation of In-Memory OLTP is SQL Server 2016 is that sp_rename is not allowed to change the name of in-memory OLTP objects.

Sp_rename is a system stored procedure which allows to change the name of the user-defined objects like table name, column name, datatype of a column etc. in the current database.

Let’s see an example of this –

There is already an In-memory table created with name ‘Test_OLTP’ and now I like to rename it with a new name ‘MemOLTP’.

I have executed the below query to rename the table name.

sp_rename ‘dbo.Test_OLTP’,’MemOLTP’

When I executed the query it comes up with an error stating –

‘Operations that require a change to the schema version,

for example renaming, are not supported with memory optimized tables.’

Lim_Sp_rename

That’s all for the day folks 🙂

 

Advertisements
Posted in Development

Getting started with In Memory OLTP in SQL Server 2016 – Part 1

In Memory OLTP (Hekaton) introduced in SQL Server 2014 is a new database engine component which is optimized for OLTP workloads to achieve the performance by storing data in memory and accessing data from memory. Memory optimized tables can be accessed using Transact –SQL. When memory optimized tables are accessed all data is stored into the memory.

Today, I will tell you how to create a memory optimized tables.

Let’s first create a database to stored memory optimized tables. Database that contain memory optimized tables must have MEMORY_OPTIMIZED_DATA filegroup. Syntax to create filegroup is same as creating regular filegroup only difference is that here we need to specify option CONTAINS MEMORY_OPTIMIZED_DATA.

–Create a database

IF EXISTS (SELECT * FROM sys.databases WHERE name=’InMemOLTP’)

DROP DATABASE InMemOLTP;

GO

CREATE DATABASE InMemOLTP

ON

PRIMARY(NAME = [InMemOLTP_data],

FILENAME = ‘I:\453491\InMemOLTP\Data\HKDB_data.mdf’, size=500MB),

FILEGROUP [InMemOLTP_fg] CONTAINS MEMORY_OPTIMIZED_DATA

(NAME = [InMemOLTP_mod_dir],

FILENAME = ‘I:\453491\InMemOLTP\InMemOLTP_mod_dir’)

LOG ON (name = [InMemOLTP_log],

Filename=’I:\453491\InMemOLTP\Log\InMemOLTP_log.ldf’, size=500MB)

COLLATE Latin1_General_100_BIN2;

Here, I have created a filegroup ‘InMemOLTP_fg’ with filecontainer ‘InMemOLTP_mod_dir’. I have also specifies BIN2 collation as currently indexes on character column can only be defined on columns that uses BIN2 collation.

Below is the script to create a memory optimized table with name ‘Test_OLTP’.

CREATE TABLE Test_OLTP

(

       Name varchar(40) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT =10000),

       Place varchar(30),

       CompanyName varchar(40),

       Skill varchar(100)

) WITH (Memory_Optimized = ON, DURABILITY = SCHEMA_AND_DATA);

Syntax to create memory optimized table is similar to the Transact SQL tables but to specify a memory optimized table we need to define clause MEMORY_OPTIMIZED = ON. Memory optimized table can be defined using two durability – SCHEMA_AND_DATA and SCHEMA_ONLY.

With SCHEMA_ONLY durability, memory optimized table will store only table structure and SQL server will not store log changes to the disk. With SCHEMA_AND_DATA, memory optimized table store table data to the checkpoint files on disk and table structure too.

Nonclustered Hash index is created on Name column as clustered indexes are not allowed on memory optimized tables. If you don’t specify Nonclustered with Primary Key column you will get error –

Msg 12317, Level 16, State 78, Line 21

Clustered indexes, which are the default for primary keys, are not supported with hash indexes. Specify a NONCLUSTERED index instead.

When we create a HASH index we need to specify the number of buckets in the hash index. More on Bucket_count I will discuss later in further posts.

Now, the table has been created, let’s try to insert some data into memory optimized table.

INSERT INTO Test_OLTP (Name, Place, CompanyName, Skill)

VALUES (‘Kapil’, ‘Gurgaon’,’CTS’,’SQL Server’),

              (‘Aditya’,’Pune’,’ABC’,’Weblogic’)

Just check the data that you has inserted by running below query.

SELECT * FROM Test_OLTP

OLPT1

So friends, in this way we can create memory optimized database and table. We will discuss further on memory optimized tables in my next blogs.

Have a happy learning 🙂

Posted in Development

Limitation of In-Memory OLTP in SQL 2016 – Create Index is not supported

Hi friends, in this blog I will discuss another limitation of In Memory OLTP in SQL Server 2016.

Once you create a table you cannot create any index created on memory optimized table using CREATE INDEX statement. So to create indexes you need to create them with column definition of memory optimized table.

Let’s create a memory optimized table with index on column Name.

CREATE TABLE Test_OLTP

(

       Name varchar(40) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT =10000),

       Place varchar(30),

       CompanyName varchar(40),

       Skill varchar(100)

) WITH (Memory_Optimized = ON, DURABILITY = SCHEMA_AND_DATA);

After creation of the table I also want to create a nonclustered index on column ‘CompanyName’.

Let’s execute the below query to create a nonclustered index-

Create nonclustered index ix_companyname ON Test_OLTP(CompanyName)

When I run this query I got an error –

Msg 10794, Level 16, State 13, Line 4

The operation ‘CREATE INDEX’ is not supported with memory optimized tables.

Lim_createindex

So, we cannot alter the memory optimized tables once created as SQL Server stores the structure of memory optimized tables as metadata.

To alter the table either recreate the table or create another table.

That’s all for the day 🙂

Have a nice day!!

 

 

Posted in Development

SQL Server – How to retrieve SQL plans from Cache

Hi friends, in this blog post I will tell you how we can retrieve the SQL plan from the cache. In SQL Server there are multiple DMV (Dynamic Management Views) and DMO (Dynamic Management Objects) which can be used to retrieve the information about execution plan.

Sys.dm_exec_query_plan is most commonly used DMO which is used to retrieve execution plans.

Similarly, sys.dm_exec_sql_text is used to retrieve the information like definition of object, object name.

I will write a query using these objects to find the information of execution plan.

Here, first I will create a stored procedure and then execute that stored procedure to generate the execution plan and then we will used these DMVs and DMOs to retrieve the execution plan.

CREATE Procedure Check_Plan

AS

BEGIN

 INSERT INTO Test_Plan (ID, Name, CreateDate, Country)

 VALUES (5,’KD’, Getdate(), ‘INDIA’)

END

In this stored procedure I am simply inserting the data into the sql table. Now to execute the stored procedure we will execute the command –

EXEC Check_Plan

Now, as the stored procedure is executed, a new entry has inserted in the system objects.

SELECT

b.[objectid] ,

OBJECT_NAME(b.objectid) ObjectName,

a.[objtype] ,

b.[dbid] ,

b.[text] ,

c.[query_plan],

a.plan_handle

FROM sys.dm_exec_cached_plans a

CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) b

CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c

WHERE b.objectid = 1330103779

sqlplan1

As we can see that in query plan column execution plan is stored in XML format.

To see the execution plan you can just simple click on the link and an execution plan will open in new tab.

sqlplan2

So in this way we can see the execution plan of sql objects and use it perform analysis for performance tuning.

That’s all for the day folks 🙂

Posted in Development

SQL Server – String_Escape function in SQL Server 2016

Hi friends, in SQL server 2016 another string function that introduced is String_Escape. This function can escape special characters within texts and will return text with escaped characters.

Syntax

STRING_ESCAPE (expression, type)

Currently only the value that is supported for type argument is ‘json’ only. If you try to specify another type it will result into error stating ‘An invalid value was specified for argument 2’.

In expression any nvarchar expression can be specified that needs to be escaped.

Currently, only limited JASON escape characters can be escaped.

Here is the below script that is used to show the escaped characters like this –

SELECT string_escape(‘/

” \/

 

‘,’json’)

It will results in this manner –

string_escape

As we can see in result set the escaped characters like new line, carriage return, double quotes etc. for the expression that we specified.

Let’s check another example in which I will create a table and insert some escape characters in the column value.

Create table #temp

(Name varchar(30))

Insert into #temp values (‘as/ ”””’)

Now let’s run below query and check the results –

SELECT string_escape(name,’json’) FROM #temp

string_escape1.jpg

This function will helps in returning the escape characters from the expression.

Hope you like this post. 🙂

Posted in Certification

Microsoft SQL Server 2016 Certifications Announced

Hi friends, Microsoft has announced the release of three new SQL 2016 certifications, based on the different working database professional’s roles.

MCSA: SQL 2016 Database Development can be achieved by passing the following two exams:

  • 70-761 – Querying Data with Transact-SQL
  • 70-762 – Developing SQL Databases

MCSA: SQL 2016 Database Administration can be achieved by passing the following two exams:

  • 70-764 – Administering a SQL Database Infrastructure
  • 70-765 – Provisioning SQL Databases

MCSA: SQL 2016 Business Intelligence Development can be achieved by passing the following two exams:

  • 70-767 – Implementing a SQL Data Warehouse
  • 70-768 – Developing SQL Data Models

For more information about these exams keep checking Microsoft certification page.

Posted in Development

SQL Server – STRING_SPLIT function in SQL Server 2016

Hi friends, in SQL server 2016 another new function STRING_SPLIT gets introduced which helps in splitting the character expression using separator.

Previously, to do this type of work we need to write some code or function to split the character expression but in SQL 2016 function STRING_SPLIT reduce that work to write multiple lines of code or function.

Syntax

STRING_SPLIT (String, Separator)

Here, String parameter can be of any character type and Separator parameter is a single character expression.

String_Split function requires atleast compatibility level 130. If you are using any lower version then you need to change the compatibility level to 130.

Let’s see some examples of how String_Split works.

Here, I have declared a string variable and stored a value with few ‘@’ sign. Now, I need to write down this string into multiple rows within a single column separated by ‘@’.

Declare @m varchar(20) = ‘a@b@c’

SELECT value from STRING_SPLIT(@m, ‘@’);

Here, value is the name of the column.

stringsplit_1

If you will pass the NULL value to the string result set will be empty. Also, if you passes any blank values in the string then it will results in empty row.

Declare @strn varchar(10) = NULL

SELECT * FROM STRING_SPLIT(@strn, ’,’);

It will results in empty result set.

Let’s see another example. I have created a table ‘ClubInfo’ and populated it with some data.

CREATE TABLE ClubInfo

(

       MemberID INT Identity(1,1),

       MemberName VARCHAR(50),

       Hobbies VARCHAR(50)

)

GO

INSERT INTO ClubInfo (MemberName, Hobbies)

VALUES (‘Kapil’, ‘Cricket,Football,Music’),

              (‘Ankit’, ‘Chess,Billards’)

GO

Here, Hobbies column contains multiple hobbies of a person separated by comma.

Now, using STRING_SPLIT we will separate the hobbies in multiple rows.

SELECT MemberName, MemberID, VALUE as Hobby

FROM ClubInfo

CROSS APPLY STRING_SPLIT(Hobbies,’,’)

stringsplit_2

Hope you will like this post.

Have a happy learning 🙂