Refactor your SQL too
I'm almost finished with a widespread change in the Dozing Dogs CMS database; changing all the varchar to nvarchar and one particular varchar to ntext. This will answer questions that potential customers have about non-ASCII languages such as Chinese.
It will also solve an "issue" where our CMS only supported content up to ~8,000 characters. Yes, it's an embarrassing admission for a CMS but all products have their skeletons in the closet. This is (was) ours.
Long, long ago back in 2001/2002 or so I thought that using varchar for content would be plenty. You know, just like 640k RAM would be more than anyone could possibly use.
After all, this was just used for a local community web site where people could post calendar events or classifieds. There were a few Site Editors that would push the envelope and create long articles, but it was very rare.
Plus, I swear that I read somewhere that working with text/ntext columns sucked. And perhaps it did back then, but at some point this changed. A very kind and supportive customer asked me the other day why such a small limit and as I started to type "because that's ...the ...way ......it ..." I stopped in my tracks and thought "that IS stupid; why is it?".
I'd answered that question before, but something that day made me stop and think. So, thanks Serge.
That's example #1 of why it pays to stop and think about your "finished" product now and again. What you believed with all your heart in the early days may not be true now.
So, I'm knee-deep in sprocs, writing my Update2.4.0.sql script to convert existing databases and finding that using ntext is as simple as using varchar. And I'm literally reading every single sproc again.
And I keep finding improvements, like:
@UserID = @@IDENTITY
Ooops. Should be:
@UserID = SCOPE_IDENTITY()
And I also find a couple of sprocs that are never called, and 1 sproc returns Email as varchar(50) and another takes varchar(100).
Silly stuff, and none of it really important (until someone adds a trigger then @@IDENTITY screws up) but it's interesting you know? I refactor code quite often, but this is the first time I've re-read my SQL so closely.