SQL Server script to create missing indexes on all foreign keys

Here is a handy script to create missing indexes on all foreign keys.
Based on the script over at lukelowrey.com.

-- Call procedure to retrieve error information.  
CREATE or ALTER proc [Utility].[usp_GetErrorMessage](
	@message nvarchar(max) output,
	@procid int = null
set @message = 
		when @procid is not null then N'Proc=' + object_schema_name(@procid) + N'.' + object_name(@procid) + N', '
		else N'<unknown procedure>'
	end +
	N'ErrorNumber=' + convert(nvarchar(max),error_number()) +
	N', Severity=' + convert(nvarchar(max),error_severity()) +
	N', State=' + convert(nvarchar(max),error_state()) +
	N', Line=' + convert(nvarchar(max),error_line()) +
	N', Message=' + convert(nvarchar(max),error_message());

CREATE or ALTER proc [Utility].[usp_AutomaticallyCreateIndexesForForeignKeys](
	@Execute bit = null
	set nocount on;

	declare @CurrentSQLCommand nvarchar(max);
	declare @IndexName TABLE ( [SQL] nvarchar(450) primary key );
	declare @message nvarchar(max);

	WITH v_NonIndexedFKColumns AS (
			[Table_Name] = OBJECT_NAME(a.[parent_object_id]),
			[Column_Name] = b.[name]
		FROM sys.foreign_key_columns [a]
			,sys.all_columns [b]
			,sys.objects [c]
		WHERE a.[parent_column_id] = b.[column_id]
			AND a.[parent_object_id] = b.[object_id]
			AND b.[object_id] = c.[object_id]
			AND c.[is_ms_shipped] = 0
				[Table_Name] = OBJECT_NAME(a.[object_id]),
				[Column_Name] = b.[name]
			FROM sys.index_columns [a]
				,sys.all_columns [b]
				,sys.objects [c]
			WHERE a.[object_id] = b.[object_id]
				AND a.[key_ordinal] = 1
				AND a.[column_id] = b.[column_id]
				AND a.[object_id] = c.[object_id]
				AND c.[is_ms_shipped] = 0
	INSERT INTO @IndexName( [SQL] )
		'CREATE INDEX [ix_' + v.[Table_Name] + '_' + v.[Column_Name] + ']'
		+ ' ON [' + SCHEMA_NAME(fk.[schema_id]) + '].[' + v.[Table_Name] + '] ([' + v.[Column_Name] + ']);'
	FROM v_NonIndexedFKColumns [v]
		,sys.all_columns [c]
		,sys.all_columns [c2]
		,sys.foreign_key_columns [fkc]
		,sys.foreign_keys [fk]
	WHERE v.[Table_Name] = OBJECT_NAME(fkc.[parent_object_id])
		AND v.[Column_Name] = c.[name]
		AND fkc.[parent_column_id] = c.[column_id]
		AND fkc.[parent_object_id] = c.[object_id]
		AND fkc.[referenced_column_id] = c2.[column_id]
		AND fkc.[referenced_object_id] = c2.[object_id]
		AND fk.[object_id] = fkc.[constraint_object_id];

	FROM @IndexName;

	OPEN sqlCommands;
	FETCH NEXT FROM sqlCommands INTO @CurrentSQLCommand;

	while @@FETCH_STATUS = 0 begin;
		print @CurrentSQLCommand;

		if @Execute = 1 begin try;
			exec sp_executesql @CurrentSQLCommand;
		end try
		begin catch;
			exec [Utility].[usp_GetErrorMessage] @[email protected] output, @[email protected]@PROCID;
			throw 51000, @message, 1;
		end catch;

		fetch next from sqlCommands into @CurrentSQLCommand;
	close sqlCommands;
	deallocate sqlCommands;

How to create unsigned data types in SQL Server

Script to create unsigned data types in SQL Server.

USE [Numbers]

DROP TYPE if exists [dbo].[UInt16];
DROP TYPE if exists [dbo].[UInt32];
DROP TYPE if exists [dbo].[UInt64];
DROP TYPE if exists [dbo].[UFloat];
DROP TYPE if exists [dbo].[UReal];
DROP TYPE if exists [dbo].[ZeroToOne];

DROP RULE if exists [dbo].[UInt16];
DROP RULE if exists [dbo].[UInt32];
DROP RULE if exists [dbo].[UInt64];
DROP RULE if exists [dbo].[UFloat];
DROP RULE if exists [dbo].[UReal];
DROP RULE if exists [dbo].[ZeroToOne];

CREATE TYPE dbo.[UInt16] FROM smallint NULL;
CREATE TYPE dbo.[UInt64] FROM bigint NULL;
CREATE TYPE dbo.[UFloat] FROM float NULL;
CREATE TYPE dbo.[ZeroToOne] FROM float NOT NULL;

CREATE RULE dbo.UInt16 AS @value >= 0;
CREATE RULE dbo.UInt32 AS @value >= 0;
CREATE RULE dbo.UInt64 AS @value >= 0;
CREATE RULE dbo.UFloat AS @value >= 0;
CREATE RULE dbo.UReal AS @value >= 0;
CREATE RULE dbo.ZeroToOne AS @value between 0.0 and 1.0;

Don’t let this little SQL slip delete your rows!

I came across this quick example that shows how easily the tiniest little mistake could end up deleting rows from your table.

The solution to this bug? Always name & reference your tables, CTE, and subqueries!

-- Create and populate test tables.
drop table if exists dbo.purge_test;

create table dbo.purge_test(
    id int primary key,
    purge_type int not null default 1, 
    purge_date datetime not null default current_timestamp

insert into dbo.purge_test(id) values(99);

drop table if exists dbo.star_wars_film;

create table dbo.star_wars_film (
    film_id int primary key,
    film_description varchar(50) not null

insert into dbo.star_wars_film(film_id, film_description)
    (1,'Star Wars - The phantom menace'),
     (2,'Revenge of the Sith'),
     (3,'Attack of the clones'),
     (4,'A new hope'),
     (5,'The empire strikes back'),
     (6,'Return of the Jedi'),
     (7,'The force awakens'),
     (8,'The last Jedi'),
     (-1,'Rogue One');

And now you can play: Spot the Bug™!

-- Check the data before the unspotted bug.
select film_id, film_description from dbo.star_wars_film order by film_id;

-- Bug! Do you see it? The query runs just fine.. right?
delete from dbo.star_wars_film
where film_id in (
    select film_id from dbo.purge_test where purge_type = 1

-- After the delete.
-- Expected the same rows as before because we *know* the purge id of 99 wasn't one of the file_id.
-- Right?
select film_id, film_description from dbo.star_wars_film order by film_id;

-- Corrected query. Won't execute now because specified column doesn't exist.
delete swf
from dbo.star_wars_film swf
where swf.film_id in (
    select pt.film_id from dbo.purge_test pt where pt.purge_type = 1

SQL Function to convert bytes to string

-- select [dbo].[FormatBytes]( 13241322.567567, 'bytes' );
-- select [dbo].[FormatBytes]( 132413225.567567, 'bytes' );
-- select [dbo].[FormatBytes]( 1324132255.567567, 'bytes' );
-- select [dbo].[FormatBytes]( 13241322551.567567, 'bytes' );
-- select [dbo].[FormatBytes]( 13241322.567567, 'mb' );
-- select [dbo].[FormatBytes]( 132413225.567567, 'gb' );
-- select [dbo].[FormatBytes]( 1324132255.567567, 'tb' );
-- select [dbo].[FormatBytes]( 13241322551.567567, 'zb' );
-- select [dbo].[FormatBytes]( 13241322551.567567, 'yb' );
-- select [dbo].[FormatBytes]( 132413225512.567567, 'yb' );
-- select [dbo].[FormatBytes]( 132413225, 'bb' );
-- select [dbo].[FormatBytes]( 1324132253, 'bb' );	--too big!
-- select [dbo].[FormatBytes]( 132413, 'geopbyte' );
-- select [dbo].[FormatBytes]( 1324135, 'geopbyte' );	--too big!

create or alter function [dbo].[FormatBytes] ( @bytes decimal(38, 2), @toUnit nvarchar(15) = N'bytes' )
returns sysname
with schemabinding
	set @bytes = @bytes *
		case @toUnit collate SQL_Latin1_General_CP1_CI_AI

			when N'b' then 1
			when N'byte' then 1
			when N'bytes' then 1

			when N'kb' then 1024
			when N'kilobyte' then 1024
			when N'kilobytes' then 1024

			when N'mb' then 1048576
			when N'megabyte' then 1048576
			when N'megabytes' then 1048576

			when N'gb' then 1073741824
			when N'gigabyte' then 1073741824
			when N'gigabytes' then 1073741824
			when N'tb' then 1099511627776
			when N'terabyte' then 1099511627776
			when N'terabytes' then 1099511627776

			when N'pb' then 1125899906842624
			when N'petabyte'then 1125899906842624
			when N'petabytes' then 1125899906842624

			when N'eb' then 1152921504606846976
			when N'exabyte' then 1152921504606846976
			when N'exabytes' then 1152921504606846976

			when N'zb' then 1180591620717411303424
			when N'zettabyte'then 1180591620717411303424
			when N'zettabytes' then 1180591620717411303424

			when N'yb' then 1208925819614629174706176
			when N'yottabyte' then 1208925819614629174706176
			when N'yottabytes' then 1208925819614629174706176

			when N'bb' then 1237940039285380274899124224
			when N'brontobyte' then 1237940039285380274899124224
			when N'brontobytes' then 1237940039285380274899124224

			when N'geopbyte' then 1267650600228229401496703205376
			when N'geopbytes' then 1267650600228229401496703205376
	declare @prefix decimal(38, 0) = --adjust precision to your needs. Can scale higher bytes the lower precision is
			when abs(@bytes) < 1024 then @bytes --bytes 
			when abs(@bytes) < 1048576 then (@bytes / 1024) --kb 
			when abs(@bytes) < 1073741824 then (@bytes / 1048576) --mb 
			when abs(@bytes) < 1099511627776 then (@bytes / 1073741824) --gb 
			when abs(@bytes) < 1125899906842624 then (@bytes / 1099511627776) --tb 
			when abs(@bytes) < 1152921504606846976 then (@bytes / 1125899906842624) --pb 
			when abs(@bytes) < 1180591620717411303424 then (@bytes / 1152921504606846976) --eb 
			when abs(@bytes) < 1208925819614629174706176 then (@bytes / 1180591620717411303424) --zb 
			when abs(@bytes) < 1237940039285380274899124224 then (@bytes / 1208925819614629174706176) --yb 
			when abs(@bytes) < 1267650600228229401496703205376 then (@bytes / 1237940039285380274899124224) --bb 
			else (@bytes / 1267650600228229401496703205376) --geopbytes 

	return convert(sysname, @prefix) +
				when abs(@bytes) < 1024 then N' Bytes'
				when abs(@bytes) < 1048576 then N' KB'
				when abs(@bytes) < 1073741824 then N' MB'
				when abs(@bytes) < 1099511627776 then N' GB'
				when abs(@bytes) < 1125899906842624 then N' TB'
				when abs(@bytes) < 1152921504606846976 then N' PB'
				when abs(@bytes) < 1180591620717411303424 then N' EB'
				when abs(@bytes) < 1208925819614629174706176 then N' ZB'
				when abs(@bytes) < 1237940039285380274899124224 then N' YB'
				when abs(@bytes) < 1267650600228229401496703205376 then N' BB'
				else N' geopbytes'

SQL Server function to return the “Xrd” day of a month and year.

USE [Tools]
-- select dbo.XrdDay(4,'thursday', 'november', 2018)	-- Thanksgiving
-- select dbo.XrdDay(4,'thursday', 'november', 2019)	-- Thanksgiving
-- select dbo.XrdDay(4,'thursday', 'november', 2020)	-- Thanksgiving

-- Must have the day of week and the name of month spelled correctly.
create or alter function [dbo].[XrdDay](
	@xrd int				--3rd, 4th, 5th, etc.
	,@dayname varchar(8)	--'saturday'
	,@month varchar(10)		--'february'
	,@year smallint			--year
returns date
	-- declare @xrd int = 3; declare @dayname varchar(8) = 'monday'; declare @month varchar(10) = 'september'; declare @year smallint = 2019;

	declare @Result date=  @month + ' ' + convert(varchar(64),(@xrd*7) - 6) + ',' + convert(varchar(64), @year);	-- get Sunday.
	--select @Result, datename(weekday, @Result);

	--I'm fairly sure this is inefficient, but I can't seem to recall of another way around it.
	--to make it better, we need to calc @sunday+int==dayname in one go, not this while loop.
	while datename(weekday, @Result) collate SQL_Latin1_General_CP1_CI_AS != @dayname set @Result = dateadd( day, 1, @Result );
	--select @Result, datename(weekday, @Result);

	return @Result;

“Annoying Bosses” Vent

So. Our team worked to improve the performance of the company’s two 16-year old desktop C# applications.. I focused my work on the applications and on the database procedures.

I personally made the main application load in 1 second on an Intel i5 with 4GB of ram running around 3ghz.. when it used to take ~10 seconds to load on the same types of computers. Literally 10 times faster!

I also cut the codebase literally in half, coalescing logic that was duplicated across multiple methods. Also fixed all known errors (and found some traps that would have been bad for the company.. such as calculating taxes and shipping incorrectly).

I changed the main internal logic to use awaits and async where appropriate. I also added error trapping (catching & logging logic/network/user exceptions) to almost the entire codebase.

I updated the .NET framework to the latest version.

I found and fixed all possible SQL injections.. the company had had no idea that the injections could even have happened. I made the database calls clean, fast, and error free. Cached static (information that rarely changed) responses so the geographically remote branches only had to pull information once from the main database. And there used to be methods that made the same database calls information repeatedly.. sometimes a single keypress would fire off a chain of duplicate database calls.

I created unit tests.. the CIO of the company had no idea what a “unit test” was.. or how to create & use LINQ in C#. I fixed most of the Array(s) of Objects that they stuffed Controls into because they wanted a “dynamic” interface. (It was horrible!)

The rest of the team and I (mostly 40% me and 60% them) also reworked the database tables to use the proper types. For example: changing bigint or int down to tinyint where appropriate. And tinyint to ints. Datetimes to Date where the “time” part was not needed.

We normalized almost all of the tables.. removed any unused or duplicate indexes.. the applications performances’ became very fast and responsive.

I spent the entire year fixing this mess they had created with multiple so-called ‘programmers‘ over the last 16 years. I worked nearly constantly.. never spent more than a minute or two away from coding. Excluding meetings, sick days, bathroom breaks, and lunch of course. If I was at my desk, then I was working.

I also took on the task with half the salary it should have been because I liked the company. Big Mistake™ on my part.. I blindly ignored the fact that companies can and will replace you with a cheaper programmer on a whim.

And can you guess what they did on the day I fixed the last known bug? They let me “go“. As in, “See yah, now we don’t need yah!”. Didn’t even get a thank you “for your hard work and saving the company thousands of dollars!” 🗯

I was like “Really? How rude..!

But I also figured, “Okay.. they can pay someone else to keep this application maintained and updated by the newbie programmer they hired next.. the bosses’s son.”

Professional nepotism.. and that’s what is annoying.

Oh well.. I’ve updated my résumé and moved on from that nightmare of a mess we had fixed.

Thanks for reading my vent.

SQL Server: What is Data Warehousing?

A data warehouse is the main repository of a company’s historical data.

Data warehouses can contain a variety of data that presents a coherent picture of the business’s conditions at a point in time.

The main factor of using a data warehouse is that analysts can perform complex queries (data mining) on the information without slowing down the production database servers.