Welcome to TechNet Blogs Sign in | Join | Help

New Career Campaign From Microsoft Learning

clip_image001

UPDATED 27 October 2009 with the correct expiration date for the E-Learning Collections discount (thanks, Lain!).

Whether you are currently unemployed, looking for a promotion, or trying to become indispensible in your existing role, Microsoft has launched a new Career Campaign to provide learning paths and guidance to help you get trained for some of the top IT job roles in the industry. These learning paths focus on some of the most popular applications, such as Windows, SQL Server, Windows Server and Exchange. Click Here to visit Microsoft’s Career Campaign portal.

To help accelerate your career plan, Microsoft is also providing discounts and special offers for you to get started:

  • Up to 25% off select Certification Exams until December 31, 2009 (while supplies last)
  • Classroom Training Career Packages for Windows 7, Windows Server 2008 R2 and SQL Server 2008 – packages include training, a Certification exam, and free software
  • 25% off select E-Learning Collections until June 30, 2010

Click Here to visit the Career Offer page.

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites

Pond’s Laws Home Page Now Live

Thanks to yet another kick-in-the-pants from Jimmy May (did he play soccer as a youth?) the Pond’s Laws Home Page is now live.  Jimmy wanted to find all of Pond’s Laws in one place, and now he (and you) can.

There’s also a link to the new page in the A Bit More From Me section to your left (if you’re reading on the blog site).

Keep those suggestions coming.. even if your name isn’t Jimmy May!

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites

Windows 7 Now Available

Windows 7 logo

You might have heard about this somewhere else..  but Windows 7 was launched today.

I recently got an upgraded laptop from my employer, upon which I installed Windows 7.  Both the machine and the software are quite remarkable.

First, the machine, so you can see how sweetly I’m riding these days, is an HP EliteBook 8530w (2.8gHz Intel® Core™ 2 Duo Processor T9600, 8gB RAM, 250gB hard disk, and all the extras which are typical these days).

What’s really incredible is how the lighter footprint of Win7 vis a vis Vista impacts my ability to mulitask on the new machine.  On my old Vista box (2gB RAM) I could run an instance of SQL, but the productivity of the other applications on the PC was immediately and dramatically impacted.

Well, for my SQLRAP unit testing work, I’ve installed four instances on my new machine (SQL2005_32bit, SQL2005_64bit, SQL2008_32bit, SQL2008_64bit).

Guess what?  I can start all four instances, do significant SQLRAP work in two of them (via two instantiations of SSMS), and I still have enough headroom to run Office 2010.

Much of this is the extra 6gB, sure, but don’t discount the fact that the new Win7 OS is far less resource-intensive (memory, in particular) than its predecessors.

So far, after a week and a half, I have no complaints, and my productivity has definitely increased.

Windows 7 is definitely worth a look in my book.  What has your experience with it been?

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites

Pond’s Thirteenth Law: Change Won’t Be Cheaper Tomorrow

Herewith, a discussion of the emergence of Pond’s Thirteenth Law (if you need to catch up, here are Laws One through Ten, Eleven, and Twelve, as well as three ruminations on the implications of Pond’s Seventh Law)..

One of the great things about my job is that I get to spend an embarrassing amount of time having conversations with really smart people.  The next couple of posts I’ve got planned are the product of conversations I’ve either had or eavesdropped on (via email).

My primary project these days is the SIPA system I’ve discussed in this space several times before.  My colleague Gerardo Saca van der Meden (who is going to be a superstar in this business, mark my words) and I spent the weekend thrashing through our complementary-but-not-entirely-aligned views of some design changes which we’ll be including in our next version.

At one point (when he erroneously thought he might lose our thoroughly professional “argument”), Saca wondered if we couldn’t possibly table the changes I was suggesting until after our pending release, in favor of spending our remaining cycles for this release building new features.

This is an interesting balancing act to contemplate, and my purpose here is not to suggest that there’s only one right answer to this question.  Rather, when making a decision like this – when to embark on a redesign, refactoring, etc. – it’s important to remember Pond’s brand-new Thirteenth Law:

Whatever change you’re contemplating, it won’t be any cheaper tomorrow

