Skip to content
May 13 13

Random Password Generator Part II

by Dan Thompson

As promised! A Part II of the Random Password Generator which was created as a stored procedure. I was stumped in the previous post as to how I was going to get this into a function. A function would be a lot nicer to use as you could sub-query it onto a table of users to generate new passwords (as an example).

While I was scratching my head, one of my colleges pointed out that you could put rand() into a view and call the view. Sceptical at first to the randomness of rand() in a view I dismissed it, but of course this was a viable solution!

So for your clipboard’s pleasure, here is the scalar function to randomly generate a password of variable length!
(use the buttons at the top of the code block to copy nicely!):


IF object_id('dbo.Rand_V') IS NOT NULL
	DROP VIEW dbo.Rand_V
GO

create View [dbo].[Rand_v]
as
	select RAND() as RandNum
GO

-- =============================================
-- Author:		Daniel Thompson
-- Create date: 30 May 2012
-- Description:	A Scalar function to return a random password!
-- ExampleA: SELECT dbo.GenerateRandomPassword(1,1,2,18)
-- ExampleB: SELECT username, dbo.GenerateRandomPassword(2,1,4,16) [NewPassword] From dbo.users
-- ExampleC: SELECT username, UPPER(dbo.GenerateRandomPassword(2,1,4,16)) [NewPassword] From dbo.users --all uppercase characters
-- =============================================
CREATE FUNCTION dbo.GenerateRandomPassword
(
	@qtyUpperCase	tinyint,
	@qtySymbols		tinyint,
	@qtyNumeric		tinyint,
	@passLength		int
)
RETURNS varchar(250)
AS
BEGIN

	-- Declare static character arrays.
	DECLARE @lowercase	varchar(26) = 'abcdefghijklmnopqrstuvwxyz',
			@uppercase	varchar(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
			@symbols	varchar(10) = '#@+?!£$^'

	DECLARE @qtyLowerCase	int	--Calculated

	SET @qtyLowerCase = @passLength - @qtyNumeric - @qtySymbols - @qtyUpperCase
	IF (@qtyLowerCase < 1)
	BEGIN
		--RAISERROR('Cannot generate password. Check length variables',16,1)
		RETURN '';
	END

	DECLARE @tblPassword TABLE(i float NOT NULL PRIMARY KEY CLUSTERED ,
								 c varchar(10) NOT NULL)
	DECLARE @r tinyint = 0

	--lowercase
	WHILE ((SELECT count(*) FROM @tblPassword) < @qtyLowerCase)
	BEGIN
		SET @r = ROUND((len(@lowercase)-1)*(SELECT TOP 1 RandNum FROM dbo.Rand_v) ,0)+1

		INSERT INTO @tblPassword(i,c)
		SELECT (SELECT TOP 1 RandNum FROM dbo.Rand_v),substring(@lowercase,@r,1)
	END

	WHILE ((SELECT count(*) FROM @tblPassword) < (@qtyLowerCase + @qtySymbols))
	BEGIN
		SET @r = ROUND((len(@symbols)-1)*(SELECT TOP 1 RandNum FROM dbo.Rand_v),0)+1

		INSERT INTO @tblPassword(i,c)
		SELECT (SELECT TOP 1 RandNum FROM dbo.Rand_v), substring(@symbols,@r,1)
	END

	WHILE ((SELECT count(*) FROM @tblPassword) < (@qtyLowerCase + @qtySymbols + @qtyUpperCase))
	BEGIN
		SET @r = ROUND((len(@uppercase)-1)*(SELECT TOP 1 RandNum FROM dbo.Rand_v),0)+1

		INSERT INTO @tblPassword(i,c)
		SELECT (SELECT TOP 1 RandNum FROM dbo.Rand_v), substring(@uppercase,@r,1)
	END

	WHILE ((SELECT count(*) FROM @tblPassword) < (@qtyLowerCase + @qtySymbols + @qtyUpperCase + @qtyNumeric))
	BEGIN
		INSERT INTO @tblPassword(i,c)
		SELECT (SELECT TOP 1 RandNum FROM dbo.Rand_v), CAST(ROUND(9*(SELECT TOP 1 RandNum FROM dbo.Rand_v),0) AS int)
	END

	DECLARE @pass as varchar(250) = ''

	SELECT @pass = @pass + p1.c
	FROM @tblPassword p1
	ORDER BY i

	RETURN @pass

END
GO

 

This allows you to perform the following very easily:

SELECT
	 username
	,dbo.GenerateRandomPassword(2,1,4,16) [NewPassword]
FROM dbo.users

or...

UPDATE dbo.users
SET [Password]=dbo.GenerateRandomPassword(2,1,4,16)
WHERE [Password] IS NULL
Feb 21 12

Random Password Generator

by Dan Thompson
Generate Password

Here’s a script I created a while ago which generates a random password. It can be used to generate a password of variable length, incorporating symbols, numeric and lower and upper case characters. I’d really like to make it possible to join a recordset onto this by creating it as a function, but not sure where to go with it because of the rand() and newid() functions. Apparently including them in the SQL means I can’t create a function out of it. Could be something todo with the randomness being not random! But feel free to post any bugs, or improvements you find to this one.

/*
Created By: Dan Thompson
Date:		25 Aug 2011

Usage:
	EXEC  dbo.GeneratePassword @qtyUpperCase=1, @qtySymbols=1, @qtyNumeric=3, @passLength=10

Notes:
	Cannot be taken into UDF due to rand() and newid()
*/
IF OBJECT_ID('dbo.GeneratePassword') IS NOT NULL
	DROP PROCEDURE dbo.GeneratePassword

GO

CREATE PROCEDURE dbo.GeneratePassword(
	@qtyUpperCase	tinyint=1,
	@qtySymbols		tinyint=1,
	@qtyNumeric		tinyint=2,
	@passLength		int = 8
)
AS
BEGIN

	DECLARE @lowercase	varchar(26) = 'abcdefghijklmnopqrstuvwxyz',
			@uppercase	varchar(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
			@symbols	varchar(10) = '#@+?!£$^'

	DECLARE @qtyLowerCase	int	--Calculated

	SET @qtyLowerCase = @passLength - @qtyNumeric - @qtySymbols - @qtyUpperCase
	IF (@qtyLowerCase < 1)
	BEGIN
		RAISERROR('Cannot generate password. Check length variables',16,1)
		RETURN
	END

	DECLARE @tblPassword TABLE (i uniqueidentifier NOT NULL PRIMARY KEY CLUSTERED DEFAULT newsequentialid(),
								 c varchar(10) NOT NULL)
	DECLARE @r tinyint = 0

	--lowercase
	WHILE ((SELECT count(*) FROM @tblPassword) < @qtyLowerCase)
	BEGIN
		SET @r = ROUND((len(@lowercase)-1)*rand(),0)+1

		INSERT INTO @tblPassword(c)
		SELECT substring(@lowercase,@r,1)
	END

	WHILE ((SELECT count(*) FROM @tblPassword) < (@qtyLowerCase + @qtySymbols))
	BEGIN
		SET @r = ROUND((len(@symbols)-1)*rand(),0)+1

		INSERT INTO @tblPassword(c)
		SELECT substring(@symbols,@r,1)
	END

	WHILE ((SELECT count(*) FROM @tblPassword) < (@qtyLowerCase + @qtySymbols + @qtyUpperCase))
	BEGIN
		SET @r = ROUND((len(@uppercase)-1)*rand(),0)+1

		INSERT INTO @tblPassword(c)
		SELECT substring(@uppercase,@r,1)
	END

	WHILE ((SELECT count(*) FROM @tblPassword) < (@qtyLowerCase + @qtySymbols + @qtyUpperCase + @qtyNumeric))
	BEGIN
		INSERT INTO @tblPassword(c)
		SELECT CAST(ROUND(9*rand(),0) AS int)
	END

	DECLARE @pass as varchar(max) = ''

	SELECT @pass = @pass + p1.c
	FROM @tblPassword p1
	ORDER BY i

	SELECT @pass

END

I’d really like to come back to this and figure if I can include it in a function somehow. I was thinking some kind of row sampling might yield some kind of random seed using number tables, but i think that’s a long shot. Hopefully there will be a Part II to this in the future!

Feb 21 12

Copy all files in a tree structure into one destination folder

by Dan Thompson
PowerShell

This is an old approach which I have used in SSIS Packages to flattern a directory tree into one source folder.

I wanted to retrieve all CSV files in a folder structure and copy them into a single folder so that a SSIS package can then process them….I know some people might think, well why don’t you just tick the box that says “Traverse Subfolders” in a for-each component – but I needed the files in a single folder for humans too in this case.

 

First Approach – DOS/CMD Prompt

for /r C:\SourceRootFolder %f in (*.csv) do @copy "%f" C:\DestinationFolder

Second Approach – Powershell

Get-ChildItem C:\SourceRootFolder -Recurse -Filter "*.csv" | `
    Copy-Item -destination C:\DestinationFolder

Either of these commands can be executed from within an Execute Process Task in SSIS, or just as commands from either CMD-Prompt or PowerShell Prompt.

Feb 21 12

Powershell – Convert BMP to JPG via pipeline (Get-ChildItem)

by Dan Thompson
PowerShell - Convert BMP to JPG

A script to convert a bunch of Bitmap files to Jpegs via PowerShell pipe from Get-ChildItem.

Copy the code below and save it as ‘Convert-ToBmpToJpeg.ps1′.

#Convert-BmptToJpeg.ps1

foreach ($filepath in $input) {
	if (Test-Path $filepath)
	{
		#Load required assemblies and get object reference
		[Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null
		$convertfile = new-object System.Drawing.Bitmap($filepath.FullName)
		$newfilname = ($filepath.FullName -replace '([^.]).bmp','$1') + ".jpg"
		$convertfile.Save($newfilname, "jpeg")
		"Converting...`t" + $filepath.FullName + "`t->`t" + $newfilname
		$convertfile.Dispose()
	}
	else
	{
		Write-Host "Path not found."
	}
}

