Monday, August 14, 2006

New Transact SQL Video Tutorial: Subqueries, Nested Queries, and Derived Tables - An Introduction to Advanced SQL Constructs

Subqueries, Nested Queries, and Derived Tables - An Introduction to Advanced SQL Constructs - view details
Subqueries and derived tables can be some of the hardest constructs for people new to SQL to really grasp. This video is a "First Look" at sub-selects (another word for sub-queries) and the basic concepts of using subqueries in your SELECT statements. Perhaps the most valuable thing that even seasoned query-writers will get from watching this video is the "how"; a major focus of this video is how SQL Server processes subqueries internally. Scott Whigham, author of this site, takes you through how SQL Server will process the subquery in a step-by-step approach.

Note: This video was shot on SQL Server 2005 but the concepts are the same for SQL Server 2000.

Highlights from this video:

  • Execution plans: are they different or the same between different subqueries?
  • Using a subquery instead of a JOIN and vice-versa
  • Correlated subqueries and Simple Subqueries
  • Derived Tables; i.e., embedding a SELECT statement in the FROM clause
  • Step by step approach to writing subqueries
  • SQL Server architecture: Query Processing Sequence

To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 8:34 PM   0 comments  

New Transact SQL Video Tutorial: Scoping of SET Commands - Connections, Stored Procedures, Batches and Functions

Scoping of SET Commands - Connections, Stored Procedures, Batches and Functions - view details
This video continues our discussion of SET commands and how they work within/outside-of stored procedures. This video also has an excellent discussion of using brackets and double quotes to delimit objects with spaces and/or reserved keywords in the name. Should You Use Brackets or Double-Quotes to delimit objects? In other words, should you use [Order Details] or "Order Details"?

Note: This video was shot on SQL Server 2005 but the concepts are the same for SQL Server 2000.

Highlights from this video:

  • Using SET NOCOUNT ON inside stored procedures and triggers
  • DBCC USEROPTIONS
  • Discussion of Quoted Identifiers and SET QUOTED_IDENTIFIERS ON/OFF
  • Object delimiters and when to use double quotes instead of single quotes
  • Hierarchy of settable options: database-level, connection-level, stored procedures/triggers, hints
  • Using the SQL Server Management Studio (SSMS) to view and change database-level connection options
  • How to change connection-level options
  • How the Query Analyzer and SSMS use connection-level options

To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 8:34 PM   0 comments  

Friday, August 11, 2006

New Transact SQL Video Tutorial: Two Ways to Generate Random Numbers and Random Rows From a SQL Server Table (SQL Server 2000)

Two Ways to Generate Random Numbers and Random Rows From a SQL Server Table (SQL Server 2000) - view details
Everyone wants to know how to randomize rows and numbers in SQL Server and this video shows you how using different techniques.

Note: This video is for SQL Server 2000 but the same concepts apply to SQL Server 2000

Highlights from this video:

  • Using the TOP operator to return "x" number of rows
  • Retrieving random rows
  • Using RAND() to generate random values
  • Using NEWID() to generate random uniqueidentifiers (GUIDs)
  • Deterministic functions vs. Non-Deterministic functions
  • Optimizing your code for faster execution when retrieving random values

To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:46 PM   0 comments  

New Transact SQL Video Tutorial: Using SET Commands Inside Stored Procedures and When Executing Stored Procedures

Using SET Commands Inside Stored Procedures and When Executing Stored Procedures - view details
This video is "Must-See TV" for those of you wanting to be better stored procedure programmers. The focus of this video is how the scoping of the SET commands in your stored procedures can impact performance and/or results.

This video was shot on SQL Server 2005 but the same concepts still apply for SQL Server 2000

Highlights from this video:

  • Using SET NOCOUNT ON/OFF
  • Using TOP vs. ROWCOUNT
  • Best Practices for using SET commands in stored procedures
  • Optimizing stored procedures for faster execution and easier client use
  • Temporary tables and scoping
  • Creating temporary stored procedures
  • Using DBCC USEROPTIONS to view connection settings
  • sys.objects/sysobjects
  • Error Handling with transactions using XACT_ABORT and @@ERROR

To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:46 PM   0 comments  

New Transact SQL Video Tutorial: SET Commands for Working with NULL values

