1 / 38

Nuevas características del lenguaje T-SQL en SQL Server 2005

Nuevas características del lenguaje T-SQL en SQL Server 2005. Adolfo Wiernik adolfo@wiernik.net. Microsoft Regional Director - http://msdn.microsoft.com/isv/rd Mentor Solid Quality Learning - http://www.solidqualitylearning.com Fundador, Costa Rica User Group .NET - http://www.crug.net

acarin
Download Presentation

Nuevas características del lenguaje T-SQL en SQL Server 2005

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Nuevas características del lenguaje T-SQL en SQL Server 2005

  2. Adolfo Wiernikadolfo@wiernik.net • Microsoft Regional Director - http://msdn.microsoft.com/isv/rd • Mentor Solid Quality Learning - http://www.solidqualitylearning.com • Fundador, Costa Rica User Group .NET - http://www.crug.net • Orador INETA Latinoamérica - http://www.ineta.org/latam • Blog - http://www.wiernik.net Jose Ricardo Ribeiroricardor@microsoft.com • En Microsoft desde 1998 • Desde el 2003 - Regional Program Manager • SQL Server Latinoamérica

  3. Series de Webcasts • Introducción a SQL Server 2005 para desarrolladoresViernes, 22 de Julio de 2005 06:00 p.m.(GMT)http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277969&Culture=es-MX • Nuevas características del lenguaje T-SQL en SQL Server 2005Lunes, 25 de Julio de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277973&Culture=es-MX • Aprovechando XML dentro de la base de datos con SQL Server 2005Viernes, 29 de Julio de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277975&Culture=es-MX • Programando SQL Server 2005 con el CLR – Integración SQL-CLRLunes, 01 de Agosto de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277977&Culture=es-MX • Nuevas características en ADO.NET 2.0Viernes, 05 de Agosto de 2005 06:00 p.m.(GMT)http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277978&Culture=es-MX

  4. Nuevas Características para Desarrollo SQL Server Engine • SQL Service Broker • HTTP Support (Native HTTP) • Multiple Active Result Sets (MARS) • Snapshot Isolation Level Reporting Services • Multiple Output Formats • Parameters (Static, Dynamic, Hierarchical) • Bulk Delivery of Personalized Content • Support Multiple Data Sources • STS (Web Parts, Doc Libraries) • Visual Design Tool • Charting, Sorting, Filtering, Drill-Through • Scheduling, Caching • Complete Scripting Engine • Scale Out architecture • Open XML Report Definition Notification Services SQL Server Mobile Edition MDAC • SNAC • Microsoft Installer base setup ADO.NET 2.0 • Notification Support • Object Model enhancements SQL Client .NET Data Provider • Server Cursor Support • Asynchronous Execution • System.Transactions Security • Separation of Users and Schema • Data encryption primitives Administration • SQL Management Objects (SMO) • Analysis Management Objects (AMO) • Replication Management Objects (RMO) T-SQL • Recursive Queries • Common Table Expressions • PIVOT – UNPIVOT Operators • APPLY Operator • Exception Handling .NET Framework • Common Language Runtime Integration • User-defined Aggregates • User-defined Data Types • User-defined Functions • SQL Server .NET Data Provider • Extended Triggers Data Types • Managed SQL Types • New XML Datatype • Varchar (MAX) Varbinary (MAX) XML • XQUERY Support • XML Data Manipulation Language • FOR XML Enhancements • XML Schema (XSD) Support • MSXML 6.0 (Native) • .Net XML Framework Full-text Search • Indexing of XML Datatype

  5. Agenda • Exception Handling • Common Table Expressions (CTE) • PIVOT • Ranking and Partitioning • Cross-Outer Apply • TOP enhancements • Auto output • Large Objects (LOBs) • Synonyms • DDL Triggers

  6. Handling exceptions • SQL Server 2005 adds exception handling • Error handling in previous SQL Server versions was tedious • @@ERROR set on each statement • set variable with @@ERROR, then check value • BEGIN-END TRY BEGIN-END CATCH blocks in SQL 2005 • semantic equivalent of BEGIN-END blocks • additional functions return error info • can query transaction status • can save @@ERROR

  7. Error handling functions • New error information functions • available inside catch block • ERROR_NUMBER() - number of the error • ERROR_SEVERITY() - severity • ERROR_STATE() - error state number • ERROR_MESSAGE() - complete text of the error message • ERROR_LINE() – line number that caused the error • ERROR_PROCEDURE() – name of the routine that cause the error • New transaction information function • operation that forced logic into catch block may cause un-commitable transaction • XACT_STATE() – state of transaction • 1 = transaction is active and valid • -1 = transaction is uncommittable • 0 = there is no transaction

  8. Exception handling example -- catch errors in a procedure CREATE PROCEDURE someproc AS BEGIN BEGIN TRY SELECT * FROM authors END TRY BEGIN CATCH SELECT ERROR_NUMBER() END CATCH END GO -- catch errors in a batch BEGIN TRY SELECT * FROM authors END TRY BEGIN CATCH -- Test tx state IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION IF (XACT_STATE()) = 1 COMMIT TRANSACTION END CATCH GO

  9. Common Table Expressions • CTE is a temporary named resultset • specified by starting query with a WITH keyword • can be replacement for subquery and used in view • can be used with SELECT/INSERT/UPDATE/DELETE 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 calculates median value compares to median value categorize invoice by relative value

  10. Common Table Expression Syntax • Common table expression starts with WITH clause • expression in parentheses, preceded by name AS • Multiple common table expressions, comma separated • Followed by SELECT statement first cte, named low 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 second cte, named high SELECT statement refers to high and low common table expression syntax

  11. Common table expression execution • CTE is evaluated only once • less scans than subquery if used more than once 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 evaluated once 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) evaluatedtwice SELECT middle third of invoices

  12. leaf value = 14 7 descendants 3 3 depth = 3 1 3 5 1 3 4 leaf 3 5 recursive calculations Recursive calculations • Hierarchy may be of inconsistent depth • chart of accounts and parts list are typical • Calculations require traversal of hierarchy • Many useful recursive calculations possible • aggregates, e.g. sum of leaves is rollup value of account • leaves, e.g. bill of materials for parts list

  13. Recursive common table expression • Common table expression can do recursive calculation • Recursive common table expression has three parts • anchor, followed by UNION ALL; does initialization • recursive member after UNION ALL; recurses until no results • outer select; selects results to be returned WITH descendant(parent, id, amount) AS (SELECT parent, id, amount FROM partsTree WHERE id = @start UNION ALL SELECT P.parent, P.id, P.amount FROM partsTree AS P INNER JOIN descendant AS A ON A.id = P.parent ) SELECT id FROM descendant anchor; executed once recursive member; repeated joined with previous recursion outer select; id's returned

  14. Recursive query example id parent 1 NULL 2 NULL 3 2 4 2 5 3 invoices table recursive common table expression WITH descendant(parent, id, amount) AS (SELECT parent, id, amount FROM partsTree WHERE id = 2 UNION ALL SELECT P.parent, P.id, P.amount FROM partsTree AS P INNER JOIN descendant AS A ON A.id = P.parent ) SELECT id FROM descendant select the cte 2, 3, 4, 5 results

  15. Unknown properties • Sometimes properties of products not known in advance • paint has color, type, and amount; bolt has pitch, diameter • Every property has a name and value • one to many solution; one table for products, one for properties • Individual product tables sometimes needed

  16. Pivot • Pivot turns columns into rows • in effect it synthesizes a table • Widens table by adding columns to it • pivot can rotate many table from one to many solution

  17. Basic pivot • Pivot needs three basic pieces of information • columns that makeup rotated table • column that contains value for rotated table columns • pivot column, i.e. the many in the one to many relation SELECT * FROM properties PIVOT ( MAX(value) FOR name IN ([color], [type], [amount]) ) AS P WHERE id IN (SELECT id FROM products WHERE name='Swish') value column pivot column make column where name = one of these select only properties for the Swish product

  18. Basic pivot results • Pivot selects all rows for a particular product • Columns not mentioned in pivot used to group properties id not mentioned in pivot expression id color type amount -- ------- -------- ------- 1 blue oil 1 gal 3 red latex 1 qt 4 white oil 1 pt properties grouped by id pivoted properties of Swish product

  19. Pivot and Unpivot • The PIVOT keyword makes • rows into columns and aggregates values • generates crosstab reports • UNPIVOT does the opposite • rotates columns to rows (not always symmetric w/PIVOT) -- quantity by quarter CREATE TABLE quarterlysales( product varchar(50), quarter int, quantity int) GO SELECT product, [1] AS 'Q1', [2] AS 'Q2', [3] AS 'Q3', [4] AS 'Q4' FROM quarterlysales PIVOT(SUM(quantity) FOR quarter IN ([1], [2], [3], [4])) AS P

  20. Ranking and Windowing Functions • Adds a column to resultset based on ratings • ROW_NUMBER • RANK • DENSE_RANK • NTILE(n)

  21. Ordering • Column to be rated specified in ORDER BY clause • there must be at least one ordering column • can be more than one SELECT orderid, customerid, ROW_NUMBER() OVER(ORDER BY orderid) AS num FROM orders WHERE orderid < 10400 AND customerid <= 'BN'

  22. Ranking Functions and Ties • Ties work differently in different functions • ROW_NUMBER – always unique • RANK – ties produce spaces and dups in ranking • DENSE_RANK – dups but not ties • NTILE(n) – divided into n approximately equal parts

  23. Duplicates and ties 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 WHERE orderid < 10400 AND customerid <= 'BN' 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

  24. Windowing • You can divide the resultset into subgroups • known as windows • use "PARITITION BY" in the OVER clause • each partition has its own ranking

  25. Windowing SELECT *, RANK() OVER(PARTITION BY COUNTRY ORDER BY age) AS [rank] from ( SELECT lastname, country, DATEDIFF(yy,birthdate,getdate()) as age FROM employees ) AS a lastname country age rank -------------------- --------------- ----------- ------ Dodsworth UK 37 1 Suyama UK 40 2 King UK 43 3 Buchanan UK 48 4 Leverling USA 40 1 Callahan USA 45 2 Fuller USA 51 3 Davolio USA 55 4 Peacock USA 66 5

  26. OVER and other aggregates • OVER can be used with other aggregates • includes user-defined aggregates • usually produces groups of duplicate values

  27. Over with max aggregate -- there is one oldest employee age for each country select *, RANK() OVER(PARTITION BY COUNTRY ORDER BY age) as [rank], MAX(age) OVER(PARTITION BY COUNTRY) as [oldest age in country] from ( select lastname, country, datediff(yy,birthdate,getdate()) as age from employees ) as a lastname country age rank oldest age in country -------------------- --------------- ----------- ------ ---------- Dodsworth UK 37 1 48 Suyama UK 40 2 48 King UK 43 3 48 Buchanan UK 48 4 48 Leverling USA 40 1 66 Callahan USA 45 2 66 Fuller USA 51 3 66 Davolio USA 55 4 66 Peacock USA 66 5 66

  28. Apply operators • APPLY is join • no ON clause allowed • Right part can be any table, but meant for table UDF • params for UDF can come from columns of left part • Cross and outer apply available amount comes from invoice row SELECT * FROM invoice CROSS APPLY greater(amount, 1500) each invoice row joined to table returned by greater function must return table SELECT I1.*, I2.amount FROM invoice I1 JOIN invoice I2 ON I2.amount > 1500 AND I2.id = I1.id equivalent join

  29. Cross apply • Cross apply does inner join • no output for row when UDF produces no output • udf can get its parameters from LHS • useful when udf uses column from LHS CREATE FUNCTION Greater(@v float, @t float) RETURNS TABLE AS RETURN SELECT @v AS v WHERE @v > @t returns either nothing or table with single row returns rows from invoicewhere value > 1500 SELECT * FROM invoice CROSS APPLY Greater(invoice.amount, 1500) @v value frominvoice table cross apply as filter constant passed in for @t

  30. Outer apply • OUTER APPLY does left outer join • all rows from left part returned • may have NULLs for columns returned by UDF CREATE FUNCTION greater(@v float, @t float) RETURNS TABLE AS RETURN SELECT @v AS v WHEN @v > @t returns all rows in invoice SELECT * from invoice OUTER APPLY greater(invoice.amount, 1500)

  31. TOP query • TOP query gets "first N" rows quickly • first page of grid • TOP query can now be based on expression • update TOP N rows aids in batching updating DECLARE @a int, @b int -- set @a and @b -- then, use expression SELECT TOP (@a/@b)* FROM license GO UPDATE TOP (3) license SET status = 'E' WHERE expire_date = getdate() GO

  32. OUTPUT clause on action statements • SQL action statements return "number of rows affected" • sometimes you want to know more • which rows were changed • you could change from a static cursor to get this info • what identity columns were generated • you could get @@ identity • both cases require extra work to return info to caller • OUTPUT clause on actions generate output • returned through TABLE variables • no addition program logic needed • can use logical tables to return before/after images

  33. Returning automatic output -- output rows of "before" values DECLARE @tab TABLE (c1 ....) UPDATE orders SET shipregion = 'RJ' OUTPUT c.*, INSERTED.* into @tab FROM orders o JOIN customers c ON o.customerid=c.customerid GO -- return default value and GUID CREATE TABLE T ( id int, name varchar(20), a TIMESTAMP, b UNIQUEIDENTIFIER) GO INSERT T OUTPUT INSERTED.* into @tab -- table variable VALUES (1, 'bob', DEFAULT, NEWID()) GO

  34. DDL Triggers • Events include all DDL statements • CREATE_TABLE, ALTER_PROCEDURE, DROP_LOGIN, etc. • Scoping at Database and Server levels • DDL_DATABASE_LEVEL_EVENTS • Eventdata() • Returns data regarding type of DDL event of type xml.

  35. Summary • T-SQL enhancements • exception handling added to T-SQL • most enhancements follow SQL-99 standard • CTE, hierarchical CTE • PIVOT, ranking and partitioning (part 8 - OLAP) • some enhance SQL Server specific functions • CROSS, OUTER APPLY • TOP • automatic output • DDL Triggers

  36. Recursos • SQL Server 2005 – Laboratorios Virtualeshttp://msdn.demoservers.com/login.aspx?group=sql2005http://www.microsoft.com/technet/traincert/virtuallab/sql.mspx(only supports SQL 2000) • SQL Server 2005 http://www.microsoft.com/sql/2005 • SQL Server Express http://www.microsoft.com/sql/express • Visual Studio 2005 http://lab.msdn.microsoft.com/vs2005

  37. Series de Webcasts • Introducción a SQL Server 2005 para desarrolladoresViernes, 22 de Julio de 2005 06:00 p.m.(GMT)http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277969&Culture=es-MX • Nuevas características del lenguaje T-SQL en SQL Server 2005Lunes, 25 de Julio de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277973&Culture=es-MX • Aprovechando XML dentro de la base de datos con SQL Server 2005Viernes, 29 de Julio de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277975&Culture=es-MX • Programando SQL Server 2005 con el CLR – Integración SQL-CLRLunes, 01 de Agosto de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277977&Culture=es-MX • Nuevas características en ADO.NET 2.0Viernes, 05 de Agosto de 2005 06:00 p.m.(GMT)http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277978&Culture=es-MX

  38. Gracias adolfo@wiernik.net adolfo@solidqualitylearning.com

More Related