Now to call this script, open up PowerShell and navigate to the directory of the saved script. You can now ‘pipe’ a bunch of files into this script. So for example, if I wanted to convert all the bitmaps in my ‘My Pictures’ folder, I’d perform the following command:

 Get-ChildItem -Filter *.bmp -Recurse | .\Convert-BmpToJpeg.ps1 

The first command ‘Get-ChildItem -Filter *.bmp -Recurse’ gets all BMP files from within the folder tree. The rest of the command is to pipe the files collected into the Convert-BmpToJpeg.ps1 script which performs the basic conversion. I would recommend checking the quality of the Jpeg files created…as far as I remember you can set a quality property within this conversion process.

For the next step, you might want to delete all the BMP files (once you’ve checked you’re happy with the quality of the JPEGs). To do this you’ll need to pipe all the BMP files into the Remove-Item Cmdlet. When I do anything like this, I always use the ‘WhatIf’ parameter so I can see the effect without performing the command:

 Get-ChildItem -Filter *.bmp -Recurse | Remove-Item -WhatIf 
Jan 28 12

Use Team Foundation Server (TFS) as your Source Control in SSMS

by Dan Thompson
Source Control in SSMS

Update:
This also will allow you to connect to visualstudio.com

If you want to start using Team Foundation Server as your source control in SSMS it’s fairly easy to set up. Microsoft have made available to download the MSSCCI providers which supply support for source control to a list of products which don’t natively support TFS.

