DBeaver – Generate SQL Insert Statements from Data

Script SQL Insert Statement from Data

Using SQL Server Management Studio ( SSMS), I entered quite a bit of data into a new table.

Not wanting to ever repeat that arduous process, I sought out tools that will allow me to script out the data.

 

Script Data

Yes, BCP will work, but I will be scripting out the data and will not be getting the SQL Statements.

 

Script SQL

DBeaver

Download and Install

Downloaded and Installed DBeaver.

BTW, here is the link.

Launched App

Installed Microsoft SQL Server JDBC Driver

Using built-in tooling added Microsoft’s SQL Server JDBC Driver.

Enter SQL

In DBeaver, launched new “SQL Editor”.

Entered the Query:


SELECT RoleID, RoleName

FROM   dbo.listofServerRoles tblSR

 

Result

Capture SQL

To capture the SQL, select all the records you will like scripted.

Then choose the menu items, “Generate SQL” and “Insert”.

Generated SQL

Shortcoming

There are some shortcomings in DBeaver’s implementation.

I will cover the shortcomings and workarounds in a later post.

Transact SQL – String Splitting Using XML

Background

Unfortunately splitting a String into rows was lacking in Transact SQL for a while.

MSFT changed that in Version 2016.

Split_String is capable and handy.

Here is the link.

 

Prior to v2016

Prior to Version 2016, there are many tools out in the wild for accomplishing same.

There are CLR Functions.  There are functions that use dbo.Numbers

 

XML

And, there are functions that rely on XML’s inherent ability to split an input into nodes.

 

XML

Standing On

Found a nice working model on sqlhints.com.

BTW, sqlHints.com is Basavaraj Biradar.

And, here is the particular post that we will be using.

It is titled “How to Split comma or any other character delimited string into a Table in Sql Server.”

 

