SQL Server – Identify Indexed Views

Background

Wanted to identify Indexed Views and took a look at sys.views to see if it exposes a guiding attribute.  But, No.

Code

Looked on the Net and found a good track.

Outline

  1. Look for views in sys.views
  2. Perform an inner join against sys.indexes

SQL

select 

          [object]
            = tblSS.[name]
              + '.'
              + tblSV.[name]

        , [index]
            = tblSI.[name]

        , [indexType]
            = tblSI.[type_desc]

from   sys.views tblSV

inner join sys.schemas tblSS

    on tblSV.schema_id = tblSS.schema_id

inner join sys.indexes tblSI

    on tblSV.object_id = tblSI.object_id

order by
        [object]
      , [index]

Transact SQL – User defined error messages

Background

Quickly, how to identify user defined error messages.

Guide Post

Here is the Link :-

Docs / SQL / Relational databases / System stored procedures
sp_addmessage (Transact-SQL)
Link

@msgnum = ] msg_id
Is the ID of the message. msg_id is int with a default of NULL. msg_id for user-defined error messages can be an integer between 50,001 and 2,147,483,647.

Explanation

To defined a new error, we have to specify a message id greater than 50001.  Or pass along null, and an id greater than 50001 will be assigned to us.

Code

Outline

  1. sys.messages
    • messsage_id >=50001

SQL


select
          tblSM.[message_id]

        , tblSM.[language_id]

        , tblSM.[severity]

        , tblSM.[is_event_logged]

        , tblSM.[text]

        , [customized]
            = case
                when (tblSM.[message_id] >= 50001) then 'Y'
                else 'N'
               end	

from   sys.messages tblSM

where  tblSM.[message_id] >= 50001

SSMS – Linked Server – Column Metadata

Background

Had a good meeting this morning while we try to figure out how to better support our Developers.

One of the issues they brought up was an inability to view the datatype of linked Server tables.

Pictorial

Here is the deepest granularity when we connect to our Linked Server.

Image

ssms.columns.20190114.0425PM.PNG

Explanation

  1. We see the following
    • Server
    • Databases
    • Objects
      • Tables
      • Views

View Columns

Script

Outline

Here are avenues we can explore to view column metadata :-

  1. sp_columns_ex
  2. Openquery/sys
    • sys.all_columns
  3. Openquery/informational_schema
    • informational_schema.columns

Choices

exp_columns_ex

Syntax

exec sp_columns_ex
		  @table_server
		, @table_catalog
		, @table_schema
		, @table_name   

Sample

declare @linkedServer   sysname
declare @linkedDatabase sysname
declare @linkedSchema   sysname
declare @linkedTable    sysname

set @linkedServer= 'AWS-JobBuilder'
set @linkedDatabase = 'acs_ap'
set @linkedSchema = 'dbo'
set @linkedTable = 'ap_user'

exec sp_columns_ex
		  @table_server  = @linkedServer
		, @table_catalog = @linkedDatabase
		, @table_schema  = @linkedSchema
		, @table_name    = @linkedTable

Output

sp_tables_ex.2019014.0443pm

openquery/sys.*

Syntax

select top 10 *

from   openquery
        (
              [AWS-JobBuilder]

            , '
                    select
                              [server] = serverproperty(''servername'')
                            , [database] = db_name()
                            , [schema] = tblSS.name
                            , [object] = tblSAO.name
                            , [column] = tblSAC.name
                            , [type]   = tblST.[name]
                            , tblST.max_length
                            , tblST.is_nullable

                    from   sys.schemas tblSS

                    inner join sys.all_objects tblSAO

                            on tblSS.schema_id = tblSAO.schema_id

                    inner join sys.all_columns tblSAC

                        on tblSAO.object_id = tblSAC.object_id

                    inner join sys.types tblST

                        on  tblSAC.system_type_id = tblST.system_type_id
                        and tblSAC.user_type_id = tblST.user_type_id

              '
        )

Output