This post assumes you already have a TFS server set up and running. In my case, I already have Visual Studio 2005/2008 and can connect up to my 2008 server already using the built in support for TFS. But you don’t need to have VS installed to use SSMS with TFS.

First of all, you’ll need to install the TFS provider. There are a few different versions of the TFS provider. In my case, I’m connecting to TFS server 2008, so I can use any version of the TFS provider which is 2008 or above. For the 2010/2012 versions there are 64-bit or 32-bit build . I’m running Windows Server 2008 R2 (64-bit) as my OS and in control panel I have SQL Server 2008 R2 (64-bit) installed. However, my version of SSMS is 32-bit. You can check easily by opening SSMS and opening task manager. The *32 at the end of the ssms.exe entry shows it is 32-bit.

Task Manager showing SSMS 32-bit

Task Manager showing SSMS 32-bit

So in this case I chose to install the TFS 2010 (32-bit version) which works fine for me, but for ease below are the links to the Team Foundation Server MSSCCI providers:

To be on the safe side, close SSMS before running the installation.

Once installed, go into SSMS. Then in the Tools menu right down at the bottom you have options. In the options window in the tree navigation you’ll have options for Source Control (as below).

If the installation went OK and you have installed the correct version, you should have “Team Foundation Server MSSCCI Provider” available. Select this provider. There are some additional settings which can be tweaked, but are more preferences than settings to get source control working. (Click OK to save your settings changes).

