SQL Server: Cheat Sheet para Desarrolladores y Administradores

rokitoh Bases de datos, SQL Server

Introducción a SQL:

¿Qué es SQL? SQL (Structured Query Language) es un lenguaje de programación diseñado para gestionar y manipular datos almacenados en sistemas de gestión de bases de datos relacionales (RDBMS). Con SQL, puedes realizar diversas operaciones, desde consultas básicas hasta tareas avanzadas de administración de bases de datos.

¿Por qué SQL es Importante? En el corazón de muchas aplicaciones y sistemas, SQL desempeña un papel crucial al permitir a los desarrolladores o administradores interactuar con bases de datos de manera eficiente. Con SQL, puedes extraer información específica, realizar actualizaciones, insertar datos y mucho más.

Estructura de la Guía:

  1. Conexión a la Base de Datos: Iniciaremos nuestro viaje aprendiendo cómo conectarnos a una base de datos, estableciendo los cimientos para nuestras futuras operaciones.

  2. Consultas Básicas: Exploraremos cómo realizar consultas simples a una sola tabla, aplicando condiciones, ordenando resultados y resumiendo datos.

  3. Manipulación de Datos: Aprenderemos a insertar, actualizar y eliminar datos, habilidades esenciales para la gestión efectiva de información.

  4. Funciones Útiles: Descubriremos funciones incorporadas que nos ayudarán a extraer información valiosa de nuestras bases de datos.

  5. Optimización de Consultas: Mejoraremos la eficiencia de nuestras consultas, utilizando índices y prácticas recomendadas.

  6. Administración y Mantenimiento: Exploraremos comandos clave para respaldar, restaurar y monitorear nuestras bases de datos.

  7. Ampliando el Cheat Sheet con Ejemplos Prácticos: Abordaremos situaciones más complejas, desde la copia de bases de datos hasta la gestión avanzada de permisos.

1. Conexión a la Base de Datos:

Antes de sumergirnos en consultas y comandos, primero conectémonos a nuestra base de datos

-- Conexión al servidor
USE NombreDeTuBaseDeDatos;

-- Conexión con autenticación de Windows
sqlcmd -S NombreDelServidor -d NombreDeTuBaseDeDatos -E

-- Conexión con autenticación de SQL Server
sqlcmd -S NombreDelServidor -U NombreDeUsuario -P TuContraseña -d NombreDeTuBaseDeDatos

Explicación:

  • USE NombreDeTuBaseDeDatos: Selecciona la base de datos con la que deseas trabajar.
  • sqlcmd -S …: Muestra ejemplos de conexión usando autenticación de Windows o SQL Server.

2. Consultas Básicas:

Comencemos con consultas simples a una sola tabla:

-- Seleccionar todos los registros de una tabla
SELECT * FROM NombreDeTuTabla;

-- Filtrar resultados con una condición WHERE
SELECT * FROM NombreDeTuTabla WHERE Condicion;

-- Ordenar resultados
SELECT * FROM NombreDeTuTabla ORDER BY NombreDeColumna ASC|DESC;

-- Agrupar y resumir datos
SELECT Columna, COUNT(*) FROM NombreDeTuTabla GROUP BY Columna;

Explicación:

  • *SELECT : Recupera todas las columnas de la tabla especificada.
  • WHERE Condicion: Filtra resultados basados en una condición específica.
  • ORDER BY …: Ordena los resultados ascendente (ASC) o descendentemente (DESC).
  • GROUP BY …: Agrupa datos basados en una columna y realiza funciones de agregación como COUNT.

3. Manipulación de Datos:

Aprendamos a insertar, actualizar y eliminar datos:

-- Insertar datos
INSERT INTO NombreDeTuTabla (Columna1, Columna2) VALUES (Valor1, Valor2);

-- Actualizar datos
UPDATE NombreDeTuTabla SET Columna = NuevoValor WHERE Condicion;

-- Eliminar registros
DELETE FROM NombreDeTuTabla WHERE Condicion;

Explicación:

  • INSERT INTO …: Agrega nuevos registros a la tabla.
  • UPDATE … SET …: Modifica datos existentes basado en una condición.
  • DELETE FROM … WHERE …: Elimina registros basado en una condición.

4. Funciones Útiles:

Utilicemos funciones incorporadas para obtener información valiosa:

-- Obtener el total de registros
SELECT COUNT(*) FROM NombreDeTuTabla;

-- Calcular el promedio
SELECT AVG(Columna) FROM NombreDeTuTabla;

-- Encontrar el valor máximo y mínimo
SELECT MAX(Columna), MIN(Columna) FROM NombreDeTuTabla;

Explicación:

  • COUNT(…): Cuenta el número total de registros.
  • AVG(…): Calcula el promedio de los valores en una columna.
  • MAX(…), MIN(…): Encuentra el valor máximo y mínimo en una columna.

5. Optimización de Consultas:

En esta sección, exploraremos estrategias clave para optimizar tus consultas SQL y mejorar el rendimiento de tus operaciones en la base de datos.

Utiliza índices para mejorar la velocidad de búsqueda Para acelerar las consultas de búsqueda, considera el uso de índices. Supongamos que tienes una tabla llamada ‘productos’, y deseas buscar por el nombre. Puedes mejorar significativamente la velocidad de esta búsqueda creando un índice específico para la columna ‘nombre’:


CREATE INDEX idx_nombre_producto ON productos(nombre);

Evita el uso excesivo de SELECT *; selecciona solo las columnas necesarias

El uso excesivo de SELECT * puede afectar el rendimiento de tu consulta y traer datos innecesarios. En lugar de seleccionar todas las columnas, elige solo aquellas que son relevantes para tu tarea. Por ejemplo:

-- Selecciona solo las columnas 'nombre' y 'precio' en lugar de todas las columnas.
SELECT nombre, precio FROM productos;

Usa JOIN de manera eficiente y evita subconsultas innecesarias

Optimizar el uso de JOIN y evitar subconsultas innecesarias contribuye a consultas más eficientes. En lugar de utilizar una subconsulta, puedes lograr el mismo resultado mediante un INNER JOIN. Aquí hay un ejemplo:

-- En lugar de usar una subconsulta, utiliza un INNER JOIN para obtener el mismo resultado.
SELECT clientes.nombre, pedidos.fecha
FROM clientes
INNER JOIN pedidos ON clientes.id = pedidos.id_cliente;

Estas prácticas ayudarán a maximizar la eficiencia de tus consultas SQL, asegurando un mejor rendimiento y una respuesta más rápida de tu base de datos.

6. Administración y Mantenimiento:

Aprende comandos esenciales para administrar tu base de datos:

-- Realizar respaldos
BACKUP DATABASE NombreDeTuBaseDeDatos TO DiscoLocal;

-- Restaurar desde un respaldo
RESTORE DATABASE NombreDeTuBaseDeDatos FROM DiscoLocal;

-- Monitorear bloqueos
sp_who2;

-- Ejecutar tareas programadas
CREATE JOB NombreDeTuTrabajo
   SCHEDULE frecuencia_de_ejecucion
   JOB_COMMAND = 'ComandoSQL';

Explicación:

  • BACKUP …: Crea una copia de seguridad de la base de datos.
  • RESTORE …: Restaura la base de datos desde una copia de seguridad.
  • sp_who2: Monitorea bloqueos en la base de datos.
  • CREATE JOB …: Programa tareas automatizadas.

Ampliando el Cheat Sheet con Ejemplos Prácticos:

Vamos a explorar comandos avanzados con ejemplos:

7. Copia de Base de Datos:

-- Cambiar el nombre de una base de datos
ALTER DATABASE [sql-db-pre] MODIFY NAME = [sql-db-pre];

-- Crear una copia de una base de datos
CREATE DATABASE [sql-db-pre] AS COPY OF [sql-srv-prod].[sql-db-pro];

Explicación:

  • ALTER DATABASE …: Cambia el nombre de una base de datos existente.
  • CREATE DATABASE AS COPY OF …: Crea una copia exacta de una base de datos.

8. Monitoreo y Estado de Copias:

-- Ver todas las copias de base de datos
SELECT * FROM sys.dm_database_copies;

-- Ver el estado de todas las bases de datos
SELECT state_desc, * FROM sys.databases;

Explicación:

  • sys.dm_database_copies: Muestra información sobre todas las copias de base de datos.
  • sys.databases: Proporciona detalles sobre el estado de todas las bases de datos.

9. Ownership de Esquemas:

-- Ver ownership de los schemas
SELECT S.*,
       [the schema owner]=dp.name,
       dp.type_desc
