Es bueno usar stores Procedures?

Bueno esta pregunta es la de muchos, aca hay varias opiniones , algunos defienden mucho a los Stores Procedures , otros nunca los usan y otros los usan en algunas ocaciones.


La idea de este post es ver para que son utiles estos objetos.


Un Store procedure no es mas que un objeto creado dentro del motor de base de datos, este objeto contiene un set de instruciones T-SQL (en el caso de SQLServer), pero para que son realmente?


Bueno, el uso de stores procedures tiene algunas ventajas, como las siguientes:




  1. Seguridad


  2. Centralizacion del codigo


  3. Performance

Seguridad: nos dan la posibilidad de no darles acceso directo a los usuarios a las tablas del sistema, sino que por medio de un Store (lo podriamos ver como un puente) dar acceso a ejecutarlo pero que el usuario no tenga acceso directos sobre las tablas, esto tiene enormes ventajas de seguridad ya que no es muy buena idea que un usuario se pueda conectar al servidor y poder hacer un select por ej sobre la tabla Customers y le muestre todos los campos o bien insertar valores como quiere.


Centralizacion del codigo: Los Stores estan dentro de la base de datos con lo cual el codigo esta centralizado, si lo tuvieramos en las aplicaciones ese codigo podria estar en mas de un lugar y seria mas complicado luego a la hora de hacer mantenimiento, por ej: agregar una columna: si manejamos Stores es muy simple poder ver cuales afectaban a la tabla modificada para poderlos tocar, de la otra manera hay que hacer un rastreo mucho mayor y mas complicado a la vez.


Performance: Depende del Store que hagamos (si hace un simple insert no veremos mucha diferencia) la performance es mayor que si ejecutamos las sentencias desde la aplicacion directamente, esto tiene una explicacion simple y es que los Stores quedan compilados y el motor de base de datos no debe calcular por cada ejecucion (a menos que se lo indiquemos) que vuelva a calcular los planes, esto hace que el proceso sea mas eficiente, si tenemos 100 usuarios que tienen codigo en su aplicacion y los tiran lo mismo hara 100 veces el mismo calculo el motor, pero si en luagar de tenerlo asi esta en un Store el quedara en cache y ademas compilado haciendo que la performance mejore.


 


Bueno estas parecen ser todas buenas noticias, pero porque mucha gente no los utiliza? bueno aqui un par de argumentos de la mayoria de esas personas:


1) Me ato a un motor de base de datos


2)No conozco tan bien T-SQL como mi lenguaje de programacion


3)No es tan poderoso T-SQL como mi lenguaje de programacion


4)Tengo que crear muchos objetos en la base de datos (minimanente 4 Stores por tabla  CRUD )


 


Bueno, esto es algo de cierto y algunas cosas que hay que analizar bien, vayamos por partes


1) Es  cierto, te atas a un motor, pero cual es el problema? con ese criterio tampoco me deberia atar a un SO, a un lenguaje de programacion, etc, etc, ademas si quiero hacer un sistema muy generico al final no terminare aprovechando lo mejor de cada tecnologia con lo cuel sere muy ineficiente, yo soy de la idea de que se debe aprovechar lo mejor de cada tecnologia y si quiero un sistema que funcione con mas de un motor de base de datos, entonces debere escribir para cada version lo mejor de cada uno, el trabajo es mas es cierto pero de calidad 🙂


2)Es una gran realidad, no mucha gente conoce bien TSQL, en 2000 esto era un gran problema pero en 2005 si no conoces bien TSQL y queres hacer Stores y centralizar podes hacerlos en .NET :), no es lo mas recomendado ya que CLR dentro de SQL se lo deberia usar para cuando TSQL no puede resolver lo que necesito.


3) Bueno, esta limitacion con SQL2005 ya ha sido superada, SQL2005 soporta CLR dentro con lo cual podemos programar nuestros objetos (Stores, Funciones, Tipos de datos, Triggers y funciones de agragacion) con CLR


4) Cual es el problema de tener muchos objetos? el motor no tiene problemas por eso 🙂 ahora si la tarea es muy laboriosa entonces porque no usar algun generador de codigos ? hay muchos y hasta Free que con solo el hecho de seleccionar una Tabla genera los Stores del CRUD y si no me gustan o bien quiero mas es solamente cuestion de generar mis plantillas


 


Bueno, es un tema que siempre tendra discusion pero la verdad que yo defiendo el uso de Stores porque me da mas beneficios que desventajas, ademas esas desventajas las puedo saltear.


Me gustaria saber la opinion de ustedes, si ven alguna otra desventaja o ventaja asi la discutimos y vamos entre todos aprendiendo 🙂 


 


 


 

Paginando dentro de SQLServer 2005

En muchas ocaciones es necesario hacer una paginacion, esta podria estar del lado de la aplicacion o bien del servidor, en este post no haremos un debate de donde es mejor ponerlo pero si mostrare dos ejemplos de paginacion para SQL2005


Primer Ejemplo


 


Use AdventureWorks


go

declare @pageNum int,@RowsPerPage int

set @pageNum = 2


Set @RowsPerPage = 20


select * from


(


SELECT row_number() over (order by CustomerID) as rownum,


Sales.SalesOrderHeader.* FROM Sales.SalesOrderHeader


) as p


where rownum between


(@pageNum 1)*@RowsPerPage + 1


and @pageNum*@RowsPerPage


Este ejemplo es un simple select que utiliza nuevas funciones de SQL2005 como son las de ranking (row_number() en este caso) y se definen 2  variables donde una es la cantidad de registros por pagina y la otra es a la pagina que deseo entrar.


Segundo Ejemplo:


CREATE FUNCTION dbo.fn_GetTopOrders(@custid AS int, @n AS INT)

RETURNS TABLE

AS


RETURN


SELECT TOP(@n) *


FROM Sales.SalesOrderHeader


WHERE CustomerID = @custid


ORDER BY TotalDue DESC


GO


 


Probando el ejemplo:


 


 


declare @pageNum int,

@RowsPerPage int

set @pageNum = 4


Set @RowsPerPage = 20;


WITH CTE as


(


SELECT C.CustomerID,


O.TotalDue,


rank() over (partition by C.CustomerID order by TotalDue) as rankOfSale


FROM


AdventureWorks.Sales.Customer AS C


CROSS APPLY


AdventureWorks.dbo.fn_GetTopOrders(C.CustomerID, 3) AS O


)


Select * from


(


SELECT row_number() over (order by CustomerID) as rownum,


CustomerID,


[1] as Rank1,


[2] as Rank2,


[3] as Rank3


from


CTE


pivot(

sum(TotalDue) for rankOfSale in ([1],[2],[3])

) as pvt


) as P


where rownum between


(@pageNum 1)*@RowsPerPage + 1


and @pageNum*@RowsPerPage


Espero les sea de utilidad


 


 

FAQ SqlServer Express 2005