My primary concern with Saca’s suggestion was that it increased the scope-of-work of our redesign effort – the new features we build between now and then would have to be rebuilt in light of the redesign.  This is neither good nor bad, it just is, and it needs to be given its due weight in the decision-making process.  Is getting the features to market quickly worth the cost of the rework?

As luck (and Saca’s mad skillz) would have it, no redesign is necessary.  We can effectively meet our requirements with the design we had, a statement we can make with much higher confidence than we could before the exercise.

If we had needed to make the change, I would’ve put in overtime to make deadline.  That was my offer to Saca to ease his pain-of-entry into contemplating the possibility of a major mid-course change.  If you’re the techie in this conversation rather then the dev lead, remember that your lead is going to have to explain what looks like a mistake to the rest of the world.  If you’re going to put him through that, help him out – give him an above-and-beyond commitment that he can use to help balance the equation.

It’s a rare dev lead who will tolerate (never mind participate enthusiastically in) this sort of mid-course soul-searching (and if you ever do undertake such an exercise, I suggest you set a tight deadline on it and stick to it, which we did).  When a senior team member is questioning a design decision, the dev lead’s reaction can color perceptions and team relations for a long time, either for the good or for the bad.

So, in such situations, be open-minded, be flexible, and above all be efficient (this is a situation that calls for a quick decision; you want to make sure you’re performing the most cogent possible analysis).  If you can grit your teeth and get through the interruption, you’ll end up with more confidence in the course you ultimately select, as well as team members who know their opinions are valued, even when they’re not valid.

With apologies to Don Henley, senior techies who’ve been burned by bad leadership in the past will walk on their tongues over broken glass to get next to a dev lead like that.

Do you have any stories about good dev leads?  Or even bad ones?  Share them here..

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites

Beta 2 Release for Visual Studio 2010 and .NET Framework 4 Available for Download

Exciting news for the developer community: yesterday Microsoft released the second beta for Visual Studio 2010 and .NET Framework 4.  Click here for the download page.

If you need it, go get it!

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites

Mark Your Calendar: Windows 7 Readiness Webcast on October 6

clip_image002

clip_image002[7]clip_image002The following description of a pending webcast arrived in my inbox earlier this week; I thought it might be of interest to some of you as you prepare for the launch of Windows 7:

Is Your Organization Ready for Windows 7?

October 6, 2009

12:00 PM Eastern / 9:00 a.m. Pacific (60 minutes)

Event Details

Featured Speaker: Independent Analyst Firm Forrester’s Benjamin Gray

Forrester's Benjamin Gray discusses his April 2009 report "Get Ready For Windows 7" and whether or not your organization should deploy Windows 7 now or test applications and hardware against Windows Vista for greater compatibility with Windows 7.  He'll also share:

· What he believes to be the top five Windows 7 features

· Industry benchmarks on what other North American and European enterprises are planning

· His recommendations for customers running Windows XP or Windows Vista

Save the Date for the Live Webcast on October 6, 2009. Prepare for the future by learning how to optimize your desktop infrastructure with Windows 7 while maximizing your existing technology investments.  Attend this webcast to see how Windows 7 can benefit your organization with powerful technologies that help you secure, officially manage, and lower the cost of your organization's desktop infrastructure.

About Benjamin Gray:

Benjamin serves IT Infrastructure & Operations professionals. He is a leading expert on business-class PCs and desktop operating systems and also researches business-class mobile devices, mobile operating systems, and mobile device management solutions. Benjamin helps

Forrester clients develop and improve their strategy around client hardware and client operating systems.  Benjamin's research and analysis have been widely cited in the press, including business media outlets such as The Associated Press, Bloomberg, The New York Times, USA Today, and The Wall Street Journal and industry media outlets such as Computerworld, eWeek, InformationWeek, InfoWorld, and NetworkWorld.

Check out more upcoming webcasts.

If this webcast is pertinent to you, I hope you’ll check it out!

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites

Posted by Ward Pond | 0 Comments

A Gift From Jens and Jimmy

Two Microsoft folks frequently featured in this space, Microsoft Certified Masters Jimmy May and Jens Suessmeyer, have joined forces to bring to the world SQLIOSimParser, a tool to parse and interpret the XML output of the Bob Dorr’s SQLIOSim testing tool.

Jimmy says Jens did most of the heavy lifting, but most of the heavy reading is found in Jimmy’s post on the tool.

