Docstoc

6-T-SQL Demo Script

Document Sample
6-T-SQL Demo Script Powered By Docstoc
					Microsoft Confidential – Microsoft Internal Use Only


SQL Server 2005 Launch Content

Data Platform Track, Session 02
Architecting Scalable, Flexible and
Secure Database Systems with
SQL Server 2005

Level 200
Demo Script 6
Transact-SQL
Microsoft Confidential – Microsoft Internal Use Only

CONTENTS

BEFORE YOU BEGIN ................................................................................................................................. 1
DEMO 1: ROW NUMBERING ..................................................................................................................... 2
  USING ROW NUMBER ..................................................................................................................................................................... 2

DEMO 2: RECURSIVE COMMON TABLE EXPRESSIONS .............................................................................. 4
  SHOW USING T-SQL RECURSIVE CTE ................................................................................................................................................ 4

DEMO 3: ERROR HANDLING ENHANCEMENTS .......................................................................................... 5
DEMO 3: ERROR HANDLING ENHANCEMENTS .......................................................................................... 5
  SHOW BEST PRACTICE ERROR HANDLING IN SQL SERVER 2000 AND UPDATE FOR 2005 ...................................................................................... 5




SQL Server 2005 Launch Event Content                                                i                                                          Data Platform Track Session 02
Microsoft Confidential – Microsoft Internal Use Only

Before you begin
1. Logon as Administrator.
2. The password for the VPC image login is Pass@word1




SQL Server 2005 Launch Event Content                    1   Data Platform Track Session 02
Microsoft Confidential – Microsoft Internal Use Only

Demo 1: Row Numbering
In this demonstration, we will show some of the updates to Transact-SQL. The use with the obvious use case in web programming is
row_number. There are other standard ranking functions in SQL Server 2005 as well.



Using row number
                            Speaker Script                                                                   Steps
SQL Server 2005 enabled tackling one of the most pervasive problems          Perform these steps on LON-SQL-01.
facing web developers, that is, cache coherency. Another problem faced         1. On the desktop, double-click SQL Server Management Studio.
by almost every web developer is being able to display page 40 of a large
                                                                               2. The Connect to Server window appears. Click Connect.
rowset just as fast as page 1 is displayed without holding state on either
and client or database. This is facilitated by SQL Server 2005's               3. The Microsoft SQL Server Management Studio window appears.
implementation of a row_number function.                                          Maximize the window.
                                                                               4. In the File menu, Select Open, then Click Project. Open the project
                                                                                  "c:\scripts\Arch\TSQLEnhancements\
                                                                                  TSQLEnhancements.smssln".
                                                                               5. Make sure the Solution Explorer window is visible on the left-hand
                                                                                  side of the screen. If it is not visible, in the View menu, Click
                                                                                  Solution Explorer.
Let's have a look at how this works. I'll type in a query that returns a       6. In the Solution Explorer window, in the Queries folder of the
large rowset. A typical one might be sales order details for a subset of          project, double-click the file 1-RowNumber.sql to open it. It should
orders. In this case I'll use just the SalesOrderDetail from the                  open in the middle pane.
AdventureWorks database. This is not an outrageously large table, but          7. Select the text from the beginning of the script to the line "-- END
it's a sample database. In a production system, you might envision this           WHOLE TABLE".
many rows coming from a medium-sized customer in a month's worth of
                                                                               8. Select the text and press the Execute button on the menu to
data.
                                                                                  Execute the command. Click on the Messages tab and note that
                                                                                  there are over 121,000 rows in the table.
