1 / 20

Subqueries and scripts & batches

Subqueries and scripts & batches. Definition of a subquery N ested subqueries Correlated subqueries T he ISNULL function Derived tables The EXISTS operator M ixing data types: CAST & CONVERT Performance considerations Writing scripts Retrieving identity values @@ ROWCOUNT

early
Download Presentation

Subqueries and scripts & batches

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. Subqueries and scripts & batches • Definition of a subquery • Nested subqueries • Correlated subqueries • The ISNULL function • Derived tables • The EXISTS operator • Mixing data types: CAST & CONVERT • Performance considerations • Writing scripts • Retrieving identity values • @@ROWCOUNT • Batches • Control-of-flow statements • Steen Jensen, autumn 2013

  2. Definition of a subquery • A subquery is a query nested inside another query • Generally used for one of a few needs: • To break a query into a series of logical steps • Tp provide a listing to be the target of a WHERE clause • To provide a lookup driven by each individual record in a parent query • Most subqueries (but not all) can also be written as a join

  3. Nested subqueries A nested subquery is a query, which only goes in one direction – returning either a single value for use in the outer query, or a full list of values to bused with the IN operator See examples page 215 – 218bot

  4. Correlated subqueries A correlated subquery works in two directions In a correlated subquery the inner query runs on information provided by the outer query, and vice versa See examples page 218bot – 223bot

  5. The ISNULL function The ISNULL function accepts a variable or expression and tests it for a NULL value If the value is NULL, the function returns some other specified value If the value is not NULL, the original value is returned See examples page 223bot – 225bot

  6. Derived tables • A derived table is made up of the columns and rows of a result set from a query • To create a derived table, you must do two things: • Enclose the query that generates the result set in parentheses • Alias the results of the query, so the alias can be referenced as a table • See examples page 215bot – 227bot

  7. Parts, which can be skipped Using common table expressions (CTEs): page 228top – 232top

  8. The EXISTS operator When you use EXISTS, you don’t return data but a simple TRUE/FALSE See examples page 232 – 234bot

  9. Mixing data types: CAST & CONVERT Both CAST and CONVERT perform data type conversions You would use CAST and CONVERT in situations, when SQL Server would not implicitly make the conversions for you See examples page 236top – 239top

  10. Performance considerations

  11. Exercise in subqueries Experiment running & changing the different subqueries at SQLZOO: http://sqlzoo.net/wiki/SELECT_.._SELECT

  12. Writing scripts • A script isn’t a script until you store it in a file, where it can be pulled up and reused • Scripts are usually treated as a unit • Scripts can use both system functions and local variables • You can declare one variable (a scalar variable) at a time or several • The are three ways to set the value of a variable: • Initialize it in the DECLARE statement • Use a SELECT statement • Use a SET statement • See examples page 384 – 388bot

  13. Common system functions

  14. Retrieving identity values By using the system function called SCOPE_IDENTITY() the value of the last inserted key can be obtained See examples page 391 – 394mid

  15. Parts, which can be skipped Generating sequences: page 395 – 398bot Running from the command prompt - SQLCMD: page 405top – 409bot Dynamic SQL – using the EXEC command: page 409bot – 415bot

  16. @@ROWCOUNT By using the system function called @@ROWCOUNT the number of affected rows can be obtained See examples page 398bot – 399mid

  17. Grouping statements into batches • A batch is a grouping of SQL statements into one logical unit • If a statement fails at parsetime, nothing runs • If a statement fails at runtime, all statements until the erroneous statement have already been run • To separate a script into multiple batches, you make use of the GO statement • The GO statement: • Must be on a separate line • Causes all statements prior to the GO statement to be sent independently to the server • Is not a SQL statement • See examples page 400mid – 401top + 404 – 405top

  18. When to use batches • Batches are used, when something has to happen either before or separately from everything else in a script • The following commands require their own batch: • CREATE DEFAULT • CREATE PROCEDURE • CREATE RULE • CREATE TRIGGER • CREATE VIEW • Batches can be used to establish precedence

  19. Control-of-flow statements • Control-of-flow statements makes it possible to use programming language constructs in SQL • The control-of-flow statements include: • IF...ELSE • GOTO • WHILE • WAITFOR • TRY/CATCH • See examples page 417-418 + 419bot – 420top + 421bot – 423top + 425 – 426mid + 427mid + 429bot – 430mid

  20. Exercise in script Make ex.1 on page 432 (chapter 11) in the book without looking at the answer Then check your answer up against the indicative answer on page 790

More Related