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
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
ALTER TABLE tablename
ALTER COLUMN columnname nvarchar(450) NULL
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
'get the ServerVariables name value collection
Dim value As NameValueCollection = Request.ServerVariables
'prepare our session class and capture the values
Dim objSession As New oSession
'we use string.empty to avoid a splat, if the value in the colleciton doesnt exist
'or turns out to be nothing the string.empty simply gets parsed
.REMOTE_ADDR = value("REMOTE_ADDR") & String.Empty
.HTTP_USER_AGENT = Left(value("HTTP_USER_AGENT") & String.Empty, 450)
.HTTP_ACCEPT_LANGUAGE = value("HTTP_ACCEPT_LANGUAGE") & String.Empty
.SERVER_NAME = Left(value("SERVER_NAME") & String.Empty, 255)
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