Wednesday, December 20, 2006

New Microsoft SQL Server 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". 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, set quoted_identifier, objects with spaces in the name

posted by Scott Whigham @ 3:47 AM   0 comments  

New Microsoft SQL Server 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 @ 3:47 AM   0 comments  

New Microsoft SQL Server 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". Fo cus 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". This is a subscriber-only video so a subscription is required.

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

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

posted by Scott Whigham @ 3:47 AM   0 comments  

New Microsoft SQL Server 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 @ 3:47 AM   0 comments  

New Microsoft SQL Server 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 @ 3:47 AM   0 comments  

New Microsoft SQL Server 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 @ 3:47 AM   0 comments  

New Microsoft SQL Server 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 @ 3:47 AM   0 comments  

New Microsoft SQL Server 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 @ 3:47 AM   0 comments  

New Microsoft SQL Server 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 @ 3:46 AM   0 comments  

New Microsoft SQL Server 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 @ 3:46 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Result sets, Recordsets, and the Client Server model

Result sets, Recordsets, and the Client Server model - view details
This video is really helpful for you newbies - those of you who want to get up to speed on all the terms, jargon, parlance, and lexicon of SQL Server. Is a field the same as a column? An alias the same as a header? Is Result Set the same as resultset? The answer to all three questions is "Probably yes, depending on who's asking!" This video helps you dissect several (not all) of the confusing terms and tries to help you speak the language fluidly and easily. Is a recordset the same as a resultset? If you don't know, watch 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: SQL Server resultset, client/server, beginner, beginning, for beginners, intro duction, step by step

posted by Scott Whigham @ 3:46 AM   0 comments  

New Microsoft SQL Server Video Tutorial: EXCEPT and INTERSECT - An Introduction to Syntax and Concepts

EXCEPT and INTERSECT - An Introduction to Syntax and Concepts - view details
New in Transact-SQL for SQL Server 2005 are the EXCEPT and INTERSECT operators. These are not new SQL concepts; rather, these are just being added to SQL Server after being supported in other systems. Chances are that you've written your fair share of EXCEPT and INTERSECT-type queries but using a different syntax. NOTE: this video talks extensively about INNER and OUTER JOINs therefore a solid understanding of both is assumed.
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: outer join, inner join, new feature 2005, ansi, iso, standard

posted by Scott Whigham @ 3:46 AM   0 comments  

New Microsoft SQL Server 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 @ 3:46 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Defaults and Rules in SQL Server 2000 - What They Are, How to Use Them and Best Practices

Defaults and Rules in SQL Server 2000 - What They Are, How to Use Them and Best Practices - view details
What is the difference between a SQL Server Default and a Default constraint (or are they the same thing)? Should you use a Check constraint or a Rule (and are they the same thing)? SQL Server Defaults and Rules have been around forever in SQL Server but, if you don't know what they are, then you should watch 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: sql default, sql rule

posted by Scott Whigham @ 3:46 AM   0 comments  

New Microsoft SQL Server 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_dropmessage, raiserror, with_log, l ocalization

posted by Scott Whigham @ 3:46 AM   0 comments  

New Microsoft SQL Server 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, unique constraint

posted by Scott Whigham @ 3:46 AM   0 comments  

New Microsoft SQL Server 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, sequenc e, surrogate key, alternate key

posted by Scott Whigham @ 3:46 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Using the UNPIVOT Operator to Normalize Denormalized Tables and Datasets

Using the UNPIVOT Operator to Normalize Denormalized Tables and Datasets - view details
The opposite of the PIVOT operator is UNPIVOT. Since PIVOT creates cross tab queries (or "pivots" the data across the vertical and horizontal axis by turning row values into columns), UNPIVOT can take a crosstab query's resultset and normalize the results by turning the column data into rows. Make sense? If not, you need to watch 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: crosstab, cross tab, cross-tab, new feature, select, report, normalize, normalizing

posted by Scott Whigham @ 3:46 AM   0 comments  

New Microsoft SQL Server Video Tutorial: TOP Operator: What's New and Why Life Is much Easier Today

