Transact SQL – PARSENAME

Background

SQL Server’s parsename function allows us to tokenize a string.

Objectname

It is particularly useful when reviewing object names.

ParseName

Sample

SQL


set nocount on
go

set XACT_ABORT on
go

declare @tblObject TABLE
(
	  [id] int not null
		identity(1,1)

	, [objectName] nvarchar(512)
)

insert into @tblObject
(
	[objectname]
)
select '[dbo].[person]'

union all

select '[tempdb].[dbo].[person]'

union all

select '[hrdb].[tempdb].[dbo].[person]'

union all

select '[aws].[hrdb].[tempdb].[dbo].[person]'

select
		  tblO.objectName

		, [object]
			 = parsename(tblO.[objectName], 1)

		, [schema]
			= parsename(tblO.[objectName], 2)

		, [database]
			= parsename(tblO.[objectName], 3)

		, [server]
			= parsename(tblO.[objectName], 4)

		, [dataCenter]
			= parsename(tblO.[objectName], 5)

from   @tblObject tblO

Output

parseName.objectName.20181108.1258PM

Explanation

  1. Works well
    • Things are good till we get to a situation with more than 4 tokens
  2. fails
    • Null is returns for all tokens once the string contains more than 4 tokens

STRING_SPLIT

Sample

SQL

set nocount on
go

set XACT_ABORT on
go

declare @tblObject TABLE
(
      [id] int not null
        identity(1,1)

    , [objectName] nvarchar(512)
)

insert into @tblObject
(
    [objectname]
)
select '[dbo].[person]'

union all

select '[tempdb].[dbo].[person]'

union all

select '[hrdb].[tempdb].[dbo].[person]'

union all

select '[aws].[hrdb].[tempdb].[dbo].[person]'

select
          tblO.objectName

        , [row]
            = ROW_NUMBER() OVER
                (

                    partition by
                        tblO.objectName

                    ORDER BY
                            (
                                select
                                    null
                            ) asc

                ) 

        , tblOSS.[value]

from   @tblObject tblO

CROSS APPLY STRING_SPLIT(tblO.objectName, '.')  tblOSS

order by
          tblO.[id] asc
        , [row] asc

Output

spiltString.objectName.20181108.0115PM

Explanation

  1. Works well
  2. Issues
    • String_Split
      • Rendering
        • Results are displayed in vertical order; whereas most people will likely prefer it in Horizontally
      • Functionality
        • The position of each token in the original stream is lost

XQuery

Sample

Scenario 1

Objective

Form XML Node

SQL

 set nocount on
go

set XACT_ABORT on
go

declare @valueSeparator     varchar(80)

declare @xmlRootNodeBegin   varchar(80)
declare @xmlRootNodeEnd     varchar(80)

declare @xmlNodeBegin   varchar(80)
declare @xmlNodeEnd     varchar(80)

set @valueSeparator = '.'

set @xmlRootNodeBegin = '';
set @xmlRootNodeEnd = '';

set @xmlNodeBegin = '';
set @xmlNodeEnd = '';

declare @tblObject TABLE
(
      [id] int not null
        identity(1,1)

    , [objectName] nvarchar(512)
)

insert into @tblObject
(
    [objectname]
)
select '[dbo].[person]'

union all

select '[tempdb].[dbo].[person]'

union all

select '[hrdb].[tempdb].[dbo].[person]'

union all

select '[aws].[hrdb].[tempdb].[dbo].[person]'

; with cteXQuery
as
(
    select
              tblO.[id]

            , tblO.objectName

            , [xmlRow] =
                cast
                (
                    (

                          @xmlRootNodeBegin

                        + replace

                        (
                              objectName
                            , @valueSeparator
                            , @xmlNodeEnd + @xmlNodeBegin
                        )

                        + @xmlRootNodeEnd
                    )
                    as xml
                ) 

    from   @tblObject tblO

)
select *

from   cteXQuery cteXQ

order by

        cteXQ.[id]

Output

