Transact SQL – Convert Hexadecimal number to Decimal

Background

Convert Hexadecimal number ( base 16) to Decimal number ( base 16)

Code

Here are the steps for converting an Hexadecimal number to Integer

  1. Convert any occurence of char (a-f) to its decimal equivalent
    • a -> 10
    • b -> 11
    • c -> 12
    • d -> 13
    • e -> 14
    • f -> 15
  2. Count number of Characters
  3. Starting from left most character navigate to right most character
    • Multiplier
      • Will be length of hex number
      • Assign length as place-holder-1
    • Get character as place-holder-2
    • place-holder-3 = ( place-holder-2 ) * ( 16 raised to ( place-holder-1  – 1  ) )
    • Accumulate place-holder-3

SQL


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

    exec('CREATE FUNCTION [dbo].[itvf_hexToDec]
            (
                @number varchar(60)
            )
            RETURNS @itvfResult TABLE
            (
                  [hex] varchar(60)
                , [dec]  bigint
            )
            AS
            BEGIN

                return

            END
        '
        )

end
go

ALTER FUNCTION [dbo].[itvf_hexToDec]
(
    @number varchar(60)
)
RETURNS @itvfResult TABLE
(
      [hex] varchar(60)
    , [dec]  bigint
)
AS
BEGIN

	-- =============================================
	-- Author:		Daniel Adeniji
	-- Create date: 2019-03-03
	-- Description:	Converts Number in Hex Based to Dec Base
	-- =============================================

    declare @id     smallint
    declare @len    smallint
    declare @nth    smallint

    declare @rc      int
    declare @result  bigint

    declare @ch      char(1)
    declare @chAsInt int

	declare @CHAR_ASCII_a tinyint

	set @CHAR_ASCII_a = 97

	-- Convert to lower case
    set @number = lower(@number)

	-- Strip out leading 0x if present
    if left(@number,2) = '0x'
    begin

        set @number = substring(@number, 3, 255)

    end

	-- Get Length
    set @len= len(@number)

    set @id = 1
    set @nth = @len
    set @result = 0

	/*
		Transverse number one character at a time
	*/
    while (@id = '0' ) and (@ch = 'a' ) and (@ch <= 'f') )
        begin

            set @chAsInt = ascii(@ch)

            set @chAsInt = 10 + @chAsInt - @CHAR_ASCII_a

        end	

		-- raise current number to 16 raised to position in string
        set @rc = @chAsInt * power(16, ( @nth -1 ) )

		-- accumulate number
        set @result = @result + @rc

		-- move pointer
        set @id = @id + 1

		-- adjust nth power
        set @nth = @nth - 1

    end

    insert into @itvfResult
    (
          [hex]
        , [dec]
    )
    select
          @number
        , @result

    return

END
GO

grant select on [dbo].[itvf_hexToDec] to [public]
go

Sample


    declare @number varchar(60);

    set @number = 'A'
    set @number = '89'

    set @number = '190'

    set @number = '2710'

    select *

    from   [dbo].[itvf_hexToDec](@number)

Source Code Control

Github

  1. Function
    • Scaler
      • dbo.fn_hexToDec
        Link
    • Multi-Statement Value Function
      • dbo.itvf_hexToDec
        Link

 

Transact SQL – Conversion and Binary Data Type – Day 01

Background

Trying to convert an Hexadecimal number to Decimal, but ran into stumbling issues.

Referenced Source

  1. StackOverflow
    • MS SQL server – convert HEX string to integer
      Link

Convert to Binary and then to Int

Get Number, Length, and Length in Pairs

Code


declare @data varchar(100)
declare @dataLen int
declare @dataLenPair int

set @data = '0x89'

set @dataLen = len(@data) -2
set @dataLenPair =  (len(@data) -2)/2

Output

convertToBinary.01.20190302.1005AM

Convert To Int Using Convert Function

Code


declare @data varchar(100)
declare @dataLen int
declare @dataLenPair int

set @data = '0x89'

set @dataLen = len(@data) -2
set @dataLenPair =  (len(@data) -2)/2

select
      [@data] = @data
    , [@dataLen] = @dataLen
    , [@dataLenPair] = @dataLenPair

/*
	Convert To Int using Using Convert
*/
select
      [@data] = @data
    , [varbin] = CONVERT(INT, CONVERT(VARBINARY, @data) )
    , [varbin1] = CONVERT(INT, CONVERT(VARBINARY(1), @data) )

