SQL Server - Truncate Using DATALENGTH

Question

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.

Answer 1

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

Update Table
Set Column = Left(Ltrim(Column),900)

Answer 2

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:

• computed_column_definition
• Indexes on Computed Columns

Answer 3

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