Selectivity

After a breif discussion with a collegue about selectivity this script was created to have something concrete to work with.

-- Single key
SELECT TOP(10)
 SalesOrderID,
 (CAST(COUNT(*) AS float) / CAST((SELECT COUNT(*) FROM Sales.SalesOrderDetail) AS float))*100 AS [percent]
FROM Sales.SalesOrderDetail WITH (NOLOCK)
GROUP BY SalesOrderID
ORDER BY [percent] DESC

-- Combined key
SELECT TOP(10)
 SalesOrderID,
 SalesOrderDetailID,
 (CAST(COUNT(*) AS float) / CAST((SELECT COUNT(*) FROM Sales.SalesOrderDetail) AS float))*100 AS [percent]
FROM Sales.SalesOrderDetail WITH (NOLOCK)
GROUP BY SalesOrderID, SalesOrderDetailID
ORDER BY [percent] DESC

On SQL Server 2005 the database is [AdventureWorks] and on SQL Server 2008 the database is [Adventureworks2008].

Still I'm (a little) uncertain if the % calculation is correct - right now the percentage is low (good), but should it be high (good)? I'm looking for a more precise calculation definition.

Differential database backup using SMO

A differential database backup of a single database can be done like this using SMO and PowerShell:

[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$SsdbName = 'Sandbox\Ssdb0'
$BackupPath = 'Z:\Backup\Ssdb0'
$Server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $SsdbName
$Database = $Server.Databases['msdb']
$Backup = New-Object 'Microsoft.SqlServer.Management.Smo.Backup'
$Backup.Checksum = $true
$Backup.Database = $Database.Name
$Backup.Incremental = $true
$Backup.BackupSetDescription = 'Differential backup of the database [' + $Database.Name + '].'
$BackupFileName = $Database.Name + '_Diff.bak'
$BackupDevice = New-Object 'Microsoft.SqlServer.Management.Smo.BackupDeviceItem'
$BackupDevice.DeviceType = 'File'
$BackupDevice.Name = [System.IO.Path]::Combine($BackupPath, $BackupFileName)
$Backup.Devices.Add($BackupDevice)
$Backup.BackupSetName = $Database.Name + ' ' + $BackupType + ' backup'
$Backup.SqlBackup($Server)
$Backup.Wait()
"[{0}] is backed up to the file '{1}'." -f $Database.Name, $BackupDevice.Name

The generated backup file is named "msdb_Diff.bak". A more unique filename is preferred, but this is just a simple example.

The output of the script is:
[msdb] is backed up to the file 'T:\Backup\Ssdb0\msdb_Diff.bak'.

A more detailed description of SMO backup using PowerShell is done by Muthusamy Anantha Kumar in a article at Database Journal (link).

PowerShell V2 CTP3 released

Just before Christmas the Community Technology Preview 3 (CTP) of PowerShell was released.
It is not yet posted at the TechNet PowerShell site (http://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx), but the download is at Microsoft Download Center (http://www.microsoft.com/downloads/details.aspx?FamilyID=c913aeab-d7b4-4bb1-a958-ee6d7fe307bc&DisplayLang=en).
The PowerShell Team has made a short blog post about the release (http://blogs.msdn.com/powershell/archive/2008/12/23/early-christmas-present-from-powershell-team-community-technology-preview-3-ctp3-of-windows-powershell-v2.aspx).

Let's take a look at it...

SMO Backup class moved

Having worked on a (new) backup solution for my employer, I have spend several hours fighting with examples not working.
Until I found the explanation and a solution at Allen Whites blog: http://sqlblog.com/blogs/allen_white/archive/2008/12/07/loading-smo-assemblies-into-powershell.aspx

I still wonder why the the classes listed in another blog entry by Allen (http://sqlblog.com/blogs/allen_white/archive/2007/10/19/sql-2008-smo-doing-a-little-rearranging.aspx) were moved and then destroying the compability between SQL 2005 and SQL 2008...

HTA dynamic document II

The solution I sketched out 2008-10-07 I found out had one major fault - the entire DOM document was cleared, which made the solution a little too dynamic. For example also script tags was cleared...
Today I found an article about making a GUI using HTA: "Extreme Makeover: Wrap Your Scripts Up in a GUI Interface". This inspired me to play a little with the innerHTML attribute.

The script looks like this:
<?xml version=1.0" encoding=utf-8"?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Strict//EN">

<html>

<head>
 <title>Dynamic Document</title>
 <hta:application id="htaDynamicDocument" applicationname="DynamicDocument" />
 <style>
  h1, h2, p { font-family:sans-serif }
 </style>
</head>

<script type="text/jscript" language="jscript">
 function newContent() {
  var markup = "<p>Navigare necesse est!<p>"
  return markup;
 }

 function jsContent() {
  myArea.innerHTML = "<h2>jsContent</h2>" + newContent();
 }
</script>

<script type="text/vbscript" language="vbscript">
 Sub vbContent_onclick
  markup = "<p>Vivere non est necesse,</p>"
  myArea.innerHTML = "<h2>vbContent</h2>" + markup + newContent()
 End Sub
</script>

<body>
 <h1>Dynamic Document</h1>

 <input type="button" value="JScript content" onclick="jsContent()">
 &emsp;
 <input type="button" value="VBScript content" name="vbContent">

 <span id="myArea"></span>

 <p style="font-size:smaller">&copy; 2008, Niels Grove-Rasmussen &mdash; SQLAdmin.dk</p>
</body>

</html>

An execution of the script starts like this:

Clicking "JScript content" alter the document to this:

Clicking "VBScript content" alter the document to this:

If "JScript content" is clicked again, the document looks like the second image.

The trick is in two parts:

1. The span-section with the identifier "myArea".
2. Adding a string to the innerHTML attribute of the span-section.

The style section in the head and the JScript function newContent() are made to show that shared elements does have effect in the contents of the innerHTML attribute.