Consultas en SQL

) Siempre que coincida con los tipos de datos en código con las columnas de la base de datos

Es importante asegurarse de que sus tipos de datos se ajustan a través de todas las capas de la aplicación. Por ejemplo, si el tipo de datos de una columna es de tipo nvarchar (50), usted debe tener el código en consultas y procedimientos almacenados utilizan variables locales del mismo tipo de datos. Del mismo modo, el código de ADO.NET en la capa de datos debe especificar el mismo tipo de datos y longitud. ¿Por qué es esto importante? Porque si los tipos de datos y consultas no coinciden, SQL Server necesita para llevar a cabo una conversión implícita de los tipos de datos para que coincida con ellos. También hay algunas situaciones en las que SQL Server no puede utilizar un índice existente, a pesar de que la columna referenciada está indexado. Por lo tanto, la consulta podría terminar usando Index Scan en lugar de Index Seek, resultando en tiempos de ejecución de las órdenes más largos de magnitud que si las variables y las columnas eran del mismo tipo.

Pro + Características Disfrute de los beneficios de la membresía Pro +,aprender más y unirse. 

E-Manual

Precios a la nueva base de datos de Windows Azure SQL 

E-Manual

Los avances en el punto de SQL Server BI para centrarse en el autoservicio, los usuarios

Más de SQL Server consejos de desarrollo Gestión del ciclo de vida de desarrollo de Visual Studio Team System 2008

2) ¿Los cambios masivos en lotes

Desarrolladores veces necesitan modificar datos de una o más columnas para todos o la mayoría de las filas de una tabla. Esto generalmente no es un problema, siempre y cuando la mesa es bastante pequeño.

Si la tabla es grande, sin embargo, su estado de actualización se bloqueará toda la tabla y archivos XML Processing hacer que no esté disponible, incluso para las con funciones de SQL lecturas de datos. Adicionalmente, en una Server mesa muy volátil puede derribar toda la aplicación o sitio web para la duración de la actualización. A veces, un grande, sola transacción como esta se ampliará en gran medida el tamaño del registro de transacciones y - en escenarios extremos - contribuir a quedarse sin espacio en disco en el servidor de base de datos. Por tanto, es una buena práctica para hacer cambios masivos en lotes, junto con copias de seguridad de registro de transacciones frecuentes. En mi experiencia, un lote de 10.000 o 50.000 que funciona mejor. Es difícil especificar un umbral de cuándo debe empezar a considerar la dosificación, ya que todo depende de factores como la rapidez con que el disco O es, la intensidad de uso de E / la mesa, y más. Hay una guía que puede utilizar sin embargo. Un tiempo de espera de comandos típica en ADO.NET es de unos 30 segundos. Mientras que la actualización se lleva a cabo, otros procesos tienen que esperar hasta que se termine. Así que si usted espera que su actualización tardará más de 20 a 25 segundos, que es mejor hacer una actualización por lotes, de lo contrario va a terminar con los tiempos de espera de la aplicación.

Aquí es un código de ejemplo que muestra cómo actualizar una columna en una tabla, utilizando 10.000 como el tamaño del lote: MIENTRAS (0 = 0) COMIENZA TOP (10000) 1 IF@@ROWCOUNT = 0 PAUSA

ACTUALIZACIÓN Persona SET Status = 2 DONDE Estado = FIN

3) Utilizar for-each procedimientos almacenados.

De vez en cuando puede que tenga que realizar la misma acción en todos los objetos de un tipo determinado. Por ejemplo, puede que tenga que asignar un permiso específico para todas las tablas de la base de datos. Los desarrolladores a menudo recurren a los cursores para este tipo de tareas, pero SQL Server viene con dos procedimientos prácticos almacenados que hacen las cosas mucho más fácil: sp_msForEachTable y sp_msForEachDB . Cada una de ellas tiene un comando que se ejecutará como un parámetro.Puede incrustar un signo de interrogación en el parámetro como un marcador de posición para la tabla o base de datos de nombre en el comando. En tiempo de ejecución, SQL Server reemplaza el signo de interrogación con el nombre de la tabla o base de datos y lo ejecuta. Por ejemplo, el código siguiente se ejecuta una copia de seguridad completa para cada base de datos en el servidor, a excepción de TempDB:

EXEC sp_msforeachdb ? 'SI' '' '' 'tempdb' 'RESPALDO BASE DE DATOS? AL DISCO = '' c: \ backups \ ?. bak '' CON INIT '

He aquí otro ejemplo de cómo estos procedimientos almacenados pueden ser útiles - y algo peligroso. El código siguiente elimina los datos de todas las tablas de la base de datos después de desactivar la clave externa.Naturalmente, usted querrá tener cuidado al usar este código: EXEC sp_MSForEachTable 'ALTER TABLE? NOCHECK CONSTRAINT ALL '

EXEC

sp_MSForEachTable '

SI OBJECTPROPERTY (object_id (''? ''), '' TableHasForeignRef '') = 1 DELETE FROM? otra cosa TRUNCATE TABLE? '

EXEC

sp_MSForEachTable 'ALTER TABLE? Restricción CHECK ALL '

4) la versión de su base de datos se basa

Se considera una buena práctica para los desarrolladores implementar versiones numérico de bases de datos, al igual que hacen con las aplicaciones. No requiere mucho esfuerzo para implementar el control de versiones sólo tienes que crear una tabla con un número de versión y las marcas de tiempo adicionales. Una vez que vaya mejor en la asignación de un número de compilación para cada conjunto de secuencias de comandos y la actualización de la tabla de versiones al implementar esos guiones, se hace mucho más fácil de solucionar y comparar sus bases de datos. Incluso se podría codificar las secuencias de comandos para que no se ejecutan si el número de compilación en la base de datos no es mayor que el número de compilación en el guión. La tabla

AWBuildVersion en la base de datos de ejemplo AdventureWorks es un buen ejemplo a la vista. 5) Reducir al mínimo el número de llamadas de red

Este consejo se aplica principalmente a las aplicaciones Web que tiran de los datos de una base de datos. Menos desarrolladores experimentados a menudo no se dan cuenta que cada llamada de base de datos es una operación relativamente cara. No es un gran problema en pequeñas aplicaciones, pero ya que muchos sitios web podrían llegar a ser popular y utilizado por miles de usuarios simultáneos, lo que necesita para empezar a pensar en la escalabilidad y la optimización de sus páginas los tiempos de carga de antemano. He visto páginas hacen hasta 15 llamadas de base de datos, con la ejecución de la mayoría de los procedimientos almacenados que devuelven una sola fila o valor. Una cosa a tener en cuenta es que SQL Server puede devolver varios conjuntos de resultados en un solo procedimiento almacenado. Usted puede utilizar el objeto DataSet en ADO.NET y rellenar una colección de objetos DataTable en una sola llamada de base de datos.

WITH common_table_expression (Transact-SQL) SQL Server 2014

Otras versiones

Especifica un conjunto de resultados temporal con nombre, conocido como expresión de tabla común (CTE). Se deriva de una consulta simple y se define en el ámbito de ejecución de una sola instrucción SELECT, INSERT, UPDATE o DELETE. Esta cláusula también se puede utilizar en una instrucción CREATE VIEW como parte de la instrucción SELECT que la define. Una expresión de tabla común puede incluir referencias a ella misma. Esto se conoce como expresión de tabla común recursiva.

