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