Note that this table has a primary key, and if all the customer wants to       9. Select the text from the line "-- END WHOLE TABLE" to the line "--
do is search on primary key and the key is a dense-populated identity             END ROWNUMBER
column this can be done very efficiently. But suppose you want to display      10. Select the text and press the Execute button on the menu to
large rowsets ordered by some other field or fields. That's where row              Execute the command. Note the results.
number comes in. This is a standard implementation of row number, that
is, you choose an ordering column and generate a row number column in
the result. Note that I can just as easily choose a different ordering
column as I can with ORDER BY.
Now if I want to display page 50 at 10 rows-per-page, I can write a query      11. Select the text from the line "-- END ROWNUMBER" to the end of
SQL Server 2005 Launch Event Content                                   2                                              Data Platform Track Session 02
Microsoft Confidential – Microsoft Internal Use Only

or stored procedure that uses row_number(). If I look at the query plan,            the script
I can see this is using the SEGMENT physical operator. SEGMENT                   12. Select the text and press the Execute button on the menu to
provides grater optimization potential when getting an arbitrary chunk of            Execute the command. Note the results.
data. This is usually used only with partitioned rowsets, but here you can
                                                                                 13. If time allows, Choose the Query menu and click "Include Actual
see its used with row_number() to limit the number of rows to work on.
                                                                                     Execution Plan". Run the query again.
                                                                                 14. Now click on the Execution Plan tab in the output
                                                                                 15. Scroll to the right until you find the SEGMENT operator.
                                                                                 16. Point out the estimated number of rows in the segment is much
                                                                                     less than the rows in the table.
Here's another T-SQL enhancement...                                              17. DO NOT Close Microsoft SQL Server Management Studio.




SQL Server 2005 Launch Event Content                                         3                                         Data Platform Track Session 02
Microsoft Confidential – Microsoft Internal Use Only

Demo 2: Recursive Common Table Expressions
In this demonstration, we will show how to use common table expressions and recursive queries.



Show using T-SQL recursive CTE
                            Speaker Script                                                                       Steps
Because you easily represent hierarchical or tree data using relational           1. In the Solution Explorer window, in the Queries folder of the
tables, an often requested feature is the ability to support recursion               project, double-click the file 2-CTE.sql to open it. It should open in
through hierarchies. The SQL standard way to do this is by using                     the middle pane.
recursive common table expressions. A common table expression is just a           2. Select the text from the beginning of the script to the line "-- END
named rowset that lasts only until the end of the query it is part of.               SIMPLE CTE".
Here's a simple (non-recursive) CTE.
                                                                                  3. Select the text and press the Execute button on the menu to
                                                                                     Execute the command.
The canonical example of a hierarchy is an org chart. To make this into           4. Select the text from the line "-- END SIMPLE CTE" to the line "--
an org-chart hierarchy, we need to do a SQL self join. We don't join the             END RECURSIVE CTE".
table with itself once, however, as that would only give us employees and         5. Point out the line that begins with "WHERE manager IS NULL" in
their immediate managers. We need to do the employee-to-manager join                 the CTE. That "anchors" the query.
recursively starting at the top of the tree. We do this by dividing a CTE
into two parts, one to get the root of the tree, this is called the anchor
that is, the employee with no manager.
We UNION ALL this with each level of hierarchy in turn by doing a "self-          6. Select the text and press the Execute button on the menu to
join" between CTE and base rowset. The final CTE contains, root,                     Execute the command. Note the levels in the rowset.
branches, and leaves. We use it to see the hierarchy.
We can also ask interesting questions, like how many levels of hierarchy          7. Point at the line that begins with SELECT MAX(level)...
there are. All the recursive CTE is accomplishing is expanding the                8. Select the text from the line "-- END RECURSIVE CTE" to the end
hierarchy into a relational rowset and giving it a name so we can query it.          of the script.
One question we'll ask is "how many levels of hierarchy exist in our              9. Select the text and press the Execute button on the menu to
organization.                                                                        Execute the command. You should receive the answer "5", the
                                                                                     number of levels of hierarchy.




SQL Server 2005 Launch Event Content                                          4                                          Data Platform Track Session 02
Microsoft Confidential – Microsoft Internal Use Only

