Introducción a Microsoft SQL Server

INSERTAR / SELECCIONAR / ACTUALIZAR / ELIMINAR: los conceptos básicos del lenguaje de manipulación de datos #

Data Manipulación Llenguaje (DML para abreviar) incluye operaciones como INSERT, UPDATE y DELETE:

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld

En este script estamos creando una tabla para demostrar algunas consultas básicas.

Los siguientes ejemplos muestran cómo consultar tablas:

USE Northwind;
GO
SELECT TOP 10 * FROM Customers 
ORDER BY CompanyName

seleccionará los primeros 10 registros de la tabla Customer, ordenados por la columna CompanyName de la base de datos Northwind (que es una de las bases de datos de muestra de Microsoft, se puede descargar desde [aquí](https:// technet.microsoft.com/en-us/library/ms143221(v=sql.105).aspx)):

Tenga en cuenta que Usar Neptuno; cambia la base de datos predeterminada para todas las consultas posteriores. Todavía puede hacer referencia a la base de datos utilizando la sintaxis completa en forma de [Base de datos].[Esquema].[Tabla]:

SELECT TOP 10 * FROM Northwind.dbo.Customers 
ORDER BY CompanyName

SELECT TOP 10 * FROM Pubs.dbo.Authors
ORDER BY City

Esto es útil si está consultando datos de diferentes bases de datos. Tenga en cuenta que dbo, especificado “en el medio” se denomina esquema y debe especificarse al usar la sintaxis completa. Puede pensar en ello como una carpeta dentro de su base de datos. dbo es el esquema predeterminado. El esquema predeterminado puede omitirse. Todos los demás esquemas definidos por el usuario deben especificarse.

Si la tabla de la base de datos contiene columnas que se nombran como palabras reservadas, p. Fecha, debe encerrar el nombre de la columna entre paréntesis, así:

-- descending order
SELECT TOP 10 [Date] FROM dbo.MyLogTable
ORDER BY [Date] DESC

Lo mismo se aplica si el nombre de la columna contiene espacios en su nombre (lo que no se recomienda). Una sintaxis alternativa es usar comillas dobles en lugar de corchetes, por ejemplo:

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
order by "Date" desc 

es equivalente pero no tan comúnmente utilizado. Observe la diferencia entre comillas dobles y comillas simples: las comillas simples se usan para cadenas, es decir,

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
where UserId='johndoe'
order by "Date" desc 

es una sintaxis válida. Tenga en cuenta que T-SQL tiene un prefijo N para los tipos de datos NChar y NVarchar, p.

SELECT TOP 10 * FROM Northwind.dbo.Customers 
WHERE CompanyName LIKE N'AL%'
ORDER BY CompanyName

devuelve todas las empresas que tienen un nombre de empresa que comienza con AL (% es un comodín, utilícelo como usaría el asterisco en una línea de comando de DOS, por ejemplo, DIR AL*). Para LIKE, hay un par de comodines disponibles, busque aquí para obtener más detalles.

Uniones #

Las uniones son útiles si desea consultar campos que no existen en una sola tabla, sino en varias tablas. Por ejemplo: desea consultar todas las columnas de la tabla “Región” en la base de datos “Northwind”. Pero nota que también necesita la RegionDescription, que se almacena en una tabla diferente, Region. Sin embargo, hay una clave común, RgionID que puede usar para combinar esta información en una sola consulta de la siguiente manera (Top 5 solo devuelve las primeras 5 filas, omítala para obtener todas las filas):

SELECT TOP 5 Territories.*, 
    Regions.RegionDescription 
FROM Territories 
INNER JOIN Region 
    ON Territories.RegionID=Region.RegionID
ORDER BY TerritoryDescription

mostrará todas las columnas de Territorios más la columna RegionDescription de Region. El resultado está ordenado por TerritoryDescription.

Alias ​​de tabla #

Cuando su consulta requiere una referencia a dos o más tablas, puede que le resulte útil utilizar un alias de tabla. Los alias de tabla son referencias abreviadas a tablas que se pueden usar en lugar de un nombre de tabla completo y pueden reducir la escritura y la edición. La sintaxis para usar un alias es:

<TableName> [as] <alias>

Donde as es una palabra clave opcional. Por ejemplo, la consulta anterior se puede reescribir como:

SELECT TOP 5 t.*, 
    r.RegionDescription 
FROM Territories t
INNER JOIN Region r 
    ON t.RegionID = r.RegionID
ORDER BY TerritoryDescription

Los alias deben ser únicos para todas las tablas de una consulta, incluso si usa la misma tabla dos veces. Por ejemplo, si su tabla de empleados incluye un campo SupervisorId, puede usar esta consulta para devolver un empleado y el nombre de su supervisor:

SELECT e.*, 
    s.Name as SupervisorName -- Rename the field for output
FROM Employee e
INNER JOIN Employee s
    ON e.SupervisorId = s.EmployeeId
WHERE e.EmployeeId = 111

Sindicatos #

Como hemos visto antes, un Join agrega columnas de diferentes fuentes de tablas. Pero, ¿qué sucede si desea combinar filas de diferentes fuentes? En este caso, puede usar una UNIÓN. Suponga que está planeando una fiesta y desea invitar no solo a los empleados sino también a los clientes. Entonces podría ejecutar esta consulta para hacerlo:

SELECT FirstName+' '+LastName as ContactName, Address, City FROM Employees
UNION
SELECT ContactName, Address, City FROM Customers

Devolverá nombres, direcciones y ciudades de los empleados y clientes en una sola tabla. Tenga en cuenta que las filas duplicadas (si debe haber alguna) se eliminan automáticamente (si no desea esto, use UNION ALL en su lugar). El número de columna, los nombres de las columnas, el orden y el tipo de datos deben coincidir en todas las declaraciones de selección que forman parte de la unión; es por eso que el primer SELECT combina FirstName y LastName de Employee en ContactName.

Variables de tabla #

Puede ser útil, si necesita manejar datos temporales (especialmente en un procedimiento almacenado), usar variables de tabla: la diferencia entre una tabla “real” y una variable de tabla es que solo existe en la memoria para procesamiento temporal.

Ejemplo:

DECLARE @Region TABLE
(
  RegionID int, 
  RegionDescription NChar(50)
)

crea una tabla en la memoria. En este caso el prefijo @ es obligatorio porque es una variable. Puede realizar todas las operaciones DML mencionadas anteriormente para insertar, eliminar y seleccionar filas, p.

INSERT INTO @Region values(3,'Northern')
INSERT INTO @Region values(4,'Southern')

Pero normalmente, lo completarías en función de una tabla real como

INSERT INTO @Region
SELECT * FROM dbo.Region WHERE RegionID>2;

que leería los valores filtrados de la tabla real dbo.Region y los insertaría en la tabla de memoria @Region, donde se puede usar para su posterior procesamiento. Por ejemplo, podría usarlo en una unión como

SELECT * FROM Territories t
JOIN @Region r on t.RegionID=r.RegionID

que en este caso devolvería todos los territorios Norte y Sur. Se puede encontrar información más detallada [aquí] (http://odetocode.com/Articles/365.aspx). Las tablas temporales se analizan aquí, si está interesado en leer más sobre ese tema .

NOTA: Microsoft solo recomienda el uso de variables de tabla si el número de filas de datos en la variable de tabla es inferior a 100. Si va a trabajar con grandes cantidades de datos, use una tabla temporal, o tabla temporal, en su lugar.

SELECCIONA todas las filas y columnas de una tabla #

Sintaxis:

SELECT *
FROM table_name

El uso del operador asterisco * sirve como atajo para seleccionar todas las columnas de la tabla. También se seleccionarán todas las filas porque esta sentencia SELECT no tiene una cláusula WHERE para especificar ningún criterio de filtrado.

Esto también funcionaría de la misma manera si agregara un alias a la tabla, por ejemplo e en este caso:

SELECT *
FROM Employees AS e

O si quisieras seleccionar todo de una tabla específica puedes usar el alias + " .* “:

SELECT e.*, d.DepartmentName
FROM Employees AS e
    INNER JOIN Department AS d 
        ON e.DepartmentID = d.DepartmentID

También se puede acceder a los objetos de la base de datos utilizando nombres completos:

SELECT * FROM [server_name].[database_name].[schema_name].[table_name]

Esto no se recomienda necesariamente, ya que cambiar los nombres del servidor y/o de la base de datos haría que las consultas que utilizan nombres totalmente calificados ya no se ejecuten debido a nombres de objetos no válidos.

Tenga en cuenta que los campos antes de table_name se pueden omitir en muchos casos si las consultas se ejecutan en un solo servidor, base de datos y esquema, respectivamente. Sin embargo, es común que una base de datos tenga varios esquemas y, en estos casos, no se debe omitir el nombre del esquema cuando sea posible.

Advertencia: El uso de SELECT * en código de producción o procedimientos almacenados puede generar problemas más adelante (a medida que se agregan nuevas columnas a la tabla o si las columnas se reorganizan en la tabla), especialmente si su código hace suposiciones simples sobre el orden de las columnas o el número de columnas devueltas. Por lo tanto, es más seguro especificar siempre explícitamente los nombres de las columnas en las declaraciones SELECT para el código de producción.

SELECT col1, col2, col3
FROM table_name

ACTUALIZAR fila específica #

UPDATE HelloWorlds
SET HelloWorld = 'HELLO WORLD!!!'
WHERE Id = 5

El código anterior actualiza el valor del campo “HelloWorld” con “HELLO WORLD!!!” para el registro donde “Id = 5” en la tabla HelloWorlds.

Nota: En una declaración de actualización, se recomienda utilizar una cláusula “dónde” para evitar actualizar toda la tabla a menos que su requisito sea diferente.

Comentarios en código #

Transact-SQL admite dos formas de escritura de comentarios. Los comentarios son ignorados por el motor de la base de datos y están destinados a que la gente los lea.

Los comentarios están precedidos por -- y se ignoran hasta que se encuentra una nueva línea:

-- This is a comment
SELECT *
FROM MyTable -- This is another comment
WHERE Id = 1;

Los comentarios con barra oblicua comienzan con /* y terminan con */. Todo el texto entre esos delimitadores se considera un bloque de comentarios.

/* This is
a multi-line
comment block. */
SELECT Id = 1, [Message] = 'First row'
UNION ALL
SELECT 2, 'Second row'
/* This is a one liner */
SELECT 'More';

Los comentarios de barra oblicua tienen la ventaja de mantener el comentario utilizable si la instrucción SQL pierde caracteres de nueva línea. Esto puede suceder cuando se captura SQL durante la resolución de problemas.

Los comentarios de estrella inclinada se pueden anidar y un ‘/’ inicial dentro de un comentario de estrella inclinada debe terminar con un ‘/’ para que sea válido. El siguiente código dará como resultado un error.

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/

La estrella de barra inclinada, aunque dentro de la cita, se considera el comienzo de un comentario. Por lo tanto, debe terminar con otra barra diagonal de cierre. La forma correcta seria

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/  */

ELIMINAR todas las filas #

DELETE
FROM Helloworlds

Esto eliminará todos los datos de la tabla. La tabla no contendrá filas después de ejecutar este código. A diferencia de DROP TABLE, esto conserva la tabla en sí y su estructura y puede continuar insertando nuevas filas en esa tabla.

Otra forma de eliminar todas las filas de la tabla es truncarlas, de la siguiente manera:

TRUNCATE TABLE HelloWords

La diferencia con la operación DELETE son varias:

1. La operación de truncado no se almacena en el archivo de registro de transacciones
2. Si existe el campo IDENTIDAD, este se restablecerá
3. TRUNCATE se puede aplicar en toda la tabla y no en parte de ella (en cambio, con el comando DELETE puede asociar una cláusula WHERE)

Restricciones de TRUNCATE

1. No se puede TRUNCAR una tabla si hay una referencia de ‘LLAVE EXTERNA’
2. Si la mesa participa en una VISTA INDEXADA
3. Si la tabla se publica mediante REPLICACIÓN TRANSACCIONAL o REPLICACIÓN DE COMBINACIÓN
4. No disparará ningún TRIGGER definido en la tabla.

[sic]

IMPRESIÓN #

Muestra un mensaje en la consola de salida. Al usar SQL Server Management Studio, esto se mostrará en la pestaña de mensajes, en lugar de la pestaña de resultados:

PRINT 'Hello World!';

Seleccionar filas que coincidan con una condición #

En general, la sintaxis es:

SELECT <column names>
FROM <table name>
WHERE <condition>

Por ejemplo:

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith'

Las condiciones pueden ser complejas:

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith' AND (City = 'New York' OR City = 'Los Angeles')

ACTUALIZAR todas las filas #

Una forma simple de actualizar es incrementar todos los valores en un campo dado de la tabla. Para hacerlo, necesitamos definir el campo y el valor de incremento

El siguiente es un ejemplo que incrementa el campo Score en 1 (en todas las filas):

UPDATE Scores
SET score = score + 1  

Esto puede ser peligroso ya que puede corromper sus datos si accidentalmente realiza una ACTUALIZACIÓN para una Fila específica con una ACTUALIZACIÓN para Todas las filas en la tabla.

Recuperar información básica del servidor #

SELECT @@VERSION

Devuelve la versión de MS SQL Server que se ejecuta en la instancia.

SELECT @@SERVERNAME

Devuelve el nombre de la instancia de MS SQL Server.

SELECT @@SERVICENAME

Devuelve el nombre del servicio de Windows con el que se ejecuta MS SQL Server.

SELECT serverproperty('ComputerNamePhysicalNetBIOS');

Devuelve el nombre físico de la máquina donde se ejecuta SQL Server. Útil para identificar el nodo en un clúster de conmutación por error.

SELECT * FROM fn_virtualservernodes();

En un clúster de conmutación por error, se devuelven todos los nodos en los que se puede ejecutar SQL Server. No devuelve nada si no es un clúster.

TABLA TRUNCADA #

TRUNCATE TABLE Helloworlds 

Este código borrará todos los datos de la tabla Helloworlds. Truncar tabla es casi similar al código Eliminar de la tabla. La diferencia es que no puede usar cláusulas where con Truncar. Truncar la tabla se considera mejor que eliminar porque utiliza menos espacios de registro de transacciones.

Tenga en cuenta que si existe una columna de identidad, se restablece al valor semilla inicial (por ejemplo, la ID incrementada automáticamente se reiniciará desde 1). Esto puede provocar incoherencias si las columnas de identidad se utilizan como clave externa en otra tabla.

Crear una nueva tabla e insertar registros de la tabla anterior #

SELECT * INTO NewTable FROM OldTable

Crea una nueva tabla con la estructura de la tabla anterior e inserta todas las filas en la nueva tabla.

Algunas Restricciones

1. No puede especificar una variable de tabla o un parámetro con valores de tabla como la nueva tabla.
2. No puede usar SELECT…INTO para crear una tabla particionada, incluso cuando la tabla fuente está particionada. SELECT…INTO no utiliza el esquema de partición de la tabla fuente; en cambio, la nueva tabla es creado en el grupo de archivos predeterminado. Para insertar filas en una partición tabla, primero debe crear la tabla particionada y luego usar el INSERTAR EN… SELECCIONAR DESDE declaración.
3. Los índices, las restricciones y los activadores definidos en la tabla de origen no se transfieren a la nueva tabla ni se pueden especificar en el sentencia SELECT…INTO. Si estos objetos son necesarios, puede crear ellos después de ejecutar la sentencia SELECT…INTO.
4. Especificar una cláusula ORDER BY no garantiza que las filas se inserten en el orden especificado. Cuando se incluye una columna dispersa en la lista de selección, la propiedad de columna dispersa no se transfiere a la columna en la nueva tabla. Si esta propiedad es requerida en el nuevo tabla, modifique la definición de la columna después de ejecutar SELECT…INTO declaración para incluir esta propiedad.
5. Cuando se incluye una columna calculada en la lista de selección, la columna correspondiente en la nueva tabla no es una columna calculada. los los valores en la nueva columna son los valores que se calcularon en ese momento SELECT…INTO fue ejecutado.

[sic]

Uso de transacciones para cambiar datos de forma segura #

Siempre que cambie los datos, en un comando de lenguaje de manipulación de datos (DML), puede envolver sus cambios en una transacción. DML incluye UPDATE, TRUNCATE, INSERT y DELETE. Una de las formas en que puede asegurarse de que está cambiando los datos correctos sería usar una transacción.

Los cambios de DML bloquearán las filas afectadas. Cuando comienza una transacción, debe finalizarla o todos los objetos que se cambien en el DML permanecerán bloqueados por quien inició la transacción. Puede finalizar su transacción con ROLLBACK o COMMIT. ROLLBACK devuelve todo dentro de la transacción a su estado original. COMMIT coloca los datos en un estado final en el que no puede deshacer los cambios sin otra instrucción DML.

Ejemplo:

--Create a test table

USE [your database]
GO
CREATE TABLE test_transaction (column_1 varchar(10))
GO

INSERT INTO 
 dbo.test_transaction
        ( column_1 )
VALUES
        ( 'a' )

BEGIN TRANSACTION --This is the beginning of your transaction

UPDATE dbo.test_transaction
SET column_1 = 'B'
OUTPUT INSERTED.*
WHERE column_1 = 'A'
  

ROLLBACK TRANSACTION  --Rollback will undo your changes
           --Alternatively, use COMMIT to save your results

SELECT * FROM dbo.test_transaction   --View the table after your changes have been run

DROP TABLE dbo.test_transaction

Notas:

• Este es un ejemplo simplificado que no incluye el manejo de errores. Pero cualquier operación de la base de datos puede fallar y, por lo tanto, generar una excepción. Aquí hay un ejemplo cómo podría verse un manejo de errores tan requerido. Nunca debe usar transacciones sin un controlador de errores, de lo contrario, podría dejar la transacción en un estado desconocido.
• Según el nivel de aislamiento, las transacciones bloquean los datos que se consultan o cambiado. Debe asegurarse de que las transacciones no se ejecuten durante mucho tiempo, ya que bloquearán los registros en una base de datos y pueden provocar [puntos muertos] (https://www.simple-talk.com/sql/performance/sql- server-deadlocks-by-example/) con otras transacciones paralelas. Mantenga las operaciones encapsuladas en transacciones lo más cortas posible y minimice el impacto con la cantidad de datos que está bloqueando.

Obtener el recuento de filas de la tabla #

El siguiente ejemplo se puede usar para encontrar el recuento total de filas para una tabla específica en una base de datos si table_name se reemplaza por la tabla que desea consultar:

SELECT COUNT(*) AS [TotalRowCount] FROM table_name;

También es posible obtener el recuento de filas para todas las tablas uniéndose de nuevo a la partición de la tabla según el HEAP de las tablas (index_id = 0) o el índice agrupado en clúster (index_id = 1) usando el siguiente script:

SELECT  [Tables].name                AS [TableName],
        SUM( [Partitions].[rows] )    AS [TotalRowCount]
FROM    sys.tables AS [Tables]
JOIN    sys.partitions AS [Partitions]
    ON  [Tables].[object_id]    =    [Partitions].[object_id]
    AND [Partitions].index_id IN ( 0, 1 )
--WHERE    [Tables].name = N'table name' /* uncomment to look for a specific table */
GROUP BY    [Tables].name;

Esto es posible ya que cada tabla es esencialmente una tabla de partición única, a menos que se le agreguen particiones adicionales. Este script también tiene la ventaja de no interferir con las operaciones de lectura/escritura en las filas de las tablas.