1

Is there a way to truncate an nvarchar using DATALENGTH? I am trying to create an index on a column, but an index only accepts a maximum of 900 bytes. I have rows that consist of 1000+ bytes. I would like to truncate these rows and only accept the first n characters <= 900 bytes.

Bridge
  • 29,818
  • 9
  • 60
  • 82
janinaj
  • 154
  • 1
  • 3
  • 10
  • 1
    This could be a symptom of a flaw in the design. Why do you want to index a wide column? Libraries don't shelve "The Bell Jar" next to "Farenheit 451" even though one begins "It was a p..." and the other starts "It was a q..." – Anon Aug 29 '14 at 16:07
  • I second that. Why do you need to index a column that big? – paparazzo Aug 29 '14 at 16:15
  • We need to do searches on that column. We accommodate partial searches so we can trim the characters. It will take a lot of time to query a row if we do not index that column. – janinaj Aug 29 '14 at 16:21

3 Answers3

0

Can be this sql useful, Just update the table for that column.

Update Table
Set Column = Left(Ltrim(Column),900)
Jithin Shaji
  • 5,893
  • 5
  • 25
  • 47
0

Create a COMPUTED COLUMN that represents the data you want to index, then create an index on it.

ALTER TABLE MyTable ADD ComputedColumn AS LEFT(LargeNVarcharColumn,900);

CREATE NONCLUSTERED INDEX MyIndex ON MyTable
(
    ComputedColumn ASC
);

Reference:

Wagner DosAnjos
  • 6,304
  • 1
  • 15
  • 29
0

Trim the column ,left or right side to 900 characters ,create a index on that column

ALTER TABLE usertable ADD used_column AS LEFT(nvarcharcolumn,900);

create a index on this used columm.it will work

Adi
  • 232
  • 1
  • 9
  • I have trimmed the rows to 450 characters, but they still take up 1000+ bytes. – janinaj Aug 29 '14 at 16:22
  • create a seprate the column using nvarchar column, that new column should have below 900 characters.build a index on new column , use that column – Adi Aug 29 '14 at 16:24
  • Sorry, this works but the value should be 450, not 900. – janinaj Aug 29 '14 at 16:39