“Oracle Database 11g Express Edition” – Installation on Windows

Background

A week ago today, DH called me to speak about an interoperability problem he was facing accessing data on an Oracle DB from SQL Server.

The tool he was using is Microsoft’s Business Intelligence Development ( BIDs)

We tried to debug the problem over phone and email.

Did not get far and so I ended up connecting remotely to his machine and found a workaround using Microsoft Linked Server.

The problem was vexing enough and I wanted to reproduce locally in our Lab environment.

And, so here we go downloading and installing a Light version of the Oracle DB Engine, Oracle Express.

Download

Overview

To download please visit Oracle’s Express Edition Overview page here.

The current version is Express Edition 11g Release 2.

Download

Image

Textual

  1. Oracle Database Express Edition 11g Release 2 for Windows x64
  2. Oracle Database Express Edition 11g Release 2 for Windows x32
  3. Oracle Database Express Edition 11g Release 2 for Linux x64

 

Installation

Image

Welcome

License Agreement

Choose Destination Location

Original

Revised

Explanation
  1. Changed destination folder from C:\Oraclexe to D:\Oraclexe
  2. Space Required to 631124 K ( 630 MB)

 

Specify Database Passwords

Original

Revised

Explanation
  1. Enter password for the System Account
    • Btw, the system accounts are
      • SYS
      • SYSTEM

Summary

Image

Explanation
  1. Oracle Database Listener :- 1521
  2. Oracle Services for Microsoft Transaction Server :- 2030
  3. Oracle HTTP Listener :- 8080

Complete

Image

Sql Server – DataTypes – datetime2 & Legacy Apps

Background

Customer raised a P-1.

Ticket Contents

VB retirement calc tool application is experiencing very weird behavior.
This SQL query “SELECT abflag_updatedate FROM abtbl WHERE tablename = ‘plan’ returns a datetime string and will be formatted by this VB code :
getUpdateDate = Format(rstBase.Fields.Item(“abflag_updatedate”).Value, C_DateFormat1) //C_DateFormat1=”mm-dd-yyyy”

Starting this week errors were reported by tester. And by tracing into the code, I noticed that the Format() method call does not do the formatting as expected. The string value getting from the query of a datetime is like “2017-09-20 22:44:26.890” which is ignored by the Format function.

Production application is working, we verified that.

I suspect that the SQL query in VB code / Recordset was getting different format of string in the past and in production at of now so the Format function was working. Please help investigate this issue, to see if there was SQL server configuration change that made the impact.

I am concerned that this issue will impact a lot of places in the VB applications as I see this type of code everywhere. And we should be aware of the root cause so it will NOT apply to Prod.

TroubleShooting

Did not have Visual Basic, but developer gave enough details for a clean room implementation.

Snippet


Dim C_DateFormat1 as string

C_DateFormat1="mm-dd-yyyy"

Do While NOT Recordset.Eof   

 dtABFlag = Format(rstBase.Fields.Item("abflag_updatedate").Value, C_DateFormat1)  
	
 Recordset.MoveNext     

Loop

DB Changes

Made a slight structural change to db, created a new column, abflag_updatedateAsDatetime.

Notice original column’s datatype is datetime2(3).

New column’s datatype is datetime.

If it were MTV’s Real World, would have gone with a computed column.

 

Clean Room Implementation


DB_CONNECTION_STRING = "Provider=sqloledb;Data Source=DBSERVER;Initial Catalog=rbp;Integrated Security=SSPI;"

'declare the SQL statement that will query the database

rem SQL = "SELECT abflag_updatedate FROM dbo.abtbl WHERE tablename = 'plan'"
SQL = "SELECT abflag_updatedate, abflag_updatedateConverted = convert(datetime,  abflag_updatedate ) FROM dbo.abtbl WHERE tablename = 'plan' "

 

'create an instance of the ADO connection and recordset objects

' 

Set Connection = CreateObject("ADODB.Connection")

Set Recordset = CreateObject("ADODB.Recordset")

 

'open the connection to the database

