SQL Server – Database Permissions – List

Background

There are different levels of permissions in SQL Server.

There are concentric permissions.

Inclusive are Instance, database, schema, and object.

And, others that are more dependant on the object type.

Database Permissions

Code


SELECT
            [database]
              = db_name()

         ,  [username] 
              = tblGranteeDP.[name]

         ,  [login]     
             = suser_sname(tblGranteeDP.[sid])

         ,  [permissionClass]
             = tblDP.class_desc

         ,  [permissionName] 
            = tblDP.[permission_name]

         ,  [permissionState] 
            = tblDP.state_desc

         ,  [principalType] 
            = tblGranteeDP.[type_desc]

FROM sys.database_permissions AS tblDP

INNER JOIN sys.database_principals AS tblGrantorDP   
    ON tblGrantorDP.principal_id = tblDP.grantor_principal_id

INNER JOIN sys.database_principals AS tblGranteeDP
    ON tblGranteeDP.principal_id = tblDP.grantee_principal_id

LEFT OUTER JOIN sys.server_principals AS tblGranteeSP
    ON tblGranteeDP.principal_id = tblGranteeSP.principal_id

/*
    Identifies class on which permission exists.
*/
where (tblDP.class = 0)

/*
    Exclude those with mere CONNECT permission
*/
and  tblDP.permission_name not in 
        (   
            'CONNECT'
        )

order by
      db_name() asc
    , tblGranteeDP.[name] asc
    , tblDP.[permission_name] asc

Output

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]

Column Stores Indexes & Partitioning – Foundational Work

Background

As data grows bigger and ages it becomes prudent to look at partitioning as an avenue to better reduce the overall footprint of data read from disk into memory.

Column Store by its very definition delivers on the promises of Vertical Partitioning.

Let us see review the steps we have to take to achieve Range Partitioning.

 

Design Goals

Our goal is to divvy up our data into years.

In each table that we will be targeting we will ensure that a date column exists.

And, hope to have one of those date columns track the occurrence date.

 

Overview

Here are the plumbing areas:

  1. Create new Filegroups and files within the new file groups
  2. Create Partitioning Function
  3. Create Partitioning Scheme

 

Steps

Platform

File Groups & Files

Objective

Create File groups for each business year.

And, create a single file in each file group.

SQL


USE [WideWorldImportersDW]   
go

-- Year NULL

if not exists  
(      
	select *      
	from   sys.filegroups tblSFG      
	where  tblSFG.name = 'fg_Partition_Year_Null'  
)  
begin      

	ALTER DATABASE [WideWorldImportersDW] 
		ADD FILEGROUP  [fg_Partition_Year_Null]   
		
end 
go

if not exists  
	(      
		select *      
		from   sys.master_files tblMF      
		where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
		and    tblMF.name = 'fg_Partition_Year_Null__01'  
	)  
begin 

   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
      NAME = [fg_Partition_Year_Null__01]   
      , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_Null_file_001.ndf'  
   )  
   TO FILEGROUP [fg_Partition_Year_Null]  
	
end 


-- Year 2010
if not exists  
(      
    select *      
    from   sys.filegroups tblSFG      
    where  tblSFG.name = 'fg_Partition_Year_2010'  
)  
begin

   ALTER DATABASE [WideWorldImportersDW]         
      ADD FILEGROUP  [fg_Partition_Year_2010]   

end 
    
if not exists  
(      
   select *      
   from   sys.master_files tblMF      
   where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
   and    tblMF.name = 'fg_Partition_Year_2010__01'  
)  
begin
   
   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
         NAME = [fg_Partition_Year_2010__01]   
       , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_2010_file_001.ndf'  
  )  
  TO FILEGROUP [fg_Partition_Year_2010]  

end 


-- Year 2011
if not exists  
(      
    select *      
    from   sys.filegroups tblSFG      
    where  tblSFG.name = 'fg_Partition_Year_2011'  
)  
begin

   ALTER DATABASE [WideWorldImportersDW]         
      ADD FILEGROUP  [fg_Partition_Year_2011]   

end 
    
if not exists  
(      
   select *      
   from   sys.master_files tblMF      
   where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
   and    tblMF.name = 'fg_Partition_Year_2011__01'  
)  
begin
   
   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
         NAME = [fg_Partition_Year_2011__01]   
       , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_2011_file_001.ndf'  
  )  
  TO FILEGROUP [fg_Partition_Year_2011]  

end 


-- Year 2012
if not exists  
(      
    select *      
    from   sys.filegroups tblSFG      
    where  tblSFG.name = 'fg_Partition_Year_2012'  
)  
begin

   ALTER DATABASE [WideWorldImportersDW]         
      ADD FILEGROUP  [fg_Partition_Year_2012]   

end 
    
if not exists  
(      
   select *      
   from   sys.master_files tblMF      
   where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
   and    tblMF.name = 'fg_Partition_Year_2012__01'  
)  
begin
   
   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
         NAME = [fg_Partition_Year_2012__01]   
       , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_2012_file_001.ndf'  
  )  
  TO FILEGROUP [fg_Partition_Year_2012]  

end 


-- Year 2013
if not exists  
(      
    select *      
    from   sys.filegroups tblSFG      
    where  tblSFG.name = 'fg_Partition_Year_2013'  
)  
begin

   ALTER DATABASE [WideWorldImportersDW]         
      ADD FILEGROUP  [fg_Partition_Year_2013]   

end 
    
if not exists  
(      
   select *      
   from   sys.master_files tblMF      
   where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
   and    tblMF.name = 'fg_Partition_Year_2013__01'  
)  
begin
   
   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
         NAME = [fg_Partition_Year_2013__01]   
       , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_2013_file_001.ndf'  
  )  
  TO FILEGROUP [fg_Partition_Year_2013]  

end 


-- Year 2014
if not exists  
(      
    select *      
    from   sys.filegroups tblSFG      
    where  tblSFG.name = 'fg_Partition_Year_2014'  
)  
begin

   ALTER DATABASE [WideWorldImportersDW]         
      ADD FILEGROUP  [fg_Partition_Year_2014]   

end 
    
if not exists  
(      
   select *      
   from   sys.master_files tblMF      
   where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
   and    tblMF.name = 'fg_Partition_Year_2014__01'  
)  
begin
   
   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
         NAME = [fg_Partition_Year_2014__01]   
       , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_2014_file_001.ndf'  
  )  
  TO FILEGROUP [fg_Partition_Year_2014]  

end 


-- Year 2015
if not exists  
(      
    select *      
    from   sys.filegroups tblSFG      
    where  tblSFG.name = 'fg_Partition_Year_2015'  
)  
begin

   ALTER DATABASE [WideWorldImportersDW]         
      ADD FILEGROUP  [fg_Partition_Year_2015]   

end 
    
if not exists  
(      
   select *      
   from   sys.master_files tblMF      
   where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
   and    tblMF.name = 'fg_Partition_Year_2015__01'  
)  
begin
   
   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
         NAME = [fg_Partition_Year_2015__01]   
       , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_2015_file_001.ndf'  
  )  
  TO FILEGROUP [fg_Partition_Year_2015]  

end 


-- Year 2016
if not exists  
(      
    select *      
    from   sys.filegroups tblSFG      
    where  tblSFG.name = 'fg_Partition_Year_2016'  
)  
begin

   ALTER DATABASE [WideWorldImportersDW]         
      ADD FILEGROUP  [fg_Partition_Year_2016]   

end 
    
if not exists  
(      
   select *      
   from   sys.master_files tblMF      
   where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
   and    tblMF.name = 'fg_Partition_Year_2016__01'  
)  
begin
   
   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
         NAME = [fg_Partition_Year_2016__01]   
       , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_2016_file_001.ndf'  
  )  
  TO FILEGROUP [fg_Partition_Year_2016]  

end 

-- Year 2017
if not exists  
(      
    select *      
    from   sys.filegroups tblSFG      
    where  tblSFG.name = 'fg_Partition_Year_2017'  
)  
begin

   ALTER DATABASE [WideWorldImportersDW]         
      ADD FILEGROUP  [fg_Partition_Year_2017]   

end 
    
if not exists  
(      
   select *      
   from   sys.master_files tblMF      
   where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
   and    tblMF.name = 'fg_Partition_Year_2017__01'  
)  
begin
   
   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
         NAME = [fg_Partition_Year_2017__01]   
       , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_2017_file_001.ndf'  
  )  
  TO FILEGROUP [fg_Partition_Year_2017]  

end 

Partition Function

