Curiosidad tabla y vista que filtra datos númericos sobre columna varchar

Muchas gracias Alejandro por esta respuesta tan clara y elaborada.

Como bien dices una alternativa sería crear la vista sin convertir, select
usando cadena, y conversión en el resultado final.

Ya por curiosidad he intentado materializar la vista (incluso con un indice
clustered por ambas columnas de la tabla, no iba buscando la mejor solución,
sino una alternativa que no diese error a partir de una vista
materializada), y me sigue dando error, sigue leyendo todas las filas de la
tabla. Te paso el script que he probado y que tambien da error de
conversion, cuando entiendo que deberia funcionar

use Pruebas
GO
drop index i_solonum on solonum
GO
drop view solonum
GO
drop table mezcla
GO
create table mezcla(id int identity(1, 1), pupurri varchar(20)
CONSTRAINT [PK_mezcla_id] PRIMARY KEY CLUSTERED )
GO
insert into mezcla values('pepe')
insert into mezcla values('001')
insert into mezcla values('15')
insert into mezcla values('juan')
insert into mezcla values('1')
insert into mezcla values('150')
GO
create view dbo.solonum with schemabinding as
select id, cast(pupurri as int) num
from dbo.mezcla
where isnumeric(pupurri) = 1
GO
create unique clustered index i_solonum on solonum(id, num)
GO
select *
from solonum
select *
from solonum
where num = 15

Un saludo
Salvador Ramos

www.helpdna.net (información sobre SQL Server y Microsoft .Net)
www.helpdna.net/acerca_de_salvador_ramos.htm