If you’ve ever plodded through a SQLIOSim output file, you’ll be very excited to hear about and use this tool.  Thanks to Jens and Jimmy for sharing the results of their shared brainstorm with the community.

If you need it, go get it!

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites

SQL Server 2008 R2 August CTP Available for Download

If you’re interested in the next release of SQL Server, the BI-focused SQL Server 2008 R2, you’ll be pleased to know that the August CTP is available for download here for MSDN and TechNet members.

Here’s the skinny from the download page:

SQL Server 2008 R2 expands on the value delivered in SQL Server 2008 to help your organization scale with confidence and improve IT and developer efficiency with new and enhanced tools for application and multi-server management, master data services and complex event processing. The new Self Service BI capabilities will empower end users to access, integrate, analyze and share information using business intelligence tools they already know – Microsoft Office.

The August Customer Technology Preview (CTP) includes Application and Multi-server Management which will help organizations manage database environments efficiently at scale with increased visibility and control across the application lifecycle. This CTP also includes Microsoft SQL Server StreamInsight, Microsoft’s new complex event processing technology to help businesses derive better insights by correlating event streams from multiple sources with near-zero latency.

The Fall 2009 CTP will include the remaining SQL Server 2008 R2 features including Master Data Services and Self Service Business Intelligence capabilities delivered in SQL Server Reporting Services and Project “Gemini”.

If you’re into it, go get it!

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites

Pond’s Twelfth Law: In Your Pursuit of Five Nines, Don’t Practice in Front of the CIO

After a long hiatus, Pond’s Laws of System Design (or, How to Be a Competent Professional in an Irrational World) returns.  Thanks to Jimmy May for a kick-in-the-pants on this topic several months ago; I hope he’ll understand why I just had to wait until right now to publish this post (the clue lies in a very careful examination of the datetime stamp on this post vis a vis our topic)..

I know you’re asking yourself..  Why would I practice looking for an impossible poker hand in front of my (great-(great-(great-)))grandboss?

Perhaps I should explain further.  This has nothing to do with poker, but everything to do with how the reliability of your service is perceived at the highest levels of your corporate food chain.

“Five nines” is high-availability shorthand for 99.999% system availability, a holy grail of design, development, configuration, and operations.  Five nines implies less than five and a half minutes of downtime a year, and you only get that kind of resiliency if both the physical and logical components of the solution are designed for it, from the ground up, and if your processes and procedures and your execution of them are all sufficiently mature to support such an ambitious environment.

This is no small thing.  If you can provide five nines availability on a mission-critical system, in many cases you can build a fine career for yourself.  Even if the hardware side of five nines is too expensive in your environment, the consumers of your services will benefit if your designs and processes are as comprehensive as you can make them.  And you’ll be one giant leap closer to being “a competent professional in an irrational world,” which is, after all, the goal of this series.

So how do you get to five nines?  You design for contingencies and then you test your designs.  In this respect I instinctively recall the Boy Scout motto from my youth: “Be prepared.”  This simple admonition has served me very well over the years.

It’s not tough to get the design part just about right.  There’s a great deal of literature pertinent to designing software and hardware for five nines-level availability.  My blogroll (at the left; scroll up/down to the last populated area) offers a host of places to start.

Once you’re prepared, though, the datacenter team needs practice!  Take the Kim Tripp maxim, “if you haven’t tested the restore, you don’t know you have a backup,” and apply it to every component in your stack.  Run your restores, run your cutovers, test connectivity to your failover sites..  CHECK EVERYTHING.  In sequence and in the same session, if you can.  There’s some good advice in the comments on the post that inspired Jimmy’s pants-kick.

This is where many shops fail, and when disaster strikes it’s much worse than it needs to be, because the team executing the fix is simultaneously executing a learning curve.  However, if you schedule exercises quarterly and rotate half your staff through the process each time, then they won’t be trying to get comfortable with their approach when things hit the fan.  These insights lead directly to the establishment of Pond’s Twelfth Law:

Don’t practice in front of the CIO.  A professional prepares ahead of time.

When I worked at the oil company, we had a weekly maintenance window; my partner and I switched off every week.  We each were exposed to the entire spectrum of the maintenance and upgrade process for our entire product stack.  One particular Sunday morning (of course it was my Sunday morning!), a database software upgrade went south.  Because I had been through upgrades and restarts many times, when it became necessary I was very confident making the “Houston, we have a problem” calls to both our management and the database vendor’s critical situation team.

I worked a forty-hour day troubleshooting that issue (yes, I was much younger then).  It ultimately evolved that we’d uncovered a hardware-specific bug in the database upgrade of which the vendor was unaware.

By Tuesday night we had a hotfix for our platform from the vendor, and our system was back up.  On Thursday, we received a letter of apology and thanks from the vendor’s Vice President of Development, saying among other things that “it was very helpful to have someone of Ward’s skill and qualifications working your side of the problem.”

I don’t cite these events to entice your compliments on my dedication and brilliance.  It’s important to note that I wasn’t unduly brilliant in this instance; I can very comfortably say, “I was just following orders.”

Our shop had practiced disaster recovery, both on-site and off-site, many times, and as a team we had been through upwards of six database upgrades without incident.  Our processes and their regular repetition had prepared me to the point where, when things went wrong that Sunday morning, I was instinctively able to respond quickly and appropriately.

Our team practiced in front of each other.  When it was time to go on-stage in front of our CIO, I was ready.  I had rehearsed, enough that I hit pretty much every note in my “performance.”

Jimmy says, “the time to learn to put out a fire isn’t when your home is burning down.”  Different metaphor, same lesson.  The oil company’s corporate headquarters was in a Los Angeles skyscraper; back in the ‘80s and ‘90s, I was part of the building’s Emergency Response Team for earthquakes and such.  We practiced four times a year, and the LAFD told us we were pretty good for a bunch of desk jockeys.  It was a point of pride for everyone on the team; none of us wanted to be unprepared if, heaven forbid, we were ever called upon to perform.  We each had a clearly defined responsibility, and we regularly practiced our roles.

It should be the same with your datacenter’s “emergency response team.”  They need to practice for things that you dearly wish won’t happen and take pride in their ability to address them.

If you don’t expend this effort, instead of showing your CIO an impossible poker hand, you’ll be on-stage singing on front of her without rehearsal.

Don’t be that guy or gal.  Don’t practice in front of the CIO.

Have you ever practiced in front of the CIO?  Have you ever been well-prepared and happy about it?  Have you figured out that datetime stamp/post topic puzzler from the first paragraph?  Please leave a comment and share your thoughts..

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites

Database Programming: A New String Concatenation Contender for SQL Server 2008

UPDATED 11:11p 7 September 2009: Based on my latest exchange with Adam (see the comments for this post), compound operators should be avoided for string concatenation due to the ORDER BY bug discused in KB 287515.  The XML variant below is the preferred approach for string concatenation, but the compound operator remains a viable alternative in other scenarios.

UPDATED 7 September 2009: See Adam Machanic's comments on this post for a link to a discussion of a SQL Server 2008 ORDER BY bug (KB 287515) which could influence the sequencing of your results under certain scenarios.  The XML variant might be better after all (the ORDER BY issue is something of an edge case (functions applied to ORDER BY columns) and should be repaired eventually, but the current risk is non-trivial); compound operators are still of use in scenarios where sequencing of results is not an issue (aggregates, etc.).

Back in March of last year, we had a discussion which concluded here (and was briefly revisited here) regarding string concatenation techniques.  I’ve uncovered a new SQL Server 2008-based contender which I’d like to share.

This approach is based on the new-in-SQL-Server-2008 += compound operator, which allows all order of iterative processing (copied from linked BOL article):

Operator Link to more information Action

+=

+= (Add EQUALS) (Transact-SQL)

Adds some amount to the original value and sets the original value to the result.

-=

-= (Subtract EQUALS) (Transact-SQL)

Subtracts some amount from the original value and sets the original value to the result.

*=

*= (Multiply EQUALS) (Transact-SQL)

Multiplies by an amount and sets the original value to the result.

/=

/= (Divide EQUALS) (Transact-SQL)

Divides by an amount and sets the original value to the result.

%=

%= (Modulo EQUALS) (Transact-SQL)

Divides by an amount and sets the original value to the modulo.

&=

&= (Bitwise AND EQUALS) (Transact-SQL)

Performs a bitwise AND and sets the original value to the result.

^=

^= (Bitwise Exclusive OR EQUALS) (Transact-SQL)

Performs a bitwise exclusive OR and sets the original value to the result.

|=

|= (Bitwise OR EQUALS) (Transact-SQL)

Performs a bitwise OR and sets the original value to the result.

Since the use of compound operators restricts us to SQL Server 2008, the snippet below exploits the also-new-in-SQL-Server-2008 row constructor capability (scroll to Example B in the latest BOL examples for the INSERT statement).  In my testing, nine distinct INSERT statements each consumed 6% of the queries resources; the two statements below were each 9% of the total query cost.  This is a pretty impressive economy for such a small bed of data, so those of you writing long seed scripts should definitely take this into account!

Two uses of row constructors as well as the use of the compound operator are highlighted in the code snippet below (the previous XML-based contender is also included for reference):


--  lay the groundwork
 
DECLARE @Results NVARCHAR(4000)
 
--      drop and create the tables
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Parent')
    DROP TABLE Parent
 
CREATE TABLE Parent
(
    ParentID        INT 
   ,ParentString    VARCHAR(100)
)
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Child')
    DROP TABLE Child
 
CREATE TABLE Child
(
    ChildID     INT 
   ,ParentID    INT
)
 
--      populate the tables
--      since this is SQL Server 2008 only, we can use row constructors
INSERT Parent VALUES
    (1, 'Parent 1 String'),
    (2, 'Parent 2 String'),
    (3, 'Parent 3 String')

INSERT Child VALUES
    (1, 1),
    (2, 1),
    (2, 2),
    (2, 3),
    (3, 1),
    (3, 3)
 
--      show the data
 
SELECT  Child.ChildID, ISNULL(Parent.ParentString, '')
FROM    Child
INNER   JOIN Parent
ON      Child.ParentID = Parent.ParentID
ORDER BY
        Child.ChildID, Child.ParentID
 
-- Concatenate a string via the new compound operator
-- Pivot Parent values into 1 column for 1 base row
 
SET @Results = N''
 
SELECT  @Results += ',' + Parent.ParentString
FROM    Child
JOIN    Parent
ON      Child.ParentID = Parent.ParentID
WHERE   Child.ChildID = 2  -- must specify 1 base row.  could be a correlated subquery
ORDER BY
        Child.ParentID
 
--  Two ways to display the result without the leading comma
SELECT RIGHT(@Results,LEN(@Results)-1)
SELECT STUFF(@Results,1,1,'')
 
-- Concatenate via XML
-- Pivot Parent values into 1 column for 1 base row
SELECT  STUFF(( SELECT [text()]= ',' + ISNULL(Parent.ParentString, '') + ''
FROM    Child
JOIN    Parent
ON      Child.ParentID = Parent.ParentID
WHERE   Child.ChildID = 2  -- must specify 1 base row.  could be a correlated subquery
ORDER BY
        Child.ParentID
FOR XML PATH('')), 1,1, '') AS Parent_CSV

The query plans for each query that the XML-based call includes a statistically insignificant (at this volume) UDX call for the XML instantiation:

Query Plan For Compound Operator Variant

query plan for compound operator variant

 

Query Plan For XML Variant

query plan for XML variant

For this reason, I’d expect the compound operator-based query to scale better with a large volume of data.  Once one becomes familiar with compound operators, which are commonplace in nGL languages, this construction strikes me as both more maintainable and more elegant.

On a related note, I continue to find the subtle programmability enhancements to SQL Server 2008 to be very cool.

Of course, these are my biases.  What do you think?

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites

Database Programming: Did You Know IN Can Do This?

Courtesy of Jens Suessmeyer comes this little gem purloined from an internal discussion; cut-and-paste this into SQL Server Management Studio and see if you can answer the questions in the comments before you execute the script:

--  create and populate a table variable
DECLARE @Demonstration TABLE (
    ID INT IDENTITY(1,1),
    Col1 INT,
    Col2 INT,
    Col3 INT
    )

 

INSERT @Demonstration (Col1,Col2,Col3) VALUES (1, 2, 100)
INSERT @Demonstration (Col1,Col2,Col3) VALUES (2, 100, 1)
INSERT @Demonstration (Col1,Col2,Col3) VALUES (100, 1, 2)

 

