Monday, January 29, 2007

New Microsoft SQL Server Video Tutorial: Pt. 2 - Using Views in the Real World

Pt. 2 - Using Views in the Real World - view details
This video, part 2 in our series on using views, helps you understand the logic behind using views as well as providing real world examples for designing and using views in your databases and applications.
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 @ 5:29 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
Part three in our series on views focuses on the dirty work: the CREATE VIEW statement. There are various ways to write the CREATE VIEW statement, all of which end up the same way (with a new view) and this video helps you understand the syntax.
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 @ 5:29 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 4 - Updateable Views in SQL Server (How to Update the Database Through a View)

Pt. 4 - Updateable Views in SQL Server (How to Update the Database Through a View) - view details
Our final video in this series is on how to modify data through your videos. There are certain rules that you must adhere to before you can issue an INSERT, UPDATE or DELETE statement using your view so you'll want to watch this video to be aware of these rules.
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: update sql view, updateable view, views as security mechanism, view security

posted by Scott Whigham @ 5:29 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 1 - Introduction to Using XML with SQL Server

Pt. 1 - Introduction to Using XML with SQL Server - view details
This video, part one in our series, is a high level overview of what XML is and when it's appropriate to use XML in a relational system. This is our entry point into using XML in SQL Server. The videos in this series are shot using SQL Server 2005 but, for those of you on SQL Server 2000, you'll be able to follow along as well.

Highlights from this video:
  • When would you use XML in SQL Server?
  • Working with FOR XML AUTO
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 mu st have the TSCC codec installed to view this video

Keywords Associated with this video: FOR XML AUTO, ROOT, PATH, part 1, beginning, beginners, intro, introduction

posted by Scott Whigham @ 5:12 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 "Create Publication Wizard" in SQL Server Enterprise Manager. 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?
  • 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 @ 5:12 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 2 - Using FOR XML Clauses in SQL Server

Pt. 2 - Using FOR XML Clauses in SQL Server - view details
This video takes you through several examples of building hierarchies using XML and using the FOR XML PATH clause. This video talks about using XQuery and XPath to work with the database and generate hierarchical list of XML data.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: querying xml data

posted by Scott Whigham @ 5:12 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 yo ur 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 @ 5:12 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 1 - Setting Up the Distributor and Publisher for Transactional Replication

Pt. 1 - Setting Up the Distributor and Publisher for Transactional Replication - view details
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 "Configure Distribution Wizard" to configure each option. Included is a detailed explanation of each of the screens in the wizard and recommendations for best practices.

Highlights from this video:
  • Replication architecture
  • What is the "distribution" database?
  • Suggestions for configuring the SQL Server agent for replication
  • What is the snapshot? Where is it stored? How often should it be generated?
  • Scripting the wizard's output for easier replay/debugging/maintenance
  • Using the Replication Monitor
  • Using the Distributor Properties and Publisher Properties
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

posted by Scott Whigham @ 5:12 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 4 - Updateable Views in SQL Server (How to Update the Database Through a View)

Pt. 4 - Updateable Views in SQL Server (How to Update the Database Through a View) - view details
Pt. 4 - Updateable Views in SQL Server (How to Update the Database Through a View)
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: update sql view, updateable view, views as security mechanism, view security

posted by Scott Whigham @ 5:12 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 2 - Using Output Parameters To Pass Data To the Caller

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

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

Note: You must have 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 @ 5:12 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 2b - Introduction to Using XML with SQL Server

Pt. 2b - Introduction to Using XML with SQL Server - view details
This video finishes out our discussion of when to use XML in SQL Server with a final discussion of using the XML datatype in SQL Server to store our Newsletter information.
To downloa d 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: newsletter, multiple outputs, xml, xsl, xslt

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

New Microsoft SQL Server Video Tutorial: Pt. 2a - Introduction to Using XML with SQL Server

Pt. 2a - Introduction to Using XML with SQL Server - view details
This video continues our discussion on beginning XML with SQL Server with a discussion on when you should/could/would use XML with SQL Server.

Highlights from this video:
  • Using XML with SQL Server 2005/2000
  • When is it appropriate to store XML in the database?
  • What datatypes are supported for working with XML?
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: semistructured data, semi-structured data, hierarchical data

posted by Scott Whigham @ 5:12 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 1 - Setting Up the Distributor and Publisher for Transactional Replication

Pt. 1 - Setting Up the Distributor and Publisher for Transactional Replication - view details
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 "Configure Publishing and Distribution Wizard" to configure each option. Included is a detailed explanation of each of the screens in the wizard and recommendations for best practices.

Highlights from this video:
  • Replication architecture
  • What is the "distribution" database?
  • Suggestions for configuring the SQL Server agent for replication
  • What is the snapshot? Where is it stored? How often sh ould it be generated?
  • Scripting the wizard's output for easier replay/debugging/maintenance
  • Using the Replication Monitor
  • Using the Distributor Properties and Publisher Properties
and much more
To download this video, Login, then visit the video's detail page and click "download". This is a subs criber-only video so a subscription is required.

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

posted by Scott Whigham @ 5:12 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 1 - Using FOR XML Clauses in SQL Server

Pt. 1 - Using FOR XML Clauses in SQL Server - view details
The FOR XML clauses allow you to generate XML based on your SELECT statements. There are several different ways to generate XML - FOR XML AUTO, FOR XML RAW, FOR XML EXPLICIT, FOR XML PATH, and more. How do you know which to use? What's the difference between RAW and AUTO? Which of these can auto-detect relationships in the data?

