Categories
Numbers script sql SQL Script Strings tsql

SQL Server Function: How to format bytes into greater units like MB, GB, TB, etc

Here’s a link to my gist for a SQL script to return a string from bytes formatted as a larger unit.

Examples

select [dbo].[FormatBytes]( 1324.13, 'byte' );
select [dbo].[FormatBytes]( 13241322.567567, 'bytes' );
select [dbo].[FormatBytes]( 1324132255.567567, 'tb' );

Link: https://gist.github.com/Protiguous/c40ef32ee4fa0f4e93c9413d6cc3d6bd

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]( 1324.13, 'byte' );
select [dbo].[FormatBytes]( 1324135, 'geopbyte' ); –too big!
create or alter function [dbo].[FormatBytes] ( @bytes decimal(38, 0), @toUnit nvarchar(15) = N'bytes' )
returns sysname
with schemabinding
as
begin
declare @prefix decimal(38, 0); adjust precision to your needs. Can scale higher bytes the lower precision is.
set @toUnit = trim(@toUnit);
if @toUnit is null return null;
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;
set @prefix =
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;
view raw FormatBytes.sql hosted with ❤ by GitHub
Categories
Benchmarking Numbers script

Jeff Moden’s Tally Table Function (Generate Large Numbers)

Testing Jeff’s excellent number table generator with the examples below takes ~8 to ~13 minutes on this 6-CPU 8GB ram, 3.8 GHz virtual machine running SQL Server 2019 Developer Edition CU8. Server maxdop is set to 6, parallelism cost threshold is set to 50. Database maxdop is also set to 6.

Disclaimer: This virtual machine is not optimized for performance. The 6 equally sized tempdb data files are purposely located on slow storage (only ~6200 IOPS) through the same storage controller in an attempt to exaggerate the effect of any “bad” parts of queries. There are also backup (and other various) jobs possibly running during these tests.


Test Query 1: Select Into #temp version, 1 billion numbers.

drop table if exists #temp;
select [N] into #temp from [dbo].[fnTallyBig]( 1073741824 );

Result: Test query 1 took ~13 minutes, went parallel and used all 6 cores; hovered around 15% CPU usage – according to the Hyper-V Manager.


Test Query 2: Select @n=N version, 1 billion numbers.

declare @n bigint;
select @n=[N] from [dbo].[fnTallyBig]( 1073741824 );

Result: Test query 2 took ~8 minutes and also went parallel and used all 6 cores; hovered around 9% CPU usage – according to the Hyper-V Manager.


Testing conclusion: There are no known bad parts of Jeff Moden’s script. (Yay!)
Also: The tempdb performance on this virtual machine is horrible! 😉


This is the modified version of Jeff Moden’s “fnTally” script to create the number generating function. I’ve removed the @ZeroOrOne parameter in favor of always starting at zero.

If you read through the comments, there are alternate versions.. One that lets you specify the starting number!

