Um blog sobre nada

Um conjunto de inutilidades que podem vir a ser úteis

Persisting a view by creating a clustered index

Posted by Diego em Agosto 13, 2013


Starting with a simple table:

image

 

We create a view to sum all ids:

 

CREATE VIEW vwtest2

AS

SELECT SUM(id) AS sum_id FROM dbo.TABLE1

if we try to create an index: 

 

CREATE   INDEX ixUnique ON vwtest2(sum_id)

it complains saying that the view is not schema bound:

Msg 1939, Level 16, State 1, Line 1

Cannot create index on view ‘vwtest2’ because the view is not schema bound.

 

so we try to schema bind the view and again, it complains because the first index on a view must be a clustered index

DROP VIEW vwtest2

 

CREATE VIEW vwtest2

WITH schemabinding

AS

SELECT SUM(id) AS sum_id FROM dbo.TABLE1

 

CREATE   INDEX ixUnique ON vwtest2(sum_id)

 

 

Msg 1940, Level 16, State 1, Line 1

Cannot create index on view ‘vwtest2’. It does not have a unique clustered index.

 

then, if we try to create the clustered index, we also get an error:

CREATE UNIQUE CLUSTERED INDEX ixClustered ON vwtest2(sum_id)

 

Msg 10138, Level 16, State 1, Line 1

Cannot create index on view ‘TSQL2012.dbo.vwtest2’ because its select list does not include a proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select list.

the reason is that, since there is an aggregate function, we need a count_big(*) on the query. Either you do that:

 

OPTION 1

DROP VIEW vwtest2

 

CREATE VIEW vwtest2

WITH schemabinding

AS

SELECT SUM(id) AS sum_id, COUNT_BIG(*) AS _count FROM dbo.TABLE1

 

CREATE UNIQUE CLUSTERED INDEX ixClustered ON vwtest2(sum_id)

  

or you remove the aggregation:

 

OPTION 2

 

CREATE VIEW vwtest3

WITH schemabinding

AS

SELECT id FROM dbo.TABLE1

 

CREATE UNIQUE clustered  INDEX ixUnique3 ON vwtest3(id)

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

 
%d bloggers like this: