Back from vacation

What happened while I was away on vacation?
SELECT
  [databases].[name],
  [server_principals].[name] AS [database_owner_name],
  [databases].[create_date],
  [databases].[state_desc]
FROM [master].[sys].[databases]
INNER JOIN [master].[sys].[server_principals] ON
  [databases].[owner_sid] = [server_principals].[sid]
WHERE
  [databases].[database_id] >= 4 AND  -- Filter out system databases
  [databases].[create_date] >= '2012-07-27';

Readable seconds

When I restored a SQL Server database, the message ended with a status of the restore:
RESTORE DATABASE successfully processed 15025885 pages in 3372.932 seconds (34.803 MB/sec).

That is a lot of seconds, but I was asked by management about the restore time. Just giving a couple of thousands of seconds was just not good enough.
But PowerShell came to the rescue with the CmdLet New–TimeSpan:
(New-TimeSpan -Seconds 3372.932).ToString()
that gives
00:56:13

The format is hh:mm:ss given by the ToString() method to the .NET TimeSpan structure.

As the CmdLet is based on the .NET TimeSpan structure, and PowerShell is integrated with .NET, it is also possible to use the static method FromSeconds() exposed by the structure:
[System.TimeSpan]::FromSeconds(3372.932).ToString()

The result is exactly the same — as expected.

ISO 4217 Currency list

To maintain a list of currencies, I have been looking for a standard and a external resource.
The standard ISO 4217 is described at Wikipedia, and though that article I found the resource at iso.org in a XML document.

A quick way to read the currency list using PowerShell:
[xml]$ISO4217 = (New-Object System.Net.WebClient).DownloadString('http://www.currency-iso.org/dl_iso_table_a1.xml')

Write-Verbose -Message "Currency count = $($ISO4217.ISO_CCY_CODES.ISO_CURRENCY.count)" -Verbose

foreach($Currency in $ISO4217.ISO_CCY_CODES.ISO_CURRENCY) {
  $Currency
}

The output on a currency is like this:
ENTITY          : DENMARK
CURRENCY        : Danish Krone
ALPHABETIC_CODE : DKK
NUMERIC_CODE    : 208
MINOR_UNIT      : 2

When you have the currency list, I think it is rather simple to update a internal list, e.g. in a database table.

You could add properties like last update timestamp or private identifier to meet your own needs.

Also the wikipedia article has a list of historical currency codes, that could be added to the internal list.

Logging in T-SQL scripts

I am working on a major version upgrade of a system that is using SharePoint and private databases. In both cases with 100+ GB data. Most database parts are scripted, and some tasks are running for hours.
Still it is important to the planning to know the execution time of the tasks and their steps.

This logging and timing I have done by wrapping the steps in some messages:
DECLARE @start datetime2 = SYSDATETIME();
DECLARE @errmsg nvarchar(2047) = CONVERT(nchar(23), @start, 126) + N'Z : Start';
RAISERROR(@errmsg,0,0) WITH NOWAIT;

-- Do something
WAITFOR DELAY '00:00:04';  -- HH:mm:ss

DECLARE @finish datetime2 = SYSDATETIME();
DECLARE @duration int = DATEDIFF(ms, @start, @finish);
SET @errmsg = CONVERT(nchar(23), @finish, 126) + N'Z : Finish after %i ms.';
RAISERROR(@errmsg, 0,0, @duration) WITH NOWAIT;

The duration of the step is in this case measured in milliseconds (ms), but could be measured in another unit. Unfortunately this unit can not be in a parameter, or as they say in the documentation on DATEDIFF() „User-defined variable equivalents are not valid.“.

I do prefer RAISERROR to PRINT to log in T-SQL. There are many discussions about this, but my take is that with RAISERROR ... WITH NOWAIT I will get the message immidiately and not when the batch block is finished and the PRINT–queue is processed.

PowerShell thoughts

To get a readable duration instead of a lot of ms like 56135000 you can create a TimeSpan object in PowerShell.
PS> \([System.TimeSpan]::FromMilliseconds(56135000)).ToString()
This will give a formatted timespan
15:35:35
Which is 15 hours, 35 minutes and 35 seconds.

If I have a larger script file with several steps, I would go for a PowerShell solution as I then would have better possibilities to log and use general parameters.

History

2012-06-26 Entry created.
2015-02-05 Section about TimeSpan object added.
2023-02-23 Formatting on @duration changed to conversion specification. And timestamp simplyfied.

Stopwatch

Some time ago I write about timestamp difference, and I have used it for measuring execution times.
But actually there is a much better way.

By using the Stopwatch class (System.Diagnostics.Stopwatch) I get a measument by a single object in stead of two DateTime (System.DateTime) objects.
Also I can take a look at the stopwatch while it is running by the Elapsed property.
The result given is a TimeSpan (System.TimeSpan) object, and by reading the value with the ToString method it is very usefull.

The Stopwatch is created by calling the static method StartNew():
PS C:\> $mywatch = [System.Diagnostics.Stopwatch]::StartNew()
Stopping the stopwatch is
PS C:\> $mywatch.Stop()
Reading the stopwatch without formatting the answer is also quite simple:
PS C:\> $mywatch.Elapsed

Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 14
Milliseconds      : 975
Ticks             : 149755134
TotalDays         : 0,000173327701388889
TotalHours        : 0,00415986483333333
TotalMinutes      : 0,24959189
TotalSeconds      : 14,9755134
TotalMilliseconds : 14975,5134
Reading the stopwatch with standard formatting [HH:mm:ss.ddddddd] gives you the result more readable:
PS C:\> $mywatch.Elapsed.ToString()
00:15:02.9033008

I use it for execution times. On scripts for the entire script and some subtasks.