FROM SYS.schemas S
INNER JOIN SYS.database_principals dp
       ON S.principal_id = dp.principal_id;

-- Cambiar Ownership de un schema
ALTER AUTHORIZATION ON SCHEMA::[jiraschema] TO [dbo];

Explicación:

  • SYS.schemas y SYS.database_principals: Muestra detalles sobre los esquemas y sus propietarios.
  • ALTER AUTHORIZATION …: Cambia el propietario de un esquema.

10. Revisar Permisos:

-- Revisar permisos de usuarios y roles
SELECT 
    u.name as [user],
    r.name as [role]
FROM sys.database_principals u
LEFT JOIN sys.database_role_members rm ON rm.member_principal_id = u.principal_id
LEFT JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
WHERE u.type<>'R' AND u.authentication_type<>0 AND u.name<>'dbo'
ORDER BY u.name;

Explicación:

  • sys.database_principals: Muestra usuarios y roles de la base de datos.
  • sys.database_role_members: Relaciona usuarios con roles.
  • LEFT JOIN … ON …: Combina información de usuarios y roles.

11. Crear Usuario con Permisos de Lectura:

-- Crear un usuario de solo lectura
-- Lanzar contra la [master]
CREATE LOGIN [DATABASEUSER] WITH PASSWORD=N'PASSWORD';
GO
CREATE USER [DATABASEUSER] FOR LOGIN [DATABASEUSER] WITH DEFAULT_SCHEMA=[dbo];
GO
-- Lanzar contra la BD sobre la que se quiere dar permisos en una nueva sesión
CREATE USER [DATABASEUSER] FOR LOGIN [DATABASEUSER] WITH DEFAULT_SCHEMA = dbo;
GO
ALTER ROLE [db_datareader] ADD MEMBER [DATABASEUSER];
GO

Explicación:

  • CREATE LOGIN …: Crea un nuevo inicio de sesión.
  • CREATE USER … FOR LOGIN …: Asocia el inicio de sesión con un usuario de la base de datos.
  • ALTER ROLE … ADD MEMBER …: Asigna permisos de lectura al usuario.

12. Crear Usuario con Permisos de Lectura y Escritura:

-- Crear un usuario de lectura y escritura
-- Lanzar contra la [master]
CREATE LOGIN [DATABASEUSER] WITH PASSWORD=N'PASSWORD';
GO
CREATE USER [DATABASEUSER] FOR LOGIN [DATABASEUSER] WITH DEFAULT_SCHEMA=[dbo];
GO
-- Lanzar contra la BD sobre la que se quiere dar permisos en una nueva sesión
CREATE USER [DATABASEUSER] FOR LOGIN [DATABASEUSER] WITH DEFAULT_SCHEMA = dbo;
GO
ALTER ROLE [db_datareader] ADD MEMBER [DATABASEUSER];
GO
ALTER ROLE [db_datawriter] ADD MEMBER [DATABASEUSER];
GO

Explicación:

  • Similar al anterior, pero también agrega permisos de escritura.

13. Crear Usuario con Permisos de Lectura, Escritura y Ejecución:

-- Crear un usuario de lectura, escritura y ejecución
-- Lanzar contra la [master]
CREATE LOGIN [DATABASEUSER] WITH PASSWORD=N'PASSWORD';
GO
CREATE USER [DATABASEUSER] FOR LOGIN [DATABASEUSER] WITH DEFAULT_SCHEMA=[dbo];
GO
-- Lanzar contra la BD sobre la que se quiere dar permisos en una nueva sesión
CREATE USER [DATABASEUSER] FOR LOGIN [DATABASEUSER] WITH DEFAULT_SCHEMA = dbo;
GO
ALTER ROLE [db_datareader] ADD MEMBER [DATABASEUSER];
GO
ALTER ROLE [db_datawriter] ADD MEMBER [DATABASEUSER];
GO
GRANT EXECUTE TO [DATABASEUSER];
GO

Explicación:

  • Similar a los anteriores, pero también otorga permisos de ejecución.

14. Eliminar Usuario de Rol de Escritura:

-- Eliminar un usuario de un rol de escritura
ALTER ROLE [db_datawriter] DROP MEMBER [DATABASEUSER];

Explicación:

  • ALTER ROLE … DROP MEMBER …: Elimina a un usuario de un rol específico.

