Yet another SQL Server enthusiast


SQL Server partition elimination – a simple example

Filed under: Uncategorized — sa @ 11:07 AM

On a partitioned table, partition elimination (ref #1 – section Partition Eliminationref #1 – section Partition Elimination, ref #2) is an query optimization

  1. Available to predicates defined on partitioning key columns (DateKey in our example) and
  2. It allows to avoid a full table scan/full [clustered] index scan
  3. By scanning records from one or more partitions.



In this case

  • DateKey is the partitioning key of dbo.FactOnlineSales_Archive table and
  • WHERE clause includes one predicate defined on this column (DateKey = <constant>).

Execution plan shows how partition elimination works: Seek Predicates (green box) property of Clustered Index Scan includes one predicate (PtnId1000 = …) which is going to do the partition elimination (it limits the Scan operator to a single partition in this case). Also, please note the Predicate property (red box) of the same Scan operator which is going to filter all records from current partition in order to count only records for DateKey = ‘2007-01-04’.


A.D.C.E.S. Meetup 2017-07-11

Filed under: Uncategorized — sa @ 8:42 PM

Download files



SQL Server Management Studio v17 – three keyboard shortcuts

Filed under: Uncategorized — sa @ 11:37 PM

1. Line swapping

Keyboard shortcut: Alt + Up / Down (new in SSMS v17)

Description: It swaps current line and above (Up) or bellow (Down) line

Example: Ctrl + Up

Alt   UporDown 

2. Vertical selection

Keyboard shortcut: {Alt + Shift + Up / Down or Shift + Alt + Up / Down or Alt + Mouse Selection} + [Typing chars]

Description: It selects source code on columns

Example: Shift + Alt + Down (x4) + (‘

Alt  Shift   UporDown

3. Clipboard cycling

Keyboard shortcut: Alt + Shift + V or Ctrl + Shift + Ins

Description: It cycling backwards  through selected text in clipboard

Example: FactSales, INSERT, UPDATE and also DELETE were copied (Ctrl + C) into clipboard and then Alt + Shift + V (x4)

Ctrl   Shift   V


Filegroup restoring and database consistency @

Filed under: Uncategorized — sa @ 10:16 PM

New article published on [Filegroup restoring and database consistency]


RoSQL 2017-05-06 Table Partitioning

Filed under: Uncategorized — sa @ 9:32 AM


Download *.sql scripts


Moving LOB data to another filegroup

Filed under: Uncategorized — sa @ 9:19 AM

Note #1: This post is (more or less) a reminder for me.

Note #2: Original solutions are described here and here.

The problem (and a partial solution)

Sometimes, within a SQL Server database, we have to move data to another filegroup. In such cases, the simplest solution is to use CREATE INDEX statement with the ON NewFileGroup clause and also with DROP_EXISTING = ON thus:


This solution is available for both clustered and non-clustered indexes. In this cases, SQL Server will rebuild current index using the new filegroup (ON NewFileGroup). The drawback of this solution is that Database Engine will not move also LOB data (Large OBject data types include: old data types [n]text, image but also the new data types [n]varchar(max), varbinary(max), xml, CLR, geometry, geography, sql_variant) to new filegroup. The [main] reason of this behavior can be seen in CREATE TABLE syntax:


The CREATE TABLE statement includes the TEXTIMAGE_ON clause what allows us to define which filegroup should be used to store LOB data. Above solution will move only row data without moving LOB data (pages) into new filegroup. So, LOB data remain within old (TEXTIMAGE_ON) filegroup.

Note #3: For heap tables, one solution is to create a clustered index on the new filegroup and then drop it. After dropping clustered index,  the LOB data remain [stored] within new filegroup.

Final solution

The final solution is to recreate clustered index using a partition scheme. In this case, the behavior of SQL Server Database Engine is to move to target filegroup(s) (defined by partition scheme) both ROW and LOB data pages:


In order to create partition scheme we need a partition function. The main difference between original solution and this one is that in this case, I’ve used an empty partition function.


Initial status of ProductPhoto table (AdventureWorks2014 database):


Partition function (empty partition scheme), partition scheme (with all “partitions” assigned to [UserObjects] filegroup) + ProductPhoto table is recreated using the new partition scheme:


After these operations, the new status of ProductPhoto table is


but current table is partitioned. In order to remove partitioning (partition scheme) of current table the simplest solution is to recreate (again) the main table structure (clustered index) on the new filegroup:


And the final status of ProductPhoto table (unpartitioned) is:



Trolling Ora

Filed under: Uncategorized — sa @ 3:08 PM



SQL Server on Linux Ubuntu - First preview

SQL Server on Linux Ubuntu – First preview



ITdevConnect 2016

Filed under: Uncategorized — sa @ 1:11 AM




eBooks from SQLCAT

Filed under: Uncategorized — sa @ 7:58 AM

On September 1, 2013 SQL Server Customer Advisory Team published three eBooks:

  1. SQLCAT’s Guide to Relational Engine
  2. SQLCAT’s Guide to High Availability and Disaster Recovery
  3. SQLCAT’s Guide to BI and Analytics

Interesting chapters from SQLCAT’s Guide to Relational Engine:

  • DBCC Checks and Terabyte-Scale Databases 4
  • Scheduling Sub-Minute Log Shipping in SQL Server 2008 10
  • SQL DMVStats Toolkit 24
  • Top SQL Server 2005 Performance Issues for OLTP Applications 78
  • Table-Valued Functions and tempdb Contention 80
  • Resolving PAGELATCH Contention on Highly Concurrent INSERT Workloads 95
  • SQL Server Indexing: Using a Low-Selectivity BIT Column First Can Be the Best Strategy 99
  • Bulk Loading Data into a Table with Concurrent Queries 135
  • Eliminating Deadlocks Caused By Foreign Keys with Large Transactions 174
  • Storage Top 10 Best Practices 225


ITDevConnect 2015 / April 25 – 26

Filed under: Uncategorized — sa @ 10:18 PM

“Join us at ITdevConnect, a two day event packed with knowledge about the latest innovations in the field of technology. Find out what’s new in 2015: Visual Studio 2015, C# 6, ASP.NET 5, SQL Server 2014 and try the programmes during a hands-on lab on Windows Azure.”

Next Page »

Create a free website or blog at