Transacciones en SQL Server

Sintaxis: MSDN

Ejemplo:
BEGIN TRY
BEGIN TRAN
INSERT INTO CLIENTES (ID) VALUES ('tabla1')
INSERT INTO CLIENTES (ID) VALUES ('tabla2')
INSERT INTO CLIENTES (ID) VALUES ('tabla3')
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH

Manejo de excepciones

En SQL Server existen los bloques TRY-CATH para el control de excepciones.

Sintaxis: MSDN

Ejemplo:
BEGIN TRY
INSERT INTO CLIENTES (ID) VALUES ('tabla')
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH

El nuevo HTML5

El HTML5 es una nueva versión del popular lenguaje HTML, que se empezó a desarrollar en 2004 en colaboración entre W3C HTML WG y WHATWG.

Lo más destacable de esta nueva versión son las nuevas etiquetas que se introducen para darle un aspecto más lógico y entendible al código, y como se trata de un estándar, que pueda ser interpretado casi por igual por los navegadores más importantes, Fire Fox, Internet Explorer, Safari y Opera. No comentan nada del Google Chrome, pero es lógico porque es todavía muy nuevo y no se ha difundido lo suficiente, aunque no planteará problemas en principio, ya que lo visto hasta el momento es que interpreta las páginas de forma muy parecida a Safari o Fire Fox.

Con estas nuevas etiquetas el uso de los difundidos div se reducirá considerablemente, ya que sustituiremos los típicos < id="header"> y < id="footer"> por etiquetas <header> y <footer>. Con esto se consigue lo comentado anteriormente, un aspecto más fácilmente entendible del código de cara a un futuro mantenimeinto.

Os muestro un ejemplo de estos cambios:


También tendremos etiquetas nuevas para vídeo y audio, con sus correspondientes atributos.

En este enlace se pueden ver otros cambios de la nueva versión.

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

Fragmentación de XML mediante OPENXML

Fragmentar XML es el proceso de transformar datos en formato XML a filas.
El proceso de fragmentación se realiza mediante los siguientes pasos:
  • Obtención de el documento XML.
  • Generación de una representación interna de arbol, mendiante el procedimiento almacenado sp_xml_preparedocument.
  • Recuperar un conjunto de filas del arbol mediante una sentencia XPath.
  • Procesar los datos del conjunto de filas temporal generados por OPENXML para insertar, seleccionar, eliminar o modificar filas en una tabla permanente.
  • Destruir el arbol interno cuando ya no es necesario con el procedimiento almacenado sp_xml_removedocument

Sintaxis: MSDN

Ejemplo:


DECLARE @dxml varchar(1000);
DECLARE @idoc integer;
SET @dxml = '
';
EXEC sp_xml_preparedocument @idoc OUTPUT, @dxml;
SELECT * FROM OPENXML(@idoc, '//CLIENTES/CLIENTE') WITH (id INT, numero VARCHAR(50), nombre VARCHAR(50), direccion VARCHAR(50), telefono VARCHAR(50))
EXEC sp_xml_removedocument @idoc;
GO

Recuperacion de XML mediante FOR XML

Se puede usar la clausula FOR XML en las sentencias SELECT de Transact-SQL para recuperar los datos en formato XML en lugar de filas y columnas.

Sintaxis: MSDN

Ejemplos:

SELECT * FROM CLIENTES FOR XML RAW
GO
SELECT * FROM CLIENTES FOR XML AUTO
GO
SELECT 1 AS TAG,
NULL AS PARENT,
ID AS [CLIENTE!1!ID],
NUMERO AS [CLIENTE!1!NUMERO],
NOMBRE AS [CLIENTE!1!NOMBRE!ELEMENT],
DIRECCION AS [CLIENTE!1!DIRECCION!ELEMENT],
TELEFONO AS [CLIENTE!1!TELEFONO!ELEMENT]
FROM CLIENTES FOR XML EXPLICIT
GO
SELECT
ID "@ID",
NUMERO "@NUMERO",
NOMBRE "NOMBRE",
DIRECCION "DIRECCION",
TELEFONO "TELEFONO"
FROM CLIENTES FOR XML PATH('CLIENTE')
GO

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...

Procedimientos almacenados del sistema

Son procedimentos almacenados para recuperar metadatos de la BD.

  • sp_databases: devuelve una lista de bases de datos de la instancia de SQL Server.
  • sp_store_procedures: devuelve una lista de los procedimientos de la base de datos actual.
  • sp_help: proporciona información acerca de un objeto de la base de datos.

