sql server 2005 para desarrolladores madrid
TRANSCRIPT
SQL Server 2005 para SQL Server 2005 para desarrolladoresdesarrolladores
Jesús López Méndez (SqlRanger)Jesús López Méndez (SqlRanger)Mentor Asociado. MVP Visual Developer–Visual BasicMentor Asociado. MVP Visual Developer–Visual [email protected]@solidqualitylearning.com
2211/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
More than just trainingMore than just training
Mentores Principales:Mentores Principales: Itzik Ben-Gan, Kalen Delaney, Fernando G. Guerrero, Itzik Ben-Gan, Kalen Delaney, Fernando G. Guerrero,
Michael Hotek, Brian Moran , Ron Talmage Michael Hotek, Brian Moran , Ron Talmage
Iberoamericana:Iberoamericana: Miguel Egea, Alejandro Leguízamo, Jesús López, Pablo Miguel Egea, Alejandro Leguízamo, Jesús López, Pablo
Pelaez, Jordi Rambla, Eladio Rincón, Daniel Seara, Pelaez, Jordi Rambla, Eladio Rincón, Daniel Seara, Guillermo Som, Antonio Soto, Adolfo WiernikGuillermo Som, Antonio Soto, Adolfo Wiernik
Ayudando a obtener lo mejor de SQLServer y .Net Ayudando a obtener lo mejor de SQLServer y .Net concon EntrenamientoEntrenamiento Consultoría Consultoría MentoringMentoring
3311/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
AgendaAgenda
Lo nuevo en SQL ServerLo nuevo en SQL Server
Integración con el CLRIntegración con el CLR
Mejoras en T-SQLMejoras en T-SQL
XMLXML
Service BrokerService Broker
4411/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Lo nuevo de SQL Server 2005Lo nuevo de SQL Server 2005
Mejoras en la seguridadMejoras en la seguridad
Mejoras en el motor relacionalMejoras en el motor relacional
Integración con .NET FrameworkIntegración con .NET Framework
Tipo XML nativo y el lenguaje XQueryTipo XML nativo y el lenguaje XQuery
Service BrokerService Broker
Servicios Web XMLServicios Web XML
5511/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Lo nuevo en el acceso a datosLo nuevo en el acceso a datos
Soporte para Soporte para Tipos de datos definidos por el usuarioTipos de datos definidos por el usuario Tipo XMLTipo XML
Cliente nativo para ODBC y OLEDBCliente nativo para ODBC y OLEDBMejoras en SqlClient:Mejoras en SqlClient: MARSMARS NotificacionesNotificaciones Ejecución asíncronaEjecución asíncrona
SQL XML:SQL XML: Soporte XQuerySoporte XQuery
6611/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Integración con CLRIntegración con CLR
SQL Server como anfitrión del CLRSQL Server como anfitrión del CLR
Objetivos:Objetivos: SeguridadSeguridad FiabilidadFiabilidad RendimientoRendimiento
Uso de ensambladosUso de ensamblados
7711/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
CLR HospedadoCLR Hospedado
Las aplicaciones se ejecutan en el mismo espacio Las aplicaciones se ejecutan en el mismo espacio de direccionesde direcciones
Procedimientos almacenados escritos en cualquier Procedimientos almacenados escritos en cualquier lenguaje .NETlenguaje .NET
Permite el acceso a recursos fuera de SQL ServerPermite el acceso a recursos fuera de SQL Server
Controlado por SQL ServerControlado por SQL Server ICorRuntimeHostICorRuntimeHost ICLRRuntimeHostICLRRuntimeHost
Un dominio de aplicación por cada base de datosUn dominio de aplicación por cada base de datos
8811/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Seguridad CLRSeguridad CLR
CAS ya no valeCAS ya no vale
Establecido por el DBA a nivel de Establecido por el DBA a nivel de ensamblado:ensamblado: SeguroSeguro Acceso externoAcceso externo InseguroInseguro
9911/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Registro de ensambladosRegistro de ensamblados
CREATE ASSEMBLYCREATE ASSEMBLY Se carga desde disco o streamSe carga desde disco o stream Se le asigna un nombreSe le asigna un nombre Se guarda en la base de datosSe guarda en la base de datos
DROP ASSEMBLY DROP ASSEMBLY Primero eliminar los objetos dependientesPrimero eliminar los objetos dependientes
ALTER ASSEMBLYALTER ASSEMBLY Sin alterar las firmas de los métodosSin alterar las firmas de los métodos
CREATE ASSEMBLY math FROM 'c:\types\math.dll‘WITH PERMISSION_SET = EXTERNAL_ACCESS
101011/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
ReferenciasReferencias
SQL Server determina las referencias del SQL Server determina las referencias del ensambladoensamblado
Tienen que estar en el mismo directorio, no Tienen que estar en el mismo directorio, no en la GACen la GAC
Los añade si es necesarioLos añade si es necesario
111111/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Información de ensambladosInformación de ensamblados
Sys.asembliesSys.asemblies
Sys.assembly_filesSys.assembly_files
Sys.assembly_referencesSys.assembly_references
121211/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Lo que se puede hacerLo que se puede hacer
Funciones definidas por el usuarioFunciones definidas por el usuario
Procedimientos almacenadosProcedimientos almacenados
TriggersTriggers
Tipos de datos definidos por el usuarioTipos de datos definidos por el usuario
AgregadosAgregados
131311/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
El proveedor SqlServerEl proveedor SqlServer
Permite acceder a datos de SQL Server desde Permite acceder a datos de SQL Server desde procedimientos CLRprocedimientos CLR
No se necesita establecer una conexiónNo se necesita establecer una conexión
SqlContextSqlContext
SqlCommandSqlCommand
SqlTransactionSqlTransaction
SqlDataReaderSqlDataReader
SqlPipeSqlPipe
SqlTriggerContextSqlTriggerContext
141411/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Funciones CLRFunciones CLRLa clase tiene que ser públicaLa clase tiene que ser pública
La función tiene que ser estáticaLa función tiene que ser estática
Parámetros por valor de tipo SqlTypesParámetros por valor de tipo SqlTypes
Pueden devolver un valor escalar o un conjunto de Pueden devolver un valor escalar o un conjunto de registrosregistros
namespace Math{ public class Inverter { public static SqlInt32 Invert(SqlInt x) { return -x; } }}
151511/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Atributo SqlFunctionAtributo SqlFunction
IsDeterministicIsDeterministic
DataAccess:DataAccess: DataAccessKind.NoneDataAccessKind.None DataAccessKind.ReadDataAccessKind.Read
SystemDataAccess:SystemDataAccess: SystemDataAccessKind.NodeSystemDataAccessKind.Node SystemDataAccessKind.ReadSystemDataAccessKind.Read
161611/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Registro de funcionesRegistro de funciones
CREATE FUNCTIONCREATE FUNCTION
Asignación de nombreAsignación de nombre
Parámetros posicionalesParámetros posicionales
CREATE FUNCTION DoInvert(@A int) returns intAs EXTERNAL NAME math.Math.Inverter.Invert
171711/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Uso de funciones CLRUso de funciones CLR
Como cualquier otra función T-SQLComo cualquier otra función T-SQL
181811/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
ProcedimientosProcedimientos almacenados CLR almacenados CLR
La clase tiene que ser públicaLa clase tiene que ser pública
La función tiene que ser estáticaLa función tiene que ser estática
Parámetros por valor y por referencia de tipo SqlTypesParámetros por valor y por referencia de tipo SqlTypes
Pueden devolver un valor entero o nadaPueden devolver un valor entero o nada
Pueden devolver un conjunto de registrosPueden devolver un conjunto de registros
Atributo SqlProcedureAtributo SqlProcedure
191911/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Registro de procedimientosRegistro de procedimientos
CREATE PROCEDURECREATE PROCEDURE
Asignación de nombreAsignación de nombre
Parámetros posicionalesParámetros posicionales
202011/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Triggers CLRTriggers CLR
La clase tiene que ser públicaLa clase tiene que ser pública
El método no devuelve ningún valor El método no devuelve ningún valor
El método no admite parámetrosEl método no admite parámetros
212111/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Triggers CLRTriggers CLR
SqlTriggerContextSqlTriggerContext:: Las columnas que han cambiadoLas columnas que han cambiado La acción que provocó el La acción que provocó el triggertrigger SqlContext.GetTriggerContextSqlContext.GetTriggerContext
SqlContext.GetCommandSqlContext.GetCommand:: Acceso a Acceso a deleteddeleted e e insertedinserted
Atributo Atributo SqlTriggerSqlTrigger:: NombreNombre Tipo (after, instead of)Tipo (after, instead of) Acción (insert, update, delete)Acción (insert, update, delete)
222211/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Registrar un trigger CLRRegistrar un trigger CLR
CREATE TRIGGERCREATE TRIGGER
CREATE TRIGGER EmailAuditON UsersFOR INSERTASEXTERNAL NAME SQLCLRTest.testclrtrigger.EmailAudit
232311/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Tipos CLR definidos por el usuarioTipos CLR definidos por el usuario
Clase o estructuraClase o estructura
Serializable (nativo o Serializable (nativo o IBinarySerializableIBinarySerializable))
Convertible desde y hacia cadena (Convertible desde y hacia cadena (ParseParse(), (), ToStringToString())())
Debe ser nulable (Debe ser nulable (INullable, IsNull, NullINullable, IsNull, Null))
242411/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Tipos CLR definidos por el usuarioTipos CLR definidos por el usuario
Atributo SqlUserDefinedTypeAtributo SqlUserDefinedType
Constructor vacíoConstructor vacío
Expone métodos y propiedades públicosExpone métodos y propiedades públicos
No soporta herenciaNo soporta herencia
Las variables y propiedades estáticas deben Las variables y propiedades estáticas deben ser inmutablesser inmutables
252511/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Uso de los tipos CLRUso de los tipos CLR
Definición de columnas de tablasDefinición de columnas de tablas
Variables, Parámetros y expresionesVariables, Parámetros y expresiones
Índices (Índices (IsByteOrderedIsByteOrdered))
Métodos que modifican sólo en Métodos que modifican sólo en UPDATEUPDATESELECT thepoint.m_x, thepoint.m_y FROM point_tabgo
-- use mutator-- the name of mutator is case-sensitive!UPDATE point_tab SET thepoint.SetXY(20, 30) WHERE thepoint.m_x = 0
262611/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Registrar un tipo CLRRegistrar un tipo CLR
CREATE TYPECREATE TYPE
CREATE ASSEMBLY Point FROM 'c:\types\Point.dll'GO
CREATE TYPE PointClsEXTERNAL NAME Point.PointClsGO
272711/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Agregados CLRAgregados CLR
Funciones de agregadoFunciones de agregado
Realizan cálculos sobre un grupo de Realizan cálculos sobre un grupo de registrosregistros
Requisitos:Requisitos: Implementado como una claseImplementado como una clase Contrato (Init, Accumulate, Merge, Terminate)Contrato (Init, Accumulate, Merge, Terminate) Atributo SqlUserDefinedAggregateAtributo SqlUserDefinedAggregate
282811/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
ContratoContrato
Sub Init()Sub Init() Reinicializar el estado de la instanciaReinicializar el estado de la instancia Llamado antes de cualquier otroLlamado antes de cualquier otro
Sub Accumulate( Value As InputSqlType)Sub Accumulate( Value As InputSqlType) Modificar el estado para acumular el nuevo valorModificar el estado para acumular el nuevo valor Llamado por cada registro del grupo o subgrupoLlamado por cada registro del grupo o subgrupo
Sub Merge( Value As ThisUdtaType)Sub Merge( Value As ThisUdtaType) Combinar el acumulado de otro subgrupo con esteCombinar el acumulado de otro subgrupo con este Llamado cuando el grupo se particiona en subgruposLlamado cuando el grupo se particiona en subgrupos
Function Terminate() As ResultSqlType Function Terminate() As ResultSqlType Devolver el resultado del cálculo del agregadoDevolver el resultado del cálculo del agregado Llamado para obtener el resultadoLlamado para obtener el resultado
292911/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Atributo SqlUserDefinedAggregateAtributo SqlUserDefinedAggregate
Aplicado a la clase que implementa el UDAGAplicado a la clase que implementa el UDAG
Optimizador de consultas:Optimizador de consultas: IsInvariantToDuplicatesIsInvariantToDuplicates IsInvariantToNullsIsInvariantToNulls IsInvariantToOrderIsInvariantToOrder IsNullIfEmptyIsNullIfEmpty
Formato de serialización (Format)Formato de serialización (Format) NativeNative UserDefined (IBinarySerialize)UserDefined (IBinarySerialize)
303011/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Registro de agregados CLRRegistro de agregados CLR
CREATE AGGREGATECREATE AGGREGATE
-- UDAGGS are scoped to the databaseCREATE AGGREGATE Concatenate ( @Value varchar(50) )RETURNS varchar(8000)EXTERNAL NAME AssemblyName.Namespace.Concatenate
313111/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Mejoras en T-SQLMejoras en T-SQL
Tipos de datos muy grandesTipos de datos muy grandes
Aislamiento snapshotAislamiento snapshot
Triggers DDLTriggers DDL
BULK INSERTBULK INSERT
Manejo de excepcionesManejo de excepciones
CTE’sCTE’s
PIVOT y UNPIVOTPIVOT y UNPIVOT
Funciones rankingFunciones ranking
CROSS APLYCROSS APLY
323211/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Tipos de datos muy grandesTipos de datos muy grandes
TEXT – VARCHAR(MAX)TEXT – VARCHAR(MAX)
NTEXT- NVARCHAR(MAX)NTEXT- NVARCHAR(MAX)
IMAGE – VARBINARY(MAX)IMAGE – VARBINARY(MAX)
Declaración de variablesDeclaración de variables
ConcatenaciónConcatenación
Funciones de cadenaFunciones de cadena
Actualizables directamenteActualizables directamente
333311/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Aislamiento snapshotAislamiento snapshot
Los lectores no bloquean a los Los lectores no bloquean a los modificadoresmodificadoresLos modificadores no bloquean a los Los modificadores no bloquean a los lectoreslectoresVarias versiones de las filasVarias versiones de las filasConflictos de concurrenciaConflictos de concurrenciaALLOW_SNAPSHOT_ISOLATIONALLOW_SNAPSHOT_ISOLATIONSET TRANSACTION ISOLATION LEVEL SET TRANSACTION ISOLATION LEVEL SNAPSHOTSNAPSHOT
343411/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Triggers DDLTriggers DDLResponden a instrucciones DDL (CREATE TABLE, ALTER Responden a instrucciones DDL (CREATE TABLE, ALTER TABLE, etc)TABLE, etc)
Usos:Usos: Prevenir y/o registrar cambios en el esquemaPrevenir y/o registrar cambios en el esquema Realizar acciones personalizadasRealizar acciones personalizadas
CREATE TABLE ddl_log (data xml)GO -- Create TriggerCREATE TRIGGER trig_create_tab ON DATABASE FOR CREATE_TABLE ASINSERT ddl_log VALUES (EVENTDATA())GO
353511/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Triggers DDLTriggers DDL
<EVENT_INSTANCE> <PostTime>2003-04-17T20:31:03.360</PostTime> <SPID>51</SPID> <EventType>CREATE_TABLE</EventType> <Database>Demo1</Database> <Schema>dbo</Schema> <Object>t1</Object> <ObjectType>TABLE</ObjectType> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText> CREATE TABLE t1 (x int) </CommandText> </TSQLCommand></EVENT_INSTANCE>
-- Perform DDL. CREATE TABLE t1 (x int)GO-- Check LogSELECT * FROM ddl_logGO
363611/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
BULK INSERTBULK INSERT
Ahora es un proveedor OLEDBAhora es un proveedor OLEDB
-- insert-select syntaxINSERT Northwind.dbo.[Order Details] -- bulk insert option WITH (BULK_FIRE_TRIGGERS)SELECT * -- "bulk" rowset provider -- special bulk insert options FROM OPENROWSET ( BULK 'f:\orders\lineitem.tbl', FIELDTERMINATOR = '|', ROWTERMINATOR = ':\n') as d
373711/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Manejo de excepcionesManejo de excepciones
Construcción TRY … CATCHConstrucción TRY … CATCH
Información del error:Información del error: ERROR_MESSAGE()ERROR_MESSAGE() ERROR_NUMBER() ERROR_NUMBER() ERROR_SEVERITY() ERROR_SEVERITY() @@ERROR@@ERROR
Errores de severidad >20 no pueden capturarseErrores de severidad >20 no pueden capturarse
Pueden anidarsePueden anidarse
XACT_STATE () XACT_STATE ()
383811/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Sintaxis TRY … CATCHSintaxis TRY … CATCH
BEGIN TRY -- Instrucciones que pueden fallarEND TRYBEGIN CATCH -- manejo del errorEND CATCH
393911/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Common table expressionsCommon table expressions
Similar a una vista temporalSimilar a una vista temporal
Puede usarse en INSERT, UPDATE y Puede usarse en INSERT, UPDATE y DELETEDELETE
WITH mid AS(SELECT ((MAX(value) - MIN(value)) / 2) AS midval FROM invoices)SELECT CASE WHEN value > mid.midval THEN 0 ELSE 1 END AS half, invoices.* FROM invoices, mid ORDER BY half
404011/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Sintaxis de las CTE’sSintaxis de las CTE’s
Empiezan con WITHEmpiezan con WITH
Separadas por comasSeparadas por comas
Seguidas por una sentencia SELECT, Seguidas por una sentencia SELECT, INSERT, UPDATE o DELETEINSERT, UPDATE o DELETE
WITH low AS (SELECT ((MAX(amount)) / 3) AS v FROM invoices),high AS (SELECT (2 * MAX(amount) / 3) AS v FROM invoices)SELECT id, amount, amount - low.v FROM invoices, low, high WHERE invoices.amount > low.v AND invoices.amount <= high.v
414111/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Ejecución de las CTE’sEjecución de las CTE’s
Se evalúan sólo una vezSe evalúan sólo una vez
WITH low AS (SELECT ((max(amount)) / 3) AS v FROM invoices),high AS (SELECT (2 * max(amount) / 3) AS v FROM invoices)select id, amount, amount - low.v FROM invoices, low, high WHERE invoices.amount > low.v AND invoices.amount <= high.v
SELECT id, amount, amount - (SELECT (max(amount) / 3) FROM invoices) FROM invoices where amount > (SELECT (max(amount) / 3) FROM invoices) and amount < (SELECT (2 * max(amount) / 3) FROM invoices)
424211/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Consultas recursivasConsultas recursivas
Tienen tres partes:Tienen tres partes: Raíz, seguido por UNION ALL, realiza la inicializaciónRaíz, seguido por UNION ALL, realiza la inicialización Miembro recursivo, se ejecuta hasta que no devuelva registrosMiembro recursivo, se ejecuta hasta que no devuelva registros La sentencia SELECT externaLa sentencia SELECT externa
WITH descendant(parent, id, amount) AS(SELECT parent, id, amount FROM partsTree WHERE id = @startUNION ALLSELECT P.parent, P.id, P.amount FROM partsTree AS P INNER JOIN descendant AS A ON A.id = P.parent)SELECT id FROM descendant
Raíz, se ejecuta una vez
Miembro recursivo
Combinado con el resultado anterior
Select externa
434311/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Ejemplo de CTE recursivaEjemplo de CTE recursivaid parent 1 NULL 2 NULL 3 2 4 2 5 3
WITH descendant(parent, id, amount) AS(SELECT parent, id, amount FROM partsTree WHERE id = 2UNION ALLSELECT P.parent, P.id, P.amount FROM partsTree AS P INNER JOIN descendant AS A ON A.id = P.parent)SELECT id FROM descendant
2, 3, 4, 5
Tabla invoices
CTE recursiva
select externa
resultado
444411/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
PIVOTPIVOT
Convierte filas en columnasConvierte filas en columnas
454511/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
SELECT * FROM propertiesPIVOT (MAX(value)FOR name IN ([color], [type], [amount]))AS PWHERE id IN(SELECT id FROM products WHERE name='Swish')
make column wherename = one of these
pivot column
select only propertiesfor the Swish product
value column
id color type amount-- ------- -------- -------1 blue oil 1 gal3 red latex 1 qt4 white oil 1 pt
pivoted properties of Swish product
id not mentionedin pivot expression
properties grouped by id
464611/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Pivot y UnPivotPivot y UnPivot
Pivot convierte filas en columnasPivot convierte filas en columnas
Unpivot hace lo contrarioUnpivot hace lo contrario--Create the table and insert values as portrayed in the above example.CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,Emp3 int, Emp4 int, Emp5 int)GOINSERT INTO pvt VALUES (1,4,3,5,4,4)INSERT INTO pvt VALUES (2,4,1,5,5,5)INSERT INTO pvt VALUES (3,4,3,5,4,4)INSERT INTO pvt VALUES (4,4,2,5,5,4)INSERT INTO pvt VALUES (5,5,1,5,5,5)GO--Unpivot the table.SELECT VendorID, Employee, OrdersFROM (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) pUNPIVOT (Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5))AS unpvtGO
VendorID Employee Orders1 Emp1 41 Emp2 31 Emp3 51 Emp4 41 Emp5 42 Emp1 42 Emp2 12 Emp3 52 Emp4 52 Emp5 5
474711/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Funciones RANKINGFunciones RANKING
ROWNUMBER()ROWNUMBER()
RANK()RANK()
DENSERANK()DENSERANK()
NTILE()NTILE()
Pueden agruparse en particionesPueden agruparse en particiones PARTITION BYPARTITION BY
484811/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Funciones RANKINGFunciones RANKINGorderid customerid num rank denserank tile5----------- ---------- ------ ------ --------- ------10308 ANATR 1 1 1 110365 ANTON 2 2 2 110355 AROUT 3 3 3 210383 AROUT 4 3 3 210278 BERGS 5 5 4 310280 BERGS 6 5 4 310384 BERGS 7 5 4 410265 BLONP 8 8 5 410297 BLONP 9 8 5 510360 BLONP 10 8 5 5
SELECT orderid, customerid, SELECT orderid, customerid,
ROW_NUMBER() OVER(ORDER BY customerid) AS num,ROW_NUMBER() OVER(ORDER BY customerid) AS num,
RANK() OVER(ORDER BY customerid) AS [rank],RANK() OVER(ORDER BY customerid) AS [rank],
DENSE_RANK() OVER(ORDER BY customerid) AS [denserank],DENSE_RANK() OVER(ORDER BY customerid) AS [denserank],
NTILE(5) OVER(ORDER BY customerid) AS ntile5NTILE(5) OVER(ORDER BY customerid) AS ntile5
FROM ordersFROM orders
SELECT orderid, customerid, SELECT orderid, customerid,
ROW_NUMBER() OVER(ORDER BY customerid) AS num,ROW_NUMBER() OVER(ORDER BY customerid) AS num,
RANK() OVER(ORDER BY customerid) AS [rank],RANK() OVER(ORDER BY customerid) AS [rank],
DENSE_RANK() OVER(ORDER BY customerid) AS [denserank],DENSE_RANK() OVER(ORDER BY customerid) AS [denserank],
NTILE(5) OVER(ORDER BY customerid) AS ntile5NTILE(5) OVER(ORDER BY customerid) AS ntile5
FROM ordersFROM orders
494911/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
CROSS APPLY y OUTER APPLYCROSS APPLY y OUTER APPLY
Utilizadas para hacer joins con funciones Utilizadas para hacer joins con funciones tabularestabulares Inner joinInner join Outer JoinOuter Join
CREATE FUNCTION Greater(@v float, @t float) RETURNS TABLE AS RETURN SELECT @v AS v WHERE @v > @t
SELECT * FROM invoice CROSS APPLY Greater(invoice.amount, 1500)
505011/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
XML y SQL Server 2005XML y SQL Server 2005
Tipo de dato XMLTipo de dato XML Índices sobre campos XMLÍndices sobre campos XML
Gestión de esquemas XSDGestión de esquemas XSD
Consulta XQueryConsulta XQuery
Vistas XML (SQLXML)Vistas XML (SQLXML)
Mejoras en FOR XML y OPENXMLMejoras en FOR XML y OPENXML
515111/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
XML y SQL Server 2005XML y SQL Server 2005EsquemasEsquemas
Los campos XML pueden asociarse con esquemasLos campos XML pueden asociarse con esquemas
CREATE TABLE Invoices(CREATE TABLE Invoices(
id INT PRIMARY KEY,id INT PRIMARY KEY,
factura XML(EsquemaFactura)factura XML(EsquemaFactura)
......
CREATE TABLE Invoices(CREATE TABLE Invoices(
id INT PRIMARY KEY,id INT PRIMARY KEY,
factura XML(EsquemaFactura)factura XML(EsquemaFactura)
......
Esquemas almacenados en la base de datosEsquemas almacenados en la base de datosCREATE XML SCHEMA COLLECTION geocollCREATE XML SCHEMA COLLECTION geocoll
'<xs:schema ...'<xs:schema ...
targetNamespace=urn:geo>targetNamespace=urn:geo>
......
</xs:schema>'</xs:schema>'
CREATE XML SCHEMA COLLECTION geocollCREATE XML SCHEMA COLLECTION geocoll
'<xs:schema ...'<xs:schema ...
targetNamespace=urn:geo>targetNamespace=urn:geo>
......
</xs:schema>'</xs:schema>'
525211/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
XML y SQL Server 2005XML y SQL Server 2005Acceso a campos XMLAcceso a campos XML
XQUERYXQUERY Standard W3C (Last Call Working Draft 4/4/2005)Standard W3C (Last Call Working Draft 4/4/2005)
Con extensiones para la actualizaciónCon extensiones para la actualización
Basado en XPathBasado en XPathMucha mayor riqueza para búsquedas complejasMucha mayor riqueza para búsquedas complejas
535311/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
XML y SQL Server 2005XML y SQL Server 2005XQuery desde T-SQLXQuery desde T-SQL
xml.query: devuelve un tipo XMLxml.query: devuelve un tipo XML
xml.exist: devuelve un booleano si hay resultadoxml.exist: devuelve un booleano si hay resultado
xml.value: devuelve un valor simple (escalar)xml.value: devuelve un valor simple (escalar)
xml.nodes: devuelve una tabla con una columnaxml.nodes: devuelve una tabla con una columna
xml.modifyxml.modify: : modifica el XMLmodifica el XML
SELECT id, xDoc.query('for $s in /doc[@id = 123]//sec[@num >= 3]return <topic>{data($s/heading)}</topic>') FROM docs
545411/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
XML y SQL Server 2005XML y SQL Server 2005T-SQL desde XQueryT-SQL desde XQuery
sql:variablesql:variable Acceso a variables T-SQL desde XqueryAcceso a variables T-SQL desde Xquery
sql:columnsql:column Acceso a la columna a la que pertenece el XMLAcceso a la columna a la que pertenece el XML
select CV.query(select CV.query(
'for $elem in /CV/DatosPersonales'for $elem in /CV/DatosPersonales
returnreturn
<Nombre><Nombre>
{ sql:column("Nombre") }{ sql:column("Nombre") }
</Nombre> '</Nombre> '
) from DatosPersonales) from DatosPersonales
select CV.query(select CV.query(
'for $elem in /CV/DatosPersonales'for $elem in /CV/DatosPersonales
returnreturn
<Nombre><Nombre>
{ sql:column("Nombre") }{ sql:column("Nombre") }
</Nombre> '</Nombre> '
) from DatosPersonales) from DatosPersonales
555511/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
XML y SQL Server 2005XML y SQL Server 2005ÍndicesÍndices
Pueden definirse índices en columnas XMLPueden definirse índices en columnas XML Aceleran las sentencias XQueryAceleran las sentencias XQuery
Varios tipos de indexaciónVarios tipos de indexación AtributosAtributos ValoresValores XPathXPath
XML y SQL Server 2005XML y SQL Server 2005
575711/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Documentación disponibleDocumentación disponible
XML Options in Microsoft SQL Server 2005XML Options in Microsoft SQL Server 2005 Microsoft – Enero 2005 – 34 páginasMicrosoft – Enero 2005 – 34 páginas http://msdn.microsoft.com/SQL/2005/2005Articles/default.aspx?pull=/http://msdn.microsoft.com/SQL/2005/2005Articles/default.aspx?pull=/
library/en-us/dnsql90/html/sql2k5xmloptions.asplibrary/en-us/dnsql90/html/sql2k5xmloptions.asp
XML Support in Microsoft SQL Server 2005XML Support in Microsoft SQL Server 2005 Shankar Pal, Mark Fussell, Irwin DolobowskyShankar Pal, Mark Fussell, Irwin Dolobowsky Microsoft Corporation – Mayo 2004 – 39 páginasMicrosoft Corporation – Mayo 2004 – 39 páginas http://msdn.microsoft.com/SQL/2005/2005Articles/default.aspx?pull=/http://msdn.microsoft.com/SQL/2005/2005Articles/default.aspx?pull=/
library/en-us/dnsql90/html/sql2k5xml.asplibrary/en-us/dnsql90/html/sql2k5xml.asp
585811/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Service BrokerService Broker
595911/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Service BrokerService Broker¿Qué es?¿Qué es?
Sistema de mensajería asíncronaSistema de mensajería asíncrona Implementado 100% en SQL ServerImplementado 100% en SQL ServerDestinatariosDestinatarios La misma BBDDLa misma BBDD Otra BBDD en la misma instancia o en otra instanciaOtra BBDD en la misma instancia o en otra instancia Otro servidor SQL Server remotoOtro servidor SQL Server remotoIncluyeIncluye Nuevos objetosNuevos objetos Nuevas sentencias T-SQLNuevas sentencias T-SQLUtilizado internamente en SQL Server 2005Utilizado internamente en SQL Server 2005 Query NotificationsQuery Notifications Event NotificationsEvent NotificationsLo pueden usar otras aplicacionesLo pueden usar otras aplicaciones
606011/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Mensajería asíncrona Mensajería asíncrona (¿Cómo dice?)(¿Cómo dice?)
Usamos una cola para:Usamos una cola para: Que el proceso deje un Que el proceso deje un mensajemensaje en la cola y en la cola y
recupere inmediatamente el control del proceso recupere inmediatamente el control del proceso ((asincroníaasincronía))
El software de gestión de colas se encarga de El software de gestión de colas se encarga de gestionar la entrega de ese mensaje en su destinogestionar la entrega de ese mensaje en su destino
Con todos los matices adicionales (persistencia, garantía, Con todos los matices adicionales (persistencia, garantía, secuencia, entrega única, transacción, trazabilidad…)secuencia, entrega única, transacción, trazabilidad…)
Esas son las diferencias con un socket TCPEsas son las diferencias con un socket TCP
El receptor puede procesar los mensajes a su El receptor puede procesar los mensajes a su conveniencia (con/sin prioridad, p.e.)conveniencia (con/sin prioridad, p.e.)
616111/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Service BrokerService BrokerMensajesMensajes
Unidad de comunicaciónUnidad de comunicación
Tres tiposTres tipos BinariosBinarios Solo cabeceraSolo cabecera XML (tipados o sin tipar)XML (tipados o sin tipar)
CREATE MESSAGE TYPE CREATE MESSAGE TYPE [[//company.com/Expenses/SubmitExpense]//company.com/Expenses/SubmitExpense]]]
VALIDATION = VALID_XML VALIDATION = VALID_XML
WITH SCHEMA COLLECTION invoice_xsdWITH SCHEMA COLLECTION invoice_xsd
CREATE MESSAGE TYPE CREATE MESSAGE TYPE [[//company.com/Expenses/SubmitExpense]//company.com/Expenses/SubmitExpense]]]
VALIDATION = VALID_XML VALIDATION = VALID_XML
WITH SCHEMA COLLECTION invoice_xsdWITH SCHEMA COLLECTION invoice_xsd
626211/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Service BrokerService BrokerContratosContratos
Define los mensajes que se pueden intercambiarDefine los mensajes que se pueden intercambiar
CREATE CONTRACT CREATE CONTRACT
[//company.com/Expenses/ExpenseSubmission][//company.com/Expenses/ExpenseSubmission]
( (
[//company.com/Expenses/SubmitExpense][//company.com/Expenses/SubmitExpense]
SENT BY INITIATOR,SENT BY INITIATOR,
[//company.com/Expenses/ApprovedOrDenied][//company.com/Expenses/ApprovedOrDenied]
SENT BY TARGET,SENT BY TARGET,
[//company.com/Expenses/ExpenseReimbursed][//company.com/Expenses/ExpenseReimbursed]
SENT BY TARGETSENT BY TARGET
))
CREATE CONTRACT CREATE CONTRACT
[//company.com/Expenses/ExpenseSubmission][//company.com/Expenses/ExpenseSubmission]
( (
[//company.com/Expenses/SubmitExpense][//company.com/Expenses/SubmitExpense]
SENT BY INITIATOR,SENT BY INITIATOR,
[//company.com/Expenses/ApprovedOrDenied][//company.com/Expenses/ApprovedOrDenied]
SENT BY TARGET,SENT BY TARGET,
[//company.com/Expenses/ExpenseReimbursed][//company.com/Expenses/ExpenseReimbursed]
SENT BY TARGETSENT BY TARGET
))
636311/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Service BrokerService BrokerColasColas
Puntos de entrada a los serviciosPuntos de entrada a los servicios Pueden leerse desde T-SQLPueden leerse desde T-SQL Pueden llamar a un procedimientoPueden llamar a un procedimiento
CREATE QUEUE ExpenseQueueCCREATE QUEUE ExpenseQueueC
WITH STATUS = ONWITH STATUS = ON
ACTIVATION (ACTIVATION (
PROCEDURE_NAME = expense_activation,PROCEDURE_NAME = expense_activation,
MAX_QUEUE_READERS = 5,MAX_QUEUE_READERS = 5,
EXECUTE_AS USER = 'sa')EXECUTE_AS USER = 'sa')
CREATE QUEUE ExpenseQueueCCREATE QUEUE ExpenseQueueC
WITH STATUS = ONWITH STATUS = ON
ACTIVATION (ACTIVATION (
PROCEDURE_NAME = expense_activation,PROCEDURE_NAME = expense_activation,
MAX_QUEUE_READERS = 5,MAX_QUEUE_READERS = 5,
EXECUTE_AS USER = 'sa')EXECUTE_AS USER = 'sa')
646411/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Service BrokerService BrokerServiciosServicios
Funcionalidad expuesta en las colasFuncionalidad expuesta en las colas
Compuesto porCompuesto por Una colaUna cola Varios contratosVarios contratos
CREATE SERVICE [//company.com/Expenses] CREATE SERVICE [//company.com/Expenses]
ON ExpenseQueueON ExpenseQueue
([//company.com/Expenses/ExpenseSubmission],([//company.com/Expenses/ExpenseSubmission],
[//company.com/Expenses/ExpenseProcessing])[//company.com/Expenses/ExpenseProcessing])
CREATE SERVICE [//company.com/Expenses] CREATE SERVICE [//company.com/Expenses]
ON ExpenseQueueON ExpenseQueue
([//company.com/Expenses/ExpenseSubmission],([//company.com/Expenses/ExpenseSubmission],
[//company.com/Expenses/ExpenseProcessing])[//company.com/Expenses/ExpenseProcessing])
656511/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Service BrokerService BrokerEnvío de mensajesEnvío de mensajes
1.- Creación del diálogo1.- Creación del diálogo Un diálogo ordena y correlaciona los mensajesUn diálogo ordena y correlaciona los mensajes
2.- Envío del mensaje2.- Envío del mensaje Mediante comando SENDMediante comando SEND
SET @ExpenseReport = “<report>…</report>”;SET @ExpenseReport = “<report>…</report>”;
BEGIN DIALOG @dialog_handleBEGIN DIALOG @dialog_handle
FROM SERVICE [//Adventure-Works.com/Expenses/ExpenseClient]FROM SERVICE [//Adventure-Works.com/Expenses/ExpenseClient]
TO SERVICE '//Adventure-Works.com/Expenses'TO SERVICE '//Adventure-Works.com/Expenses'
ON CONTRACT [//Adventure-Works.com/Expenses/ExpenseProcessing] ;ON CONTRACT [//Adventure-Works.com/Expenses/ExpenseProcessing] ;
SEND ON CONVERSATION @dialog_handleSEND ON CONVERSATION @dialog_handle
MESSAGE TYPE [//Adventure-Works.com/Expenses/SubmitExpense]MESSAGE TYPE [//Adventure-Works.com/Expenses/SubmitExpense]
(@ExpenseReport) ;(@ExpenseReport) ;
SET @ExpenseReport = “<report>…</report>”;SET @ExpenseReport = “<report>…</report>”;
BEGIN DIALOG @dialog_handleBEGIN DIALOG @dialog_handle
FROM SERVICE [//Adventure-Works.com/Expenses/ExpenseClient]FROM SERVICE [//Adventure-Works.com/Expenses/ExpenseClient]
TO SERVICE '//Adventure-Works.com/Expenses'TO SERVICE '//Adventure-Works.com/Expenses'
ON CONTRACT [//Adventure-Works.com/Expenses/ExpenseProcessing] ;ON CONTRACT [//Adventure-Works.com/Expenses/ExpenseProcessing] ;
SEND ON CONVERSATION @dialog_handleSEND ON CONVERSATION @dialog_handle
MESSAGE TYPE [//Adventure-Works.com/Expenses/SubmitExpense]MESSAGE TYPE [//Adventure-Works.com/Expenses/SubmitExpense]
(@ExpenseReport) ;(@ExpenseReport) ;
666611/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
Service BrokerService BrokerRecepción de mensajesRecepción de mensajes
Comando RECEIVEComando RECEIVE Especifica la cola y el diálogoEspecifica la cola y el diálogo Es posible quedar a la espera con WAITFOREs posible quedar a la espera con WAITFOR
WAITFOR ( WAITFOR (
RECEIVE * RECEIVE *
FROM ExpenseQueue), FROM ExpenseQueue),
TIMEOUT 60000 TIMEOUT 60000
WAITFOR ( WAITFOR (
RECEIVE * RECEIVE *
FROM ExpenseQueue), FROM ExpenseQueue),
TIMEOUT 60000 TIMEOUT 60000
Colas simplesColas simples
686811/04/2311/04/23 Introducción a SQL Server 2005Introducción a SQL Server 2005
¡Gracias! ¿Preguntas?¡Gracias! ¿Preguntas?
Descargue el código fuente desde:Descargue el código fuente desde: httphttp://://www.solidqualitylearning.comwww.solidqualitylearning.com//conferences.aspxconferences.aspx
Contácteme a:Contácteme a: [email protected] [email protected]