Avoiding NOLOCK

Here’s an interesting article (http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/) on the affects of using the NOLOCK hint in your SQL queries and how even data not being directly affected by an update can be messed up in your query.

I’ve always tried to avoid using it but have to admit to being a bit more liberal about it recently to solve some sticky deadlocking issues.  Those places where it’s being used could happily show bad data and not have any real affects, but it’s easy to see where they could, especially where aggregates are being calculated.  I’ve also not yet made the jump to snapshot isolation as that presents it’s own set of issues but will be looking into it more in the future.

Database Updates without Downtime

I was forwarded in interesting article, Developing Low-Maintenance Databases by Alex Kuznetsov, that talked about good database design and implementation practices.  Many I already adhere to, such as limiting access to the database through stored procedures, views and functions.  However, there was one section in the article that I found particularly intriguing, How to Refactor a Table without Downtime.

In many of the systems I work on we have many components on many servers accessing the same database, coordinating the simultaneous upgrade of those components can be tricky at best.  Although in some cases access can be controlled through a web service and thus limiting upgrade points, performance factors have led us to have many of the components access the database directly.

I definitely intend on looking at incorporating Alex’s pattern into our future upgrade procedures.

COALESCE vs. ISNULL in T-SQL

A colleague of mine forwarded the following article along to me:

Deciding between COALESCE and ISNULL in SQL Server (http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/)

I switched over from ISNULL to COALESCE a couple of years ago and haven’t looked back, primarily to standardize and avoid nested ISNULLs.  This article seems to affirm my thought that there are few disadvantages to COALESCE except in certain unusual circumstances.  It’s worth a quick read.

Tracing WCF Calls

I ran into an issue with one of my WCF services and was having the quite a time trying to track it down, especially since it was failing before it was getting to the actual service.  I came across a nice way of tracing the call all the way through the process.  Add the following section to your web.config file:

<system.diagnostics>
  <sources>
    <source name="System.ServiceModel"
            switchValue="Information, ActivityTracing"
            propagateActivity="true">
      <listeners>
        <add name="traceListener"
            type="System.Diagnostics.XmlWriterTraceListener"
            initializeData= "C:\Logs\Traces.svclog" />
      </listeners>
    </source>
  </sources>
</system.diagnostics>

This will create a trace file that you can open up in the Microsoft Service Trace Viewer.  You can read about the details here.

Quick note – Don’t forget to remove it when you’re done!

WCF Security Debugging Trick

Not infrequently, while deploying my WCF web services to various dev and QA environments I run into security issues, where for some reason I can’t get authenticated.  I recently found an article that (WCF: "An error occurred when verifying security for the message." and Service Security Audit) that talks about how to get more info on these errors logged in the application log using the following serviceSecurityAudit behavior:

<serviceSecurityAudit 
	auditLogLocation="Application"
	serviceAuthorizationAuditLevel="Failure"
	messageAuthenticationAuditLevel="Failure"
	suppressAuditFailure="true"/>

Text Files, ASCII, ANSI, Unicode and Code Pages

OK, so according to Joel Spolsky, I should have known this 7 or so years ago, but I’m only now dealing with multi-lingual text files so you’ll have to forgive me.  Anyway, this is a very instructive article on the encoding of text files:\

The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)

C# Case-Insensitive Dictionary

Here’s a neat little feature I wasn’t aware of until recently.  You can make a .NET Dictionary object case-insensitive.  i.e. myDictionary[“ABC”] returns the same as myDictionary[“aBc”].  You can read more at:

C# Case-Insensitive Dictionary on Dot Net Perls (http://www.dotnetperls.com/case-insensitive-dictionary)