Objective

  1. Look for separator
    • Replace separator with endNode and beginNode
      • Close out endNode
      • Start new node with beginNode
  2. Begin & End fragment
    • Begin Fragment with beginNode
    • End Fragment with endNode
  3. Start and end node fragment
    • XML needs root node
      • And, so we start string with rootNode
      • And, end with rootNode
  4. Use XQuery to parse node
    • Parse node using XQuery
    • Nodes ( “//node”)

Code

[stringSplit].[itvf_stringSplitUsingXML]

 


use [master]
go


if schema_id('stringSplit') is null
begin

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

end
go

if object_id('[stringSplit].[itvf_stringSplitUsingXML]') is null
begin

	exec
	(
		'create function [stringSplit].[itvf_stringSplitUsingXML]
			()
			RETURNS @RESULT TABLE
			(
				Value VARCHAR(MAX)
			)
		as
		begin

			return

		end

		'
	) 

end
go


ALTER FUNCTION [stringSplit].[itvf_stringSplitUsingXML]
(
      @string  VARCHAR(MAX)
	, @separator CHAR(1) = ','
)
RETURNS @tblResult TABLE
(

	  [rowNumber] smallint not null identity(1,1)
	, [value]	  VARCHAR(600)
	, [xml]		  xml
	, [element]	  xml

)
AS
BEGIN

	/*

		a) sqlhints.com
		   http://sqlhints.com/tag/split-comma-separated-values-in-sql/
	
	*/    

	 DECLARE @xml XML
	 DECLARE @xmlAsString nvarchar(max)

	 declare @rootBegin varchar(10)
	 declare @rootEnd varchar(10)

	 declare @node      varchar(10)
	 declare @nodeBegin varchar(10)
	 declare @nodeEnd varchar(10)

	 declare @xmlNodeAsString as varchar(600)

	 set @rootBegin = '<root>'
	 set @rootEnd = '</root>'

	 set @node = 'node'
	 set @nodeBegin = '<node>'
	 set @nodeEnd = '</node>'

	 /*

		Look for separator, when found
			end earlier node by replacing separator
				 with [nodeEnd] 
				 and starting new node with [nodeBegin]

	 */
	 set @xmlNodeAsString = REPLACE
								(
									  @string
									, @separator
									, @nodeEnd + @nodeBegin
								)

	/*
		Bracket fragment with begin and end node
	*/
 	 SET @xmlNodeAsString =  @nodeBegin 		
							+ @xmlNodeAsString
							+ @nodeEnd

	/*
		Root node
		 separated out contents
		 End Root Node
	*/
 	 SET @xmlAsString = @rootBegin
							+ @xmlNodeAsString
							+ @rootEnd

	/*
		Convert to XML
	*/
	set @xml = @xmlAsString
 

	INSERT INTO @tblRESULT
	(
		  [value]
		, [xml]
		, [element]

	)
	SELECT 

		  [value]
		  = 
			(
				t.i.value
				(
						'(.)[1]'
					, 'varchar(max)'
				)
			)

		, [xml]
			= @xml

		 , [element]
		  = 
			(
				t.i.query
				('.')
			)
	  
	from @xml.nodes('//node') AS t(i)

	RETURN

END
go


 

Invoke

 


	declare @data varchar(600)

	set @data = 'sammie,bobbie,jackie'

	select *

	from   [master].[stringSplit].[itvf_stringSplitUsingXML]
			(
				  @data
				, default
			)

 

Output

SQL Server – Statistics IO – Scan Count – When Non-unique Index exist

Background

Quick follow-up to our last post.

That post is titled “SQL Server – Statistics IO – Scan Count – When unique Index exists“.

And, it is here.

Lab

Outline

In this exercise, we will create a table and create a single non-unique index on our lone column.

The name of the table is dbo.Numbers_NoUniqueINDX, the name of the lone column is Number.

And, we will create a single non-unique Index.

 

Create & Populate Table

SQL


use master
go

set noexec off
go
SET NOCOUNT ON;
go

/*
	drop table [dbo].[Numbers_NoUniqueINDX]
*/
if object_id('[dbo].[Numbers_NoUniqueINDX]') is null
begin

	create table [dbo].[Numbers_NoUniqueINDX]
	(
		  [Number] int not null
			
	)

end 
go

declare @edition sysname
declare @bCompressionSupported bit

set @edition = cast(serverproperty('edition') as sysname)

if (
		@edition in 
		( 
			'Standard Edition (64-bit)'
		)
	)
begin

	set @bCompressionSupported = 0

end
else if (@edition like 'Enterprise%')
begin

	set @bCompressionSupported = 1

end

print 'edition: ' + @edition
print 'compression : '  + cast(@bCompressionSupported as varchar(10))


if not exists
(

	select *
	from   sys.objects tblSO
	inner join sys.indexes tblSI
		on tblSO.object_id = tblSI.object_id
	where  tblSO.object_id = object_id('[dbo].[Numbers_NoUniqueINDX]')
	and    tblSI.[name] = 'INDX_NonUnique_Number' 

)
begin

	print 'Create Non-Clustered Index - INDX_NonUnique_Number ..'

	if (@bCompressionSupported = 1)
	begin

		create nonclustered index [INDX_NonUnique_Number]
		on [dbo].[Numbers_NoUniqueINDX]
		(
			[Number]
		)
		WITH 
		(
			  DATA_COMPRESSION = PAGE
			, FILLFACTOR=100
			, ignore_dup_key=OFF
		)				

	end
	else
	begin


		create nonclustered index [INDX_NonUnique_Number]
		on [dbo].[Numbers_NoUniqueINDX]
		(
			[Number]
		)
		WITH 
		(
			  DATA_COMPRESSION = NONE
			, FILLFACTOR=100
			, ignore_dup_key=OFF
		)				


	end

	print 'Created Non-Clustered Index - INDX_NonUnique_Number'

end

go

DECLARE @UpperLimit INT;

set @UpperLimit = 1000000;

if not exists
	(
		select 1
		from   [dbo].[Numbers_NoUniqueINDX]
	)

begin

	print 'Adding data into [dbo].[Numbers_No_UniqueINDX] ....'

	;WITH cteNumber 
	AS
	(
			SELECT
				x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
			FROM  sys.all_objects AS s1
			CROSS JOIN sys.all_objects AS s2
			CROSS JOIN sys.all_objects AS s3
	)
	INSERT INTO [dbo].[Numbers_NoUniqueINDX]
	(
		[Number] 
	)
	SELECT [Number] = cte.[x]
    FROM   cteNumber cte
    WHERE  cte.[x] BETWEEN 1 AND @UpperLimit;
 
	print 'Added data into [dbo].[Numbers_No_UniqueINDX]'

end

GO


 

Query


set noexec off
set nocount on
set statistics io on;
go

use [master]
go

print replicate('=', 120)

print 'Searching for 1 Number'

SELECT
        [Extent1].[Number] AS [Number]  -- 1 Value
		FROM [dbo].[Numbers_NoUniqueINDX] AS [Extent1]
 		WHERE [Extent1].[Number] IN
		 (  1 ) 

print replicate('=', 120)

print 'Searching for 2 Numbers_NoUniqueINDX'

SELECT
        [Extent1].[Number] AS [Number]  -- 1-2 Value
		FROM [dbo].[Numbers_NoUniqueINDX] AS [Extent1]
 		WHERE [Extent1].[Number] IN
		 (
			  1, 2
		 ) 

print replicate('=', 120)

print 'Searching for 10 Numbers_NoUniqueINDX'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 thru 10 Values
		FROM [dbo].[Numbers_NoUniqueINDX] AS [Extent1]
 		WHERE [Extent1].[Number] IN
		 (
			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
		 ) 


print replicate('=', 120)

print 'Searching for 60 Numbers_NoUniqueINDX'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 thru 10 Values
		FROM [dbo].[Numbers_NoUniqueINDX] AS [Extent1]
 		WHERE [Extent1].[Number] IN
		 (
			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
			, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
			, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
			, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
			, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
			, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
		 ) 


print replicate('=', 120)

print 'Searching for 64 Numbers_NoUniqueINDX'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 Thur 64 Values
FROM [dbo].[Numbers_NoUniqueINDX] AS [Extent1]
WHERE [Extent1].[Number] IN
	(
		1, 2, 3, 4, 5, 6, 7, 8, 9, 10
	, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
	, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
	, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
	, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
	, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
	, 61, 62, 63, 64
	) 

		 
print replicate('=', 120)

------------------------------------------------------------------------------------------------

print 'Searching for 65 Numbers_NoUniqueINDX'

SELECT 
        [Extent1].[Number] AS [Number]  -- 65 Values
FROM [dbo].[Numbers_NoUniqueINDX] AS [Extent1]
WHERE [Extent1].[Number] IN
	(

		1, 2, 3, 4, 5, 6, 7, 8, 9, 10
	, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
	, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
	, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
	, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
	, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
	, 61, 62, 63, 64, 65
	) 

print replicate('=', 120)

print 'Searching for 66 Numbers_NoUniqueINDX'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 thru 66 Values
FROM [dbo].[Numbers_NoUniqueINDX] AS [Extent1]
WHERE [Extent1].[Number] IN
	(

		1, 2, 3, 4, 5, 6, 7, 8, 9, 10
	, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
	, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
	, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
	, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
	, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
	, 61, 62, 63, 64, 65, 66
	) 

print replicate('=', 120)

------------------------------------------------------------------------------------------------

print 'Searching for 70 Numbers_NoUniqueINDX'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 thru 90 Values
FROM [dbo].[Numbers_NoUniqueINDX] AS [Extent1]
WHERE [Extent1].[Number] IN
	(

		1, 2, 3, 4, 5, 6, 7, 8, 9, 10
	, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
	, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
	, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
	, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
	, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
	, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70
	) 

print replicate('=', 120)

---------------------------------------------------------------------------------------------------
go

 

Statistics IO

 

 

Tabulate

Scenario Statistics I/O Scan Count
 Searching for 1 Number Table ‘Numbers_NoUniqueINDX’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 0
 Searching for 2 Numbers Table ‘Numbers_NoUniqueINDX’. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  2
 Searching for 10 Numbers Table ‘Numbers_NoUniqueINDX’. Scan count 10, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  10
 Searching for 60 Numbers Table ‘Numbers_NoUniqueINDX’. Scan count 60, logical reads 180, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  60
 Searching for 64 Numbers Table ‘Numbers_NoUniqueINDX’. Scan count 64, logical reads 192, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  64
 Searching for 65 Numbers Table ‘Numbers_NoUniqueINDX’. Scan count 65, logical reads 234, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  65
 Searching for 66 Numbers Table ‘Numbers_NoUniqueINDX’. Scan count 66, logical reads 237, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 66
 Searching for 70 Numbers Table ‘Numbers_NoUniqueINDX’. Scan count 70, logical reads 249, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 70

 

 

Query Plan – Operator – Index Seek

Explanation

Scan Count matches accurately with the Number of Rows Read.

 

Version Tested On

 

Version
Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64)
Oct 28 2016 18:17:30
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) – 12.0.5203.0 (Intel X86)
Sep 23 2016 18:45:55
Copyright (c) Microsoft Corporation
Developer Edition on Windows NT 6.1 (Build 7601: ) (WOW64)

 

 