Se aplica a: SQL Server (SQL Server 2008 a versión actual), Windows Azure SQL Database (Versión inicial Convenciones de sintaxis de Transact-SQL

Sintaxis [ WITH [ ,...n ] ] ::= expression_name [ ( column_name [ ,...n ] ) ] AS ( CTE_query_definition )

Argumentos expression_name Es un identificador válido de la expresión de tabla común. expression_name debe ser diferente del nombre de cualquier otra expresión de tabla común definida en la misma cláusula WITH , pero expression_name puede coincidir con el nombre de una vista o tabla base. Cualquier referencia aexpression_name en la consulta utiliza la expresión de tabla común y no el objeto base. column_name Especifica un nombre de columna en la expresión de tabla común. No se permiten nombres duplicados en una misma definición de CTE. El número de nombres de columna especificado debe coincidir con el número de columnas del conjunto de resultados de CTE_query_definition. La lista de nombres de columna es opcional solamente si en la definición de la consulta se suministran nombres diferentes para todas las columnas resultantes. CTE_query_definition Especifica una instrucción SELECT cuyo conjunto de resultados llena la expresión de tabla común. La instrucción SELECT de CTE_query_definition debe cumplir los mismos requisitos que en la creación de una vista, excepto que una expresión CTE no puede definir otra expresión CTE. Para obtener más información, vea la sección Comentarios y el tema CREATE VIEW (Transact-SQL). Si se definen varios parámetros CTE_query_definition, las definiciones de consulta deben combinarse mediante uno de estos operadores de conjunto: UNION ALL, UNION, EXCEPT o INTERSECT.

Comentarios Instrucciones para crear y utilizar expresiones de tabla comunes Las instrucciones siguientes se aplican a expresiones de tabla comunes no recursivas. Para obtener instrucciones que se aplican a expresiones de tabla comunes recursivas, vea "Instrucciones para definir y usar expresiones de tabla comunes recursivas" más adelante.  Una expresión CTE debe ir seguida de una única instrucción SELECT, INSERT, UPDATE o DELETE que haga referencia a una parte o a la totalidad de sus columnas. Una expresión CTE también se puede especificar en una instrucción CREATE VIEW como parte de la instrucción SELECT de definición de la vista.  Se pueden especificar varias definiciones de consulta de CTE en una CTE no recursiva. Las definiciones deben combinarse mediante uno de estos operadores de conjuntos: UNION ALL, UNION, INTERSECT o EXCEPT.  Una expresión CTE puede hacer referencia a ella misma y a otras expresiones CTE previamente definidas en la misma cláusula WITH. No se permite la referencia adelantada.  No se permite especificar más de una cláusula WITH en una expresión CTE. Por ejemplo, si un argumento CTE_query_definition contiene una subconsulta, esta no puede contener ninguna cláusula WITH anidada que defina otra expresión CTE.  No se pueden utilizar las cláusulas siguientes en la definición de CTE_query_definition: o ORDER BY (excepto cuando se especifica una cláusula TOP) o INTO o Cláusula OPTION con sugerencias de consulta o FOR BROWSE  Cuando se utiliza una expresión CTE en una instrucción que forma parte de un lote, la instrucción que la precede debe ir seguida de punto y coma.  Una consulta que haga referencia a una CTE se puede utilizar para definir un cursor.  En la expresión CTE se puede hacer referencia a tablas de servidores remotos.  Cuando se ejecuta una CTE, todas las sugerencias que hagan referencia a ella pueden entrar en conflicto con otras sugerencias detectadas cuando la CTE tiene acceso a sus tablas subyacentes, de la misma manera que las sugerencias que hacen referencia a vistas en las consultas. En ese caso, la consulta devuelve un error.

Instrucciones para definir y usar expresiones de tabla comunes recursivas Las instrucciones siguientes se aplican a la definición de una expresión de tabla común recursiva:  La definición de la CTE recursiva debe contener al menos dos definiciones de consulta de CTE, un miembro no recursivo y un miembro recursivo. Se pueden definir varios miembros no recursivos y recursivos, aunque todas las definiciones de consultas de miembros no recursivos deben colocarse delante de la primera definición de miembro recursivo. Todas las definiciones de consulta de CTE son miembros no recursivos a menos que hagan referencia a la propia CTE.  Los miembros no recursivos deben combinarse mediante uno de estos operadores de conjuntos: UNION ALL, UNION, INTERSECT o EXCEPT. UNION ALL es el único operador de conjuntos permitido entre el último miembro no recursivo y el primer miembro recursivo, y si se combinan varios miembros recursivos.  El número de columnas de los miembros no recursivo y recursivo debe coincidir.  El tipo de datos de una columna del miembro recursivo debe ser igual al tipo de datos de la columna correspondiente en el miembro no recursivo.

La cláusula FROM de un miembro recursivo solo debe hacer referencia una vez a expression_name de CTE.  No se permiten los siguientes elementos en el parámetro CTE_query_definition de un miembro recursivo: o SELECT DISTINCT o GROUP BY o PIVOT (cuando el nivel de compatibilidad de la base de datos sea 110 o superior. Vea Cambios recientes en las características del Motor de base de datos de SQL Server 2014). o HAVING o Agregación escalar o TOP o LEFT, RIGHT, OUTER JOIN (se permite INNER JOIN) o Subconsultas o Una sugerencia aplicada a una referencia recursiva a una CTE dentro de CTE_query_definition. Las instrucciones siguientes se aplican al uso de una expresión de tabla común recursiva:  Todas las columnas devueltas por la expresión CTE recursiva aceptan valores NULL independientemente de la nulabilidad de las columnas devueltas por las instrucciones SELECT participantes.  Una expresión CTE formada incorrectamente puede generar un bucle infinito. Por ejemplo, si la definición de la consulta del miembro recursivo devuelve los mismos valores para las columnas primarias y secundarias, se crea un bucle infinito. Para evitar que se genere un bucle infinito, se puede limitar el número de niveles de recursividad permitidos para una instrucción determinada mediante el uso de la sugerencia MAXRECURSION y un valor de 0 a 32.767 en la cláusula OPTION de la instrucción INSERT, UPDATE, DELETE o SELECT. De esta manera, se puede controlar la ejecución de la instrucción hasta que se resuelva el problema de código que genera el bucle. El valor predeterminado de todo el servidor es 100. Cuando se especifica 0, no se aplica ningún límite. Solo se puede especificar un valor de MAXRECURSION por instrucción. Para obtener más información, vea Sugerencias de consulta (Transact-SQL).  No se puede utilizar una vista que contenga una expresión de tabla común recursiva para actualizar datos.  Se pueden definir cursores en las consultas que utilicen expresiones CTE. La expresión CTE es el argumento de select_statement que define el conjunto de resultados del cursor. En el caso de las CTE recursivas únicamente se permiten los cursores de solo avance rápido y estáticos (de instantánea). Si se especifica otro tipo de cursor en una CTE recursiva, el tipo de cursor se convierte a estático.  En la expresión CTE se puede hacer referencia a tablas de servidores remotos. Si se hace referencia al servidor remoto en el miembro recursivo de la CTE, se crea una cola para cada tabla remota de manera que se pueda tener acceso local a las tablas repetidas veces. Si es una consulta de CTE, aparecerá Index Spool/Lazy Spools en el plan de consulta y tendrá el predicado adicional WITH STACK. Esta es una forma de confirmar la recursividad apropiada.  Las funciones analíticas y de agregado de la parte recursiva del CTE se aplican al conjunto para el nivel de recursividad actual y no al conjunto para el CTE. Las funciones como ROW_NUMBER solo funcionan sobre el subconjunto de datos que les pasa el nivel de recursividad actual y no sobre todo el conjunto de datos pasados a la parte recursiva de la 

CTE. Para obtener más información, vea el ejemplo K. Usar funciones analíticas en una CTE recursiva que sigue.

Ejemplos A.Crear una expresión de tabla común simple En el siguiente ejemplo se muestra el número total de pedidos de venta por año para cada representante de ventas en Adventure Works Cycles. -- Define the CTE expression name and column list. WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) AS -- Define the CTE query. ( SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL ) -- Define the outer query referencing the CTE name. SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear FROM Sales_CTE GROUP BY SalesYear, SalesPersonID ORDER BY SalesPersonID, SalesYear; GO

