SqlServer: tally table performance


drop table #t;
create table #t (n int);
select 'Step 1', GETDATE();
;with [seed](n) as (
select 1 UNION ALL SELECT 1),
l01(n) as (
select 1 from [seed] a cross apply [seed] b),
l02(n) as (
select 1 from l01 a cross apply l01 b),
l03(n) as (
select 1 from l02 a cross apply l02 b),
l04(n) as (
select 1 from l03 a cross apply l03 b),
l05(n) as (
select 1 from l04 a cross apply l04 b),
myTally(n) as (select row_number() over (order by n) from l05)
select count(1) from myTally
where n < 10000000;
select 'Step 2', GETDATE();
-- that took about 5 seconds
;with [seed](n) as (
select 1 UNION ALL SELECT 1),
l01(n) as (
select 1 from [seed] a cross apply [seed] b),
l02(n) as (
select 1 from l01 a cross apply l01 b),
l03(n) as (
select 1 from l02 a cross apply l02 b),
l04(n) as (
select 1 from l03 a cross apply l03 b),
l05(n) as (
select 1 from l04 a cross apply l04 b),
myTally(n) as (select row_number() over (order by n) from l05)
insert into #t (n)
select n from myTally
where n < 10000000
-- that took over a minute (but not apples and apples)
select 'Step 3', GETDATE();

SqlTally01

SqlServer backup and restore

Useful commands…

–Addumpdevice…
https://msdn.microsoft.com/en-gb/library/ms188409(v=sql.110).aspx

— restore options
https://msdn.microsoft.com/en-gb/library/ms186858(v=sql.110).aspx

USE master;
GO
EXEC sp_addumpdevice ‘disk’, ‘mydiskdump’, ‘F:\ssistemp\BikeRides.Bak’;

select * from sys.backup_devices

 

restore database BikeRides
from mydiskdump
with
move ‘BikeRides’ to ‘f:\database\data\BikeRides.mdf’,
move ‘BikeRides_log’ to ‘f:\database\logs\BikeRides_log.ldf’

 

PowerShell: Testing with Pester

Scripting Guy has a useful series here. Github is here.

Json: great site for faking up test data

Great site here. First 2 show the template I wrote for my composer test data. Final one is the author’s show-it-all template.

Here I try to reduce the Json to the bare minimum (I just want the “composer” line). But I can’t get the 2 dates to work inside the function. Doubtless me. It will do for now.

[
 {
 'repeat(3,3)': {
 dateOfBirth: '{{moment(this.date(new Date(1684, 0, 1), new Date(1722, 0, 1))).format("L")}}',
 dateOfDeath: '{{moment(this.date(new Date(1722, 0, 2), new Date(1801, 0, 1))).format("L")}}', 
 composer: function (tags) {
 return tags.surname().toUpperCase() + ', ' + tags.firstName() + 
 ' (' + this.dateOfBirth + ' - ' + this.dateOfDeath + ') ' ;
 }
 }
 }
]