Passing Lists in SQL Server 2005

DECLARE @productIds xml

SET @productIds ='3615'

SELECT
ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)

Hierarchical vs Linear Workflow

Several of the applications that we've built or are currently working on in the OSS group involve the processing of workflow in some form. If you peruse the Web you'll find there are a variety of types such as "activity-based" or "content-driven". Microsoft Windows Workflow Foundation supports two types: "Sequential" and "State Machine" (http://msdn.microsoft.com/en-us/library/ms468447.aspx). There is enough material out there though to make your head spin. Being that I'm not an authority on the subject I thought I'd explain in a simple-to-understand format the two types that we've used.

Hierarchical: workflow events are fired one at a time and they operate in a very sequential fashion...one waits on the other. Example: I fill out a form and submit it to my boss for approval. Until he approves or denies the request, the entire transaction is in a holding state. Once he approves it, it then gets submitted to his boss. If he denies the request, it comes back to me. After all approvals have been gathered, the transaction is considered complete.

Linear: here workflow events all fire off on different threads (asynchronously) and are independent of one another. They must all be completed though before the transaction can be considered complete. Example: I put in a request to get a widget ordered. This order requires the participation of four different groups to perform an action. So, requests are sent to all four groups and they all complete their task independent of one another. Once all four are completed, the widget can be processed.

Hopefully those two approaches make sense. If you have more to add to this topic, I welcome your feedback.

Shay

Someone sent me this story via email and I just had to share it...

At a fundraising dinner for a school that serves children with learning disabilities, the father of one of the students delivered a speech that would never be forgotten by all who attended. After extolling the school and its dedicated staff, he offered a question:

'When not interfered with by outside influences, everything nature does, is done with perfection. Yet my son, Shay, cannot learn things as other children do. He cannot understand things as other children do. Where is the natural order of things in my son?'

The audience was stilled by the query.

The father continued. 'I believe that when a child like Shay, who was mentally and physically disabled comes into the world, an opportunity to realize true human nature presents itself, and it comes in the way other people treat that child.'

Then he told the following story:

Shay and I had walked past a park where some boys Shay knew were playing baseball. Shay asked, 'Do you think they'll let me play?' I knew that most of the boys would not want someone like Shay on their team, but as a father I also understood that if my son were allowed to play, it would give him a much-needed sense of belonging and some confidence to be accepted by others in spite of his handicaps.

I approached one of the boys on the field and asked (not expecting much) if Shay could play. The boy looked around for guidance and said, 'We're losing by six runs and the game is in the eighth inning. I guess he can be on our team and we'll try to put him in to bat in the ninth inning.'

Shay struggled over to the team's bench and, with a broad smile, put on a team shirt. I watched with a small tear in my eye and warmth in my heart. The boys saw my joy at my son being accepted. In the bottom of the eighth inning, Shay's team scored a few runs but was still behind by three.

In the top of the ninth inning, Shay put on a glove and played in the right field. Even though no hits came his way, he was obviously ecstatic just to be in the game and on the field, grinning from ear to ear as I waved to him from the stands.

In the bottom of the ninth inning, Shay's team scored again.

Now, with two outs and the bases loaded, the potential winning run was on base and Shay was scheduled to be next at bat.

At this juncture, do they let Shay bat and give away their chance to win the game? Surprisingly, Shay was given the bat. Everyone knew that a hit was all but impossible because Shay didn't even know how to hold the bat properly, much less connect with the ball. However, as Shay stepped up to the Plate, the pitcher, recognizing that the other team was putting winning aside for this moment in Shay's life, moved in a few steps to lob the ball in softly so Shay could at least make contact.

The first pitch came and Shay swung clumsily and missed.

The pitcher again took a few steps forward to toss the ball softly towards Shay. As the pitch came in, Shay swung at the ball and hit a slow ground ball right back to the pitcher.

The game would now be over. The pitcher picked up the soft grounder and could have easily thrown the ball to the first baseman. Shay would have been out and that would have been the end of the game. Instead, the pitcher threw the ball right over the first baseman's head, out of reach of all team mates. Everyone from the stands and both teams started yelling, 'Shay, run to first! Run to first!' Never in his life had Shay ever run that far, but he made it to first base.

He scampered down the baseline, wide-eyed and startled.

Everyone yelled, 'Run to second, run to second!' Catching his breath, Shay awkwardly ran towards second, gleaming and struggling to make it to the base. By the time Shay rounded towards second base, the right fielder had the ball . The smallest guy on their team who now had his first chance to be the hero for his team. He could have thrown the ball to the second-baseman for the tag, but he understood the pitcher's intentions so he, too, intentionally threw the ball high and far over the third-baseman's head. Shay ran toward third base deliriously as the runners ahead of him circled the bases toward home.

All were screaming, 'Shay, Shay, Shay, all the Way Shay'

Shay reached third base because the opposing shortstop ran to help him by turning him in the direction of third base, and shouted, 'Run to third! Shay, run to third!' As Shay rounded third, the boys from both teams, and the spectators, were on their feet screaming, 'Shay, run home! Run home!' Shay ran to home, stepped on the plate, and was cheered as the hero who hit the grand slam and won the game for his team.

'That day', said the father softly with tears now rolling down his face, 'the boys from both teams helped bring a piece of true love and humanity into this world'.

Shay didn't make it to another summer. He died that winter, having never forgotten being the hero and making me so happy, and coming home and seeing his Mother tearfully embrace her little hero of the day!

Cohesion and Coupling

Every seasoned object-oriented developer should be familiar with these coding principles. Sometimes we forget though to implement them in the code that we write which leads to future refactoring. This article is a good reminder of these design and architecture patterns, and it provides some pretty good examples.
http://msdn.microsoft.com/en-us/magazine/cc947917.aspx

URL ReWrite

void Application_BeginRequest(object sender, EventArgs e)


{

System.Web.HttpContext httpContext = HttpContext.Current;

String currentURL = httpContext.Request.Path.ToLower();

string processPath =currentURL.Substring(httpContext.Request.ApplicationPath.Length).TrimStart('/').ToLower();

//Creates th



string physicalPath = httpContext.Server.MapPath(

currentURL.Substring(currentURL.LastIndexOf("/") + 1));



//checks to see if the file does not exsists.

if (!System.IO.File.Exists(physicalPath))

{

string queryString =httpContext.Request.ServerVariables["QUERY_STRING"];

string defaultPage = "~/PageViewer.aspx?process=";



if (processPath.EndsWith(".aspx"))

processPath = processPath.Substring(0, processPath.Length - ".aspx".Length);

// Rewrites the path

httpContext.RewritePath(defaultPage + processPath + "&" + queryString);

}

}

Peopleware: Productive Projects and Teams

This is one of the best books that I've read in a while on People Management. I would recommend it to all Development and Project Managers...heck, all managers in general.

Show screens early

One of the principles of Agile Development (via the Manifesto - http://agilemanifesto.org/principles.html) is to deliver working software frequently. One of the main ways we do this within the OSS development group is to get screens out there to our users early. Even if the screens don't do a whole lot, the functionality that they do contain has been tested and works.

Throughout the development of an application we're continuously pushing out features in small iterations to our customers. Even though the product is not fully "baked" it allows them to see visual progress, begin working with the product, and even provide testing and feedback.

So far it has produced great results. Our customers know exactly what they're getting, they've had interactive collaboration with us throughout the development effort, and the end result is a custom technology solution that meets the business need.

SQL Tuning

A while back one of the guys on my team recommended to me SQL Tuning as a fabulous read. I also endorse it as a book that should be a part of every SQL Developer's library. Note: it is not a light weekend read, but it will get you thinking about how you go about writing your SQL Statements going forward.

Create Test Data

It pains me when I see applications get off of the ground and they start out performing well in the first few months and then after some significant usage they slow down and become unweildy. It's because the application was tested with little or no data in the database.

How we have combated this in our group is to create "test data" and LOTS of it! In fact, working with our business sponsors on one project we determined that a certain number of transactions would be created in a year and that only 3 years of data was ever needed on hand. Older data could be archived. So, we planned for triple that amount and entered 10 years of test data and then expected the application to perform at the same level.

We learned a lot from our testing and made the necessary alterations for the application to perform well working with years of data.

The Joel Test

I got these from Joel Spolsky's latest book (previous blog entry). It's a test for every software team. How does your team score?

• Do you use source control?
• Can you make a build in one step?
• Do you make daily builds?
• Do you have a bug database?
• Do you fix bugs before writing new code?
• Do you have an up-to-date schedule?
• Do you have a spec?
• Do programmers have quiet working conditions?
• Do you use the best tools money can buy?
• Do you have testers?
• Do new candidates write code during their interview?
• Do you do hallway usability testing?

More details here: http://www.joelonsoftware.com/articles/fog0000000043.html

Brooks's Law

This is a principle in software development which says that "adding manpower to a late software project makes it later" (http://en.wikipedia.org/wiki/Brooks%27s_law). I couldn't have said it better. Just about anyone that's been in the industry of software development has experienced this first-hand.

Imagine Cup

I had never heard of this before today...it's a great idea and appears to have gained a lot of momentum recently both in the US and abroad. I'm all for research into technologies that enable a sustainable environment...
http://msdn.microsoft.com/en-us/magazine/cc748665.aspx

Smart and Get Things Done

I just finished reading this book written by Joel Spolksy. It's a quick read and one that I think every hiring technical manager should have in his library. I'm not necessarily a die-hard Spolsky fan, but he does have some good nuggets of information in his writings. In this latest book I summed up some of the items I got from it (in my own words) when it comes to finding and hiring good technical talent.

Help with Resume Sorting

•  Look for individuals that have passion about what they do (their field of specialty).
• Are they wanting to work for you and your company or do they just want a job?
• Is their resume full of grammatical and spelling errors? If so, toss it out.
• Look for smart people….they should have honors / accomplishments under their belt.
• Have they been through a highly selective process before (i.e. school, programs, elite military, etc.)?
• Look for those that are hard-core at what they do. These people usually push the envelope.
• Look for someone that will add some diversity to the group (i.e. background, culture, skillset, etc.).

Phone Interviews

• Ask them to talk about their history and tell you about themselves. The point here is to uncover who this person is and what they're about. Explore their technical aptitude, how they think, and how they get things done.
• Go into a technical deep dive. Ask hard questions and dig into the details of their answers. This step is where you'll uncover if they're good at what they do and if they're smart.
• Let the candidate interview you. Focus in on the type of questions they're asking. Hopefully they've taken the time and already know something about your company.

In-Person Interviews

• Always have at least 5-6 people interview the candidate.
•  Make sure your interview room is quiet, comfortable, and has a white board.
•  Each person interviewing should spend at least an hour with the candidate.
•  If the candidate is not good enough for your team, why would they be good for some other team?
•  Keep it simple: Hire or No Hire (nothing in between).
•  Keep in mind that it's all about hiring people that are "smart and get stuff done".
•  Avoid going in with preconceived notions…you won't get over them.
•  Never listen to recruiters…they're simply there to make a match and a sale.
•  Do not talk with other interviewers about a candidate until you've each made an independent decision.
•  Look for expressed passion from the individual -- find it, foster it, challenge it.
•  Make sure the candidate can express themselves clearly and can make others understand what they're talking about.

Seven Laws of Identity

Identity Management is a complex subject and one that can be hard to wrap your hands around. Journal 16 of "The Architecture Journal" focuses on this subject. One thing that caught my eye was the 7 Laws of Identity:

Law #1 User Control and Consent
Technical identity systems must only reveal information identifying a user with the user’s consent.

Law #2 Minimal Disclosure for a Constrained Use
The solution that discloses the least amount of identifying information and best limits its use is the most stable long-term solution.

Law #3 Justifiable Parties
Digital-identity systems must be designed so that the disclosure of identifying information is limited to parties that have a necessary and justifiable place in a given identity relationship.

Law #4 Directed Identity
A universal-identity system must support both “omnidirectional” identifiers for use by public entities and “unidirectional” identifiers for use by private entities—thus, facilitating discovery while preventing unnecessary release of correlation handles.

Law #5 Pluralism of Operators and Technologies
A universal-identity system must channel and enable the interworking of multiple identity technologies run by multiple identity providers.

Law #6 Human Integration
The universal-identity metasystem must define the human user to be a component of the distributed system integrated through unambiguous human-machine communication mechanisms offering protection against identity attacks.

Law #7 Consistent Experience Across Contexts
The unifying identity metasystem must guarantee its users a simple and consistent experience, while enabling separation of contexts through multiple operators and technologies.

To read more, go to:
http://www.identityblog.com/stories/2005/05/13/TheLawsOfIdentity.pdf

Be prepared for "gotchas"

Several months ago, my father and I replaced one of the water heaters in my house. It was about 10 years old, making "clangy" noises, and from my assessment was on its way out. When we had originally looked at the project we thought it would take us about 3 hours from start to finish to replace it. Well, 6 hours later we were finally wrapping things up and it wasn't because we didn't know what we were doing either...there were just so many "gotchas" in the replacement process.

That night as I sat back and reflected on the whole ordeal I realized how similar this particular home improvement project can be to IT development projects. So, here are a few bullet points that elaborate on my analogy:

- Always plan ahead in your project. One of the reasons for the extra time in this project was that I had to make 3 different stops to the hardware store because I didn't plan ahead enough for the project.

- Hope for the best, but be prepared for the worst. Account for this in your project estimation. I hadn't put too much thought into the fact that the new water heater was 3 inches taller and 2 inches wider in diameter. This makes a big difference when all of the plumbing lines are sautered copper...they don't bend and they're not flexible. So, we hadn't built into our estimate the time it would take to redo all of the plumbing.

- Don't be suprised if there are "gotchas"...especially when it comes to your budget. I had no idea that the price of metals had gone up so drastically over the last few years. I spent an additional $100 in project materials...I had originally estimated this cost to be less than half that amount.

- When implementing your solution, think of the worst possible scenario, and account for it. Not only do I have a drain pan underneath the water heater, but I also have a drain line so if leaking starts to occur the water exits through the pvc lines (which we ran through the walls) and to the outside of the house. In addition, the new unit has a "smart sensor" system that would not only prevent an interior flood, but will also shut itself off completely (including the natural gas) if a problem is detected. I'm very confident now that I'll never have a problem in this department.

- When designing your solution, think scalability. The new plumbing lines are "flex" lines going from the actual hard-copper lines to the new water heater unit. This cost me a little more, but the next time I have to replace it, re-plumbing won't be necessary.

- If at all possible, don't go it alone. If you haven't lifted a fifty-gallon hot water heater lately, go to your local hardware store and try. They're quite heavy and an oblong shape. It was good that there were two of us for this reason, but also because we were continually double-checking each other. We had a good system of checks and balances in place.

- Read the fine print. The new unit came with the option to purchase a "lifetime warranty". Folks, water heaters don't last a lifetime, so this wording caught my eye. After reading the fine print though and calculating the cost, I realized their business genius. The total implementation cost would have been 3-4 times as much as I paid doing it myself. In addition, there are ongoing maintenance costs to ensure you comply with the fine print within the warranty. We've all heard it a thousand times, but if something sounds too good to be true, it probably is!

- Don't wait for a problem to occur...be proactive. This is often times easier said than done because the reality of it is that usally the squeaky wheel gets the grease. I didn't have to change the unit when I did, but I saved myself a potential mess and a lot of $$$ by doing it this way.

So, when planning your next development project, take into account the need to have a solid plan, have enough resources available, have adequate funding, set aside contingency dollars for the unexpected "gotchas", anticipate requirements scope creep, and think proactively.

The Story of Stuff

I found this online short-story movie to be very interesting, entertaining, and thought provoking. In your free time I would encourage you to watch it. Individually, collectively, and as a corporate entity we can always improve in this area...even by taking baby steps.
http://www.storyofstuff.com/

Code Snippets

public static DateTime GetDate(int month, int year, DayOfWeek day, int dayNumber)

{
// validation
if (dayNumber < 1
dayNumber > 5) return DateTime.Now;
if (month < 1
month > 12) return DateTime.Now;
if (year < 1900) return DateTime.Now;

// set up our root variables
int daysInMonth = DaysInMonth(month, year);
int earliestDay = (dayNumber * 7) - 6;
DateTime earliestDate = new DateTime(year, month, earliestDay);
// if the earliest date matches the day we are looking for, it is the correct date
if ((DayOfWeek)earliestDate.DayOfWeek == day)
return earliestDate;
else
{
// calculate the new day
int newDay = earliestDate.Day;
if ((int)earliestDate.DayOfWeek > (int)day)
{
newDay += 7 - ((int)earliestDate.DayOfWeek) + 1;
}
else
{
newDay += ((int)day - (int)earliestDate.DayOfWeek);
}
if (newDay > daysInMonth)
{
// new day is more than the days in the month, so we need to go backwards
// start the day at the last day of the month
return GetDate(month, year, day, 4);
}
return new DateTime(year, month, newDay);
}
}


PIVOT TABLES -- SQL SERVER 2005

CREATE TABLE [dbo].[Quotas](
[Week] [smallint] NOT NULL,
[Days] [varchar](3) NOT NULL,
[Quota] [smallint] NOT NULL,
CONSTRAINT [PK_Quotas] PRIMARY KEY CLUSTERED
(
[Week] ASC,
[Days] ASC
)
Inserts
insert into Quotas (Week, Days, Quota) values (1, '1', 8)
insert into Quotas (Week, Days, Quota) values (1, '2', 7)
insert into Quotas (Week, Days, Quota) values (1, '3', 6)
insert into Quotas (Week, Days, Quota) values (2, '1', 5)
insert into Quotas (Week, Days, Quota) values (2, '2', 4)
insert into Quotas (Week, Days, Quota) values (2, '3', 3)
insert into Quotas (Week, Days, Quota) values (3, '3.5', 2)

SQL

select *
from
(select Week, days, quota from Quotas) q
PIVOT
(
sum(quota)
FOR days IN([1],[2],[3],[3.5])
) as pvt

It requires a function in the first argument of the pivot keyword, so as long as the week/day combination are unique, your "sum" will always only be on one row, so it won't matter. Maybe you could try an isnull(quota,0) and see if that works.

Art of Business

I recently read Sun Tzu's "The Art of Business" and was intrigued with his approach to competition.  I took the following notes:

1. Win all without Fighting
     a. Capture your Market without Destroying it
     b. Use the "Take-All-Under-Heaven" approach leaving everything intact though
     c. The goal is relative market dominance without destroying the industry

2. Avoid Strength, Attack Weakness
     a. Strike your enemy / competitor where they least expect it
     b. Attacking weaknesses is a better use of your resources
     c. Going strength against strength is the preferred method in Western culture
     d. Attack the weakest part of your competitor's value chain
     e. Be the "first mover" to launch a preemptive attack to gain competitive advantage
     f. Find the boundaries of your competitor…capitalize on their vulnerability
     g. Attack a competitor psychologically…attack their plans
     h. To be strong everywhere is to be strong nowhere

3. Deception and Foreknowledge
     a. Maximizing the power of market information
     b. Competitive research and analysis should be deep, conducted ethically, and taken seriously
     c. Learn your competitors strengths, weaknesses, and capabilities and know the marketplace
     d. Know yourself as well as your competitors
     e. Beating the competitor involves deceiving executives about the true nature of your plans

Scaling Strategies for ASP.NET applications

I finished reading an interesting MSDN article (http://msdn.microsoft.com/en-us/magazine/cc500561.aspx) that spoke to many of issues that enterprise application developers must face when scaling their solutions. Things like: it working just fine in the development environment but choking in Production due to the number of simultaneous users, or determining how to architect your server environment (i.e. application, web servers) for optimal performance, or what load-balancing is best for your situation, or what method should be chosen for storing Session State (with the intent of ridding your solution of affinity).
The article is long so I'll highlight some of their suggestions:

1) Use a hardware-based load-balancing solution.
2) Use SQL Server or ASP State Server to store session state.
3) Make sure file compression is turned on within IIS.
4) Turn off viewstate within your UI where it is not needed.
5) Leverage AJAX to reduce the payload on the client.
6) Use Data Caching judiciously but expect it to get complex.
7) Partition your data across databases and use replication to syncronize data.
8) Plan on your strategy growing as your application data and user base grows.

Ajaxification

"Ajaxification", a word you won't yet find on Wikipedia, is a word that we use to describe the process of applying AJAX to a web-based applicaiton. The technology was introduced to me about 18 months or so ago and I must admit that at first I was reserved and a little hesitant to jump on board the bandwagon. So, we decided to just "test it out" with one of our apps. Well, let's just say it didn't take too long to "ajaxify" everything we'd built. Now it's an integral part of the OSS development framework and a technology that we liberally use. The benefits are huge (i.e. real-time page updates, partial page rendering for optimized UI performance, no more screen flickering and repeated full page loads, a richer user experience, and the list goes on. Dino Esposito, who is referred to by some in the software development industry as a "machine", wrote an article (http://msdn.microsoft.com/en-us/magazine/cc507641.aspx) recently discussing in more depth Single-Page Interface and AJAX Patterns.