Highlights from this video:
  • An overview of the different FOR XML options
  • When should I use FOR XML?
  • Using FOR XML RAW
  • USING FOR XML AUTO
  • Changes in SQL Server 2005
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.

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

Keywords Associated with this video: sqlxml, msxml, sql xml, msxml4

posted by Scott Whigham @ 5:12 AM   0 comments  

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

Pt. 2 - Determining Which Events, Data Columns and Filters to Use in Your Trace - view details
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 how filters can be used (what Event Classes use what Filters?).

Highlights from this video:
  • Suggestions for using filters to create more manageable traces
  • Tracing for performance tuning
  • Tracing errors
  • Viewing trace data and interpreting Profiler's results
  • What's the difference between "Profiler" and a "trace"?
  • SQL Batches and the SP:BatchCompleted Event Class
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 @ 5:12 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 3 - Publications and Subscriptions

Pt. 3 - Publications and Subscriptions - view details
This video, our final overview video on SQL replication, finishes out our discussion with a lecture on publications and subscriptions.

Highlights from this video:
  • What objects (tables, views, stored procedures, etc) can I replicate?
  • Do I have to replicate the whole database or can I just replicate part of the database?
  • Can I replicate only certain columns of a table or do I have to replicate the entire table?
  • What types of subscriptions are available and what are the differences?
  • How to work with mobile and/or frequently disconnected systems
  • How synchronization schedules control replication and dictate "latency"
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 @ 5:12 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 vid eo'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 @ 5:12 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 @ 5:12 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 4 - Introduction to Using XML with SQL Server

Pt. 4 - Introduction to Using XML with SQL Server - view details
This video discusses using XQuery, XPath and XML DML as options for working with XML data.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: xquery standard, xdr

posted by Scott Whigham @ 5:12 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 3 - Introduction to Using XML with SQL Server

Pt. 3 - Introduction to Using XML with SQL Server - view details
Even more discussion of when to use XML in the database
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: xml, for xml, xml datatype, xml schema collections

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

New Microsoft SQL Server Video Tutorial: Pt. 1 - Using SQL Profiler - Concepts, Terms and Screens

Pt. 1 - Using SQL Profiler - Concepts, Terms and Screens - view details
SQL Server Profiler is just an amazing tool and one that every sysadmin should know "backwards and forwards." 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.

Highlights from this video:
  • Differences between SQL Server 2000 Profiler and SQL Server 2005 Profiler
  • Permissions required for SQL Server 2005 Profiler
  • Making decisions on the "General" screen
  • Should you save to file or save to table?
  • Using templates with Profiler
  • What sort of performance impact will Profiler cause? How much?
  • How to minimize the "drag" that Profiler creates
  • Server side vs. client side traces
  • What are rollover files (file rollover)? What should the setting be?
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 2005 profiler, profiling, sql sniffer, sniffing sql, tracing sql

posted by Scott Whigham @ 5:12 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 @ 5:12 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 T SCC 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 @ 5:12 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 4 - Configuring Profiler for Common Tasks (Templates)

Pt. 4 - Configuring Profiler for Common Tasks (Templates) - view details
Scott Whigham, author of LearnSqlServer.com, walks you through one of his favorite tweaks for SQL Server Profiler - creating and customizing templates. This is a very quick video but it's content will save you a ton of frustration and time!

Highlights from this video:
  • Creating templates
  • Best Practices for Backing Up templates
  • Configuring Profiler to auto-start a trace upon connection
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 @ 5:12 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 3 - What Do You Do With Your Trace Data? How to Write Queries Against Your Trace

Pt. 3 - What Do You Do With Your Trace Data? How to Write Queries Against Your Trace - view details
Do you want to know how many times a query has run in your trace? What about finding aggregates like what the minimum duration, maximum duration, and average durations/page reads/CPU? This is a great video that teaches you how to manipulate the trace data into a table for querying. Even then, you still have some work to do...

Highlights from this video:
  • How to save a trace to a table
  • Querying trace tables
  • Writing reporting-style queries against traces
  • Working with ntext data types and GROUP BY/WHERE clauses
  • Modifying your trace table to make it usable in aggregates
  • Saving the queries traced to a SQL file
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

posted by Scott Whigham @ 5:12 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 2 - The Three Most Common Types of SQL Server Replication

Pt. 2 - The Three Most Common Types of SQL Server Replication - view details
This video, part 2 in our series, focuses on Snapshot, Merge and Transactional replication architectures. You'll learn what the purpose of each type of replication is, common uses of each, and just a general overview of the replication types in SQL Server 2005 and SQL Server 2000.

Highlights from this video:
  • Understanding how synchronization affects replication
  • Snapshot replication architecture
  • Merge replication architecture
  • Transactional replication architecture
  • Useful analogies that help you understand and remember each type
  • Which type(s) of replication are "One Way" by default?
  • Which type(s) of replication supports conflict resolution?
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

posted by Scott Whigham @ 5:12 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 @ 5:12 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 TS CC 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 @ 5:12 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 TS CC codec installed to view this video

Keywords Associated with this video: trace template, start automatically, profile startup

posted by Scott Whigham @ 5:12 AM   0 comments  

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

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

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

Note: You 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 @ 5:12 AM   0 comments  

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

Pt. 3 - Setting Up Push 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 "Create Publication Wizard"
  • Discussion of the Distribution Agent and how it works (does it run at subscriber?)
  • Discussion of using non-SQL Server subscribers
  • Discussion of service account usage and recommendations
  • Snapshot initialization and how to start the Snapshot Agent
  • Working with multiple subscri ptions 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 @ 5:12 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 1 - Using SQL Profiler - Concepts and Screens

Pt. 1 - Using SQL Profiler - Concepts and Screens - view details
SQL Server Profiler is one of the greatest SQL Server admin tools ever - you absolutely need to know it! This video walks you through the basics of "What is SQL Server Profiler?" and "How do I run Profiler?"

Highlights from this video:
  • What traces are and how to use them
  • Security requirements for running Profiler
  • Using (local) or . or any alias in Profiler - good or bad?
  • Concepts of server-side tracing vs. client-side traces
  • Suggestions for naming traces
  • Explanation of rollover files and Best Practices for using Profiler
  • Should You Trace to File or Trace to Table? What's the "Best Practice"?
  • Using 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: sql trace, sql profiler, sql server 2000 profiler, profiling, sql sniffer, sniffing sql, tracing sql

posted by Scott Whigham @ 5:12 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 2 - Using Views in the Real World

Pt. 2 - Using Views in the Real World - view details
Pt. 2 - Using Views in the Real World
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 @ 5:11 AM   0 comments  

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

Pt. 4 - OUTPUT Parameters and How to Use Them - view details
One of the more confusing aspects of the stored procedure is the syntax and usage of OUTPUT parameters. This video tutorial will help you understand what OUTPUT params are, where they are used (in a Singleton pattern, for example), and how to access/assign the values to local variables. Don't miss this video!
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

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

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 7 - A Look at the Installed Folders

Part 7 - A Look at the Installed Folders - view details
In our final video on installing SQL Server 2005 for the first time we take a look at what folders are installed. What does MSSQL.1 mean? What is MSSQL.2? How does SQL Server choose what goes in which folders? Learn about it 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: sql folder, sql server install, program files, beginner, beginning, for beginners, introduction, step by step

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

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

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

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

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

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

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

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

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

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

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

New Microsoft SQL Server Video Tutorial: Pt. 2 - Creating Your First SSIS Packages (Integration Services Packages)

Pt. 2 - Creating Your First SSIS Packages (Integration Services Packages) - view details
The second video in our introduction series on Integration Services focuses on creating, importing, and adding packages in the BI Studio (more formally known as the SQL Server Business Intelligence Design Studio). This is an introductory-level video designed as a "first look" into SSIS packages and the BI Studio. Many people get very confused with the basic concepts of the SSIS architecture and this video helps allay some of the intimidation. Scott Whigham walks you through finding the SSIS Package Store, how the file system works within SSIS, and more information about creating packages.
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: SSIS package store, create ssis package, ssis package basics, ssis package intro

posted by Scott Whigham @ 5:11 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 t he 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 @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 4 - Modifying Maintenance Plans

Pt. 4 - Modifying Maintenance Plans - view details
One of the most exciting improvements to Maintenance Plans in SQL Server 2005 is the SSIS-based interface that you get when you modify the Maintenance Plans. This interface will allow you to create very intricate Maintenance Plans that go well beyond the wizard's basic functionality. You can add Operator notifications (i.e., send an email to an Operator whenever a Maintenance Plan task fails), add complex error handling or any type of auditing/SQL/etc.

This video is just an overview of how to modify the Maintenance Plans along with a quick walk-through of the drag-and-drop features of the SSIS interface. It does not go too deep, however, since you need some familiarity with SSIS to really expose the more interesting/flexible parts of the Maintenance Plan.

One suggestion: watch this series in conjunction with some of our introductory videos on SSIS to get the most out of your Maintenance Plans.
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: edit maintenance plan, schedule maintenance plan

posted by Scott Whigham @ 5:11 AM   0 comments  

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

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

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

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

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 2 - An Overview of the AdventureWorks Database - Database Diagrams and DDL Triggers

Part 2 - An Overview of the AdventureWorks Database - Database Diagrams and DDL Triggers - view details
Continuing our series on the AdventureWorks database, Scott Whigham walks you through how to create database diagrams using AdventureWorks as well as using the DDL triggers for database-level events. A very interesting video with some exciting new SQL Server 2005 features.
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: database events, database event trigger

posted by Scott Whigham @ 5:11 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, s tored queries, views, beginner, beginning, for beginners, introduction, step by step

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 2 - Index-Related Tasks in SQL Server 2005 (the Rebuild Index, Reorganize Index, and Update Statistics options)

Pt. 2 - Index-Related Tasks in SQL Server 2005 (the Rebuild Index, Reorganize Index, and Update Statistics options) - view details
Continuing our discussion on SQL Server 2005 Maintenance Plan tasks in the wizard, this video gives a nice overview of the index-related tasks, the 'Rebuild Index', 'Reorganize Index' and the 'Update Statistics' tasks.

Scott Whigham, author of LearnSqlServer.com, walks you through a simple-to-understand discussion of how index fragmentation works, how data-distribution statistics are calculated and why tasks such as the Rebuild Index tasks are vitally important to the health of your SQL Server. Included in this video is a discussion of online defragmentation vs. offline defragmentation and a quick overview of the new ALTER INDEX ... WITH REBUILD or WITH REORGANIZE (instead of the DBCC DBREINDEX and DBCC INDEXDEFRAG).

