Tag Archives: sql server

Extract (Create) SQL Data-Tier Application

SQL Data-Tier allow database administrator to package SQL Server objects into portable artifact.
There are 2 types:
1. DACPAC – contain database schema only.
2. BACPAC – contain database schema and data.

You can create Data-Tier in SSMS (SQL Server Management Studio) or Visual Studio Data Tools. I’d like Visual Studio Data Tools as it give me more options to configure.

To extract SQL Data-Tier from a database in Visual Studio.
1. Open SQL Server Object Explorer.
2018-12-10 14_06_30-

2. Right click on database you want to extract (assuming you have configured database connection).
3. Select “Extract Data-Tier Application…”
2018-12-10 14_04_49-MDEDatabase - Microsoft Visual Studio (Administrator)

4. Dialog displayed.
2018-12-10 14_07_26-Extract Data-tier Application

Here you can configure different options. Some to consider:

  • Include user login mappings – whether to include objects under Security (users, roles, etc).
  • Verify extration – whether Visual Studio verify reference in every objects, for example: if you have view that join table from another database, VS will verify the connection to the other database.

To deploy Data-Tier to database engine (in VS):
1. Right click on “Databases”
2. Select “Publish Data-Tier Application…”
2018-12-10 14_17_53-MDEDatabase - Microsoft Visual Studio (Administrator)

Leave a comment

Posted by on December 10, 2018 in General


Tags: , , , , , , ,

What is SQL Server “Included Columns” ?

SQL Server organized indexes in a table as B-tree structure, which look like this.


A table that has `clustered index` store actual data rows at leaf level.
A table that has `non-clustered index`, only store (at leaf level) the value from the indexed column and a `row locator` point to actual data rows. It does not store actual data rows.

Specifying Included Columns in `non-clustered index` tell SQL Server you want to store actual data rows at leaf level.

For example:

ON Employee(EmployeeFirstName)
INCLUDE (EmployeeAddress, EmployeeDOB)

This example will create `non-clustered index` and its leaf level will store value from indexed column (EmployeeFirstName) and data rows from EmployeeAddress and EmployeeDOB columns.

Leave a comment

Posted by on December 3, 2018 in General


Tags: , , , , , ,

SQL Server Configuration Manager: “Cannot connect to WMI provider. You do not have permission”

This caused by uninstalling certain version of SQL Server and re-installing different version.

See this for fixes.

Note: Run command prompt as Administrator.

Leave a comment

Posted by on November 13, 2018 in General


Tags: , , , , ,

Fix Linq Generate ‘ESCAPE’ Keyword on Contains/StartsWith/EndsWith Predicate and Cause Performance Hit

When using Linq to Entity (or Linq to SQL) with these predicates, Linq will generate ‘ESCAPE’ keyword in SQL statement which cause performance hit.

This code:

public IEnumerable GetUser(string filter)
    using (var db = new SomeEntities())
        var result = db.User
            .Where(u => u.DisplayName.Contains(filter))

Will generate this sql statement:

SELECT [Extent1].[DisplayName] AS [DisplayName]
FROM   (SELECT [User].[DisplayName] AS [DisplayName]
        FROM   [dbo].[User] AS [User]) AS [Extent1]
WHERE  [Extent1].[DisplayName] LIKE '%garlan%' /* @p__linq__0 */ ESCAPE '~'

The `ESCAPE ‘~’` could potentially cause performance degradation.

Solution to this issue is to pass in constant into Contains predicate. It’s easier said than done.
If we have method like `GetUser` above which take ‘filter’ parameter, it’s not possible to convert to constant.
To overcome this, we have to create method that return predicate with constant.

This solution is proposed here.

Summary is to create extension class.

using System;
using System.Linq.Expressions;

namespace Blahblahblah
    public static class PredicateConstantCreator
        public static Expression EmbedConstant(this Expression expression, TConstant constant)
            var body = expression.Body.Replace(expression.Parameters[1], Expression.Constant(constant));

            return Expression.Lambda(body, expression.Parameters[0]);

        private static Expression Replace(this Expression expression, Expression searchEx, Expression replaceEx)
            return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);

        internal class ReplaceVisitor : ExpressionVisitor
            private readonly Expression from;
            private readonly Expression to;

            public ReplaceVisitor(Expression from, Expression to)
                this.from = from;
       = to;

            public override Expression Visit(Expression node)
                return node == this.from ? : base.Visit(node);

