CODEWITHSUNDEEP
sql-serverviewuniqueclustered-index
Awesome
Awesome

Reputation: 580

Error while creating unique clustered index in view in SQL Server

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

enter image description here

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

Upvotes: 1

Views: 1059

Answers (1)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

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
)

enter image description here

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

Upvotes: 2

Related Questions