Also included in this video is a discussion of how often you should run these tasks along with the impact that each task will have on your server and users. You'll also understand why fragmentation occurs and how these tasks can help your database run faster.
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: index defragmentation, index defrag, how to defragment indexes, defragment sql server, alter index, dbcc

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 1 - Introduction to SQL Server 2005 Maintenance Plans

Pt. 1 - Introduction to SQL Server 2005 Maintenance Plans - view details
Maintenance Plans (and the Maintenance Plan Wizard) are not new in SQL Server; Microsoft introduced Maintenance Plans in SQL Server 7.0 as a great way to automatically schedule common DBA tasks such as backups. In SQL Server 2005, Microsoft has really listened to its customers and vastly improved the Maintenance Plans (and wizard). If you've used Maintenance Plans in the past, things have changed considerably but for the good.

This video walks you through (1) what maintenance plans are, (2) launching the Maintenance Plan Wizard, and the basic choices of the wizard. There are so many choices in the Maintenance Plan Wizard that this video can only cover so many; the remaining videos of the series will help you walk through all of the choices in the maintenance plans. This particular video focuses on the "Check Database Integrity" choice including a walk-through of DBCC CHECKDB and what impact selecting this option may have on your server and a discussion on how often you'll want to schedule this option.

Another option covered in this video is the "Shrink Database" task along with Scott Whigham's discussion of Best Practices for shrinking databases.
To download this video, Login, then visit the video's detail page and click "download". This is a subscriber-only video so a subsc ription is required.

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

Keywords Associated with this video: sql server maintenance plan, maintenance plans, maintenance plan task, maintenance plan wizard

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 2 - Common Table Expressions (CTEs) - Recursion and Recursive CTEs

Part 2 - Common Table Expressions (CTEs) - Recursion and Recursive CTEs - view details
This video, the second in our series on using Common Table Expressions (CTEs), walks you through recursion and working with hierarchies with CTEs. This video uses the Northwind database to simplify the learning process and uses some common recursive queries used in Northwind examples to highlight the basic use of the CTE.
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 recursion, sql hierarchy, hierarchical query

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 3 - The Cleanup History, Execute SQL Server Agent job, and Back Up Database Tasks

Pt. 3 - The Cleanup History, Execute SQL Server Agent job, and Back Up Database Tasks - view details
This video finishes off the discussion on the individual tasks and ties on with Parts 1 and 2 in this series as the final video on choosing the tasks in the Maintenance Plan Wizard. After watching this video, you'll understand the intricacies of the Cleanup History Task, the SQL Server Agent job Task, and the individual Back Up Database tasks (the Full, Differential, and the Transaction Log).

Included in this video are discussions on how often you should run the Cleanup History Task and how the SQL Server Agent job Task can work in conjunction with your existing jobs. This is definitely one of the best improvements to the Maintenance Plan architecture and allows your maintenance plans to be extensible beyond anything that was available in the SQL Server 7.0 or 2000 Database Maintenance Plans.

Of course, the main features of the Maintenance Plans that most people will use are the Back up Database tasks. This gives us the option for scheduling our full backups, differentials and transaction log backups. Job Schedules for maintaining backups are included and you'll see how the text file reports are generated as well as how the Maintenance Plan Wizard can create separate files for each backup with a time-stamp (DatabaseName_yyyymmddhhmmss.bak, for example).
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: schedule log backup, maintenance plan backup, how to backup sql server database, cleanup history, how to schedule sql server bac

posted by Scott Whigham @ 5:11 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
This video is designed to help you understand when you should use SET @Variable = value and SELECT @Variable = value. For new people coming into T-SQL programming, it's often quite confusing when to use SET vs. SELECT for variable assignment.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: assign variable from database

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 5 - How to Configure SQL Server 2005 to Listen on Multiple TCP IP Ports

Part 5 - How to Configure SQL Server 2005 to Listen on Multiple TCP IP Ports - view details
Another in our series on SQL Server 2005's Network Libraries, this video shows you a rather obscure and not-often-used technique of configuring your SQL Server to listen on multiple TCP/IP ports. TCP/IP is the most common way to connect to SQL Servers (including 7.0 and 2000) so the more you know, the better you are!
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 multiple ips, multiple tcp/ip ports

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 2 - SQL Server Network Library Discussion - TCP IP Connections and TCP IP Ports

Part 2 - SQL Server Network Library Discussion - TCP IP Connections and TCP IP Ports - view details
This video focuses on SQL Server connections and protocols, primarily how to connect to SQL Server 2005 using TCP/IP as well as a discussion on TCP/IP ports.
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: port, tcp, tcp/ip, tcp ip

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 3 - Running the January 2006 Microsoft SQL Server Upgrade Advisor Against a SQL Server 7.0 Instance

Part 3 - Running the January 2006 Microsoft SQL Server Upgrade Advisor Against a SQL Server 7.0 Instance - view details
For those of you wanting to upgrade your SQL Server 7.0 to SQL Server 2005, you'll want to run the SQL Server 2005 Upgrade Advisor against your SQL Server 7.0 instance before running the upgrade. This video walks you through running the Upgrade Advisor and provides insights into the meanings of the Upgrade Advisor reports including what is important and what you can safely ignore.
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 UA, sql server 7.0 upgrade, sql server 7 upgrade

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 2 - Choosing the Right Installation For Your Organization

Part 2 - Choosing the Right Installation For Your Organization - view details
Scott Whigham continues the series on installing SQL Server 2005 with this video on the setup choices.
To download this video, Login, th en 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 setup, install, typical, minimal, minimum, custom, set up, beginner, beginning, for beginners, introduction, step by step

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

New Microsoft SQL Server Video Tutorial: Part 1 - How to Configure SQL Server 2005 for Remote Connections Using TCP IP and Named Pipes

Part 1 - How to Configure SQL Server 2005 for Remote Connections Using TCP IP and Named Pipes - view details
When you install SQL Server 2005, remote connections are disabled. This can cause confusion for newbies trying to install and work with SQL Server for the first time. This video shows you the secure-by-default design and how to configure your SQL Server for remote connections and TCP IP.
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 port, sql connection, connect sql server

posted by Scott Whigham @ 5:11 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 @ 5:11 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
The rules for variable scoping (i.e., the "life of the variable") in SQL Server might be a little different from the rules in other languages. To understand variable scoping in SQL, you have to have a good understanding of batches. This video helps you understand both!
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: GO

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 1 - An Overview of the Setup Process Including Installing the Pre-Requisites

Part 1 - An Overview of the Setup Process Including Installing the Pre-Requisites - view details
The setup process for SQL Server 2005 is very involved, may take you over an hour (depending on features you choose and speed of media) and often features cryptic messages and stringent requirements. This video is designed to help you understand the initial setup including installing the Pre-Requisites (such as the .NET 2.0 Framework). You'll also receive a few suggestions and recommendations for ensuring a smooth setup experience.
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 prerequisite, sql pre-requisite, minimum requirements, sql hardware, .net 2.0, setup, set up, beginner, beginning, for begin

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

New Microsoft SQL Server Video Tutorial: Part 1 - Common Table Expressions (CTEs) - What They Are and When to Use Them

Part 1 - Common Table Expressions (CTEs) - What They Are and When to Use Them - view details
Common Table Expressions (CTEs) are an interesting addition to SQL Server 2005's Transact-SQL language. Scott Whigham, author of LearnSqlServer.com and LearnTransactSql.com, walks you through the basics of what CTEs are, the syntax and concepts of using CTE and gives you a few examples in this video. This video is part 1 in our series on CTEs and is designed to be an introduction to the syntax and the concepts of using CTEs.
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 recursion, sql hierarchy, hierarchical query

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 4 - How to Schedule an SSIS Package (Introduction)

Pt. 4 - How to Schedule an SSIS Package (Introduction) - view details
Our fourth video in this introductory series designed to help you perform the most basic SSIS tasks, this video focuses on how to schedule an SSIS package with a SQL Server Agent job. One of the new features of the SQL Server Agent job scheduling engine is the new SSIS Package Execution job step. There are many options for scheduling SSIS packages and this video shows you the most logical/simplest technique for storage and scheduling (using an SSIS package stored in the msdb).

Many people find package scheduling to be a very confusing topic - let this video help you schedule your SSIS packages!
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: schedule ssis package, schedule .dtsx, ssis job, ssis sql agent

posted by Scott Whigham @ 5:11 AM   0 comments  

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

New Microsoft SQL Server Video Tutorial: Pt. 3 - Introduction to SSIS Package Deployment

Pt. 3 - Introduction to SSIS Package Deployment - view details
How do you get an SSIS package "into" SQL Server 2005? Give up? It is quite confusing and one of the more common questions people have as they start looking at SSIS for the first time. If you want to schedule an SSIS package, there are several options but perhaps the easiest and most manageable technique is to (1) store the SSIS package in MSDB, and (2) schedule a job to execute the package. This video walks you through creating a "Deployment Utility" which is really just a euphemism for an XML file with a .SSISDeploymentManifest extension. If you double-click on the .SSISDeploymentManifest file, it launches the Package Installation Wizard that allows you to easily deploy an entire project's worth of SSIS packages. This is not the only technique but definitely the easiest way to deploy a series of related SSIS packages.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: SSISDeploymentManifest, deploy ssis package, build SSIS package, schedule ssis package, ssis package job step, deploy .dtsx

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

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 @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 3 - Understanding the SQL Server Configuration Manager's Display of TCP IP Properties

Part 3 - Understanding the SQL Server Configuration Manager's Display of TCP IP Properties - view details
The SQL Server Configuration Manager is a handy tool but perhaps it isn't the easiest tool to understand when you want to change/modify/view your server's TCP/IP settings. In this video, SQL Server consultant Scott Whigham walks you through each screen in the Configuration Manager and explains the meaning of some of the (cryptic) menu items and gives examples of how the settings affect your 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: view ip address, view tcp ip, view setting

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 6 - Using Aliases with TCP IP Connections

Part 6 - Using Aliases with TCP IP Connections - view details
In our final video on working with the SQL Server Network Libraries, you'll learn how to use client aliases to make your life much easier. Do you currently have to connect to your SQL Server using an IP address? If so, then you'll really get some mileage out of 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 client network utility, sql client, sql alias, server alias

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 4 - How to Change the SQL Server 2005 TCP Port Using SQL Server Configuration Manager

Part 4 - How to Change the SQL Server 2005 TCP Port Using SQL Server Configuration Manager - view details
Quick - what is the default port number for SQL Server's TCP IP connections? Anyone who knows SQL Server knows the default port number and, if you were a hacker who wanted to attack a SQL Server, you would start with trying to go after a SQL Server using the default port number (which, by the way, is 1433). This video shows you how to identify the current port number that SQL Server is using and how to change it. This is an excellent video for configuring your SQL Server for secure connections.
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: port, tcp, tcp/ip, tcp ip, change port, change tcp port, change sql server tcp port, change sql server port

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 2 - My How Things Have Changed - A Video Walk-Through of Some of the New Tools with SQL Server 2005