objectName.XQuery.01.20181108.0202PM

Scenario 2

Objective

use XQuery to split XML Node

SQL
set nocount on
go

set XACT_ABORT on
go

declare @valueSeparator     varchar(80)

declare @xmlRootNodeBegin   varchar(80)
declare @xmlRootNodeEnd     varchar(80)

declare @xmlNodeBegin   varchar(80)
declare @xmlNodeEnd     varchar(80)

--set @valueSeparator = '\'
set @valueSeparator = '.'

set @xmlRootNodeBegin = '';
set @xmlRootNodeEnd = '';

set @xmlNodeBegin = '';
set @xmlNodeEnd = '';

declare @tblObject TABLE
(
      [id] int not null
        identity(1,1)

    , [objectName] nvarchar(512)
)

insert into @tblObject
(
    [objectname]
)
select '[dbo].[person]'

union all

select '[tempdb].[dbo].[person]'

union all

select '[hrdb].[tempdb].[dbo].[person]'

union all

select '[aws].[hrdb].[tempdb].[dbo].[person]'

; with cteXQuery
as
(
    select
              tblO.[id]

            , tblO.objectName

            , [xmlRow] =
                cast
                (
                    (

                          @xmlRootNodeBegin

                        + replace

                        (
                              objectName
                            , @valueSeparator
                            , @xmlNodeEnd + @xmlNodeBegin
                        )

                        + @xmlRootNodeEnd
                    )
                    as xml
                ) 

    from   @tblObject tblO

)
, cteXQueryNode
as
(

    select
              [id]

            , cteXQ.objectName

            , cteXQ.xmlRow

            , [rowNumber]
                = ROW_NUMBER()
                    OVER
                    (
                        PARTITION BY
                              [objectName]

                        ORDER BY
                              [objectName] 

                    )

            , [xmlNode]
                = n.value('.','varchar(4000)') 

    from   cteXQuery cteXQ

    cross apply [cteXQ].xmlRow.nodes('/Root/Node') m(n)

)
select *

from   cteXQueryNode

order by
          [id]

Output

objectName.XQuery.02.20181108.0209PM

Scenario 2

Objective

use XQuery to split XML Node and also pivot columns

SQL

set nocount on
go

set XACT_ABORT on
go

declare @valueSeparator     varchar(80)

declare @xmlRootNodeBegin   varchar(80)
declare @xmlRootNodeEnd     varchar(80)

declare @xmlNodeBegin   varchar(80)
declare @xmlNodeEnd     varchar(80)

set @valueSeparator = '.'

set @xmlRootNodeBegin = '';
set @xmlRootNodeEnd = '';

set @xmlNodeBegin = '';
set @xmlNodeEnd = '';

declare @tblObject TABLE
(
      [id] int not null
        identity(1,1)

    , [objectName] nvarchar(512)
)

insert into @tblObject
(
    [objectname]
)
select '[dbo].[person]'

union all

select '[tempdb].[dbo].[person]'

union all

select '[hrdb].[tempdb].[dbo].[person]'

union all

select '[aws].[hrdb].[tempdb].[dbo].[person]'

; with cteXQuery
as
(
    select 

              tblO.[id]

            , tblO.objectName

            , [xmlRow] =
                cast
                (
                    (

                          @xmlRootNodeBegin

                        + replace

                        (
                              objectName
                            , @valueSeparator
                            , @xmlNodeEnd + @xmlNodeBegin
                        )

                        + @xmlRootNodeEnd
                    )
                    as xml
                ) 

    from   @tblObject tblO

)
, cteXQueryNode
as
(

    select
              [id]

            , cteXQ.objectName

            --, cteXQ.xmlRow

            , [rowNumber]
                = ROW_NUMBER()
                    OVER
                    (
                        PARTITION BY
                              [objectName]

                        ORDER BY
                              [objectName] 

                    )

            , [xmlNode]
                = n.value('.','varchar(4000)') 

    from   cteXQuery cteXQ

    cross apply [cteXQ].xmlRow.nodes('/Root/Node') m(n)

)
select
          [id]
        , tblP.objectName
        , [element1] = [1]
        , [element2] = [2]
        , [element3] = [3]
        , [element4] = [4]
        , [element5] = [5] 

