Creacion de funciones

Son rutinas que constan de una o mas instrucciones que sirven para encapsular codigo y poder reutilizarlo, puede tomar 0 o más parámetros de entrada y devuelve un valor escalar o una tabla. Los parámetros de entrada pueden ser de cualquier tipo excepto timestamp, cursor o table.

Sintaxis: MSDN

Ejemplo:

Creación de una función escalar:

CREATE FUNCTION BDs(@COLLATION_NAME VARCHAR(50) = NULL) RETURNS INT
AS
BEGIN
DECLARE @RET INT
IF @COLLATION_NAME IS NULL
SET @RET = (SELECT COUNT(*) FROM sys.databases WHERE COLLATION_NAME IS NULL)
ELSE
SET @RET = (SELECT COUNT(*) FROM sys.databases WHERE COLLATION_NAME = @COLLATION_NAME)
RETURN @RET
END
GO

Creación de una funcion con valores de tabla en linea:

CREATE FUNCTION BDs(@COLLATION VARCHAR(50)) RETURNS TABLE
AS
RETURN(SELECT * FROM SYS.DATABASES WHERE COLLATION_NAME=@COLLATION)
GO

Creación de una función con valores de tabla de varias instrucciones:

CREATE FUNCTION BDs(@COLLATION VARCHAR(50))
RETURNS @RET TABLE (NAME VARCHAR(50) PRIMARY KEY)
AS
BEGIN
IF(@COLLATION='')
BEGIN
INSERT @RET
SELECT [NAME] FROM SYS.DATABASES WHERE COLLATION_NAME IS NULL
END
ELSE
BEGIN
INSERT @RET
SELECT [NAME] FROM SYS.DATABASES WHERE COLLATION_NAME = @COLLATION
END
RETURN
END
GO

Procedimientos almacendados parametrizados

Los procedimientos almacenados pueden tener parámetros de entrada y de salida para comunicarse con el programa que los llama.

Sintaxis: MSDN

Ejemplo:
CREATE PROCEDURE BDs
(@COLLATION_NAME VARCHAR(50) = NULL,
@RET INT = 0 OUTPUT)
AS
IF @COLLATION_NAME IS NULL
BEGIN
SELECT * FROM sys.databases WHERE COLLATION_NAME IS NULL
SET @RET = (SELECT COUNT(*) FROM sys.databases WHERE COLLATION_NAME IS NULL)
END
ELSE
BEGIN
SELECT * FROM sys.databases WHERE COLLATION_NAME = @COLLATION_NAME
SET @RET = (SELECT COUNT(*) FROM sys.databases WHERE COLLATION_NAME = @COLLATION_NAME)
END
RETURN @RET + 1
GO

Se crea un procedimiento almacenado que acepta un parámetro para filtrar la lista de bases de datos por el collation y retorna en un segundo parametro la cantidad de bases de datos con ese collation, como ejemplo retorna la cantidad de bases de datos mas 1 como valor de retorno.

Procedimientos almacendados

Un procedimiento almacenado es una colección de instrucciones con nombre de sentencias T-Sql.
Ventajas:

• Encapsula la funcionalidad de negocio y crea lógica de aplicación reutilizable.
• Evita la exposición de la estructura de la BD a los usuarios.
• Proporciona mecanismos de seguridad (acceso a tablas a las que no se tiene acceso real a traves de los procedimientos almacenados.)
• Mejorar el rendimiento
• Reducir tráfico en la red
• Reducir vulneravilidad a ataques mediante instrucciones SQL incrustadas en los parámetros.

Creación de procedimientos almacenados.

Sintaxis: MSDN

Ejemplo:

CREATE PROCEDURE BDs

AS

SELECT * FROM sys.databases

GO

Modificación de procedimientos almacenados:

Sintaxis: MSDN

Ejemplo:

ALTER PROCEDURE BDs

AS

SELECT * FROM sys.databases ORDER BY name

GO

Ejecución de un procedimiento almacenado:

Sintaxis: MSDN

Ejemplo:

Exec BDs

Eliminacion de procedimientos almacendos:

Antes de borrar cualquier procedimiento almacenado es recomendable ejecutar el procedimiento almacenado de la BD master sp_depends pare resolver si existe algun procedimiento que dempenda de este:

EXEC sp_depends @objname = N'BDs'

Sintaxis: MSDN

Ejemplo:

DROP PROC BDs

Creación de tablas con particiones

Para crear una tabla con particiones tendremos que crear una función de partición y un esquema de partición.

Para crear una función de partición usaremos la instrucción CREATE PARTITION FUNCTION:

Sintaxis: MSDN

Ejemplo:

CREATE PARTITION FUNCTION pf_FechaNacimiento (datetime)
AS RANGE RIGHT
FOR VALUES('01/01/2000', '01/01/2005', '01/01/2010', '01/01/2015', '01/01/2020')
GO

El siguente paso sería crear un esquema de partición con CREATE PARTITION SCHEME

Sintaxis: MSDN

Ejemplo:

CREATE PARTITION SCHEME ps_FechaNamimiento
AS PARTITION pf_FechaNacimiento
TO (fg1, fg2, fg3, fg4, fg5, fg6, fg7)

Con esto habremos creado una funcion de particion con 6 rangos y un esquema de particiones con 7 grupos de ficheros, el séptimo sería el grupo de archivos SIGUIENTE que se utilizaría en el caso de crear una partición mas a la función de partición.

