Memory Optimized “Table Variables” in SQL Server 2014 and 2016


SQL Server 2014 provided you these new features to create [Memory Optimized Tables] and [Native Compiled Stored Procedures] for efficient and quick processing of data and queries which happens all in memory.

It also provided you one more feature to create Memory Optimized Table Variables, in addition to normal Disk Based Table Variables.

This new feature would provide you more efficiency in Storing, Retrieving and Querying temporary data from and in memory.

Normal Table Variables are created in tempdb and utilize it for their entire life. Now with these new Memory Optimized Table Variables they will become free from tempdb usage, relieve tempdb contention and reside in memory only till the scope i.e. batch of a SQL script or a Stored Procedure.

Let’s see how to use these and what performance gain you get out of these tables.
 

–> Enable Database for supporting Memory Optimized tables: To use this feature your Database should be associated with a FileGroup. So, let’s alter the database.


USE [TestManDB]
GO

-- Add the Database to a new FileGroup

ALTER DATABASE [TestManDB]
    ADD FILEGROUP [TestManFG] CONTAINS MEMORY_OPTIMIZED_DATA 
GO

ALTER DATABASE [TestManDB]
ADD FILE ( 
	NAME = TestManDBFG_file1,
    FILENAME = N'E:\MSSQL\DATA\TestManDBFG_file1' -- Put correct path here
)
TO FILEGROUP TestManFG
GO

Otherwise, while creating Memory Optimized objects you will get below error:

Msg 41337, Level 16, State 100, Line 1
Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.

 

You cannot create a Memory Optimized Table Variable directly with DECLARE @TableVarName AS TABLE (…) statement. First you will need to create a Table Type, then based upon this you can create Tables Variables.

–> Create a Table TYPE [Person_in_mem]

CREATE TYPE dbo.Person_in_mem AS TABLE(
	 BusinessEntityID INT NOT NULL
	,FirstName NVARCHAR(50) NOT NULL
	,LastName NVARCHAR(50) NOT NULL

	INDEX [IX_BusinessEntityID] HASH (BusinessEntityID) 
		WITH ( BUCKET_COUNT = 2000)
)
WITH ( MEMORY_OPTIMIZED = ON )
GO

The Memory Optimized Table Type should have an Index, otherwise you will see an error as mentioned below:

Msg 41327, Level 16, State 7, Line 27
The memory optimized table ‘Person_in_mem’ must have at least one index or a primary key.
Msg 1750, Level 16, State 0, Line 27
Could not create constraint or index. See previous errors.

 

Ok, now as we’ve created this table type, now we can create as many Table Variables based upon this.

–> Now, create a Table variable @PersonInMem of type [Person_in_mem] that is created above:

DECLARE @PersonInMem AS Person_in_mem

-- insert some rows into this In-Memory Table Variable
INSERT INTO @PersonInMem
SELECT TOP 1000
	 [BusinessEntityID]
	,[FirstName]
	,[LastName]
FROM [AdventureWorks2014].[Person].[Person]

SELECT * FROM @PersonInMem
GO

Here we successfully created a Table Variable, inserted records into it and retrieved same by the SELECT statement, and this all happened in memory.
 

Now how can we see we that how much benefits we got from this? What we can do is, we can create a separate Disk-Based Table Variable and do similar operation on it and compare the results by checking the Execution Plan.

–> Comparing performance of both In-Memory vs Disk-Based Table-Variables

– Enable the Actual Execution Plan and run below script to Create and Populate both:

1. In-Memory Table Variable

2, Disk-Based Table Variable

-- 1. In-Memory Table Variable
DECLARE @PersonInMem AS Person_in_mem

INSERT INTO @PersonInMem
SELECT TOP 1000
	 [BusinessEntityID]
	,[FirstName]
	,[LastName]
FROM [AdventureWorks2014].[Person].[Person]

select * from @PersonInMem


-- 2. Disk-Based Table Variable
DECLARE @Person AS TABLE (
	 BusinessEntityID INT NOT NULL
	,FirstName NVARCHAR(50) NOT NULL
	,LastName NVARCHAR(50) NOT NULL
)

INSERT INTO @Person
SELECT TOP 1000
	 [BusinessEntityID]
	,[FirstName]
	,[LastName]
FROM [AdventureWorks2014].[Person].[Person]