from   cteXQueryNode

pivot
(
    max
    (
        [xmlNode]
    )

    for [rowNumber]

    in
            (
                 [1]
               , [2]
               , [3]
               , [4]
               , [5]

            )

) tblP

order by
        [id]
Output

objectName.XQuery.03.20181108.0213PM.PNG

Transact SQL and the Null Character

Background

Transact SQL is a very high level language and 99.99% of the time one does not really have to pay attention.

This morning for me was not one of those times that I did not have to be attentive.

Lineage

A quick following up to our last post :-

SQL Server – Transact SQL – Get Network Info
Link

In that post we discussed a couple of options for discovering network metadata per SQL Server Instances.

Query

Original Query

Here is our original query

SQL


select *

from sys.dm_server_registry tblDSR

where tblDSR.value_name = 'TcpDynamicPorts'

Output

sysDOTdm_server_registry__20180607_0918PM

Revised Query – Filter Out Null and Empty Records

In our revised query we will filter out null and empty records

SQL

select *

from sys.dm_server_registry tblDSR

where tblDSR.value_name = 'TcpDynamicPorts'

and tblDSR.value_data is not null

and tblDSR.value_data != ''

Output

sysDOTdm_server_registry__20180607_0918PM

Explanation

Our filter clauses “is Not null” and != ” did not help.

Revised Query – Filter Out Null Character

In our second attempt, we filter out records that simply have the NULL CHARACTER as there whole value

SQL

select *

from sys.dm_server_registry tblDSR

where tblDSR.value_name = 'TcpDynamicPorts'

and tblDSR.value_data is not null

and tblDSR.value_data != ''

and tblDSR.value_data != char(0)

Output

sysDOTdm_server_registry__20180607_0925AM

Explanation

Our filter clause is != char(0) and did discard the unwanted records.

SQL Server – Transact SQL – Get Network Info

Background

Wanted to review the IP Addresses for some of our SQL Server Instances that are hosted on Cloud Servers.

Outline

  1. CONNECTIONPROPERTY
  2. sys.dm_exec_connections
  3. sys.dm_tcp_listener_states

Code

CONNECTIONPROPERTY

SQL

Sample

SELECT 

          [netTransport]
            = CONNECTIONPROPERTY('net_transport')

        , [authScheme]
            = CONNECTIONPROPERTY('auth_scheme')

        , [ipAddress]
            = CONNECTIONPROPERTY('local_net_address')

        , [iPort]
            = CONNECTIONPROPERTY('local_tcp_port')

Output

connectionProperty_20181107_0732AM

sys.dm_exec_connections

SQL

Sample

SELECT
              [machineName]
                = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 

            , [sqlInstance]
                = SERVERPROPERTY('servername') 

            , [instance]
                = SERVERPROPERTY('InstanceName')

            , [ipAddress]
                = tblDMEC.[LOCAL_NET_ADDRESS]

            , [numberofConnections]
                = count(*)

 FROM sys.dm_exec_connections tblDMEC

 where
        (

            ( tblDMEC.LOCAL_NET_ADDRESS is not null )

        )

 group by

         tblDMEC.LOCAL_NET_ADDRESS

Output

sys.dm_exec_connections.20181107.0738AM.PNG

sys.dm_tcp_listener_states

SQL

Sample

SELECT * 

FROM   sys.dm_tcp_listener_states tblTLS

Output

sys.dm_tcp_listener_states.20181107.0811AM.PNG

SQL Server – Backup Type

Background

Quite familiar with the three types of backup that we have in SQL Server.

Database Backup Types

The database backup types are full, differential, and log.

Metadata

msdb.dbo.backupset

To track backups we can look in the [msdb].[dbo].[backupset] table.

Something Broke?