La idea de este post es responder algunas de las preguntas mas frecuentes que se hacen en distintos foros sobre este producto, tratare de irlo manteniendo mas seguido 😉




  1. Que costo tiene SQLServer Express?




      • Es un producto gratuito


  2. Se puede instalar en un equipo donde tenga SQL2000?




      • Si, simplemente se debera instalar con otro nombre de instancia


  3. Necesito aprender algo distinto de las otras ediciones?




      • No, SQLExpress es simplemente una edicion limitada con respecto a sus caracteristicas


  4. Una base de datos en SQLExpress se puede migrar a un Standard o Entherprise?




      • Sin ningun problema, simplemente hay que hacer un backup y restore o bien un Detach / Attach


  5. El codigo de un sistema en Express debe ser modificado para escalar a otras ediciones de SQL2005? 




      • No, el codigo que funciona en Express funcionara perfectamente en las otras ediciones de SQL2005


  6. SQL Express dispone de herramientas administrativas?




      • Si, dispone del management Studio Express edition


  7. Cuales son las limitaciones mas significativas de esta edicion?




      • Tamaño maximo por base de datos de 4GB


      • Soporte para un solo procesador


      • Soporte hasta 1GB de RAM


      • No soporta Analysis Service


      • No dispone de Agent


      • No soporta HTTP endpoint de SQL


      • No tiene el servicio de Integration Service


  8. SQL Express dispone de Reporting Service?




      • Si, hay que instalar la edicion Advance de Express


  9. Que cosas de las otras ediciones soporta?




      • TSQL (es el mismo)


      • CLR dentro del motor


      • Nuevos Tipos de datos (XML – Varchar(Max), etc)


      • Transacciones


      • Stores Procedures


      • Funciones


      • Triggers


      • Seguridad integrada y de Windows


  10. Si tengo un sistema en SQLExpress 2005 como lo paso a un 2000?




      • Esto es indistinto de la edicion, pero para pasarlo a 2000 hay que migrar las estructuras y los datos, si se han utilizados funciones de 2005 hay que recodificar para 2000

Es bueno usar CLR dentro de SQLServer2005

Bueno esta fue la primer pregunta que me hice cuando lo vi en la beta1 , la verdad que es un tema bien discutible y sobre todo para aquellos que son bien tradicionales y solo usan la base de datos con tablas , índices y quizás algunos Stores Procedures.


Bueno mi opinión es que si las cosas están es para usarlas no? ahora la pregunta seria donde y como.


Con CLR dentro de SQL2005 puedo generar:



  • Stores Procedures

  • Funciones

  • Tipos de datos

  • Funciones de agregación

  • Triggers

Las funciones de agregación y los tipos de datos “solo” se pueden hacer vía CLR y no TSQL, el resto es viable hacerlo de ambas maneras.


Bueno pareciera ser entonces que el CLR me es muy útil solamente para estas 2 cosas que en versiones anteriores no tenía la posibilidad de hacer, pues bien, es una muy buena idea usar el CLR para ello, cuántas veces hemos querido crear nuestro propio tipo de datos, por ej. EMAIL o hasta el tipo de datos hora no, sabemos que SQL tiene el tipo de datos Datetime que incluye fecha y hora.


Bueno con las funciones de agregación pasa algo similar, quizás necesitemos hacer nuestra propia función de promedio, me ha tocado implementar en un cliente que hacia producción de piezas tener una función de agregación para calcular los tiempos Standard de los productos, este cálculo era por medio de una terrible formula


Pero que sucede con el resto, sería buena idea usarlos en SP, UDF y triggers? bueno claro que si también pero no abusar, SQL es eficiente y muy bueno con TSQL así que todo lo que podamos resolver con TSQL hagámoslo con él, pero sabemos que TSQL no es un lenguaje de programación y que muchas veces necesitamos hacer cosas más complejas que con TSQL son  muy difíciles o imposibles de hacer.


Un ejemplo de esto sería el de tener una función que comprima texto, imaginemos que queremos guardar imágenes dentro de una base de datos (ya hablaremos en otro post de esto) pero no queremos que ocupen tanto, entonces podríamos tener una función de CLR dentro del SQLServer donde al recibir el parámetro por ej. image lo comprima y lo guarde así en la base de datos, luego tener otra función para hacer la operatoria inversa no.


Bueno esta también sería una excelente manera de implementar quizás CLR.


Como podrán ver CLR no es tan malo como algunos piensan, ahora que cosas quizás no sea buena idea implementar? una seria por ej.: tratar de implementar un trigger para control de auditorías, esto no es necesario hacerlo desde CLR , implementar un tipo de datos llamado Cliente como si fuere un objeto.


En resumen , CLR es un valor agregado y no un reemplazo de TSQL.


Me gustaría saber las experiencias de ustedes y vuestras opiniones, aquí abajo les dejo un link de cómo desarrollar el tipo de datos Hora con CLR en SQL2005