TOP Operator: What's New and Why Life Is much Easier Today - view details
The TOP operator has been greatly enhanced over SQL Server 2000's implementation of TOP by adding support for parameterization and the ability to use TOP in all DML statements (instead of just the SELECT). Scott Whigham, author of LearnSqlServer.com and LearnTransactSql.com, walks you through using the new features of TOP and how it differs from previous versions.
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: new feature, top parameter, insert, update, delete, select

posted by Scott Whigham @ 3:46 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Using the PIVOT Operator - An introduction to PIVOT Tables and Crosstab Queries with PIVOT

Using the PIVOT Operator - An introduction to PIVOT Tables and Crosstab Queries with PIVOT - view details
SQL Server 20005 introduces the PIVOT operator to Transact-SQL for creating crosstab queries in a much simpler way. If you're used to working with cross tab queries and pivot tables and are tired of having to hard code the options in your CASE operator(s), then check out this excellent video on the PIVOT operator and how to use it.
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: crosstab, cross tab, cross-tab, new feature, select, report, denormalize, denormalizing

posted by Scott Whigham @ 3:46 AM   0 comments  

New Microsoft SQL Server Video Tutorial: COLLATE and How Queries and Table Data Work with Case-Sensitive and Accent-Sensitive Data

COLLATE and How Queries and Table Data Work with Case-Sensitive and Accent-Sensitive Data - view details
If you have to work with multi-national and international data, then you'll often find yourself working with Unicode Datatypes (such as nchar and nvarchar) and even needing to use particular collations. In fact, some of you may need to work with (gasp) a case-sensitive server, database, table or column. If you are wanting to know more information about how case sensitive data, regional data, global data and basically get a handle on how string data works in SQL Server, this is a good video for helping you understand how SQL Server 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: unicode, nchar, nvarchar, collate, collat ion, globalization

posted by Scott Whigham @ 3:46 AM   0 comments  

New Microsoft SQL Server Video Tutorial: An Overview of the Northwind Database

An Overview of the Northwind Database - view details
Scott Whigham walks you through the layout of the Northwind database in this video. Northwind is used for many demos and videos throughout the site due to how easy it is to work with. This is not a long video but will serve to familiarize you with the sample database used in many of the T-SQL-based videos.

NOTE: You can download the Northwind (and Pubs) sample databases from the microsoft.com website here
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: sample database, sample db, install sample, oltp database, beginner, beginning, for beginners, introduction, st ep by step

posted by Scott Whigham @ 3:46 AM   0 comments  

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

Scalar, System, Built-In and Niladic Functions - What They Are and How to Use Them - view details
There are far too many SQL functions to list here but what is important is what they are, what the difference is between them, and how you could/should use them. What's the difference between Niladic and Deterministic? Is GETDATE() Deterministic or Non-Deterministic? Is RAND() guaranteed to return a random number? Is NEWID() allowed as a parameter to other functions? If you haven't heard terms like Niladic or Deterministic before then you'll really appreciate this video. Also, SQL guru Scott Whigham gives an excellent breakdown of some common functions
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: udfs, udf, deterministic

posted by Scott Whigham @ 3:46 AM   0 comments  

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

Collations, Character Sets, Code Pages, and Sort Orders - How String Data Works in SQL Server - view details
Do you know what a collation is in SQL Server? What's the difference between CI and CS collations? How about CP1 versus CP1252 - what's the difference (nothing)? If these questions baffle you, then you might enjoy watching Scott Whigham explain how string (character) data works in SQL Server. If you are trying to understand how SQL Server works with case-sensitive data (or case-insensitive data), then you'll get a lot out of this video. Learn the difference between SQL Collations, Windows Locale, Code Pages, and whether or not a lower-case "a" sorts before or after an upper-case "A"
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: collate, collation, sql collation

posted by Scott Whigham @ 3:45 AM   0 comments  

Monday, December 04, 2006

Blogger post failed

Blogger could not process your message at this time.

Error code: 6.19A94E3

