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

SSIS Package – Version Number – Setting

Background

We use a bit of SQL Server Integration Services on our site.

When troubleshooting for errors, it is not always easy to note whether we have the right version deployed.

Package

Setting version number on each SSIS package is one way of noting each package.

How To

Outline

  1. Launch SQL Server Data Tools
  2. Load Project
  3. Solution Explorer
    • Access the “Solution Explorer” panel
    • Select Each Package
      • Double-click each package
      • Access the Packages panel
        • Access the Version Group
          • Review and Set the following entries
            • VersionMajor
            • VersionMinor

Images

Package

Properties – Class View

SSISPackage.versionNumber.20190114.0122PM.PNG

 

SQL Server – SSMS – Query Plan – Compare

Background

One of my favorite go to tool with SQL Server Query troubleshooting is to simple use SQL Server Management Studio (SSMS) to compare the query plans generated via running the same SQL across different machines or SQL Server Versions.\

Use

To use, please follow these simple steps :-

  1. Invoke SSMS
    • Query 1
      • Enable “Include Actual Execution Plan”
      • Run Query
      • Save resultant plan
    • Query 2
      • Enable “Include Actual Execution Plan”
      • Run Query
      • Save resultant plan
    • Access Query Plan pane
      • Access either Query’s Plan tab
      • Right click in an empty area of the pane
      • The drop-down menu appears
      • From the drop down menu, please choose “Compare Showplan”
      • From the File Open window,  choose the saved query plan from the opposing query

 

Sample

Sample – 01

Showplan Comparison

Pictorial

queryPlan.compare.showPlanComparison.20190109.0535PM.PNG

Explanation

  1. Certificates
    • Top Query :- Index Seek
    • Bottom Query :- Index Scan

 

Showplan Analysis

queryPlan.compare.showPlanAnalysis.20190109.0534PM.PNG

Explanation

  1. Scenarios
    • Difference :- 15316%
    • Estimated top/left :- 3274
    • Estimated bottom/right :- 504816

 

Requirement

As for client, requires at least SQL Server Management Studio v2017.

Works across various SQL Server Database Engine Versions.

Tested here against Server versions :-

  1. v2005
  2. v2017

 

SQL Server – BCP Error – “String Truncation”

Background

Trying to Bulk copy data but running into error.

Error

Error Image

stringdatarighttruncation.20190108.0552pm

Error Text


SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
1000 rows sent to SQL Server. Total sent: 1591000
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation

1591585 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 347468 Average : (4580.52 rows per sec.)

Troubleshooting

We copied the data over using character mode and it is possible carriage return our default line terminator is naturally occurring.

Remediation

Change from character mode (-c) to native mode ( -n)

Next Error

Image

unexpectedEOFEncounteredInBCPDatafile.20190108.0604PM.PNG

Textual


1000 rows sent to SQL Server. Total sent: 3198000
1000 rows sent to SQL Server. Total sent: 3199000
1000 rows sent to SQL Server. Total sent: 3200000
1000 rows sent to SQL Server. Total sent: 3201000
1000 rows sent to SQL Server. Total sent: 3202000
1000 rows sent to SQL Server. Total sent: 3203000
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]
Unexpected EOF encountered in BCP data-file

 

References

  1. Github
    • Microsoft
      • msphpsql
        • PDOException: String data, right truncation when insert a long string #169
          Link

DOS batch file – Counting Lines

Background

Needing to count lines in a file.

Script

DOS / Command Line

Find Command

syntax

find /v /c "" [filename]

sample

find /v /c "" modules.txt

findstr

findstr – type / findstr/find

syntax
findstr /R "." [filename] | find /c /v ""
sample
findstr /R "." products.txt | find /c /v ""

findstr – type / findstr/find

syntax
type [filename] | findstr /r /n "^"| find /c ":"
sample
type products.txt | findstr /r /n "^"| find /c ":"

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