wscript.echo DB_CONNECTION_STRING

Connection.Open DB_CONNECTION_STRING 

 

'Open the recordset object executing the SQL statement and return records 

Recordset.Open SQL,Connection

 

'first of all determine whether there are any records 

If Recordset.EOF Then 

	wscript.echo "There are no records to retrieve; Check that you have the correct job number."

Else 

'if there are records then loop through the fields 

Do While NOT Recordset.Eof   

 
  field = Recordset("abflag_updatedate")

  fieldFormatted = FormatDatetime(Recordset.Fields.Item("abflag_updatedate").Value, vbShortDate )

	
  if field <> "" then

    wscript.echo "field " & field
    wscript.echo "fieldFormatted " & fieldFormatted

 end if
	
 if (Err.Number <> 0) Then 	

   Wscript.echo String(CHAR_LENGTH, CHAR_SEP)
		
   wscript.echo "Err.Number :- " & Err.Number
   wscript.echo "Err.Description :- " & Err.Description
		
   Wscript.echo String(CHAR_LENGTH, CHAR_SEP)		
		
   Err.Clear
	
 end if	

   fieldConverted = Recordset("abflag_updatedate")

   rem fieldFormatted = Format(Recordset.Fields.Item("abflag_updatedate").Value, C_DateFormat1)

   fieldConvertedFormatted = FormatDatetime(Recordset.Fields.Item("abflag_updatedateConverted").Value, vbShortDate )

	
  if (Err.Number <> 0) Then
	
	Wscript.echo String(CHAR_LENGTH, CHAR_SEP)	
	wscript.echo "Err.Number :- " & Err.Number
	wscript.echo "Err.Description :- " & Err.Description
		
	Wscript.echo String(CHAR_LENGTH, CHAR_SEP)				


  end if

  if fieldConverted <> "" then

    wscript.echo "fieldConverted " & fieldConverted
    wscript.echo "fieldConvertedFormatted " & fieldConvertedFormatted

  end if
	
  Recordset.MoveNext     

Loop

End If

Findings

  1. Format Versus FormatDatetime
    • VBScript does not support the Format Function, and so please use FormatDateTime
  2. Function – FormatDatetime
    • Database Data Type
      • datetime
        • Works
      • datetime2
        • Fails with error
          • Error Number ( Err.Number ) :- 13
          • Error Number ( Err.Description ) :- Type mismatch
  3. Function – Format
    • Database Data Type
      • datetime
        • Works
      • datetime2
        • Fails silently
        • Returns passed in datetime without formatting as requested
  4. Function – CDate
    • Database Data Type
      • datetime
        • Works
      • datetime2
        • Fails

Source Code Control

GitHub

DanielAdeniji/SQLServerDatatypeDatetime2LegacyApp

Link

 

Dedicated

There is always a first time.

First post under the Visual Basic Category.

 

Moral of the Story

Do not use new data types and if you have to, use views, stored procedures, tracking ( computed )  columns to shield from frontend.

SQL Server – Security – Privileging & Tracking the Guest Account

Background

Each month we get databases from our Vendors.  And, we have to review data in those tables.

There are a couple of pathways to quickly granting accessing to our users.

In this post, we will grant access to the guest user.

 

SQL

Grant Access



grant connect to guest;

Review Access

Review Permissions grant/denied to Guest on Contextual Database

Code


declare @principal sysname

set @principal = 'guest'

; with cteObject
(
      objectID
    , [object]
    , schemaID
    , [schema]
)
as
(
    select
              tblSO.object_id
            , tblSO.[name]
            , tblSO.schema_id
            , tblSS.[name]

    from   sys.objects tblSO

    inner join sys.schemas tblSS

            on tblSO.[schema_id] = tblSS.[schema_id]
)
select 

          tblSP.[name]
        
        --, tblSDP.*

        , tblSDP.[state_desc]

        , tblSDP.[permission_name]

        , [objectClass]
            = tblSDP.class_desc

        , [object]
            = coalesce
                (
                     quoteName
                        (
                            cteO.[schema]
                        )
                    + '.'
                    + quoteName
                        (
                            cteO.[object]
                        )

                    , null
                )

            , [grantee]
                = SUSER_NAME(tblSDP.grantor_principal_id)

from   sys.database_principals tblSP

inner join sys.database_permissions tblSDP

    on tblSP.principal_id = tblSDP.[grantee_principal_id]

left outer join cteObject cteO

    on tblSDP.major_id = cteO.objectID

where tblSP.[name] = @principal

order by

    tblSP.[name] asc


Review Access for contextual database

Code

use master
go

set nocount on
go

set XACT_ABORT on
go

declare @tblResult table
(
      [database]    sysname
    , [principal]   sysname
    , [access]      sysname
    , [grantee]     sysname null
)

declare @command nvarchar(4000)

set @command =
                'if databasepropertyex(''?'', ''Collation'') is not null '
                + ' begin '
                + ' use [?]; '
                + ' select [database] = db_name(), tblSP.name,  tblSDP.[state_desc], SUSER_NAME(tblSDP.grantor_principal_id) '
                + ' from sys.database_principals tblSP '
                + ' inner join sys.database_permissions tblSDP '
                + ' on tblSP.principal_id = tblSDP.grantee_principal_id '
                + ' where tblSP.[name] = ''guest'' '
                + ' and tblSDP.[permission_name] = ''CONNECT'' '
                + ' and tblSDP.[state_desc]  = ''GRANT'' '
                + ' end '

insert @tblResult
(
      [database]    
    , [principal]   
    , [access]      
    , [grantee] 
)
exec master.dbo.[sp_MSforeachdb]
        @command1 = @command

select *

from   @tblResult tblR

order by 
    tblR.[database]

SQL Server – Review Database Restore Timeline

Background

Reviewing Database Restore timeline and wasted to share what we have thus far.

 

Metadata

SQL

/*

    a) restorehistory (Transact-SQL)
    https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/restorehistory-transact-sql

*/
; with cteRestoreHistory
(
      [restoreHistoryID]
    , [databaseName]
    , [restorer]
    , [restoreDate]
    , [restoreStopAt]
    , [restoreType]
    , [backupSetID] 
    , [sequenceNbr]
)
as
(

    select 
              [restoreHistoryID]
                = tblRH.[restore_history_id]

            , [databaseName]
                = tblRH.[destination_database_name]

            , [restorer]
                = tblRH.[user_name]

            , [restoreDate]
                = tblRH.[restore_date]

            , [restoreStopAt]
                = tblRH.[stop_at]

            , [restoreType]
                = tblRH.restore_type

            , [backupSetID] 
                = tblRH.[backup_set_id] 

            , [sequenceNbr]
                = DENSE_RANK() 
                    OVER 
                    (

                        PARTITION BY 
                            tblRH.[destination_database_name]

                        ORDER BY 
                            tblRH.[restore_date] DESC
                    )

    from   msdb.[dbo].[restorehistory] tblRH


)

, cteRestoreType
(
      [restoreType]
    , [restoreTypeLiteral]
)
as
(
    select 
              'D'
            , 'Database'
    union
    select 
              'F'
            , 'File'
    union
    select 
              'G'
            , 'FileGroup'
    union
    select 
              'L'
            , 'Log'

)
select 
          [database] 
            = tblSD.[name]

        , [dbid]
             = tblSD.[database_id]

        , [restoreInitatedBy]
             = tblRH.[restorer]

        , [restoreDate]

        , [restoreType]
             = tblRH.[restoreType]

        , [restoreTypeDescription]
            = cteRT.restoreTypeLiteral

        , [backupMachineName]
            = tblBS.[machine_name]

        , [backupInitiatedBy]
            = tblBS.[user_name]

        , [databaseVersion]
            = tblBS.database_version

        , [backupFile]
            = tblBMF.physical_device_name

        , [originalBackupStartDate]
            = tblBS.backup_start_date

        , [originalBackupCompletionDate]
            = tblBS.backup_finish_date

        , [originalBackupDuration (in minutes)]
            = datediff
                (   
                      minute
                    , tblBS.backup_start_date
                    , tblBS.backup_finish_date
                )

