nvarchar(n) and nvarchar(max) different datatypes?

Even the index on column with data type nvarchar(n) has limitation, because the limitation for Index Key size is 900bytes.

The below script successfully creates an index with a warning "Warning! The maximum key length is 900 bytes. The index 'ix_3' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail."

create table test_hunt1(c1 nvarchar(4000))

create index ix_3 on test_hunt1(c1)

If the index column is modifed with data more than 900 bytes, the operation fails.

insert into test_hunt1 select REPLICATE('a',400)--successful insert
go
insert into test_hunt1 select REPLICATE('b',500)--failure

The reason Nvarchar(max) is not applicable is , it is BLOB and its storage pattern differs.

Also, choosing large datatypes for index key, would increase the size of B-tree, be cautious.

---

Thanks

Sarat



Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.