--  this one you can probably guess..
SELECT  ID
FROM    @Demonstration
WHERE   Col1 IN (1,2)

 

--  but what's going to happen here?
SELECT  ID
FROM    @Demonstration
WHERE   1 IN (Col1,Col2)

I knew IN could examine a column for a list of values; I didn’t know that it could examine a list of columns for a value.  For the record, here’s the result set:

ID
1
2

ID
1
3

This works in every version of SQL Server as far back as I can check (SQL Server 2000 SP4).  Thanks, Jens, for debunking a case of one-dimensional thinking on my part!

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

Download a New Browser and Help Feed the Hungry

click to open the BrowserForTheBetter website in a new window.

I sure wish I’d known about this sooner..

If you haven’t yet downloaded Internet Explorer 8, here’s a great opportunity to acquire some fantastic software and help out some folks less fortunate than we are in the bargain.

If you download Internet Explorer 8 from http://www.browserforthebetter.com, Microsoft will donate eight meals to the Feeding America campaign.  Furthermore, if you’re replacing IE6, Microsoft will double its donation to sixteen mealsThis is all part of Microsoft’s support of Hunger Action Month.

So, if you’ve been putting off your evaluation of IE8, there’s really no further reason to do so; if you can find time to download the browser this month, you’ll have a great browser on your PC, and you’ll have helped some folks who need it in the bargain.

Sure sounds like a win-win situation to me.  Do I work for a great company, or what?

Go get it, even if you don’t need it!

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

Adam Machanic Likes MERGE, Too

download CU1 for SQL Server 2008 SP1Hot on the heels of yesterday’s MERGE discussion comes a complementary (and far more detailed) take from Adam Machanic.  It’s a nifty technical discussion, under the banner of an outstanding Dr. Strangelove pun.

To see some of the true power of MERGE (and to share Adam’s joy in releasing his MERGE-related stress), click on over!  As with all of Adam’s technical discussions, you’ll be glad you did.

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

 

Database Programming: Why I Like MERGE

download CU1 for SQL Server 2008 SP1In the wake of last month’s post on my new development efforts in SQL Server 2008, several of you have asked me to go into further detail.  This post is my first effort to answer those questions; right now, we’ll focus on these two, which relate to this statement:

By using a CTE with a MERGE and a TRY-CATCH block, in a single statement I can:

  • shred the XML document containing user input;
  • update existing records;
  • modify existing records;
  • delete records (either logically or physically);
  • create a driver table for child processes containing PKs of inserted records (isolating the PKs of just-inserted records was a huge challenge until the OUTPUT clause was introduced in SQL Server 2005).

Before I present the code, I can partially address Adam’s performance question: performance does appear to suffer some under load.  My performance tuning is incomplete, so it’s hard for me to say at the moment how much of this about MERGE and how much of this about the state of my progress in the tuning analysis.  Once the tuning is complete, our test team will be running an analysis to determine exactly where our code stops scaling.

We’re using the same code for user interactions that we are for our bulk load, which is pretty bold on our part, but also testimonial to the fundamental flexibility and power of the syntax.  Our user interactions are running great; our data loader is currently running slower than we’d like but I’ve proposed an architectural change which should resolve that issue.  I’ll of course report back.

Now, for Andrew Bauer and any other interested parties, here’s the code.  The procedure this code is adapted from is one in a hierarchical series.  Each procedure error-checks the data pertinent to its level in the hierarchy before processing the MERGE statement; the TRY-CATCH block in each procedure is designed to cumulative populate an XML document which is passed among the levels as an OUTPUT parameter.

I’ll present the CATCH block first, because it’s shorter:

BEGIN CATCH       
      
    IF 0 = @CalledInternally
    BEGIN
        ROLLBACK TRANSACTION Data_Maintenance
    END
      
    -- Add error to ErrorXml
    SET     @ParamResult = 0  
    SELECT  
             @ErrorNumber = ERROR_NUMBER()
            ,@ErrorMessage = ERROR_MESSAGE()
            ,@ErrorLine = ERROR_LINE()
            ,@ErrorModule= ERROR_PROCEDURE()
             
    SET @ParamErrorXml.modify  
    (  
        'insert   
            <Error   
                Code        = "{sql:variable("@ErrorNumber")}"  
                Description = "{sql:variable("@ErrorMessage")  }"  
                LineNumber  = "{sql:variable("@ErrorLine")  }" 
                ErrorModule = "{sql:variable("@ErrorModule")  }" 
            />  
        as last into (/Errors)[1]'  
    )
     