To connect up to TFS, look in the File menu, and go to Source Control and select Open From Source Control:

Source control menu in SSMS

Source control menu in SSMS

You’ll be prompted to select a server:

Connect to a Team Foundation Server

Select a TFS server to connect to

As this is the first time we’ve opened it, there are no servers to pick from. Click Servers… and click Add… to enter the details of the TFS server you wish to connect to. You may be prompted to enter credentials here. Click Close and Cancel.

Now you can create new projects (or open existing ones) to/from TFS.

To create a new project. Select File…New…Project in the usual way, but ensure that the tick-box Add to Source Control is ticked:

SSMS New Project add to Source Control

SSMS New Project add to Source Control

Hope that provides you with enough information to start adding your SSMS solutions into TFS. Now I can create a library of all our job scripts for a particular server and add the solution into TFS all via SSMS without needing to use VS! Please feel free to add comments of your own implementation experiences and how TFS is going to benefit you via SSMS!

Nov 25 11

Calculate the number of days between two dates not including weekend days

by Dan Thompson
GetDate()

This one’s not as tricky as it seems. Initially, for those not wanting a detailed explanation of how this works, i’d like to show the equation and the SQL, then go into the explanation.

The equation:

(EndDate - StartDate)
-
(
  (Number of Weeks between StartDate and EndDate x2)
  +1 [if StartDate is a Sunday]
  +1 [if EndDate is a Saturday]
)

The SQL:

DECLARE @StartDate datetime, @EndDate datetimeSELECT @StartDate='2011-01-02', @EndDate='2011-01-13'

SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1) --Number of days between Start and End
-
(
(DATEDIFF(wk, @StartDate, @EndDate) * 2) --Weekend Days
+(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) --Taking into consideration the startdate being a Sunday
+(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) --Taking into consideration the enddate being a Saturday
)

Explanation

For this whole example i have selected a date range which begins on a Sunday, ends on a Thursday and includes 3 weekend days. In order to get the number of days (24 hour periods) between two date ranges you’d usually use DATEDIFF:

DECLARE @StartDate datetime, @EndDate datetime

SELECT @StartDate='2011-01-02', @EndDate='2011-01-13'

SELECT DATEDIFF(d,@startdate,@enddate)

Result: 11

Explanation: 11x 24 hour periods between 2011-01-02 00:00:00.000 AND 2011-01-13 00:00:00.000

So if we wanted to know how many days rather than 24 hour periods the date range includes within it we would need to +1 that number. It might seem like you should calculate the time of the date to make sure, but you don’t – you can simple +1 the days. To prove this, in the example above the widest we can make the date range is to extend the @EndDate to ’2011-01-13 23:59:59.997′ (or 9 if you prefer!) so we can never include 12 days. Likewise with regards to the time element, the above example is the most limited we can get. I just wanted to clarify the +1 rule actually worked always, and as far as i can figure it does.

So 12 ‘days’ between the two dates, or 11x 24 hour periods going on the way DATEDIFF works. How many Saturday’s and Sundays?

Looking at the calendar we know there are 3 weekend days. So our total needs to show 12-3=9 days (not including the weekend days). Once we get this figure i’ll test it against a few different date ranges. So far we know we can DATEDIFF+1, now we need to subtract the number of weekend days. We know that each week contains two weekend days – Saturday and Sunday. So we can account for the days between the start and end date by calculating the number of weeks between the two dates and multiplying that number by 2, again using DATEDIFF:

DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate='2011-01-02', @EndDate='2011-01-13'

SELECT DATEDIFF(wk, @StartDate, @EndDate) * 2 --Weekend Days

That calculates the number of 7 day periods between the two dates. So 13th – 2nd = 11 days. 11 days / 7 days (rounded down) = 1

So that now shows we have one period of 7 days between the two days (which we multiply by 2) = 2 days.

We know that there’s actually 3 days between the start and end dates (if you include the start and end dates). We need to take into account that if we define a start or end date as a weekend day then it should add these days onto the total to be deducted. A case statement resolves this, which concludes our statement:


DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate='2011-01-02', @EndDate='2011-01-13'

 SELECT
 (DATEDIFF(dd, @StartDate, @EndDate) + 1) --Number of days between Start and End
 -
 (
  (DATEDIFF(wk, @StartDate, @EndDate) * 2) --Weekend Days
  +(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) --Taking into consideration the startdate being a Sunday
  +(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) --Taking into consideration the enddate being a Saturday
  )

I’ve tested it out in a few different scenarios and it seems to work ok.

Performance!

So you have a table which has a StartDate and EndDate column. You have a load of rows, and you want to perform this calculation out on all the rows. You’ll need an algorithm that performs well! I’m not going to include the SQL to generate test data to test this against, as I simply don’t have time. But i’m going to run this algorithm against a table here and show you how it performs and see if we can gain any improvements from it.

The table i’m running this against has around 10,000 rows in. I’m going to use the DateCreated and DateModified columns in this particular table to test the performance of this algorithm. I’m not going to put the algorithm in a function for now, as i want to see the raw performance. I may put it into a function later if there’s negligible performance difference in doing so.

I’ve just executed it against that data-set using the DateCreated as the StartDate and DateModified as the end date and it’s executed in a split second! (There are no indexes against the date columns in case you wondered).

I created a function to house this algorithm and the performance of that function was very similar. But i’m open to ideas if anyone can see a way to increase the performance!

Here’s the function for anyone interested (sorry about the name!)

CREATE FUNCTION dbo.DATEDIFF_WorkingDaysOnly(@StartDate datetime, @EndDate datetime)
RETURNS INT
AS
BEGIN
 RETURN (DATEDIFF(dd, @StartDate, @EndDate) + 1) --Number of days between Start and End
 -
 (
  (DATEDIFF(wk, @StartDate, @EndDate) * 2) --Weekend Days
  +(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) --Taking into consideration the startdate being a Sunday
  +(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) --Taking into consideration the enddate being a Saturday
  )
END 
Oct 17 11

Searching All Stored Procedures and SQL Agent Job Steps

by Dan Thompson
Stored Procedures

Every now and again I get asked if any specific business logic is going through any of the steps of our scheduled jobs or stored procs. This script will search through the commands within any job steps and through any stored procs (across all databases).

DECLARE @term varchar(max)
SET @term = 'DELETE FROM Products'

SET @term = '%' + @term + '%'

--All DBs:
DECLARE @sql varchar(max)
SET @sql = 'USE [?]
            SELECT ''?'' [Database], schema_name([schema_id]) [schema], Name
				FROM [?].Sys.procedures
				WHERE OBJECT_DEFINITION(object_id) LIKE ''' + @term + ''''

EXEC sp_msforeachdb @sql

SELECT j.name, js.*
FROM msdb.dbo.sysjobsteps js
INNER JOIN msdb.dbo.sysjobs j ON js.job_id = j.job_id
WHERE js.command LIKE @term

/*
Search text in a stored proc in sql 2000
*/
--SELECT OBJECT_NAME(id),*
--FROM syscomments
--WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1 and text LIKE @term

Bare in mind that sp_msforeachdb is an undocumented stored procedure by Mircosoft.

Hopefully this code will be useful to others.

Oct 15 11

Using Find and Replace to your advantage!

by Dan Thompson
Featured Image

Learning how to use Find and Replace (CTRL+H) within SQL Server Management Studio can save you loads of time!