Source Code Control

GitHub

  1. SQL Server – Statistics I/O – Scan Count – In List Query
    Link

 

GuidePost

Amit Banerjee

Scan Count meaning in SET STATISTICS IO output: Part 2
Link

Even though we are evaluating the condition for all the 20 rows returned by the “backupset” table, we find that there is an additional filter in the SEEK condition i.e. the file_number column, for the data fetched from the “backupfile” table. This changes the scan count to Zero. The reason for this is that the SQL instance now knows that it has to fetch only one row for the value given in the filter as both columns present in the composite primary key are being used during the SEEK. Now SQL knows prior to the fetch that there cannot be duplicate rows, so no extra scan/seek is required.

This is a gotcha that we might overlook when a clustered index seek is being used! Since, we are dealing with a small set of rows, the plan changes may not be significant but for larger tables, such a difference in evaluation may have a significant impact on the query plan and ultimately the performance of the query.

 

Summary

There is a slight difference in how SQL Server measures Scan Counts when unique key scans are being measured, compared to how they are measured when Non-Unique Keys are used.

I don’t get it, but as always Amit Banerjee obviously does.

SQL Server – Statistics IO – Scan Count – When unique Index exist

Background

Reviewing the performance of a query and wanted to dig in deeper into the Statistics IO; specifically the Scan Count.