END CATCH

Rather than the SET statement shown above, the production code calls a stored procedure which does a little more business to ensure that the XML is populated and properly formatted, but this SET statement, which is contained in the procedure, gets to the heart of the XML population.

The heart of this post is the MERGE statement, and it’s taken me long enough to get to it.  I’ve tweaked it a bit to obfuscate the source, but the benefit to that is that I believe it to be self-documenting.  Please leave a comment if you disagree.

Here’s the code..

--  Merge @IncomingXML into the BaseTable table

;WITH [IncomingXML] (
     [PrimaryKey_Provided]
    ,[PrimaryKey_Derived]
    ,[ParentKeysInXML]
    ,[Version]
    ,[NextVersion]
    ,[IncomingGuid]
    ,[OwnerID]
    ,[CheckedOut]
    ,[CheckedOutByID]
    ,[LanguageLocaleID]
    ,[ChildXML]
    ,[StatusID]
) AS (   
    SELECT
         [xml].[PrimaryKey] AS PrimaryKey_Supplied
        ,[s].[PrimaryKey] AS [PrimaryKey_Derived]
        ,[xml].[ParentKeysInXML]
        ,[xml].[Version]
        ,(
            SELECT ISNULL(MAX([Version]),0)+1 AS [NextVersion] 
            FROM   [dbo].[BaseTable]
            WHERE  [Guid] = [xml].[IncomingGuid]
        )
        ,[xml].[IncomingGuid]
        ,[xml].[CheckedOut]
        ,[xml].[CheckedOutByID]
        ,[xml].[ExampleTypeId]
        ,[xml].[LanguageLocaleID]
        ,[xml].[ChildXML]
        ,[xml].[StatusID]
    FROM (   
        SELECT  
             ref.value('@ID','int')                   AS [PrimaryKey]
            ,ref.query('Relationship')                AS [ParentKeysInXML]
            ,ref.value('@Version','nvarchar(10)')     AS [Version]
            ,ref.value('@Guid','uniqueidentifier')    AS [IncomingGuid]
            ,ref.value('@OwnerID','int')              AS [OwnerID]
            ,ref.value('@CheckedOut','bit')           AS [CheckedOut]
            ,ref.value('@CheckOutByID','int')         AS [CheckedOutByID]
            ,ref.value('@TypeID','int')               AS [ExampleTypeId]
            ,ref.value('@LanguageLocaleID','int')     AS [LanguageLocaleID]
            ,ref.query('ChildXML')                    AS [ChildXML]
            ,ref.value('@StatusID','int')             AS [StatusID]       
        FROM    @IncomingXML.nodes('/root[1]/node') as node(ref)
    ) AS [xml]
    LEFT OUTER JOIN [dbo].[BaseTable] [s]
          ON     [xml].[PrimaryKey] = [s].[PrimaryKey]
    )
MERGE
    INTO    [dbo].[BaseTable] [table]
    USING   [IncomingXML] [cte]
    ON      [table].[PrimaryKey] = ISNULL([cte].[PrimaryKey_Derived], [cte].[PrimaryKey_Provided])

 