Demo 3: Error Handling Enhancements
In this demonstration, we will demonstrate how query notifications implement automatic cache invalidation in ASP.NET when a row
is changed by a different user in SQL Server 2005.



Show best practice error handling in SQL Server 2000 and update for 2005
                            Speaker Script                                                                        Steps
One of the things that made T-SQL tedious to write properly was the lack           1. In the Solution Explorer window, in the Queries folder of the
of structured error handling. The feature that may do more to assist T-               project, double-click the file 3-TryCatch.sql to open it. It should
SQL programmers in writing robust, scalable code is a structured error                open in the middle pane.
handling, based on BEGIN TRY-END TRY and BEGIN CATCH-END CATCH                     2. Select the text from the beginning of the script to the line "-- END
blocks.                                                                               OF TABLE DEFINITIONS".
                                                                                   3. Press the Execute button on the toolbar to Execute the command.
                                                                                      You should see the message "Command(s) successfully executed"
                                                                                      in the bottom pane.
Suppose that I want to do a set of operations within a single transaction          4. Select the code in procedure A as you are speaking. You don't
in SQL Server 2000. If any of the operations fail I need to roll back the             need to execute it, although it would not cause an error.
transaction and also return the error number of the error to the caller. If
I want my code to be robust, I need to execute a block of error checking
code after each statement. This is shown in procedure A in the code
sample


Another way to accomplish this is by using the T-SQL (go to) GOTO
statement. This is just a simple example, but with error handling like this,
as the amount of code increases, so does the potential for error.


In previous versions of SQL Server, gathering information about
problems in T-SQL code was more difficult. @@error had to be checked
after every statement and its lifetime was one statement. Additional
error_information could (sometimes) be derived after the fact.
As you can see in procedure B, the NEW try-catch syntax enables you to             5. Select the code in procedure B as you are speaking. You don't
use structured exception handling techniques well known to most                       need to execute it, although it would not cause an error.
programmers. The code is more robust because it is simpler to read and             6. Point out the ERROR_NUMBER() function.
maintain. You get richer error information, like the ERROR_NUMBER()

SQL Server 2005 Launch Event Content                                           5                                          Data Platform Track Session 02
Microsoft Confidential – Microsoft Internal Use Only

function with a lifetime of the entire catch block. This is unlike @@ERROR
which lasts for only one statement.
In addition to the ERROR_NUMBER function, there are 5 additional                  7. Select the code in procedure C as you are speaking. You don't
functions that provide error information and their lifetime is the lifetime          need to execute it, although it would not cause an error.
of the try-catch block. You can automatically return information in T-SQL         8. Point out the ERROR_NUMBER(), ERROR_PROCEDURE(), and
like PROCEDURE name and line number that was obtainable only through                 ERROR_LINE() functions.
client APIs like ADO.NET in previous versions of SQL Server. Now T-SQL
has this ability.


The six functions are:
ERROR_NUMBER()
ERROR_MESSAGE()
ERROR_SEVERITY() - SQL Server severity (1-24)
ERROR_STATE() - ODBC standard state
ERROR_PROCEDURE() - procedure where error took place
ERROR_LINE() - line number in the procedure
There is also a function to test the state of a transaction, for the              9. Point out the XACT_STATE() function.
occasions where your code is in a state where you cannot commit a
transaction. In procedure C, the XACT_STATE function is used to see if
we need to roll back a transaction before writing a row to the error
logging table.


XACT_STATE() = 0 - no transaction in progress
XACT_STATE() = 1 - transaction in progress, commitable
XACT_STATE() = -1 - transaction in progress. must be rolled back


TRY-CATCH is not one of "showiest" features in SQL Server 2005 T-SQL,
but it may contribute more towards making T-SQL code and therefore
your mission-critical applications, more robust than any other feature.




SQL Server 2005 Launch Event Content                                          6                                        Data Platform Track Session 02

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:16
posted:11/4/2010
language:English
pages:8