http://www.levelextreme.net/ViewPageArticle.aspx?Session=5A6E4C49615932576F55303D206F5130434B657132707152546E786A4D7372526A47673D3D


 

Como pasar Logins entre servidores

Hola, he vuelto de mis vacaciones [:)] y me estoy poniendo al dia, prometo tratar de escribir mucho mas seguido por aqui.


En este post voy a explicar uno de los dolores de cabeza mas frecuentes que tenemos y es el pasar los logins entre servidores.


Sabemos que en SQLServer hay 2 tipos de login, los de Windows y los de SQL, los primeros son usuarios del dominio que tambien tienen acceso al servidor de base de datos y los segundos son usuarios que se guardan dentro del SQLServer, o sea, la password esta dentro del SQLServer , en lugar de la de windows que la controla el controlador de dominio.


Lo recomendado es tratar de tener y usar siempre usuarios Windows, la recomendacion se debe a que le dejaremos toda la administracion (complejidad de password, control de vencimientos, etc) al sistema operativo y ademas permitirle a un usuario que no deba volver a hacer login (si usara usuarios SQL) cuando ya lo hizo al entrar a su windows.


OK, hay varios servidores donde me ha tocado trabajar donde no solo hay usuarios windows sino que tambien muchos SQL, esto es por lo general por el tipo de aplicaciones que usan SQLServer y como han sido pensadas, pero eso ya es otro tema que discutiremos en otro momento [;)]


Bien, como hacemos si deseamos pasar los logins SQL y los de windows de un servidor a otro, esto podria ser a la misma version de SQL o bien a distintas versiones, por ej se desea migrar todo el servidor 7 y 2000 a un 2005 pero no es cuestion de solo migrar las bases sino tambien los logins entre otras cosas.


Si deseamos migrar los usuarios SQL podriamos por ej desde SQL2005 usar Integration Service (el reemplazo de DTS) y usar una tarea para esa funcion, la desventaja de hacer esto es que pasara los logins pero no las password con lo cual le deberiamos decir a todos los usuarios SQL que vuelvan a cambiar las password (nada agradable no?)


Lo que les voy a dejar es un script el cual nos permitira migrar los usuarios de un server a otro, este script no solo migra usuarios SQL sino tambien los de windows y lo mas lindo de esto es que se puede usar para migrar de 7 a 2005 , de 7 a 2000 o de 2000 a 2005 [:)]


Estos seran los pasos que debemos realizar para migrar usuarios



  1. Generamos SP del script en el servidor origen

  2. Ejecutamos el SP en el servidor origen

  3. El resultado del SP lo copiamos a un archivo .sql

  4. Ejecutamos este archivo .sql en el servidor destino

Script


USE [master]


GO


/****** Object:  StoredProcedure [dbo].[sp_hexadecimal]    Script Date:


03/23/2006 10:24:06 ******/


SET ANSI_NULLS OFF


GO


SET QUOTED_IDENTIFIER ON


GO


 


 


CREATE PROCEDURE [dbo].[sp_hexadecimal]


    @binvalue varbinary(256),


    @hexvalue varchar(256) OUTPUT


AS


DECLARE @charvalue varchar(256)


DECLARE @i int


DECLARE @length int


DECLARE @hexstring char(16)


SELECT @charvalue = ‘0x’


SELECT @i = 1


SELECT @length = DATALENGTH (@binvalue)


SELECT @hexstring = ‘0123456789ABCDEF’


WHILE (@i <= @length)


BEGIN


  DECLARE @tempint int


  DECLARE @firstint int


  DECLARE @secondint int


  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))


  SELECT @firstint = FLOOR(@tempint/16)


  SELECT @secondint = @tempint (@firstint*16)


  SELECT @charvalue = @charvalue +


    SUBSTRING(@hexstring, @firstint+1, 1) +


    SUBSTRING(@hexstring, @secondint+1, 1)


  SELECT @i = @i + 1