-- delete a record
-- note that we could also employ an UPDATE statement for a logical delete here; DELETE used for brevity
-- logical delete might also imply changing the following WHEN statement to something like
--  WHEN MATCHED AND [table].[Guid] = [cte].[IncomingGuid] AND [cte].[StatusID] =
--      (SELECT [StatusID] FROM [dbo].[Status] WHERE [StatusDescription] = 'delete')

 

    WHEN NOT MATCHED BY SOURCE
        THEN DELETE        

 

 -- update existing record:
    WHEN MATCHED AND ([table].[Guid] = [cte].[IncomingGuid] or [cte].[IncomingGuid] IS NULL)
        THEN UPDATE SET
             [Version]           = ISNULL([cte].[Version],[table].[Version])
            ,[CheckedOut]        = ISNULL([cte].[CheckedOut],[table].[CheckedOut])
            ,[CheckedOutByID]    = CASE
                                        WHEN ISNULL([cte].[CheckedOut],[table].[CheckedOut]) = 1
                                          THEN @UserNameID
                                        ELSE NULL
                                    END
            ,[Guid]              = ISNULL([cte].[IncomingGuid],[table].[Guid])
            ,[ExampleTypeId]     = ISNULL([cte].[ExampleTypeId],[table].[ExampleTypeId])
            ,[LanguageLocaleID]  = ISNULL([cte].[LanguageLocaleID],[table].[LanguageLocaleID])
            ,[StatusID]          = ISNULL([cte].[StatusID],[table].[StatusID])
            ,[LastModifiedDate]  = @Now
            ,[LastModifiedByID]  = @UserNameID

 

 -- insert new record:
    WHEN NOT MATCHED BY TARGET AND [cte].[IncomingGuid] IS NULL
        THEN INSERT (
             [Version]
            ,[CheckedOut]
            ,[CheckedOutByID]
            ,[Guid]
            ,[ExampleTypeId]
            ,[LanguageLocaleID]
            ,[LastModifiedDate]
            ,[LastModifiedByID]
            ,[CreatedDate]
            ,[CreatedByID]
            ,[StatusID]
        ) VALUES (
             ISNULL([cte].[Version],[cte].[NextVersion])
            ,ISNULL([cte].[CheckedOut],0)
            ,CASE
                  WHEN [cte].[CheckedOut] = 1 THEN [cte].[CheckedOutByID]
                  ELSE NULL
            END
            ,NEWID()
            ,[cte].[ExampleTypeId]
            ,ISNULL([cte].[LanguageLocaleID],@DefaultLanguageLocale)
            ,@Now
            ,@UserNameID
            ,@Now
            ,@UserNameID
            ,ISNULL([cte].[StatusID],@DefaultStatus)
        )

 

-- populate a previously built table for the "child" procedure to use:
      OUTPUT
             ISNULL([inserted].[PrimaryKey],[deleted].[PrimaryKey])
            ,[cte].[ParentKeysInXML]
            ,ISNULL([inserted].[CheckedOut],[deleted].[CheckedOut])
            ,ISNULL([inserted].[CheckedOutByID],[deleted].[CheckedOutByID])
            ,ISNULL([inserted].[LanguageLocaleID],[deleted].[LanguageLocaleID])
            ,ISNULL([inserted].[StatusID],[deleted].[StatusID])
            ,[cte].[ChildXML]
            ,CASE
                  WHEN [deleted].[PrimaryKey] IS NULL AND [inserted].[PrimaryKey] IS NOT NULL THEN 'I'
                  WHEN [deleted].[PrimaryKey] IS NOT NULL AND [inserted].[PrimaryKey] IS NULL THEN 'D'
                  ELSE 'U'
             END
    INTO #MapTableForChildSproc (
            [PrimaryKey]
            ,[ParentKeysInXML]
            ,[CheckedOut]
            ,[CheckedOutByID]
            ,[LanguageLocaleID]
            ,[StatusID]
            ,[ChildXML]
            ,[ActionInDatabase]
        );

Thanks for making it this far..  please let me know if you have any questions!

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

At the Onset of My Fiftieth Year

Those of you who follow me on Twitter or Facebook are aware that I’ve spent the last week and a half dealing with my latest round of health issues.  I returned home yesterday afternoon following a brief hospitalization for diagnosis and removal of a non-functional gall bladder.

Today, I celebrate the beginning of my fiftieth year on the planet with new reasons for optimism.  Unlike previous procedures when I was still feeling ill afterwards, I was drinking apple juice five minutes after coming out of the anesthetic on Monday.  I have typical post-laparoscopic surgery discomfort, but all systems are basically “go” here, and I’ve enjoyed mixing my first productive work day in over a week along with today’s birthday celebrations.

I greatly appreciate the notes of concern I’ve received from many of you, and I don’t take lightly the blessings of healing energy which my family and friends have sent my way.  As my energy level increases, I’ll respond to the comments left on last week’s SQL 2K8 post.

For now, though, forgive me if I kick back and enjoy the rest of my (and Bob Beauchemin’s) birthday..

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

Posted by Ward Pond | 2 Comments
Filed under: ,
More Posts Next page »
 
Page view tracker