Informatica Jobs at Tx

							             SQL Server 2005


Jordi Rambla
Certia
Microsoft MVP
jordi@ramblainf.com
Yo (Jordi Rambla – jordi@ramblainf.com)
  Director técnico de Certia
  Consultor y profesor de Reporting y BI
  Mentor Asociado de Solid Quality Learning
  Iberoamericana
  MVP SQL Server
  Regional Director de Microsoft circa1997-2004
  Consultor de P&P,XML, ES y WS en Ciclo .NET UOC
Certia (http://certia.ramblainf.com)
  Es la marca de servicios de rambla informàtica
     Consultoría, formación y aplicaciones en BI y .NET
     Trabajando en informática empresarial desde 1985
                 More than just training

Mentores Principales:
  Itzik Ben-Gan, Kalen Delaney, Fernando G. Guerrero,
  Michael Hotek, Brian Moran , Ron Talmage
Iberoamericana:
  Miguel Egea, Alejandro Leguízamo, Jesús López, Pablo
  Pelaez, Jordi Rambla, Eladio Rincón, Daniel Seara,
  Guillermo Som, Antonio Soto, Adolfo Wiernik
Ayudando a obtener lo mejor de SQLServer y .Net con
  Formación
  Consultoría
  Mentoring
Solid Quality Learning University Summits
  ¿Qué son los SQL University Summits?
    Intensos, avanzados, esencialmente útiles
    Temarios aplicados a sistemas empresariales
    Eventos formativos, no de marketing
    2 grupos de sesiones simultáneas
    4 seminarios de día completo
    9 mentores presentando 28 sesiones
    En castellano
  ¿Dónde?
    Madrid (del 25 al 29 de Abril del 2005)
    Barcelona (¿2005?); también en:
       Milán, Washington DC, Los Angeles, Chicago, Dallas, Monterrey, San Salvador,
       Singapore, Hong Kong
Agenda
 ¿Qué hay de nuevo?
 Integración con .NET
 XML y SQL Server 2005
 Service Broker
 Mejoras en T-SQL
 Mejoras en ADO.NET
¿Qué hay de nuevo?
 ¿Qué hay de nuevo?
.NET Framework                              Database Maintenance                             Replication
     Common Language Runtime Integration         Backup and Restore Enhancements                 Auto-tuning Replication Agents
     User-defined Aggregates                     Checksum Integrity Checks                       Oracle Publication
     User-defined Data Types                     Dedicated Administrator Connection              Improved Blob Change Tracking
     User-defined Functions                      Dynamic AWE                               OLAP and Data Mining
     SQL Server .NET Data Provider               Fast Recovery                                   Analysis Management Objects
     Extended Triggers                           Highly-available Upgrade                        Windows Integrated Backup and Restore
Data Types                                        Online Index Operations                         Web Service/XML for Analysis
     FileStream Storage Attribute                Online Restore                                  DTS and DM Integration
     Managed SQL Types                           Parallel DBCC                                   Eight new DM algorithms
     New XML Datatype                            Parallel Index Operations                       Auto Packaging and Deployment
SQL Server Engine                           Management Tools                                 Data Transformation Services
     New Message Service Broker                  MDX Query Editor                                New Architecture (DTR + DTP)
     HTTP Support (Native HTTP)                  MDX Intellisense                                Complex Control Flows
     Database Tuning Advisor                     T-SQL Intellisense                              Control Flow Debugging
     Enhanced Read ahead & scan                  Version Control Support                         For Each Enumerations
     Extended Indexes                            XML/A                                           Property Mappings
     Multiple Active Result Sets                 SQLCMD Command Line Tool                        Full Data Flow Designer
     Persisted Computed Columns            Performance Tuning                                     Full DTS Control Flow Designer
     Queuing Support                             Profiler Enhancements                           Graphical Presentation of Pkg Execution
     Snapshot Isolation Level                    Profiling Analysis Services                     Immediate Mode and Project Mode
     Scale Up Partitioning                       Exportable Showplan                             Package (Advanced) Deployment Tools
     VIA support                                 Exportable Deadlock Traces                      Custom Tasks and Transformations
     NUMA support                          Full-text Search                                 Reporting Services
Database Failure and Redundancy                   Indexing of XML Datatype                        Multiple Output Formats
     Fail-over Clustering (up to 8 node)   MDAC                                                   Parameters (Static, Dynamic, Hierarchical)
     Enhanced Multi-instance Support             Side by Side installation                       Bulk Delivery of Personalized Content
     Database Mirroring                          Microsoft Installer base setup                  Support Multiple Data Sources
     Database Viewpoints                         Support for Active Directory Deployment         STS (Web Parts, Doc Libraries)
XML                                         SQL Client .NET Data Provider                          Visual Design Tool
     XQUERY Support (Server & Mid Tier)          Server Cursor Support                           Charting, Sorting, Filtering, Drill-Through
     XML Data Manipulation Language              Asynch                                          Scheduling, Caching
     FOR XML Enhancements                  Security                                               Complete Scripting Engine
     XML Schema (XSD) Support                    All Permissions Grantable                       Scale Out architecture
     MSXML 6.0 (Native)                          Fine Grain Administration Rights                Open XML Report Definition
     .Net XML Framework                          Separation of Users and Schema            Notification Services
¿Qué hay de nuevo?
Contexto

   Otros                                      Otros
  servicios                                  servicios
                Servicios    Formularios
                  Web
                       Biz      Web
                 Tier Logic
  Browsers
  estándar
                   Lógica aplicación


   Clientes         OS
 inteligentes
                  Services SO
                    Servicios
                                              Servicios
                                           building blocks
 Dispositivos
   móviles
¿Qué hay de nuevo?
Contexto

   Otros                                      Otros
  servicios                                  servicios
                Servicios    Formularios
                  Web
                       Biz      Web
                 Tier Logic
  Browsers
  estándar
                   Lógica aplicación


   Clientes         OS
 inteligentes
                  Services SO
                    Servicios
                                              Servicios
                                           building blocks
 Dispositivos
   móviles
Integración con .NET
Integración con .NET
Alternativa a T-SQL

                                    Lenguajes
                            T-SQL
                                      .NET
           Funciones
         definidas por el
             usuario
         Procedimientos
          almacenados

            Triggers

         Tipos definidos
          por el usuario

           Aggregates
Integración con .NET
Creación
  Registro de assemblies
    Almacenados en la base de datos
    CREATE ASSEMBLY NombreAssembly FROM c:\assembly.dll



  Referencias
    Se almacenan automáticamente
    Sólo dependencias que no estén en la GAC
Integración con .NET
Creación (II)
  Definición de:
     UDFs, procedimientos almacenados, triggers
        Enlazando a función estática y pública
     UDTs, aggregates
        Enlazando a clase

  CREATE FUNCTION NombreFuncion () RETURNS int
         AS EXTERNAL NAME NombreAssembly.[clase]:funcion
Integración con .NET
Seguridad
  CAS deja de tener validez
  Tres nuevos niveles gestionados por SQL
    Safe
    External
    Unsafe
  Validados al dar de alta el assembly
    Ej. Type safe, finalizers, mutable statics…
  Llamadas al BCL restringidas
    Nuevo atributo HostProtectionAttribute
Integración con .NET
Acceso a datos
  Comunicación mediante SqlAccess.dll
    System.Data.Sql
  Proveedor ADO.NET inprocess
    Acceso al contexto de la conexión
      Pipe
      Estado servidor
      Tablas triggers
      Petición
    Cursores de servidor
Procedimiento almacenado en .NET
Documentación disponible

 Using CLR Integration in SQL Server 2005
   Balaji Rathakrishnan
   Christian Kleinerman
   Brad Richards
   Ramachandran Venkatesh
   Vineet Rao
   Microsoft – Noviembre 2004 – 41 páginas
   http://msdn.microsoft.com/SQL/2005/2005Articles/default.aspx?pull=
   /library/en-us/dnsql90/html/sqlclrguidance.asp

 Managed Data Access Inside SQL Server with ADO.NET
 and SQLCLR
   Pablo Castro - Microsoft – Abril 2005 – 26 páginas
   http://msdn.microsoft.com/library/default.asp?url=/library/en-
   us/dnsql90/html/mandataaccess.asp
XML y SQL Server 2005
XML y SQL Server 2005
Novedades
 Tipo de dato XML
   Índices sobre campos XML
 Gestión de esquemas XSD
 Consulta XQuery
 Vistas XML (SQLXML)
 Mejoras en FOR XML y OPENXML
XML y SQL Server 2005
Nuevo tipo XML
 Almacena documentos XML
   Puede utilizarse como cualquier otro tipo
     Columnas, parámetros, retorno de funciones …
   Validación well-formed y XSD
   Se guarda como un BLOB (b1)
 Escenarios
   Información compleja
   Datos semiestructurados
   Gestión documental (ej. e-mail), mensajería (SOAP)
   Intercambio de documentos (B2B, B2C…)
XML y SQL Server 2005
Esquemas
 Los campos XML pueden asociarse con
 esquemas
 CREATE TABLE Invoices(
        id INT PRIMARY KEY,
        factura XML(EsquemaFactura)
        ...


 Esquemas almacenados en la base de datos
 CREATE XML SCHEMA COLLECTION geocoll
        '<xs:schema ...
        targetNamespace=urn:geo>
        ...
        </xs:schema>'
XML y SQL Server 2005
Acceso a campos XML
 XQUERY
   Standard W3C (Last Call Working Draft 4/4/2005)
     Con extensiones para la actualización
   Basado en XPath
     Mucha mayor riqueza para búsquedas complejas


   for $s in /doc[@id = 123]//sec[@num >= 3]
   return <topic>{data($s/heading)}</topic>
XML y SQL Server 2005
XQuery desde T-SQL
 xml.query: devuelve un tipo XML
 xml.exist: devuelve un booleano si hay resultado
 xml.value: devuelve un valor simple (escalar)
 xml.nodes: devuelve una tabla con una columna
 xml.modify: modifica el XML

   SELECT id, xDoc.query(
   'for $s in /doc[@id = 123]//sec[@num >= 3]
   return <topic>{data($s/heading)}</topic>')
   FROM docs
XML y SQL Server 2005
T-SQL desde XQuery
 sql:variable
   Acceso a variables T-SQL desde Xquery
 sql:column
   Acceso a la columna a la que pertenece el XML
    select CV.query(
    'for $elem in /CV/DatosPersonales
       return
       <Nombre>
       { sql:column("Nombre") }
       </Nombre> '
    ) from DatosPersonales
XML y SQL Server 2005
Índices
  Pueden definirse índices en columnas XML
    Aceleran las sentencias XQuery
  Cuatro tipos
    PRIMARY: Indexa los nodos
    PATH: Indexa por path de nodo
    VALUE: Indexa por contenido de nodos
    PROPERTY: Indexa por nodos comunes
XML y SQL Server 2005
Documentación disponible

 XML Options in Microsoft SQL Server 2005
   Microsoft – Enero 2005 – 34 páginas
   http://msdn.microsoft.com/SQL/2005/2005Articles/default.aspx?pull=
   /library/en-us/dnsql90/html/sql2k5xmloptions.asp

 XML Support in Microsoft SQL Server 2005
   Shankar Pal, Mark Fussell, Irwin Dolobowsky
   Microsoft Corporation – Mayo 2004 – 39 páginas
   http://msdn.microsoft.com/SQL/2005/2005Articles/default.aspx?pull=
   /library/en-us/dnsql90/html/sql2k5xml.asp
Service Broker
Service Broker
¿Qué es?
 Sistema de mensajería asíncrona
   Implementado 100% en SQL Server
 Destinatarios
   La misma BBDD
   Otra BBDD en la misma instancia o en otra instancia
   Otro servidor SQL Server remoto
 Incluye
   Nuevos objetos
   Nuevas sentencias T-SQL
 Utilizado internamente en SQL Server 2005
   Query Notifications
   Event Notifications
 Lo pueden usar otras aplicaciones
Service Broker
Y con éste ya van 4…
 MSMQ
   Necesita usar DTC para transacciones con BD
   No garantiza ordenamiento de mensajes
 Biztalk messaging
   Incorpora orquestación
   Automatiza proceso
 Indigo messaging
   Es interoperable
Mensajería asíncrona
(¿Cómo dice?)
 Usamos una cola para:
   Que el proceso deje un mensaje en la cola y
   recupere inmediatamente el control del proceso
   (asincronía)
   El software de gestión de colas se encarga de
   gestionar la entrega de ese mensaje en su destino
     Con todos los matices adicionales (persistencia, garantía,
     secuencia, entrega única, transacción, trazabilidad…)
     Esas son las diferencias con un socket TCP
   El receptor puede procesar los mensajes a su
   conveniencia (con/sin prioridad, p.e.)
Mensajería asíncrona
(¿Dónde está el truco?)
  Es necesario disponer del software cliente de la
  cola y de un servidor
  Es necesario “diseñar/definir” una serie de
  mensajes y de diálogos entre emisores y
  receptores
  Si se utiliza en muchas aplicaciones
    Definimos un “canal” para cada cola (muchas)
    Mezclamos varias comunicaciones en una misma
    cola (control)
  En algunos aspectos es similar a una conexión
  a la BBDD (abrir/cerrar, coste, estado, …)
Service Broker
Mensajes
 Unidad de comunicación
 Tres tipos
   Binarios
   Cabecera
   XML (tipados o sin tipar)

   CREATE MESSAGE TYPE
   [http://www.company.com/messages/Invoice]
     VALIDATION = VALID_XML
       WITH SCHEMA COLLECTION invoice_xsd
Service Broker
Contratos
 Define los mensajes que se pueden
 intercambiar
    CREATE CONTRACT
     [//company.com/Expenses/ExpenseSubmission]
       (
         [//company.com/Expenses/SubmitExpense]
           SENT BY INITIATOR,
         [//company.com/Expenses/ApprovedOrDenied]
           SENT BY TARGET,
         [//company.com/Expenses/ExpenseReimbursed]
           SENT BY TARGET
       )
Service Broker
Colas
 Puntos de entrada a los servicios
   Pueden leerse desde T-SQL
   Pueden llamar a un procedimiento

   CREATE QUEUE ExpenseQueueC
      WITH STATUS = ON
      ACTIVATION (
      PROCEDURE_NAME = expense_activation,
      MAX_QUEUE_READERS = 5,
      EXECUTE_AS USER = 'sa')
Service Broker
Servicios
  Funcionalidad expuesta en las colas
  Compuesto por
    Una cola
    Varios contratos

    CREATE SERVICE [//company.com/Expenses]
      ON ExpenseQueue

      ([//company.com/Expenses/ExpenseSubmission],
       [//company.com/Expenses/ExpenseProcessing])
Service Broker
Envío de mensajes
 1.- Creación del diálogo
   Un diálogo ordena y correlaciona los mensajes
 2.- Envío del mensaje
   Mediante comando SEND
   SET @ExpenseReport = “<report>…</report>”;

   BEGIN DIALOG @dialog_handle
   FROM SERVICE [//Adventure-Works.com/Expenses/ExpenseClient]
   TO SERVICE '//Adventure-Works.com/Expenses'
   ON CONTRACT [//Adventure-Works.com/Expenses/ExpenseProcessing] ;

   SEND ON CONVERSATION @dialog_handle
       MESSAGE TYPE [//Adventure-Works.com/Expenses/SubmitExpense]
       (@ExpenseReport) ;
Service Broker
Recepción de mensajes
 Comando RECEIVE
   Especifica la cola y el diálogo
   Es posible quedar a la espera con WAITFOR


   WAITFOR (
     RECEIVE *
       FROM ExpenseQueue),
     TIMEOUT 60000
Colas simples
Documentación disponible

 An Introduction to SQL Server Service Broker
   Roger Wolter – Julio 2004 – 10 páginas
   Kit de Recursos SQL Server 2005 Beta 2
   Conceptos básicos

 A First Look at SQL Server 2005 Service Broker
   Roger Wolter – Julio 2004 – 20 páginas
   http://msdn.microsoft.com/SQL/2005/2005Articles/default.aspx?pull=
   /library/en-us/dnsql90/html/sqlsvcbroker.asp
   Conceptos y ejemplos de código
Mejoras en T-SQL
Mejoras en T-SQL
Control de excepciones
 Se acabó el acceso a @@ERROR
 Bloques TRY…CATCH
   Anidables
   Nuevas funciones disponibles en el CATCH
      ERROR_NUMBER, ERROR_MESSAGE…

    BEGIN TRY
       SELECT * FROM authors
     END TRY
     BEGIN CATCH
      SELECT ERROR_NUMBER()
     END CATCH
Mejoras en T-SQL
Common Table Expressions
 Resultset temporal
   Sólo vive en la sentencia actual
   WITH mid AS
   (
   SELECT (MAX(ListPrice) + MIN(ListPrice))/2 as midval
   from production.product
   )
   SELECT * FROM Production.Product, mid
   WHERE ListPrice > midval


 Mejor que las subqueries si hay varias iguales
 Admite recursividad
Mejoras en T-SQL
Ranking
 Añade una columna basada en rankings
   ROW_NUMBER
   RANK
   DENSE_RANK
   NTILE (n)
 Pueden agruparse en particiones
   PARTITION BY
Mejoras en T-SQL
Ranking (2)
 SELECT orderid, customerid,
   ROW_NUMBER() OVER(ORDER BY    customerid)     AS    num,
   RANK()       OVER(ORDER BY    customerid)     AS    [rank],
   DENSE_RANK() OVER(ORDER BY    customerid)     AS    [denserank],
   NTILE(5)     OVER(ORDER BY    customerid)     AS    ntile5
 FROM orders



                   orderid       customerid   num      rank     denserank   tile5
                   -----------   ----------   ------   ------   ---------   ------
                   10308         ANATR        1        1        1           1
                   10365         ANTON        2        2        2           1
                   10355         AROUT        3        3        3           2
                   10383         AROUT        4        3        3           2
                   10278         BERGS        5        5        4           3
                   10280         BERGS        6        5        4           3
                   10384         BERGS        7        5        4           4
                   10265         BLONP        8        8        5           4
                   10297         BLONP        9        8        5           5
                   10360         BLONP        10       8        5           5
Mejoras en T-SQL
Otras
 Pivot
   Convierte filas a columnas
 OUTPUT
   Permite especificar una salida a sentencias de
   actualización distinta al número de filas
   Ej. Registros borrados, identities, etc.
 Aislamiento snapshot
   Escritores no bloquean a lectores
Mejoras en T-SQL
Ranking (2)
 DECLARE @DeletedOrders TABLE
 (    orderid INT,
     orderdate DATETIME,
     empid INT,
     custid VARCHAR(5),
     qty INT )
 WHILE 1=1
    BEGIN
       BEGIN TRAN
          DELETE TOP(5000) FROM Orders
          OUTPUT deleted.* INTO @DeletedOrders
          WHERE orderdate < '20030101'
          INSERT INTO OrdersArchive
          SELECT * FROM @DeletedOrders
       COMMIT TRAN
       DELETE FROM @DeletedOrders
       IF @@rowcount < 5000
       BREAK
    END
Mejoras en T-SQL
Otras (2)
  DRI
    SET NULL y SET DEFAULT
        (…)
        CONSTRAINT FK_Orders_Customers
        FOREIGN KEY(customerid)
        REFERENCES Customers(customerid)
        ON DELETE SET NULL
        ON UPDATE SET DEFAULT

  TOP
    Permite expresiones
    También para actualizaciones
T-SQL
Documentación disponible
 SQL Server 2005 Beta 2 Transact-SQL
 Enhancements
   Itzik Ben-Gan – Solid Quality Learning
   Diciembre 2004 – 49 páginas
   http://msdn.microsoft.com/library/en-
   us/dnsql90/html/sql_05TSQLEnhance.asp

  Hands On Lab 115
    Microsoft - 18 páginas
    Kit de recursos Beta 2


  SQL Server 2005 Beta 2 Snapshot Isolation
    Kimberly L. Tripp - SQLSkills.com – Febrero 2005 – 59 páginas
    http://msdn.microsoft.com/SQL/2005/2005Articles/default.aspx?pull=
    /library/en-us/dnsql90/html/sql2k5snapshotisol.asp
Mejoras en ADO.NET
 para SQL Server 2005
Mejoras en ADO.NET
MARS
 Múltiples resultsets y transacciones simultáneas
 por conexión
    SqlCommand cmd = new SqlCommand("select * from
    authors",conn);

    SqlCommand cmd2 = new SqlCommand("select * from
    jobs",conn);
    conn.Open();
    SqlDataReader rdr = cmd.ExecuteReader();

    SqlDataReader rdr2 = cmd2.ExecuteReader();
    rdr2.Read();
    rdr.Read();
Mejoras en ADO.NET
Dependencias SQL
 Notificación cuando cambia un resultset
   Útil para invalidar caché o vistas
 Basado en Service Broker
   SqlNotificationRequest
     Recibe un objeto comando y un servicio
     Envía un mensaje al servicio cuando cambia el resultset
   SqlDependency
     Abstrae el Service Broker
     Evento OnChanged
Notificaciones
Documentación disponible

 Multiple Active Result Sets (MARS) in SQL Server 2005
   Christian Kleinerman - Microsoft – Octubre 2004 – 19 páginas
   http://msdn.microsoft.com/library/default.asp?url=/library/en-
   us/dnsql90/html/MARSinSQL05.asp


 ADO.NET and System.Xml v.2.0--The Beta Version 2ªed
   Alex Homer, David Sussman, Mark Fussell
   Addison Wesley – Marzo 2005 – 560 páginas (cap.muestra – 38 págs.)
   http://www.awprofessional.com/content/images/0321247124/samplec
   hapter/homer_ch05.pdf
   http://www.daveandal.net/books/7124/ (código)
Mejoras en ADO.NET
Transacciones
 System.Transactions
   API manejada para definir transacciones
   Sin EnterpriseServices (COM+)
 Promotable
   Comienza como local (10 ms)
   Sigue local en accesos a SQL Server 2005
   Sólo se promueve a DTC si
     Se accede a SQL Server 2000, MSMQ, Oracle…
     Se acceden a dos SQL Server 2005
Mejoras en ADO.NET
Transacciones (2)
   using (TransactionScope ts = new TransactionScope())
   {
     SqlConnection conn1 = new SqlConnection(
       "server=.;integrated security=sspi;database=pubs"))
     // Local tx si SQL 2005, si no distribuida
     conn1.Open();
     SqlCommand cmd1 = new SqlCommand(
        "INSERT jobs 'job1', 10, 10", conn1);
     cmd1.ExecuteNonQuery();

       SqlConnection conn2 = new SqlConnection(
         "server=other;integrated security=sspi;database=pubs"))
       // promueve a distribuida
       conn2.Open();
       SqlCommand cmd2 = new SqlCommand(
          "INSERT jobs 'job2', 10, 10", conn2);
       cmd2.ExecuteNonQuery();

       ts.Complete(); // commit transaction
   }
Transacciones
Documentación disponible

 ADO.NET and System.Transactions
   John Papa - Microsoft – MSDN Magazine Febrero 2005 – 5 páginas
   http://msdn.microsoft.com/msdnmag/issues/05/02/DataPoints/defaul
   t.aspx
BI Operaciones diversas
    SSIS
    UDM
    SSAS (OLAP)
    SSRS
jordi@ramblainf.com