Interfaz del Usuario SQL Server

Interfaz de usuario

SQL Server proporciona unas interfaces que han cambiado durante los años, de los cuales los más conocidos son las interfaces gráficas que están utilizadas como herramienta de desarrollo estándar a los desarrolladores y administradores.

La interfaz gráfica hasta 2005 incluyó el Enterprise Manager con una vista de árbol de los distintos objetos y con la capacidad de manejarlos; y el Query analyzer como interfaz textual para ejecutar comandos de TSQL.

En la versión 2005 las dos herramientas se unificaron a una –el SQL Server Management Studio (SSMS), y a partir de 2008 fue incluida la opción de trabajar con el Visual-Studio– la interfaz estándar de desarrollo de Microsoft (a los distintos lenguajes, BI, etc.). Otra interfaz opcional es la utilización de la Linea de comandos, con herramientas como SQLCmd, ISQL, OSQL que posibilita la ejecución de scripts y procesamiento por lotes. Desde 2008 se puede desarrollar con SQLCmd (SQL Command) a través del SSMS sin interconectarse al interfaz textual de Windows. Otra opción en el ámbito de scripts es la utilización del lenguaje de scripts Powershell de Microsoft.

Aparte de las interfaces estándares de SQL Server, se puede ejecutar comandos de TSQL con herramientas de conexión como ODBC y OLE-DB.

 

Servicios

A contrario de sistemas de bases de datos como Microsoft Access que son "pasivas" y contienen un archivo a cual hay que conectar y la ejecución de los comandos se lleva a cabo en el cliente (la computadora de usuario), en SQL Server hay número de servicios, software que están ejecutadas en la memoria del servidor por parte del sistema, y por lo tanto aprovechan las capacidades del servidor que es más potente que los clientes, previenen congestión en la red, y pueden programar tareas que corran aunque el cliente no está conectado.

Los servicios principales:

·         SQL Server - El "motor" del sistema

·         SQL Agent - Ejecución de tareas (Jobs, scripts programados) y envió de advertencias en caso de carga pesada e irregulares en el sistema

·         Full-Text Filter Daemon Launcher - La utilización de los índices especiales del "Full text search" por búsqueda textual avanzada

·         SQL Browser - El "oyente" dedicado a comandos enviados y redirigirlos a su destino

·         SSIS Server - La operación del SSIS (la herramienta de ETL)

·         SSAS Server - La operación del SSAS (la herramienta de OLAP)

·         SSRS Server - La operación del SSRS (la herramienta de informes)

 

Capacidades y herramientas básicas

Base de datos

En cada instalación de SQL Server hay 4 bases de datos de sistema, y la capacidad de crear nuevas bases de datos por el usuario, en los cuales los datos están almacenados en tablas.

Estas bases de datos, creadas por parte de los usuarios, incluyen básicamente un archivo de datos (con el sufijo mdf) con las tablas y los distintos objetos a nivel de la base de datos; y un archivo de registro (con el sufijo ldf) con las transacciones abiertas, y transacciones cerradas, Sujeto al modelo de recuperación seleccionado (se puede acumular en el archivo de registro todos los cambios en la base de datos desde el último respaldo). Se puede crear un conjunto de archivos de datos además del principal (con el sufijo ndf) por consideraciones de eficiencia, partición de carga de trabajo entre los discos rígidos, etc.

Las bases de datos del sistema:

·         master - Todos los procedimientos, funciones y tablas del sistema que están utilizadas por parte de todas las bases de datos y que están instaladas automáticamente, tanto como las que han sido creado por parte de los administradores del sistema. Además, todas las definiciones en respecto a la seguridad a nivel del servidor, están almacenadas en esta base de datos.

·         msdb - Almacenamiento de las tareas del agente, los códigos de CLR combinados en el sistema, los paquetes de SSIS, y otros más.

·         model - El molde de las bases de datos. Cada nueva base de datos se crea como una copia de esta base de datos, menos que algo más estaba definido explícitamente.

·         tempdb - Base de datos temporal que se crea de nuevo cada vez que el servicio reinicia. Se utiliza para almacenar tablas temporales creadas por parte de los usuarios o el sistema (por ejemplo en ordenaciones complejos).

Tablas fijas y temporales

Desde la perspectiva lógica, los datos almacenados en las bases de datos en tablas, que mediante ellas se implementa la teoría de las bases de datos relacionales. La tabla se divide en filas y columnas (A veces se les conoce como registros y campos). Las tablas pueden ser fijas o temporales, mientras que en el segundo caso existen físicamente en la base de datos tempdb, y se borran automáticamente en caso de desconexión de la sesión o de la conexión al servidor, depende en el tipo de la tabla temporal.

Desde la perspectiva física, el sistema divide los archivos de la base datos en Extents de 64 KB, y cada cual a ocho páginas de 8 KB. Generalmente, cada Extent se asigna a una tabla o un índice, menos las tablas pequeñas; y cada página se asigna siempre a una tabla específica. El sistema es responsable del aumento de los archivos, de acuerdo con los ajustes del usuario, y de asignar Extents y páginas a las tablas.

A las tablas se puede crear índices. Los índices se almacenan junto a la tabla (Non Clustered Index) o son la tabla en sí (Clustered Index). Los índices asisten en la búsqueda de datos en las tablas (como los ficheros en las librerías), en ordenarlas, y la definición de claves primarias.

Entre las tablas se puede crear una relación de uno a muchos.

Aparte de las tablas de los usuarios, hay tablas que almacenan meta data: datos sobre el sistema mismo, los diferentes objetos, los derechos, estadísticas sobre el rendimiento del sistema (DMV), etc.

Tipos de datos

 Para cada columna en una tabla y a cada variable o parámetro, se define un tipo de datos que sean almacenados en él, entre ellos:

1.      Numeros: Números enteros y no enteros en distintos tamaños, y en diferentes niveles de precisión; y auto incremento opcional.

2.      Textos: Cadenas de distintas longitudes, y distintas capacidades de apoyar distintas lenguas.

3.      Fechas: Fechas en distintos niveles de precisión, desde días completos hasta fracciones menores de un segundo, que apoyan fechas a partir del principio del siglo XX o del calendario gregoriano, y la capacidad de diferenciar entre distintos usos de horarios.

4.      XML: Datos textuales (cadenas) que representan conjuntos estándares de datos (estándar SGML).

5.      Datos binarios: Datos almacenados como datos binarios (bits y bytes), que posibilitan el almacenamiento de archivos gráficos, etc.

6.      Geography: Representación estándar de información geográfica, tales como estados, zonas geográficas, localidades; y las cálculos como distancias.

7.      Geometry: Representación estándar de puntas, líneas, superficies en el plano; y las relaciones entre ellas.

8.      Hierarchyid: Representación estándar de información jerárquica como lista de materiales, relaciones de subordinación entre empleados, etc.

Vistas

Las vistas representan generalmente comandos de extracción de datos, que se almacenan sin los datos (que están almacenados en las tablas). Esta opción nos posibilita crear extracciones complejas o estándares, almacenarlas como vistas, y utilizar las vistas sin la necesidad de escribir de nuevo los comandos o mantener los códigos donde ellas aparecen. Adicionalmente, es un medio muy importante para otorgar derechos selectivos de lectura (en caso de que queramos posibilitar a un usuario contemplar parcialmente las columnas o las filas de una tabla).

Una vista se puede considerar una tabla virtual o una consulta almacenada. Los datos accesibles a través de una vista no están almacenados en un objeto distinto de la base de datos. Lo que está almacenado en la base de datos es una instrucción SELECT. El resultado de la instrucción SELECT forma la tabla virtual que la vista devuelve. El usuario puede utilizar dicha tabla virtual haciendo referencia al nombre de la vista en instrucciones Transact-SQL, de la misma forma en que se hace referencia a las tablas. Las vistas se utilizan para alguna de estas funciones, o para todas:
• Restringir el acceso del usuario a filas concretas de una tabla. Por ejemplo, permitir que un empleado solo vea las filas que guardan su trabajo en una tabla de seguimiento de actividad laboral.
• Restringir el acceso del usuario a columnas específicas. Por ejemplo, permitir que los empleados que no trabajen en el departamento de nóminas vean las columnas de nombre, oficina, teléfono y departamento de la tabla de empleados, pero no permitir que vean las columnas con los datos de salario u otra información personal.
• Combinar columnas de varias tablas de forma que parezcan una sola tabla.
• Agregar información en lugar de presentar los detalles. Por ejemplo, presentar la suma de una columna o el valor máximo o mínimo de una columna.

Las vistas se crean definiendo la instrucción SELECT que recupera los datos presentados por la vista. Las tablas de datos a las que hace referencia la instrucción SELECT se conocen como las tablas base para la vista. Las vistas en todas las versiones de SQL Server son actualizables (pueden ser objetivo de instrucciones UPDATE, DELETE o INSERT) mientras la modificación afecte solo a una de las tablas base de la vista.

 

Procedimientos Almacenados

Los procedimientos son scripts de comandos de TSQL, que pueden ser ejecutados con distintos parámetros. Por ejemplo, procedimiento que obtiene número de año como parámetro, y actualiza una tabla de resumen de ventas, con las ventas de los agentes en el dicho año, basada en la tabla de registro de ventas.

Los procedimientos almacenados pueden facilitar en gran medida la administración de la base de datos y la visualización de información sobre dicha base de datos y sus usuarios. Los procedimientos almacenados son una colección precompilada de instrucciones SQL e instrucciones de control de flujo opcionales almacenadas bajo un solo nombre y procesadas como una unidad. Los procedimientos almacenados se guardan en una base de datos; se pueden ejecutar desde una aplicación y permiten variables declaradas por el usuario, ejecución condicional y otras funciones eficaces de programación. Los procedimientos almacenados pueden contener flujo de programas, lógica y consultas a la base de datos. Pueden aceptar parámetros, proporcionar resultados de parámetros, devolver conjuntos de resultados individuales o múltiples y devolver valores.

Las ventajas de utilizar procedimientos almacenados en SQL Server en vez de programas Transact-SQL almacenados localmente en equipos clientes consisten en que:
• Permiten una programación modular.
Puede crear el procedimiento una vez, almacenarlo en la base de datos, y llamarlo desde el programa el número de veces que desee. Un especialista en programación de bases de datos puede crear procedimientos almacenados, que luego será posible modificar independientemente del código fuente del programa. Facilitan el mantenimiento.
• Permiten una ejecución más rápida.
En situaciones en las que se necesita una gran cantidad de código Transact-SQL, o si las operaciones se realizan varias veces, los procedimientos almacenados pueden ser más rápidos que los lotes de código Transact-SQL. Los procedimientos son analizados y optimizados en el momento de su creación, y es posible utilizar una versión del procedimiento que se encuentra en la memoria después de que se ejecute por primera vez. Las instrucciones de Transact-SQL que se envían varias veces desde el cliente cada vez que deben ejecutarse tienen que ser compiladas y optimizadas siempre que SQL Server las ejecuta.
• Pueden reducir el tráfico de red.
Una operación que necesite centenares de líneas de código Transact-SQL puede realizarse mediante una sola instrucción que ejecute el código en un procedimiento, en vez de enviar cientos de líneas de código por la red.
• Pueden utilizarse como mecanismo de seguridad.
Es posible conceder permisos a los usuarios para ejecutar un procedimiento almacenado, incluso si no cuentan con permiso para ejecutar directamente las instrucciones del procedimiento.

Funciones definidas por el usuario

Las funciones son un objeto que combina algunas capacidades de las vistas, con otras de los procedimientos. Como las vistas, pueden extraer datos y ejecutar cálculos, y devuelven un resultado al usuario o al programa que les ejecutó. Tanto como los procedimientos, incluyen códigos de TSQL, y pueden ser ejecutados con parámetros.

Las funciones devuelven un valor o un conjunto de valores.

Las funciones definidas por el usuario se crean con la instrucción CREATE FUNCTION, se modifican con la instrucción ALTER FUNCTION y se quitan con la instrucción DROP FUNCTION. Todos los nombres de funciones completos (database_name.owner_name.function_name) definidos por el usuario deben ser únicos. Para crear, modificar o quitar funciones definidas por el usuario, debe tener permisos de CREATE FUNCTION. Los usuarios distintos del propietario deben tener permiso EXECUTE para una función, y solo así podrán utilizarla en una instrucción de Transact-SQL. Para crear o modificar tablas con referencias a funciones definidas por el usuario en la restricción CHECK, la cláusula DEFAULT o la definición de una columna calculada, también debe tener permiso REFERENCES para las funciones. Los errores de Transact-SQL que producen la cancelación de una instrucción y continúan con la siguiente instrucción del módulo, como desencadenadores o procedimientos almacenados, se tratan de forma distinta dentro de una función. En las funciones, estos errores hacen que se detenga la ejecución de la función. Esto hace que se cancele la función que invocó la instrucción. Una función definida por el usuario no tiene ninguno o tiene varios parámetros de entrada y devuelve un valor escalar o una tabla. Una función puede tener un máximo de 1024 parámetros de entrada. Cuando un parámetro de la función toma un valor predeterminado, debe especificarse la palabra clave DEFAULT al llamar a la función para poder obtener el valor predeterminado. Este comportamiento es diferente del de los parámetros con valores predeterminados de los procedimientos almacenados, para los cuales omitir el parámetro implica especificar el valor predeterminado. Las funciones definidas por el usuario no admiten parámetros de salida.

Consultas distribuidas

Las consultas distribuidas tienen acceso a datos de varios orígenes, que pueden estar almacenados en un equipo o en equipos distintos. Microsoft SQL Server 2000 admite las consultas distribuidas a través de OLE DB Las consultas distribuidas proporcionan a los usuarios de SQL Server acceso a:
• Datos distribuidos almacenados en múltiples instancias SQL Server.
• Datos heterogéneos almacenados en varios orígenes de datos relacionales y no relacionales a los que se tiene acceso mediante un proveedor OLE DB.
Los proveedores OLE DB exponen datos en objetos tabulares llamados conjuntos de filas. En las instrucciones Transact-SQL, SQL Server 2000 permite que se haga referencia a los conjuntos de filas de los proveedores OLE DB como si fueran una tabla de SQL Server. En las instrucciones SELECT, INSERT, UPDATE y DELETE de Transact-SQL, se puede hacer referencia directa a las tablas y vistas de orígenes de datos externos. Puesto que las consultas distribuidas usan OLE DB como interfaz subyacente, éstas tienen acceso a los sistemas DBMS relacionales tradicionales con procesadores de consultas SQL, así como a los datos administrados por orígenes de datos de capacidad y sofisticación diversas. Siempre que el software propietario de los datos los expone en un conjunto de filas tabular a través del proveedor OLE DB, los datos se podrán usar en las consultas distribuidas. Nota: El uso de las consultas distribuidas en SQL Server es similar a la funcionalidad de las tablas vinculadas mediante ODBC, que anteriormente admitía Microsoft Access. Esta funcionalidad se encuentra ahora integrada en SQL Server con OLE DB como interfaz para los datos externos.