En este Monográfico he intentado resumir experiencias realizadas con SQL-SERVER. Espero que os guste, considero que a modo de repaso e iniciación no tiene desperdicio.

SQL Server ofrece una gran variedad de soluciones. Desde el uso del gestor para desarrollar aplicaciones tradicionales en entornos no virtualizados, hasta el trabajo en la nube. Microsoft ha hecho un esfuerzo a partir de la versión 2012 en las áreas de disponibilidad, manejabilidad, programática, escalabilidad, rendimiento y seguridad, pilares básicos para todo DBA.

En la versión 2012-14 existen 3 ediciones principales, en versiones 32 y 64 bits.

Las ediciones son, estándar, BI edition (capacidades de toma de decisiones, informe y analítica) y Enterprise edition (SKU superior). Aunque también posee ediciones especializadas como la Developer, Web y la Express.

La edición Developer incluye todas las características de la Enterprise edition, pero para propósitos de desarrollo, testeo.

La edición web posee un precio más económico que el resto, creada para proveedores de hosting y entornos de servicio web. Esta edición no posee restricciones sobre el tamaño de la base de datos, aunque no posee todas las características de las ediciones superiores. Ideal para websites y aplicaciones web.

Por último la edición Express, es una versión gratuita, para proveedores  independientes de software, desarrolladores no profesionales, y aficionados. Aunque posee serias limitaciones, soporta 1 solo procesador, 1GB de memoria y el tamaño de la base de datos solo puede ser de 10 Gb. Este gestor SQL se encuentra integrado en Visual Studio.

La dirección web para consultar el modelo de licencias es de SQL-Server 2014:
http://www.microsoft.com/es-xl/server-cloud/products/sql-server/Buy.aspx#fbid=zLpgo9wCnY7

Cuando trabajamos con este potente gestor de bases de datos, podemos realizarlo asumiendo que tenemos un determinado rol. Puesto que el conocimiento de todo el producto requiere especializaciones independientes, y profesionales conocedores de cada área. Por ende, podrán existir DBA o administradores de base de datos,  desarrolladores en Transact, arquitectos de soluciones BI, analistas de bases de datos, optimizadores de rendimiento, administradores de backup, etc.

Por lo tanto, dentro de SQL Server, es preciso diferenciar lo que es el motor del SGBDR de las herramientas de manejo como pueden ser el SSMS (SQL Server Management Studio). Los motores crean instancias en el servidor. Puede existir más de una instancia ejecutándose a la vez.

Otras herramientas como el Administrador de configuración de SQL Server o SQL Server Config Manager gestiona servicios y protocolos de red utilizados por SQL Server.

El Agente SQL Server es otra herramienta que permite la ejecución de scripts programados y trabajos de mantenimiento. También se encarga de las alertas automáticas, y puede ser configurado para distribuir tareas entre servidores.

El Analizador SQL-Server es una interfaz gráfica que realiza  consultas y ofrece resultados. Analiza comandos T-SQL, la concurrencia de eventos, bloqueos, inicios de sesión, errores, datos de rendimiento. En definitiva se puede auditar la actividad del servidor SQL.

Como diseñadores y usuarios de bases de datos, la herramienta más empleada es el SSMS o las alternativas sugeridas.

Las copias de seguridad se pueden realizar desde el SSMS. También existen soluciones alternativas comerciales.

Respecto al inicio de sesión en SQL server, pueden existir 2 tipos.

Autenticar sobre la máquina/windows (directorio activo o local) o sobre el propio servidor SQL.
Al instalarse SQL Server se crean 2 inicios de sesión. La cuenta de servicio para iniciar el servicio SQL Server y el usuario sa. Este usuario no puede eliminarse ni modificarse. Solo estará disponible si no está configurada la autenticación con Windows.

En el servidor SQL Server pueden una serie de roles.
Sysadmin, Serveradmin, securityadmin, dbcreator, diskadmin, bulkadmin,processadmin, setupadmin

Cuando se crea una base de datos, existen una serie de usuarios por defecto.
El dbo corresponde al propietario. No puede ser borrado.
Guest: Permite a usuarios que no tienen cuenta en la BD, que accedan a ella, pero hay que darle permiso explícitamente.
Information_schema, Permite ver los metadatos de SQL Server
sys, Permite consultar las tablas y vistas del sistema, procedimientos extendidos y otros objetos del catálogo del sistema.

Los roles y demás operaciones contra la base de datos se pueden realizar mediante sentencias DDL o de definición.

Una base de datos puede contener múltiples Esquemas. Cada esquema tiene un propietario. Cada usuario tiene un esquema por defecto para la resolución de nombres.

SQL Server es un sistema transaccional.

Una transacción es una secuencia de operaciones realizadas como una sola unidad lógica de trabajo. Una unidad lógica de trabajo debe exhibir cuatro propiedades, conocidas como propiedades de atomicidad, coherencia, aislamiento y durabilidad (ACID), para ser calificada como transacción.

Atomicidad
Una transacción debe ser una unidad atómica de trabajo, tanto si se realizan todas sus modificaciones en los datos, como si no se realiza ninguna de ellas.

Coherencia
Cuando finaliza, una transacción debe dejar todos los datos en un estado coherente.

Aislamiento
Las modificaciones realizadas por transacciones simultáneas se deben aislar de las modificaciones llevadas a cabo por otras transacciones simultáneas. Una transacción reconoce los datos en el estado en que estaban antes de que otra transacción simultánea los modificara o después de que la segunda transacción haya concluido, pero no reconoce un estado intermedio. Esto se conoce como seriabilidad, ya que deriva en la capacidad de volver a cargar los datos iniciales y reproducir una serie de transacciones para finalizar con los datos en el mismo estado en que estaban después de realizar las transacciones originales.

Durabilidad
Una vez concluida una transacción, sus efectos son permanentes en el sistema. Las modificaciones persisten aún en el caso de producirse un error del sistema.

Cuando trabajamos en Microsoft Access, pensamos en el concepto de relación. Es decir, relacionamos tablas y solemos realizar la integridad referencial con un click desde el programa. Esto nos ofrece una integridad al modelo relacional que el propio motor Jet de Access se encarga de verificar.

En SQL-Server,  La opción Diagrama de la Base de Datos nos permite realizar las relaciones de las tablas en forma Gráfica. Para ello crearemos un diagrama de base de datos.

Existen diversos tipos de integridad. El aplicado a campos, tipos, tablas, y relaciones. Además del creado mediante procedimientos y funciones. El programador puede controlar la integridad también desde el propio código. Pero la entidad referencial  similar a la de Access lo debes hacer a través de Primary Key y Foreing Key entre las tablas. Una de las alternativas que SQL Server ofrece para asegurar la integridad de datos es el uso de restricciones (constraints).

La siguiente es la sintaxis parcial general para agregar una restricción «foreign key«:

alter table NOMBRE-TABLA1
add constraint NOMBRE-RESTRICCION
foreign key (CAMPO-CLAVE-FORANEA)
references NOMBRE-TABLA2 (CAMPO-CLAVE-PRIMARIA);

Analicémosla:

– NOMBRE-TABLA1 referencia el nombre de la tabla a la cual le aplicamos la restricción,

– NOMBRE-RESTRICCION es el nombre que le damos a la misma,

– luego de «foreign key», entre paréntesis se coloca el campo de la tabla a la que le aplicamos la restricción que será establecida como clave foránea,

– luego de «references» indicamos el nombre de la tabla referenciada y el campo que es clave primaria en la misma, a la cual hace referencia la clave foránea. La tabla referenciada debe tener definida una restricción «primary key» o «unique»; si no la tiene, aparece un mensaje de error.

Respecto a las funciones y los procedimientos.

Las funciones de SQL Server definidas por el usuario y los procedimientos almacenados ofrecen funcionalidades similares. Ambos nos permiten crear paquetes de sentencias SQL que son almacenadas en un servidor para futuro uso.

Gracias a ellas podemos reutilizar código, esconder detalles, centralizar el mantenimiento.

Por ejemplo, en aplicaciones que he desarrollado, el borrado de registros se realiza mediante el empleo de disparadores o triggers en vez desde el código del programa.

Un trigger es un procedimiento almacenado que se ejecuta cuando se cumple una condición establecida al realizar una operación. Dependiendo de la base de datos, los triggers pueden ser de inserción (INSERT), actualización (UPDATE) o borrado (DELETE). Algunas bases de datos pueden ejecutar triggers al crear, borrar o editar usuarios, tablas, bases de datos u otros objetos. Los triggers pueden ser DML y DDL.

Su sintaxis general:

CREATETRIGGER<Trigger_Name,sysname, Trigger_Name>

ON<Table_Name,sysname, Table_Name>

AFTER <Data_Modification_Statements,,INSERT,DELETE,UPDATE>

AS

BEGIN

— SET NOCOUNT ON added to prevent extra result sets from

— interfering with SELECT statements.

SETNOCOUNTON;

— Insert statements for trigger here

END

Ejemplos de triggers en transact (enlace externo, castellano, Pedro Herrarte Sánchez )
http://www.devjoker.com/contenidos/catss/278/Triggers-en-Transact-SQL.aspx

Por lo tanto, el trigger es una clase especial de procedimiento almacenado que se ejecuta automáticamente cuando se produce un evento en el servidor de bases de datos.
Cuál es entonces la diferencia entre una función y un procedimiento:

A primera vista, las funciones y los procedimientos parecen idénticos. Sin embargo, hay varias diferencias sutiles, pero importantes, entre las dos:

• Los procedimientos almacenados son llamados independientemente, usando el comando EXEC, mientras que las funciones son llamadas dentro de otra sentencia SQL.
• Los procedimientos almacenados permiten mejorar la seguridad de la aplicación concediendo permiso a los usuarios para que utilicen estos procedimientos, en lugar de permisos para acceder a las tablas dependientes. También proveen la habilidad de restringir las acciones del usuario en un nivel mucho más granular que permisos standard de SQL Server.
• Las funciones deben siempre devolver un valor (tanto un valor escalar como una tabla). Los procedimientos pueden retornar un valor escalar, un valor de tabla o simplemente nada.

Sobre ficheros.

Una base de datos SQL Server tiene dos componentes: un archivo DATA y un archivo LOG. El archivo DATA contiene las tablas, stored procedures, defaults, rules, etc en un formato propio con extensión MDF. El archivo LOG contiene una copia de las últimas transacciones realizadas en un formato propio con extensión LDF

Es costumbre a veces no incluir la base de datos sino incluir un archivo SCRIPT que contenga toda la información de la base de datos. De este modo lo que haces es
– Crear la base de datos con el Enterprise Manager del SQL SERVER
– Ejecutar el SCRIPT desde el Query Analyzer
Por default, las bases están en C:\Archivos de programa\Microsoft SQL Server\MSSQL\Data.
Los SCRIPTs suelen ser archivos de texto con extensión SQL.

Para gestionar SQL Server, podemos realizarlo con la herramienta SQL Server Management Studio. Lo primero que tendremos que hacer es logearnos en el sistema. Esta herramienta permite conectarnos a las bases.

IMPORTANTE:

Existen alternativas comerciales para gestionar bases de datos SQL Server.

HEIDI:http://www.heidisql.com/
RazorSQL: http://razorsql.com/
EMS: http://www.sqlmanager.net/en/products/mssql/manager
TOAD: http://www.quest.com/toad-for-sql-server/
dbFORGE: http://www.devart.com/dbforge/sql/studio/
Para mac: https://itunes.apple.com/us/app/sql-client-database-management/id828466809?mt=12&ign-mpt=uo%3D4

La interfaz de Sql Management Studio es similar a la de Visual Studio para el desarrollo.

Las bases de datos del sistema son necesarias para el funcionamiento de SQL Server.

Para crear una base de datos, es necesario saber que existen dos archivos asociados a una base. Por un lado los datos en sí, y los registros de transacciones.

Cuando se crea la base de datos se puede comprobar que aparecen 2 nombres. Un archivo que almacena las tablas y  las filas (nombre lógico), y un registro log para las transacciones, creaciones, eliminaciones, actualizaciones, copias.

El SQL Server es muy importante el tamaño del archivo. Podemos establecer el tamaño inicial, una vez creada dentro del disco duro. Por defecto ocupan 5 Mb la de datos,  1 la de transacciones.

El tamaño inicial, no quiere decir que este restringido a su crecimiento. El crecimiento de la base es muy importante en SQL-server. Por defecto crece en 1 Mb por cada vez sin límite, así hasta el límite del disco duro.

El crecimiento se puede expresar en megas y en porcentaje.

Las rutas de almacenamiento en disco pueden ser del tipo C:\Program Files\Microsoft SQL Server\MSQLSERVER\MSQLDATA\DATA\ …

Una base de datos contiene tablas, las tablas contienen columnas y estas a su vez registros.

La creación de campos dentro de una tabla es “similar” a Access. Eso si los tipos y sistema es diferente. Podremos establecer propiedades para cada tipo de campo, en la ventana propiedades de columna. Recordad que en las claves principales, no se admiten valores nulos.

Los sistemas SQL Server son muy flexibles a la hora de cambiar el tipo los datos de una tabla una vez creada, no obstante no es recomendable puesto que suele estar asociada a un lenguaje. Es por ello que es necesario pensar de forma anticipada la estructura de campos que va a tener cada tabla.

Cuando realizo acciones, se puede consultar en todo momento el SQL generado.

El acceso a tablas suele ser [nombre-base].[dbo].[mi-tabla]. El dbo significa data base object u objeto de base de datos.

Las vistas son consultas. Las peticiones a una base de datos se almacenan en vistas. Las vistas permiten seleccionar las tablas, vistas o funciones que deseamos trabajar.

Al margen de las típicas sentencias Select, Insert, delete y update que forman parte del DML del SQL, y no explicando las DDL para crear estructuras, voy a mostrar una serie de operadores que suelen ser singulares o característicos de SQL Server.

Ejemplos:

Clausula TOP:
SELECT TOP(20) nombre, apellido1 FROM clientes; ->Sólo obtiene los primeros 20 resultados. Con Select TOP (10) PERCENT nombre FROM clientes: -> Seleccionamos mediante porcentaje

Clausula BETWEEN
SELECT nombre, apellido1
FROM dbo.clientes
WHERE (id BETWEEN 5 and 10);
Selecciona un rango entre 5 y 10 del id. Alternativa al and  >= <=

Clausura LIKE
SELECT nombre, apellido1
FROM dbo.clientes
WHERE nombre LIKE ‘ %CA%’
Operador más flexible que el =, en este caso muestra nombres que contienen CA, ejemplo: OSCAR

Clausula IN
SELECT nombre, apellido1
FROM dbo.clientes
WHERE nombre IN (‘Pedro’, ‘Juan,’Carlos’);
-> Alternativa perfecta al OR

Clausulas JOIN
LEFT, RIGHT, FULL JOIN, INNER JOIN
INNER JOIN : Resultado columnas con misma condición. Por defecto. No aparece información cuando no se cumple la condición
LEFT OUTER JOIN: Todos los elementos de la columna de la izquierda y solo aquellos que tenga de la derecha.
RIGHT OUTER JOIN: Todos los elementos de la columna derecha aunque no tengan registros asociados y solo aquellos condición izquierda.
FULL OUTER JOIN: Resultados  de las tablas. Unión de tablas.

Union
SELECT nombre, apellido1
FROM dbo.usuarios UNION
SELECT nombre, apellido1
FROM dbo.usuarios

Une varias consultas. Podemos emplear UNION ALL, para forzar que muestre los duplicados.

Existen en TSQL funciones de agregado.
Gracias a las funciones no necesitamos un lenguaje de programación de apoyo para calcular datos de las tablas. Lo hace SQL por nosotros.
AVG, SUM,MIN, MAX, COUNT, COUNT(DISTINT nombre) ->(para filtrar resultados/regitros duplicados, sobre todo en cuentas)

Funciones escalares
SELECT UPPER(nombre) from dbo.clientes; — Mayúsculas.
SELECT LOWER(nombre) FROM dbo.clientes; — Minúsculas.
SELECT SUBSTRING(nombre, 1, 2) AS iniciales FROM dbo.clientes;  — Recorta cadenas
SELECT LEN(nombre) as longitud FROM dbo.clientes; — Longitud
SELECT { fn NOW()} as fecha from dbo.clientes;  — Coloca la fecha actual
SELECT CONVERT(CARCHAR(40), GETDATE(), 1) as fecha; — Devuelve fecha actual

Es necesario diferenciar entre SQL y Transact SQL. En ocasiones programar acciones y en función de las condiciones, ejecutar consultas SQL.  Esta lógica se programa mediante el lenguaje de la base de datos. Gracias a Transact no necesitamos programar desde el exterior las consultas, podemos programar dentro de SQL Server.

Aspectos básicos de Transact SQL

Podemos ejecutar transact desde cualquier consulta.

• Los comentarios simples se establecen con 2 guiones.
• /* */ Para los comentarios múltiples.
• Para crear variables debemos declararlas. El formato de las variables puede ser el mismo que los datos de las tablas.

Ejemplo:  declare @MiNombre VARCHAR(200)
Para asignar el valor set @MiNombre=’Oscar’

El comando print, nos devuelve información en pantalla. Print ‘Hola mundo’

• Podemos imprimir variables: print @MiNombre
• Podremos conectar la base mediante variables:
  Select @MiNombre = nombre FROM proveedores WHERE id=’2’
• Los operadores son los mismos que otros lenguajes (+,-,*,/,%).
  print @numero1 / @numero2
• Existen estructuras de control. Ejemplo de Estructura condicional (<,>,!<,>=,<=), podemos emplear operadores lógicos, AND, OR
  IF @MiNombre=’OSCAR’
  print ‘Soy yo’
  ELSE
  print ‘No sé quién es’
• Posee estructuras iterativas.
  set @i=0

Un pequeño bucle

While (@contador<10)
begin
Set @contador=contador + 1
end

La estructura CASE
set @mensaje= (CASE @estado
WHEN ‘SI’ THEN ‘Estado correcto’
WHEN ‘NO’ THEN ‘Estado incorrecto’
END

Podemos hacer consultas con variables.
Select @total=(SELECT cantidad FROM pedidos WHERE cliente = @cliente)
Emplear WHEN de CASES con variables, ejemplo: when @total

Estructuras de control Try-Catch

Necesitaremos crear un begin try y end try y luego un  begin catch end catch

Ejemplo
begin try
— comandos
end try
begin catch
print ERROR_MESSAGE()

end catch

Esto es todo que no es poco,  de momento …