Ezequiel Podadera

miércoles, 7 de diciembre de 2011

Función SQL para Comparar un rango de direcciones IP

Esta función nos sirve para comparar si una dirección IP se encuentre entre un rango de direcciones IP desde-hasta.

Recibe como parámetros la dirección IP a comparar, el rango IP "desde" y el rango IP "hasta", como salida devuelve 1 (bit) si la IP a comparar se encuentra entre dicho rango, de lo contrario devuelve 0.

En muchos casos las direcciones IP son guardadas en la base como caracteres, para poder comparar un rango IP primero se debe convertir a un BINARY(4) que es lo que realmente representa a una dirección IP y permite comprar rangos.

La función es la siguiente:
CREATE FUNCTION dbo.RangoIPv4(@ip AS VARCHAR(15),@ipDesde AS VARCHAR(15),@ipHasta AS VARCHAR(15)) RETURNS BIT
AS
BEGIN
DECLARE @binIP AS BINARY(4)
DECLARE @binDesde AS BINARY(4)
DECLARE @binHasta AS BINARY(4)
DECLARE @bit as bit

SELECT @binIP =
CAST( CAST( PARSENAME ( @ip, 4 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

SELECT @binDesde =
CAST( CAST( PARSENAME( @ipDesde, 4 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ipDesde, 3 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ipDesde, 2 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ipDesde, 1 ) AS INTEGER) AS BINARY(1))

SELECT @binHasta =
CAST( CAST( PARSENAME( @ipHasta, 4 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ipHasta, 3 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ipHasta, 2 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ipHasta, 1 ) AS INTEGER) AS BINARY(1))

set @bit= Case when @binIP between @binDesde and @binHasta then 'TRUE' else 'FALSE' end

RETURN @bit
END
go

Un par de ejemplos de los resultados:

select dbo.RangoIPv4('192.168.10.8','192.168.10.1','192.168.10.255')

Resultado:

-----
1

select dbo.RangoIPv4('192.168.10.8','192.168.10.1','192.168.10.7')

Resultado:

-----
0

Fuente, adaptación del artículo: http://stackoverflow.com/questions/1385552/datatype-for-storing-ip-address-in-sql-server

lunes, 1 de agosto de 2011

Función SQL para obtener la edad de una persona

Con esta simple función podremos saber la edad en años a partir de la fecha de nacimiento:

Select dbo.GetEdad('19810522')

-----------
30

(1 row(s) affected)

Script:
CREATE function dbo.GetEdad

(
@FechaNacimiento DATETIME
)

RETURNS int
Begin
declare @edad integer
if (@FechaNacimiento is null or @FechaNacimiento = '' )
set @edad = 0
else
if (month(getdate()) > month(@FechaNacimiento))
set @edad = DateDiff(yyyy,@FechaNacimiento,getdate())
else
if (month(getdate()) = month(@FechaNacimiento)
AND day(getdate()) >= day(@FechaNacimiento))
set @edad = DateDiff(yyyy,@FechaNacimiento,getdate())
else
set @edad = DateDiff(yyyy,@FechaNacimiento,getdate()) -1
Return @edad
End

jueves, 14 de julio de 2011

Mandar Mail HTML desde SQLserver

Este es un simple script para enviar un mail desde sql server (2005/2008) con un formato un poco mas amigable:

DECLARE @strMensaje VARCHAR(MAX);
SET @strMensaje = '<p><i><H3>El motivo de este correo es informarte que ha sido seleccionado para segunda entrevista.</H3></i></p>'
SET @strMensaje = @strMensaje + '<p><i><H3>Esperamos se comunique a la brevedad.</H3></i></p>'
SET @strMensaje = @strMensaje + '<p><i><H3>¡Muchas gracias!</H3></i></p>'
SET @strMensaje = @strMensaje + '<p> </p>'
SET @strMensaje = @strMensaje + '<p><b><H4>Equipo de Selección RRHH(FIRMA)<BR>EMPRESA BLABLA Inc.<BR>Santa Ana 2058, 2do. Piso, CP X5000ACE - Córdoba, Argentina</H4></b></p>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'perfilMail1',
@recipients = 'pepe@pepe.com',
@body = @strMensaje ,
@body_format = 'HTML',
@subject = 'Prueba Mail HTML'

Y este es el resultado:

De: NO REPLY
Enviado el: Jueves, 14 de Julio de 2011 12:01 p.m.
Para: pepe@pepe.com
Asunto: Prueba Mail HTML

El motivo de este correo es informarte que ha sido seleccionado para segunda entrevista.

Esperamos se comunique a la brevedad.

¡Muchas gracias!

Equipo de Selección RRHH(FIRMA)
EMPRESA BLABLA Inc.
Santa Ana 2058, 2do. Piso, CP X5000ACE - Córdoba, Argentina

fuente: http://msdn.microsoft.com/es-es/library/ms190307.aspx

martes, 19 de abril de 2011

Obtener cuanto ocupa una tabla y cada uno de los indices de la misma

Por medio de este script podemos obtener el espacio en disco que ocupa una tabla y el detalle del espacio ocupado por cada uno de los indices de dicha tabla:

RESULTADO:
name rows reserved data index_size unused
------- ----------- ------------------ ------------------ ---------------- ------------------
Client 10580220 2809928 KB 1408328 KB 1384496 KB 17104 KB

IndexName IndexSizeKB
------------------------------- --------------------
IX_ForDataView 933944
IX_tuning_id_include_username 445144
PK_Client 1413736

SCRIPT: (en la variable @strNombreTabla se indica la tabla a analizar)
DECLARE @strNombreTabla VARCHAR(50)
SET @strNombreTabla = 'Client'

EXEC sp_spaceused @strNombreTabla
SELECT i.name AS IndexName, SUM(s.used_page_count) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.[object_id] = object_id(@strNombreTabla )
GROUP BY i.name ORDER BY i.name

jueves, 14 de abril de 2011

Función Recursiva SPLIT , transformar una cadena de texto separada por un caracter (ej coma) en una tabla

Por medio de la sig. función (del tipo recursiva) podemos transformar una cadena de texto en tabla, ej:

Declare @stringtest as varchar(8000)

set @stringtest ='1,2,3,4,5,6,7,8,9'

Select * from [dbo].[Split_returns_Table](@stringtest,',')

RESULTADO:

Id
--
1
2
3
4
5
6
7
8
9

(9 row(s) affected)

FUNCION:

CREATE FUNCTION [dbo].[Split_returns_Table]

(

@Id varchar(8000),

@Separador varchar(8) --= ','

)

RETURNS TABLE

RETURN

(

WITH SplitETC (Inicio, Fin)

(

SELECT 1,

CHARINDEX(@Separador, @Id)

UNION ALL

SELECT Fin + LEN(@Separador),

CHARINDEX(@Separador, @Id, Fin + LEN(@Separador))

FROM SplitETC

WHERE Fin > 0

)

SELECT RTRIM(LTRIM(SUBSTRING(@Id, Inicio, Largo))) Id

FROM (SELECT Inicio,

CASE WHEN Fin IS NULL OR Fin = 0 THEN

LEN(@Id) + 1

ELSE

Fin

END - Inicio AS Largo

FROM SplitETC /*OPTION (MAXRECURSION 0) */) AS Split

)

miércoles, 23 de marzo de 2011

Función SQL para quitar Acentos (y pasa a mayusculas)

Por medio de la sig función podemos usarla para que le quite los acentos a cadenas de caracteres.

Uso:
Select dbo.replaceAccentCharAndEnieUpper('123ÁáéíóúñÑ456')

Resultado:
123AAEIOUNN456

Función:

CREATE FUNCTION replaceAccentCharAndEnieUpper (@source as varchar(255))
RETURNS varchar(255) AS
BEGIN

declare @charList as varchar(20)
declare @temp as varchar(255)
declare @i as int

set @temp = @source
set @charList = 'aeiou'

set @i = 0
while @i <= len(@charList)
begin
set @temp = replace(@temp, substring(@charList, @i, 1) , substring(@charList, @i, 1) )
set @i = @i + 1
end
set @temp = Replace(@temp, '’', '''')
set @temp = Replace(@temp, 'ñ', 'n')

set @temp = Upper(@temp)

return @temp

END

Fuente:

http://us.generation-nt.com/answer/removing-accents-doing-accent-insensitive-processing-help-151335981.html

jueves, 10 de marzo de 2011

Consulta para analizar el uso de Indices en una tabla, determinar índices no usados

Cuando en una tabla con muchas filas tenemos varios índices sin usar nos genera dos grandes problemas , uno el espacio desperdiciado por los índices que no se usan y el otro y tal vez mas importante es el consumo que generan estos índices cuando se le hace un Insert o un Update en la tabla.

Por medio de esta consulta podemos corroborar la cantidad de usos que se le dieron a los índices de cierta tabla.

Se debe prestar atención a las columnas user_seeks, user_scans, user_lookups, las mismas indican la cantidad de veces que se usaron desde el ultimo reinicio del servidor, si las tres columnas están en 0 se podría borrar el índice ya que esta consumiendo recursos y no se está usando.

(Mas info http://msdn.microsoft.com/en-us/library/ms188755.aspx)

Remplazar 'empleado' por el nombre de la tabla a analizar:

Declare @table as nvarchar(200)

Set @table='empleado'

SELECT i.name,s.*
FROM sys.indexes i
INNER JOIN sys.objects o ON o.object_id = i.object_id
JOIN sys.dm_db_index_usage_stats s
ON i.object_id=s.object_id AND i.index_id=s.index_id AND database_id = DB_ID()
WHERE objectproperty(o.object_id,'IsUserTable') = 1
and object_name(i.object_id) =@table
order by user_seeks,user_scans,user_lookups