And use it like this:

Expression predicate = (item, filterTerm) => item.DisplayName.Contains(filterTerm);
var result = User

Linq will generate sql statement like this:

SELECT [Extent1].[DisplayName] AS [DisplayName]
FROM   (SELECT [User].[DisplayName] AS [DisplayName]
        FROM   [dbo].[User] AS [User]) AS [Extent1]
WHERE  [Extent1].[DisplayName] LIKE '%garlan%' /* @p__linq__0 */

Alternative solution is to add function to generated XML file in EDMX. This solution is lay out here.

Leave a comment

Posted by on October 23, 2018 in General


Tags: , , , , ,

SQL Server Concurrency Effects

Lost Update

Happens when one transaction update overwrites another, causing the update to be lost.

Dirty Reads

Reading uncommitted records.

Phantom Reads

Reading while another transaction is adding new record, result in different number of rows being returned.

Repeatable Reads

When the same query to read is executed, it will return the same result.



Leave a comment

Posted by on May 22, 2018 in General


Tags: , , , ,

Columnstore Index

  • New in SQL Server 2012.
  • Stored by columns, a column-based index, instead of row-based like in traditional index. For example, if row-based index is consisted of Firstname and Lastname columns, the column-based index would have 2 different indexes: Firstname in its own index and Lastname in its own index.
  • The index is compressed, allowing high performance.
  • The compressed data is stored in-memory, reducing needs to read off the disks.
  • Compression ratio is generally high because the same data type in a column.
  • Generally a better choice for wide table with many columns, as commonly found in data warehouse tables.
  • Clustered and non-clustered index.
  • Can be combined with row-based index.

Microsoft Docs

Leave a comment

Posted by on May 16, 2018 in General


Tags: , , , , ,

SQL Server Isolation Level


Isolation is the “I” in ACID principal which rules the transactional state and its concurrency. The higher the isolation level, the lower the concurrency effects. And vice versa.

ISO Standard Isolation

The ISO standard defines 4 different isolation levels, from lowest to highest:
– Read uncommitted
– Read committed
– Repeatable read
– Serializable, where transactions are completely isolated from one another.

SQL Server Database Isolation

SQL Server Database supports all levels of isolation defined by ISO standard. In addition, it adds another isolation levels: the Snapshot isolation level (ALLOW_SNAPSHOT_ISOLATION).

SQL Server Database also add another implementation of Read Committed isolation level: Read Committed Snapshot Isolation or RCSI (READ_COMMITTED_SNAPSHOT). The original Read Committed implementation is referred to RC.

In Azure SQL Database, the default setting for Read Committed is RCSI (both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are set to ON).

In on-premise SQL Server, the default is RC (both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are set to OFF). But can be set to use RCSI by setting them to ON.

These two isolation levels, Snapshot isolation and RCSI, use row versioning that is maintained in tempdb.


To check database settings for Snapshot isolation:

SELECT name, snapshot_isolation_state, is_read_committed_snapshot_on FROM sys.databases

To check current connection’s transaction isolation level:

SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
FROM sys.dm_exec_sessions

To set transaction isolation level for current connection. Transaction isolation level is per session / connection.


Isolation Level and Concurrency Effects Matrix

Isolation Level Dirty Read Lost Update Non Repeatable Read Phantom
Read uncommitted Yes Yes Yes Yes
Read committed No Yes Yes Yes
Repeatable read No No No Yes
Snapshot No No No No
Serializable No No No No


In a load test, performance is significantly higher in RCSI, but it requires a lot higher throughput in tempdb (some 50x larger I/O). So planning on tempdb scaling is very important.

Microsoft Docs
Technet Blog

Leave a comment

Posted by on May 7, 2018 in General


Tags: , , , , , , ,

%d bloggers like this: