Friday, June 26, 2015

CCI (Another mature feature of SQL server 2016)

During the release of SQL server 2012 , one major announcement was support for Clustered ColumnStore Index (CCI). When it was first released one thing that caught my eye was the speed at which queries run and reduction in the size of the table.   

The drawback was usability of feature in real world.  When you create the CCI you cannot insert any data so you need to come up with different workaround that I am sure some of you probably tried.  That is probably why its adoption in warehouse was limited.  When Microsoft released SQL 2014, they made CCI updatable but still non-clustered index were not updatable. 2014 solved half of the problem.

Third time lucky In SQL Server 2016, tables with CCI can now enforce referential integrity through foreign key relationship.  These tables can now have additional indexes on them and allows addition of data to a table that has a Non-clustered ColumnStore index without any modification to the index (previously you need to drop and recreate the index). You will also be able to create columnstore index on disk and memory-optimized table.

One thing that you cannot do is MERGE functionality still disabled. Hey, I am ok with this for now and I am sure Microsoft will work in the future to support it.
Other added features are
  • Support for snapshot isolation level 
  • Support to run reorganize the index 
  •  Support for filtered index on Non-Clustered Columnstore Indexes (Note this feature is only is available on Non-clustered Columnstore indexes)
  • A lot of enhancements on batch mode processing. Sort, aggregation with multiple distinct functions (count, AVG and etc) and more is now candidate for batch mode processing. In SQL 2014, batch mode was only available for parallel queries.

Thursday, June 25, 2015

SQL Server Integration Services (SQL Server 2016 new or improved features)

Three main areas of improvement are in cloud integration, connectivity and enhancements

Cloud integration mainly Azure data factory
As expected there is more integration with cloud.
  • ADF can now orchestrate on-premises SSIS execution. In addition,
  • ADF can now read from Azure data factory as data source though the ADF data flow task
  • Ability for developers move data from on-Premise to Azure storage though Azure Storage Connector
  • The ability to trigger Azure HDInsight jobs directly from SSIS
  •  Azure commandlet to issue commands directly to Azure that will help developers manage and control Azure services
There are a lot of enhancements and new connectors supports added such as
  •  Hadoop File System (HDFS),=
  •  JavaScript Object Notation (JSON)
  •  Oracle/Teradata connector
  • OData Version 4
Product improvements
As per Microsoft white paper
SQL Server 2016 makes a number of significant enhancements to SSIS usability. For example, the package designer itself has been improved with many enhancements in the areas of resizing, dragging and dropping, and other features. SQL Server 2016 also supports package templates to facilitate code reuse and package setup. Also, SSIS packages can now be deployed incrementally, rather than having to deploy the entire project. Custom logging levels can also be configured on top of the current log level. Developers can easily and fully upgrade their projects to SQL Server 2016 SSIS compatibility with a click of a button. Further, you can now easily configure high-availability AlwaysOn for the SSIS catalog database directly in SQL Server Management Studio (SSMS) without the need to set it up manually.” (

Again, this is good news for ETL developers.

Tuesday, June 23, 2015

SSRS (SQL server 2016)

In recent release of Microsoft products the improvements related to SSRS was a bit disappointing as compared to the release of core features of SQL server engine and cloud offerings and enhancements.  This time round, there seems to be more good news for SSRS community and business users as microsoft finally wake up to the needs and cries of RS community. Few that catch my eyes are:
  • Customize report themes using CSS that will allow SSRS developers to develop new report themes and styles that will hopefully allow to create a modern looking reports
  • New chart types are added mainly for better data visualization
  • Support for mobile BI  and data virtualization on windows, IOS, Android devise
  • Greater control over parameter prompts that will result in improved design dynamic and parameterized reports
  • Support for major browsers such as chrome, Firefox and safari
  • Support for mobile BI and data visualization on other devices such as IOS and Android devices
  • Ability to publish to Power BI which is fantastic news as on premise data can be easily viewed on-premise or in the cloud
  •  Addition of power query to a data source  

Monday, June 22, 2015

Dynamic Data Masking (SQL 2016 new feature)

Another useful feature of SQL server is the introduction of dynamic data masking. The introduction of this feature supports real-time masking of data from non-privileged requesters. Note here, privileged requesters can still access the masked data. Introduction of this feature as part of the database engine will definately boost data security and can be combined with other security features

The use of the feature can be varied.  Masking production data for UAT and QA testing would probably be attractive one. The other use can be in production environment to limit/avoid sensitive data viewing by non-privileged support personnel. 

It is not as sophisticated as some other third party or application masking tools but will do the work if used appropriately. The main issue will be how good basic masking function provided is enough for the business. Dynamic data masking currently supports three types’ of functions

  1. Default masking function provides full masking depending on data type of a column. For instance.
    • If the data type is integer , money, decimal it will display as zero
    •  If the data type is character it will replace an entire string with four X’s (XXXX)
  2.  The second built in function is email.  It exposes the first character of your email address, keeps @ character and replaces the domain name with XXXX and the leading. For instance if your email address is mry@mydomain.[com or or .org or .net] will replace with
  3. The third function is partial function. It will allow you to define how many numbers of the first characters/numbers and last characters/numbers you want to be displayed and choose the padding between the exposed characters/numbers.

Now let us see those feature in action

Now let us see those feature in action.
1.       To use this functionality trace flags 219 and 209 needs to be enabled.
So run  DBCC TRACEON(209,219,-1)

2.       Let us create a table that we can use to demonstrate how masking works. I will try to include all the functions provided.  On my table, I created fields with character that I can either

CREATE TABLE customers
       (CustomerId int IDENTITY PRIMARY KEY,
       FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"X*XXXXX",2)') NULL,
       LastName varchar(100) NOT NULL,
       Phone varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
       EMail varchar(100) MASKED WITH (FUNCTION = 'email()') NULL,
       NI varchar(20) MASKED WITH (FUNCTION = 'partial(3,"X*XXXXX",2)') NULL,
       salary money MASKED WITH (FUNCTION = 'default()') NULL,
       Age int MASKED WITH (FUNCTION = 'default()') NULL);

3.       Insert rows
INSERT customers (FirstName, LastName, Phone, EMail, NI, salary, Age) VALUES
('John', 'Miller', '02079458879', '', 'PN7383736A', 40000, 20),
('David', 'James', '02089458879', '', 'PN9364791B', 50000, 30),
('Yared', 'Allem', '01219348765', '', 'PN7369581C', 60000, 40),
('Genet', 'Mena', '08459297865', '', 'PN8383963D', 70000, 50);

4.       Create non-privileged user

5.       Grant select on customers table to non-privileged user

GRANT SELECT ON customers TO SupportUser;

6.       Execute as supportuser
exec as user = 'SupportUser'
select * from customers

7.       Execute as privileged user

SELECT * FROM customers


So, Looking at the output FirstName, salary, NI and age columns use  partial function, phone column uses default function and email column uses email function to mask the data.