Error while creating unique clustered index in view in SQL Server

Question

I wanted to create unique clustered index in view by name.

This is how I created the view

create view vWTotalsalesbyProduct
with schemabinding
as
    select 
        Name,
        Sum(Isnull(Unitprice * QuantitySold, 0)) as TotalSales,
        COUNT_BIG(*) as Totaltransation 
    from 
        dbo.product P 
    join
        dbo.tblproductcount C on P.productID = C.productID 
    group by 
        Name

and this is how I created unique clustered index

create unique clustered index UIX_vWTotalsalesbyProductName 
on vWTotalsalesbyProduct(Name)

But I get an error

Column 'Name' in table 'vWTotalsalesbyProduct' is of a type that is invalid for use as a key column in an index.

Help me solve it

So what is the datatype of the Name column in the base table? — Martin Smith
– Martin Smith, Commented Jan 11, 2018 at 8:58
Can you change it to a saner datatype? I can't imagine a name needs to support lengths of 2GB — Martin Smith
– Martin Smith, Commented Jan 11, 2018 at 9:02

Jayasurya Satheesh · Accepted Answer · 2018-01-11 09:17:49Z

2

Unique Constraint can hold up to 8000 bytes per row. So if the maximum length of the column allows to store more than 8000 bytes, you will get error.

CREATE TABLE dbo.Temp
(
    Name VARCHAR(901) UNIQUE
)

This code gave me the following warning

Warning! The maximum key length is 900 bytes. The index 'UQ__Temp__737584F64FD1D5C8' has maximum length of 5000 bytes. For some combination of large values, the insert/update operation will fail.

While this works fine

create TABLE dbo.Temp
(
    Name VARCHAR(900) UNIQUE
)

If You Use VARCHAR(MAX) or NVARCHAR(MAX) You Will get this Exact error

create TABLE dbo.Temp
(
    Name VARCHAR(max) UNIQUE
)

So make sure that the maximum allowable size of the column Name is less than 900 bytes

edited Jan 11, 2018 at 9:17

answered Jan 11, 2018 at 9:03

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Panagiotis Kanavos Over a year ago

The error is about the data type, not the size. (n)varchar(max) is a different data type with different storage

Denis Rubashkin Over a year ago

@Panagiotis Kanavos that's why he gets just warning instead of error

Panagiotis Kanavos Over a year ago

@marc_s in the question the error is is of a type that is invalid for use as a key column in an index. and the type is varchar(max)

Panagiotis Kanavos Over a year ago

@DenisRubashkin are we reading a different question or was the question edited to something completely different? Column 'Name' in table 'vWTotalsalesbyProduct' is of a type that is invalid for use as a key column in an index

Panagiotis Kanavos Over a year ago

@marc_s varchar(max) is a blob type. It's stored outside the page. The table contains only a pointer to the actual data location. The same restriction, for the same reasons, applied when we used text/ntext.

|

Collectives™ on Stack Overflow

Error while creating unique clustered index in view in SQL Server

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related