from   sys.databases tblSD

inner join cteRestoreHistory tblRH

        on tblSD.[name] = tblRH.[databaseName]

inner join msdb..backupset tblBS

    ON tblRH.[backupSetID] = [tblBS].[backup_set_id]

INNER JOIN msdb..backupmediafamily tblBMF
    ON [tblBS].[media_set_id] = [tblBMF].[media_set_id] 

left outer join cteRestoreType cteRT
    on tblRH.[restoreType] = cteRT.restoreType


where tblRH.sequenceNbr = 1

order by

          tblSD.[name]
        , tblRH.[restoreDate] desc


Output

Sample

SQL Server – Database Internal Version Number

Introduction

As we prepare for a new SQL Server release, SQL Server 2017 on Windows & Linux, it is as good a time as any to publicly avail the Internal Database Version Numbers.

Internal Database Version

Internal Version Number Marketing Description
852 2016
782 2014
706 2012
663 2008-R2 2008-R2 SP1, SP2, SP3 ( This server supports version 663 and earlier. A downgrade path is not supported )
661 2008-R2
655 2008
612 2005 When vardecimal feature is enabled ( Paul S. Randal – SQL Q&A Partitioning, Consistency Checks, and More )
611 2005
539 2000
515 7

References

  1. Paul S. Randal
    • SQL Q&A Partitioning, Consistency Checks, and More
      Link
  2. Dan Guzman
    • Internal SQL Server Database Version Numbers
      Link
  3. Dixin Yan
    • Attach SQL Server 2000 database to SQL Server 2014
      Link

SQL Server – Error – Msg 902 – “To change the NAME, the database must be in state in which a checkpoint can be executed”

Background

Trying to rename a database, but ran into the error noted below

 

SQL

Command Issued


exec sp_renamedb [TSD08282017], 'REDWOOD_TSD_PC31'

 

Error Message


Msg 902, Level 16, State 1, Line 5
To change the NAME, the database must be in state in which a checkpoint can be executed.


TroubleShooting

Get Database State


declare @database sysname

set @database = 'TSD08282017'

select 
	  tblSD.[name]
	, tblSD.[is_read_only]
from   sys.databases tblSD
where  tblSD.[name] = @database

Output

Database came back as readonly

Remediation

Change Database State


alter database [TSD08282017] set READ_WRITE
   with rollback immediate

Column Stores Indexes – Query Comparison – Unpartitioned VS Partitioned

Objective

We had Column Store Tables created and now have tracking partitioned tables created, as well.

It is now time to see whether we benefit by partitioning the tables.

From our original set of tables, we only created tracking Partition tables for the Row Store Clustered Index and the Column Store Clustered Index.

For brevity sake, we skipped the Column Store Non-Clustered Index.

 

Query

Scenario

Our sample query will retrieve the top two sales within a date range.

SQL

Sample

Here is what the query likes when we target the [Fact].[SaleLargeRowStore] table.

BTW, that table is the unpartitioned Row Store table.

It has a Clustered Index on it’s primary key, Sale Key.


declare @dateBegin datetime
declare @dateEnd   datetime


set @dateBegin = '2013-04-15';
set @dateEnd = '2013-05-15';

; with cteYearCustomer --[Fact].[SaleLargeRowStore]
(
      [CustomerKey]
    , [profit]
)
as
(
    select  
              [CustomerKey] = tblFS.[Customer Key]
            , [profit] = max(tblFS.[profit])

    from  [Fact].[SaleLargeRowStore] tblFS

    where tblFS.[Invoice Date Key] between @dateBegin and @dateEnd

    group by
             tblFS.[Customer Key]

)

select top 2 *

from    cteYearCustomer cteYC

order by
        [profit] desc


Full


dbcc dropcleanbuffers with no_infomsgs
go

use [WideWorldImportersDW]
go