openquery.sys.all.2019014.0452pm

openquery/information_schema.columns

Syntax
select top 10 *

from   openquery
        (
              [AWS-JobBuilder]

            , '
                select top 100 

                          [server] = serverproperty(''servername'')

                        , [database] = tblSIC.[TABLE_CATALOG]

                        , [schema] = tblSIC.[TABLE_SCHEMA]

                        , [object] = tblSIC.[TABLE_NAME]

                        , [column] = tblSIC.[COLUMN_NAME]

                        , [position] = tblSIC.[ORDINAL_POSITION]

                        , [dataType] = tblSIC.[DATA_TYPE]

                        , [charMaxLength] = tblSIC.[CHARACTER_MAXIMUM_LENGTH]

                        , [charOctetLength] = tblSIC.[CHARACTER_OCTET_LENGTH]

                from   information_schema.columns tblSIC

              '
        )			   	

Output

openquery.openquery.informational_columns.2019014.0515PM.PNG

Summary

Unfortunately, SQL Server Management Studio ( SSMS ) v17.x does not let us view columns on Linked Servers.

To gather column level metadata, one has to write code.

Transact SQL – Scaler Functions – Schema Bound

Background

Want to have a quick talk about Scaler Functions; specifically Schema Bound.

Transact SQL

DDL

Outline

We are going to create two functions, dbo.left10.No and dbo.left10.Yes.

dbo.left.No will be non-schema bound and dbo.left10.Yes will be schema bound.

Function

Function – [dbo].[left10.No]