Lab

dbo.Numbers

Created and Populate Table

Let us create our test table.

It is a simple Numbers table and we will name it dbo.Numbers.

We will fill it with a million records; starting at 1, incrementing by 1, and close out at 1 million.

SQL


use master
go

SET NOCOUNT ON;
go

/*
	drop table [dbo].[Numbers]
*/
if object_id('[dbo].[Numbers]') is null
begin

	create table [dbo].[Numbers]
	(
		[Number] int not null
	)

end 
go

declare @edition sysname
declare @bCompressionSupported bit

set @edition = cast(serverproperty('edition') as sysname)

if (
		@edition in 
		( 
			'Standard Edition (64-bit)'
		)
	)
begin

	set @bCompressionSupported = 0

end
else if (@edition like 'Enterprise%')
begin

	set @bCompressionSupported = 1

end

print 'edition: ' + @edition
print 'compression : '  + cast(@bCompressionSupported as varchar(10))

if not exists
(

	select *
	from   sys.objects tblSO
	where  tblSO.parent_object_id = object_id('[dbo].[Numbers]')
	and    tblSO.[type] = 'PK' 

)
begin

	print 'Creating Primary Key ...'

	if (@bCompressionSupported = 1)
	begin

		alter table [dbo].[Numbers]
			add constraint [PK_Number] primary key
		   (
				[Number]
		   )
			WITH 
			(
				  DATA_COMPRESSION = PAGE
				, FILLFACTOR=100
				, ignore_dup_key=ON
			)				

	end
	else
	begin


		alter table [dbo].[Numbers]
			add constraint [PK_Number] primary key
		   (
				[Number]
		   )
			WITH 
			(
				  DATA_COMPRESSION = NONE
				, FILLFACTOR=100
				, ignore_dup_key=ON
			)				


	end

	print 'Created Primary Key'

end


if not exists
(

	select *
	from   sys.objects tblSO
	inner join sys.indexes tblSI
		on tblSO.object_id = tblSI.object_id
	where  tblSO.object_id = object_id('[dbo].[Numbers]')
	and    tblSI.[name] = 'INDX_Unique_Number' 

)
begin

	print 'Create Non-Clustered Index - INDX_Unique_Number ..'

	if (@bCompressionSupported = 1)
	begin

		create unique nonclustered index [INDX_Unique_Number]
		on [dbo].[Numbers]
		(
			[Number]
		)
		WITH 
		(
			  DATA_COMPRESSION = PAGE
			, FILLFACTOR=100
			, ignore_dup_key=ON
		)				

	end
	else
	begin


		create unique nonclustered index [INDX_Unique_Number]
		on [dbo].[Numbers]
		(
			[Number]
		)
		WITH 
		(
			  DATA_COMPRESSION = NONE
			, FILLFACTOR=100
			, ignore_dup_key=ON
		)				


	end

	print 'Created Non-Clustered Index - INDX_Unique_Number'

end


if not exists
(

	select *
	from   sys.objects tblSO
	inner join sys.indexes tblSI
		on tblSO.object_id = tblSI.object_id
	where  tblSO.object_id = object_id('[dbo].[Numbers]')
	and    tblSI.[name] = 'INDX_NonUnique_Number' 

)
begin

	print 'Create Non-Clustered Index - INDX_NonUnique_Number ..'

	if (@bCompressionSupported = 1)
	begin

		create nonclustered index [INDX_NonUnique_Number]
		on [dbo].[Numbers]
		(
			[Number]
		)
		WITH 
		(
			  DATA_COMPRESSION = PAGE
			, FILLFACTOR=100
			, ignore_dup_key=OFF
		)				

	end
	else
	begin


		create nonclustered index [INDX_NonUnique_Number]
		on [dbo].[Numbers]
		(
			[Number]
		)
		WITH 
		(
			  DATA_COMPRESSION = NONE
			, FILLFACTOR=100
			, ignore_dup_key=OFF
		)				


	end

	print 'Created Non-Clustered Index - INDX_NonUnique_Number'

end

go

DECLARE @UpperLimit INT;

set @UpperLimit = 1000000;

if not exists
	(
		select 1
		from   [dbo].[Numbers]
	)

begin

	print 'Adding data into [dbo].[Numbers] ....'

	;WITH cteNumber 
	AS
	(
			SELECT
				x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
			FROM  sys.all_objects AS s1
			CROSS JOIN sys.all_objects AS s2
			CROSS JOIN sys.all_objects AS s3
	)
	INSERT INTO [dbo].[Numbers]
	SELECT [Number] = cte.[x]
    FROM   cteNumber cte
    WHERE  cte.[x] BETWEEN 1 AND @UpperLimit;
 
	print 'Added data into [dbo].[Numbers]'

end

GO


 

 

Query Table

It is time to query the table

We will progressively add more numbers to our “In Clause”.

SQL



/*

	set statistics io on;
	set nocount on;

*/
set noexec off
go

set noexec off
go

use [master]
go

print replicate('=', 120)

print 'Searching for 1 Number'

SELECT
        [Extent1].[Number] AS [Number]  -- 1 Value
		FROM [dbo].[Numbers] AS [Extent1]
 		WHERE [Extent1].[Number] IN
		 (  1 ) 

print replicate('=', 120)

print 'Searching for 2 Numbers'

SELECT
        [Extent1].[Number] AS [Number]  -- 1-2 Value
		FROM [dbo].[Numbers] AS [Extent1]
 		WHERE [Extent1].[Number] IN
		 (
			  1, 2
		 ) 

print replicate('=', 120)

print 'Searching for 10 Numbers'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 thru 10 Values
		FROM [dbo].[Numbers] AS [Extent1]
 		WHERE [Extent1].[Number] IN
		 (
			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
		 ) 


print replicate('=', 120)

print 'Searching for 60 Numbers'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 thru 10 Values
		FROM [dbo].[Numbers] AS [Extent1]
 		WHERE [Extent1].[Number] IN
		 (
			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
			, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
			, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
			, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
			, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
			, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
		 ) 


print replicate('=', 120)

print 'Searching for 64 Numbers'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 Thur 64 Values
		FROM [dbo].[Numbers] AS [Extent1]
 		WHERE [Extent1].[Number] IN
		 (
			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
			, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
			, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
			, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
			, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
			, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
			, 61, 62, 63, 64
		 ) 

		 
print replicate('=', 120)

------------------------------------------------------------------------------------------------

print 'Searching for 65 Numbers'

SELECT 
        [Extent1].[Number] AS [Number]  -- 65 Values
		FROM [dbo].[Numbers] AS [Extent1]
		WHERE [Extent1].[Number] IN
		 (

			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
			, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
			, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
			, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
			, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
			, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
			, 61, 62, 63, 64, 65
		 ) 

