Categories
Script SQL The Arcane

ALL, ANY, and SOME: 3 SQL operators you do NOT want to use…

http://bradsruminations.blogspot.com/2009/08/all-any-and-some-three-stooges.html

-- 4 questions with BoxOf3Coins
with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth more than ANY of the coins in BoxOf3Coins?',case when 25 > any (select CoinValue from BoxOf3Coins) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth more than ALL (EACH) of the coins in BoxOf3Coins?',case when 25 > all (select CoinValue from BoxOf3Coins) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth the same as ANY of the coins in BoxOf3Coins?',case when 25 = any (select CoinValue from BoxOf3Coins) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth the same as ALL (EACH) of the coins in BoxOf3Coins?',case when 25 = all (select CoinValue from BoxOf3Coins) then 'Yes' else 'No' end;

-- Now answer the same question with the EmptyBox
with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth more than ANY of the coins in EmptyBox?',case when 25 > any (select CoinValue from EmptyBox) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth more than ALL (EACH) of the coins in EmptyBox?',case when 25 > all (select CoinValue from EmptyBox) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth the same as ANY of the coins in EmptyBox?',case when 25 = any (select CoinValue from EmptyBox) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth the same as ALL (EACH) of the coins in EmptyBox?',case when 25 = all (select CoinValue from EmptyBox) then 'Yes' else 'No' end;

Categories
Numbers Script SQL

SQL Generate Tally (Number) Table

-- select top 10 [n] from [dbo].[GetNumbers]( 1024 ) order by [n];
create or alter function [dbo].[GetNumbers]( @n bigint )
returns table with schemabinding as
return
-- declare @n bigint = 1024;
with
	l0 as (select 0 [c] union all select 1),
	l1 as (select 1 [c] from l0 [a] cross join l0 [b]),
	l2 as (select 1 [c] from l1 [a] cross join l1 [b]),
	l3 as (select 1 [c] from l2 [a] cross join l2 [b]),
	l4 as (select 1 [c] from l3 [a] cross join l3 [b]),
	l5 as (select 1 [c] from l4 [a] cross join l4 [b]),
	nums as(select row_number() over(order by (select null)) as n from l5)
  select top (@n) n
  from nums
  order by n;
Categories
Script SQL Strings

Jeff Moden’s Script for Splitting CSV Strings up to 8000 Length

Pulled from https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function, I think.

CREATE OR ALTER FUNCTION [dbo].[DelimitedSplit8K](
	@pString VARCHAR(8000),
	@pDelimiter CHAR(1)
) --WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000... enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l;
Categories
Date & Times Script SQL

Improved SQL Function to Generate DateTimes

-- select * from [dbo].[GenDateTimes]( 'year', '1/1/0001', '12/12/9999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'month', '1/1/0001', '12/12/9999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'week', '1/1/0001', '12/12/9999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'day', '1/1/0001', '12/12/9999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'hour', '1/1/0001', '12/12/9999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'minute', '1/1/1999', '12/12/2999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'second', '1/1/1999', '12/12/1999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'millisecond', '1/1/1999', '1/12/1999' ) order by [Date];
create or alter function [dbo].[GenDateTimes](
	@Increment varchar(11),
	@Start datetime2(3),
	@End datetime2(3)
)
returns	@Range table( [Date] datetime2(3) )
as
begin;
	declare @units bigint = 
				case
					when @Increment = 'ms' THEN datediff_big( ms, @Start, @End )
					when @Increment = 'millisecond' THEN datediff_big( millisecond, @Start, @End )
					when @Increment = 's' THEN datediff_big( s, @Start, @End )
					when @Increment = 'second' THEN datediff_big( second, @Start, @End )
					when @Increment = 'mi' THEN datediff_big( mi, @Start, @End )
					when @Increment = 'minute' THEN datediff_big( minute, @Start, @End )
					when @Increment = 'hh' THEN datediff_big( hh, @Start, @End )
					when @Increment = 'hour' THEN datediff_big( hour, @Start, @End )
					when @Increment = 'd' THEN datediff( d, @Start, @End )
					when @Increment = 'day' THEN datediff( day, @Start, @End )
					when @Increment = 'wk' THEN datediff( wk, @Start, @End )
					when @Increment = 'week' THEN datediff( week, @Start, @End )
					when @Increment = 'mm' THEN datediff( mm, @Start, @End )
					when @Increment = 'month' THEN datediff( month, @Start, @End )
					when @Increment = 'y' THEN datediff( y, @Start, @End )
					when @Increment = 'year' THEN datediff( year, @Start, @End )
				end;

	set @Increment = lower( @Increment );

	with
		[l0] as ( select 0 [c] union all select 1 ),
		[l1] as ( select 1 [c] from [l0] [a] cross join [l0] [b] ),
		[l2] as ( select 1 [c] from [l1] [a] cross join [l1] [b] ),
		[l3] as ( select 1 [c] from [l2] [a] cross join [l2] [b] ),
		[l4] as ( select 1 [c] from [l3] [a] cross join [l3] [b] ),
		[l5] as ( select 1 [c] from [l4] [a] cross join [l4] [b] ),
		[nums] as ( select row_number() over( order by ( select null ) ) [n] from [l5] ),
		[cte]( [Date] ) AS (
			select @Start [Date]
			union all
			select
				case
					when @Increment = 'ms' THEN dateadd( ms, [n], @Start )
					when @Increment = 'millisecond' THEN dateadd( millisecond, [n], @Start )
					when @Increment = 's' THEN dateadd( s, [n], @Start )
					when @Increment = 'second' THEN dateadd( second, [n], @Start )
					when @Increment = 'mi' THEN dateadd( mi, [n], @Start )
					when @Increment = 'minute' THEN dateadd( minute, [n], @Start )
					when @Increment = 'hh' THEN dateadd( hh, [n], @Start )
					when @Increment = 'hour' THEN dateadd( hour, [n], @Start )
					when @Increment = 'd' THEN dateadd( d, [n], @Start )
					when @Increment = 'day' THEN dateadd( day, [n], @Start )
					when @Increment = 'wk' THEN dateadd( wk, [n], @Start )
					when @Increment = 'week' THEN dateadd( week, [n], @Start )
					when @Increment = 'mm' THEN dateadd( mm, [n], @Start )
					when @Increment = 'month' THEN dateadd( month, [n], @Start )
					when @Increment = 'y' THEN dateadd( y, [n], @Start )
					when @Increment = 'year' THEN dateadd( year, [n], @Start )
				end
			from [nums] [t]
			where [t].[n] <= @units
	)
	insert into @Range( [Date] )
	select [Date]
	from [cte];

	return;
end;
Categories
Date & Times Script SQL

SQL Function to Generate Date Table with Count

-- select * from [dbo].[GenDates2]( 'd', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates2]( 'w', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates2]( 'm', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates2]( 'y', getdate()-1, getdate()-16384 );
create or alter function [dbo].[GenDates2](
	@Increment char(1),
	@Start datetime2(3),
	@End datetime2(3)
)
returns	@Range table( [Date] datetime2(3), [Count] int )
as
begin;
	set @Increment = lower( @Increment );

	with cte( [Date], [Count] ) AS (
		select @Start [Date], 0 [Count]
		union all
		select
			case
				when @Increment = 'd' THEN dateadd(day, 1, [Date])
				when @Increment = 'w' THEN dateadd(week, 1, [Date])
				when @Increment = 'm' THEN dateadd(month, 1, [Date])
				when @Increment = 'y' THEN dateadd(year, 1, [Date])
			end,
		case
			when @Increment = 'd' THEN datediff( day, @Start, dateadd(day, 1, [Date]))
			when @Increment = 'w' THEN datediff(week, @Start, dateadd(week, 1, [Date]))
			when @Increment = 'm' THEN datediff(month, @Start, dateadd(month, 1, [Date]))
			when @Increment = 'y' THEN datediff(year, @Start, dateadd(year, 1, [Date]))
		end [Count]
		from [cte]
		where [Date] <= 
			case
				when @Increment = 'd' THEN dateadd(day, -1, @End)
				when @Increment = 'w' THEN dateadd(week, -1, @End)
				when @Increment = 'm' THEN dateadd(month, -1, @End)
				when @Increment = 'y' THEN dateadd(year, -1, @End)
			end
	)
	insert into @Range( [Date], [Count] )
	select [Date], [Count]
	from [cte]
	option (maxrecursion 32767);

	return;
end;
Categories
Date & Times Script SQL

SQL Function to Generate Date Table

Update: Please try the Improved version of this script.

-- select * from [dbo].[GenDates]( 'd', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates]( 'w', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates]( 'm', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates]( 'y', getdate()-16383, getdate()+16384 );
create or alter function [dbo].[GenDates](
	@Increment char(1),
	@Start datetime2(3),
	@End datetime2(3)
)
returns	@Range table( [Date] datetime2(3) )
as
begin;
	set @Increment = lower( @Increment );

	with [cte]( [Date] ) AS (
		select @Start
		union all
		select
			case
				when @Increment = 'd' THEN dateadd(day, 1, [Date])
				when @Increment = 'w' THEN dateadd(week, 1, [Date])
				when @Increment = 'm' THEN dateadd(month, 1, [Date])
				when @Increment = 'y' THEN dateadd(year, 1, [Date])
			end
		from [cte]
		where [Date] <= 
			case
				when @Increment = 'd' THEN dateadd(day, -1, @End)
				when @Increment = 'w' THEN dateadd(week, -1, @End)
				when @Increment = 'm' THEN dateadd(month, -1, @End)
				when @Increment = 'y' THEN dateadd(year, -1, @End)
			end
	)
	insert into @Range( [Date] )
	select [Date]
	from [cte]
	option (maxrecursion 32767);

	return;
end;
Categories
How To

Enable Windows Firewall for SQL Server

Run these commands on the Windows Server to open the ports for listening on a named instance of SQL Server.
Note: Remember to enable TCP in the SQL Server Configuration Manager.

netsh firewall set portopening protocol=TCP port=1433 name=SQLServerTCP mode=ENABLE scope=SUBNET profile=CURRENT

netsh firewall set portopening protocol=UDP port=1434 name=SQLServerUDP mode=ENABLE scope=SUBNET profile=CURRENT

For more help, refer to SQL Server & Windows Firewall.

Categories
How To

Example: How to MOVE rows from first table to a second table in a single transaction in SQL Server.

-- NOTE: This is an EXAMPLE to show it is possible
-- to achieve moving rows using an implicit transaction
-- from one table into another table.

-- Set up example tables, Foo & Bar.
drop table if exists [dbo].[Bar];
drop table if exists [dbo].[Foo];

create table [dbo].[Bar](
	[ID] [int] identity(1,1) not null primary key clustered,
	[MyKey] [int] null index ix_mykey,
	[MyColumn] [int] null index ix_mycol
)

create table [dbo].[Foo](
	[ID] [int] identity(1,1) not null primary key clustered,
	[MyKey] [int] null index ix_mykey,
	[MyColumn] [int] index ix_mycol,
)

insert into [dbo].[Foo]( [MyKey], [MyColumn] )
select 1, 11 union all
select 2, 22;

-- Verify the rows before.
select * from [dbo].[Foo];
select * from [dbo].[Bar];

-- NOTE: These move queries create identical execution plans in 2019.

-- Move rows from table Foo into table Bar using two styles.

-- Method 1: Direct move
delete
from [dbo].[Foo]
output [deleted].[MyKey], [deleted].[MyColumn]
into [dbo].[Bar]( [MyKey], [MyColumn] )
where [MyKey] = 2;

-- Method 2: Subquery move
insert [dbo].[Bar]( [MyKey], [MyColumn] )
select [MyKey], [MyColumn]
from (
	delete
	from [dbo].[Foo]
	output deleted.*
	where [MyKey] = 2
) [MovedRows]
--where [MovedRows].[MyKey] = 2;
-- Not needed, but it is possible to filter incoming rows.

-- Verify the rows after.
select * from [dbo].[Foo];
select * from [dbo].[Bar];

Categories
Script SQL

Send a text message from SQL Server 2019

NOTE: You must have SQL Server database mail enabled for this stored procedure to work.

create or alter proc [dbo].[SendTextMessage](
	@number sysname
	,@message nvarchar(max)
	,@subject sysname = null
	,@provider nvarchar(20) = N'Verizon'
)
as
begin;
	set nocount on;

	set @number = trim(@number);
	if @number is null or len(@number)<7 begin;
		set @message = N'@number is too short';
		throw 51000, @message, 1;
	end;

	set @message = trim(@message);
	if @message is null or len(@message)<2 begin;
		set @message = N'@message is too short';
		throw 51000, @message, 1;
	end;

	set @subject = trim(@subject);
	if @subject is null set @subject = @@SERVERNAME + N' Notification';

	declare @to nvarchar(255) = @number + N'@'+
		case @provider
			when N'Verizon' then N'vtext.com'
			when N'VZ' then N'vtext.com'
			when N'V' then N'vtext.com'
			when N'ATT' then N'txt.att.net'
			when N'SPRINT' then N'messaging.sprintpcs.com'
			when N'TMOBILE' then N'tmomail.net'
	end;

	if nullif(@provider,N'@') is null begin;
		throw 51000, 'Unknown provider. Message not sent.', 1;
	end;

	begin try;
		declare @mailitem_id int;
		exec msdb.dbo.sp_send_dbmail @recipients = @to, @subject = @subject, @body = @message, @mailitem_id=@mailitem_id output;
	end try
	begin catch;
		throw 51000, 'Unknown error calling msdb.dbo.sp_send_dbmail.', 1;
	end catch;
end;