Output

convertToBinary.02.20190302.1010AM

Explanation

  1. The convert operator that uses varbinary, comes back with a high decimal number
  2. The one that uses varbinary(1) is much a smaller number

Convert To Int Using Cast Function

Code


declare @data varchar(100)
declare @dataLen int
declare @dataLenPair int

set @data = '0x89'

set @dataLen = len(@data) -2
set @dataLenPair =  (len(@data) -2)/2

select
      [@data] = @data
    , [@dataLen] = @dataLen
    , [@dataLenPair] = @dataLenPair

/*
	Convert to Int using Cast
*/
select
       [@data] = @data
     , [varbin] = CONVERT(INT, CAST(@data AS VARBINARY))
     , [varbin1] = CONVERT(INT, CAST(@data AS VARBINARY(1)))

Output

convertToBinary.03.20190302.1020AM

Explanation

  1. The cast operator that uses varbinary, comes back with a high decimal number
  2. The one that uses varbinary(1) is much a smaller number

Convert To Varbinary Using Cast Function

Code


declare @data varchar(100)
declare @dataLen int
declare @dataLenPair int

set @data = '0x89'

set @dataLen = len(@data) -2
set @dataLenPair =  (len(@data) -2)/2

select
          [@data] = @data
        , [@dataLen] = @dataLen
        , [@dataLenPair] = @dataLenPair

select
          [@data] = @data
        , [@varbin] = CAST(@data AS VARBINARY)
        , [@varbin1] = CAST(@data AS VARBINARY(1))

 

Output

convertToBinary.04.20190302.1024AM

Explanation

  1. The cast operator that uses varbinary, comes back with a high decimal number
  2. The one that uses varbinary(1) is much a smaller number

Conclusion

Please be very careful with the binary datatype when using it in the course of convert or cast function.

Couple of things to look for :-

  1. The size of the binary datatype has to be specified
  2. And, it needs to be hard-coded, as a variable can not be used

Dedicated

Dedicated to Stack Overflow and its members.

Noted members :-

  1. Pondlife

SQL Server – Auto Statistics – Naming Convention ( Day 2 )

Background

Quick follow up to our last post.

In this post, we track back and see if we can decipher the roots of the stats name.

Lineage

  1. SQL Server – Auto Created Stats – Naming Convention
    Link

 

SSMS

Here are the stats shown us via SQL Server Management Studio.

stats.raw.bible.kjv.20190302.0626AM

Goal

Let us relate the data to actual objects and columns.

 

Outline

  1. isAutoGenerated
    • Auto Generated Statistics have names starting with
      • _WA_Sys_
  2. statAsPeriodDelimeted
    • Replace underscore ( _ ) with period ( . )
    • Doing so allows us to use the parsename function
  3. objectIDToken
    • read statAsPeriodDelimeter and get first Octet from right
  4. columnIDToken
    • read statAsPeriodDelimeter and get second Octet from right
  5. objectIDAsHex
    • ObjectIDToken is an obvious number, but it is missing the 0x
    • Let us prefix it with 0x
  6. objectIDAsBinary
    • Convert ObjectIDAsHex to Binary’
    • Use convert/int
  7. objectIDAsInt
    • Convert objectIDAsBinary to objectIDAsInt
    • Use convert/int
  8. object Name
    • Get schema :- object_schema_name
    • Get object :- object_name
  9. Column Name
    • Join sys.objects to sys.columns
    • Map object_id and column_id

Code

SQL


set nocount on
go

use [bible]
go

declare @object sysname

set @object = '[dbo].[kjv]'

declare @tblStat table
(
      [id] tinyint not null
        identity(1,1)

    , [stat] sysname not null
    , [statKey] sysname not null

    , [isAutoGenerated]
        as  cast
            (
                case
                    when ( ltrim([stat]) like '_WA_Sys_%') then 1
                    else 0
                end
                as bit
            )

    , [statAsPeriodDelimited] sysname null

    , [objectIDToken]         varchar(20) null
    , [columnIDToken]	      varchar(8) null

    , [objectIDAsHex]		  varchar(30)
    , [objectIDAsBinary]	  varbinary(8)
    , [objectIDAsInt]         int null
    , [objectName]            varchar(512) null

    , [columnIDAsInt]		  int null
    , [columnName]            sysname null

)

insert into  @tblStat
(
      [stat]
    , [statKey]
)
exec sp_helpstats
           @objname =  @object
 --        , @results = 'ALL'

/*
    a) To allow us to use parsename as tokenizer, let us replace _ with period(.)
*/
update tblS

set    tblS.[statAsPeriodDelimited]
            = replace
              (
                  substring
                   (
                       [stat]
                     , 2
                     , 255
                    )
                  , '_'
                  , '.'
              )

from   @tblStat tblS

where  [isAutoGenerated] = 1

/*
    a) Get objectIDAsHex as first octet from right
    b) Get columnIDAsVarchar as second octet from right
*/
update tblS

set    tblS.[objectIDToken]
            = parseName
              (
                  [statAsPeriodDelimited]
                  , 1
              )

        , tblS.[columnIDToken]
            = parseName
              (
                  [statAsPeriodDelimited]
                  , 2
              )

from   @tblStat tblS

where  [isAutoGenerated] = 1

/*
    Get objectIDAsBinary
        a) Prefix ObjectIDAshex with 0x, this makes it Hex
*/
update tblS

set    tblS.[objectIDAsHex]
            =  '0x' + tblS.[objectIDToken]

from   @tblStat tblS

where  [isAutoGenerated] = 1

/*
    Get objectIDAsBinary
        a) Prefix ObjectIDAshex with 0x, this makes it Hex
*/
update tblS

set    tblS.[objectIDAsBinary]
            =  convert
                (
                      varbinary
                    , tblS.[objectIDAsHex]
                    , 1
                )

from   @tblStat tblS

where  [isAutoGenerated] = 1

/*
    Get ObjectIDAsInt
        a) Convert objectIDAsHex to Int
*/
update tblS

set    tblS.[objectIDAsInt]
            =  convert
                    (
                          int
                        , tblS.[objectIDAsBinary]
                    )
from   @tblStat tblS

/*
    Get Column ID
*/
update tblS

set    tblS.[columnIDAsInt]
            = tblS.columnIDToken

from   @tblStat tblS

where  ISNUMERIC(tblS.[columnIDToken]) = 1

/*
    Get Object Name
*/
update tblS

set tblS.[objectName]
            = object_schema_name(tblS.[objectIDAsInt])
                + '.'
                + object_name(tblS.[objectIDAsInt])

from   @tblStat tblS

where (

         ( tblS.[objectIDAsInt] is not null )

      )

/*
    Get Column Name
*/
update tblS

set tblS.[columnName] = tblSC.[name]

from   @tblStat tblS

inner join sys.columns tblSC

        on  tblS.[objectIDAsInt] = tblSC.[object_id]
        and tblS.[columnIDAsInt] = tblSC.[column_id]

select *

from   @tblStat

 

Output

stats.interpreted.bible.kjv.20190302.0621AM

Summary

The data shown via SSMS is not so opaque after all.

For instance :- _WA_Sys_00000002_3D5E1FD2

  1. _WA
  2. _Sys
  3. _00000002
    • Column ID 2
  4. _3D5E1FD2
    • Object ID in Hex
    • Convert Hex to Int to determine actual number

 

SQL Server – Auto Created Stats – Naming Convention

Background

As I was reviewing slow performing queries opted to see what statistics look like on some of the referenced tables.

SSMS

Within SSMS, accessed the table and looked underneath the statistics branch.

Greeted with a view that looks like this :

ssms.objectExplorer.statistics.20190301.0703AM

Here I am thinking what does this tell me.

Code

sp_helpstats

syntax


exec sp_helpstats
	  @objname = [object-name]
	, @results = 'ALL'
	; 

sample


exec sp_helpstats
	  @objname = '[dbo].[UP]'
	, @results = 'ALL'
	;  

Output

sp_helpstats.20190301.0709AM

Explanation

Thankfully I can see the column each auto-created statistics is mapped to.

Dedicated

My bike didn’t get stolen
Seen the place, but not from Compton
yet, loses is familiar

And, so here is one to one of the good ones, Joe S.

Listening

The Game, 50 Cent
Hate It Or Love It
Link

SQL Server – Linked Server – Stored Procedures – Parameter List

Background

Needing to quickly issue Stored Procedure calls against a Linked Server.

To do so need to know which parameters the SP expects to receive.

Code

Objective

Unfortunately it is difficult to seamlessly write a generic code that will apply against any Linked Server and database combination.

Naming Convention