CREATE OR ALTER FUNCTION [dbo].[fnTallyBig]( @MaxN BIGINT )
/**********************************************************************************************************************
 Purpose:
 Return a column of BIGINTs from 0 up to and including @MaxN with a max value of 10 Quadrillion.

 Usage:
--===== Syntax example
 SELECT t.[N]
   FROM [dbo].[fnTallyBig](@MaxN) t;
 
 select t.[N] into #numbers from [dbo].[fnTallyBig](4294967296) t;

 @MaxN has an operational domain from 0 to 4,294,967,296. Silent truncation occurs for larger numbers.

 Please see the following notes for other important information.

Original script can be found at https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally

 Jeff's Notes:
 1. This code works for SQL Server 2008 and up.
 2. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
    Refer to the following URL for how it works.
    https://www.itprotoday.com/sql-server/virtual-auxiliary-table-numbers
 3. To start a sequence at 0, @ZeroOrOne must be 0. Any other value that's convertible to the BIT data-type will cause the sequence to start at 1.
 4. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
 5. If @MaxN is negative or NULL, a "TOP" error will be returned.
 6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 4,294,967,296. If a larger number is used, the function will silently truncate after that max. If you actually need a sequence with that many or more values, you should consider using a different tool. 😉
 7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending sort is required, use code similar to the following. Performance will decrease by about 27% but it's still very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
    If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.

    DECLARE @MaxN BIGINT; 
     SELECT @MaxN = 1000;
     SELECT DescendingN = @MaxN-N+1 
       FROM dbo.fnTally2(@MaxN);

 8. There is no performance penalty for sorting "N" in ascending order because the output is implicitly sorted by ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
 9. This will return 1-10,000,000 to a bit-bucket variable in about 986ms.
    This will return 0-10,000,000 to a bit-bucket variable in about 1091ms.
    This will return 1-4,294,967,296 to a bit-bucket variable in about 9:12(mi:ss).

 Revision History:
 Rev 00 - Unknown     - Jeff Moden 
        - Initial creation with error handling for @MaxN.
 Rev 01 - 09 Feb 2013 - Jeff Moden 
        - Modified to start at 0 or 1.
 Rev 02 - 16 May 2013 - Jeff Moden 
        - Removed error handling for @MaxN because of exceptional cases.
 Rev 03 - 07 Sep 2013 - Jeff Moden 
        - Change the max for @MaxN from 10 Billion to 10 Quadrillion to support an experiment. 
          This will also make it much more difficult for someone to actually get silent truncation in the future.
 Rev 04 - 04 Aug 2019 - Jeff Moden
        - Enhance performance by making the first CTE provide 256 values instead of 10, which limits the number of CrossJoins to just 2. Notice that this changes the maximum range of values to "just" 4,294,967,296, which is the entire range for INT and just happens to be an even power of 256. Because of the use of the VALUES clause, this code is "only" compatible with SQLServer 2008 and above.
        - Update old link from "SQLMag" to "ITPro". Same famous original article, just a different link because they changed the name of the company (twice, actually).
        - Update the flower box notes with the other changes.
**********************************************************************************************************************/
      
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  H2(N) AS ( SELECT 1 
               FROM (VALUES
                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ) V(N))           --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
            SELECT N = 0 UNION ALL
            SELECT TOP(@MaxN)
                   N = ROW_NUMBER() OVER (ORDER BY N)
              FROM H8;
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
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 script sql

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,
)
GO

-- Create some dummy data
insert into [dbo].[Foo]( [MyKey], [MyColumn] )
values (1, 11), (2, 22), (3, 33), (4, 44);
GO 10240

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

-- 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 [ID] % 3 = 1;


-- Method 2: "Subquery" move
insert [dbo].[Bar]( [MyKey], [MyColumn] )
select [MyKey], [MyColumn]
from (
	delete
	from [dbo].[Foo]
	output deleted.*
	where [ID] % 3 = 2
) [MovedRows]
where [MovedRows].[MyKey] = 2;
-- Note: The last where is not needed, just showing it is possible to re-filter incoming [from the deleted output] rows.

-- Verify the counts after.
-- Note: Any deleted rows filtered out via Method 2 are now gone.
select count(*) from [dbo].[Foo];
select count(*) 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;
Categories
mssql script sql sqlserver

SQL Server Table Smells – Simple-Talk

Here is the corrected version of Phil’s table smell:
Note: This needs to be run per database.

