Category Archives: Consultancy

How to quickly convert all NTEXT columns to NVARCHAR(MAX) in a SQL Server database

I was at a client’s earlier today and the question came up of how to convert all NTEXT columns to NVARCHAR(MAX) in their SQL Server databases, and it turns out they have rather a lot of them.

There are a couple of obvious advantages to this conversion:

  1. Online index rebuilds with SQL Server Enterprise Edition become a possibility,
  2. Values are stored in row by default, potentially yielding performance gains.

My response to this was, “Yeah, sure: I can write a script to do that.” Two seconds after I said this I thought, “Hmm, I bet 30 seconds of Googling will provide a script because this must have come up a zillion times before.”

Sure enough, there are some pretty reasonable hits. For example,

Buuuuuuuuuuuut as ever, you’d be naive indeed to think that you can just copy and paste code from StackOverflow and have it work first time. Moreover, even with modification, you need to go over it with a fine-toothed comb to make sure you’ve squashed every last bug.

For example, this boned me earlier because I wasn’t paying proper attention:

CASE WHEN is_nullable = 1 THEN 'NOT' ELSE '' END

You can see the logic is reversed from what it should be.

So, anyway, I ended up concocting my own, of which you can find the latest version at

There are essentially three phases:

  1. Change the datatype of the NTEXT columns to NVARCHAR(MAX) using ALTER TABLE statements
  2. Pull any values small enough to fit in row out of LOB storage and back into rows using UPDATE statements. Thanks to John Conwell for pointing out the necessity of doing that to realise any performance increase with existing data.
  3. Refresh the metadata for any views using sp_refreshview – this makes sure that, for example, columns listed for them in sys.columns have the correct data type.

Phases 1 and 2 are actually done together in a loop for each NTEXT column in turn, whilst phase 3 is done in a separate loop at the end. I just refresh the metadata for all views because, although I could figure out only the views that depend on the tables, it’s simpler to just do them all and doesn’t take that long. Of course, if you have thousands of views and a relatively small number of NTEXT columns you might want to rethink this. My situation is numbers of tables, views, and NTEXT columns are all of the same order of magnitude so a simple script is fine.

For those of you who don’t have git installed, or aren’t comfortable with DVCS, here’s the full script:



-- Set this to 0 to actually run commands, 1 to only print them.
DECLARE @printCommandsOnly BIT = 1;

-- Migrate columns NTEXT -> NVARCHAR(MAX)

DECLARE @object_id INT,
@columnName SYSNAME,
@isNullable BIT;