Creación de tablas

Al crear una tabla de base de datos hay que indicar el nombre de la tabla, los nombre de las columnas, sus tipos de datos, la capacidad de aceptar o no valores null, la intercalación de los datos, etc.

La intercalación es el criterio de ordenación de los datos en el campo. Si no se especifica intercalación se utilizará la intercalación definida por defecto en la BD.

Existen tipos de columnas especiales:

• COLUMNAS CALCULADAS: Es una columna virtual, que no se almacena en BD. SQL Server utiliza una formula creada para calcularla.
• COLUMNAS DE IDENTIDAD: Se crean con la propiedad identity. Contiene un valor secuencial generado automaticamente por Sql Server. Usarlas como claves primarias puede mejorar el rendimiento y los costes de mantenimiento de la BD.
• COLUMNAS TIMESTAMP: Las columnas timestamp tienen una marca de tiempo generado automáticamente.
• COLUMNAS UNIQUEIDENTIFIER: se definen como uniqueidentifier. Se almacenan GUID que son udentigicadores unicos globales. Se puede generar un GUID con la función NEWID de T-SQL.

Sintaxis: MSDN

Ejemplo:

CREATE TABLE dbo.CONTACTO(
id_contacto int IDENTITY NOT NULL,
Nombre varchar(50) NOT NULL,
Apellidos varchar(100) NULL,
Telefono varchar(12) NULL,
Email varchar(50) NULL,
Web varchar(256) NULL,
timestamp )
GO

Tipos de datos de Alias

Son tipos de datos personalizados que nos permiten guardar coherencia de los datos cuando utilizamos los mismos elementos en tablas distintas de la base de datos.

Sintaxis: MSDN
Ejemplo:
CREATE TYPE dbo.Nombre FROM varchar(50) NOT NULL

Hay que tener en cuenta de que no existe la instruccion ALTER TYPE por los que para cambiar un tipo habra que usar la instruccion DROP TYPE y previamente habra que quitarla de todas las tablas.

Instantaneas de bases de datos

Una instantanea de una base de datos es una vista estática de solo lectura de una base de datos

Sintaxis: MSDN
Ejemplo:
CREATE DATABASE JABASS ON
( NAME = JABASS,
FILENAME = 'C:\Archivos de programa\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\JABA.ss')
AS SNAPSHOT OF JABA;
GO

Creacion de esquemas de bases de datos

Los esquemas nos sirven para organizar las bases de datos tal y como los namespaces nos permiten organizar nuestras bibliotecas de clases cuando programamos.

Sintaxis: MSDN
Ejemplo:

USE DATOS
GO
CREATE SCHEMA [Marketing]
GO

Creación de grupos de archivos

Los grupo de archivos nos sirven para controlar la posición física de los objetos en la base de datos, lo cual nos permitira mejorar el rendimiento de la base de datos.
Normalmente el grupo de archivos principal tendrá la extension mdf y los secundarios ndf.

Sintaxis: MSDN
Ejemplo:
USE master
GO
ALTER DATABASE [JABA] ADD FILEGROUP SECONDARY
GO
ALTER DATABASE [JABA]
ADD FILE
( NAME = 'JABA2',
FILENAME = N'C:\Archivos de programa\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\JABA2.ndf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 0),
( NAME = 'JABA3',
FILENAME = N'C:\Archivos de programa\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\JABA3.ndf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 0)
TO FILEGROUP SECONDARY
GO
ALTER DATABASE [JABA] MODIFY FILEGROUP SECONDARY DEFAULT
GO

Con los grupos de archivos se podría:

• Almacenar los datos de lectura/escritura de los de solo lectura.
• Almacenar los indices y los datos en discos separados.
• Hacer copias de seguridad o restaurar archivos individuales o en grupos de archivos en lugar de copias completas.
• etc...

Creacion de un base de datos con SQL Server 2005

Consideraciones a tener en cuanta a la hora de crear una BD:

• Propósito: Las Bds OLAP y OLTP tienen distintos requisitos de diseño.
• Rendimiento: Las Bds OLTP suelen tener un requisito de rendimiento alto en cuanto al numero de transacciones a procesar por minuto, por lo tanto requiere un nivel de normalización adecuado.
• Crecimiento de la necesidad de almacenamiento de la BD: Hay que tener en cuenta las cantidades de datos a a procesar para determinar las necesidades de hardware. Asi mismo se puede configurar las bds para que crezca su tamaño en disco automáticamente aunque esto repercute negativamente en el rendimiento, seria deseable supervisar la necesidad de ajustar el tamaño de las bds en disco.
• Ubicación de los archivos: Seria deseable la utilización de distintos discos físicos para que SQL Server optimize el uso de los cabezales de los discos.

Sintaxis: MSDN

Ejemplo:

CREATE DATABASE DATOS ON PRIMARY

( NAME='DATOS',

FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\DATOS.MDF',

SIZE=5MB,

MAXSIZE=UNLIMITED,

FILEGROWTH=0),

FILEGROUP SECONDARY

( NAME='DATOS2',

FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\DATOS2.NDF',

SIZE=5MB,

MAXSIZE=UNLIMITED,

FILEGROWTH=0)

LOG ON

( NAME='DATOS_LOG',

FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\DATOS_LOG.LDF',

SIZE=1MB,

MAXSIZE=UNLIMITED,

FILEGROWTH=0)

GO