Monday, January 29, 2007

New Transact SQL Video Tutorial: Variables and Scoping - Local Variables and Batch Terminators

Variables and Scoping - Local Variables and Batch Terminators - view details
The rules for variable scoping (i.e., the "life of the variable") in SQL Server might be a little different from the rules in other languages. To understand variable scoping in SQL, you have to have a good understanding of batches. This video helps you understand both!
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

Keywords Associated with this video: GO

posted by Scott Whigham @ 7:11 AM   0 comments  

New Transact SQL Video Tutorial: Variables - Understanding the Difference Between Using SET and SELECT to Assign Values

Variables - Understanding the Difference Between Using SET and SELECT to Assign Values - view details
This video is designed to help you understand when you should use SET @Variable = value and SELECT @Variable = value. For new people coming into T-SQL programming, it's often quite confusing when to use SET vs. SELECT for variable assignment.
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

Keywords Associated with this video: assign variable from database

posted by Scott Whigham @ 7:11 AM   0 comments  

Monday, January 22, 2007

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

Keywords Associated with this video: node, graphical plan, SET STATISTICS IO ON, SET STATISTICS IO OFF

posted by Scott Whigham @ 8:53 AM   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

Keywords Associated with this video: hide columns, meta data, rad tool, rdd tool, beginner, beginning, for beginners, introduction, step by step

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Design Pattern for Finding Duplicate Data - A Simple Way to Spot Non-Unique Rows (Multi Column Test)

Design Pattern for Finding Duplicate Data - A Simple Way to Spot Non-Unique Rows (Multi Column Test) - view details
We have multiple SQL tutorials for writing queries to return duplicate data in a column or multiple columns. This video focuses on spotting dupes across multiple columns. Although primary keys prevent duplicate rows, having a Primary Key on "EmployeeId" does not prevent users from entering duplicate LastName/FirstName combinations. This video shows you how to use subqueries (both correlated and simple) along with EXISTS to locate non-unique rows.
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

Keywords Associated with this video: duplicate check

posted by Scott Whigham @ 8:53 AM   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

Keywords Associated with this video: sp_help, sproc, procs, , beginner, beginning, for beginners, introduction, step by step

posted by Scott Whigham @ 8:53 AM   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 hav e the TSCC codec installed to view this video

Keywords Associated with this video: , beginner, beginning, for beginners, introduction, step by step

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Pt. 1 - What Return Values Are and How to Use Them

Pt. 1 - What Return Values Are and How to Use Them - view details
This video is an excellent introduction to using Return Values in your SQL Server stored procedures. Return values can speed up performance of your applications as well as helping make a more logical flow. Anyone writing or maintaining stored procedures needs to know this information. NOTE: This video was shot on SQL Server 2005 but the same concepts apply to all versions of SQL Server.

Highlights from this video:
  • Making decisions using return values
  • Returning values to the caller
  • Using RAISERROR and RETURN 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 mu st have the TSCC codec installed to view this video

Keywords Associated with this video: SqlParameter, ParameterDirection.ReturnValue, returning error, return @@ERROR, @RETURN_VALUE

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Pt. 4 - OUTPUT Parameters and How to Use Them

Pt. 4 - OUTPUT Parameters and How to Use Them - view details
One of the more confusing aspects of the stored procedure is the syntax and usage of OUTPUT parameters. This video tutorial will help you understand what OUTPUT params are, where they are used (in a Singleton pattern, for example), and how to access/assign the values to local variables. Don't miss this video!
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

Keywords Associated with this video: stored proc output params, OUT parameters, advanced stored procedures

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: JOINs - An Overview of the Five Types of JOINs (Cross, Outer, and Inner)

JOINs - An Overview of the Five Types of JOINs (Cross, Outer, and Inner) - view details
Understanding the types of JOINs and the differences between each is vitally important to anyone who writes queries/reports for a living. This video does an excellent job of explaining what many people find to be very intimidating: joining tables. This video is really meant to be watched as part of the other videos in this category since it is more theoretical rather than talking syntax. After watching this video, you should be able to think "visually" with your JOINs, understand when to use the INNER or the OUTER join and the purpose of the CROSS join.

Highlights from this video:
  • INNER join logic
  • Discussions of matching vs. non-matching rows
  • When to use an INNER or an OUTER join
  • Using the CROSS JOIN and the architecture behind Cartesian products/cross product queries
  • Should you use the CROSS JOIN often?
  • Which join type is most popular? What does that depend on?
  • LEFT OUTER JOIN, RIGHT OUTER JOIN, and the FULL OUTER JOIN
and much more

Note: This video include a sample SQL script
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

Keywords Associated with this video: cartesian product, set of all possible ordered pairs whose first component is a member of X and whose second component is a memb

posted by Scott Whigham @ 8:53 AM   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

Keywords Associated with this video: best practices for temporary tables

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Part 2 - Using IF Statements to Control Execution in Scripts and Stored Procedures

Part 2 - Using IF Statements to Control Execution in Scripts and Stored Procedures - view details
This video explores more uses of IF statements to control the flow of your SQL code inside stored procedures. You'll learn about using IF EXISTS and more in this video on basic SQL programming.
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

Keywords Associated with this video: if exists

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Aliasing in Queries - What It Is and How to Use It (SQL Server 2000)

Aliasing in Queries - What It Is and How to Use It (SQL Server 2000) - view details
Table aliases, column aliases, column headers, resultset headers, range variables, correlation names - whew! That's a lot of terms to describe a simple concept! In this video, you'll learn the proper use of SQL aliases including when and where you can/cannot use them. Most queries can benefit from using aliases so understanding their use and Best Practices will certainly help you in your quest for SQL greatness.
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

Keywords Associated with this video: query management, alias, beginner, beginning, for beginners, introduction, step by step

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: How to Return Identity Value for a Just-Inserted Row

How to Return Identity Value for a Just-Inserted Row - view details
So many SQL developers use IDENTITY columns (a.k.a. sequences and/or auto-number columns) therefore this video is essential knowledge for SQL developers, DBAs, and even application developers. This tutorial focuses on working with IDENTITY columns as primary keys and how you can retrieve the just-inserted row's primary key value. Several techniques are discussed here with a final discussion on perhaps the "safest" (and recommended) technique.
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

Keywords Associated with this video: identity, @@identity, scope identity, scope_identity

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Variables and Scoping - Local Variables and Batch Terminators

Variables and Scoping - Local Variables and Batch Terminators - view details
Variables and Scoping - Local Variables and Batch Terminators
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

Keywords Associated with this video: Variables and Scoping - Local Variables and Batch Terminators

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Part 1 - Introduction to Using Control-of-Flow Language in Your SQL

Part 1 - Introduction to Using Control-of-Flow Language in Your SQL - view details
IF, WHILE, GOTO, BEGIN and END are all essential parts of the SQL language and this video, part 1 in our series on using conditional statements, focuses on teaching them to you. Many people get confused when working with SQL's IF statement becuase there is no END IF syntax and the rules for execution are not intuitive perhaps. This video explains the proper use of IF along with BEGIN and END to help control code execution.
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

Keywords Associated with this video: loop, while, begin end

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Overview of SET Commands in Transact-SQL - What They Are, Syntax, and How to Use Them

Overview of SET Commands in Transact-SQL - What They Are, Syntax, and How to Use Them - view details
SET commands in T-SQL control the environment in which SQL Server or the client processes that client queries. This view shows you some of the more common SET commands, how they work, scoping (are they scoped to the statement, the batch, the connection?), and how you can use SET commands to increase throughput and help increase your SQL knowledge.
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

Keywords Associated with this video: SET NOCOUNT ON, OFF, QUOTED_IDENTIFIERS, ANSI_NULLS

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Part 3 - GOTO Statements and When and How to Use Them

Part 3 - GOTO Statements and When and How to Use Them - view details
"IF @@Error <> 0 GOTO Errorhandler" Look familiar? Well, it might if you have been working with error handlers and writing GOTO labels and statements. In this third installment in our series on working with Control-of-Flow language, SQL instructor Scott Whigham shows what GOTO is, how to use it and talks about best practices for using GOTO in your 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

Keywords Associated with this video: goto label, control of flow, controlling execution in SQL

posted by Scott Whigham @ 8:53 AM   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

Keywords Associated with this video: smalldatetime, casting date data, converting dates, formatting dates

posted by Scott Whigham @ 8:53 AM   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 sub scription is required.

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

Keywords Associated with this video: case when end, alter table, add constraint

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Pt. 1 - An Overview of the Three Types of User Defined Functions (UDFs)

Pt. 1 - An Overview of the Three Types of User Defined Functions (UDFs) - view details
Scott Whigham (author of this site) is a huge fan of user defined functions (UDFs). This video is an introduction to concepts and syntax - the basics, really. This video is shot using the SQL Server Management Studio (SSMS) on SQL Server 2005 but the concepts are identical in SQL Server 2000.

Highlights from this video:
  • Different types of functions available
  • Syntax differences
  • Differences between scalar functions and table-valued functions
  • An overview of usage
and much more
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

Keywords Associated with this video: getdate, app_name, host_name, niladic, syst em

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Pt. 2 - Working with Scalar-Valued UDFs

Pt. 2 - Working with Scalar-Valued UDFs - view details
Part two focuses on writing your own scalar functions (scalar UDFs). There are some great performance tuning tips in this video!
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

Keywords Associated with this video: performance tuning,. best practices, optimizations, optimizing

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: DATETIME and SMALLDATETIME - Working with Dates and Times SQL Server

DATETIME and SMALLDATETIME - Working with Dates and Times SQL Server - view details
One of the most confusing datatypes to work with in SQL Server, for new people, seems to be the DATETIME/SMALLDATETIME datatypes. Questions abound and, even worse, incorrectly written queries abound using these data types. This video will teach you the fundamentals of working with date and time data in SQL Server so that you can avoid these common query writing pitfalls.

Highlights from this video:
  • Differences between DATETIME and SMALLDATETIME
  • Implicit date rounding and Rounding dates
  • Using DATETIME data in the BETWEEN clause
  • Writing WHERE clauses and <, >, <= and >= with dates
  • Default values for time and dates
  • Should dates use single quotes, double quotes or no quotes (or does it matter)?
and much more
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

Keywords Associated with this video: datetime, smalldatetime, date time, milliseconds, seconds, rounding, year, month, day, truncate, truncating, truncation

posted by Scott Whigham @ 8:53 AM   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

Keywords Associated with this video: RTRIM, LTRIM, basics of udfs

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Pt. 3 - Input Parameters and Variables

Pt. 3 - Input Parameters and Variables - view details
The bulk of your stored procedures will be dynamic, meaning that they will have parameters and those parameters will be used to make decisions (such as what rows to return). This video talks about how to create parameters and variables as well as working with default parameters ( to make a parameter optional instead of required, for example).
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

Keywords Associated with this video: declare, local variable, argument

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Collations, Character Sets, Code Pages, and Sort Orders - How String Data Works in SQL Server 2000

Collations, Character Sets, Code Pages, and Sort Orders - How String Data Works in SQL Server 2000 - view details
Many developers do not get the opportunity to work with international and multi-national data therefore they have a cursory understanding of collations in SQL Server. If you'd like to understand Unicode data and what the difference is between the nchar/char datatypes and the nvarchar/varchar datatypes, then this video is for you. You'll learn about code pages, character sets, collations, and how SQL Server works with case-sensitive data.
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

Keywords Associated with this video: Unicode, nvarchar, nchar, ntext, cp1252, cp1, latin1_general, cp850

posted by Scott Whigham @ 8:53 AM   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 require d.

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

Keywords Associated with this video: set nocount on, set quoted_identifier, objects with spaces in the name

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: When to Use HAVING Instead of WHERE - Aggregates, GROUP BY, and the HAVING Clause

When to Use HAVING Instead of WHERE - Aggregates, GROUP BY, and the HAVING Clause - view details
The traditional areas of difficulty for SQL developers are aggregates, JOINs, and subqueries. Most people have no trouble understanding when to use COUNT instead of SUM but they get "stuck" when it comes to determining how to filter the data. Suppose you are asked to find all orders in 2006 in which customers ordered more than 5 items - how would you solve this query? Obviously you need two filters: for the date (2006) and for orders with more than 5 items. People have difficulty with this type of query however; most people would approach this query along these lines: "SELECT ... FROM Orders JOIN OrderDetails ... WHERE Year(DateOrdered)=2006 AND COUNT(*) > 5". Focus on the WHERE clause for a moment: is that right? If you aren't sure or if you think it is right, then you need to watch this video to understanding why this query must use the HAVING clause.
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

Keywords Associated with this video: having, aggregates where clause, group by

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Performance Tuning Query Tips That Everyone Should Know - Using DBCC and SET Commands

Performance Tuning Query Tips That Everyone Should Know - Using DBCC and SET Commands - view details
There are lots and lots of DBCC commands related to performance tuning but did you know that several SET commands can help you out with your query tuning? This video, recorded by SQL consultant Scott Whigham, teaches you several of the most commonly used DBCC and SET commands for performance tuning and provides some tips and tricks for their use. Note: you'll want to likely have a solid foundation in query writing before viewing this video. An understanding of execution plans would also be helpful.
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

Keywords Associated with this video: dbcc, statistics, i o, statistics_io, statistics_time, showplan, showplan_all, showplan_text

posted by Scott Whigham @ 8:53 AM   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

Keywords Associated with this video: SET ANSI_NULLS ON OFF

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Part 2 - Design Pattern for JOINs - A Simple Way to Look at Joining Tables

Part 2 - Design Pattern for JOINs - A Simple Way to Look at Joining Tables - view details
The second in our two-part series on JOIN patterns, this video shows you the final pattern and walks you through creating your own template that you can use to simplify your use of the design pattern.
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

Keywords Associated with this video: sql join pattern, join tables

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Part 3 - Using EXISTS to Control Execution In an IF Statement

Part 3 - Using EXISTS to Control Execution In an IF Statement - view details
EXISTS is a fundamental part of a SQL programmer's arsenal. This video, part three in our series on using control-of-execution statements, explores using IF EXISTS to drop/create objects and detect data.
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

Keywords Associated with this video: if exists

posted by Scott Whigham @ 8:53 AM   0 comments  

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 TSC C codec installed to view this video

Keywords Associated with this video: rand(), newid()

posted by Scott Whigham @ 8:53 AM   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

Keywords Associated with this video: SET XACT_ABORT ON, while, declare @e int, set @e = @@error, rollback, commit

posted by Scott Whigham @ 8:53 AM   0 comments