SET Commands for Working with NULL values - view details
Do you know the difference between ANSI NULLS and ANSI NULL DEFAULT (or is there a difference)? If you aren't sure what an "ANSI" is, then you definitely will want to watch this video! If you have trouble with NULLs or just want a good explanation of what NULLs are and how three-valued logic works in relational databases, you'll also want to watch this video.

Highlights from this video:

  • Explanation of NULLs
  • Using SET commands and database options to control how SQL Server works with NULLs
  • Concatentation and arithmetic with NULL values
  • Changing the default behavior with CONCAT_NULL_YIELDS_NULL
  • ISNULL and COALESCE
  • Using "= NULL" and IS NULL
  • Determining SET options with DBCC USEROPTIONS
  • Defaults of SET options, database options and connection options
  • Variable assigment and whether unassigned variables are null, empty or 0

To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:46 PM   0 comments  

New Transact SQL Video Tutorial: User-Defined Functions (UDFs) - What They Are and How to Use Them (SQL Server 2000)

User-Defined Functions (UDFs) - What They Are and How to Use Them (SQL Server 2000) - view details
User-Defined Functions (UDFs) in SQL Server 2000 are so helpful and are one of Scott Whigham's (author of this site) favorite additions to TSQL. This video walks you through the syntax of the three different types and provides the context for when/where to use each.

Note: This video was shot for SQL Server 2000 but the same concepts apply in SQL Server 2005

Highlights from this video:

  • Scalar Functions
  • Writing a TRIM() function
  • Single-statement (inline) UDFs
  • Multistatement UDFs
  • Syntax differences between the table-valued functions
  • Performance implications of UDFs in the wrong spots

To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:45 PM   0 comments  

New Transact SQL Video Tutorial: Stored Procedure Introduction: Definition, Usage, and Creating Stored Procedures Using the Create Stored Procedure Wizard

Stored Procedure Introduction: Definition, Usage, and Creating Stored Procedures Using the Create Stored Procedure Wizard - view details
Stored procedures are, quite simply, the most popular way that modern database developers allow applications to interface with the database. This video is an excellent introduction to what a stored procedure is, how to use ("execute") stored procedures, and the syntax and tools used in creating/managing stored procedures.

The Enterprise Manager allows you to quickly create generic stored procedures using the Create Stored Procedure Wizard. Is it a good wizard; i.e., should you use it? Maybe - this video gives you the pros and cons of using the Enterprise Manager to manage stored procedures.

Highlights from this video:

  • What is a stored procedure?
  • Example stored procedures
  • Using the Create Stored Procedure Wizard
  • Suggestions for using stored procs
  • Naming Conventions
  • Creating and Editing Stored Procedures with TSQL

Note: This should be considered a "Must-See" video for all newbies!
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:45 PM   0 comments  

New Transact SQL Video Tutorial: RAISERROR and Substitution Parameters - How to Customize Your Error Messages (SQL Server 2000)

RAISERROR and Substitution Parameters - How to Customize Your Error Messages (SQL Server 2000) - view details
This video is part of our series on using custom error messages in SQL Server and shows you how to create dynamic error messages that are stored in sysmessages (sys.messages in SQL Server 2005).

Highlights from this video:

  • sp_addmessage
  • Storing messages in the master database
  • Calling adhoc messages
  • Substition parameters (%s, %d) and dynamic vs. static parameterization
  • Architecture of error messages in SQL Server 2005 and 2000
  • RAISERROR and severity levels
  • SQL Server error message 50000
  • How to pass scalar functions into RAISERROR (i.e. SUSER_SNAME(), USER_NAME(), DB_NAME())
  • Using RAISERROR in stored procedures for custom error handling
  • Using RAISERROR WITH LOG to log SQL Server errors to the Event Log in Windows

To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:45 PM   0 comments  

New Transact SQL Video Tutorial: Using Defaults to Avoid Using NULLs in Your Database (SQL Server 2000)

Using Defaults to Avoid Using NULLs in Your Database (SQL Server 2000) - view details
There seems to be two camps with respect to database design in SQL Server with respect to NULLs in the database: one group believes that NULLs are unnecessary and should not be allowed in the database while the other sort of shrugs their shoulders and says, "I'll use NULLs if it makes sense to use NULLs."; in other words, they don't go out of their way to avoid NULLs.