Original message:
From: bigtoga.sqlnewvids@blogger.com
Date: Mon, 04 Dec 2006 06:36:48 -0600
Subject: New Microsoft SQL Server Video Tutorial: Pt. 2 - Using Output Parameters To Pass Data To the Caller
<b style="font-size: 18px; color: black;">Pt. 2 - Using Output Parameters To Pass Data To the Caller</b> - <a href="http://www.learnsqlserver.com/VideoTutorials/TechnicalTrainingVideo514.aspx">view details</a><br /><blockquote style="border: 1px dotted black; padding: 10px;">This continues our discussion of OUTPUT parameters from Part 1. Please ensure that you have viewed Part 1 before watching this video. <br /><br />Highlights from this video:<ul> <li>When to Use Output Parameters</li> <li>Output Parameter or Result Set?</li> <li>Passing Data Between Stored Procedures</li> <li>Master-Detail Inserts with IDENTITY values</li> <li>@@IDENTITY or SCOPE_IDENTITY?</li> <li>OUT or OUTPUT?</li> </ul></blockquote><hr /><ul> <li>Source: <a href="http://www.learnitfirst.com/Database-Professionals.aspx">http://www.learnsqlserver.com</a></li> <li>Category: <a href="http://www.learnsqlserver.com/VideoTutorials/videoCategoryDetail.aspx?videoCategoryId=96">Transact-SQL Basics: Queries, Stored Procedures, Functions and more</a></li><li>Skill Level: 300 (Intermediate)</li><li>Length: 09:11 (8.66MB)</li><li>Applies to Versions: SQL Server - All Versions</li><li><a href="http://www.learnsqlserver.com/VideoTutorials/TechnicalTrainingVideo514.aspx">Full Details</a></li></ul><hr />To download this video, <a href="http://www.learnsqlserver.com/Login.aspx">Login</a>, then visit the video's <a href="http://www.learnsqlserver.com/VideoTutorials/TechnicalTrainingVideo514.aspx">detail page</a> and click "download". This is a subscriber-only video so a <a href="http://www.learnsqlserver.com/SubscriptionOptions.aspx">subscription</a> is required.<br /><br /><p>Note: You must have the <a href="http://www.learnsqlserver.com/Faqs/TSCCCodec.aspx">TSCC codec</a> installed to view this video</p><hr />Keywords Associated with this video: output, out params, sql parameters, sqlparameter, parameterdirection.output

posted by Scott Whigham @ 4:43 AM   0 comments  

New Microsoft SQL Server 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 @ 4:40 AM   0 comments  

New Microsoft SQL Server 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 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 @ 4:37 AM   0 comments  

New Microsoft SQL Server 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 must 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 @ 4:37 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 1 - An Introduction to Using Views in SQL Server

Pt. 1 - An Introduction to Using Views in SQL Server - view details
Who uses views? We all do! Every developer, DBA, business analyst, systems analyst, manager, CEO, etc uses views to access the data in the database. Views make life so much easier - they are, for many people, the first choice to simplify query-writing and/or create reusable queries.

This video, recorded by resident expert Scott Whigham, is a great introduction to using views and helps with:
  • When should I use a view?
  • Should I use a view or a stored procedure?
  • What is the basic syntax for creating views in 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: sql view, stored query, stored queries, views, beginner, beginning, for beginners, introduction, step by step

posted by Scott Whigham @ 4:37 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 3 - Create View Syntax and Creating Views in SQL Server

Pt. 3 - Create View Syntax and Creating Views in SQL Server - view details
Pt. 3 - Create View Syntax and Creating Views 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: create sql view, create view syntax, alter view, drop view, views, beginner, beginning, for beginners, introduction, step by ste

posted by Scott Whigham @ 4:37 AM   0 comments  

New Microsoft SQL Server 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 @ 4:37 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 1- Introduction to SQL Server 2005's Integration Services

Pt. 1- Introduction to SQL Server 2005's Integration Services - view details
If you've been confused, mystified, or downright ornery about SQL Server 2005's Integration Services, you're not alone. Everything has changed so drastically from the "old" DTS days that much of our previous knowledge is passé. This video is a jumping-off point, a first look, at SSIS and some of the basic architectural changes including using the Business Intelligence Design Studio (often referred to as the BI Studio) and how the .dtsx files work within a project/solution. This video doesn't feature any package creation nor talk about the individual tasks; it's really more about how things have changed, what the new tools are, and is really designed to help you get comfortable with SSIS.
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: SSIS intro, integration services first look, ssis basics, integration services architecture

posted by Scott Whigham @ 4:37 AM   0 comments  

New Microsoft SQL Server 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 @ 4:37 AM   0 comments  

New Microsoft SQL Server 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 @ 4:37 AM   0 comments  

New Microsoft SQL Server Video Tutorial: An Overview of the Fixed Server Roles for SQL Server 2000

An Overview of the Fixed Server Roles for SQL Server 2000 - view details
Scott Whigham walks you through an overview of the fixed server roles in SQL Server with this excellent video. Quick - what is the one server role that you should probably never use? Which server role allows its members to add other members to it? Can you add your own server roles? Can you modify the server role permissions? If you don't know the answers to all of these questions, then you should probably watch 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: server roles sql server, sql server server role, sql server role, sql role

posted by Scott Whigham @ 4:29 AM   0 comments  

Blogger post failed

Blogger could not process your message at this time.

Error code: 6.14DEE66

Original message:
From: bigtoga.sqlnewvids@blogger.com
Date: Mon, 04 Dec 2006 05:33:33 -0600
Subject: New Microsoft SQL Server Video Tutorial: Pt. 1 - Setting Up the Distributor and Publisher for Transactional Replication
<b style="font-size: 18px; color: black;">Pt. 1 - Setting Up the Distributor and Publisher for Transactional Replication</b> - <a href="http://www.learnsqlserver.com/VideoTutorials/TechnicalTrainingVideo528.aspx">view details</a><br /><blockquote style="border: 1px dotted black; padding: 10px;">This video is a continuation from our introductory series on SQL Server replication and walks you through setting up the distributor and the publisher for transactional replication. You'll walk through setting up replication using the &quot;Configure Distribution Wizard&quot; to configure each option. Included is a detailed explanation of each of the screens in the wizard and recommendations for best practices. <br /><br />Highlights from this video:<ul> <li>Replication architecture</li> <li>What is the &quot;distribution&quot; database?</li> <li>Suggestions for configuring the SQL Server agent for replication</li> <li>What is the snapshot? Where is it stored? How often should it be generated?</li> <li>Scripting the wizard's output for easier replay/debugging/maintenance</li> <li>Using the Replication Monitor</li> <li>Using the Distributor Properties and Publisher Properties</li> </ul> and much more</blockquote><hr /><ul> <li>Source: <a href="http://www.learnitfirst.com/Database-Professionals.aspx">http://www.learnsqlserver.com</a></li> <li>Category: <a href="http://www.learnsqlserver.com/VideoTutorials/videoCategoryDetail.aspx?videoCategoryId=113">Replication in SQL Server</a></li><li>Skill Level: 200 (Beginner - Intermediate)</li><li>Length: 13:12 (8.74MB)</li><li>Applies to Versions: SQL Server 2005</li><li><a href="http://www.learnsqlserver.com/VideoTutorials/TechnicalTrainingVideo528.aspx">Full Details</a></li></ul><hr />To download this video, <a href="http://www.learnsqlserver.com/Login.aspx">Login</a>, then visit the video's <a href="http://www.learnsqlserver.com/VideoTutorials/TechnicalTrainingVideo528.aspx">detail page</a> and click "download". This is a subscriber-only video so a <a href="http://www.learnsqlserver.com/SubscriptionOptions.aspx">subscription</a> is required.<br /><br /><p>Note: You must have the <a href="http://www.learnsqlserver.com/Faqs/TSCCCodec.aspx">TSCC codec</a> installed to view this video</p><hr />Keywords Associated with this video: replication architecture, transactional, beginning, beginner, intro, introduction, snapshot folder, snapshot

posted by Scott Whigham @ 3:37 AM   0 comments  

Blogger post failed

Blogger could not process your message at this time.

Error code: 6.FB45DC

Original message:
From: bigtoga.sqlnewvids@blogger.com
Date: Mon, 04 Dec 2006 05:33:27 -0600
Subject: New Microsoft SQL Server Video Tutorial: Pt. 1 - Using SQL Profiler - Concepts, Terms and Screens
<b style="font-size: 18px; color: black;">Pt. 1 - Using SQL Profiler - Concepts, Terms and Screens</b> - <a href="http://www.learnsqlserver.com/VideoTutorials/TechnicalTrainingVideo546.aspx">view details</a><br /><blockquote style="border: 1px dotted black; padding: 10px;">SQL Server Profiler is just an amazing tool and one that every sysadmin should know &quot;backwards and forwards.&quot; This video, the first in our series, covers what Profiler is, what traces are, and gives you an introduction to using Profiler. You'll also cover the basic screens when you start Profiler. <br /><br />Highlights from this video:<ul> <li>Differences between SQL Server 2000 Profiler and SQL Server 2005 Profiler</li> <li>Permissions required for SQL Server 2005 Profiler</li> <li>Making decisions on the &quot;General&quot; screen</li> <li>Should you save to file or save to table?</li> <li>Using templates with Profiler</li> <li>What sort of performance impact will Profiler cause? How much?</li> <li>How to minimize the &quot;drag&quot; that Profiler creates</li> <li>Server side vs. client side traces</li> <li>What are rollover files (file rollover)? What should the setting be?</li> </ul> and much more</blockquote><hr /><ul> <li>Source: <a href="http://www.learnitfirst.com/Database-Professionals.aspx">http://www.learnsqlserver.com</a></li> <li>Category: <a href="http://www.learnsqlserver.com/VideoTutorials/videoCategoryDetail.aspx?videoCategoryId=98">Performance Tuning SQL Server</a></li><li>Skill Level: 300 (Intermediate)</li><li>Length: 11:45 (0.00MB)</li><li>Applies to Versions: SQL Server 2005</li><li><a href="http://www.learnsqlserver.com/VideoTutorials/TechnicalTrainingVideo546.aspx">Full Details</a></li></ul><hr />To download this video, <a href="http://www.learnsqlserver.com/Login.aspx">Login</a>, then visit the video's <a href="http://www.learnsqlserver.com/VideoTutorials/TechnicalTrainingVideo546.aspx">detail page</a> and click "download". This is a subscriber-only video so a <a href="http://www.learnsqlserver.com/SubscriptionOptions.aspx">subscription</a> is required.<br /><br /><p>Note: You must have the <a href="http://www.learnsqlserver.com/Faqs/TSCCCodec.aspx">TSCC codec</a> installed to view this video</p><hr />Keywords Associated with this video: sql trace, sql profiler, sql server 2005 profiler, profiling, sql sniffer, sniffing sql, tracing sql

posted by Scott Whigham @ 3:37 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 4 - Managing, Viewing and Interpreting SQL Server Traces

Pt. 4 - Managing, Viewing and Interpreting SQL Server Traces - view details
This is a huge video - one that you should definitely watch if you'll be working with Profiler often. In this video our resident guru, Scott Whigham, walks you through all the various techniques for saving and exporting trace data: saving trace data as SQL files (for performance tuning and index tuning), exporting as a template, saving your traces as a SQL Server table and more. One of the best things in this video is seeing how, once you export/save your trace to a SQL table, you can write queries against the data. You could write queries that would tell you how many times a particular table appears in the trace, average/min/max execution times, and more.

Highlights from this video:
  • Importing and Exporting trace data and trace files
  • Working with TextData column in your trace table
  • Interpreting Reads/CPU/Duration and other information
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: profile, profiler table, write queries against profiler data, save trace to sql file

posted by Scott Whigham @ 3:37 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 2 - Setting Up Publications for Transactional Replication

Pt. 2 - Setting Up Publications for Transactional Replication - view details
This video, part 2 in our series, walks you through creating publications using the "New Publication Wizard" in SQL Server Management Studio (SSMS). You'll learn about publishing tables and doing vertical as well as horizontal filters.