create proc dbo.sp_CodeSmells
as
;WITH TableSmells (TableName, Problem, Object_ID )AS
(
SELECT object_schema_name(Object_ID)+'.'+object_name(Object_ID), Problem,Object_ID FROM
(
SELECT object_id, 'wide (more than 15 columns)'
FROM sys.tables /* see whether the table has more than 15 columns */
WHERE max_column_id_used>15
UNION ALL
SELECT DISTINCT sys.tables.object_id, 'heap'
FROM sys.indexes/* see whether the table is a heap */
INNER JOIN sys.tables ON sys.tables.object_id=sys.indexes.object_id
WHERE sys.indexes.type=0
UNION ALL
SELECT sys.tables.object_id, 'No primary key'
FROM sys.tables/* see whether the table has a primary key */
WHERE objectproperty(object_id,'TableHasPrimaryKey') = 0
UNION ALL
SELECT sys.tables.object_id, 'No index at all'
FROM sys.tables /* see whether the table has any index */
WHERE objectproperty(object_id,'TableHasIndex') = 0
UNION ALL
SELECT sys.tables.object_id, 'No candidate key'
FROM sys.tables/* if no unique constraint then it isn't relational */
WHERE objectproperty(object_id,'
TableHasUniqueCnst') = 0
AND objectproperty(object_id,'
TableHasPrimaryKey') = 0
UNION ALL
SELECT DISTINCT object_id, '
disabled Index(es)'
FROM sys.indexes /* don'
t leave these lying around */
WHERE is_disabled=1
UNION ALL
SELECT DISTINCT parent_object_id, 'disabled constraint(s)'
FROM sys.check_constraints /* hmm. i wonder why */
WHERE is_disabled=1
UNION ALL
SELECT DISTINCT parent_object_id, 'untrusted constraint(s)'
FROM sys.check_constraints /* ETL gone bad? */
WHERE is_not_trusted=1
UNION ALL
SELECT DISTINCT parent_object_id, 'disabled FK'
FROM sys.foreign_keys /* build script gone bad? */
WHERE is_disabled=1
UNION ALL
SELECT DISTINCT parent_object_id, 'untrusted FK'
FROM sys.foreign_keys /* Why do you have untrusted FKs?
Constraint was enabled without checking existing rows;
therefore, the constraint may not hold for all rows. */
WHERE is_not_trusted=1
UNION ALL
/*
SELECT sys.tables.object_id, 'unrelated to any other table'
FROM sys.tables
LEFT OUTER join
(SELECT referenced_object_id AS table_ID
FROM sys.foreign_keys
UNION ALL
SELECT parent_object_id
FROM sys.foreign_keys
)referenced(table_ID)
ON referenced.table_ID=sys.Tables.object_ID
WHERE referenced.table_id IS null*/
SELECT sys.tables.object_id, 'unrelated to any other table'
FROM sys.tables /* found a simpler way! */
WHERE objectpropertyex(object_id,'TableHasForeignKey')=0
AND objectpropertyex(object_id,'TableHasForeignRef')=0
UNION ALL
SELECT DISTINCT object_id, 'unintelligible column names'
FROM sys.columns /* column names with no letters in them */
WHERE name COLLATE Latin1_general_CI_AI
NOT LIKE '%[A-Z]%' COLLATE Latin1_general_CI_AI
UNION ALL
SELECT DISTINCT object_id, 'non-compliant column names'
FROM sys.columns /* column names that need delimiters*/
WHERE name COLLATE Latin1_general_CI_AI
LIKE '%[^_@$#A-Z0-9]%' COLLATE Latin1_general_CI_AI
UNION ALL
SELECT DISTINCT parent_id, 'has a disabled trigger'
FROM sys.triggers
WHERE is_disabled=1 AND parent_id>0
UNION ALL
SELECT sys.tables.object_id, 'can''t be indexed'
FROM sys.tables/* see whether the table has a primary key */
WHERE objectproperty(object_id,'IsIndexable') = 0
)f(Object_ID,Problem)
)
SELECT TableName,
CASE WHEN count(*)>1 THEN /*only do correlated subquery when necessary*/
stuff(( SELECT ', '+Problem
FROM TableSmells t2
WHERE t1.TableName = t2.TableName
ORDER BY Problem
FOR XML PATH(''), TYPE).value(N'(./text())[1]',N'varchar(8000)'),1,2,'')
ELSE max(Problem) END
FROM TableSmells t1 WHERE OBJECTPROPERTYEX(t1.Object_ID, 'IsTable')=1
GROUP BY TableName;