Advanced Transact-SQL Training Sequence Good Programming Practices Overview – This section is not the only one where tips are mentioned; good programming practices are encouraged and highlighted throughout Documentation – creating a narrative and showing the evolution of scripts System Variables and Local Variables Efficiencies in SELECT, SELECT INTO, INSERT, UPDATE/DELETE Efficiency tips Declaring and loading Variables Sybase and the SQL Standard Overview – Sybase was a pioneer in many of the features we take for granted in RDBMS – triggers, stored procedures, for example. This sample sequence illustrates the create syntax and the stored procedure which provides documentation of the fact  1> use pubs2  1> sp_helpconstraint authors   name            definition                                                     --------------- ----------------------------------------------- phonedflt        create default phonedflt as "UNKNOWN" Use the fipsflagger setting of Sybase to get acquainted with which parts of a Sybase application can be ported to other RDBMS. Here is an isql session illustrating its import:   1> set fipsflagger on   2> select db_name()  go Line number 2 contains Non-ANSI text. The error is caused due to the use of Sybase built-in function: db_name. Other errors will occur when you use Sybase's T-SQL variables.  For example, a batch may cause this error:   Line n contains Non-ANSI text. The error is caused due to the use of Sybase declared global/local variables. The reason why some things can be accomplished in two ways in Sybase  Constraints vs. Rules Triggers vs. Declarative RI Datatypes Not all datatypes support searches and sorts User Defined Datatypes Nullability can be overridden for UDD’s in DDL Nullability Notes DDL definitions of Columns in Tables do not have to specify NULL or NOT NULL; however, the difference in what is assumed by the server can vary markedly; Sybase, in the absence of a specification, assumes NOT NULL, whereas the ANSI standard assumes the other --- keep this in mind if trying to port an application from one RDBMS, such as Oracle, to another, such as Sybase, or vice-versa.  Don’t even make it an issue --- always specify NULL or NOT NULL, which is more self-documenting anyway. Standard SQL datatypes in the modern world Time Series and Spatial (GIS) data are non-standard, but very powerful   Formatting and Reporting Techniques Overview – This short section introduces some important T-SQL functions; others are mentioned in other sections Doing Numeric Sorts on Character Columns Substrings with T-SQL functions patindex() char_length() reverse() and others Counting the number of characters in a column Governing the ‘Rows Processed’ display Getting samples by truncating the result set Using Batches, PRINT and variables   Triggers and Stored Procedures Overview – The performance implications of stored procedures and triggers should be prominent in any serious developers mind.  This is not an introduction to these topics, but a survey of important themes Overview of the Significance of Triggers When Triggers Fire Set Orientation and Triggers A Trigger does not fire on a row-by-row basis Cursors in Triggers The Significance and Location of the Virtual Tables used in Triggers Data being manipulated by DML is always being worked on within data cache.  This is also the location of Exposing variables as parameters to the query optimizer through modular design of stored procedures   Transaction Tips Overview – Failure to understand the difference between chained and unchained mode is a fruitful source of confusion for some, as well as the implications of savepoints and nested transactions Important variables @@TRANCOUNT @@TRANSTATE Chained vs non-chained Relationship of Triggers to Transactions ROLLBACK and ROLLBACK TRIGGER   Debugging and Tracing Overview – In the absence of information from the training prospect about which client software is used, this discussion will be limited to T-SQL based approaches, and the value of incremental design of queries, batches, procedures Determining where you are during execution of a script/procedure Verifying result sets The temptation to rush to complexity vs. incremental approach   Transact-SQL Functions Overview – As mentioned above, functions are discussed in other relevant parts of the course.  Part of this discussion, namely the sign(), charindex() and abs() functions, is to set up their use in Characteristic Functions later Conversion and Implicit Conversion String Functions (Basic) Math Functions   DateTime Issues Oveview – This is a relatively routine section DateTime Datatypes SET DATEFORMAT DateTime Components DateTime Functions DateTime StyleCodes and Convert()   Cursor Issues Overview – While cursors have a significant downside, especially with releases of Sybase prior to 11.5, they are an important tool in the developer’s toolkit.  Some experience with Cursors is assumed in this section Why Cursors are not the preferred way of working in RDBMS One-to-many reporting using a Cursor UPDATE and DELETE WHERE CURRENT OF logic The importance of READ ONLY, and the default behavior of a Cursor Cursors on Temporary Tables within a Trigger Conditional Logic Overview – Prior to the advent of the CASE feature, conditional display, conditional math and decoding were done with a handful of T-SQL functions which together create the ‘Characteristic Function’ feature Conditional Procedure or Batch Execution Conditional Insert vs. Update Logic The Building Blocks of Characteristic Functions Conditional Column contents using Characteristic Functions Decoding Columns using Characteristic Functions Histograms Grouping Data into Derived Categories   Table Folding and Pivoting Overview – Manipulating data to ‘flip’ it 90 degrees, or to provide another view of columns and rows, is available through the methods discussed in this section   Joins Overview – As with other sections, some comfort level is already assumed with Joins.  The variations on standard ‘inner’ Joins are covered in this section, as enumerated below Joins vs. SubQueries Joins and the query optimizer Cartesian Product in isolating errors in Join Logic Inner Join Outer Join Self Join   Subqueries Overview – The main emphasis here is on the Correlated Subquery Non-Correlated Subqueries Correlated Subqueries Looping Techniques Overview – Alternatives to Cursors are covered in this section Set Orientation vs. Row-by-Row handling Conditions when Set Orientation not viable Looping using Temporary Table Emulating Cursors with While Loops   Summary and Statistical Techniques Overview – This section illustrates a number of recipes for fulfilling more advanced reporting needs. Numbering Rows (line numbers in a listing) Finding Missing Sequential Data Rankings Medians Standard Deviation and Variance   Key Generation Overview – In the absence of a natural key such as SSN or License plate number, applications often need to generate unique keys.  This should, of course, be done on the server.  Here’s how to do this. Maximum Key + 1 Separate Key Table on a one-to-one basis, or for several Tables in a Database Filling in gaps in the Numbering Sequence Identity Column Property in DDL Indexing and Sequential keys Pseudo-Random Key Generation