Part 2 - My How Things Have Changed - A Video Walk-Through of Some of the New Tools with SQL Server 2005 - view details
Part 2 in our series on What's New in SQL Server 2005's Toolbox, Scott Whigham walks you through a few more important tools and changes in SQL Server 2005.
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: change sql 2005, new sql 2005, feature sql 2005

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 4 - Choosing the Right SQL Server Authentication Mode

Part 4 - Choosing the Right SQL Server Authentication Mode - view details
Our fourth video in this series on installing SQL Server 2005, this video focuses on SQL Server 2005 security with a discussion of the Authentication modes. Included in this video are discussions on SQL Server Best Practices for Security, the differences between Windows Authentication Mode and Mixed Mode (which allows SQL Server Loginauthentication) 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: windows authentication mode, mixed mode, windows Login, sql Login

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 6 - Verifying Our Installation and Reviewing The Install Logs

Part 6 - Verifying Our Installation and Reviewing The Install Logs - view details
This is, as the title implies, Part 6 in our 7-part series on installing SQL Server 2005 for the first time. In this video, SQL guru Scott Whigham has finished the installation and shows you the logs that SQL Server uses to track the installation along with several of the changes that have now occurred on your system.
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 install, setup, sql server log, setup log, sql set up logs, beginner, beginning, for beginners, introduction, step by step

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 5 - Collations and How to Choose the Most Compatible Collation

Part 5 - Collations and How to Choose the Most Compatible Collation - view details
Collations, sort orders, code pages, character sets - whew! Most people, including even some advanced administrators, have a difficult time understanding what collations are and how to effectively use collations. This video walks you through what they are, how they work, and how you use them. You'll also learn why this screen of the setup is perhaps the most important - it's the only choice you'll make during the SQL Server 2005 setup process that cannot be changed later on!
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, collate, international, multinational, multi-national data, default collation, server collation

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 4 - Running the January 2006 Microsoft SQL Server Upgrade Advisor Against a SQL Server 2000 Instance

Part 4 - Running the January 2006 Microsoft SQL Server Upgrade Advisor Against a SQL Server 2000 Instance - view details
Running the Upgrade Advisor is easy enough but understanding the reports requires a little more knowledge and experience. This video walks you through running the SQL Server 2005 Upgrade Advisor and finished with a detailed walk through of the generated reports including what you can safely ignore and what will cause problems after you upgrade.
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 UA, sql server 2000 upgrade, sql server 2k upgrade

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 3 - Choosing Your Service Accounts and a Tour of SQL Server 2005's Services

Part 3 - Choosing Your Service Accounts and a Tour of SQL Server 2005's Services - view details
SQL Server 2005 introduces a few new services and a few new choices. This video talks about the services in SQL Server 2005 including the MSSQLServer, SqlServerAgent, SqlBrowser, and 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 service accounts, mssql, mssqlserver, mssql$, sql agent, full-text, sql browser

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 2 - Installing the SQL Server Upgrade Advisor from the CD and Upgrading From the Web

Part 2 - Installing the SQL Server Upgrade Advisor from the CD and Upgrading From the Web - view details
The SQL Server Upgrade Advisor should be updated prior to use on a production server and, in this video, Scott Whigham (author of LearnSqlServer.com) shows you how to upgrade the Upgrade Advisor and how to run it for your upgrade.
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 UA, sql server 2000 upgrade, sql server 7 upgrade, migrate

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 1 - An Overview of the Options Available for Upgrading and Migrating

Part 1 - An Overview of the Options Available for Upgrading and Migrating - view details
This video walks you through the choices for migrating to SQL Server 2005 from prior versions (such as SQL Server 2000 or SQL Server 7.0). This is the first video in our series on upgrading to SQL Server 2005 from previous versions and highlights the different options for upgrading along with offering recommendations for your upgrade.
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: upgrade sql server 2000, upgrade sql 7.0, upgrade sql 7, migrate sql server 2000

posted by Scott Whigham @ 5:11 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Install SQL Server - Discussions of Network Libraries Including Port Suggestions and Security Choices

Install SQL Server - Discussions of Network Libraries Including Port Suggestions and Security Choices - view details
During the installation of SQL Server 2000 you are asked which Network Libraries you would like to choose (if you choose the Custom installation). For each Network Library, you must choose the Network Address. Which are the most common network libraries used? What are the defaults? What about TCP/IP ports - which TCP port should you use for SQL Server? This video helps you make intelligent decisions that will protect your SQL Server from hackers, worms and viruses
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 network libraries, sql server tcp port

posted by Scott Whigham @ 5:10 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 2 - Installing Service Pack 4

Part 2 - Installing Service Pack 4 - view details
Part 2 in our series on installing Service Pack
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: sp4 sql server install

posted by Scott Whigham @ 5:10 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 1 - Installing Service Pack 4

Part 1 - Installing Service Pack 4 - view details
This video walks you through the first part of installing Service Pack 4
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: install sp4, sql server service pack install

posted by Scott Whigham @ 5:10 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Install SQL Server - Discussion of Whether Sharing SQL Server with IIS and Other Applications is Recommended

Install SQL Server - Discussion of Whether Sharing SQL Server with IIS and Other Applications is Recommended - view details
Veteran SQL trainer Scott Whigham talks to you about how to put your SQL Server on the web without unnecessarily exposing it directly to potential hackers, all wrapped in a discussion about whether you should install SQL Server on the web server or another server in your organization. This video also talks about how dynamic data pages, such as ASP, ASP.NET, and JSP, work with respect to 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: sql iis, sql server iis, where to install sql server, separate machine

posted by Scott Whigham @ 5:10 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 2 - Network Libraries and Authentication Modes - Choosing What's Right for Your Organization

Part 2 - Network Libraries and Authentication Modes - Choosing What's Right for Your Organization - view details
Part 2 in our series on SQL Server Netlibs
To download this video, Login, then visit the vi deo'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 netlibs, sql server network libraries, sql server appletalk, multiprotocol, protocol encryption

posted by Scott Whigham @ 5:10 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 1 - An Overview of the Default SQL Server Installation

Part 1 - An Overview of the Default SQL Server Installation - view details
SQL trainer Scott Whigham walks you through the default installation of SQL Server 2005 in this video, part 1 in a series on installing SQL Server 2005. In this video you'll learn about the instances, folders, services, and options chosen during the default install.
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 2005 overview, sql server change, beginner, beginning, for beginners, introduction, step by step

posted by Scott Whigham @ 5:10 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 1 - An Overview of the AdventureWorks Database

Part 1 - An Overview of the AdventureWorks Database - view details
AdventureWorks - the "new standard" for sample databases out of Redmond. Scott Whigham, author of LearnSqlServer.com and LearnTransactSql.com, is certainly a fan of this new database for certain things but also feels reservations about the complexity of AdventureWorks when it comes to using it as your primary training database. Nevertheless, this video (part 1), walks you through the layout of AdventureWorks, how the schemas are used, and shows some of the "primary" relationships between the important tables and views. This video provides a good starting point for working with AdventureWorks and understanding how the tables are related.
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: adventureworks install, sample database, sql server sample database, sql server adventure works, oltp, beginner, beginning, for

posted by Scott Whigham @ 5:10 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 1 - Network Libraries and Authentication Modes - Choosing What's Right for Your Organization

Part 1 - Network Libraries and Authentication Modes - Choosing What's Right for Your Organization - view details
Network Libraries in SQL Server are very important to SQL security. What you choose to enable or disable can have a profound impact on potential vulnerabilities. This video, Part 1, walks through the idea of Network Libraries and helps you choose the one(s) for your installation.
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 netlibs, sql server network libraries, sql server appletalk, multiprotocol, protocol encryption

posted by Scott Whigham @ 5:10 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Install SQL Server - Discussion of File Placement and Suggestions for High Performance

Install SQL Server - Discussion of File Placement and Suggestions for High Performance - view details
Here's a general rule of thumb for most SQL Server installations: the more hard drives that you can put your SQL Server's database files on, the faster your performance. This video talks about file placement and gives you a solid understanding of why SQL Server works better this way. If you really want to know the fundamentals and the architecture of SQL Server, this is an excellent place to start
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 file optimization, multiple hard drives, spread your database across multiple disks, raid, raid 1, raid 5

posted by Scott Whigham @ 5:10 AM   0 comments  

Monday, January 22, 2007

New Microsoft SQL Server Video Tutorial: Part 2 - How to Change SQL Server Authentication Modes with a Discussion on Differences in Service Pack Levels

Part 2 - How to Change SQL Server Authentication Modes with a Discussion on Differences in Service Pack Levels - view details
Part 2 in our series on changing authentication modes for 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: sql server authentication, sql security, sql server security

posted by Scott Whigham @ 5:54 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 2 - Cross Database Ownership Chaining - What It Is and When to Use It

Part 2 - Cross Database Ownership Chaining - What It Is and When to Use It - view details
This video is part 2 in our series on Cross Database Ownership Chaining
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 cross-database ownership chaining

posted by Scott Whigham @ 5:54 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Install SQL Server - Discussion of Collation Types and Recommendations

