SQL index on nvarchar

In SQL 2005 in order to be able to index any field, the field length cannot exceed 900 bytes
This in mind you can have an index on something like a varchar(900) or an nvarchar(450)
This greatly assisted me on my quest to improve performance on our global www platform.
We record every visit to our website, which as you can imagine was increasing our db size exponentially.
I ended up using two locations to download browser user agent information
http://www.user-agents.org/allagents.xml
http://www.botsvsbrowsers.com/category/1/index.html
and populate a table in the with this, periodically updating the table twice a day. I then created an insert trigger on the table that records each user, this trigger cross referenced the user agent table to identify if this browsing instance was human or a bot of some description, raising a bit field flag if it was not human.
As you can imagine, on a very busy website it is critical that this happen as fast as possible. Hence applying an index on the fields under comparison is ideal and very fast.

To edit the field in the db and bring it down to the correct size a simple transact staement was needed

The website when it pulls out the http user agent from the web request, truncates it down to 450 so as to avoid any splat in the db when the data layer tries to insert the data

Now that the non human traffic has been flagged, in our fully normalised referential database we can archive the data and delete the records from the production servers very rapidly, saving space and improving performance

Leave a Reply

Your email address will not be published. Required fields are marked *