CODEWITHSUNDEEP
sql-serversql-server-2008indexing
Dustin Davis
Dustin Davis

Reputation: 14605

SQL: Index nvarchar(2048) - Full text vs hash

I have two tables: table1 holds a column nvarchar(2048) (unique values) and table2 holds a reference to table1 id.

Since I can't index nvarchar(2048) my question is, should I add a full-text index to table1 or should I add another column to table1 that holds a hash so that I can put an index on the hash column and then do checks against the hash column?

Data is inserted into table2 after a check to table1 is done to see if a value exists (if not, it is inserted into table1, then gets the id back for storing in table2).

That hash would not be computed in SQL.

What would be a better approach as far as scalability, data storage and performance?

Upvotes: 1

Views: 983

Answers (1)

a1ex07
a1ex07

Reputation: 37382

I think that in such a case (the only important thing is to ensure uniqueness, no search/sorting needed) you can create a CLR UDF to generate hash, a computed persistent column that holds hash calculated by this function, and an unique constraint on that column.

Upvotes: 2

Related Questions