Today I was trying to do a restore and noticed that that the type column in the [msdb].[dbo].[backupset] table read ‘F‘.

backupset.type ( Legacy )

Here is the traditional mapping of backup operation to type.

Backup Operation Backup Type ( backupset.type)
Database D
Differential I
Transaction L

File Groups Backup

To better support Very Large Databases ( VLDB ), Microsoft added a couple of targeted functionalities.

LAB

Let us play around with targeted file groups backup.

Database Properties

Database Properties – Filegroups

GUI

databaseProperties_GUI_20180625_1047PM.png

Code

sp_helpfilegroup
Sample

use [DBLab]
go

exec sp_helpfilegroup
go

Output

sp_helpfilegroup__20180625__1054PM

sys.filegroups
Sample

use [DBLab]
go

select
        [filegroup]
        = tblSFG.[name]

    , [filegroupID]
        = tblSFG.[data_space_id]

    , [isReadOnly]
        = tblSFG.[is_read_only]

    , [isDefault]
        = tblSFG.is_default

from   sys.filegroups tblSFG

order by
	tblSFG.[name] asc

Output

 sysDOTfilegroups_20180625_1108PM

Explanation
  1. isReadOnly
    • fgAudioFiles
  2. isDefault
    • PRIMARY

Backup

Database Backup – Normal

Sample


use master
go

backup database [DBLab]
	to DISK='NUL:'
	with init
	    , format
		, description='normal' 

Output

database_backup_normal_20180625__1138PM

Explanation

  1. Data files
    • DBLab, fileAudioFile_001, fileVideo_001
  2. Log
    • DBLab_log

Review Backup Type

SQL

declare @dbname sysname

set @dbname = 'DBLab';

select top 1

          backup_set_id

        , [type]

        , [database]
            = tblBS.[database_name]

        , [description]
            = tblBS.[description]

from   [msdb].[dbo].[backupset] tblBS

where tblBS.[database_name] = @dbname

order by
        backup_set_id desc

Output

msdb__dbo__backupset__20180625__1138PM

Database Backup – Read/Write File Groups

Overview

Add read-write_filegroups to implicitly skip read-only file groups.

Sample


use master
go

backup database [DBLab]
    read_write_filegroups
	to DISK='NUL:'
	with init
	, format

Output

readwritefilegroups__20180625__1148PM

Explanation

  1. Included
    • Data files
      • DBLab, fileVideo_001
    • Log
      • DBLab_log
  2. Not Included (as readonly )
    • fileAudioFile__001

Review Backup Type

Output

readwritefilegroups__backupset_20180625__1152PM

Explanation
  1. Type=P
    • Partial

Database Backup – Specific filegroups

Overview

Use the filegroup marker to explicitly tag filegroups you want included.

Sample


use master
go

backup database [DBLab]
	filegroup='PRIMARY'
	to DISK='NUL:'
	with init
	    , format
	    , description='filegroup explicit ( primary)'

Output

filegroupsTag__20180626_1203AM

Explanation

  1. Included
    • Data files
      • DBLab
    • Log
      • DBLab_log
  2. Not Included (as not part of tagged filegroup)
    • fileAudioFile__001
    • fileVideo_001

Review Backup Type

Output

filegroupsTag__20180626_1205AM

Explanation
  1. Type=F
    • File Groups

backupset.type ( Revised )

Here is a revised mapping of backup operations that includes new file groups subtleties.

Backup Operation File Group Backup Type ( backupset.type)
Database D
Differential I
Transaction L
Partial Read Write file groups only / Skip Read Only file groups P
File Groups or Files  Explicitly listed file groups F

References

  1. Microsoft

Transact SQL – Schema – Operations

Background

Wanted to document a couple of operations one can take on schemas.

Outline

  1. Check Existence
  2. Create Schema
  3. Change Object’s Schema ( Transfer Object from one schema to another)
  4. Grant Permission

Action

Exist?

Syntax

schema_id()

Sample

set nocount on
go

set XACT_ABORT on
go

