Saturday, May 27, 2006

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

Part 1 - Introduction to Using Control-of-Flow Language in Your SQL - view details
IF, WHILE, GOTO, BEGIN and END are all essential parts of the SQL language and this video, part 1 in our series on using conditional statements, focuses on teaching them to you. Many people get confused when working with SQL's IF statement becuase there is no END IF syntax and the rules for execution are not intuitive perhaps. This video explains the proper use of IF along with BEGIN and END to help control code execution.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 7:10 AM   0 comments  

Friday, May 26, 2006

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, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 9:20 AM   0 comments  

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

Part 2 - Design Pattern for JOINs - A Simple Way to Look at Joining Tables - view details
The second in our two-part series on JOIN patterns, this video shows you the final pattern and walks you through creating your own template that you can use to simplify your use of the design pattern.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 9:20 AM   0 comments  

New Transact SQL Video Tutorial: Naming Conventions for SQL Server Tables Constraints and Indexes (SQL Server 2000)

Naming Conventions for SQL Server Tables Constraints and Indexes (SQL Server 2000) - view details
Tis video, recorded by author and consultant Scott Whigham, walks you through some of the common naming conventions for SQL Server tables, stored procedures, triggers and more. Do you use tblCustomers, Customer, Customers? Which is "right"? How should you name your stored procedures - sp_AddCustomer, AddCustomer, usp_Customer? If you want a solid understanding of how developers name their objects, watch this video.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 9:20 AM   0 comments  

Thursday, May 25, 2006

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 peolpe would approach this query along these lines: "SELECT ... FROM Orders JOIN OrderDetails ... WHERE Year(DateOrdered)=2006 AND COUNT(*) > 5". Focus on the WHERE clause for a moment: is that right? If you aren't sure or if you think it is right, then you need to watch this video to understanding why this query must use the HAVING clause.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 5:47 PM   0 comments  

New Transact SQL Video Tutorial: Interactive Help and Books Online - Using Context-Sensitive Help (SQL Server 2000)

Interactive Help and Books Online - Using Context-Sensitive Help (SQL Server 2000) - view details
This is a video tutorial for those of you new to SQL Server. If you want to quickly get up to speed on how to work with the Transact SQL language, you'd better learn how to navigate Books Online (BOL). TSQL is such a complex language that you're going to really appreciate having a T-SQL reference at your fingertips. Of course there is other information in BOL too...
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 5:47 PM   0 comments  

Monday, May 22, 2006

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

How to Return Identity Value for a Just-Inserted Row - view details
So many SQL developers use IDENTITY columns (a.k.a. sequences and/or auto-number columns) therefore this video is essential knowledge for SQL developers, DBAs, and even application developers. This tutorial focuses on working with IDENTITY columns as primary keys and how you can retrieve the just-inserted row's primary key value. Several techniques are discussed here with a final discussion on perhaps the "safest" (and recommended) technique.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 6:26 AM   0 comments  

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

Overview of SET Commands in Transact-SQL - What They Are, Syntax, and How to Use Them - view details
SET commands in T-SQL control the environment in which SQL Server or the client processes that client queries. This view shows you some of the more common SET commands, how they work, scoping (are they scoped to the statement, the batch, the connection?), and how you can use SET commands to increase throughput and help increase your SQL knowledge.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 6:17 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, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 6:15 AM   0 comments  

Sunday, May 21, 2006

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, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 2:17 PM   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 in SQL Server 2000 - What They Are and How to Use Them to Generate Your Data Dictionary
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 2:00 PM   0 comments  

New Transact SQL Video Tutorial: Design Pattern for Looping Through Rows and Executing A Stored Procedure - Using a Temporary Table

Design Pattern for Looping Through Rows and Executing A Stored Procedure - Using a Temporary Table - view details
Another great video in our Design Pattern series, this video tutorial focuses on how to iterate through a resultset and execute a stored procedure for each of the row values. Example: let's say you wanted to execute SP_HELPDB for every database on your server. You could write the code by hand by entering EXEC SP_HELPDB master; EXEC SP_HELPDB msdb; etc. The problem? This is not a dynamic approach so any new databases added after your script would not be included. The technique in the video uses temp tables for iteration.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 2:00 PM   0 comments  

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

Design Pattern for Finding Duplicate Data - A Simple Way to Spot Non-Unique Rows (Single Column Test) - view details
Let's say that you have a Primary Key on "SubscriberId" but you want to locate rows that live in the same Country - how would you do it? This query talks about spotting duplicates in a single column (like "Country" in this case). Primary Keys prevent duplicate data in the table but they don't necessarily prevent duplicates across non-key columns. This video is all about spotting single-column duplicates and it uses subqueries and the EXISTS operator to help you locate the dupes.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 8:51 AM   0 comments  

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, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 8:00 AM   0 comments  

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

Design Pattern for Finding Duplicate Data - A Simple Way to Spot Non-Unique Rows (Multi Column Test) - view details
We have multiple SQL tutorials for writing queries to return duplicate data in a column or multiple columns. This video focuses on spotting dupes across multiple columns. Although primary keys prevent duplicate rows, having a Primary Key on "EmployeeId" does not prevent users from entering duplicate LastName/FirstName combinations. This video shows you how to use subqueries (both correlated and simple) along with EXISTS to locate non-unique rows.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 8:00 AM   0 comments  

Saturday, May 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
Why You Use SCOPE_IDENTITY() Instead of @@IDENTITY
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 4:57 PM   0 comments  

Friday, May 19, 2006

New Transact SQL Video Tutorial: Working with User Defined Data Types in the Enterprise Manager (SQL Server 2000)

Working with User Defined Data Types in the Enterprise Manager (SQL Server 2000) - view details
User Defined Datatypes have existed for many years in SQL Server (sysname is an internal user-defined data type for example). This video explores the concept of what they are and where they are generally used but it ends with some "gotchas" - some things that may surprise you about User defined datatypes. Scott Whigham, author of this video, cautions against overuse of these "shortcuts" due to the manageability factor. Learn to create and manage your own custom defined datatypes for ease of use and consistency in this video.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 8:33 AM   0 comments  

Thursday, May 18, 2006

New Transact SQL Video Tutorial: Resultsets, Recordsets, and the Client Server model (SQL Server 2000)

Resultsets, Recordsets, and the Client Server model (SQL Server 2000) - view details
This is an excellent video for those of you wanting to get a solid foundation in the terms and techniques you can use to connect to and retrieve data from a SQL Server database. Although this video was originally created on a SQL Server 2000 machine, the concepts have not changed in SQL Server 2005 with the exception of the new SQL Native Client. This is an excellent video for newbies and those just getting started with database programming and the client/server world.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 5:51 PM   0 comments  

New Transact SQL Video Tutorial: Creating Linked Servers to Microsoft Access (SQL Server 2000)

Creating Linked Servers to Microsoft Access (SQL Server 2000) - view details
Creating Linked Servers to Microsoft Access (SQL Server 2000)
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 8:05 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 LearnSqlServer2005.com, LearnSqlServer2000.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, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 8:05 AM   0 comments  

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

Pages, Extents, and Blocksizes - What They Are and How They Work (SQL Server 2000) - view details
Another in our series on the internals of SQL Server, this video focuses on data storage, particularly data pages and how SQL Server stores the data on pages. All SQL Server developers and administrators should know the page size, what an "extent" is, what types of pages there are, and the basics of the internal storage engine and this video helps you learn the fundamentals.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 7:51 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, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 7:47 AM   0 comments  

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

Collations, Character Sets, Code Pages, and Sort Orders - How String Data Works in SQL Server 2000 - view details
Many developers do not get the opportunity to work with international and multi-national data therefore they have a cursory understanding of collations in SQL Server. If you'd like to understand Unicode data and what the difference is between the nchar/char datatypes and the nvarchar/varchar datatypes, then this video is for you. You'll learn about code pages, character sets, collations, and how SQL Server works with case-sensitive data.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 7:35 AM   0 comments  

New Transact SQL Video Tutorial: Choosing Between Cursors, Table Variables and Temp Tables (SQL Server 2000)

Choosing Between Cursors, Table Variables and Temp Tables (SQL Server 2000) - view details
Cursors have long been available as a TSQL developer's method of recursion and working with hierachical data but are they necessary? Perhaps temporary tables are a more effective solution or even table variables? If you'd like to learn when to use each (or whether to use a cursor at all), then you'll enjoy this video from SQL consultant and trainer Scott Whigham. Scott focuses on the syntax and use of each choice and discusses the differences between the three.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 7:35 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, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 7:32 AM   0 comments  

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

Aliasing in Queries - What It Is and How to Use It (SQL Server 2000) - view details
Table aliases, column aliases, column headers, resultset headers, range variables, correlation names - whew! That's a lot of terms to describe a simple concept! In this video, you'll learn the proper use of SQL aliases including when and where you can/cannot use them. Most queries can benefit from using aliases so understanding their use and Best Practices will certainly help you in your quest for SQL greatness.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 7:30 AM   0 comments  

Wednesday, May 17, 2006

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
Design Pattern for Finding Duplicate Data - A Simple Way to Spot Non-Unique Rows (Multi Column Test)
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 3:04 PM   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
Creating and Altering Views in the Enterprise Manager (SQL Server 2000)
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 2:49 PM   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, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 2:48 PM   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, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 1:39 PM   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, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 1:39 PM   0 comments  

New Transact SQL Video Tutorial: SQL Server 2005 Query Governor Bug

SQL Server 2005 Query Governor Bug - view details
SQL Server 2005 Query Governor Bug - Oops…
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 1:39 PM   0 comments  

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

Performance Tuning Query Tips That Everyone Should Know - Using DBCC and SET Commands - view details
There are lots and lots of DBCC commands related to performance tuning but did you know that several SET commands can help you out with your query tuning? This video, recorded by SQL consultant Scott Whigham, teaches you several of the most commonly used DBCC and SET commands for performance tuning and provides some tips and tricks for their use. Note: you'll want to likely have a solid foundation in query writing before viewing this video. An understanding of execution plans would also be helpful.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 1:31 PM   0 comments  

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

Part 3 - GOTO Statements and When and How to Use Them - view details
"IF @@Error <> 0 GOTO Errorhandler" Look familiar? Well, it might if you have been working with error handlers and writing GOTO labels and statements. In this third installment in our series on working with Control-of-Flow language, SQL instructor Scott Whigham shows what GOTO is, how to use it and talks about best practices for using GOTO in your queries.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 1:31 PM   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, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 1:31 PM   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 designers Best Friends it seems while 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, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 1:23 PM   0 comments  

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

Linked Servers - How to Query Microsoft Excel Named Regions 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, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 1:23 PM   0 comments  

New Transact SQL Video Tutorial: How to Avoid Using a Temp Table When Constructing Dynamic SQL (Looping) to Build Arrays and Lists

How to Avoid Using a Temp Table When Constructing Dynamic SQL (Looping) to Build Arrays and Lists - view details
Maybe you need to create a comma-separated list of every column in a table or every value in a row, how would you do it? Most people would immediately think of using a temporary table (a temp table) or a table variable and iterating through the rows one by one, concatenating a local variable (possibly by using dynamic sql), and then returning the final variable. This video shows a more set-based approach and walks you through the execution and logic. Want to avoid using a cursor or staging table? Check out this alternative and, if you're lucky, it can help you avoid row-by-row thinking!
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

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

posted by Scott Whigham @ 1:23 PM   0 comments