set nocount on
go

set statistics io on
go

declare @dateBegin datetime
declare @dateEnd   datetime


set @dateBegin = '2013-04-15';
set @dateEnd = '2013-05-15';

-- [Fact].[SaleLargeRowStore] 
declare @dateRSCBegin datetime
declare @dateRSCEnd datetime

declare @dateRSCPIndexIsChosenByEngineBegin datetime
declare @dateRSCPIndexIsChosenByEngineEnd datetime

declare @dateRSCPIndexIsClusteredBegin datetime
declare @dateRSCPIndexIsClusteredEnd datetime

declare @dateCSCIndexBegin datetime
declare @dateCSCIndexEnd datetime

declare @dateCSCPIndexBegin datetime
declare @dateCSCPIndexEnd datetime

declare @dateCSNCIndexBegin datetime
declare @dateCSNCIndexEnd datetime


declare @datediffSum bigint 

declare @NUMBER_OF_CHARS_IN_DIVIDER int

set @NUMBER_OF_CHARS_IN_DIVIDER = 180

set @dateRSCBegin = getdate();

print '@@version :- '
		+ cast(@@version as varchar(4000))

print replicate('=', @NUMBER_OF_CHARS_IN_DIVIDER);

print '[Fact].[SaleLargeRowStore]'
print '**************************'

-- [Fact].[SaleLargeRowStore]
; with cteYearCustomer --[Fact].[SaleLargeRowStore]
(
	  [CustomerKey]
	, [profit]
)
as
(
	select  
			  [CustomerKey] = tblFS.[Customer Key]
			, [profit] = max(tblFS.[profit])

	from  [Fact].[SaleLargeRowStore] tblFS

	where tblFS.[Invoice Date Key] between @dateBegin and @dateEnd

	group by
			 tblFS.[Customer Key]

)

select top 2 *

from    cteYearCustomer cteYC

order by
		[profit] desc

set @dateRSCEnd = getdate()

print replicate('=', @NUMBER_OF_CHARS_IN_DIVIDER);



/*
	 [Fact].[SaleLargeRowStoreClusteredPartition]
*/

print '[Fact].[SaleLargeRowStoreClusteredPartition] -- Clustered Index'
print '***************************************************************'

set @dateRSCPIndexIsClusteredBegin = getdate()

; with cteYearCustomer --[Fact].[SaleLargeRowStoreClusteredPartition] --Clustered Index
(
	  [CustomerKey]
	, [profit]
)
as
(
	select  
			  [CustomerKey] = tblFS.[Customer Key]
			, [profit] = max(tblFS.[profit])

	from [Fact].[SaleLargeRowStoreClusteredPartition]  tblFS with ( INDEX = 1)

	where tblFS.[Invoice Date Key] between @dateBegin and @dateEnd

	group by
			 tblFS.[Customer Key]

)

select top 2 *

from    cteYearCustomer cteYC

order by
		[profit] desc

set @dateRSCPIndexIsClusteredEnd = getdate()

print replicate('=', @NUMBER_OF_CHARS_IN_DIVIDER);

exec('dbcc dropcleanbuffers with no_infomsgs')

/*
	 [Fact].[SaleLargeRowStoreClusteredPartition]
*/

print '[Fact].[SaleLargeRowStoreClusteredPartition] -- Chosen by SQL Engine'
print '********************************************************************'

set @dateRSCPIndexIsChosenByEngineBegin = getdate()

; with cteYearCustomer --[Fact].[SaleLargeRowStoreClusteredPartition] -- Chosen by SQL Engine
(
	  [CustomerKey]
	, [profit]
)
as
(
	select  
			  [CustomerKey] = tblFS.[Customer Key]
			, [profit] = max(tblFS.[profit])

	from [Fact].[SaleLargeRowStoreClusteredPartition]  tblFS --with ( INDEX = 1)

	where tblFS.[Invoice Date Key] between @dateBegin and @dateEnd

	group by
			 tblFS.[Customer Key]

)

select top 2 *