declare @schema sysname
declare @schemaID int

set @schema = 'security';

set @schemaID = schema_id(@schema);

if ( @schemaID is null )
begin

	print 'Schema ' + @schema + ' does not exist!'

end
else
begin

	print 'Schema ' + @schema + ' exists'

end

Create

Syntax

create schema [schema] authorization [owner];

Sample

if schema_id('security') is null
begin

	exec('create schema [security] authorization [dbo]');

end

Change Object Schema

Syntax


alter schema [schema-new]
   transfer [scheme-current].[object-current]

Sample


set nocount on;
go

set XACT_ABORT on;
go

declare @object sysname
declare @commit bit

set @object = 'permission';

set @commit = 0

if schema_id('security') is null
begin

    exec('create schema [security] authorization [dbo]');

end

begin tran

    --exec sp_help '[dbo].[permission]'

    select
              [schema] = tblSS.[name]
            , [object] = tblSO.[name]

    from   sys.objects tblSO

    inner join sys.schemas tblSS

            on tblSO.schema_id = tblSS.schema_id

    where  tblSO.[type] = 'U'

    and    tblSO.[name] = @object

    if object_id('[dbo].[permission]') is not null
    begin

        alter schema [security]
            transfer [dbo].[permission]

    end	

    -- exec sp_help '[security].[permission]'

    select
              [schema] = tblSS.[name]
            , [object] = tblSO.[name]

    from   sys.objects tblSO

    inner join sys.schemas tblSS

            on tblSO.schema_id = tblSS.schema_id

    where  tblSO.[type] = 'U'

    and    tblSO.[name] = @object

while (@@trancount > 0)
begin

    if (@commit = 1)
    begin

        print 'commit  tran'
        commit tran;

    end
    else
    begin

        print 'rollback tran'
        rollback tran;

    end

end

Grant Permissions

Syntax


    grant [permission] on [schema]::[schema-name] to [database-principal];

Sample


set nocount on
go

set XACT_ABORT on
go

declare @schema sysname
declare @schemaID int
declare @commit   bit

set @commit = 0

set @schema = 'security';

set @schemaID = schema_id(@schema);

begin tran

	if ( @schemaID is null )
	begin

		print 'Schema ' + @schema + ' does not exist!'

	end
	else
	begin

		print 'Schema ' + @schema + ' grant permission'

		grant select on schema::[security] to [public];

	end

while (@@trancount > 0)
begin

    if (@commit = 1)
    begin

        print 'commit  tran'
        commit tran;

    end
    else
    begin

        print 'rollback tran'
        rollback tran;

    end

end

Transact SQL – No Join Predicate – “Unioned” CTE

Background

In our last post, we laid the foundation for this post.

Foundation Post

In that previous post we covered two very simple examples that triggers the “No Join Predicate” warning.

Here is that post:

Transact SQL – Warning – “No Join Predicate”
Link

 

Common Table Expression – Union

Overview

In this post we will try to trigger same warning by using a Union within a common table expression.

Diagram

diagram_school_20180619_1102AM.png

Data

Base Table

data_baseTable_20180619_0945AM.png

Trip Participant

data_tripParticipant_20180619_1003AM.png

Stored Procedure

school.usp_TripParticipants_List

Code


use [DBLab]
go

if object_id('school.usp_TripParticipants_List') is null
begin

    exec('create procedure [school].[usp_TripParticipants_List] as ')

end
go

alter procedure [school].[usp_TripParticipants_List]
(
    @tripID bigint
)
as

begin

    set nocount on

    set XACT_ABORT on


    ; with cteSchoolBody
    (
          id
        , identifier
        , firstname
        , lastname
        , [poolID]
        , [pool]
    )
    as
    (

        select 
                 tblS.id
               , tblS.identifier
               , tblS.firstname
               , tblS.lastname
               , [poolID] = 1
               , [pool] = 'Student'

        from   [school].[student] tblS

        union all

        select 
                 tblF.id
               , tblF.identifier
               , tblF.firstname
               , tblF.lastname
               , [poolID] = 2
               , [pool] = 'Faculty'

        from   [school].[faculty] tblF

    )
    select 
              [tripID] = tblT.[id]

            , [tripName] = tblT.[name]

            , cteSB.[pool]

            , cteSB.lastname

            , cteSB.firstname

            , cteSB.identifier

    from   [school].[trip] tblT

    --inner join [school].[tripParticipant] tblTP
    inner join [tripParticipant] tblTP

        on tblT.id = tblTP.tripID

    inner join cteSchoolBody cteSB
        on  tblTP.participantID = cteSB.id
        /*
            intentionally left of participantTypeID
            and tblTP.[participantTypeID] 
				= cteSB.[poolID]
        */ 
    where (

            ( 
                tblT.[id] 
                    = isNull(@tripID, tblT.[id])
            )

          )

    order by
              [tripName]

            , [pool]

            , cteSB.lastname

            , cteSB.firstname

    
end
go




 

Output

tripParticipant_20180619_1012AM

Explanation
  1. Noticed duplicate records

 

Query Plan

qp_20180619_1015AM

Explanation
  1. Thought that missing the participantTypeID will trigger “No Join Predicate” warning.

Source Code

GitHub

DanielAdeniji/SQLServer.SampleDB.School
Link

Listening

Paul Simon – Take me to the Mardi Gras
Link

Transact SQL – Warning – “No Join Predicate”

 

Background

A couple of weeks ago, I noticed a warning in a query plan.

The warning read “No Join Predicate“.

Query Plan

Warning – No Join Predicate

Sample Queries

Conventional Join

Code

use [DBLab]
go

if object_id('school.usp_TripParticipants_List_NJP_Legacy') is null
begin

    exec('create procedure school.usp_TripParticipants_List_NJP_Legacy as ')

end
go

alter procedure [school].[usp_TripParticipants_List_NJP_Legacy]
as

begin

    ; with cteParticipant
    as
    (
        select *

        from   [school].[student]

        union 

        select *

        from   [school].[faculty]

    )
    select 

              [trip]
				 = tblT.[name]
            , [tripDate]
				 = [tblT].tripDate

            , [participant]
				=
				  tblP.firstname
				+ ' '
				+ tblP.lastname

    from
          [school].[tripParticipant] tblTP
        , [school].[trip] tblT
        , cteParticipant tblP

    where tblTP.tripID = tblT.id

    -- and   tblTP.participantID = tblP.id

end

go

Query Plan

school__usp_TripParticipants_List_NJ__20180618__1145A

Explanation
  1. In this example we are using the old classic join
  2. We have three tables, but only joined the first two tables

 

ANSI Join

Code

use [DBLab]
go

if object_id('school.usp_TripParticipants_List_NJP') is null
begin

    exec('create procedure school.usp_TripParticipants_List_NJP as ')

end
go

alter procedure [school].[usp_TripParticipants_List_NJP]
as

begin

    ; with cteParticipant
    as
    (
        select *

        from   [school].[student]

        union 

        select *

        from   [school].[faculty]

    )
    select 

              [trip] = tblT.[name]
            , [tripDate] = [tblT].tripDate
            , tblP.firstname
            , tblP.lastname

    from   [school].[tripParticipant] tblTP

    inner join [school].[trip] tblT

        on tblTP.tripID
            = tblT.id

    inner join cteParticipant tblP
        /*
            on tblTP.participantID
                = tblP.id
        */
        on tblTP.tripID
             = tblT.id

end

go

Query Plan

school__usp_TripParticipants_List_NJ__20180618__1150AM

Explanation
  1. In this example we are using ANSI join
  2. We have three tables and two join clauses
  3. Unfortunately the second join does not actually join the two tables that it is meant to join
    • We intentionally cut and pasted the early join.
    • But, did not modify the pasted clause and reference the actual tables we are trying to join

 

Summary

The samples listed above are simple and easy to correct.

The one I actually ran into is a bit more difficult to address.