This video shows you how you can design your tables so that the columns do not have to allow NULLs using a Gender and Address1/Address2 example.

Highlights from this video:

  • Address schemes in the database
  • DDL syntax for DEFAULT constraints
  • DDL syntax for ALTER TABLE
  • How IDENTITY columns work internally in SQL Server
  • How defaults apply to columns that allow NULLs

To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:45 PM   0 comments  

New Transact SQL Video Tutorial: UNION Queries - What They Are and How to Use Them (SQL Server 2000)

UNION Queries - What They Are and How to Use Them (SQL Server 2000) - view details
Although less popular than JOINs, UNIONs are another way to work with multiple tables in the same query. Much of the time UNIONs are used to generate lists of things; i.e., "Find all Customers and Suppliers in the United States". This video gives you the basic syntax and, more importantly, the logic behind how it works and when to use UNION vs. UNION ALL.

Note: This video was shot on SQL Server 2000's Query Analyzer but the same logic applies for SQL Server 2005

Highlights from this video:

  • Difference between UNION and UNION ALL
  • Performance with UNION
  • Including indicators in result sets to identify which table a row comes from
  • Writing UNION compatible queries
  • Datatype mismatches in UNIONs and how to avoid them
  • ORDER BY in UNION queries

To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:45 PM   0 comments  

New Transact SQL Video Tutorial: Views: Introduction, When and Where to Use, and Using the Create View Wizard

Views: Introduction, When and Where to Use, and Using the Create View Wizard - view details
Views are one of the most popular techniques to access data in the database. This video is the perfect introduction to the view which is really nothing more than a stored query. When should you use views? How do you create views? What happens to the data when you remove the view from the database? All of these questions and more are answered in this great video from our author, Scott Whigham.

Highlights from this video:

  • Best Practices for Using Views
  • Designing Manageable and Reusable SQL
  • Using Views to Control Security and Data Access
  • Using the Enterprise Manager to Create/Manage Views
  • CREATE VIEW syntax
  • ANSI-89 vs. ANSI-92 JOIN syntax (SQL-1 vs. SQL-2)
  • Designing Views with the Query Designer
  • CROSS JOINs and How to Run Out of Disk Space Quickly
  • Why You Should Always, Always, Always Test Your Queries Before Posting to Production
  • Internal Acrhitecture of Query Analyzer

Note: This should be considered a "Must-See" video for all newbies!
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:45 PM   0 comments  

New Transact SQL Video Tutorial: The Differences Between Local Temporary Tables and Global Temp Tables and When to Use Each

The Differences Between Local Temporary Tables and Global Temp Tables and When to Use Each - view details
Temporary tables are essential knowledge for all TSQL developers and you should know (a) the differences between the local temp tables (#MyTable) and global temp tables (##MyTable), and (b) when and where to use each. This video gives you the details about each type of temporary table and best practices for using them. Also included is an architecture discussion of how temp tables work and a peek inside the tempdb system database.

Highlights from this video:

  • When to use local temp tables or global temp tables
  • Naming clashes with temp tables
  • Syntax for creating temporary tables
  • Scoping of temp tables
  • Passing temporary tables (result sets) between stored procedures using temporary tables
  • How to populate temporary tables using INSERT and SELECT INTO
  • Dynamic SQL with temporary tables: scoping issues

To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:45 PM   0 comments  

New Transact SQL Video Tutorial: Should I use a Check Constraint or a Foreign Key - Best Practices and Design (SQL Server 2000)

Should I use a Check Constraint or a Foreign Key - Best Practices and Design (SQL Server 2000) - view details
This video uses a few simple examples, working with States/Regions and Genders as the discussion points, to highlight when you should/would use a CHECK constraint or a FOREIGN KEY to solve a simple problem. This video is more of a database design video and is very helpful for anyone working with CHECK constraints with literal values embedded in the filter expression.

Note: This video was shot on SQL Server 2000 but the concepts are the same SQL Server 2005

Highlights from this video:

  • DDL syntax for creating foreign keys and check constraints
  • Logical reasons to use each type of constraint
  • Best Practices for design
  • Constraint naming conventions
  • Normalization topics: How Normalized Should a Database Be for Practical Reporting?

This video was a request from one of our subscribers. Do you have any video requests? We'd love to hear from you - visit our Contact Us page and submit it today!

To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:45 PM   0 comments  

New Transact SQL Video Tutorial: Scalar, System, Built-In and Niladic Functions - What They Are and How to Use Them (SQL Server 2000)

Scalar, System, Built-In and Niladic Functions - What They Are and How to Use Them (SQL Server 2000) - view details
This video focuses on the common scalar functions built into SQL Server 2000 (such as GETDATE(), UPPER(), SUBSTRING(), et al). Scott Whigham, author of this site, walks you through the most commonly used functions and does an excellent job of explaining the different types of functions (such as the cryptic term "Niladic Function").

Highlights from this video:

  • Definitions of the terms "Scalar", "Niladic" "System Functions" and more
  • How scalar functions work in SQL Server and where/when to use them
  • Syntax elements of commonly used functions
  • String Manipulation: UPPER(), LOWER(), SUBSTRING()
  • Trimming strings
  • Commonly used functions in triggers; i.e., Auditing Usage with APP_NAME, DB_NAME, etc
  • Overview of User Defined Functions

To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:45 PM   0 comments  

New Transact SQL Video Tutorial: SET Commands for Performance Tuning and Query Execution Statistics

SET Commands for Performance Tuning and Query Execution Statistics - view details
This is a rather length video (over 21 minutes) due to the in-depth explanations and demos of each of the SET options available for performance tuning and optimizing queries. This video is valid for both SQL Server 2005 and SQL Server 2000 with highlights on the new features available in SQL Server 2005.

Highlights from this video:

  • SQL Server 2005 and SQL Server 2000 SET Commands for optimization and tuning
  • Difference between STATISTICS and SHOWPLAN
  • SET STATISTICS IO and SET STATISTICS TIME
  • The new SET STATISTICS XML
  • SET SHOWPLAN choices (SHOWPLAN_ALL, SHOWPLAN_TEXT)
  • SET STATISTICS PROFILE
  • Detailed explanations of stats output
  • Comparisons between graphical show plan and text showplan

To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:44 PM   0 comments  

New Transact SQL Video Tutorial: SET Commands Often Used to Control Dates and Date Formats in Input or Export Data

SET Commands Often Used to Control Dates and Date Formats in Input or Export Data - view details
Even more in our series on using SET commands in TSQL, this video shows you how to handle different date formats and handling international dates. The more you understand how SQL Server works with dates, the more robust your code. Is '2005/05/01' May 1 or January 5? How about 2005-06-02 - February 6 or June 2? It's up to you!

This video is for SQL Server 2005 and SQL Server 2000.

Highlights from this video:

  • Using DBCC USEROPTIONS to determine connection settings
  • DATEFORMAT and DATEFIRST to control settings
  • DATENAME, DATEPART and CAST/CONVERT to manage datetime data
  • Accurately making decisions based on day of week

To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:44 PM   0 comments  

New Transact SQL Video Tutorial: Rapid Database Development Using Database Diagrams (SQL Server 2000)

Rapid Database Development Using Database Diagrams (SQL Server 2000) - view details
This video tutorial shows you how to use the Enterprise Manager's Database Diagrams to create tables and manage constraints. Instead of having to manually create tables in the Enterprise Manager with the New Table dialog, you can use the Database Diagrams to do proof of concept and rapid database development (similar to rapid application development).

Highlights from this video:

  • Customizing the Database Diagrams to include/exclude columns/metadata
  • Adding Existing Tables to Layout
  • Using the Save Change Script Option
  • Explanations of what the asterisks mean in the Database Diagrams
  • Create Foreign Keys Using the Drag-and-Drop features of the Database Diagrams

To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:44 PM   0 comments  

New Transact SQL Video Tutorial: Pages, Extents, and Blocksizes - What They Are and How They Work

Pages, Extents, and Blocksizes - What They Are and How They Work - view details
If knowing the architecture of SQL Server is interesting to you, then you'll enjoy this video from Scott Whigham on how things like pages and indexes work in SQL Server. This is more of a theoretical discussion that discusses the sysinternals of SQL Server at a high level. The goal is to give you an understanding of how SQL Server stores data and indexes and how your index and table design decisions can influence SQL Server in multiple ways
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:44 PM   0 comments