Wednesday, December 20, 2006

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 @ 5:47 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 @ 5:47 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 @ 5:47 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 @ 5:47 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 @ 5:47 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 @ 5:47 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 @ 5:47 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". 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: case when end, alter table, add constraint

posted by Scott Whigham @ 5:47 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 @ 5:47 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 @ 5:47 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 @ 5:47 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 @ 5:46 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 @ 5:46 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 @ 5:46 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 @ 5:46 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 @ 5:46 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 @ 5:46 AM   0 comments  

Monday, December 04, 2006

Blogger post failed

Blogger could not process your message at this time.

Error code: 6.1B45A9

Original message:
From: bigtoga.tsqlblogger@blogger.com
Date: Mon, 04 Dec 2006 06:36:22 -0600
Subject: New Transact SQL Video Tutorial: Overview of SET Commands in Transact-SQL - What They Are, Syntax, and How to Use Them
<b style="font-size: 18px; color: black;">Overview of SET Commands in Transact-SQL - What They Are, Syntax, and How to Use Them</b> - <a href="http://www.LearnTransactSQL.com/VideoTutorials/TechnicalTrainingVideo417.aspx">view details</a><br /><blockquote style="border: 1px dotted black; padding: 10px;">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.</blockquote><hr /><ul> <li>Source: <a href="http://www.learnitfirst.com/Course/150/SQL-2005-Programming.aspx">http://www.LearnTransactSQL.com</a></li> <li>Category: <a href="http://www.LearnTransactSQL.com/VideoTutorials/videoCategoryDetail.aspx?videoCategoryId=75">Commonly Used T-SQL Constructs: Datatypes, Type Conversion, Variables, System Functions and Views and more</a></li><li>Skill Level: 200 (Beginner - Intermediate)</li><li>Length: 10:44 (7.27MB)</li><li>Applies to Versions: SQL Server - All Versions</li><li><a href="http://www.LearnTransactSQL.com/VideoTutorials/TechnicalTrainingVideo417.aspx">Full Details</a></li></ul><hr />To download this video, <a href="http://www.LearnTransactSQL.com/Login.aspx">Login</a>, then visit the video's <a href="http://www.LearnTransactSQL.com/VideoTutorials/TechnicalTrainingVideo417.aspx">detail page</a> and click "download". This is a subscriber-only video so a <a href="http://www.LearnTransactSQL.com/SubscriptionOptions.aspx">subscription</a> is required.<br /><br /><p>Note: You must have the <a href="http://www.LearnTransactSQL.com/Faqs/TSCCCodec.aspx">TSCC codec</a> installed to view this video</p><hr />Keywords Associated with this video: SET NOCOUNT ON, OFF, QUOTED_IDENTIFIERS, ANSI_NULLS

posted by Scott Whigham @ 6:40 AM   0 comments  

New Transact SQL Video Tutorial: Using the Create Index Wizard in the Enterprise Manager (SQL Server 2000)

Using the Create Index Wizard in the Enterprise Manager (SQL Server 2000) - view details
The Create Index Wizard in SQL Server 2000's Enterprise Manager is a friendly-enough tool that can help you get started building indexes. This video shows you how it works and helps you get started with creating your first index.
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: nonclustered, clustered, non-clustered, indices

posted by Scott Whigham @ 6:37 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 @ 6:37 AM   0 comments  

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

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

Highlights from this video:

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

To download this video, 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 @ 6:37 AM   0 comments  

New Transact SQL Video Tutorial: The Three Steps that SQL Server Must Go Through to Execute Your Query

The Three Steps that SQL Server Must Go Through to Execute Your Query - view details
The Three Steps that SQL Server Must Go Through to Execute Your Query
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: The Three Steps that SQL Server Must Go Through to Execute Your Query, beginner, beginning, for beginners, introduction, step by

posted by Scott Whigham @ 6:37 AM   0 comments  

New Transact SQL Video Tutorial: Query Analyzer - Beyond the Basics (SQL Server 2000)

Query Analyzer - Beyond the Basics (SQL Server 2000) - view details
For those of you who want to go beyond the basics of hitting F5 to execute your query (CTRL+E works as well), this video walks you through some of Scott Whigham's favorite "tweaks" to the SQL Server 2000 Query Analyzer. Scott Whigham is the author of LearnSqlServer.com, LearnDTS.com and LearnTransactSql.com and has many, many years customizing and optimizing SQL Server including the tools. This video will really give you some good ideas for using the Query Analyzer and will help you speed up your day-to-day tasks.
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 analyzer, object browser, object search

posted by Scott Whigham @ 6:37 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 @ 6:37 AM   0 comments  

New Transact SQL Video Tutorial: Linked Servers - How to Query Microsoft Excel Named Ranges and Worksheets (SQL Server 2000)

Linked Servers - How to Query Microsoft Excel Named Ranges and Worksheets (SQL Server 2000) - view details
For those of you taking full advantage of Microsoft Excel's ability to expose "tables" to SQL Server, this videos helps you understand how you can query even the most advanced Excel spreadsheets and worksheets. No longer are you tied to querying the worksheet element; now you can query certain "tables" *called "named regions") in the worksheet and make your life much easier.
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, jet, worksheet

posted by Scott Whigham @ 6:37 AM   0 comments  

New Transact SQL Video Tutorial: Creating and Altering Views in the Enterprise Manager (SQL Server 2000)

Creating and Altering Views in the Enterprise Manager (SQL Server 2000) - view details
If you are new to working with SQL Server (and views), then this is an excellent introduction to creating and altering views using SQL Server 2000's Enterprise Manager. This video is focused on SQL Server 2000 but, for those of you using SQL Server 2005, the screens are almost identical in the SQL Server Management Studio.
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 view, alter view, drop view

posted by Scott Whigham @ 6:37 AM   0 comments  

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

Pages, Extents, and Blocksizes - What They Are and How They Work - view details
If knowing the architecture of SQL Server is interesting to you, then you'll enjoy this video from Scott Whigham on how things like pages and indexes work in SQL Server. This is more of a theoretical discussion that discusses the sysinternals of SQL Server at a high level. The goal is to give you an understanding of how SQL Server stores data and indexes and how your index and table design decisions can influence SQL Server in multiple ways
To download this video, 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 architecture, sql design, sql internal design, index pages, extents

posted by Scott Whigham @ 6:37 AM   0 comments