"Alejandro Mesa" escribió en el
mensaje news:
> Hola Salva,
>
> 1 - Recuerda que SS usa la definicion de la vista, ya que esta no esta
> materializada, por lo que la sentencia final sera:
>
> select id, cast(pupurri as int) num
> from mezcla
> where
> pupurri not like '%[A-Z]%'
> and pupurri like '%[0-9]%'
> and pupurri = 15
>
> Este es el plan estimado. fijate que hace el CAST para todas las filas.
>
> |--Compute
> Scalar(DEFINE:([Expr1004]=CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)))
> |--Table Scan(OBJECT:([Northwind].[dbo].[mezcla]),
> WHERE:(CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)=CONVERT_IMPLICIT(int,[@1],0)
> AND [Northwind].[dbo].[mezcla].[pupurri] like '%[0-9]%' AND NOT
> [Northwind].[dbo].[mezcla].[pupurri] like '%[A-Z]%'))
>
> Como vez, hace la conversion a entero para todas las filas. El problema es
> que usas la columna [num] en la expresion de la clausula "where", y la
> comparas contra un entero. Como la vista no esta materializada, SS usa el
> plan anterior.
>
> Yo te recomiendo esto:
>
> 1 - Puedes crear la vista para filtrar, pero sin convertir / cast.
> 2 - Filtrar en la sentencia "select" usando una cadena, en vez de un
> numero
> 3 - Hacer el cast en el resultado final.
>
> Por cierto, esta expresion filtra las columnas que contienen solo #s
> (fijate
> en la clausula "where").
>
> create view solonum
> as
> select id, pupurri
> from mezcla
> WHERE pupurri NOT LIKE '%[^0-9]%'
> GO
>
> select id, CAST(pupurri AS int) num
> from solonum
> WHERE pupurri = '15'
> GO
>
> |--Compute
> Scalar(DEFINE:([Expr1004]=CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)))
> |--Table Scan(OBJECT:([Northwind].[dbo].[mezcla]),
> WHERE:([Northwind].[dbo].[mezcla].[pupurri]=[@1] AND NOT
> [Northwind].[dbo].[mezcla].[pupurri] like '%[^0-9]%'))
>
> Tambien puedes dejar la vista como esta, pero adiciona la columna
> [pupurri]
> y usa esta en la expresion que usas en la clausula "where" de la sentencia
> "select" que referencia la vista. De esta forma SS solo necesita convertir
> el
> resultado final.
>
> create view solonum
> as
> select id, pupurri, CAST(pupurri AS int) AS num
> from mezcla
> where pupurri not like '%[A-Z]%'
> and pupurri like '%[0-9]%'
> GO
>
> SET SHOWPLAN_TEXT ON
> GO
>
> select id, pupurri, num
> from solonum
> WHERE pupurri = '15'
> GO
>
> |--Compute
> Scalar(DEFINE:([Expr1004]=CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)))
> |--Table Scan(OBJECT:([Northwind].[dbo].[mezcla]),
> WHERE:([Northwind].[dbo].[mezcla].[pupurri]=[@1] AND
> [Northwind].[dbo].[mezcla].[pupurri] like '%[0-9]%' AND NOT
> [Northwind].[dbo].[mezcla].[pupurri] like '%[A-Z]%'))
>
> Usar solamente la funcion ISNUMERIC no es de ayuda aqui, pues ocurrira lo
> mismo que en el script original.
>
> create view solonum
> as
> select id, CAST(pupurri AS int) AS num
> from mezcla
> WHERE ISNUMERIC(pupurri) = 1
> GO
>
> SET SHOWPLAN_TEXT ON
> GO
>
> select id, num
> from solonum
> WHERE num = 15
> GO
>
> SET SHOWPLAN_TEXT OFF
> GO
>
> |--Compute
> Scalar(DEFINE:([Expr1004]=CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)))
> |--Table Scan(OBJECT:([Northwind].[dbo].[mezcla]),
> WHERE:(CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)=CONVERT_IMPLICIT(int,[@1],0)
> AND isnumeric([Northwind].[dbo].[mezcla].[pupurri])=(1)))
>
>
> AMB
>
>
> "Salvador Ramos" wrote:
>
>> Hola a todos,
>>
>> El otro día me plantearon la siguiente curiosidad. Hago una tabla y en un
>> campo varchar introduzco datos numéricos y no numéricos, hago una vista
>> para
>> filtrar sólo los numéricos. Si luego hago una select sobre la vista
>> funciona, pero si esa select lleva una condición where da error. Aquí
>> tenéis
>> un script que reproduce dicha curiosidad:
>> (Por supuesto no vamos a entrar en hacerlo de otra forma con los números
>> en
>> columnas numéricas, simplemente estudiar este caso)
>>
>> use Pruebas
>> GO
>>
>> drop table mezcla
>> GO
>>
>> create table mezcla(id int identity(1, 1), pupurri varchar(20))
>> GO
>>
>> insert into mezcla values('pepe')
>> insert into mezcla values('001')
>> insert into mezcla values('15')
>> insert into mezcla values('juan')
>> insert into mezcla values('1')
>> insert into mezcla values('150')
>> GO
>>
>> sean
>> create view solonum as
>> select id, cast(pupurri as int) num
>> from mezcla
>> where pupurri not like '%[A-Z]%'
>> and pupurri like '%[0-9]%'
>> GO
>>
>> select *
>> from solonum
>>
>> select *
>> from solonum
>> where num = 15
>>
>> El mensaje es el siguiente:
>> Mens. 245, Nivel 16, Estado 1, Línea 1
>> Error de conversión al convertir el valor varchar 'pepe' al tipo de datos
>> int.
>>
>> El problema es que esto ya lo tienen implementado sobre 2000 (que
>> funciona
>> correctamente) y ahora para migrar a 2005 se encuentran con el error.
>> Qué soluciones se os ocurren, a parte de crear nuevas columnas u otras
>> modificaciones en el diseño ?
>>
>> Muchas gracias
>>
>> Un saludo
>> Salvador Ramos
>>
>> www.helpdna.net (información sobre SQL Server y Microsoft .Net)
>> www.helpdna.net/acerca_de_salvador_ramos.htm
>>
>>
>>
>>

Hola Salva,

Que edicion de SS 2005 estas usando?

Para que SS considere una vista indexada en cualquier edicion que no sea
Enterprise, se debe usar el hint NOEXPAND. De lo contrario SS tratara
usara
la tabla, en vez de la vista. Trata:

select [id], num
from dbo.solonum WITH (NOEXPAND)
where num = 15
GO

AMB

"Salvador Ramos" wrote:

Muchas gracias Alejandro por esta respuesta tan clara y elaborada.

Como bien dices una alternativa sería crear la vista sin convertir,
select
usando cadena, y conversión en el resultado final.

Ya por curiosidad he intentado materializar la vista (incluso con un
indice
clustered por ambas columnas de la tabla, no iba buscando la mejor
solución,
sino una alternativa que no diese error a partir de una vista
materializada), y me sigue dando error, sigue leyendo todas las filas de
la
tabla. Te paso el script que he probado y que tambien da error de
conversion, cuando entiendo que deberia funcionar

use Pruebas
GO
drop index i_solonum on solonum
GO
drop view solonum
GO
drop table mezcla
GO
create table mezcla(id int identity(1, 1), pupurri varchar(20)
CONSTRAINT [PK_mezcla_id] PRIMARY KEY CLUSTERED )
GO
insert into mezcla values('pepe')
insert into mezcla values('001')
insert into mezcla values('15')
insert into mezcla values('juan')
insert into mezcla values('1')
insert into mezcla values('150')
GO
sean
create view dbo.solonum with schemabinding as
select id, cast(pupurri as int) num
from dbo.mezcla
where isnumeric(pupurri) = 1
GO
create unique clustered index i_solonum on solonum(id, num)
GO
select *
from solonum
select *
from solonum
where num = 15

Un saludo
Salvador Ramos

www.helpdna.net (información sobre SQL Server y Microsoft .Net)
www.helpdna.net/acerca_de_salvador_ramos.htm


"Alejandro Mesa" escribió en el
mensaje news:
> Hola Salva,
>
> 1 - Recuerda que SS usa la definicion de la vista, ya que esta no esta
> materializada, por lo que la sentencia final sera:
>
> select id, cast(pupurri as int) num
> from mezcla
> where
> pupurri not like '%[A-Z]%'
> and pupurri like '%[0-9]%'
> and pupurri = 15
>
> Este es el plan estimado. fijate que hace el CAST para todas las filas.
>
> |--Compute
> Scalar(DEFINE:([Expr1004]=CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)))
> |--Table Scan(OBJECT:([Northwind].[dbo].[mezcla]),
> WHERE:(CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)=CONVERT_IMPLICIT(int,[@1],0)
> AND [Northwind].[dbo].[mezcla].[pupurri] like '%[0-9]%' AND NOT
> [Northwind].[dbo].[mezcla].[pupurri] like '%[A-Z]%'))
>
> Como vez, hace la conversion a entero para todas las filas. El problema
> es
> que usas la columna [num] en la expresion de la clausula "where", y la
> comparas contra un entero. Como la vista no esta materializada, SS usa
> el
> plan anterior.
>
> Yo te recomiendo esto:
>
> 1 - Puedes crear la vista para filtrar, pero sin convertir / cast.
> 2 - Filtrar en la sentencia "select" usando una cadena, en vez de un
> numero
> 3 - Hacer el cast en el resultado final.
>
> Por cierto, esta expresion filtra las columnas que contienen solo #s
> (fijate
> en la clausula "where").
>
> create view solonum
> as
> select id, pupurri
> from mezcla
> WHERE pupurri NOT LIKE '%[^0-9]%'
> GO
>
> select id, CAST(pupurri AS int) num
> from solonum
> WHERE pupurri = '15'
> GO
>
> |--Compute
> Scalar(DEFINE:([Expr1004]=CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)))
> |--Table Scan(OBJECT:([Northwind].[dbo].[mezcla]),
> WHERE:([Northwind].[dbo].[mezcla].[pupurri]=[@1] AND NOT
> [Northwind].[dbo].[mezcla].[pupurri] like '%[^0-9]%'))
>
> Tambien puedes dejar la vista como esta, pero adiciona la columna
> [pupurri]
> y usa esta en la expresion que usas en la clausula "where" de la
> sentencia
> "select" que referencia la vista. De esta forma SS solo necesita
> convertir
> el
> resultado final.
>
> create view solonum
> as
> select id, pupurri, CAST(pupurri AS int) AS num
> from mezcla
> where pupurri not like '%[A-Z]%'
> and pupurri like '%[0-9]%'
> GO
>
> SET SHOWPLAN_TEXT ON
> GO
>
> select id, pupurri, num
> from solonum
> WHERE pupurri = '15'
> GO
>
> |--Compute
> Scalar(DEFINE:([Expr1004]=CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)))
> |--Table Scan(OBJECT:([Northwind].[dbo].[mezcla]),
> WHERE:([Northwind].[dbo].[mezcla].[pupurri]=[@1] AND
> [Northwind].[dbo].[mezcla].[pupurri] like '%[0-9]%' AND NOT
> [Northwind].[dbo].[mezcla].[pupurri] like '%[A-Z]%'))
>
> Usar solamente la funcion ISNUMERIC no es de ayuda aqui, pues ocurrira
> lo
> mismo que en el script original.
>
> create view solonum
> as
> select id, CAST(pupurri AS int) AS num
> from mezcla
> WHERE ISNUMERIC(pupurri) = 1
> GO
>
> SET SHOWPLAN_TEXT ON
> GO
>
> select id, num
> from solonum
> WHERE num = 15
> GO
>
> SET SHOWPLAN_TEXT OFF
> GO
>
> |--Compute
> Scalar(DEFINE:([Expr1004]=CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)))
> |--Table Scan(OBJECT:([Northwind].[dbo].[mezcla]),
> WHERE:(CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)=CONVERT_IMPLICIT(int,[@1],0)
> AND isnumeric([Northwind].[dbo].[mezcla].[pupurri])=(1)))
>
>
> AMB
>
>
> "Salvador Ramos" wrote:
>
>> Hola a todos,
>>
>> El otro día me plantearon la siguiente curiosidad. Hago una tabla y en
>> un
>> campo varchar introduzco datos numéricos y no numéricos, hago una
>> vista
>> para
>> filtrar sólo los numéricos. Si luego hago una select sobre la vista
>> funciona, pero si esa select lleva una condición where da error. Aquí
>> tenéis
>> un script que reproduce dicha curiosidad:
>> (Por supuesto no vamos a entrar en hacerlo de otra forma con los
>> números
>> en
>> columnas numéricas, simplemente estudiar este caso)
>>
>> use Pruebas
>> GO
>>
>> drop table mezcla
>> GO
>>
>> create table mezcla(id int identity(1, 1), pupurri varchar(20))
>> GO
>>
>> insert into mezcla values('pepe')
>> insert into mezcla values('001')
>> insert into mezcla values('15')
>> insert into mezcla values('juan')
>> insert into mezcla values('1')
>> insert into mezcla values('150')
>> GO
>>
>> sean
>> create view solonum as
>> select id, cast(pupurri as int) num
>> from mezcla
>> where pupurri not like '%[A-Z]%'
>> and pupurri like '%[0-9]%'
>> GO
>>
>> select *
>> from solonum
>>
>> select *
>> from solonum
>> where num = 15
>>
>> El mensaje es el siguiente:
>> Mens. 245, Nivel 16, Estado 1, Línea 1
>> Error de conversión al convertir el valor varchar 'pepe' al tipo de
>> datos
>> int.
>>
>> El problema es que esto ya lo tienen implementado sobre 2000 (que
>> funciona
>> correctamente) y ahora para migrar a 2005 se encuentran con el error.
>> Qué soluciones se os ocurren, a parte de crear nuevas columnas u otras
>> modificaciones en el diseño ?
>>
>> Muchas gracias
>>
>> Un saludo
>> Salvador Ramos
>>
>> www.helpdna.net (información sobre SQL Server y Microsoft .Net)
>> www.helpdna.net/acerca_de_salvador_ramos.htm
>>
>>
>>
>>