print replicate('=', 120)

------------------------------------------------------------------------------------------------

print 'Searching for 65 Numbers ( Using Unique Index)'

SELECT 
        [Extent1].[Number] AS [Number]  -- 65 Values
		FROM [dbo].[Numbers] AS [Extent1] with ( INDEX=INDX_Unique_Number)
		WHERE [Extent1].[Number] IN
		 (

			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
			, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
			, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
			, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
			, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
			, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
			, 61, 62, 63, 64, 65
		 ) 

print replicate('=', 120)

------------------------------------------------------------------------------------------------


print 'Searching for 65 Numbers ( Using Non-Unique Index)'

SELECT 
        [Extent1].[Number] AS [Number]  -- 65 Values
		FROM [dbo].[Numbers] AS [Extent1] with ( INDEX=INDX_NonUnique_Number)
		WHERE [Extent1].[Number] IN
		 (

			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
			, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
			, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
			, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
			, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
			, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
			, 61, 62, 63, 64, 65
		 ) 

print replicate('=', 120)


print 'Searching for 66 Numbers'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 thru 66 Values
		FROM [dbo].[Numbers] AS [Extent1]
		WHERE [Extent1].[Number] IN
		 (

			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
			, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
			, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
			, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
			, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
			, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
			, 61, 62, 63, 64, 65, 66
		 ) 

print replicate('=', 120)

------------------------------------------------------------------------------------------------

print 'Searching for 70 Numbers'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 thru 90 Values
		FROM [dbo].[Numbers] AS [Extent1]
		WHERE [Extent1].[Number] IN
		 (

			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
			, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
			, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
			, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
			, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
			, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
			, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70
		 ) 

print replicate('=', 120)

---------------------------------------------------------------------------------------------------
go



 

Output

Query Plan

Explanation
  1. Message :- Scan a particular range of rows from a nonclustered index.
  2. Actual Number of Rows :- 65
  3. Estimated Number of Executions :- 65
  4. Number of Executions :- 65
  5. Number of Rows Read :- 65
Statistics IO

 

Tabulate

Scenario Statistics I/O Scan Count
 Searching for 1 Number Table ‘Numbers’. Scan count 0, logical reads 3, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 0
 Searching for 2 Numbers  Table ‘Numbers’. Scan count 2, logical reads 6, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  2
 Searching for 10 Numbers  Table ‘Numbers’. Scan count 10, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  10
 Searching for 60 Numbers  Table ‘Numbers’. Scan count 60, logical reads 180, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  60
 Searching for 64 Numbers  Table ‘Numbers’. Scan count 64, logical reads 192, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  64
 Searching for 65 Numbers  Table ‘Numbers’. Scan count 0, logical reads 302, physical reads 1, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  0
 Searching for 65 Numbers ( Using Unique Index)  Table ‘Numbers’. Scan count 0, logical reads 208, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  0
 Searching for 65 Numbers ( Using Non-Unique Index)  Table ‘Numbers’. Scan count 0, logical reads 208, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  0
 Searching for 66 Numbers  Table ‘Numbers’. Scan count 0, logical reads 211, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  0
Searching for 70 Numbers  Table ‘Numbers’. Scan count 0, logical reads 223, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  0

 

 

Version Tested On

 

Version
Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64)
Oct 28 2016 18:17:30
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) – 12.0.5203.0 (Intel X86)
Sep 23 2016 18:45:55
Copyright (c) Microsoft Corporation
Developer Edition on Windows NT 6.1 (Build 7601: ) (WOW64)

 

 

Source Code Control

GitHub

  1. SQL Server – Statistics I/O – Scan Count – In List Query
    Link

 

MS Connect

  1. Statistics IO / Scan Count not always indicative
    Number :- 3133893
    Status :- Active
    Type :- Bug
    Opened :- 5/8/2017 9:46:53 AM
    Link

 

Summary

Not so sure why Scan Count is being reported as zero when more than 65 values are being queried on.

 

References

  1. Amit Banerjee ( SQL Server Escalation Engineer [ SEE ] , Microsoft SQL support )
    • SCAN COUNT meaning in SET STATISTICS IO output
      Link
    • Scan Count meaning in SET STATISTICS IO output: Part 2
      Link