from    cteYearCustomer cteYC

order by
		[profit] desc

set @dateRSCPIndexIsChosenByEngineEnd = getdate()

print replicate('=', @NUMBER_OF_CHARS_IN_DIVIDER);



/*
	[Fact].[SaleLargeColumnStoreClustered]
*/
print '[Fact].[SaleLargeColumnStoreClustered]'
print '**************************************'

set @dateCSCIndexBegin = getdate()

; with cteYearCustomer -- [Fact].[SaleLargeColumnStoreClustered]
(
	  [CustomerKey]
	, [profit]
)
as
(
	select  
			  [CustomerKey] = tblFS.[Customer Key]
			, [profit] = max(tblFS.[profit])

	from  [Fact].[SaleLargeColumnStoreClustered] tblFS

	where tblFS.[Invoice Date Key] between @dateBegin and @dateEnd

	group by
			--  tblY.[year]
			 tblFS.[Customer Key]

)

select top 2 *

from    cteYearCustomer cteYC

order by
		[profit] desc

set @dateCSCIndexEnd = getdate()

print replicate('=', @NUMBER_OF_CHARS_IN_DIVIDER);

/*
	[Fact].[SaleLargeColumnStoreClusteredPartition] 
*/

print '[Fact].[SaleLargeColumnStoreClusteredPartition]'
print '**********************************************'

set @dateCSCPIndexBegin = getdate()

; with cteYearCustomer -- [Fact].[SaleLargeColumnStoreClusteredPartition] 
(
	  [CustomerKey]
	, [profit]
)
as
(
	select  
			  [CustomerKey] = tblFS.[Customer Key]
			, [profit] = max(tblFS.[profit])

	from  [Fact].[SaleLargeColumnStoreClusteredPartition] tblFS

	where tblFS.[Invoice Date Key] between @dateBegin and @dateEnd

	group by
			 tblFS.[Customer Key]

)

select top 2 *

from    cteYearCustomer cteYC

order by
		[profit] desc

set @dateCSCPIndexEnd = getdate()

print replicate('=', @NUMBER_OF_CHARS_IN_DIVIDER)

/*
	[Fact].[SaleLargeColumnStoreNonClustered] 
*/

print '[Fact].[SaleLargeColumnStoreNonClustered]'
print '*****************************************'

set @dateCSNCIndexBegin = getdate()

