Eric Swanson on Technology Services

• The Joel Test: 12 Steps to Better Code - Joel on Software

Something for your SQL toolbox…

I was recently challenged with needing to split a row into multiple rows (a quantity across multiple dates).

We were using a CURSOR, but the performance was horrible. When I converted it to a recursive CTE it went from 6+ minutes to a few seconds!

Below was my “theorem proof” before I implemented the final solution…

*Note: PIVOT would be used to split a row into multiple columns rather than rows.

Links:

• MSDN for Common Table Expression
• MSDN for PIVOT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

DECLARE @RowsToSplit TABLE (
      Id INT IDENTITY(1, 1) NOT NULL
      -- how many rows are desired after the split?
      , MaxRows INT NOT NULL
      -- what value to split across the rows?
      , QuantityToSplit INT NOT NULL
);
 
INSERT INTO @RowsToSplit VALUES ( 20, 100 );
INSERT INTO @RowsToSplit VALUES ( 5, 15 );
 
-- Recursive Common Table Expression (CTE)
WITH SplitRowsCTE ( Id, MaxRows, QuantityToSplit, Quantity, RowIndex )
AS (
      -- "Anchor" Set: The root set of the recursive function.
      SELECT 
            Id -- row tracking
            , MaxRows -- remember the goal # of rows
            , QuantityToSplit -- remember the goal value to split
            -- split the first row's quantity
            , QuantityToSplit / MaxRows AS Quantity
            -- start at index 1
            , 1 AS RowIndex
      FROM @RowsToSplit -- anchor source
      -- Recursive Set: Iterate from the source until all rows have been generated.
      UNION ALL
      SELECT
            Id -- row tracking
            , MaxRows -- remember the goal # of rows
            , QuantityToSplit -- remember the goal value to split
            -- split the row's quantity
            , QuantityToSplit / MaxRows AS Quantity
            -- increment the row index
            , RowIndex + 1 AS RowIndex
      FROM SplitRowsCTE
      -- stop just before we get to the max rows (root has already been created)
      WHERE RowIndex < MaxRows
)
 
-- Call the recursive CTE function
-- Eliminate the source information from the result set
SELECT Id, RowIndex, Quantity
FROM SplitRowsCTE
ORDER BY Id, RowIndex;

• How to Customize/Change Windows 7 Logon Background Wallpaper Screen
  (tags: win7 windows7 windows os customize)

• SQL to Select a random row from a database table
  (tags: sql random database programming reference db tips)

• Built-in WPF IValueConverters - Stack Overflow
  (tags: wpf converter)
• diff/merge configuration in Team Foundation - common Command and Argument values - James Manning's blog - Site Home - MSDN Blogs
  (tags: TFS diff merge compare tools)
• Windows 7 – VHD Boot – Setup Guideline - knom's developer corner - Site Home - MSDN Blogs
  (tags: windows7 vhd microsoft windows virtualization)

Use the “Video Snack Downloader” to download all of the Microsoft Learning “Snack” Videos to your local machine! Video Snack Downloader is a small, open source application for discovering the videos available on the Microsoft Learning “Snacks” website and downloading them to your local machine for offline viewing.

Many people still struggle with effective branching and merging in TFS; either in understanding the concepts or applying them. I have attempted to address this through a demo using TFS 2010 and Microsoft’s TFS Guidance from the Patterns & Practices and “ALM Rangers” teams.

Microsoft also released the ability to customize the TFS process template guidance on MSDN. See the following resources:

• Customize the VS SCRUM Guidance
• Customize the MSF Agile Guidance
• Customize the MSF CMMI Guidance

For a demo of how or even why you might do this, see Allen’s post on customizing the process guidance using WebMatrix.

• Manning Publishing’s TFS 2008 in Action
• The “Don’t Break My Build” t-shirt is compliments of telerik.com

MS Tech·Ed North Ameria has been running this week in New Orleans…

• Watch the Tech·Ed videos online - The keynotes (1 and 2) have great insight into Microsoft’s vision; how their 7-year old promise of “Dynamic IT” is just beginning to be realized this past year and even more so with their Azure cloud computing platform, System Center 2010, and Application Lifecycle Management support with tooling like System Center 2010 with Opalis visualization and workflow management, Team Foundation Server 2010 with Lab Management including virtual labs, Test Manager 2010 with record/playback test automation and reusable manual test step definitions, SQL Server 2008 R2 with Data-Tier Application (DAC) integration, and Visual Studio 2010 with support for Azure platform solutions, Data Tier Applications, and IntelliTrace debugging technology
• Windows Azure updates:
  • Support for .NET 4
  • Windows Azure Platform Training Kit (June Update)
  • Windows Azure Tools for Visual Studio 1.2 (June Update)
• Expression Studio 4 was released - This is a tremendous update to Expression Studio including improved application prototyping through Blend’s SketchFlow, designer support for path layouts for lists (layout items in a list along any path), enhanced Live Media streaming capabilities with Encoder improvements, robust XAML states and transitions for WPF and Silverlight through Blend, .NET 4 and Silverlight 4 support with designer support, and many, many more improvements.
• I have confirmed that the Windows Phone 7 CTP still works with the Blend 4 release (this was a pleasant surprise after MS released VS2010 with Silverlight 4 beta support in VS Beta 2 and then did not support it in VS RTM until the SL4 RTM - Of course, this was only a few months of waiting…)

You either love or hate “Local Service”, “Network Service”, and “Local System”. Have you ever avoided setting up individual Service Accounts for SQL Server, IIS, SharePoint, or other enterprise applications and simply defaulted these built-in accounts? Perhaps you didn’t know that it’s “best practice” to use domain accounts for these services. Or, perhaps you knew and wanted to avoid the maintenance overhead of updating passwords constantly per company policy.

In my experience, what you use will vary based on the hat you like to wear most:

• IT Pro – Knows they should setup separate service accounts, but wants to avoid maintenance. So, they use the built-in accounts unless someone complains enough. Some may have even gotten smart about it and written scripts to automate account password updates every month.
• Developer – Ignore the opportunity to setup separate accounts, find the install process complicated or burdensome so they skip this step, or they don’t know better, so they setup the service to use one of the built-in accounts
• Security – Wants to use separate service accounts as a policy, but can’t seem to keep the IT Pro or Developer compliant!

In addition to this basic problem, separate environments for development, testing, staging, and production sometimes require different account credentials! All of this has been exacerbated in recent years by investments in clustering and virtualization. More servers with more services and more accounts to maintain…

In response to this problem, Microsoft released new features in Windows Server 2008 R2 and Windows 7 called Managed Service Accounts and Virtual Accounts.

Finally! Improve service isolation, increase account security, eliminate administration and support growth of your infrastructure! Read “What’s New in Service Accounts in Windows Server 2008 and Windows 7”.

Most of us use Forrester, Gartner, and any number of other aggregated information hubs for reducing the communication clutter to make effective decisions. Forrester recently revamped their blogging strategy and platform to improve information access and updates. Inevitably, this social aspect of Forrester’s research will provide insight that wasn’t possible through the standard email alerts, newsletter subscriptions, and typical website news and press releases. Subscribe and/or begin your conversation today!