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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, 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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos .

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, Login, 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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, 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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, 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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos .

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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  

New Transact SQL Video Tutorial: Pt. 3 - Best Practices for Using Return Values

Pt. 3 - Best Practices for Using Return Values - view details
The final video in this series on using return values talks about using RETURN to send @@ERROR values back to the caller and whether or not this is a common/good practice.

Highlights from this video:
  • Using RETURN with @@ERROR
  • Making Decisions with SqlParameter and ParameterDirection.ReturnValue
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

Note: You must 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: Part 1 - Design Pattern for JOINs - A Simple Way to Look at Joining Tables

Part 1 - Design Pattern for JOINs - A Simple Way to Look at Joining Tables - view details
Another interesting design pattern for Transact-SQL is the JOIN pattern. The JOIN pattern is a simplistic way of looking at table joins and works with two table joins, three table joins and more. This video, part 1 of 2, features the basics concepts of the pattern and shows you how it works.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: sql server design pattern, design patterns for sql

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Auto-Numbering Rows with IDENTITY Columns - How Identity Columns Work

Auto-Numbering Rows with IDENTITY Columns - How Identity Columns Work - view details
This video is mainly an architecture/sysinternals type of video designed to show you how things work "behind the scenes" with IDENTITY columns. You'll learn how SQL Server chooses the values, how you can influence those values as well as tips and tricks for using IDENTITY columns and Best Practices. IDENTITY columns (and all surrogate keys) are often easy to use but not completely understand by new users. If you use IDENTITY columns in your databases then you'll appreicate this thorough treatment of how IDENTITY columns work in SQL Server.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: IDENTITY, auto number, sequence, surrogate key, alternate key

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Part 4 - WHILE Loops and Implementing Looping Structure in SQL

Part 4 - WHILE Loops and Implementing Looping Structure in SQL - view details
WHILE loops and iteration and part and parcel of any programming language and SQL is no different. This video focuses on how to iterate through resultsets and rows and the semantics of the WHILE loop (along with BEGIN and END).
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: WHILE, recursion, iteration, iterate, recursive queries

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Part 2 - Identity Columns - When Should I Use an Identity Column?

Part 2 - Identity Columns - When Should I Use an Identity Column? - view details
Our second video in the series on using IDENTITY/sequencing columns, this video focuses on when you might consider using a system-generated identifier.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

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

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Pt. 5 - RETURN Codes and How to Use Them

Pt. 5 - RETURN Codes and How to Use Them - view details
The last video in our "Introduction to Stored Procedures" series, this video helps you understand the @RETURN_VALUE parameter when calling/executing stored procedures. The RETURN statement in SQL Server is very important - it allows you to stop execution inside a stored procedure or trigger. Did you know that it also has the ability to return a value to the caller? It sure can but there are certain rules and Best Practices. Check out this video for more!
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: RETURN, RETURN_VALUE, @@ERROR

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Pt. 1 - Introduction to Stored Procedure Concepts and Common Usage

Pt. 1 - Introduction to Stored Procedure Concepts and Common Usage - view details
Stored procedures are the way of the modern programmer and this first video in this series helps you understand the need for and the use of stored procedures in modern applications. You'll learn the basic syntax elements, where to use stored procs, who uses them, and see some rather intricate examples of usage.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: stored procedure tutorial, stored proc help, stored proc script

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Extended Properties in SQL Server 2000 - What They Are and How to Use Them to Generate Your Data Dictionary

Extended Properties in SQL Server 2000 - What They Are and How to Use Them to Generate Your Data Dictionary - view details
Extended Properties are available for tables, columns, stored procedures - actually, they're available for all objects in SQL Server. In many systems, good database design goes hand in hand with good documentation and that means that you'll need a good data dictionary. Extended Properties allow you to add descriptions, traits, and anything else you'd like to your objects. These are query-able, updateable, and very handy in describing difficult column relationships, keys, etc.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: ms_description, description, ex tended properties

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Pt. 3a - A Scalar-Valued UDF for Working with Propert Case and CamelCase

Pt. 3a - A Scalar-Valued UDF for Working with Propert Case and CamelCase - view details
This is an interesting video - for SQL Server 2005 users only�� - that shows you how to convert data into CamelCase and Proper Case. The idea is given by tony Rogerson and the idea for this video was inspired by his blog post on this topic: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/20/832.aspx

Examples of what you'll learn:
  • You are given a row with FirstName and LastName of SCOTT WHIGHAM
    • You will learn to convert this to Scott Whigham and ScottWhigham
    • You will learn how to make this work with an entire s et (a whole table) or one row
    • You will learn how to work with excluded words: Scott von Whigham (where von is not capitalized)
    • You will learn how to work with hyphenated words: Scott Whigham-Smith
This video makes extensive use of the FOR XML PATH clause and the text() node so you may wish to watch our videos on using FOR XML before viewing this video.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: sequence numbers, upper, lower

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Part 1 - Identity Columns - When Should I Use an Identity Column?

Part 1 - Identity Columns - When Should I Use an Identity Column? - view details
IDENTITY columns are some data modeler's Best Friend yet others avoid them like the plague in favor of "Natural Keys" only and/or GUIDs. This video, part one in a series, walks you through the if, ands, and buts of using IDENTITY columns, points out some suggested use of IDENTITY columns and finished with a few Gotchas of using IDENTITY columns.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: identity, update identity, autonumber, auto-number, auto number, seed, increment, beginner, beginning, for beginners, introducti

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Design Patterns and SQL Programming - How You Can Use Patterns to Simplify Your Life

Design Patterns and SQL Programming - How You Can Use Patterns to Simplify Your Life - view details
Think of this video as the introduction to Design Patters and SQL. This video, recored by Scott Whigham, walks you through the idea of using patterns to simplify your SQL programming. Design Patterns are in nearly every modern language yet they are not often seen in SQL. This video shows you the basics of design patterns and how to use some common patterns. It is suggested that you watch this video before continuing on to the other Design Pattern videos.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: design pattern, go4

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Pt. 2 - Using Return Values to Control Program Execution

Pt. 2 - Using Return Values to Control Program Execution - view details
This video expands on the use of return values in SQL Server and shows you how to make decisions in your code based on a stored procedure's return value.

Highlights from this video:
  • What value does RETURN return by default?
  • What are the most likely conventions/uses of RETURN?
  • Using RETURN to return @@ERROR and SQL error codes
  • Best Practices for stored procedure programming
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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. 2 - Using Output Parameters To Pass Data To the Caller

Pt. 2 - Using Output Parameters To Pass Data To the Caller - view details
This continues our discussion of OUTPUT parameters from Part 1. Please ensure that you have viewed Part 1 before watching this video.

Highlights from this video:
  • When to Use Output Parameters
  • Output Parameter or Result Set?
  • Passing Data Between Stored Procedures
  • Master-Detail Inserts with IDENTITY values
  • @@IDENTITY or SCOPE_IDENTITY?
  • OUT or OUTPUT?
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: output, out params, sql parameters, sqlparameter, parameterdirection.output

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Pt. 1 - What Output Parameters Are and When to Use Them

Pt. 1 - What Output Parameters Are and When to Use Them - view details
This video is a great introduction to using OUTPUT parameters. Modern SQL programmers use output parameters in so many stored procedures that it's tough to be a decent SQL developer without a solid understanding of how they work. This video series walks you through what OUTPUT parameters are, how they work, and gives you several business cases for how to use them effectively. Please note that this video needs to be downloaded with Part 2 of this series since the demo/discussion is completed in Part 2.

NOTE: This video series was shot using SQL Server 2005 but the concepts are the same for all versions of SQL Server.

Highlights from this video:
  • When to Use Output Parameters
  • Output Parameter or Result Set?
  • Passing Data Between Stored Procedures
  • Master-Detail Inserts with IDENTITY values
  • @@IDENTITY or SCOPE_IDENTITY?
  • OUT or OUTPUT?
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: output, out params, sql parameters, sqlparameter, parameterdirection.output

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Pt. 2 - CREATE PROCEDURE Syntax and Execution

Pt. 2 - CREATE PROCEDURE Syntax and Execution - view details
This video walks you through the essential syntax elements of the CREATE PROC (or CREATE PROCEDURE if you prefer) statement and also helps you understand how to execute stored procs. At the end of this video, you'll also learn how to modify (ALTER) and remove (DROP) stored procedures.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: create proc, create procedure, alter proc, alter procedure, drop proc, drop procedure

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: SQL Standards - ISO, ANSI, and Transact-SQL's Implementation of the Various Standards

SQL Standards - ISO, ANSI, and Transact-SQL's Implementation of the Various Standards - view details
This video walks you through the various SQL Standards and how Transact SQL implements the various standards. This video should be requisite knowledge for anyone wanting to become a serious SQL Server developer.

Highlights from this video:
  • Why do we have standards?
  • When did the standards first appear?
  • What are the most prevalent standards?
  • Are stored procedures part of the standard?
  • Discussions of SQL-1, SQL-2, SQL-3
  • ANSI and ISO standards such as ANSI-92 and ANSI-99
  • Standards support for each of the versions of SQL Server
and much more
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: SQL1, SQL2, SQL3, ANSI-1989, ANSI-1999, ANSI-1992, ANSI-2003, ISO-2003, ISO 1992, standardized Sql, joe celko

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: JOIN Syntaxes - Should You Use ANSI-89 or ANSI-92 Join Syntax?

JOIN Syntaxes - Should You Use ANSI-89 or ANSI-92 Join Syntax? - view details
The query below is an older style JOIN syntax and is not recommended today. Take a look - have you ever seen a query like this before?


FROM Employees e, Orders o
WHERE e.EmployeeId *= o.EmployeeId

This syntax is not preferred or encouraged in SQL Server and, starting in SQL Server 2005, this has been deprecated. This video helps you understand how to rewrite your queries to use the newer JOIN syntax and gives you excellent examples and reasons why you want to avoid using the older, ANSI-89 style syntax. This video should be required viewing for anyone coming from other systems who use the above syntax!

Highlights from this video:
  • *=, =*, *=*, (+) as JOIN syntax elements
  • The difference between the WHERE clause and the ON clause
  • How to rewrite ANSI-89 JOINs as ANSI-92 JOINs
  • Disadvantages of using the old style join syntax
and much more
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: SQL1, SQL2, SQL3, ANSI-1989, ANSI-1999, ANSI-1992, ANSI-2003, ISO-2003, ISO 1992, standardized Sql

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Pt. 3b - Performance and Our CamelCase Function

Pt. 3b - Performance and Our CamelCase Function - view details
This video talks about performance tuning the UDF created in Pt. 3a (our video on creating a scalar function to do proper/camel casing)

This video makes extensive use of the FOR XML PATH clause and the text() node so you may wish to watch our videos on using FOR XML before viewing this video.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: performance tuning,. best practices, optimizations, optimizing, for xml, xml, text(), xpath

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: How to Choose Whether to Use a Function, a View or a Stored Procedure

How to Choose Whether to Use a Function, a View or a Stored Procedure - view details
For a lot of people just getting started with SQL Server, understanding when to use each of the various objects can be quite a challenge. In this video, resident SQL Server trainer Scott Whigham talks to you about how he uses each of these objects and provides context for using them.

Understand that everyone uses these objects in different ways: some prefer views to functions and procs to everything else. There is no one-size-fits-all approach but, the more you understand the nuances and reasons behind each, the better prepared you'll be to handle any situation thrown at you!
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: p roc, udf

posted by Scott Whigham @ 8:53 AM   0 comments  

New Transact SQL Video Tutorial: Pt. 4 - Writing Inline UDFs and Multi-Statement UDFs

Pt. 4 - Writing Inline UDFs and Multi-Statement UDFs - view details
This video covers the inline (a.k.a. the 'single statement') UDFs as well as the multi-statement UDFs.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: TABLE datatype, @Table

posted by Scott Whigham @ 8:52 AM   0 comments  

Friday, January 12, 2007

New Transact SQL Video Tutorial: Why You Use SCOPE_IDENTITY() Instead of @@IDENTITY

Why You Use SCOPE_IDENTITY() Instead of @@IDENTITY - view details
If your queries and stored procedures use @@IDENTITY (perhaps you have legacy code updated from SQL Server 7.0 or earlier), you'll likely get good value from watching this video on replacing @@IDENTITY calls with the T-SQL function SCOPE_IDENTITY(). This video shows very plainly the negative possiblities of using @@IDENTITY today (triggers are a real problem) and, after you watch this tutorial, you'll never want to use @@IDENTITY again!
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: scope Identity, dbcc check_ident

posted by Scott Whigham @ 10:31 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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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 @ 10:30 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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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 @ 10:30 AM   0 comments  

New Transact SQL Video Tutorial: Using Linked Servers In Your SQL Server SELECT Statement (SQL Server 2000)

Using Linked Servers In Your SQL Server SELECT Statement (SQL Server 2000) - view details
Linked servers can really help you get "out of a jam" with SQL. If you'd like to be able towrite SQL queries against Microsoft Access and JOIN the data to a SQL Server table, for example, then you'll love the flexibility that linked servers offer you. This video is just an overview of how linked servers work and how you can use them in your queries to simplify your life.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: excel, access, oracle, sql server 7, remote server, oledb, jet, ole-db

posted by Scott Whigham @ 10:30 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
Variables - Understanding the Difference Between Using SET and SELECT to Assign Values
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: Variables - Understanding the Difference Between Using SET and SELECT to Assign Values

posted by Scott Whigham @ 10:30 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, Login, 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 @ 10:30 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, Login, 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 @ 10:30 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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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 @ 10:29 AM   0 comments  

New Transact SQL Video Tutorial: JOINs and UNIONs - How Do I Know When to Use a JOIN or a UNION?

JOINs and UNIONs - How Do I Know When to Use a JOIN or a UNION? - view details
So many newbies to Transact-SQL have difficulties determing when to use a UNION or a JOIN to satisy their query. This video teaches you some guidelines to help you quickly and easily identify whether your query will likely need to become a JOIN or UNION. Disclaimer: these are just guidelines and they will not work in every situation but they are applicable for most SQL developers most of the time.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: join union, sql ansi join, sql union all

posted by Scott Whigham @ 10:29 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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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 @ 10:29 AM   0 comments  

New Transact SQL Video Tutorial: Design Patterns and SQL Programming - How You Can Use Patterns to Simplify Your Life

Design Patterns and SQL Programming - How You Can Use Patterns to Simplify Your Life - view details
Think of this video as the introduction to Design Patters and SQL. This video, recored by Scott Whigham, walks you through the idea of using patterns to simplify your SQL programming. Design Patterns are in nearly every modern language yet they are not often seen in SQL. This video shows you the basics of design patterns and how to use some common patterns. It is suggested that you watch this video before continuing on to the other Design Pattern videos.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: design pattern, go4

posted by Scott Whigham @ 10:29 AM   0 comments  

New Transact SQL Video Tutorial: Customizing SQL Server with International Error Messages and How SQL Server 2000 Uses the Default Language

Customizing SQL Server with International Error Messages and How SQL Server 2000 Uses the Default Language - view details
If you work with international or multi-national applications, SQL Server's custom error messages can really help make your error handling and user experience much better by using localized error messages. This video shows you how to create multiple "multiple versions" of the same error message with the only difference being that each message is in a different language. Have you used SQL Server's error messages before? If not, you'll want to watch this video to get an idea of how they work!
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: sp_addmessage, sp_dr opmessage, raiserror, with_log, localization

posted by Scott Whigham @ 10:29 AM   0 comments  

New Transact SQL Video Tutorial: Constraint Keys, Index Keys, Composite Keys, Natural keys, and Surrogate Keys

Constraint Keys, Index Keys, Composite Keys, Natural keys, and Surrogate Keys - view details
This video focuses on common SQL developer/DBA terms regarding indexes and constraints and helps you get comfortable with the SQL lexicon. The work "key" is used over and over in SQL - Primary Key, Foreign Key, Alternate key, and more - yet many developers rarely go beyond the primary key and foreign key. This makes it difficult to develop robust systems. For more on the types of keys available and their usage, watch this video from SQL consultant Scott Whigham.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: alternate key, associative key, pk, fk, check constraint, u nique constraint

posted by Scott Whigham @ 10:29 AM   0 comments  

New Transact SQL Video Tutorial: Auto-Numbering Rows with IDENTITY Columns - How Identity Columns Work

Auto-Numbering Rows with IDENTITY Columns - How Identity Columns Work - view details
This video is mainly an architecture/sysinternals type of video designed to show you how things work "behind the scenes" with IDENTITY columns. You'll learn how SQL Server chooses the values, how you can influence those values as well as tips and tricks for using IDENTITY columns and Best Practices. IDENTITY columns (and all surrogate keys) are often easy to use but not completely understand by new users. If you use IDENTITY columns in your databases then you'll appreicate this thorough treatment of how IDENTITY columns work in SQL Server.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: IDENTITY, auto number, sequence, surrogate key, alternate key

posted by Scott Whigham @ 10:29 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, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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 @ 10:28 AM   0 comments