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




SELECT {d ‘2015-06-20’} [RoSQL Presentation]

Filed under: sql server — sa @ 6:58 AM


When a query plan goes bad

Filed under: sql server — sa @ 9:03 PM


Next Page »

Blog at