select * from @Person
GO

 

–> Now, check the Actual Execution Plan results below:

1. Check the Cost of INSERT operation with both the tables:

– It took only 8% cost to insert into In-memory Table Variable.

– But it took 89% cost to insert into a Disk-Based Table Variable.

> If You see the individual Operators in both the plans you will see that :
For @PersonInMem Table Variable the cost of INSERT was just 19% compared to the cost of INSERT for @Person Table Variable that was 92%.

SQL Server 2016 - Memory Optimized Table Variables 01

2. Check the Cost to SELECT/Retrieve rows both the tables:

– It took only 0% cost to retrieve rows from the In-memory Table Variable

– And it took 3% cost to retrieve rows from a Disk-Based Table Variable

SQL Server 2016 - Memory Optimized Table Variables 02

This proves that the INSERT and SELECT operations with Memory Optimized table are way more faster that normal Disk-Based tables.

Thus, using Memory Optimized Table Variables will provide you better performance for storing temporary data within memory and process with in Stored Procedure or your T-SQL Scripts.


Obfuscate column level data by using “Dynamic Data Masking” in SQL Server 2016


This time SQL Server 2016 has made good additions in area of Security by introducing features like:

1. Always Encrypted

2. Row Level Security, check my previous post,

3. Dynamic Data Masking, this post

4. and other security features, like Transparent Data Encryption (TDE), etc.
 

Dynamic Data Masking provides you support for real-time obfuscation of data so that the data requesters do not get access to unauthorized data. This helps protect sensitive data even when it is not encrypted, and shows obfuscated data at the presentation layer without changing anything at the database level.

Dynamic Data Masking limits sensitive data exposure by masking it to non-privileged users. This feature helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It’s a Policy-based Security feature that hides the sensitive data in the result set of a Query over designated database columns, while the data in the database is not changed.
 

–> “Dynamic Data Masking” provides you three functions/options to Mask your data:

1. default(): just replaces the column value with ‘XXXX’ by default.

2. email(): shows an email ID to this format ‘aXXX@XXXX.com’.

3. partial(prefix,padding,suffix): gives you option to format and mask only some part of a string value.

We will see the usage of all these 3 masking functions below.
 

–> To setup Dynamic Data Masking on a particular Table you need to:

1. CREATE TABLE with MASKED WITH FUNCTION option at column level.

– Or ALTER TABLE columns by using this option if the table is already present.

2. Create Users and Grant Read/SELECT access for the above CREATED/ALTERED table.
 

–> 1. Create a sample table [dbo].[Customer] with masked columns:

CREATE TABLE dbo.Customer (
	CustomerID 	 INT IDENTITY PRIMARY KEY,
	FirstName 	 VARCHAR(250),
	LastName 	 VARCHAR(250) MASKED WITH (FUNCTION = 'default()') NULL,
	PhoneNumber  VARCHAR(12)  MASKED WITH (FUNCTION = 'partial(1,"XXXXXXXXX",0)') NULL,
	Email 		 VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NULL,
	CreditCardNo VARCHAR(16)  MASKED WITH (FUNCTION = 'partial(4,"XXXXXXXXXX",2)') NULL,
);

–> Now insert some test records (fictitious figures):

INSERT INTO dbo.Customer (FirstName, LastName, PhoneNumber, Email, CreditCardNo)
VALUES
('Manoj',   'Pandey', '4442889882', 'manojp@gmail.com', '4563234576547834'),
('Saurabh', 'Sharma', '9812446452', 'sausha@gmail.com', '1243096778653487'),
('Vivek',   'Singh',  '6745239856', 'viveks@gmail.com', '8756341209876735'),
('Keshav',  'Singh',  '9867452387', 'keshav@gmail.com', '2938713685372618');

–> Let’s check the rows on [dbo].[Customer] table in context of my User:

SELECT * FROM dbo.Customer;
GO

SQLServer2016 - DDM 01

Here, I can see all the row/column values as I have full access to read the masked/sensitive data.
 

–> 2.a. Now let’s create a Test Account and just Grant Read access to [dbo].[Customer] table:

CREATE USER AnyUser WITHOUT LOGIN;
GO

GRANT SELECT ON dbo.Customer TO AnyUser;
GO

–> Let’s execute the SELECT statement on [dbo].[Customer] table in the Context of this new user account:

EXECUTE AS USER = 'AnyUser';
SELECT * FROM dbo.Customer;
REVERT;
GO

SQLServer2016 - DDM 02

And you can see that the user is not able to see the masked data as he is not authorized to see it.
 

–> Removing Masking from a column by simple ALTER TABLE/COLUMN statement:

ALTER TABLE dbo.Customer 
ALTER COLUMN LastName DROP MASKED;
GO

-- Let's check the table data again:
EXECUTE AS USER = 'AnyUser';
SELECT * FROM dbo.Customer;
REVERT;
GO

SQLServer2016 - DDM 03

Here, now you are able to see contents of LastName columns, as the masking has been removed from this column by using simple ALTER TABLE/COLUMN statement.
 

–> 2.b. Granting the UNMASK permission to “AnyUser”:

GRANT UNMASK TO AnyUser;
GO

-- Let's check the table data again:
EXECUTE AS USER = 'AnyUser';
SELECT * FROM dbo.Customer;
REVERT; 
GO

SQLServer2016 - DDM 01

He is able to see all data unmasked when the UNMASK permission is granted to this user.
 

–> 2.c. Revoking back the UNMASK permission form the same user:

REVOKE UNMASK TO AnyUser;
GO
EXECUTE AS USER = 'AnyUser';
SELECT * FROM dbo.Customer;
REVERT; 
GO

After Revoking UNMASK permission he is again not able to see complete data.
 

This way you can control access to your precious or PII data by masking the columns/fields that you don’t want to show to the external world or some set of users.
 

–> Final Cleanup:

DROP TABLE dbo.Customer;
GO
DROP USER [AnyUser]
GO

 

–> Check the same demo on YouTube:

SQL Server - Dynamic Data Masking - YouTube


Configure multiple TempDB Database Files while installing SQL Server 2016 (new feature)


With SQL Server 2016 now you can configure the number of TempDB Database Files during the installation of a new Instance. While installation process in the Database Engine Configuration page you will see an extra option to set number of TempDB files.
 

Or, you can specify the number of files by using the new command line parameter: /SQLTEMPDBFILECOUNT

setup.exe /Q /ACTION="INSTALL" /IACCEPTSQLSERVERLICENSETERMS 
 /FEATURES="SqlEngine" /INSTANCENAME="SQL15" .. 
 /SQLTEMPDBDIR="D:\tempdb" /SQLTEMPDBFILECOUNT="4"

 

While installing via UI the label besides the Input Control below mentions: “The default value is 8 or the number of cores, whichever is lower. This value can be increased up to the number of cores”.

SQL Server 2016 Install 06
 

So, in my [C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER2016\MSSQL\DATA\] folder I could see 8 data files, with 1 log file:

SQL Server 2016 Install 07


Implementing “Row Level Security” (RLS) with SQL Server 2016

July 13, 2015 3 comments

For an Organization its Data is the most important thing, and the Org’s IT department takes lot of measures to make sure data does not fall in wrong hands. The DBA’s and Application programmers setup different layers of security on top of data so that the user is only able to see the Filtered data/rows from a table that he has access to. DB programmers normally create abstracted Views or Stored Procedures with complex logic on top of the Tables by using other master-mapping tables that contains the user-data access key. Sometimes these security logics are not upto the standards, and may have various versions on a database and thus it becomes difficult to track, update and make them fail-proof. So, with SQL Server 2016 the new Row Level Security feature is going to handle this Out of the Box and as a Standard.

RLS or Row Level Security is a feature that enables fine grained control over access to rows in a table, allowing you to easily control which users can access which data with complete transparency to the application.

With this feature rows are filtered based on the Execution Context of the Query, rather than the current user access rights. A secure logic can be created to determine which user can see which rows and restrict any kind of data (rows) by designing a flexible and robust Security policy for a table.

SQL Server 2016 - Row Level Security 04
 

–> To setup Row Level Security (RLS) on a particular table it needs only these simple steps:

1. Create Users and Grant Read/SELECT access for a Particular table.

2. Create a new Inline Table-Valued Function that will contain the Filter Predicate for that table. This Function Predicate can be a sophisticated business logic with multiple JOINs or a simple WHERE ManagerCode = ‘userHR’.

3. Create a new Security Policy for this table and add the above Function (Filter) Predicate to it.

