I recently modified a stored procedure at work and ended up removing a sort order and was reminded that Explicit Sort Order Matters.

The short version of the procedure is that it fills a temporary table with data, does some manipulation of that data and then returns a subset of the data. The problem was, the data contains an NTEXT column which contains XML data. Before you ask, the system was architected before SQL 2005 and the XML choice wasn't available. After the database was moved to a 2005 server, there was no need to change the data to XML because the database doesn't actually care about the data, it handles information used by the client.

NTEXT columns can be expensive, even in a temporary table, and especially when you've got several thousand rows to be slinging about and the average data length in the NTEXT is a tad under 1K. So, to optimize performance, I removed the NTEXT from the temporary table and added it in using a JOIN at the end of the procedure.

select *, Row_Number() OVER(ORDER...

Read More »

Microsoft has finally released Community Technology Preview 3 of their "distributed in-memory application cache platform for developing scalable, high-performance applications." And, as a caching guy who will be speaking about Velocity in Vegas in a few short weeks, I've been waiting for the release, which I played with this past weekend. First things first, though. I use memcached at work, a blazingly fast, in-memory, distributed cache. One of the key differences between memcached and Velocity is that Velocity is a "cache platform," a phrase which brings to mind unwieldy to begin with. Then, during the install, we see this dialog:



The DistributedCacheService makes sense, but why the RemoteAdministration and File and Printer...

Read More »

Deadlocks are no fun. Creating deadlocks for demonstration purposes is easy. To see how they work and how to get around them...

Yesterday, Nae and I went out to Scottown for her work and today is a mostly work on abstracts, talks and play Rock Band kind of day. My goal is to get all of the code wrapped up for my CODODN and put together my abstracts for CodeStock.org, I'm thinking Caching, Service Broker, and a couple of cloud based ones.

I also got a couple of new websites set up. One you'll have to find for yourself ;) The other I'm looking for beta-testers for. I got the idea at the MS event I attended Friday and it's loosely based on HotOrNot.com but instead of pictures it's for coders and their code.

We all write code, sometimes it's good code, sometimes it's not. Sometimes it gets formally reviewed and other times it doesn't. This site, CodeOrNot.net, is designed to allow people to post code they've written that peers can judge as good, bad, or just plain ugly. My goal is to get a wiki up with suggested...

Read More »

There are frequent posts to the memcached mailing list asking how to use memcached to solve a problem. There are two things for certain in the world of memcached users:

Some folks will want to use memcached as something other than a cache Some folks have gone so far as to create versions of memcached that are not a cache To help you with these questions, I've compiled a list of how you can implement memcached:

As a Lock Server: Check out this link and use something other than memcached.

As a Persistent Data Store: Check out this link and use something other than memcached. If you really want to use memcached, go here. In either case, don't use memcached.

...

Read More »

I would have attended anyway, a free day of .NET speakers, but I'll also be doing a presentation.

Session: SQL Server Service Broker A 100 level presentation by Josef Finsel . This handy tool, introduced in SQL Server 2005, allows you to build quick and effective tools for queuing, both totally encapsulated within SQL Server and using a combination of SQL Server and external programs. Learn not only the hows but some of the whys behind setting up Service Broker and leave ready to look at some of your current processes in a different light.

There are a lot of good speakers, some I have worked with, some I know, and some I know only by reputation. If you're anywhere in the area, I recommend you Register...

Read More »

The College Class Version

One of my first consulting gigs was with a company that wrote software for manufacturers and it was there that I met Paul. Paul was an engineer, a great guy and an even better story teller. One day he related about a course he took in college, a 300 or 400 level course required for his engineering degree which basically had the goal of reinventing the wheel. At the beginning of the semester, the professor showed them a basic sled like the one to the left. The goal of the class was to improve the method of transportation within certain guidelines. The important caveat was that they had to justify how they got there. They couldn't just say, "Wheels are better, here." No, they literally had to reinvent the wheel...

Read More »

 Ok, maybe it's not exactly a myth, but have you ever thought of why transactions handled by the server are necessary? We have had drilled into us from our very first database lessons that ACID is a requirement. And it's very possible that you've probably seen demonstrations where they talk about having two accounts and moving money from one account to another and how that should be wrapped in a transaction to prevent the loss of data between the two. But there's a problem with that; well, two problems with that.

SQL Server Management Studio (SSMS) doesn't always show you what you want to see, sometimes it "interprets" your results, especially when it comes to XML.  Open a query window in SSMS and enter the following query:

select 1 Tag, 0 as Parent, CompanyName [Inserted!1!CompanyName!cdata]FROM (SELECT 'Johnson & Johnson' CompanyName) AS A FOR XML EXPLICIT

This returns exactly what we would expect to see:

Â

The CompanyName contains data that would cause errors in valid XML so we wrap it in a CDATA tag. But when you store your XML in a XML Variable, something strange happens:

DECLARE @XMLExample XMLSET @XMLExample = (select 1 Tag, 0 as Parent, CompanyName [Inserted!1!CompanyName!cdata]FROM (SELECT 'Johnson & Johnson' CompanyName) AS A FOR XML EXPLICIT)select @XMLExample

You might expect to see the exact same output we saw from the first example but instead we get this:

  Johnson & Johnson

The CDATA...

Read More »

I had implemented a host file on the computer I regularly post from so I could test some changes to my web sites.

Then forgot to remove the entries. So yesterday's posts never showed up.

This has been fixed.