T-SQL index over computer column in a table
Posted on 2014-10-23
I have a SQL 2008 R2 DB and in my table I have a field DocumentNumber (Num) bigint. I want to provide my C# app to search by any part of this number, therefore I should search in it in nvarchar (with LIKE %234). My question is – which is the classis way:
1. To create a separated fields and on Add record to cast the bigint to nvarchar and to index on it. Because this is a official document, it will be very rarely modified / deleted.
2. To create computed field ( [NumS] AS (CONVERT([nvarchar],[Num],0)), and to index on it. Should I mark it Is persisted or not? I think this will be the better way, if the SQL use this column only to create index, while in the point 1 this separated column will take HDD space.
3. I prefer to use nvarchar, not varchar because if in the feature the document number included letters as well. I know nvarchar get twice more space because of Unicode support, but what about search of index? I mean – a index over nvarchar is equal fast as index over varchar, right ? If there is big differences, may be I'll use varchar.
The more likely usage of search will be the user to search with last 2 or 3 numbers of the document, so the index will be used in real. I expect not more than 50 000 records in that table for few years, but want to do it right, next time it could be 5 000 000 records.