Objective

  1. Create a new Partition Function
  2. Indicate Column’s data type
    • date
  3. Range Type
    • Left or Right
      • Right Values
  4. Values
    • null
      • When date is null
      • When null entries are accounted for, please be sure to use convert function
      • Else
        • Msg 7705, Level 16, State 1
        • Could not implicitly convert range values type specified at ordinal 1 to partition function parameter type

SQL

Code

 CREATE PARTITION FUNCTION [pfnYear]
 ( 
    date 
 )     
 AS RANGE RIGHT FOR VALUES    
 (       
	  convert ( date , null ) 
	, '2010-01-01'
	, '2011-01-01'
	, '2012-01-01'
	, '2013-01-01'
	, '2014-01-01'
	, '2015-01-01'
	, '2016-01-01'
	, '2017-01-01'
	, '2018-01-01'
	, '2019-01-01'
	, '2020-01-01'  
) 

Partition Scheme

SQL



CREATE PARTITION SCHEME [pschemeYear]   
AS PARTITION [pfnYear]    
TO  
(       
	  [fg_Partition_Year_Null]
	, [fg_Partition_Year_2010]
	, [fg_Partition_Year_2011]
	, [fg_Partition_Year_2012]
	, [fg_Partition_Year_2013]
	, [fg_Partition_Year_2014]
	, [fg_Partition_Year_2015]
	, [fg_Partition_Year_2016]
	, [fg_Partition_Year_2017]
	, [fg_Partition_Year_2018]
	, [fg_Partition_Year_2019]
	, [fg_Partition_Year_2020]
	, [fg_Partition_Year_Next]  
	
) 

Summary

To make it easier to come back and edit our documentation, we will address using the underlying Partitioning components in a secondary post

 

Transact SQL – Error – “The associated partition function [pfn] generates more partitions than there are file groups mentioned in the scheme [scheme]”

Background

Trying to automate scripts for auto-creation Database File Partition Objects ( FileGroups \ Files \ Functions \ Schemes).

Error

While testing out the script for generating the Partition Scheme, arrived at the error pasted below:

The associated partition function ‘pfnYear‘ generates more partitions than there are file groups mentioned in the scheme ‘pschemeYear‘.

 

Code

Original Code

Partition Scheme

Explanation

  1. Partition Schema
    • Name :- pschemeYear
  2. Partition Function
    • Name :- pfnYear
  3. Filegroup
    • List of Database File groups

SQL


CREATE PARTITION SCHEME [pschemeYear]
AS PARTITION [pfnYear]
TO
(
       fg_Partition_Year_2010, fg_Partition_Year_2011
     , fg_Partition_Year_2012, fg_Partition_Year_2013
     , fg_Partition_Year_2014, fg_Partition_Year_2015
     , fg_Partition_Year_2016, fg_Partition_Year_2017
     , fg_Partition_Year_2018, fg_Partition_Year_2019
     , fg_Partition_Year_2020
)

Partition Function

Explanation

  1. Partition Function
    • Name :- pfnYear
  2. Values
    • List of dates

SQL


CREATE PARTITION FUNCTION [pfnYear](datetime)
AS RANGE RIGHT FOR VALUES
(
     N'2010-01-01T00:00:00.000', N'2011-01-01T00:00:00.000'
   , N'2012-01-01T00:00:00.000', N'2013-01-01T00:00:00.000'
   , N'2014-01-01T00:00:00.000', N'2015-01-01T00:00:00.000'
   , N'2016-01-01T00:00:00.000', N'2017-01-01T00:00:00.000'
   , N'2018-01-01T00:00:00.000', N'2019-01-01T00:00:00.000'
   , N'2020-01-01T00:00:00.000'
)
GO

 

Revised Code

Outline

There are a few workarounds:

  • Create an extra file group for outliers
  • Assign outliers to one of the existing file groups
  • Assign all partition schemes to single filegroup

Sample

Use Default Primary group

Explanation

We can use the default primary group.

SQL

CREATE PARTITION SCHEME [pschemeYear]
AS PARTITION [pfnYear]
TO
(
       PRIMARY -- Use default file group ( PRIMARY )
     , fg_Partition_Year_2010, fg_Partition_Year_2011
     , fg_Partition_Year_2012, fg_Partition_Year_2013
     , fg_Partition_Year_2014, fg_Partition_Year_2015
     , fg_Partition_Year_2016, fg_Partition_Year_2017
     , fg_Partition_Year_2018, fg_Partition_Year_2019
     , fg_Partition_Year_2020
)

Create extra File Group

Explanation

We can use the default primary group.

SQL
SQL – Create Filegroup

USE [WideWorldImportersDW]
go
if not exists
(
	select *
	from   sys.filegroups tblSFG
	where  tblSFG.name = 'fg_Partition_Year_Base'
)
begin

      ALTER DATABASE [WideWorldImportersDW]
	     ADD FILEGROUP  [fg_Partition_Year_Base]   

end 

SQL – Add File to Created Filegroup

ALTER DATABASE [WideWorldImportersDW]
ADD FILE
(
	  NAME = [fg_Partition_Year_Base__01]
	, FILENAME = 'C:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_Base_file_001.ndf'
	, MAXSIZE = UNLIMITED
	, FILEGROWTH = 100MB
)
TO FILEGROUP [fg_Partition_Year_Base]
SQL – Create Partition Scheme against Partition Function across fie groups

CREATE PARTITION SCHEME [pschemeYear]
AS PARTITION [pfnYear]
TO
(
      [fg_Partition_Year_2010], [fg_Partition_Year_2011]
    , [fg_Partition_Year_2012], [fg_Partition_Year_2013]
    , [fg_Partition_Year_2014], [fg_Partition_Year_2015]
    , [fg_Partition_Year_2016], [fg_Partition_Year_2017]
    , [fg_Partition_Year_2018], [fg_Partition_Year_2019]
    , [fg_Partition_Year_2020]
    , [fg_Partition_Year_Base]  -- extra file group

) 

Create all partitions on same File Group

Explanation

We can simply create all partitions on same file group

SQL
SQL – Create all Partitions on same filegroup

CREATE PARTITION SCHEME [pschemeYear]
AS PARTITION [pfnYear]
ALL TO
   (
     [fg_Partition_Year_Base]
   )

SQL Server – The dbo user

Background

There is a database that we restore nightly from Production.

The user has been doing a bit of acceptance testing and they called us this morning to let us know that they have too much access.

What a thing, user complaining of too much access?

 

TroubleShooting

Session

Run As User, Check User

CURRENT_USER

SQL

set nocount on
go

set XACT_ABORT on
go

use [pin]
go

DECLARE @login NVARCHAR(256)
DECLARE @user  NVARCHAR(256);

select 
			[STAGE]
				 = 'Before Context Switch'
		  , [SYTEM_USER_BEFORE_SWITCH] 
				= SYSTEM_USER
		  , [CURRENT_USER_IN_DB] 
				= CURRENT_USER
		  , [isSrvRoleMember] 
				= IS_SRVROLEMEMBER('sysadmin', SYSTEM_USER)	
;		

	
EXECUTE AS LOGIN = 'LAB\AnnMargaret'

select 
		    [STAGE] 
				= 'Within Context Switch'
		  , [SYTEM_USER] 
				= SYSTEM_USER
		  , [CURRENT_USER_IN_DB]
				= CURRENT_USER
		  , [isSrvRoleMember]
				= IS_SRVROLEMEMBER('sysadmin', SYSTEM_USER)	


/*

	select top 10 *
	from   dbo.pintbl

*/

--setuser
REVERT

select 
		    [STAGE] = 'Post Context Switch'
		  , [SYTEM_USER] = SYSTEM_USER
		  , [CURRENT_USER_IN_DB] = CURRENT_USER
		  , [isSrvRoleMember] = IS_SRVROLEMEMBER('sysadmin', SYSTEM_USER)	
;		


Image

Explanation
  1. We have 3 sections above
    • Section 1
      • Before Any User Context Change
        • I,the DBA, have all access
    • Section 2
      • Upon User Context Change
        • We use ‘SetUser’ or ‘Execute as Login’ to change the User
        • Queried to ensure user context change
          • SYSTEM_USER
            • The changed User Login account
          • CURRENT_USER
            • If user has db_owner privileges, then dbo returned
            • Else the user’s name in the contextual db
    • Section 3
      • Once change is reverted
        • We are back to ourselve

 

Database

DBO ROLE

sys.database_role_members

SQL

select 
		  [account]
			= tblSDP.[name]

		, [accountType] 
			= tblSDP.[type_desc]

		, [role] 
			= tblSDPR.[name]