Code
use [tempdb]
go

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

    exec('create function [dbo].[left10.No]
            (
                @input varchar(100)
            )
            returns varchar(10)
            as
            begin

                return reverse
                (
                    left(@input, 10)
                )

            end
        ')

end
go

ALTER FUNCTION [dbo].[left10.No]
(
    @input varchar(100)
)
returns varchar(30)
as
begin

    return reverse
            (
                left(@input, 10)
            )

end
go

Function – [dbo].[left10.Yes]

Code
use [tempdb]
go

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

    exec('create function [dbo].[left10.Yes]
            (
                @input varchar(100)
            )
            returns varchar(10)
            as
            begin

                return reverse
                (
                    left(@input, 10)
                )

            end
        ')

end
go

ALTER FUNCTION [dbo].[left10.Yes]
(
    @input varchar(100)
)
returns varchar(10)
with schemabinding
as
begin

    return reverse
            (
                left(@input, 10)
            )

end
go

DML

Outline

In our sample exercise we compare the effect of updating a table with a non-schema bound Scaler function against updating a similar table with a schema bound Scaler function.

Steps

  1. Non-Schema Bound Function
    • Create Table 1
    • Add data
    • Update data with non-schema bound function
  2. Schema Bound Function
    • Create Table 2
    • Add data
    • Update data with schema bound function

Code


use [tempdb]
go

set nocount on
go

set statistics io on
go

set XACT_ABORT on
go

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

    drop table [dbo].tbl1

end
go

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

    drop table [dbo].[tbl2]

end
go

create table [dbo].[tbl1]
(
      [id] int not null identity(1,1)
    , [data] varchar(60) not null
    , [dataProcessed] varchar(100) null
)

create table [dbo].[tbl2]
(
      [id] int not null identity(1,1)
    , [data] varchar(60) not null
    , [dataProcessed] varchar(100) null
)

declare @lMaxNumberofRecords int

set @lMaxNumberofRecords = 100000

;with
   L0 as (select 1 as C union all select 1)       --2 rows
  ,L1 as (select 1 as C from L0 as A, L0 as B)    --4 rows
  ,L2 as (select 1 as C from L1 as A, L1 as B)    --16 rows
  ,L3 as (select 1 as C from L2 as A, L2 as B)    --256 rows
  ,L4 as (select 1 as C from L3 as A, L3 as B)    --65536 rows
  ,L5 as (select 1 as C from L4 as A, L4 as B)    --4,294,967,296 rows
  ,Nums as
  	(
		select 

			row_number() 

			over
			(

				order by
					(
						select 0
					)
			) as N 

		from L5
	)

insert into tbl1
(
      [data]
)
select
          CAST (newid() as varchar(36))
from Nums
where N<=@lMaxNumberofRecords

;with
   L0 as (select 1 as C union all select 1)       --2 rows
  ,L1 as (select 1 as C from L0 as A, L0 as B)    --4 rows
  ,L2 as (select 1 as C from L1 as A, L1 as B)    --16 rows
  ,L3 as (select 1 as C from L2 as A, L2 as B)    --256 rows
  ,L4 as (select 1 as C from L3 as A, L3 as B)    --65536 rows
  ,L5 as (select 1 as C from L4 as A, L4 as B)    --4,294,967,296 rows
  ,Nums as
	(
		select 

			row_number() 

			over
			(

				order by
					(
						select 0
					)
			) as N 

		from L5
	)

insert into tbl2
(
      [data]
)
select
        CAST (newid() as varchar(36))
from Nums
where N<=@lMaxNumberofRecords

go

print replicate('=', 160)
go

update statistics [dbo].[tbl1] with fullscan
go

update statistics [dbo].[tbl2] with fullscan
go

update tbl
set    dataProcessed = [dbo].[left10.No](tbl.[data])
from   tbl1 tbl
go

print replicate('=', 160)
go

update tbl
set    dataProcessed = [dbo].[left10.Yes](tbl.[data])
from   tbl2 tbl
go

print replicate('=', 160)
go

drop table [dbo].[tbl1]
go

drop table [dbo].[tbl2]
go

Query Plan

Image

queryplan.20190107.0820am

Explanation
  1. We can see that the Non-Schema bound function has a Table Spool

Statistics

Statistics I/O
Statistics I/O – Image

statistics.io.20190107.0818am

Statistics I/O – Textual
================================================================================
Update table 1 ...
================================================================================================================================================================
Table 'tbl1'. Scan count 1, logical reads 145382, physical reads 125, read-ahead reads 654, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 202750, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Updated table 1
================================================================================================================================================================
Update table 2 ...
================================================================================================================================================================
Table 'tbl2'. Scan count 1, logical reads 45522, physical reads 0, read-ahead reads 658, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
================================================================================================================================================================
Updated table 2
================================================================================================================================================================

Statistics I/O – Explanation
  1. We can see that the Non-Schema bound function
    • References a Worktable
    • And, has more IO against the targeted table

Contributing to Microsoft Docs

Background

I was researching a subject earlier today and ran into an area that I wasn’t so sure if it was erroneous or an oversight.

ObjectPropertyex

Microsoft

The subject area is objectpropertyex and here is Microsoft’s documentation.

Here is the link

microsoftDocs.sqlDocs.objectPropertyex

 

Property

Noticed that “IsSystemVerified” is not on the list.

 

Edit

As it is on GitHub and publicly available, switched to edit mode, pulled it, and added the missing entry.

Outline

Here are the steps:-

  1. Revise Page
  2. Save Changes
  3. Pull Request
    • Create Pull Request
    • Open Pull Request
    • Merge Pull Request

Create Pull Request

microsoftDocs.sqlDocs.objectProperty.createPullRequest.PNG

Open Pull Request

microsoftDocs.sqlDocs.objectProperty.openPullRequest.PNG

 

Merge Pull Request

 

microsoftDocs.sqlDocs.objectProperty.mergeRequestSent.PNG

 

Pull Requests

If you will like to review Pull Requests, here is the link :-

MicrosoftDocs/sql-docs
Link

Image

microsoftDocs.sqlDocs.objectPropertyex.pullRequests.PNG

 

 

 

Transact SQL – Get XML Attribute Value

Background

Trying to get an XML Attribute’s value, but ran into error.

XML Fragment

Image

xmlFragment.song.20181225.1026PM

Code


<song id="1" album="control" name="Pleasure Principle" releasedYear="1986" />

Code

Prepare Data

SQL


set nocount on
go

set XACT_ABORT on
go

use [tempdb]
go

declare @tblSong TABLE
(

	  [id] smallint not null identity(1, 1)
	, [songFragment] xml not null
	, [song]         nvarchar(80) null
)

insert into @tblSong
(
  [songFragment]
)
select
 'song id="1" album="control" name="Pleasure Principle" releasedYear="1986" /'

Error

Error – Top-level attribute nodes are not supported

SQL


select 

       tblS.songFragment

    , [song]
        = tblS.songFragment.value
            (
                  '@name'
                , 'sysname'
            )

from   @tblSong tblS

Error

Error – Image

topLevelAttributeNodesAreNotSupported.20181225.1040PM

Error – Textual


Msg 2390, Level 16, State 1, Line 41
XQuery [value()]: Top-level attribute nodes are not supported

Remediation

Datatype – XML – Value Function

Objective

  1. Use /song to track down to the node
  2. We will use [1] to specify the top node
  3. And, use @[attribute-name] to specify specific attribute

SQL

select 

       tblS.songFragment

     , [id]
        = tblS.songFragment.value
            (
                    '(/song[1]/@id)'
                , 'int'
            )

    , [album]
         = tblS.songFragment.value
            (
                    '(/song[1]/@album)'
                , 'sysname'
            )

    , [song]
        = tblS.songFragment.value
            (
                    '(/song[1]/@name)'
                , 'sysname'
            )

    , [releasedYear]
        = tblS.songFragment.value
            (
                    '(/song[1]/@releasedYear)'
                , 'sysname'
            )

from   @tblSong tblS

Datatype – XML – Nodes Function

Objective

  1. Use cross apply nodes (/song) to track down to the node
  2. Value Datatype function
    • Use @[attribute-name] to specify specific attribute
    • And, be sure to cast to our targeted datatype

SQL

select tblS.songFragment

        , [node] = q1.c1.query('.')
        , [album] = q1.c1.value('@album', 'sysname')
        , [song] = q1.c1.value('@name', 'sysname')
        , [releasedYear] = q1.c1.value('@releasedYear', 'int')

from   @tblSong tblS

CROSS APPLY tblS.[songFragment].nodes('/song') AS q1(c1)

SQL Server – Error – “Full-Text Search is not installed, or a full-text component cannot be loaded”

Background

While trying to deploy an Application that I am working on ran into an expected error.

Error

Error Image

FullTextSearchIsNotInstalled.20181209.0457PM

Error Message


Msg 7609, Level 17, State 5, Line 31513
Full-Text Search is not installed, or a full-text component cannot be loaded.

Troubleshooting

Transact SQL

FULLTEXTSERVICEPROPERTY

Code


SELECT
	[IsFullTextInstalled]
	  = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

Output

metadata.FULLTEXTSERVICEPROPERTY.No.20181209.0511PM

Explanation

Full-text is not installed.

Remediation

Install and configure Full Text.

Outline

  1. Install
    • Install Fulltext
  2. Restart MS SQL Server Engine
  3. Validate FullText Installation

 

Install

Install Fulltext

Code

Syntax

sudo yum install -y {package}

Sample

sudo yum install -y mssql-server-fts

Output
Output – 01

yum.install.mssql-server-fts.20181209.0528PM.PNG

Output – 02

yum.install.mssql-server-fts.20181209.0537PM

Restart SQL Server Engine Services

systemctl

Syntax


sudo systemctl restart {service}

Sample

sudo systemctl restart mssql-server

FULLTEXTSERVICEPROPERTY

Code


SELECT
	[IsFullTextInstalled]
	  = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

Output

metadata.FULLTEXTSERVICEPROPERTY.Yes.20181209.0545PM

Explanation

Full-text is installed.

 

References

  1. Docs \ SQL \ SQL Server on Linux
    • Install SQL Server Full-Text Search on Linux
      Link