I STRONGLY second the call by @acloughnane to move the best practices paragraph to the top of the article. While this article is very informative, cursors should not...

This technique should come with a strong warning about understanding your database concurrency, because auditing with triggers increases transaction times, and can cause serious locking and/or latching issues.

I've encountered this...

This is all great stuff...that SQL Server should have had a decade ago. PostgreSQL users have been enjoying these features for a LONG time. Great to finally see the catch...

Check the waits that are occurring during the query when it is ran directly on the server (eliminate variable of the application).

If you get CXPACKET waits (waiting to recombine results...

We loaded something like 150 million records from Library of Congress and had a co-located 2 post rack running a quad processor something-or-other with a RAID 5 setup on Oracle...

Oh, that row_number trick is so beautiful it makes me want to cry!

@ KermitTheRock,

Indexed views rock! Keep in mind though, you need SS Enterprise to get this feature. Most ISP licences are Standard so unless you paid the big nut for Enterprise...

@ Antonio,

Just so I can file this away in my brain for later:

What is your max sustained throughput (total users and max queries per minute)? How many CPUs?

Thanks.

We added a synonyms table that cross referenced common abbreviations, common misspellings, and plural forms. We also built our own table of stop words to strip from the list of...

#1 This will be faster than full text search for large loads (hundreds of queries per minute) on a db with millions of records/documents to search.

I did this exact thing...

Save this to a text file called test.bat then execute on the command line:

@echo off

FOR /F "usebackq tokens=1,2,3 delims=~" %%i IN (`sqlcmd -w200 -h-1 -E -Q"set nocount on; select convert(varchar(10),getdate())...