Highlights from this video:
  • What requirements are there for publishing tables?
  • How to publish an entire table and how to publish only certain columns
  • How to publish all columns but only rows that meet a WHERE clause
  • Should you change the destination object owner?
  • Syntax for writing filters
  • Determining snapshot creation and snapshot agent location/properties
  • How frequently should the snapshot agent run?
  • Configuring Agent Security for the snapshot agent and the log reader agent
  • Should you run the agent accounts as the SQL Server Agent service account?
  • Scripting your publication for easier maintenance/reinitialization
  • After running the wizard, how to modify and view the publication's properties
  • How to generate new snapshots
  • What does the snapshot look like? How is it implemented?
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: replication architecture, transactional, beginning, beginner, intro, introduction, snapshot folder, snapshot, filters, horizont

posted by Scott Whigham @ 3:34 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 5 - Templates and Working Within the Profiler UI

Pt. 5 - Templates and Working Within the Profiler UI - view details
This is a quick video that teaches you how to work with templates in Profiler so that you can quickly start tracing and get helpful data.

Highlights from this video:
  • Default templates
  • How to Backup templates
  • Importing and Exporting templates
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: trace template, start automatically, profile startup

posted by Scott Whigham @ 3:34 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 3 - Setting Up Push and Pull Subscriptions

Pt. 3 - Setting Up Push and Pull Subscriptions - view details
This video walks you through setting up a push subscriptions and pull subscriptions as well as synchronizing servers. You'll be able to view latency and how to diagnose replication problems.

Highlights from this video:
  • Push Subscriptions vs. Pull Subscriptions
  • Synchronization Schedules and Latency
  • How to initialize a new publication using the "New Publication Wizard"
  • Discussion of the Distribution Agent and how it works (does it run at subscriber?)
  • Discussion of using non-SQL Server subscribers (Oracle subscribers, DB2 subscribers for example)
  • Discussion of service account usage and recommendations
  • Snapshot initialization and how to start the Snapshot Agent
  • Working with multiple subscriptions for a single publication
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: replication architecture, merge, transactional, snapshot, dr, beginning, beginner, intro, introduction, publication, article, pu

posted by Scott Whigham @ 3:34 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 3 - Performance Tuning Profiler and Traces By Adding Filters and Working with Large Traces

Pt. 3 - Performance Tuning Profiler and Traces By Adding Filters and Working with Large Traces - view details
The filters you choose (or don't choose) can definitely give you improved throughput for your traces. This video walks you through how to set up the filters and designing traces for performance. At the end of this video, a great demo of how organize your traces for easier readability and troubleshooting.

Highlights from this video:
  • What filters should I use when I want to filter for long running/slow queries?
  • What are Reads and Duration and why are they important?
  • How can I only trace my application (not SQL Server Agent, for example)
  • Tracing for performance tuning
  • What Event Classes use what Filters
  • Suggestions for using filters to create more manageable traces
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: sql trace, sql profiler, sql server 2000 profiler, profiling, sql sniffer, sniffing sql, tracing sql, grouping, filtering, limit

posted by Scott Whigham @ 3:34 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 2 - Determining Which Events and Data Columns to Use in Your Trace

Pt. 2 - Determining Which Events and Data Columns to Use in Your Trace - view details
This video, part 2 in our "Intro to Profiler" series, provides a great overview of the various Events (also called "Event Classes") and how the Data Columns work. You'll also see how to choose the various options and how to group columns in Profiler for easier navigation over large traces. You can significantly impact your trace's "drag" on your SQL Server by wisely choosing your events, data columns, and filters when you start your trace. This video walks you through the Event Classes (and makes suggestions as to which to use for what purpose), what common Data Columns are used (and what are Data Columns anyway?), and gives you several creative ideas for doing your own traces.

Highlights from this video:
  • New Events in SQL Server 2005
  • How templates affect your events and columns
  • Tracing for performance tuning
  • Tracing errors
  • What's the difference between "Profiler" and a "trace"?
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: sql trace, RPC:Complete, CursorOpen, Deadlock, Exception, Data File Auto Grow, SP:CacheMiss, SP:CacheInsert

posted by Scott Whigham @ 3:34 AM   0 comments