15.Permisos y roles en Azure SQL Server:

Roles fijos de nivel de servidor

A continuación se presentan los roles fijos de nivel de servidor junto con sus capacidades.

Rol fijo de nivel de servidorDescripción
##MS_DatabaseConnector##Permite la conexión a cualquier base de datos sin requerir una cuenta de usuario específica en la base de datos. Para denegar el permiso CONNECT en una base de datos particular, se puede crear una cuenta de usuario coincidente y luego usar DENY para este usuario. Este DENY anula cualquier permiso CONNECT otorgado por este rol.
##MS_DatabaseManager##Permite la creación y eliminación de bases de datos. Al crear una base de datos, el miembro se convierte en propietario (dbo) con todos los permisos en esa base de datos. No implica acceso automático a otras bases de datos. Se debe utilizar junto con el rol de base de datos dbmanager en master.
##MS_DefinitionReader##Permite leer todas las vistas de catálogo utilizando VIEW ANY DEFINITION o VIEW DEFINITION en bases de datos específicas donde el usuario tenga una cuenta.
##MS_LoginManager##Permite la creación y eliminación de inicios de sesión. Se debe usar en conjunto con el rol de base de datos loginmanager en master.
##MS_SecurityDefinitionReader##Permite leer vistas de catálogo cubiertas por VIEW ANY SECURITY DEFINITION y VIEW SECURITY DEFINITION en bases de datos específicas donde el usuario tenga cuenta, un subconjunto de los permisos de ##MS_DefinitionReader##.
##MS_ServerStateManager##Incluye todos los permisos de ##MS_ServerStateReader## y además ALTER SERVER STATE, que permite operaciones administrativas como DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE (‘ALL’) y DBCC SQLPERF().
##MS_ServerStateReader##Permite leer vistas de administración dinámica (DMV) y funciones cubiertas por VIEW SERVER STATE o VIEW DATABASE STATE en bases de datos específicas donde el usuario tenga cuenta.

Permisos de roles fijos de servidor

Cada rol fijo de nivel de servidor tiene permisos específicos asignados, que se detallan a continuación. Estos permisos se heredan en las bases de datos a las que el usuario tiene acceso.

Rol fijo de nivel de servidorPermisos a nivel de servidorPermisos de nivel de base de datos (si existe un usuario de base de datos coincidente)
##MS_DatabaseConnector##CONNECT ANY DATABASECONNECT
##MS_DatabaseManager##CREATE ANY DATABASE, ALTER ANY DATABASEALTER
##MS_DefinitionReader##VIEW ANY DATABASE, VIEW ANY DEFINITION, VIEW ANY SECURITY DEFINITIONVIEW DEFINITION, VIEW SECURITY DEFINITION
##MS_LoginManager##CREATE LOGIN, ALTER ANY LOGINN/D
##MS_SecurityDefinitionReader##VIEW ANY SECURITY DEFINITIONVIEW SECURITY DEFINITION
##MS_ServerStateManager##ALTER SERVER STATE, VIEW SERVER STATE, VIEW SERVER PERFORMANCE STATE, VIEW SERVER SECURITY STATEVIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE
##MS_ServerStateReader##VIEW SERVER STATE, VIEW SERVER PERFORMANCE STATE, VIEW SERVER SECURITY STATEVIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE

Trabajo con roles de nivel de servidor

A continuación se explican las vistas del sistema y las funciones útiles para trabajar con roles de nivel de servidor en Azure SQL Database.

CaracterísticaTipoDescripción
IS_SRVROLEMEMBERMetadatosIndica la membresía de un inicio de sesión SQL en un rol de nivel de servidor específico.
sys.server_role_membersMetadatosDevuelve una fila por cada miembro de cada rol de nivel de servidor.
sys.sql_loginsMetadatosDevuelve una fila por cada inicio de sesión SQL.
ALTER SERVER ROLEGet-HelpModifica la membresía de un rol de servidor.

Ejemplos

A continuación se muestra un ejemplo de cómo trabajar con roles de nivel de servidor en Azure SQL Database:

Ejemplo: Añadir un inicio de sesión SQL a un rol de nivel de servidor.

ALTER SERVER ROLE ##MS_ServerStateReader##
    ADD MEMBER rokitoh;
GO

:wq!

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *