Transact SQL :- List Default Constraints

Background

List SQL Server Default Constraints.

Metadata

If one decides to use Sequences, one has to be able to document where it is being used.

Identity Columns

Identify Columns has self documenting tooling.

Inclusive :-

  1. sys.columns
    • is_identity
  2. sys.identity_columns
  3. OBJECTPROPERTY
    • TableHasIdentity
  4.  COLUMNPROPERTY
    • IsIdentity

Sequence

There is really nothing like a Sequence Column.

They are defined outside of a table definition.

It is not intrinsic so to speak.

It is a service that can be used as a communal toolset.

Outline

To detect use we can ask :-

  1. sys.columns
  2. sys.default_constraints
  3. sys.sql_expression_dependencies

Code


 if SUSER_ID('rdsa') is null
 begin

    use [master]

 end
 go

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

    exec('create procedure [dbo].[sp_helpConstraintDefault] as ')

 end
 go

 alter procedure [dbo].[sp_helpConstraintDefault]
 as
 begin

   select 

              [schema]
                = tblSS.[name]

            , [object]
                = tblSO.[name]

            , [column]
                = tblSC.[name]

            , [columnType]
                = tblST.[name]

            , [is_identity]
                = tblSC.is_identity

            , [is_rowguidcol]
                = tblSC.is_rowguidcol

            , [columnDefault]
                = tblSDC.[name]

            , [definition]
                = tblSDC.[definition]

            , [referencedObject]
                = quotename
                    (
                        tblSORefS.[name]
                    )
                    + '.'
                    + quoteName
                        (
                            tblSORef.[name]
                        )

            , [referencedObjectType]
                = tblSORef.[type_desc]

    from   sys.columns tblSC

    inner join sys.types tblST

        on  tblSC.system_type_id = tblST.system_type_id

        and tblSC.user_type_id = tblST.user_type_id

    inner join sys.objects tblSO

        on tblSC.[object_id] = tblSO.[object_id]

    inner join sys.schemas tblSS

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

    inner join sys.default_constraints tblSDC

        on  tblSC.object_id = tblSDC.parent_object_id

        and tblSC.column_id = tblSDC.parent_column_id

    left outer join sys.sql_expression_dependencies AS tblSSED 

        on tblSC.[default_object_id] = tblSSED.referencing_id

    left outer join sys.objects tblSORef

        on tblSSED.referenced_id = tblSORef.[object_id]

    left outer join sys.schemas tblSORefS
        on tblSORef.[schema_id] = tblSORefS.[schema_id]

    order by

              tblSS.[name]

            , tblSO.[name]

            , tblSC.[name]

 end
 go

 if SUSER_ID('rdsa') is null
 begin

    exec sp_MS_marksystemobject '[dbo].[sp_helpConstraintDefault]'

 end
 go

Output

Output – Sequence – #1

Output – Database – Idf – #1

 

Source Control

GitHub

Repository

DanielAdeniji/dbo.sp_helpConstraintDefault
Link

Transact SQL :- Sequence Object

Background

As I read more about Hibernate, reminded once again the usage of Identity Columns to implement Sequences is quite unique to SQL Server and likely Sybase.

And, that Oracle, for one, uses Sequence to generate increasing numbers.

Hibernate

hibernate.IdGeneratorStrategy

Sample Code

Outline

  1. Schema
    • Does Schema exists?
      • schema_id()
    • If Schema does not exist?
      • Create Schema
  2. Sequence
    • Does Sequence exists?
      • Consult sys.sequences
    • If Sequence does not exist
      • Create Using “CREATE SEQUENCE”
  3. Utilize Sequence Generator
    • Add Data
      • Get next available Sequence
        • Syntax
          • NEXT VALUE FOR [sequence object]
        •  Sample
          • NEXT VALUE FOR [lab].[seq]
  4. Review Sequence Objects
    • sys.sequences
      • name
      • is_cached
      • increment
      • current_value
      • is_exhausted
      • maximum_value

Code


use [tempdb]
go

set nocount on
go

set XACT_ABORT on
go

declare @commit bit

declare @object  sysname

declare @schema_id int
declare @schema    sysname
declare @sequence  sysname

set @commit = 0

set @schema = 'lab'
set @sequence = 'seq';

begin tran

    /*
        Get schema_id for schema @schema
    */
    set @schema_id = schema_id(@schema);

    /*
        If schema_id is null, then does not exist
        create it
    */
    if @schema_id is null
    begin

        exec('create schema [lab] authorization [dbo]')

    end

    set @schema_id = schema_id(@schema);

    /*
        If schema does not exist, please create it
    */
    if not exists
    (
        select *
        from   sys.sequences
        where  [name] = @sequence
        and    [schema_id] = @schema_id
    )
    begin

        CREATE SEQUENCE [lab].[seq]
        AS INTEGER
        START WITH 1
        INCREMENT BY 1
        MINVALUE 1
        --MAXVALUE 1000000
        CYCLE
        ; 

    end

    /*
        Please create table lab.vehicle
    */
    if object_id('[lab].[vehicle]') is null
    begin

        create table [lab].[vehicle]
        (
            id bigint not null
                DEFAULT NEXT VALUE FOR [lab].[seq]
        )

    end

    /*
        Please create table lab.bridge
    */
    if object_id('[lab].[bridge]') is null
    begin

        create table [lab].[bridge]
        (
            id bigint not null
                DEFAULT NEXT VALUE FOR [lab].[seq]

            , [name] nvarchar(80) not null

        )

    end

    /*
        Insert data
    */
    insert into [lab].[vehicle] default values;

    insert into [lab].[bridge]([name]) values('Seven Mile Bridge');

    insert into [lab].[vehicle] default values;

    insert into [lab].[bridge]([name]) values('Royal Gorge Bridge and Park');

    insert into [lab].[bridge]([name]) values('New River Gorge Bridge');

    /*
        Review data
    */
    select *

    from   [lab].[vehicle]

    select *

    from   [lab].[bridge]

    /*
        Review Sequence
    */
    select
              [schema] = tblSS.[name]
            , tblSeq.[name]
            , tblSeq.is_cached
            , tblSeq.increment
            , tblSeq.current_value
            , tblSeq.is_exhausted
            , tblSeq.maximum_value

    from   sys.sequences tblSeq

    inner join sys.schemas tblSS

            on tblSeq.[schema_id] = tblSS.schema_id

    where  tblSeq.[name] = @sequence
    ;

while (@@TRANCOUNT>0)
begin

    if (@commit =1)
    begin

        commit tran;

    end
    else
    begin

        rollback tran;
    end
end

Output

sequence.lab.20190512.1134PM

SQL Server Agent on AWS/RDS

Background

There is quite a bit of SQL Server management surface area that is not available when running on AWS/RDS.

 

Surface Area

For instance with SQL Server Agent :-

  1. SQL Server Agent
    • Category
      • Add new category
        • API
          • sp_add_category
        • Error
          • Msg 229, Level 14, State 5, Procedure msdb.dbo.sp_add_category
          • The EXECUTE permission was denied on the object ‘sp_add_category’, database ‘msdb’, schema ‘dbo’.
    • Job Step
      • Fetch Job Steps
        • API
          • SELECT * FROM msdb.dbo.sysjobsteps
        • Error
          • Msg 229, Level 14, State 5, Line 71
          • The SELECT permission was denied on the object ‘sysjobsteps’, database ‘msdb’, schema ‘dbo’.

 

Scripting

In terms of scripting, SQL Server Agent is not accessible in an AWS/RDS Environment.

XQuery/Transact SQL – Case Sensitivity

Background

When issuing XPath queries, XQuery in SQL, one has to keep in mind that though the database itself could be defined as case-insensitive, XML is case-sensitive.

 

Create & Populate Data

Create Table

SQL


declare @tbl TABLE
(
	[id] smallint not null
		identity(1,1)

	, [xml] xml
)

Populate Table with data

SQL


insert into @tbl
(
	[xml]
)
select ''

union all

select ''

union all

select ''

union all

select ''

union all

select ''

union all

select ''

Data

 

Queries

  1. Normal Queries
    • Fetch all data
    • Fetch data whose part name is Teeth
  2. Function – fn:lower-case
    • Fetch all data
    • Fetch data whose part name is Teeth ( Case In-Sensitive )

Normal Query

Fetch all Records

Code


select
          [src] = 'all'

        , *

        , [count]
            = [xml].value
                (
                    '(/body/part/@count)[1]'
                    , 'smallint'
                ) 

        , [countOfteeth]
            = [xml].value
                (
                    '(/body/part[@name="teeth"]/@count)[1]'
                    , 'nvarchar(max)'
                ) 

from   @tbl

Output

Body Part = teeth


select
          [src] = 'name=teeth'

        , *

        , [count]
            = [xml].value
                (
                    '(/body/part/@count)[1]'
                    , 'smallint'
                ) 

        , [countofteeth]
            = [xml].value
            (
                '(/body/part[fn:lower-case(@name)="teeth"]/@count)[1]'
                , 'smallint'
            ) 

from   @tbl

where  [xml].exist
        (
            '(/body/part[@name="teeth"])'
        )
        = 1		 

 

Output

 

Function – Lower Case

Read All Records

SQL


select
          [src] = 'all'

        , *

        , [count]
            = [xml].value
                (
                    '(/body/part/@count)[1]'
                    , 'smallint'
                ) 

        , [countOfteeth]
            = [xml].value
                (
                    '(/body/part[@name="teeth"]/@count)[1]'
                    , 'smallint'
                ) 

        , [countOfteethCI]
            = [xml].value
              (
                '(/body/part[fn:lower-case(@name)="teeth"]/@count)[1]'
                , 'smallint'
              )

from   @tbl

Output

Body Part = teeth

SQL


select 

          [src] = 'name=teeth (lowercase)'

        , *

        , [countofteeth]
            = [xml].value
            (
                '(/body/part[fn:lower-case(@name)="teeth"]/@count)[1]'
                , 'smallint'
            ) 

from   @tbl

where  [xml].exist
        (
            '(/body/part[fn:lower-case(@name)="teeth"])'
        )
        = 1

Output

SQL Server Version

XML datatype and XQuery was introduced in SQL Server v2005.

But, v2005, programmability support was limited.

Some String functions such as lower-case and upper-case were not introduced till v2008.

 

Summary

You know your data.

If you suspect it might be mixed-case, please take “precausive” measures.

 

References

  1. Stackoveflow
    • Kevin Aenmey
      • how to perform a case-insensitive attribute selector in xquery
        Link
  2. Microsoft
    • XQuery
      • XQuery Functions against the xml Data Type
        Link
      • Functions on String Values – lower-case
        Link
  3. IBM
    • IBM Knowledge Center
      • Home > IBM i 7.1 > Database Programming > SQL XML programming > Overview of XPath > Descriptions of XPath functions
        • fn:lower-case function
          Link
  4. W3.Org
    • Xpath Functions
      • XQuery, XPath, and XSLT Functions and Operators Namespace Document
        Link

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