DECLARE @ntextColumnInfo TABLE (
object_id INT,
ColumnName SYSNAME,
IsNullable BIT

INSERT INTO @ntextColumnInfo ( object_id, ColumnName, IsNullable )
SELECT  c.object_id,, c.is_nullable
FROM    sys.columns AS c
INNER JOIN sys.objects AS o
ON c.object_id = o.object_id
WHERE   o.type = 'U' AND c.system_type_id = 99;

object_id, ColumnName, IsNullable FROM @ntextColumnInfo;

OPEN col_cursor;
FETCH NEXT FROM col_cursor INTO @object_id, @columnName, @isNullable;

@command =
'.' + QUOTENAME(OBJECT_NAME(@object_id))
+ QUOTENAME(@columnName)
WHEN @isNullable = 1 THEN ''
+ ' NULL;';
PRINT @command;
IF @printCommandsOnly = 0
sp_executesql @command;

   SELECT @command =
'.' + QUOTENAME(OBJECT_NAME(@object_id))
' SET '
+ QUOTENAME(@columnName)
' = '
+ QUOTENAME(@columnName)

   PRINT @command;
IF @printCommandsOnly = 0
sp_executesql @command;

   FETCH NEXT FROM col_cursor INTO @object_id, @columnName, @isNullable;

CLOSE col_cursor;
DEALLOCATE col_cursor;

-- Now refresh the view metadata for all the views in the database
-- (We may not need to do them all but it won't hurt.)

DECLARE @viewObjectIds TABLE (
object_id INT

INSERT INTO @viewObjectIds
SELECT o.object_id
FROM sys.objects AS o
WHERE o.type = 'V';

object_id FROM @viewObjectIds;

OPEN view_cursor;
FETCH NEXT FROM view_cursor INTO @object_id;

@command =
'EXECUTE sp_refreshview '''
PRINT @command;

   IF @printCommandsOnly = 0
sp_executesql @command;

   FETCH NEXT FROM view_cursor INTO @object_id;

CLOSE view_cursor;
DEALLOCATE view_cursor;

NOTE: this won’t work where views are created WITH SCHEMABINDING. It will fail at ALTER TABLE for any table upon which schemabound views depend. Instead, to make it work, you have to DROP the views, then do the ALTERs and UPDATEs, then re-CREATE the views. Bit of a PITA but there’s no way around it unfortunately. I didn’t need to worry about this because my client doesn’t use schemabound views.

Of course it goes without saying that you should back up your database before you run any script like this!

To use it you just need to substitute the name of your database where it says _YOUR_DATABASE_NAME_ at the top of the script.

Also, As with automating many tasks in SQL Server, dynamic SQL is a necessity. It’s a bit of a pain in the backside so a @printCommandsOnly mode is advised for debugging purposes, and I’ve switched this on by default. You can copy and paste the commands into a query window, parse them, or even execute them to ensure they work as expected.

Once you’re happy this script does what you want set the value of @printCommandsOnly to 0 and rerun it to actually execute the commands it generates.

You might wonder why I’ve written this imperatively rather than in set-based fashion. Well, it’s not just because I’m a programmer rather than a DBA. In fact the original version, which you can still see if you look at the file’s history, was set-based. It looked pretty much like this:


-- Migrate columns NTEXT -> NVARCHAR(MAX)

DECLARE @alterColumns NVARCHAR(MAX) = '';
SELECT  @alterColumns = @alterColumns
+ QUOTENAME(OBJECT_SCHEMA_NAME(c.object_id)) + '.' + QUOTENAME(OBJECT_NAME(c.object_id))
+ CASE WHEN c.is_nullable = 1 THEN '' ELSE 'NOT' END + ' NULL;'
+ CHAR(13)
+ QUOTENAME(OBJECT_SCHEMA_NAME(c.object_id)) + '.' + QUOTENAME(OBJECT_NAME(c.object_id))
' SET '
' = '
';' + CHAR(13) + CHAR(13)
FROM    sys.columns AS c
INNER JOIN sys.objects AS o
ON c.object_id = o.object_id
WHERE   o.type = 'U' AND c.system_type_id = 99; --NTEXT

PRINT @alterColumns;

EXECUTE sp_executesql @alterColumns;

-- Update VIEW metadata

DECLARE @updateViews NVARCHAR(MAX) = '';
SELECT @updateViews = @updateViews
+ 'EXECUTE sp_refreshview '''
+ QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id))
''';' + CHAR(13)
FROM sys.objects AS o
WHERE o.type = 'V'

PRINT @updateViews;

EXECUTE sp_executesql @updateViews;

It’s certainly a lot less code, which is nice. And it doesn’t use CURSORs, which is also nice.

However, it does have problems:

  • The PRINT statement in T-SQL truncates output if it goes beyond a certain length. I don’t know exactly what this length is off the top of my head, but my generated scripts were more than long enough to reach it.
  • The result of this is you can’t copy and paste the complete generated script into another query window, so it might make debugging a bit trickier in some instances.
  • The really problematic thing is that, when something goes wrong, you can’t necessarily relate it back to the exact command that failed, whereas the imperative version makes this easy since each generated command is executed individually.

So I threw away the, on the face of it, "cleverer" and more "elegant" set-based version in favour of the longer, clunkier (but easier to debug) imperative version.

I hope you find it useful and please feel free to submit patches, pull requests, bug reports, feature requests via the main project GitHub page at (Turns out I’m building up a small library of handy scripts so I’ll be pushing a few more items into this repo in due course.)

Live Bookmarking in ANTS Performance Profiler: a killer feature to help you zero in on performance problems fast

Last week I was sat with Simon, one of my client’s managers, as he showed me around their new customer support centre web app highlighting slow-loading pages. Simon, along with a couple of others, has been playing guinea pig using the new support centre in his day to day work.

The main rollout is in a few weeks but the performance problems have to be fixed first so support team members don’t spend a lot more time on calls, forcing customers to wait longer on hold before speaking to someone. Potentially bad for costs, customer satisfaction, and team morale!

Simon gave me a list of about a dozen trouble spots and I remoted into their production box to profile them all. I had to collect the results and get off as quickly as possible to avoid too much disruption; I could analyse them later on my own laptop. This gave me plenty of time to hunt down problems and suggest fixes.

I used Red Gate’s ANTS Performance Profiler throughout. One of the many helpful features it includes is bookmarking. You can mark any arbitrary time period in your performance session, give it a meaningful name (absolutely invaluable!), and use that as a shortcut to come back to it later.

For example, here I’ve selected the “Smart search” bookmark I created whilst profiling the support centre:

Timeline with bookmarked region selected.

The call tree shows me the call stacks that executed during the bookmarked time period. Towards the bottom you can see that SQL queries are using the vast majority of time in this particular stack trace:

Call tree showing call stacks within bookmarked region on timeline.

(Identifying SQL as a problem I took these queries and analysed them in more detail using both their execution plans, and SQL Server’s own SQL Profiler. I then suggested more efficient queries that could be used by NHibernate via repository methods.)

Also note we’re looking at Wall-clock time as opposed to CPU time. I won’t talk about the differences in detail here. What you need to understand is that Wall-clock time represents actual elapsed time. This matters because the queries execute in SQL Server, outside the IIS worker process running the site. Under CPU time measurements, which only include time spent in-process, they therefore wouldn’t appear as significant contributors to overall execution time.

Back on point: bookmarking is great as far as it goes, but you have to click and drag on the timeline after the fact to create them yourself. In the midst of an involved profiling session this is a hassle and can be error prone: what if by mistake you don’t drag out the full region you need to analyse? All too easily done, and as a result you can miss something important in your subsequent analysis.

Step in Live Bookmarks.

Basically, whilst profiling, you hit a button to start the bookmark, do whatever you need to do in your app, then hit a button to save the bookmark. Then you repeat this process as many times as you need. No worries about missing anything.

Here’s how it goes in detail:

  1. Start up a profiling session in ANTS Performance Profiler.
  1. Whilst profiling, click Start Bookmark. (To the right of the timeline.)

Start a live bookmark.

  1. Perform some action in your app – in my case I was clicking links to navigate problem pages, populate data, etc.
  1. Click Stop Bookmark.

Stop (and save) a live bookmark.

  1. The bookmark is added to the list on the right hand side of the timeline. It’s generally a good idea to give your bookmark a meaningful name. To do this just click the pencil icon next on the bookmark and type in the new name.

Rename bookmark.

  1. Rinse and repeat as many times as you need.
  1. When you’ve finished, click Stop in the top-left corner of the timeline to stop profiling.

Stop profiling.

It’s a good idea to save your results for later using File > Save Profiler Results, just in case the worst happens, and of course you can analyse them offline whenever you have time.

And that’s it: nice and easy, and very helpful when it comes to in depth performance analysis across a broad range of functionality within your application.

Any questions/comments, please do post below, or feel free to get in touch with me directly.

How to stop feeling bad about charging your clients lots of money for consultancy

UPDATE: I’ve since done more reading about pricing and, whilst I think the approach outlined below is certainly a lot better than no approach at all (otherwise known as picking a figure out of the air), I don’t think it’s the best approach. I’ll write on this topic again when I’ve had more opportunities to try out different approaches. In the meantime you could do a lot worse than read Alan Weiss on value-based pricing.

“How much will this cost us?”

The question’s come up quite a bit recently and, every time it does, my heart leaps into my mouth and I want to go and hide under a rock. It seems so innocuous. It’s just business, after all, and I mean: I ought to know the answer, right?

But I feel terrible about charging people lots of money, even when they can blatantly afford it. And this despite the obvious fact that one of the multitude of reasons I left my job at Red Gate was that I wanted to earn more money.

(Nothing against Red Gate: it was just time. Those of you who’ve been through a similar thing will know exactly what I mean. By the way: if you’re a consultant you should be earning more. You are in an inherently riskier and more precarious situation, and your income will be lumpier. If you’re not earning more then what’s the point? Why take the risk? You may as well go back to working for somebody else.)

Some of you will be mercenary as hell and won’t identify with this at all. You’re probably sat there thinking, “What is this guy on?” and, honestly, you can safely move on. I don’t think you’re going to have any problems at all, and I wish I was more like you.

Unfortunately I’m not, and this lead to the inevitable situation where the first job that came my way, via a friend working for a local company, I underquoted for. He asked me how much and I agonised. I looked at contractor rates for the local area, and added about half again on, then submitted a quote including a justification of why the fee was so “high” and heard nothing for a couple of days. Then he sent back an email saying, “yes, that’s absolutely fine,” with no hint of a quibble, and I thought: “Oh balls, I should have quoted more.”*

Well, yes, I should have done, but that’s not exactly very scientific, is it?

To the list of unscientific methods of setting your consultancy rates I’d also add asking your friends what sounds reasonable when you’re next down the pub with them even if – especially if – they work in the same domain. They’ll probably underquote because (a) most of them aren’t consultants, (b) they’re already appalled (and secretly jealous) of how much they perceive consultants to earn, (c) they might confuse consultancy with contracting, and (d) they probably haven’t sat down and worked through the calculations I’m about to lay out for you below.

Also, despite all my artsy tendencies, I’m a programmer with a science education and there are a lot of questions where I just feel a lot more comfortable and confident when I know the “right” answer and, more importantly, can follow the route that led to that answer. This is true even where the answer has some element of error/uncertainty associated with it.

So what is the “right” answer to the question of how much you should charge?

OK, so I’m not going to give you a figure. Nobody will because the answer depends very much on your situation. What I can do is map out the route by which you can arrive at that answer, and feel comfortable that the answer is reasonable. As with many seemingly intractable questions, it involves breaking the problem down into more manageable chunks.

The first thing you need to do is answer a few simple questions:

  1. On average, how many billable hours will I work each week? This will be less – most likely significantly less – than the total number of hours you work.
  2. How much do I want to earn? For the sake of argument we’ll call this your salary, although the exact mechanics of how the money finds its way into your pocket are best discussed with an accountant.
  3. What are my costs? Strictly, costs include your salary (2), but here I’m really talking about all the other things that you need to spend money on. The combination of (2) (before tax) and (3), taking into account (1), will allow you to calculate your consulting fees.

Let’s deal with each of these questions in turn…

1. On average, how many billable hours will I work each week?

As I said, this will most likely be a lot less than the total number of hours you’ll work.


Because you need to spend time on business development (marketing, wooing clients, actively searching for work, developing other revenue streams) and admin (finance, etc.).

For example, I reckon I’ll average around 24 billable hours/week, giving me 16 hours (2 days) to work on everything else. In reality I’ve accepted that the “everything else” bit will take more than 16 hours, meaning I’ll work more than the 40-hour work week that’s become standard in the UK, in order to pursue other business opportunities whilst still meeting my financial goals.

2. How much do I want to earn?

As I said, let’s call this your salary. You need to work out what you want this to be before tax.

Say you want to earn £100k/year, and take 5 weeks holiday. You’ll work 47 weeks and, since you only earn when you’re working, your salary for those weeks will be £2130.

If you’re trying to think about what a reasonable salary might be, here are some issues you should consider. Some of these are domestic expenses, whilst others are things that might be provided as employee benefits were you working for a larger company:

  • How much money do you need to survive for 6 months if, for some reason, you can’t work?
  • How much is your mortgage/rent?
  • How much do you need to pay for childcare?
  • How much is income protection insurance going to cost? Think about at least being able to cover your bills (NOT live the life of riley, unless you can afford to pay a lot),
  • How much time do you want to take as holiday each year? Remember: when you’re on holiday, you’re not earning, so treat it as a cost. (IMPORTANT NOTE: don’t forget to buy travel insurance, including medical cover!)
  • What about healthcare? The UK has the NHS and you can get free hospital care throughout the EU with a valid EHIC card but, in some instances, private healthcare can ensure you receive treatment more quickly for non-emergency conditions.

3. What are my costs?

Salary is obviously a cost, but as I said before, this is exclusive of salary.

So, here is a non-exhaustive list of business expenses you need to take into account when calculating your costs:

  • Travel
    • Car (or other vehicle)
      • Purchase or finance costs
      • Insurance – expect this to go up (social, domestic, leisure and commuting use won’t cover you if you spend a lot of time on the road visiting different clients)
      • Tax (known as Vehicle Excise Duty in the UK)
      • Annual roadworthiness test (known as MOT in the UK)
      • Servicing/repairs
      • Fuel
    • Rail (also metro/underground, and bus for large cities)
    • Air
  • Insurance
    • Professional indemnity
    • Travel (make sure it covers both business, personal, and medical expenses!)
    • Health (optional: depends on where you live)
  • Domain names and hosting
  • Equipment (including software licenses, and SaaS subscriptions)
  • Administrative services (accountancy, legal, company registration, etc.)
  • VAT (depends on how much you earn/anticipate you’ll earn)
  • Electricity
  • Heating
  • Water
  • Property/council tax
  • Food (especially if you’ve previously worked somewhere with a subsidised or free canteen)
  • Pension (you contribute to this from your salary but your employer would also normally make a contribution so I think it’s worth highlighting as a cost)

(It’s non-exhaustive because I keep thinking of things that need to be added to it.)

You need to work out how much these will cost you per annum. Do NOT underestimate them…

Business Expense Example 1

I have a pretty good bead on my car costs, as they stand right now, but I anticipate they’ll change significantly over the next 12-18 months. For starters, I may buy a newer car since my current car is getting a bit tired. I’m also not exactly sure how much of my mileage will be business mileage, or how many miles I’ll drive, so I’m assuming the “worst case” scenario: most miles will be for business, and I’ll have to drive longer distances (albeit perhaps less often) than I was simply commuting to and from my old job. Thus I’ve budgeted to be able to cover any increase in car costs.

Business Expense Example 2

Right now I’m paying next to nothing for hosting and domain names. My hosting (on Azure) is covered by the MSDN subscription Microsoft have kindly given me so I can continue working on NTVS, whilst my domain names currently cost well under £100/year. However, over the next 12 months I have plans that I anticipate could increase my hosting and domain costs to as much as £200/month which, in my cost breakdown, I’ve rounded up to a convenient £2500/year. They may go higher (in fact, I sort of hope they do, because that would indicate success… or possibly terribly inefficient code, but hopefully the former) and, if so, I’ll need to adjust. I anticipate a gradual ramp up though, so £2500 should be sufficient for the first year.

You might also be able to consider a portion of your mortgage or rent as a business expense if you work from home but BE CAREFUL. If you get this wrong you can run into hot water with the Inland Revenue, so make sure you know the rules for your jurisdiction.

Once you’ve worked out what your business expenses are per annum, divide by the number of weeks you’ll be working (NOT by 52) to work out what they’ll be for the average week. You’ll probably be surprised by how much they are. In fact, I’d go as far as to say that if they’re running at much less than £500/week you should be suspicious that you’ve miscalculated. Depending on your exact circumstances you may find they’re much higher – maybe closer to £1000. For the purposes of the calculation below I’m going to split the difference and say £750/week.

So, how much should I charge?

So, we want to pay ourselves £2130/week and our costs are £750 week, which means we need to earn £2880/week.

BUT we’re only billing for 24 hours, which means we need to charge out at £2880/24 = £120 per hour.

Or £960 per day.

And, of course, at that level over the course of a year (unless you’re particularly lazy) you’ll easily hit the VAT registration threshold, which means in that your fees are in fact £960 + VAT per day, which is basically £1200 per day.

Sounds like a lot, doesn’t it? But in consultancy terms it’s actually still pretty cheap. That in itself may be a problem – I don’t pretend to have figured that out yet. It also gives you some idea of why large consultancy firms are so eye-wateringly expensive: they have to pay for all that glitzy marketing, aggressive recruitment, graduate training, and those fancy offices somehow.

Because you’re worth it

I’m sure you can see how I’ve landed on those figures which, by the way, are a ballpark (and also chosen to make the sums easy for this example!). You might still have nagging doubts though. You might be wondering, am I really worth it?

This is tricky, and is really something you can only answer for yourself. What you need to think about is the value you’re offering to clients. Here’s a concrete example from my own experience.


Potential client comes to me looking for help with some serious performance and scalability problems – even at minimal loads – with a website and set of associated services they’re planning to roll out in four weeks’ time. Technologies are .NET, ASP.NET MVC, and SQL Server. They want a specialist, with a background in .NET and SQL Server performance optimisation to come in and help them fix their performance problems.

My Background

I’m a software engineer with 14 years professional experience across a wide range of projects on various platforms. Of that, I spent nearly 10 years building tools to solve common problems encountered by .NET developers and SQL Server developers and DBAs. That includes the award winning ANTS Performance Profiler, which allow developers to quickly isolate performance problems in .NET code, and will highlight long-running/inefficient queries in SQL Server. I’ve done a lot of user testing, helping people find performance issues in their code, I’ve written about .NET performance, and I’ve spoken at .NET user groups on the topic.

What value do I bring?

The client has already spent a lot of time (and therefore money) trying to fix the problems they’re having, but with only limited success. They therefore want a specialist. With my background clearly I am something of a specialist. This is not a situation in which any .NET developer will do.

I can go in and help them fix their performance problems much more quickly than they might otherwise be able to. Although my day rate may seem high, they will actually spend less overall, and can be much more confident of a successful launch. This means they won’t lose money due to the site being unable to handle the load placed on it.

So, am I worth it?

In this case: yes. (But that doesn’t guarantee that they’ll be willing to pay – that’s a separate issue.)

You too are a professional (I assume). That being the case people should (and will) be willing to pay professional rates for your services. There will obviously be some element of negotiation for at least some jobs, and that’s OK: it’s just business. It’s part of your clients’ jobs to negotiate, and therefore part of yours.

Your fees might vary a bit anyway, depending on the client and the nature of the job. For example, for longer jobs with more billable hours per week you may negotiate a lower fee, because it’s guaranteed work for a longer period of time. I drew a distinction between consultancy and contracting earlier, and this is really what I’d describe as contracting. Consultancy I tend to think of as more being pulled in to advise on a specific issue or to help fix specific problems. It’s all obviously open to interpretation, and is something of a sliding scale.

Not everyone will be willing or able to pay your fees. For example, not many one- or two-man outfits would pay £800-1000 (or more) per day for consultancy, and that’s fine: those are not your customers. But many larger companies will.

What do I mean by larger?

Well, Red Gate regularly paid those kinds of fees (and more) for consultants and trainers from at least the time when I joined the company as employee number 17 back in 2004, and continues to do so. So, by larger, I really don’t mean that large.

I mentioned negotiation in passing a few paragraphs back. There’s an important point here: once you’ve gone through the process I’ve outlined above you’re in a much better position to negotiate. You know how much money you need to earn. You know where you can afford to compromise, and where you can’t.

You know, I hate negotiating, particularly when it comes to money, but it becomes a heck of a lot easier when you take the emotion out of the equation, and can back it up with facts and figures. You probably wouldn’t share those with your clients, because it’s none of their business, but knowing in your own mind what’s reasonable and what’s not will give you a lot more confidence.

Going back to people won’t be willing to pay for your services: that’s OK. They are not your customers. Think about the opportunity cost of cutting your fees to work for them: you may end up turning down a better paying job later on as a result, or you may gain a reputation for being cheap/easy to exploit that is hard to shake. If that happens people will undervalue you and your work, and you really don’t want that.

I’m going to stop now before this completely degenerates into a random stream of unconnected thoughts, but I hope it’s proven helpful for you. And if you have any comments or advice of your own please do come back at me – I’d certainly love to hear it.

Now go forth and charge lots of money**!

*Only half a days’ work so not a disaster.

**By which I mean a reasonable hourly/daily rate that allows you to cover all your costs and earn a decent living on top!

(Oh: one more tale of schoolboy error for your amusement. I recently sent out a quote to a potential client that I calculated using the above, but I still reckon he might think he’s getting a real bargain. Why? Because, like a muppet, I forgot to take VAT into account. Fortunately a short job again so not the end of the world.)

EDIT: Thanks to my friend Claire Taylor for pointing out that I’d forgotten to include a pension in my list of costs.