INDICES en VISTAS

03/05/2004 - 17:33 por Isaías | Informe spam
Hola a todos

Estoy intentando crear INDICES en una vista, para lo cual
ejecuto las siguientes instrucciones:

SET NUMERIC_ROUNDABORT OFF
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABO
RT,QUOTED_IDENTIFIER,ANSI_NULLS ON


CREATE view view_1403_MI WITH SCHEMABINDING AS
SELECT datepart(hh,fd_FileInputDateTime) AS HORA,
AS HORA,
MINUTO = CASE
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 0
AND 4 THEN '05'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 5
AND 9 THEN '10'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 10
AND 14 THEN '15'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 15
AND 19 THEN '20'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 20
AND 24 THEN '25'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 25
AND 29 THEN '30'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 30
AND 34 THEN '35'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 35
AND 39 THEN '40'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 40
AND 44 THEN '45'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 45
AND 49 THEN '50'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 50
AND 54 THEN '55'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 55
AND 59 THEN '00' END,
count(fb_InputFileStatus) as TOTAL
FROM DBO.file_1403
WHERE CONVERT(CHAR(8),fd_FileInputDateTime,112) = CONVERT
(CHAR(8),GETDATE(),112)
AND fb_InputFileStatus = 1
GROUP BY datepart(hh,fd_FileInputDateTime),CASE
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 0
AND 4 THEN '05'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 5
AND 9 THEN '10'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 10
AND 14 THEN '15'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 15
AND 19 THEN '20'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 20
AND 24 THEN '25'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 25
AND 29 THEN '30'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 30
AND 34 THEN '35'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 35
AND 39 THEN '40'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 40
AND 44 THEN '45'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 45
AND 49 THEN '50'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 50
AND 54 THEN '55'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 55
AND 59 THEN '00' END

/* Aqui es donde truena la creacion de los indices */
CREATE UNIQUE CLUSTERED INDEX indx_view_1403_MI ON
view_1403_MI (HORA)

Server: Msg 1935, Level 16, State 1, Line 1
Cannot create index. Object 'FILE_1403' was created with
the following SET options off: 'ANSI_NULLS.'.

¿Algun tip o idea de como solucionarlo?

Gracias.
 

Leer las respuestas

#1 ulises
03/05/2004 - 17:59 | Informe spam
El mensaje es claro, la tabla a la que hace referencia la
vista también tendría que haber sido creada con SET
ANSI_NULLS ON, te recomiendo que leas la información sobre
los requerimientos para crear vistas indexadas en el BOL.

Saludos,
Ulises

Hola a todos

Estoy intentando crear INDICES en una vista, para lo cual
ejecuto las siguientes instrucciones:

SET NUMERIC_ROUNDABORT OFF
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHAB


O
RT,QUOTED_IDENTIFIER,ANSI_NULLS ON


CREATE view view_1403_MI WITH SCHEMABINDING AS
SELECT datepart(hh,fd_FileInputDateTime) AS HORA,


(hh,fd_FileInputDateTime)
AS HORA,
MINUTO = CASE
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 0
AND 4 THEN '05'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 5
AND 9 THEN '10'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 10
AND 14 THEN '15'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 15
AND 19 THEN '20'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 20
AND 24 THEN '25'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 25
AND 29 THEN '30'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 30
AND 34 THEN '35'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 35
AND 39 THEN '40'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 40
AND 44 THEN '45'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 45
AND 49 THEN '50'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 50
AND 54 THEN '55'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 55
AND 59 THEN '00' END,
count(fb_InputFileStatus) as TOTAL
FROM DBO.file_1403
WHERE CONVERT(CHAR(8),fd_FileInputDateTime,112) = CONVERT
(CHAR(8),GETDATE(),112)
AND fb_InputFileStatus = 1
GROUP BY datepart(hh,fd_FileInputDateTime),CASE
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 0
AND 4 THEN '05'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 5
AND 9 THEN '10'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 10
AND 14 THEN '15'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 15
AND 19 THEN '20'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 20
AND 24 THEN '25'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 25
AND 29 THEN '30'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 30
AND 34 THEN '35'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 35
AND 39 THEN '40'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 40
AND 44 THEN '45'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 45
AND 49 THEN '50'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 50
AND 54 THEN '55'
WHEN datepart(mi,fd_FileInputDateTime) BETWEEN 55
AND 59 THEN '00' END

/* Aqui es donde truena la creacion de los indices */
CREATE UNIQUE CLUSTERED INDEX indx_view_1403_MI ON
view_1403_MI (HORA)

Server: Msg 1935, Level 16, State 1, Line 1
Cannot create index. Object 'FILE_1403' was created with
the following SET options off: 'ANSI_NULLS.'.

¿Algun tip o idea de como solucionarlo?

Gracias.

.

Preguntas similares