0

I have a table

CREATE TABLE Test
(
    id int primary key,
    a nvarchar(max),
    c nvarchar(max),
    b nvarchar(max)
)

I want to improve performance of queries to the table and want to create index (or indexes) for columns that are accessed often.

As all fields except id has type nvarchar(max) so sql-server says that I can't create index for columns where type is nvarchar(max)

Create index MyIndex on T(a)

ERROR: Column 'a' in table 'T' is of a type that is invalid for use as a key column in an index

According to the MSND it is possible to create index using followed notation 

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
[ ; ]

I'm interested in the option 

[ INCLUDE ( column_name [ ,...n ] ) ]
About it MSDN says:

INCLUDE (column [ ,... n ] ) Specifies the non-key columns to be added to the leaf level of the nonclustered index. The nonclustered index can be unique or non-unique. All data types are allowed except text, ntext, and image.

Can I create any index or alter existing PK index in order to add columns to the index?

Dimitry
  • 217
  • 4
  • 15
  • 1
    Do you really need those columns to be `NVARCHAR(MAX)`? – DavidG Jul 28 '14 at 09:44
  • What *is* the question? (Even if an index could be added on such a "large text" column, it would likely not be usable in a query plan.) – user2864740 Jul 28 '14 at 09:44
  • You might find some help (or even your answer) here. Please check [link][1] [1]: http://stackoverflow.com/questions/2863993/is-of-a-type-that-is-invalid-for-use-as-a-key-column-in-an-index – chaitanya.moguluri Jul 28 '14 at 09:58
  • What do you not understand about the error message? That message is not about syntax. It says that the column type is not usable as an index key. – usr Jul 28 '14 at 10:05
  • adding those nvarchar-columns on a leaf node level (INCLUDing them) will not make them searchable like the PK. Only the PK will be available for quick index scans. If your goal is to do searches in those nvarchar-columns, you need to index those directly. the answer of user mehdi lotfi goes in that direction. – Der U Jul 28 '14 at 10:23
  • What the limit for column with type nvarchar(n) to be indexed? – Dimitry Jul 28 '14 at 11:02
  • 1
    i very much think the limit will by 450 characters as for nvarchar the datalength is twice the stringlength and the limit for indexes in sql server is 900 byte. if you want to search every word in your text columns full text search might be what you need. – Der U Jul 28 '14 at 16:35
  • @DerU, I have done it easier: I change the size of field: there are not so long data to keep it so large. Then I have created two indexes – Dimitry Jul 28 '14 at 16:53

1 Answers1

0

You can create calculated index with persist option and create index on it.

ALTER TABLE dbo.test ADD a2 AS SUBSTRING(a,1,1000) PERSISTED
ALTER TABLE dbo.test ADD b2 AS SUBSTRING(b,1,1000) PERSISTED
ALTER TABLE dbo.test ADD c2 AS SUBSTRING(c,1,1000) PERSISTED


CREATE INDEX ix_text_a ON dbo.text(a2)
CREATE INDEX ix_text_b ON dbo.text(b2)
CREATE INDEX ix_text_c ON dbo.text(c2)
mehdi lotfi
  • 11,194
  • 18
  • 82
  • 128
  • 1
    that would be calculated *columns*, but other than that: good idea! does this work with the 900 byte limit for indexes? – Der U Jul 28 '14 at 10:21