Here is one that targets :-

  1. Linked Server Name :- LS
  2. Database :- dbContext

Please replace both with your Linked Server alias and contextual database.

Choices

  1. System Stored Procedure
    • sp_help
  2. System Catalog Views
  3. Information Schema Views

SQL

SQL Server System Views

Code

Syntax


exec [LS].[dbContext].dbo.sp_help 'object-name'

Sample

exec [LS].[dbContext].dbo.sp_help '[usp].[listNames]'

Output

sp_help.20190223.0815PM.PNG

SQL Server System Views

Code


; with cteDataType

(
      object_id
    , parameter_id
    , [datatype]
    , [maxLength]
    , [maxLenghtInferred]
)
as
(

    select
              tblP.object_id
            , tblP.parameter_id
            , tblST.[name]
            , tblP.max_length
            , [lengthInferred]
                =   case tblST.name

                        when 'varchar' then
                                '('
                                + cast
                                    (
                                        tblP.max_length as varchar(10)
                                    )
                                + ')'

                        when 'nvarchar' then
                                '('
                                + cast(tblP.max_length as varchar(10))
                                + ')'

                        else ''
                     end

    from  [LS].[dbContext].sys.parameters tblP 

    JOIN [LS].[dbContext].sys.types tblST
            ON  tblP.system_type_id = tblST.system_type_id
            AND tblP.user_type_id = tblST.user_type_id

)
SELECT

      [object]
        = quoteName(tblSS.[name])
            + '.'
            + quoteName(sp.[name])

    , [parameterId]
        = p.parameter_id

    , [parameter]
        = p.[name]

    , [datatype]
        = cteDT.[datatype]

    , [maxLenghth]
        = p.[max_length]

    , p.is_nullable

    , p.has_default_value

    , p.default_value

    , p.is_output

    , [parmDeclaration]
        =
              'declare '
            + p.[name]
            + ' '
            + cteDT.datatype
            + cteDT.[maxLenghtInferred]

            COLLATE DATABASE_DEFAULT

    , [parmSend]
        =
            case p.[parameter_id]
                when 1 then ' '
                else ', '
            end
            + p.[name]
            + ' = '
            + p.[name]

            COLLATE DATABASE_DEFAULT

FROM [LS].[dbContext].sys.procedures sp

JOIN [LS].[dbContext].sys.schemas tblSS
    ON sp.schema_id = tblSS.[schema_id]

JOIN [LS].[dbContext].sys.parameters p
    ON sp.object_id = p.object_id

INNER JOIN cteDataType cteDT
        on  p.object_id = cteDT.object_id
        and p.parameter_id = cteDT.parameter_id

order by
          tblSS.[name]
        , sp.[name]
        , p.parameter_id

Output

paramters.20190223.1143AM.PNG

INFORMATION_SCHEMA

SQL


; with cteDataType

(
      [catalog]
    , [schema]
    , [object]
    , parameter_id
    , [datatype]
    , [maxLength]
    , [maxLenghtInferred]
)
as
(

    select
          tblP.SPECIFIC_CATALOG
        , tblP.SPECIFIC_SCHEMA
        , tblP.SPECIFIC_NAME
        , tblP.ORDINAL_POSITION
        , tblP.[DATA_TYPE]
        , tblP.CHARACTER_MAXIMUM_LENGTH
        , [lengthInferred]
            =   case tblP.[DATA_TYPE]

                    when 'varchar' then
                            '('
                            + cast
                                (

                                    case
                                        when [tblP].[CHARACTER_MAXIMUM_LENGTH] =
                                            -1 then 'MAX'
                                        else [tblP].[CHARACTER_MAXIMUM_LENGTH]
                                    end
                                        as varchar(10)

                                )
                            + ')'

                    when 'nvarchar' then
                            '('
                            +   cast
                                (
                                    case
                                        when tblP.[CHARACTER_MAXIMUM_LENGTH] =
                                            -1 then 'MAX'
                                        else tblP.[CHARACTER_MAXIMUM_LENGTH]
                                    end
                                        as varchar(10)
                                )

                            + ')'

                    else ''
                 end

    from  [LS].[dbContext].INFORMATION_SCHEMA.PARAMETERS  tblP 

)