END


SELECT @hexvalue = @charvalue


 


 


set ANSI_NULLS ON


set QUOTED_IDENTIFIER ON


go


 


 


create PROCEDURE [dbo].[sp_help_revlogin] @login_name sysname = NULL AS


DECLARE @name    sysname


DECLARE @xstatus int


DECLARE @binpwd  varbinary (256)


DECLARE @txtpwd  sysname


DECLARE @tmpstr  varchar (256)


DECLARE @SID_varbinary varbinary(85)


DECLARE @SID_string varchar(256)


 


 


IF (@login_name IS NULL)


  DECLARE login_curs CURSOR FOR


    SELECT sid, name, xstatus, password FROM master..sysxlogins


    WHERE srvid IS NULL AND name <> ‘sa’


ELSE


  DECLARE login_curs CURSOR FOR


    SELECT sid, name, xstatus, password FROM master..sysxlogins


    WHERE srvid IS NULL AND name = @login_name


OPEN login_curs


FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd


IF (@@fetch_status = 1)


BEGIN


  PRINT ‘No login(s) found.’


  CLOSE login_curs


  DEALLOCATE login_curs


  RETURN 1


END


SET @tmpstr = ‘/* sp_help_revlogin script ‘


PRINT @tmpstr  + ‘ — ‘


SET @tmpstr = ‘** Generated ‘


  + CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’


PRINT @tmpstr + ‘ — ‘


PRINT   + ‘ — ‘


PRINT ‘DECLARE @pwd sysname’ + ‘ — ‘


WHILE (@@fetch_status <> 1)


BEGIN


  IF (@@fetch_status <> 2)


  BEGIN


    PRINT


    SET @tmpstr = ‘– Login: ‘ + @name


    PRINT @tmpstr  + ‘ — ‘


    IF (@xstatus & 4) = 4


    BEGIN — NT authenticated account/group


      IF (@xstatus & 1) = 1


      BEGIN — NT login is denied access


        SET @tmpstr = ‘EXEC master..sp_denylogin ”’ + @name + ””


        PRINT @tmpstr + ‘ — ‘


      END


      ELSE BEGIN — NT login has access


        SET @tmpstr = ‘EXEC master..sp_grantlogin ”’ + @name + ””


        PRINT @tmpstr + ‘ — ‘  


      END


    END


    ELSE BEGIN — SQL Server authentication


      IF (@binpwd IS NOT NULL)


      BEGIN — Non-null password


        EXEC sp_hexadecimal @binpwd, @txtpwd OUT


        IF (@xstatus & 2048) = 2048


          SET @tmpstr = ‘SET @pwd = CONVERT (varchar(256), ‘ + @txtpwd + ‘)


— ‘


        ELSE


          SET @tmpstr = ‘SET @pwd = CONVERT (varbinary(256), ‘ + @txtpwd +


‘) — ‘


        PRINT @tmpstr


        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT


        SET @tmpstr = ‘EXEC master..sp_addlogin ”’ + @name


          + ”’, @pwd, @sid = ‘ + @SID_string + ‘, @encryptopt = ‘


      END


      ELSE BEGIN


        — Null password


        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT


        SET @tmpstr = ‘EXEC master..sp_addlogin ”’ + @name


          + ”’, NULL, @sid = ‘ + @SID_string + ‘, @encryptopt = ‘


      END


      IF (@xstatus & 2048) = 2048


        — login upgraded from 6.5


        SET @tmpstr = @tmpstr + ”’skip_encryption_old”’


      ELSE


        SET @tmpstr = @tmpstr + ”’skip_encryption”’


      PRINT @tmpstr + ‘ — ‘  


    END


  END


  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd


  END


CLOSE login_curs


DEALLOCATE login_curs


RETURN 0


 


 


Ahora debemos ejecutar como hemos mencionado anteriormente el SP_HELP_REVLOGIN y su resultado guardarlo como .sql para luego ejecutarlo en el servidor destino.


 


 


use master


go


 


EXEC sp_help_revlogin


El resultado de este SP por ej en mi caso es el siguiente:


 


/* sp_help_revlogin script —


** Generated Feb 7 2007 10:13PM on NOTEBOOK */ —



DECLARE @pwd sysname —


 


— Login: BUILTIN\Administrators —


EXEC master..sp_grantlogin ‘BUILTIN\Administrators’ —


 


— Login: maxi —


SET @pwd = CONVERT (varbinary(256), 0x010063041400C219E42B05857103FA7537624D18A08EF952471C43B39E21C47C5A098B75FAE89F36B27D912666AA) —


EXEC master..sp_addlogin ‘maxi’, @pwd, @sid = 0xF6630592B345974588C021007A10A99A, @encryptopt = ‘skip_encryption’ —


 


Como podemos obervar el SP ha recorrido todos los usuarios de mi servidor (menos el SA) y ha dado como resultado las sentencias de comando que debo ejecutar en mi servidor destino.


Las claves seran pasadas pero como se observa no podemos identificar cual es la misma, con lo cual es un proceso totalmente seguro ya que lo unico que aseguramos que pasen los logins y sus claves tambien para que el usuario se pueda conectar sin ningun problema en el servidor destino.


 


Bueno espero que el truco les sea de utilidad


 

Aun con el manejo de fechas

Pues si, hace tiempo que se viene confundiendo la gente en como se manejan las fechas dentro de SQLServer, de hecho hace unos anitos me llevo a escribir un articulo (de los tantos que hay por la web) sobre este tema. Pero aun el problema continua y es muy pero  muy frecuente en cada foro o charla que alguien pregunte como se trabaja con esto y se haga unos rollos de aquellos.


Bueno aqui voy a marcar algunos tips nuevamente:


Primero, los tipos de datos para manejar fechas en sql son el Datetime o el SmallDatetime, en ambos casos se guarda fecha/hora. Como todos sabemos hay paises donde las fechas se representan de distintas maneras, por ej los americanos usan el formato MM/dd/YYYY mientras que latonoamerica y espania usan el dd/mm/yyyy, bueno ya se empieza a complicar la cosa no?


Como resolver esto y que siempre mis fechas funcionen sin depender de donde lo esten haciendo? como todo en la vida cuando la cosa se pone feita se genera un Standard [:)] , para las fechas tenemos el ISO YYYYMMDD HH:mm:ss el cual si usamos en TODO nuestra manejo de este tipo de datos (insert, update, wheres) no tendremos de que preocuparnos y no nos aparecera el error comun de que nuestra fecha esta fuera de rango.


SQL server define el formato de la fecha segun el idioma que tenga definido el login, no tiene nada que ver con la configuracion regional de la maquina, esto es una gran confusion que hay.


Como ya mencionamos antes, las fechas se guardan tambien las horas, entonces si nos piden consultar los registros del 1/12/2006 deberiamos hacer


SELECT CAMPOS FROM TABLA WHERE FECHA >=’20061201′ AND FECHA <‘20061202’


Esto consultara todos los registos de esa fecha ya que considera todo el espectro de horas.


Algo comun es que cuando quieren consultar por ej por un mes determinado hagan algo asi como:


SELECT CAMPOS FROM TABLA WHERE MONTH(FECHA) = 4


Esto a simple vista no tiene problemas y parece simple no? pues es patetico este query, si aplican una funcion sobre un campo a la izquierda de la condicion esto hara que si hay indices por esta columna NO SE UTILICEN de forma eficiente.


Entonces para resolver este tipo de queys es aconsejable poner el rango de fechas para ese mes o si realmente quieren hacerlo de forma mas simple y sera algo recurrente, podrian crear una columna calculada en sus tablas que de como resultado el mes, a esa columna calculada le crean un indice y ahi si consumen por esa columna calculada [:)] .


Bueno espero que este tema de las fechas quede claro