Firstly, I want to put a bit of a warning in about find and replace. It can save lots of time when repeating manual tasks of entering characters between words etc., but it can also cause huge data issues if you get the mix wrong. As long as you control what is going to be replaced and you consider the implications of your search criteria you’ll narrow the risk.

In this example, we have a list of Surnames 1-100 ordered by ‘PopularityIndex’ and we want to insert this data into one table using a union of the data. Currently the data is in the form [PopularityIndex],[Surname]. Firstly, we need to quote enclose the surname. Then we need to add another column to the end of the current data which is a Country_Key of 3 in this case. Then we need to UNION ALL the list of statements.

Of course there are other ways to import this data in, including BCP and SSIS. But if you master find and replace you’ll find it’s quicker to enter small amounts of data this way.

So our SQL Statement currently looks as follows:

INSERT INTO Surnames (PopularityIndex,Surname,Country_Key)
100,SMITH
99,BROWN
98,TAYLOR
97,JOHNSON
96,WALKER
95,WRIGHT
94,ROBINSON
93,THOMPSON

We know that there will always be a comma to the left of our surname and furthermore we know that none of our Surnames contain columns. So we can do a Find and Replace to find a , (comma) and Replace with ,’ (comma, single-quote). This will give us the following data:

INSERT INTO Surnames (PopularityIndex,Surname,Country_Key)
100,'SMITH
99,'BROWN
98,'TAYLOR
97,'JOHNSON
96,'WALKER
95,'WRIGHT
94,'ROBINSON
93,'THOMPSON

 

Now we need a single-quote at the end of the data… but there are no characters visible. If you were to look at the data in an application such as Notepad++ and show all characters, we can see that each line ends with a CR LF pattern:

This is a Carriage-Return, Line-Feed combination and is written into the file as a hidden character to show where each line ends. We can pattern match on these hidden characters in SSMS, but first it’s always wise to see which type of line endings are used. Some environments include different line endings to others. Generally Windows uses the CRLF combination.

Within SSMS you can pattern match on a variety of symbols and hidden characters – such as CR/LF and the TAB key. In the Find and Replace dialog in SSMS there is actually a handy link which shows us which characters to use to get the results we’re after. At the end of the ‘Find What:’ and ‘Replace With:’ fields there’s a play button which drops down a menu. At the bottom of this menu is a link to the full reference document:

So now you know how to find that document in future to help you with your intelligent Find and Replaces, let’s go back to our query. We know now we need to replace our CRLF’s with a single quote – not forgetting to put our CRLF back at the end!

The backslash is used as an escape character for a few standard hidden characters, so for example \n refers to a ‘new line’, and \t refers to a ‘TAB’ key. So let’s replace the term \n with ‘\n:

This will put a single-quote at the end of each line for us – that’s saved a lot of key presses right?!

Now let’s add our last column in using the same technique, find \n and replace with ,3\n This will put a ,3 at the end of every row. That’s our last column sorted. Now we’ll need a SELECT at the start of every row, so replace \n with \nSELECT (remember to include a space at the end of ‘SELECT ‘). So you know what’s next now – let’s replace our new lines in order to insert our ‘UNION ALL’ statement:

 

You’ll notice the first line ‘INSERT INTO…’ also includes the column, so delete the column from there and the UNION ALL which is underneath. You’ll also need to remove any extra lines, in my case there was an extra SELECT at the end of the file. Sorted – you can run your INSERT statement and send those rows in:

INSERT INTO Surnames (PopularityIndex,Surname,Country_Key)
SELECT 100,'SMITH',3
UNION ALL
SELECT 99,'BROWN',3
UNION ALL
SELECT 98,'TAYLOR',3
UNION ALL
SELECT 97,'JOHNSON',3
UNION ALL
SELECT 96,'WALKER',3
UNION ALL
SELECT 95,'WRIGHT',3
UNION ALL
SELECT 94,'ROBINSON',3
UNION ALL
SELECT 93,'THOMPSON',3

 

Now, what if we went wrong? Run the \nUNION ALL\n statement again. This will create 3x UNION ALLs between every SELECT. Now if we wanted to remove those 3x UNION ALLs and replace them with just one, we’d run the following:

Back to normal!

 

I hope this blog encourages you to use the Find and Replace tool more intelligently and saves your fingers from unnecessary key strokes! Remember to always check your data and that you can use undo at any point to roll back a find and replace.

Oct 11 11

Table Scan vs. Index Scan

by Dan Thompson
Featured Image2

We know that an index seek would be preferred over an index scan, but what should we do if we see a table scan in our execution plan? This demonstration shows the difference between a table scan and an index scan.

A table scan is performed on a table which does not have an index upon it (a heap) – it looks at the rows in the table. An index scan is performed on an indexed table – the index itself.

I’ve seen it written that the two are the same, and I can understand why that conclusion is made. In fact running side-by-side there is little difference between a table scan and an index scan. In terms of performance, they both run the same. The performance balance between the two is changed when you are wanting to do any meaningful operations on the data, such as join another table.

I’d like to demonstrate first the similarity of the two without furthering the query:

 

SET NOCOUNT ON;

USE [master]
GO

IF NOT EXISTS (SELECT * FROM sys.databases WHERE [name] = 'Index_Test')
	CREATE DATABASE Index_Test
GO

USE [Index_Test]
GO

IF EXISTS(select * FROM sys.tables WHERE [name] = 'without_index')
	DROP TABLE without_index

CREATE TABLE without_index
(
	ID int NOT NULL,
	value varchar(50) NOT NULL
)

IF EXISTS(select * FROM sys.tables WHERE [name] = 'with_index')
	DROP TABLE with_index

CREATE TABLE with_index
(
	ID int NOT NULL PRIMARY KEY CLUSTERED,
	value varchar(50) NOT NULL
)

INSERT INTO with_index
SELECT 1,'testvalue'
UNION
SELECT 2,'anothertestvalue'
UNION
SELECT 3, 'my last test value'
INSERT INTO without_index
SELECT 1,'testvalue'
UNION
SELECT 2,'anothertestvalue'
UNION
SELECT 3, 'my last test value'

Then if Include Actual Execution Plan (CTRL+M), and execute the following:

SELECT * FROM dbo.without_index
SELECT * FROM dbo.with_index
Execution Plan Table vs Index Scan
Execution Plan

You’ll notice that the first statement on the table without the index will cause a ‘Table Scan’, while the second statement causes an ‘Index Scan’.

Notice as well that the I/O usage and CPU cost for these two operations are different, however the sub tree cost for the two operations is identical.

Table Scan:

Table Scan cost

Table Scan cost

Clustered Index Scan:

Index Scan cost

Index Scan cost

If we were to create a non-clustered index on the table ‘without_index’:

CREATE NONCLUSTERED INDEX [IX_TEST] ON [dbo].[without_index]
(
[ID] ASC,
[value] ASC
)

And we perform the two selects statements we’ll get two index scans:

Index Scan vs Non-Clustered Index Scan

Index Scans

You’ll notice that the I/O and CPU cost is the same.

This is only for 3 rows of ordered data inserted. If you take that to millions of rows the results are still the same. The real benefit to having an index on the table is made aparent when you want to perform any operations on this data such as joining another table. If we magnify the scale of the inserts up to 1m, when a join onto another table is performed the table scan performs a sort and uses a paralellism to hash join the data. Whereas our indexed table is straing joined as the data is already sorted. This is when the performance cost is 96% of the batch for the without_index table and just 4% for with_index table over 1m rows.

Conclusion

So we know that index seeks are preferred over index scans. This demonstration shows that index scans are preferred over table scans. In fact, a good rule of thumb is to always include at least one index on a table this way we avoid table scans altogether.

 

This final script will drop the database:

--DROP DATABASE
USE [master]
GO

ALTER DATABASE [Index_Test] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DROP DATABASE [Index_Test]
GO
Sep 28 11

Welcome to SQL By Numbers!

by Dan Thompson
Home

This is just a holding page until i get round to writing my first blog! You’ll notice in the screenie I was attempting different methods of writing “SQL By Numbers” creatively. I’m sure there are many other ways!