Please note: that these Functions & Security Policies should be unique for a table. So to create RLS for an another table, you will need to create separate Function & Security Policy.
 

–> Step 1.a. Let’s create some test accounts: I will create three users for:

1. The CEO, over-all admin of the company data.

2. HR department head

3. Finance department head

CREATE USER userCEO WITHOUT LOGIN;
GO
CREATE USER userHR WITHOUT LOGIN;
GO
CREATE USER userFin WITHOUT LOGIN;
GO

 

–> Create a sample table [dbo].[Employee]: with a self-referencing Manager ID column.

CREATE TABLE dbo.Employees (
	[EmpCode] NVARCHAR(50),  -- Employee ID
	[EmpName] NVARCHAR(250), -- Employee/Manager Full Name
	[Salary]  MONEY,		 -- Fictious Salary
	[MgrCode] NVARCHAR(50)   -- Manager ID
);
GO

-> Now insert some test records:

-- Top Boss CEO
INSERT INTO dbo.Employees VALUES ('userCEO'	, 'CEO Top Boss'  , 800, NULL)

-- Next 2 levels under CEO
INSERT INTO dbo.Employees VALUES ('userHR'	, 'HR User'		  , 700, 'userCEO');
INSERT INTO dbo.Employees VALUES ('userFin'	, 'Finance User'  , 600, 'userCEO');

-- Employees under Kevin
INSERT INTO dbo.Employees VALUES ('manojp'	, 'Manoj Pandey'  , 100, 'userHR');
INSERT INTO dbo.Employees VALUES ('saurabhs', 'Saurabh Sharma', 400, 'userHR');
INSERT INTO dbo.Employees VALUES ('deepakb' , 'Deepak Biswal' , 500, 'userHR');

-- Employees under Amy
INSERT INTO dbo.Employees VALUES ('keshavk'	, 'Keshav K'	  , 200, 'userFin');
INSERT INTO dbo.Employees VALUES ('viveks'	, 'Vivek S'		  , 300, 'userFin');
GO

–> Let’s check the records before applying “Row Level Security”:

SELECT * FROM dbo.Employees; -- 8 rows
GO

As a normal SEELCT and without RLS, it just ignores my Execution Context and execute the Query and return all the 8 rows.

–> The Traditional way to setup the Row Level Security till now was as follows (a simple example):

-- Stored Procedure with User-Name passed as parameter:
CREATE PROCEDURE dbo.uspGetEmployeeDetails (@userAccess NVARCHAR(50))
AS
BEGIN
	SELECT * 
	FROM dbo.Employee
	WHERE [MgrCode] = @userAccess
	OR @userAccess = 'userCEO'; -- CEO, the admin should see all rows
END
GO

-- Execute the SP with different parameter values:
EXEC dbo.uspGetEmployeeDetails @userAccess = 'userHR'  -- only 3 rows
GO
EXEC dbo.uspGetEmployeeDetails @userAccess = 'userFin' -- only 2 rows
GO
EXEC dbo.uspGetEmployeeDetails @userAccess = 'userCEO' -- all 8 rows
GO

The above method is prone to issues, like SQL Injection and any other user can apply other user’s User-Name and get the information that he is not allowed to see. With this type of method you have to apply another security layer at the application level so whenever a user executes the SP it gets executed with the same user’s User-Name.
 

–> The new Row Level Security feature let you:
– apply this security at the database level
– and there is no need to apply the WHERE clause filter for the User-Name.

This makes the security system more reliable and robust by reducing the surface area of your security system.

–> Step 1.b. Grant Read/SELECT access on the dbo.Employee table to all 3 users:

GRANT SELECT ON dbo.Employees TO userCEO;
GO
GRANT SELECT ON dbo.Employees TO userHR;
GO
GRANT SELECT ON dbo.Employees TO userFin;
GO

 

–> Step 2. Let’s create an Inline Table-Valued Function to write our Filter logic:

CREATE FUNCTION dbo.fn_SecurityPredicateEmployee(@mgrCode AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_SecurityPredicateEmployee_result
	-- Predicate logic
	WHERE @mgrCode = USER_NAME() 
	OR USER_NAME() = 'userCEO'; -- CEO, the admin should see all rows
GO

This function returns value 1 when:

– a row in the MgrCode (i.e. the Manager Code) column is the same as the user executing the query (@@mgrCode = USER_NAME())

– or if the user executing the query is the Top Boss user (USER_NAME() = ‘userCEO’)
 

–> Step 3. Create a security policy adding the function as a filter predicate:

CREATE SECURITY POLICY ManagerFilter
ADD FILTER PREDICATE dbo.fn_SecurityPredicateEmployee(MgrCode)  -- Filter Column from dbo.Employee table
ON dbo.Employees
WITH (STATE = ON); -- The state must be set to ON to enable the policy.
GO

The above Security Policy takes the Filter Predicate Logic from the associated Function and applies it to the Query as a WHERE clause.
 

–> Now let’s again check the records after applying “Row Level Security”:

SELECT * FROM dbo.Employees; -- 0 rows, 
GO

The simple “SELECT *” statement will fetch me zero rows after applying RLS, as my user ID is not configured to have access to any of those rows & Table.

–> And if you check in the Execution Plan of above SELECT statement without WHERE clause, it will show you the Filter Predicate that is added by the Security Policy defined in Step #3 for applying RLS on this table, which looks like this:

[TestManDB].[dbo].[Employee].[MgrCode]=user_name()
OR user_name()=N’userCEO’

SQL Server 2016 - Row Level Security 10
 

–> Let’s check the 3 users we created and provided them customized access to the dbo.Employee table and rows in it:

-- Execute as our immediate boss userHR (3 rows): 
EXECUTE AS USER = 'userHR';
SELECT * FROM dbo.Employees; -- 3 rows
REVERT;
GO

-- Execute as our immediate boss userFin: 
EXECUTE AS USER = 'userFin';
SELECT * FROM dbo.Employees; -- 2 rows
REVERT;
GO

-- Execute as our Top boss userCEO (8): 
EXECUTE AS USER = 'userCEO';
SELECT * FROM dbo.Employees; -- 8 rows
REVERT;
GO

–> The results of the above 3 SELECTs looks like this:

SQL Server 2016 - Row Level Security 01

So, as you can see the three users we created resulted in different results:

– The HR & Finance users got just 3 & 2 rows, respectively.

– But the admin CEO user got all the 8 rows.
 

–> Thus, by using Row Level Security feature in SQL Server 2016 you can create your own customized Security by creating an Inline Table-Valued Function that is linked to the Security Policy for your Table.
 

–> Final Cleanup

DROP SECURITY POLICY [dbo].[ManagerFilter]
GO
DROP FUNCTION [dbo].[fn_SecurityPredicateEmployee]
GO
DROP TABLE [dbo].[Employee]
GO

DROP PROCEDURE dbo.uspGetEmployeeDetails
GO

 

Check the same demo on YouTube:


Pass multiple values with a single Parameter in a Stored Procedure – SQL Server

July 10, 2015 1 comment

This post comes from an old discussion on MSDN T-SQL Forum[link], where someone asked about the same topic.

Check my reply there and here with this post I’m going to collate all the different methods I’ve blog previously that can be used for the same purpose.
 

Stored Procedures accept only a fixed and pre-defined number of parameters thus there is a limitation you cannot provide dynamic number of params, like you can do with other languages, like C, C#, Java, etc. In these languages there is concept of Arrays which you can pass in a method/function, but in SQL there are no array variables and it does not have any datatype that support arrays. Thus if you have to provide multiple values to any parameter you cannot do it directly, though there are some workarounds.
 

–> Here are some of the methods or workarounds by which we can pass multiple values as a single Parameter in a Stored Procedure or a Function:

Method #1 – Passing a CSV: list of strings as a parameter to a (N)VARCHAR datatype parameter, then splitting/parsing it inside the SP or UDF, check here.

Method #2 – Passing an XML: string as an XML datatype parameter. We will need to parse the XML inside the SP, check here.

Method #3 – Using a temp table: inside an SP which is created outside just before its execution. Here there is no need to pass any parameter with the SP, check here.

Method #4 – Using TVPs: With SQL Server 2008 and above you can create TVPs or Table Valued Parameters and declare them by using user-defined table types. These TVPs can then be used to send multiple rows of data to SPs or UDFs, without creating a temp table or multiple parameters, check here.
 

Thanks a lot, please provide your valuable comments and suggestions on this topic.


Follow

Get every new post delivered to your Inbox.

Join 456 other followers