Help: SQL Server

Microsoft believes that everyone in the world is not a developer there are some DBAs as well. Be it Visual Studio, SQL Server or any other programming paradigm – Microsoft makes it easy for developers and DBA alike. In my previous post, you would have read about new T-SQL constructs added to support In-Memory OLTP objects. This blog post shows the SSMS improvements and dialogs introduced with SQL Server 2014 to work with In-Memory OLTP.

CREATE DATABASE

We have learned earlier that we need a new filegroup which would contain memory_optimized_data. This is the key point to understand the flow of actions below..

• Right Click on database folder in SSMS and Choose “New database”. This is the same place where we create regular database.

• Open “New Database” interface, go to “Filegroups” tab. Then click on “Add Filegroup” under “Memory Optimized Data” section.

• I have given name as “SQLSeverHelp_IMO_FG” and then went back to “General” tab.

• Here is the little explanation of numbered steps.

1. We need to come back to “General” Tab after creating filegroup in earlier step.
2. Provide “Database name” SQLSeverHelp_IMO
3. Click on “Add” button and provide details in new row (third row)
4. We have given SQLSeverHelp_IMO_dir as logical name
5. Since it is going to contain In-Memory optimized data, we need to use “FILESTREAM Data” in “File Type”
6. Once we choose drop down in step 5, “Filegroup” would be “SQLSeverHelp_IMO_FG” created in previous screen.
7. Provide complete physical “Path” where the files/data needs to be stored for In-Memory tables. C:\IMO_Database\SQLSeverHelp_IMO_dir 

Follow sequence given in above image and hit OK.

Do you know that I can remember all command by heart? I am joking! If you want to script this action, use the “Script” button (shown in below image). This would give us the same script (with little more setting) which we use in previous part.

The “Script” feature is available in almost every user interface of SSMS. Another option is to use predefined template. This is one of under-utilized/less known feature of SSMS. As shown below, go to “View” Menu and click on “Template Explorer” (Ctrl+Alt+T) to access a lot of predefined scripts.

• Once we click on “template explorer”, we would see “template browser” opened within management studio. Over there, we can see many template and one of them of our interest, as of now, is highlighted in below image.

• Double click would open the template and we need to use “Ctrl+Shift+M” to get window called “Specify Values for Template Parameters”

• We can make changes here and hit OK. We can edit path, name and modify the TSQL as per our requirement.

If you have an existing database then, ALTER DATABASE would be needed. This is because the filegroup for memory_optimized_data might not have been defined. We can go to database properties and add new filegroup and follow same screen with 7 steps.

CREATE TABLE

Next step is to create an In-Memory Optimized table. Note that there is no “table designer” UI to create the In-Memory Optimized table. If you have followed earlier steps, template explorer might be open (else press Ctrl+Alt+T)

Option # 1
In situations where we have an existing database which is not having memory_optimize_data filrgroup defined, we can use template called “Add MEMORY_OPTIMIZED_DATA Filegroup and Create Memory Optimized Table” (shown below)

Option # 2
If we have memory optimized filegroup in the database then we can choose “Create Memory Optimized Table”

Option # 3
If we go with conventional way of creating table using SSMS then we can Right Click on “Tables” folder under the database SQLSeverHelp_IMO and choose New > Memory Optimized Table

All three options would open a query window with the template. (See, I told you earlier – no table designer)

In this query windows, we need to use Ctrl+Shift+M and replace the parameter as needed. We can add more columns, indexes and make modifications. As the commented section in above template says, we need to have filegroup defined for memory_optimized_data

In a nut-shell, in the absence of UI, you need to have T-SQL syntax knowledge.

CREATE PROCEDURE

Same as CREATE TABLE, there is no special interface available for natively compiled procedure. We can use either Right Click technique (#1) or template browser (#2) to reach to template.

Option # 1

Option # 2

Both of the options above are going to open template and then we can write the code there. Remember the shortcut Ctrl+Shift+M for filling template parameter.

Till now we have learned how to create objects. In next post, we would see what happens differently (than regular objects) when in-memory tables are in-memory stored procedures are created. Stay tuned!

Missed reading earlier posts? Here is the quick reference:

• Part 1 A-Z of In-Memory OLTP : My name is Hekaton
• Part 2 A-Z of In-Memory OLTP : TSQL Constructs for In-Memory

Stay Tuned!

Cheers,
Balmukund Lakhani
Twitter @blakhani
Author: SQL Server 2012 AlwaysOn – Paperback, Kindle