SELECT

      [object]
        = quoteName([sp].[SPECIFIC_SCHEMA])
            + '.'
            + quoteName(sp.[SPECIFIC_NAME])

    , [parameterId]
        = p.ORDINAL_POSITION

    , [parameter]
        = p.[PARAMETER_NAME]

    , [datatype]
        = cteDT.[datatype]

    , [maxLenghth]
        = p.[CHARACTER_MAXIMUM_LENGTH]

    , p.PARAMETER_MODE

    , [is_output]
        = case p.PARAMETER_MODE
                when 'IN' then 0
                when 'OUT' then 1
                when 'INOUT' then 1
          end   

    , [parmDeclaration]
        =
              'declare '
            + p.[PARAMETER_NAME]
            + ' '
            + cteDT.datatype
            + cteDT.[maxLenghtInferred]

            COLLATE DATABASE_DEFAULT

    , [parmSend]
        =
            case p.[ORDINAL_POSITION]
                when 1 then ' '
                else ', '
            end
            + p.[PARAMETER_NAME]
            + ' = '
            + p.[PARAMETER_NAME]

            COLLATE DATABASE_DEFAULT

/*

    , [is_nullable]
        = null

    , [has_default_value]
        = null

    , [default_value]
        = null

 */

FROM [LS].[dbContext].INFORMATION_SCHEMA.ROUTINES sp

JOIN [LS].[dbContext].INFORMATION_SCHEMA.SCHEMATA tblSS
    ON  sp.SPECIFIC_CATALOG = tblSS.[CATALOG_NAME]
    AND sp.SPECIFIC_SCHEMA = tblSS.[SCHEMA_NAME]

JOIN [LS].[dbContext].INFORMATION_SCHEMA.PARAMETERS  p
    ON  sp.SPECIFIC_CATALOG = p.SPECIFIC_CATALOG
    AND sp.SPECIFIC_SCHEMA  = p.SPECIFIC_SCHEMA
    AND sp.SPECIFIC_NAME    = p.SPECIFIC_NAME

INNER JOIN cteDataType cteDT
    ON  p.SPECIFIC_CATALOG  = cteDT.[catalog]
    AND p.SPECIFIC_SCHEMA   = cteDT.[schema]
    AND p.SPECIFIC_NAME     = cteDT.[object]
    AND p.ORDINAL_POSITION  = cteDT.parameter_id

order by
          p.SPECIFIC_CATALOG
        , p.SPECIFIC_SCHEMA
        , p.SPECIFIC_NAME
        , p.ORDINAL_POSITION

References

  1. System Catalog Views
    • sys.parameters

 

Transact SQL – Non-ANSI Joins

Background

Trying to see which SQL modules uses legacy Non-ANSI SQL Joins.

Discovery

There are a couple of pathways we can use to find sql code that relies on Non-Ansi SQL Joins.

The mediums we will use are :-

  1. Code
    • Execute Code
  2. Dynamic Management Views
    • System Catalog Views
      • sys.sql_Modules
  3. SQL Server Profiler

Code

Code Execution

SQL

SQL
DECLARE	@return_value int

EXEC	@return_value = [dbo].[sp_get_bottom2]

SELECT	'Return Value' = @return_value

GO
Output
Output – Text
Msg 102, Level 15, State 1, Procedure dbo.sp_get_bottom2, Line 8 [Batch Start Line 2]
Incorrect syntax near '*='.

Msg 102, Level 15, State 1, Procedure dbo.sp_get_bottom2, Line 17 [Batch Start Line 2]
Incorrect syntax near '*='.

Msg 102, Level 15, State 1, Procedure dbo.sp_get_bottom2, Line 26 [Batch Start Line 2]
Incorrect syntax near '*='.

Explanation
  1. Msg 102
    • Msg 102, Level 15, State 1, Procedure dbo.sp_get_bottom2, Line 8 [Batch Start Line 2]
      Incorrect syntax near ‘*=’.

Dynamic Management Views

System Catalog Views

sys.sql_modules

SQL

select *

from   sys.sql_modules tblSSM

where
        (
               (tblSSM.definition like '%*=%' )
            or (tblSSM.definition like '%=*%' )
        )
Output

NonAnsiJoins.20190221.0755AM

SQL Server Profiler

Trace Definition

Outline

  1. Deprecation
    • Deprecation Announcement
    • Deprecation Final Support

Image

sqlServerProfiler.traceProperties.eventsSelection.20190221.0800AM.PNG

Trace Events

Image

sqlServerProfiler.traceEvents.01.traceEvents.20190221.0804AM.PNG

Explanation

  1. Found
    • Deprecation Final Support