Category: SQL Server

Categories
SQL Server

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)
values 
    (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
);
Categories
SQL Server

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
as
begin
	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
		end;
			
	declare @prefix decimal(38, 0) = --adjust precision to your needs. Can scale higher bytes the lower precision is
		case 
			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 
		end;

	return convert(sysname, @prefix) +
		case
				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'
			end;
end;
Categories
SQL Server

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

USE [Tools]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 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
as 
begin;
	-- 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;
end;
Categories
SQL Server

Agent Alerts Not Configured – Brent Ozar Unlimited®

Here is Brent Ozar’s script to add important alerts: via Agent Alerts Not Configured – Brent Ozar Unlimited®

The below script sets up SQL Server Agent alerts for severity 16 through 25 as well as specific alerts for 823, 824 and 825 errors.  Do a search & replace in this code and change ‘The DBA Team’ to the name of your SQL Agent operator – and remember, always use email distribution lists for your operator emails, not individual peoples’ emails.  Someday – you’re going to go on vacation, and you want other people to get notified when you’re gone.

USE [msdb]
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Severity 016',
 @message_id=0,
 @severity=16,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000';
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 016', @operator_name=N'The DBA Team', @notification_method = 7;
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Severity 017',
 @message_id=0,
 @severity=17,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000';
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 017', @operator_name=N'The DBA Team', @notification_method = 7;
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Severity 018',
 @message_id=0,
 @severity=18,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000';
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 018', @operator_name=N'The DBA Team', @notification_method = 7;
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Severity 019',
 @message_id=0,
 @severity=19,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000';
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 019', @operator_name=N'The DBA Team', @notification_method = 7;
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Severity 020',
 @message_id=0,
 @severity=20,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000';
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 020', @operator_name=N'The DBA Team', @notification_method = 7;
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Severity 021',
 @message_id=0,
 @severity=21,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000';
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 021', @operator_name=N'The DBA Team', @notification_method = 7;
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Severity 022',
 @message_id=0,
 @severity=22,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000';
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 022', @operator_name=N'The DBA Team', @notification_method = 7;
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Severity 023',
 @message_id=0,
 @severity=23,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000';
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 023', @operator_name=N'The DBA Team', @notification_method = 7;
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Severity 024',
 @message_id=0,
 @severity=24,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000';
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 024', @operator_name=N'The DBA Team', @notification_method = 7;
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Severity 025',
 @message_id=0,
 @severity=25,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000';
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 025', @operator_name=N'The DBA Team', @notification_method = 7;
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Error Number 823',
 @message_id=823,
 @severity=0,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 823', @operator_name=N'The DBA Team', @notification_method = 7;
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Error Number 824',
 @message_id=824,
 @severity=0,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 824', @operator_name=N'The DBA Team', @notification_method = 7;
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Error Number 825',
 @message_id=825,
 @severity=0,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 825', @operator_name=N'The DBA Team', @notification_method = 7;
 GO
Categories
mssql SQL Server

SQL Server: Date and Time Data Types (Size and Ranges)

SQL Server Date and Time data types and storage spaces.
Thank you for this reference chart, whoever made this!

Tip: DateTime2(3) has the same number of digits as DateTime but uses 7 bytes of storage instead of 8 bytes (SQLHINTS- DateTime Vs DateTime2).

Categories
SQL Server

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.

Categories
SQL Server

TempDB Best Practices

  • TempDB should have as many data files as there are cores available to SQL Server, but only up to 8 data files. Too many TempDB data files can cause contention. Recommendation: Create 8 files and test from there on.
  • TempDB should only have 1 log file. Extra log files will improve performance as they are written to sequentially.
  • The data files for TempDB should all be the same size.
  • TempDB data files and TempDB log files should be on separate disks.
  • TempDB files should be on the fastest disks.