; with cteYearCustomer -- [Fact].[SaleLargeColumnStoreNonClustered 
(
	  [CustomerKey]
	, [profit]
)
as
(
	select  
			  [CustomerKey] = tblFS.[Customer Key]
			, [profit] = max(tblFS.[profit])

	from  [Fact].[SaleLargeColumnStoreNonClustered] tblFS

	where tblFS.[Invoice Date Key] between @dateBegin and @dateEnd

	group by
			 tblFS.[Customer Key]

)

select top 2 *

from    cteYearCustomer cteYC

order by
		[profit] desc

set @dateCSNCIndexEnd = getdate()


print replicate('=', @NUMBER_OF_CHARS_IN_DIVIDER)

set @datediffSum =
			        isNull(datediff(millisecond, @dateRSCBegin, @dateRSCEnd), 0)
				  + isNull(datediff(millisecond, @dateRSCPIndexIsChosenByEngineBegin, @dateRSCPIndexIsChosenByEngineEnd), 0)
				  + isNull(datediff(millisecond, @dateRSCPIndexIsClusteredBegin, @dateRSCPIndexIsClusteredEnd), 0)
				  + isNull(datediff(millisecond, @dateCSCIndexBegin, @dateCSCIndexEnd), 0)
				  + isNull(datediff(millisecond, @dateCSCPIndexBegin, @dateCSCPIndexEnd), 0)
				  + isNull(datediff(millisecond, @dateCSNCIndexBegin, @dateCSNCIndexEnd), 0)


select 

		  [sourceID] = [sourceID]
		,  =  
		, [dateStart] = dateStart
		, [dateEnd] = dateEnd
		, [durationMilliseconds] =  [duration] 
		, [%] = cast([%] as decimal(6, 2))

from   
	(

		select 
				  [sourceID] = 1
				,  = '[Fact].[SaleLargeRowStore]'
				, [dateStart] = @dateRSCBegin
				, [dateEnd] = @dateRSCEnd
				, [duration] = datediff(millisecond, @dateRSCBegin, @dateRSCEnd)
				, [%] = (datediff(millisecond, @dateRSCBegin, @dateRSCEnd)) * 100.00 / @datediffSum


		union all

		select 
				  [sourceID] = 2.1
				,  = '[Fact].[SaleLargeRowStoreClusteredPartition] - Clustered Index'
				, [dateStart] = @dateRSCPIndexIsClusteredBegin
				, [dateEnd] = @dateRSCPIndexIsClusteredEnd
				, [duration] = datediff(millisecond, @dateRSCPIndexIsClusteredBegin, @dateRSCPIndexIsClusteredEnd)
				, [%] = (datediff(millisecond, @dateRSCPIndexIsClusteredBegin, @dateRSCPIndexIsClusteredEnd)) * 100.00 / @datediffSum

		union all

		select 
				  [sourceID] = 2.2
				,  = '[Fact].[SaleLargeRowStoreClusteredPartition] - Chosen by Engine'
				, [dateStart] = @dateRSCPIndexIsChosenByEngineBegin
				, [dateEnd] = @dateRSCPIndexIsChosenByEngineBegin
				, [duration] = datediff(millisecond, @dateRSCPIndexIsChosenByEngineBegin, @dateRSCPIndexIsChosenByEngineEnd)
				, [%] = (datediff(millisecond, @dateRSCPIndexIsChosenByEngineBegin, @dateRSCPIndexIsChosenByEngineEnd)) * 100.00 / @datediffSum

		union all

		select 
				  [sourceID] = 3
				,  = '[Fact].[SaleLargeColumnStoreClustered]'
				, [dateStart] = @dateCSCIndexBegin
				, [dateEnd] = @dateCSCIndexEnd
				, [duration] = datediff(millisecond, @dateCSCIndexBegin, @dateCSCIndexEnd)
				, [%] = (datediff(millisecond, @dateCSCIndexBegin, @dateCSCIndexEnd)) * 100.00 / @datediffSum


		union all

		select 
				  [sourceID] = 4
				,  = '[Fact].[SaleLargeColumnStoreClusteredPartition]'
				, [dateStart] = @dateCSCPIndexBegin
				, [dateEnd] = @dateCSCPIndexEnd
				, [duration] = datediff(millisecond, @dateCSCPIndexBegin, @dateCSCPIndexEnd)
				, [%] = (datediff(millisecond, @dateCSCPIndexBegin, @dateCSCPIndexEnd)) * 100.00 / @datediffSum

		union all

		select 
				  [sourceID] = 5
				,  = '[Fact].[SaleLargeColumnStoreNonClustered]'
				, [dateStart] = @dateCSNCIndexBegin
				, [dateEnd] = @dateCSNCIndexEnd
				, [duration] = datediff(millisecond, @dateCSNCIndexBegin, @dateCSNCIndexEnd)
				, [%] = (datediff(millisecond, @dateCSNCIndexBegin, @dateCSNCIndexEnd)) * 100.00 / @datediffSum
		

	) tblA

order by

	  [sourceID] asc


Query Plan

Query Plan – v2014

Row Store – Unpartition Table ( Fact.SaleLargeRowStore )

Image

Explanation
  1. Missing Index
    • Improvement Projection
      • The Query Processor estimates that implementing the following index could improve the query cost by 93.1978%.
    • Index Statement
      • CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [Fact].[SaleLargeRowStore] ([Invoice Date Key]) INCLUDE ([Customer Key],[Profit])

Row Store – Partition Table ( Fact.SaleLargeRowStoreClusteredPartition ) – Index Chosen By Engine

Image

Explanation
  1. Missing Index
    • Improvement Projection
      • The Query Processor estimates that implementing the following index could improve the query cost by 93.1978%.
    • Index Statement
      • CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [Fact].[SaleLargeRowStore] ([Invoice Date Key]) INCLUDE ([Customer Key],[Profit])

Row Store – Partition Table ( Fact.SaleLargeRowStoreClusteredPartition ) – Index Chosen By Engine

Image

Column Store – Unpartitioned Table ( Fact.SaleLargeColumnStoreClustered )

Image

  1. Column Index Scan
    • With a Clustered Column Store Index, the Clustering Index is inclusive of all columns

Column Store – Clustered – Partitioned Table ( Fact.SaleLargeColumnStoreClusteredPartition )

Image

 

Explanation
  1. Column Index Scan
    • With a Clustered Column Store Index, the Clustering Index is inclusive of all columns

 

Column Store – Non Clustered – Non-Partitioned Table –  ( Fact.SaleLargeColumnStoreNonClustered)

Image

Explanation
  1. Table Scan
    • We do not have a targeted Index

 

Statistics I/O

Image

Statistics – v2014

Statistics I/O – v2016

Tabulated

 

Table v2014 v2016
[Fact].[SaleLargeRowStore]
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SaleLargeRowStore’. Scan count 1, logical reads 344481, physical reads 2, read-ahead reads 344467, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘SaleLargeRowStore’. Scan count 5, logical reads 344945, physical reads 3, read-ahead reads 344456, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
[Fact].[SaleLargeRowStoreClusteredPartition] – Clustered Index ( Hardcoded to use clustered Index )
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SaleLargeRowStoreClusteredPartition’. Scan count 13, logical reads 344557, physical reads 12, read-ahead reads 344531, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘SaleLargeRowStoreClusteredPartition’. Scan count 13, logical reads 344611, physical reads 10, read-ahead reads 344547, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
[Fact].[SaleLargeRowStoreClusteredPartition] – Chosen by SQL Engine
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SaleLargeRowStoreClusteredPartition’. Scan count 13, logical reads 37, physical reads 6, read-ahead reads 26, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘SaleLargeRowStoreClusteredPartition’. Scan count 13, logical reads 37, physical reads 6, read-ahead reads 54, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
[Fact].[SaleLargeColumnStoreClustered]
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  ( No References to workfile )
Table ‘SaleLargeColumnStoreClustered’. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 1032, lob physical reads 5, lob read-ahead reads 2706.  Table ‘SaleLargeColumnStoreClustered’. Scan count 2, logical reads 4, physical reads 2, read-ahead reads 8, lob logical reads 268, lob physical reads 5, lob read-ahead reads 497.

Table ‘SaleLargeColumnStoreClustered’. Segment reads 1, segment skipped 0.

[Fact].[SaleLargeColumnStoreClusteredPartition]
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  ( No References to workfile )
Table ‘SaleLargeColumnStoreClusteredPartition’. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 78, lob physical reads 1, lob read-ahead reads 90.  Table ‘SaleLargeColumnStoreClusteredPartition’. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
[Fact].[SaleLargeColumnStoreNonClustered]
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  ( No References to workfile )
Table ‘SaleLargeColumnStoreNonClustered’. Scan count 1, logical reads 344132, physical reads 0, read-ahead reads 344098, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘SaleLargeColumnStoreNonClustered’. Scan count 5, logical reads 344005, physical reads 0, read-ahead reads 343533, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Summary

Here a couple of findings:

  1. Vertical partitioning
    • Helpful
      • Especially when queries are date driven
      • Please do some prep work and determine which date columns users often target
    • Not as Helpful
      • In a multi-column Index, not as helpful when the Date Column is not the first column referenced in the Index
  2. SQL Server Version 2016/Enterprise Versus 2014/Developer Edition
    • Optimization
      • In v2016, Enterprise Edition
        • Workfile not referenced in Statistics IO
        • Segment Reads and Segments Skipped explicitly Stated
          • Table ‘SaleLargeColumnStoreClustered’. Segment reads 1, segment skipped 0.