from   sys.database_principals tblSDP

inner join sys.database_role_members tblSDRM

		on tblSDP.principal_id = tblSDRM.member_principal_id

inner join sys.database_principals tblSDPR

		on tblSDRM.role_principal_id = tblSDPR.principal_id

where tblSDPR.[name] = 'db_owner'

-- skip user dbo
and   tblSDP.[name] != 'dbo' 

;


Output

 

Explanation
  1. Only one account, dataVal, has db_owner privileges
    • dataVal
      • dataVal is a SQL Account
      • We are looking to track down a Windows Login

 

“DB OWNER”

 sys.databases

SQL


use [pin]
go

select 
		 [database]
			= tblSD.[name]
		, [ownerSID]
			= tblSD.[owner_sid]
		, [owner]
			= suser_sname(tblSD.[owner_sid])

from   sys.databases tblSD

where  tblSD.[name] = db_name()


Image

Explanation
  1. Database’s owner is sa

 

SQL Instance

Server Role Member

sp_helpsrvrolemember

SQL

declare @srvrolename  sysname

set @srvrolename = 'sysadmin'

EXEC [master].[dbo].sp_helpsrvrolemember
		  @srvrolename = @srvrolename

 

Image

Explanation
  1. Our Login is not listed as having sysadmin privileges

 

Account Membership

xp_logininfo

SQL

declare @acctname sysname
declare @option   sysname

set @acctname = 'BUILTIN\Administrators'
set @option = 'members';

EXEC [master].[dbo].xp_logininfo 
		  @acctname = @acctname
		, @option = @option 
		;

Output

Explanation

We found the account is part of the Local Administrator group.

 

Summary

Here is how the account we are tracking ended up with more information than we thought it would have:

  1. OS
    • Active Directory Account
      • Part of Machine’s Local Administrator group
  2. SQL Instance
    • SysAdmin
      • BUILTIN\Administrators has sysadmin privileges
  3. SQL Database
    • dbo
      • All logins that sysadmin have dbo access in each database

 

Sql Server – ColumnStore Indexes – Metadata – Encoding Type & Sizes

Background

Another post on metadata surrounding Column Store Indexes.

Lineage

  1. Sql Server – ColumnStore Indexes – Metadata – Stats & Fragmentation
    Published On :- 2017-Sept-2nd
    Link

 

Encoding

Tabulate

ID Type Description Data Type
1 VALUE_BASED non-string/binary with no dictionary (very similar to 4 with some internal variations) Non String
2 VALUE_HASH_BASED non-string/binary column with common values in dictionary Non String
3 STRING_HASH_BASED string/binary column with common values in dictionary String/Binary
4 STORE_BY_VALUE_BASED non-string/binary with no dictionary Non String/Binary
5 STRING_STORE_BY_VALUE_BASED string/binary with no dictionary String/Binary

 

Explanation

  1. Data types
    • Basically, we have two types String/Binary or non-string/non-binary
  2. Storage
    • Value
    • Hash Value

Metadata

Overview

  1. Column Store – Segment
    • sys.column_store_segments
      • Each Column is kept in each own segment.
      • And, encoded on scheme based on its data types and data richness
  2. Column Store – Row Groups
    • sys.column_store_row_groups
      • Column Indexes are vertically packaged into row groups
      • Each Row Group contains about a million records

 

Code

Column Segment

sys.column_store_segments

SQL

; with cteEncodingType
(
	  [encodingTypeID]
	, [encodingType]
	, [encodingDescription]
)
as
(

	--Type of encoding used for that segment:
	select 
			  [id] = 1
			, [encodingType] = 'VALUE_BASED'
			, [encodingDescription] = 'non-string/binary with no dictionary (very similar to 4 with some internal variations)'
	
	union
	
	select 
			  2
			, 'VALUE_HASH_BASED'
			, 'non-string/binary column with common values in dictionary'
	
	union

	select 
			  3
			, 'STRING_HASH_BASED'
			, 'string/binary column with common values in dictionary'
	
	union
	
	select 
			  4
			, 'STORE_BY_VALUE_BASED'
			, 'non-string/binary with no dictionary'
	union

	select 
			  5
			, 'STRING_STORE_BY_VALUE_BASED'
			, 'string/binary with no dictionary'

)
select 
		[object]
			=   object_schema_name(tblSP.object_id)
				+ '.'
				+ object_name(tblSP.object_id)

		, tblCSRG.partition_number

		, [totalRows]
			= tblCSRG.total_rows

		, [deletedRows]
			= tblCSRG.deleted_rows

		, [sizeInBytes]
			= tblCSRG.size_in_bytes

		, [sizeInKB]
			= tblCSRG.size_in_bytes/1024

from   sys.column_store_row_groups tblCSRG

inner join sys.partitions tblSP

	on	tblCSRG.[object_id] = tblSP.[object_id]
	and tblCSRG.[partition_number] = tblSP.[partition_number]



 

Image

 

Explanation
  1. Based on Column Type ( data type ) and data variance, one of 5 encoding types is chosen

 

Column Segment

sys.column_store_segments

SQL

use [WideWorldImportersDW]
go

with cteRowGroup
(
		  [object]
		, [indexName]
		, [type]
		, [partitionNumber]
		, [rowGroupID]
		, [rowGroupIDMax]
		, [stateDescription]
		, [totalRows]
		, [deletedRows]
		, [sizeInBytes]
		, [sizeInKB]

)
as
(
	select 
		[object]
			=   object_schema_name(tblCSRG.object_id)
				+ '.'
				+ object_name(tblCSRG.object_id)

		, [indexName]
			= tblSI.[name]
		
		, [type]
			= tblSI.[type_desc]
						
		, [partitionNumber]
			= tblCSRG.partition_number

		, [rowGroupID]
			= tblCSRG.row_group_id

		, [rowGroupIDMax]
			= max ( tblCSRG.row_group_id )
				over(
					partition by
							  tblCSRG.object_id
							, tblCSRG.index_id
				  )

		, [stateDescription]
			= tblCSRG.state_description

		, [totalRows]
			= tblCSRG.total_rows

		, [deletedRows]
			= tblCSRG.deleted_rows

		, [sizeInBytes]
			= tblCSRG.size_in_bytes

		, [sizeInKB]
			= tblCSRG.size_in_bytes/1024

from   sys.column_store_row_groups tblCSRG


inner join sys.partitions tblSP

	on	tblCSRG.[object_id] = tblSP.[object_id]
	and tblCSRG.[index_id] = tblSP.[index_id]
	and tblCSRG.[partition_number] = tblSP.[partition_number]

inner join sys.indexes tblSI
	on  tblCSRG.object_id = tblSI.object_id
	and tblCSRG.index_id = tblSI.index_id

)
select 
		  [object]
		, [indexName]
		, [type]
		, [partitionNumber]
		, [rowGroupID]
		, [rowGroupCurrent]
			 = case 
					when ([rowGroupID] = rowGroupIDMax) then 'Yes'
					else 'No'
				end
		, [stateDescription]
		, [totalRows]
		, [deletedRows]
		, [sizeInBytes]
		, [sizeInKB]


from   cteRowGroup cteRG

order by
	  [object]
	, [indexName]
	, [partitionNumber]
	, [rowGroupID]

Image

 

Explanation
  1. Grouping
    • Column Indexes are grouped into Row Groups of about a million records each ( 1024 * 1024 = 1048576)
    • As a  million records are packaged into a Row Group it is filed away and a new Row Group started
    • The current Group will be the Max (Row Group ID) for that partition ( Object ID / Index ID / Partition ID )

Summary

We can see that depending on our data type and the variance of the data set, we will have access to specific sets of encoding.

Also, Columnar Indexes enjoy both the benefits of Column and Vertical partitioning.

 

References

  1. Microsoft
    • Technet
      • Database Engine Features and Tasks > Database Features > Indexes (Database Engine)
        • Column Store Indexes
          Link
    • Docs / SQL / Relational databases / System catalog views/
      • sys.column_store_row_groups (Transact-SQL)
        Link
      • sys.column_store_segments (Transact-SQL)
        Link
  2. Patrick Keisler
    • Everyday SQL
      • Monitor the Number of Deleted Rows in a Clustered Columnstore Index
        Link
  3. Greg Low
    • Greg Low (The Bit Bucket: IDisposable)
      • Rebuild clustered columnstore indexes when they require maintenance
        Link
  4. SqlTimes
    • Playing with Database Servers
      • What is a hobt-id?
        Link