B.Usar una expresión de tabla común para limitar recuentos y promedios de informes En el siguiente ejemplo se muestra el número medio de pedidos de venta correspondiente a todos los años para los representantes de ventas. WITH Sales_CTE (SalesPersonID, NumberOfOrders) AS ( SELECT SalesPersonID, COUNT(*) FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL GROUP BY SalesPersonID ) SELECT AVG(NumberOfOrders) AS "Average Sales Per Person" FROM Sales_CTE; GO

C.Usar varias definiciones de CTE en una sola consulta En el ejemplo siguiente se muestra cómo definir más de una CTE en una sola consulta. Observe que se usa una coma para separar las definiciones de consulta CTE. La función FORMAT, utilizada para mostrar las cantidades de moneda en un formato de moneda, está disponible en SQL Server 2012 y versiones posteriores. WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear) AS -- Define the first CTE query. ( SELECT SalesPersonID, SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS SalesYear

FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL GROUP BY SalesPersonID, YEAR(OrderDate) ) ,

-- Use a comma to separate multiple CTE definitions.

-- Define the second CTE query, which returns sales quota data by year for each sales person. Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear) AS ( SELECT BusinessEntityID, SUM(SalesQuota)AS SalesQuota, YEAR(QuotaDate) AS SalesQuotaYear FROM Sales.SalesPersonQuotaHistory GROUP BY BusinessEntityID, YEAR(QuotaDate) ) -- Define the outer query by referencing columns from both CTEs. SELECT SalesPersonID , SalesYear , FORMAT(TotalSales,'C','en-us') AS TotalSales , SalesQuotaYear , FORMAT (SalesQuota,'C','en-us') AS SalesQuota , FORMAT (TotalSales -SalesQuota, 'C','en-us') AS Amt_Above_or_Below_Quota FROM Sales_CTE JOIN Sales_Quota_CTE ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear ORDER BY SalesPersonID, SalesYear; GO A continuación se muestra un conjunto de resultados parcial. SalesPersonID SalesYear TotalSales SalesQuotaYear SalesQuota Amt_Above_or_Below_Quota ------------- -------------------------------- ---------- --------------------------------274 2.08) 274 ($48,379.93) 274 ($28,377.09) 274 23.55

2005

$32,567.92

2005

$35,000.00

2006

$406,620.07

2006

$455,000.00

2007

$515,622.91

2007

$544,000.00

2008

$281,123.55

2008

$271,000.00

($2,43

$10,1

D.Usar una expresión de tabla común recursiva para mostrar varios niveles de recursividad En el ejemplo siguiente se muestra la lista jerárquica de los directivos y de los empleados que tienen a su cargo. En el ejemplo se empieza creando y rellenando la tabladbo.MyEmployees. -- Create an Employee table. CREATE TABLE dbo.MyEmployees ( EmployeeID smallint NOT NULL, FirstName nvarchar(30) NOT NULL, LastName nvarchar(40) NOT NULL, Title nvarchar(50) NOT NULL, DeptID smallint NOT NULL, ManagerID int NULL, CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) ); -- Populate the table with values. INSERT INTO dbo.MyEmployees VALUES (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL) ,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1) ,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273) ,(275, N'Michael', N'Blythe', N'Sales Representative',3,274) ,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274) ,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273) ,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285) ,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273) ,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16); USE AdventureWorks2012; GO WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel FROM dbo.MyEmployees WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 FROM dbo.MyEmployees AS e INNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) SELECT ManagerID, EmployeeID, Title, EmployeeLevel FROM DirectReports ORDER BY ManagerID; GO

E.Usar una expresión de tabla común recursiva para mostrar dos niveles de recursividad En el ejemplo siguiente se muestran los directivos y los empleados que tienen a su cargo. El número de niveles devueltos está limitado a dos. USE AdventureWorks2012;

GO WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel FROM dbo.MyEmployees WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 FROM dbo.MyEmployees AS e INNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) SELECT ManagerID, EmployeeID, Title, EmployeeLevel FROM DirectReports WHERE EmployeeLevel