Sintaxis: MSDN

Ejemplo:
EXEC sp_databases
GO

Funciones de metadatos

Son funciones que devuelven información acerca de la base de datos y sus objetos:

  • DB_ID:
  • DN_NAME:
  • FILE_ID:
  • FILE_NAME:
  • FILEGROUP_ID:
  • FILEGROUP_NAME:

Vistas de catálogo

Con las vistas de catalogo podemos consultar metadatos acerca de los objetos de la base de datos:

  • sys.databases
  • sys.database_files
  • sys.columns
  • sys.events
  • sys.idexes
  • sys.tables
  • sys.views
  • sys.schemas
  • sys.database_permissions
  • sys.database_principals
  • sys.database_role_members

Opciones de bases de datos

Estas son algunas de las opciones de bases de datos mas usadas:

  • AUTO_CREATE_STATICS:
  • AUTO_UPDATE_STATICS:
  • AUTO_CLOSE: Cierra automaticamente la base de datos al salir el último usuario. La propiedad Auto_Close es una de las tantas que existen a nivel de configuración de base de datos que se suele dar poca o nada de importancia, motivos?, absoluto desconocimiento de su impacto en la perfomance. Cuando la propiedad Auto_Close está establecida a True, SQL Server cierra los archivos de la base de datos y liberará sus recursos asociados cuando el último usuario cierra su conexión con ella. Esta medida puede conducir a ciertos ahorros en memoria en situaciones de RAM escasa, sin embargo, el siguiente usuario experimentará, al conectarse, las demoras asociadas con la reapertura de la base de datos, es decir el siguiente usuario demorará más en establecer una conexión a la base de datos. Cuidado, he dicho volver establecer un conexión a la base de datos más no al servidor de datos, cuestiones que vienen a ser diferentes. Por lo tanto, se debe evaluar si esto es realmente útil en la configuración de cada base de datos, aunque debo admitir que el ahorro de memoria será insignificante (se libera de 12KB a 20KB), no vale la pena en la mayoría de escenarios configurar este valor en True. Mi recomendación es casi siempre (por no decir siempre) deshabilitar la opción Auto_Close. En realidad se gana muy poco en ahorro de memoria al ajustarlo en True, y se pierde mucho en la performance. Tenga mucho cuidado, aunque la configuración por defecto es False, en el mundo real he podido encontrarme con base de datos ajustada esta propiedad en Auto_Close en True. La verdad es que es bastante importante el aumento de rendimiento. También apuntar que mientras que en las versiones comerciales de SQL Server la propiedad está establecida por defecto a FALSE, no sucede lo mismo en SQL Server Express, con la consiguiente merma del rendimiento. La buena noticia es que en SQL Server 2008 Express Edition esta propiedad ya viene en False por defecto.
  • AUTO_SHRINK: El archivo de base de datos es candidato a reducción periodica.
  • ONLINEOFFLINEEMERGENCY: Controla si la base de datos tiene o no conexión.
  • READ_ONLYREAD_WRITE: Controla si los usuarios pueden o no modificar los datos.
  • SINGLE_USERRESTRICTED_USERMULTI_USER: Define que usuarios pueden acceder a la base de datos.
  • CURSOR_CLOSE_ON_COMMIT: Cierra automaticamente los cursores abiertos al acabar una transaccion.
  • CURSOR_DEFAULT_LOCAL: Limita el ambito del cursor.
  • CURSOR_DEFAULT_GLOBAL: El ambito del cursor el global a la conexión.
  • ANSI_NULL_DEFAULT: Define el valor predeterminado de aceptar nulos en los campos de las tablas.
  • ANSI_NULLS: Cuando es ON las coparaciones con null devuelven null.
  • RECURSIVE_TRIGGERS: Controla si se permite la activacion recursiva de desencadenadores AFTER.

Sintaxis: MSDN

Ejemplo:
ALTER DATABASE [DATOS] SET AUTO_SHRINK OFF
GO

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

Recuperar la contraseña de administrador de Joomla

Sé que esto no tiene nada que ver con .net, pero es un artículo muy interesante que ayuda a recuperar (o modificar) la contraseña de administrador de tu sitio web hecho con Joomla!

Conexiones razonables: Recuperar la contraseña de administrador de Joomla

Buscar este blog

Seguidores

Blogs amigos