Install SQL Server - Discussion of Collation Types and Recommendations - view details
When you install SQL Server, you make perhaps one of the most important decisions you can make: which default collation you will use. Should you choose Windows Locale or a SQL Collation (and what's the difference)? Are SQL Server Authentication passwords case-sensitive (and why are you asking me that here)? Collations are incredibly important to SQL Server and the collation that you choose during setup can save you hours of frustration later on. Watch this video and get an excellent overview of what collations are and how you should go about choosing the right collation for your installation
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 collations, code page, Cp1252, CP1, Latin1_General, Latin, CP850 multi-lingual

posted by Scott Whigham @ 5:54 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 1 - How to Change SQL Server Authentication Modes with a Discussion on Differences in Service Pack Levels

Part 1 - How to Change SQL Server Authentication Modes with a Discussion on Differences in Service Pack Levels - view details
This video series covers how to change the authentication modes (from Mixed Mode to Windows Authentication Mode and vice-versa) and shows you the differences between Service Pack levels (an RTM install of SQL Server, for example, and an SP4 install in this case)
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: change sql authentication mode, change mixed mode

posted by Scott Whigham @ 5:54 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Part 1 - Cross Database Ownership Chaining - What It Is and When to Use It

Part 1 - Cross Database Ownership Chaining - What It Is and When to Use It - view details
Cross Database Ownership Chaining was introduced as a settable database and server option in SQL Server 2000's Service Pack 3 yet most people do not even know what it is. SQL Server trainer Scott Whigham shows you all the details about Cross Database Ownership Chaining in this video and also shows you how installing Service Pack 3 completely changes how your SQL Server's security model works. Microsoft does not recommend setting Cross Database Ownership Chaining for your server but why not? If you want to know the answer then watch this series of 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: sql cdoc, sql database ownership chaining

posted by Scott Whigham @ 5:54 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Discussions on Decisions Such as What to Install, Optional Components, and Infrequently Used Terms

Discussions on Decisions Such as What to Install, Optional Components, and Infrequently Used Terms - view details
SQL Server trainer and consultant Scott Whigham talks about what each of the options mean during the install regarding what tools to install (and which are his favorites) and some of the terms you might hear or use when discussing an install of SQL Server 2000.
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 install, install sql server

posted by Scott Whigham @ 5:54 AM   0 comments  

Friday, January 12, 2007

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

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 @ 8:30 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 @ 8:29 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 @ 8:29 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 @ 8:29 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 @ 8:29 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 @ 8:29 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 @ 8:29 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 @ 8:28 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 @ 8:28 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 @ 8:28 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 @ 8:28 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 @ 8:28 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 @ 8:28 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 @ 8:28 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 beginne rs, introduction, step by step

posted by Scott Whigham @ 8:28 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 @ 8:27 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 @ 8:27 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 @ 8:27 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 @ 8:27 AM   0 comments  

Thursday, January 04, 2007

New Microsoft SQL Server Video Tutorial: Pt. 2 - Using FOR XML Clauses in SQL Server

Pt. 2 - Using FOR XML Clauses in SQL Server - view details
This video takes you through several examples of building hierarchies using XML and using the FOR XML PATH clause. This video talks about using XQuery and XPath to work with the database and generate hierarchical list of XML data.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: querying xml data

posted by Scott Whigham @ 11:45 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 1 - Using FOR XML Clauses in SQL Server

Pt. 1 - Using FOR XML Clauses in SQL Server - view details
The FOR XML clauses allow you to generate XML based on your SELECT statements. There are several different ways to generate XML - FOR XML AUTO, FOR XML RAW, FOR XML EXPLICIT, FOR XML PATH, and more. How do you know which to use? What's the difference between RAW and AUTO? Which of these can auto-detect relationships in the data?

Highlights from this video:
  • An overview of the different FOR XML options
  • When should I use FOR XML?
  • Using FOR XML RAW
  • USING FOR XML AUTO
  • Changes in SQL Server 2005
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.

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

Keywords Associated with this video: sqlxml, msxml, sql xml, msxml4

posted by Scott Whigham @ 11:45 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 3 - Introduction to Using XML with SQL Server

Pt. 3 - Introduction to Using XML with SQL Server - view details
Even more discussion of when to use XML in the database
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: xml, for xml, xml datatype, xml schema collections

posted by Scott Whigham @ 11:45 AM   0 comments  

New Microsoft SQL Server Video Tutorial: XML Schema Collections and Using Typed and Untyped XML Data

XML Schema Collections and Using Typed and Untyped XML Data - view details
This video is a high-level overview of using XML Schema Collections to constrain the XML data stored in an XML datatype-based column or variable. You'll also learn about using typed and untyped xml data and when to use each.

Highlights from this video:
  • Discussion of the XML schema standard
  • Discussion of how to create XML Schemas��
  • Syntax for schemas
  • What are xml schemas and when would I use one?
  • Working with XML datatype variables
  • Creating XML Schema Collections
  • Difference between an XML schema and an XML schema collection
and much more
To download this video, Login, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required.

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

Keywords Associated with this video: typed data, untyped xml data, create xml schema collection, xml dml

posted by Scott Whigham @ 11:45 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 4 - Introduction to Using XML with SQL Server

Pt. 4 - Introduction to Using XML with SQL Server - view details
This video discusses using XQuery, XPath and XML DML as options for working with XML data.
To download this video, Login, then visit the video's detail page and click "download". You might also enjoy our free training videos.

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

Keywords Associated with this video: xquery standard, xdr

posted by Scott Whigham @ 11:45 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 2b - Introduction to Using XML with SQL Server

Pt. 2b - Introduction to Using XML with SQL Server - view details
This video finishes out our discussion of when to use XML in SQL Server with a final discussion of using the XML datatype in SQL Server to store our Newsletter information.
To downloa d 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: newsletter, multiple outputs, xml, xsl, xslt

posted by Scott Whigham @ 11:45 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 1 - Introduction to Using XML with SQL Server

Pt. 1 - Introduction to Using XML with SQL Server - view details
This video, part one in our series, is a high level overview of what XML is and when it's appropriate to use XML in a relational system. This is our entry point into using XML in SQL Server. The videos in this series are shot using SQL Server 2005 but, for those of you on SQL Server 2000, you'll be able to follow along as well.

Highlights from this video:
  • When would you use XML in SQL Server?
  • Working with FOR XML AUTO
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 mu st have the TSCC codec installed to view this video

Keywords Associated with this video: FOR XML AUTO, ROOT, PATH, part 1, beginning, beginners, intro, introduction

posted by Scott Whigham @ 11:45 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt. 2a - Introduction to Using XML with SQL Server

Pt. 2a - Introduction to Using XML with SQL Server - view details
This video continues our discussion on beginning XML with SQL Server with a discussion on when you should/could/would use XML with SQL Server.

Highlights from this video:
  • Using XML with SQL Server 2005/2000
  • When is it appropriate to store XML in the database?
  • What datatypes are supported for working with XML?
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: semistructured data, semi-structured data, hierarchical data

posted by Scott Whigham @ 11:45 AM   0 comments