Learn SQL skills


If anyone wants to upgrade/refresh their SQL skills and would like to do so using any webcasts\training videos\technet videos for SQL Server 2008; they can refer to the following resources:

Some more short videos are listed below:

How to create, modify, and execute a stored procedure with Project Houston?

How to create, select, and modify a view with Project Houston?

How to create, modify, execute, save, and open a query with Project Houston?

How to create and modify a table with Project Houston?

 

How Do I: Optimize SQL Server Integration Services?

In this video, Max Adams will introduce 5 tips to increase the performance of SQL Service Integration Services packages. From network packet resizing to altering the ETL (Extract, Transform and Load) SQL statements, these tips are designed to make the packages perform at an optimal level.

 

How Do I: Render reports to a wide-range of formats?

This video shows how use SQL Server Reports Portal and related tools to export or render reports to different file formats. This video demonstrates the following: How to export to PDF. How to export to Excel. How to export to Word. How to setup a subscription that exports a report in the PDF format to a file share. How to export to other file formats using the Report Builder. Design Considerations for Rendering: http://msdn.microsoft.com/en-us/library/aa178936(SQL.80).aspx

 

How Do I: Create an gallery of reusable report parts?

This video shows how use create a gallery of reports parts in SQL Server Reporting Services 2008 This video demonstrates the following: • How to create a report part gallery • How to publish report parts from existing reports • How to specify settings while publishing report parts • How to use the published report parts within reports

 

Creating a Basic Package (SQL Server Video)

This video demonstrates how to create a basic package by using the SSIS Designer in Business Intelligence Development Studio. You’ll learn how to create the project for the package, add a data flow task to the control flow of the package, and add elements to the data flow.

 

Designing Your SSIS Packages for Parallelism (SQL Server Video)

This video demonstrates how to increase the performance of your Integration Services packages by designing for parallelism.

 

How to: Automate SSIS Package Execution by Using the SQL Server Agent (SQL Server Video)?

This video demonstrates the following: 1) How to create a proxy account that runs an Integration Services package as a job step in SQL Server Agent; 2) How to create the job and the job step; 3) How to schedule the running of the job.

 

How to: Call a Web Service by Using the Web Service Task (SQL Server Video)?

Learn how to call a Web service successfully from an Integration Services package by configuring an HTTP connection manager and the Web Service task. Then, set up an XML task to read the information that you have retrieved, and use that information in the package.

 

Exporting SQL Server Data to Excel (SQL Server Video)

This video demonstrates how to use the SQL Server Import and Export Wizard to create a package that exports data from a SQL Server database to a Microsoft Excel spreadsheet.

 

How to: Use the Data Profiling Task (SQL Server Video)?

Learn how to use this powerful new task in SQL Server 2008 to become familiar with an unfamiliar database, or to look for problems in existing data. Take a quick look at all eight of the profiles that the task can compute.

 

Measuring and Understanding the Performance of Your SSIS Packages in the Enterprise (SQL Server Video)

This video demonstrates how to measure and understand the performance of packages, based on lessons learned from enterprise customers. In this video, you will learn the following guidelines for improving performance: 1) How the limits of the source system affect performance; 2) Why disk I/O is important; 3) Why you should establish a package performance baseline.

 

Understanding SSIS Data Flow Buffers (SQL Server Video)

This video looks at the memory buffers that transfer data to the data flow of an Integration Services package.

 

Tuning Your SSIS Package Data Flow in the Enterprise (SQL Server Video)

This video demonstrates how to improve the performance of the data flow in an Integration Services package. In this video, you will learn how to tune the following phases of the data flow: 1) Extraction; 2) Transformation; 3) Loading. You can apply these performance tuning tips when you design, develop, and run the data flow.

 

Troubleshooting: SSIS Package Execution Using SQL Server Agent (SQL Server Video)

This video demonstrates how to troubleshoot an SQL Server Integration Services package that doesn't run when you call the package from a SQL Server Agent job step. The package does run successfully outside SQL Server Agent.

 

How to: Implement a Lookup Transformation in Full Cache Mode (SQL Server Video)?

This video demonstrates how to implement a Lookup transformation in Full Cache mode by using a Lookup transformation, Cache connection manager, and reference data set that is stored in cache.

 

Add a Node to an Existing Failover Cluster in SQL Server 2008

In this video, Max Verun will demonstrate how to add a node to an exisiting SQL Server Faliover Cluster instance.

 

How Do I: Do I need to Scale Up or Scale Out in SQL Server 2008?

Scaling is all about making SQL Server 2008 capable of handling larger workloads. With SQL Server 2008 there are two distinct paths one can take when faced with a database environment which no longer can handle the workload it is receiving: Scale Up, Scale out In this video, Max Adams explores both of these options.

 

How Do I: Create Reusable Report Parts with SQL Server Report Builder?

This video explains how to create and publish reusable report parts with SQL Server Report Servers 2008 R2 and Report Builder
This video demonstrates the following:

  • Create a report using Report Building
  • Publish a report
  • Publish a report part
  • Edit and republish an existing a report part

 

How Do I : Install and Configure Master Data Services?

This video explains how to install the new Master Data Services component included with SQL Server 2008 R2.
This video demonstrates the following:

  • Where to find the installation program for MDS
  • How to complete the installation wizard
  • How to configure the MDS database
  • How to configure the MDS website

 

How Do I: Integrate a Map with SQL Server Reporting Services?

This video explains how to insert a map into a report using SQL Server 2008 R2 Reporting Services. The map utilizes spatial data within a SQL Server database
This video demonstrates the following:

  • Create a report using Business Intelligence Design Studio
  • Add a map to the report
  • Define spatial to visual within the report
  • The various maps available by default plus where to find additional maps

 

Creating Effective Aggregations - Technical Article

Aggregations are the key to getting the best performance out of your cube. MVP Chris Webb demonstrates the steps he takes to design effective aggregations.

 

How Do I: Installing SQL Server 2008?

In this video I will use the Installation Wizard to install a new instance of SQL Server 2008.

 

How Do I: Forecast Table Analysis Tool?

In this tutorial you will learn how to use the Forecast Table Analysis Tool for Excel. The Forecast tool uses the Microsoft time series algorithm to predict future values based on trends in existing time series data.

 

How Do I: Highlight Exceptions Table Analysis Tool for Data Mining?

In this tutorial we will learn how to use the Highlight Exceptions Table Analysis Tool for Excel. The Highlight Exceptions tool uses the Microsoft clustering algorithm and pattern analysis to find values in a dataset that are atypical.

 

How Do I: Scenario Analysis - Goal Seek Table Analysis Tool for Data Mining?

The Scenario Analysis tool uses the Microsoft logistic regression algorithm and allows you to model two types of scenarios and report the effect on either a single row or the entire table of input data. In this video we'll look at the Goal Seek scenario Table Analysis Tool for Microsoft Excel.

 

How Do I: Prediction Calculator Table Analysis Tool?

In this tutorial you will learn how to use the Prediction Calculator Table Analysis Tool for Excel. This tool generates both interactive and printable calculators for scoring new data against a desired outcome.

 

How Do I: Modifying Measures, Attributes and Hierarchies?

This video demonstrates how to modify the measures, attributes and hierarchies in a cube.

 

How Do I: Getting Started with the Table Analysis Tools for Excel?

This video will help you get started with the Data Mining Table Analysis Tools add-in for Excel by showing you how to open the tools, use the sample Excel data, and connect to an Analysis Services server.

 

How Do I: Analyze Key Influencers Table Analysis Tool?

In this tutorial you will learn how to use the Analyze Key Influencers Table Analysis Tool for Excel. The Analyze Key Influencers tool uses the Microsoft Clustering algorithm to enable you to select a column that contains a desired outcome or target value, and then analyze the patterns in your data to determine which factors had the strongest influence on that outcome.

 

How Do I: Fill From Example Table Analysis Tool?

In this tutorial we will learn how to use the Fill From Example Table Analysis Tool for Excel.

 

How Do I: Defining and Deploying a Cube?

This video demonstrates how to define and deploy an OLAP cube using Analysis Services.

 

Detect Categories Table Analysis Tool

In this tutorial we will learn how to use the Detect Categories Table Analysis Tool for Excel.

 

Shopping Basket Table Analysis Tool for Data Mining

In this tutorial you will learn how to use the Shopping Basket Analysis Table Analysis Tool for Microsoft Excel. The Shopping Basket Analysis tool uses the Microsoft Association Rules algorithm to detect the relationship of items frequently purchased together.

 

Scenario Analysis - What If Table Analysis Tool for Data Mining?

The Scenario Analysis tool uses the logistic regression algorithm and allows you to model two types of scenarios and report the effect on either a single row or the entire table of input data.

 

How Do I: Create a Utility Control Point (UCP)?

The Create Utility Control Point Wizard will help you to create a UCP on an instance of SQL Server to manage your SQL Server Utility. Presented by Colleen Hamilton and Buck Woody http://blogs.msdn.com/sqlrem/

 

How Do I: Convert a Table to use the Hierarchical Data Type?

In this video, Ty Anderson shows how to use T-SQL to convert a table that is self-referencing table for hierarchy purposes into a table that utilized the new Hierarchical data type.

 

How Do I: Design High Performance Cubes in SQL Server 2008 Analysis Services?

In this video, Jacques Victor explains how to Design High Performance Cubes in SQL Server 2008 Analysis Services.
The benefit of building a cube to house data is that business rules can be centralized for calculations that can't easily be stored in a relational data mart.
Note: The AdventureWorks Db is used in this video.
The Db can be found here: http://msftdbprodsamples.codeplex.com/wikipage?title=Installing%20Databases

 

How Do I: Optimize Data Warehousing with Microsoft SQL Server 2008?

In this video, Max Adams will present 10 tips toward optimizing Data Warehouses or DataMarts build on Microsoft's SQL Server 2008 platform.

 

How Do I: Monitor SQL Server Analysis Services Performance?

Tuesday, January 12, 2010, 1:59:25 AMGo to full article

In this video, Jacques Victor explains how to monitor SQL Server Analysis Services performance with SQL Server Profiler which can be used as a performance optimization and monitoring tool for Analysis Services.

 

How Do I: Use SQL Profiler with SQL Server 2008?

This video demonstrates how use SQL Profiler to perform the following tasks: - Create and Edit Traces
- Create Trace Templates
- Watch Trace results
- Start, Pause, and Stop a Trace
- Replay Trace results
- Capture SHOWPLAN events and view analyze their results

 

How Do I: Setup Snapshot Replication with SQL Server 2008?

This video demonstrates how to configure Snapshot Replication between two instances of SQL Server 2008. This video demonstrates the following: Creating a Publisher database, Creating Subscriber database, Selecting replicable SQL Server database objects, Configuring security for the replication processes.

 

How Do I: Set up Database Mirroring?

Database mirroring is a feature of SQL Server that allows an administrator to create a copy of the database that is always up to date. If the principal server fails or needs to be taken offline for maintenance the mirror server can be quickly set up to take on the workload. Mirroring provides high availability for your databases. In this video Scott Golightly shows how to use T-SQL commands to set up database mirroring between two servers. Follow along with these easy steps to create a database mirror to help protect your mission critical data.

 

How Do I: Setup Transactional Replication?

Transactional replication is a feature of SQL Server that replicates committed changes to the publisher database to one or more subscriber databases. Transactional replication is useful when you need data that is up to date in different locations. In this video Scott Golightly shows how to set up transactional replication. First the publisher is configured, then the distributor, next publications with articles are created, and finally a subscriber is created. The video shows making changes to the publisher database and having those changes appear at the subscriber.

 

How Do I: Optimize SQL Server Integration Services?

In this video, Max Adams will introduce 5 tips to increase the performance of SQL Service Integration Services packages. From network packet resizing to altering the ETL (Extract, Transform and Load) SQL statements, these tips are designed to make the packages perform at an optimal level.

 

How Do I: Create a data-tier application ?

Systematically create and register a data-tier application from an existing user database on a UCP enrolled instance of SQL Server.

 

How Do I: Connect to an existing UCP?

You will need the name of the instance of SQL Server and credentials to connect to an existing UCP.

 

How Do I: Set resource health policies?

Edit resource health policies for a managed instance of SQL Server, a data-tier application, and utilization of host resources like CPU and storage space.

 

How Do I: Modify UCP users?

Grant users the rights to view health status or administer health policies on the currently connected SQL Server Utility.

 

How Do I: Enroll instances of SQL Server with a UCP?

The Enroll Instance Wizard will guide you through the process of enrolling an instance of SQL Server with the currently connected UCP.

 

How Do I: Using the HierarchyID Data Type in SQL Server 2008 to manage data hierarchies?

Managing hierarchies in Information Systems has long been a challenge to most information systems professionals. The issues are simple, but the implementation of hierarchies within databases is complex and often onerous. The HierarchyID datatype introduced in SQL Server 2008 provides a new way to deal with these types of issues. In this video, Max Adams will introduce this new HierarchyID Datatype and examine how to:
- Create tables that implement this datatype
- Populate the table with data using CTE
- Query the table using some of the inherent functions available for the HierarchyID Datatype.

 

How Do I: Profile Data with the Data Profiling Task and Viewer

Data quality is important to every business. Using the SQL Server 2008 Data Profiling task computes statistics about the data in a database to spot incorrect values. The Data Profiling viewer allows you to view the generated statistics. In this video Scott Golightly shows how to create the data profile and how to review the results.

 

How Do I: Import Text Files with SQL Server Integration Services?

Many programs produce logs as plain text files. Sometimes these log files need to be imported into SQL Server to allow further analysis of the data. In this video Scott Golightly shows how to use SQL Server Integration Services to import text files into SQL Server

 

How Do I: Use the Row Value Constructor to insert data into a SQL Server 2008 table?

This video shows how to insert data into a SQL Server 2008 table using the Row Value Constructor.
This demo shows how to:
- Insert data into a SQL Server 2008 table
- Four different methods are used to insert bulk data, including the new Data Row Constructor

 

How Do I: Use the Service Audit Object in SQL Server 2008?

This video shows how to use the Service Audit Object to record events within SQL Server 2008 Enterprise Edition.
This demo shows how to:
- Create a Server Audit Object
- Create a Server Audit Specification
- Create a Database Audit Specification
- Examine the audit logs for Login Failed events

 

How Do I: Import Data from Oracle Databases?

Many organizations have both Oracle and SQL Server databases. In this video Scott Golightly shows how easy it is to use the import data wizard and SQL Server Integration Services to import data from a table in an Oracle database into SQL Server.

 

How Do I: Configure Database Mail in SQL Server 2008?

Database Mail is useful for sending e-mail to administrators and end users. Database mail can contain data from queries or from files or other resources on your network. In this video Scott Golightly shows how to configure Database Mail in SQL Server 2008. After setting up Database Mail we see how to test the mail and view the mail log.

 

How Do I: Block Connections to SQL Server By IP Address Using Logon Triggers?

This video shows how to restrict access to SQL Server 2005 SP 2 (Or Above) based on IP Address.
This demo shows how to:
- Retrieve the IP Address of the client using the EVENTDATA() function
- Use a Logon Trigger to restrict access to a SQL Server instance

 

How Do I: Use PowerShell to Transfer Data?

PowerShell can be used to export or import data. By creating a simple script you can easily move data between databases. This can be given to developers or testers to allow them to refresh data in their systems.

 

How Do I: Use PowerShell to Audit Servers?

Most DBAs spend a portion of their day reviewing servers looking for errors and potential problems. Windows PowerShell offers the ability to automate some of those tasks freeing the DBA to perform other duties. In this video Scott Golightly will show how to use PowerShell to automate reviewing log files and checking SQL Server properties.

 

Use the Database Engine Tuning Advisor

For many database administrators ensuring top performance is a large part of their job. By using the Database Engine Tuning Advisor the database any workload can be analyzed for changes to the database that will increase performance. By taking advantage of the database engine tuning advisor, administrators can reduce the amount of time they spend analyzing the database to increase performance. In this video Scott Golightly shows how to use a SQL script as the input to the database engine tuning advisor to determine how to increase performance in certain tables in the Adventureworks sample database.

 

Use the Filestream Data Type to Store BLOB Data

In this video Scott Golightly shows how to configure Windows and SQL Server to enable the Filestream data type. After configuring SQL Server to support the Filestream, Scott creates a new database and table that uses the Filestream data type. The Filestream data type is new in SQL Server 2008 and enables storing binary large object (BLOB) data like pictures and music as a file on a share to efficiently store the data while treating it as an integral part of the database so backup and restore operations include the Filestream data as well. Filestream allows SQL Server to efficiently manage the rich data that today's applications demand.

 

Configure a Report Portal in SSRS

This video shows how configure a SSRS report portal from scratch. This demo shows how to: setup a Report URL, define an execution account, create a Reports Server database, configure SMTP and more...

 

Create and Publish a Data Cube for SQL Server Analysis Server 2008

This video shows how to use Business Intelligence Development Studio (BIDS) to create an data cube for SQL Server Analysis Server 2008. This demo shows how to: use BIDS to create a data model project, create Data Sources, Data Views, Dimenions, and Cubes and how to Deploy the project to SSAS 2208.

 

Provide an Up-to-Date Data Warehouse with Change Data Capture

In this video Scott Golightly introduces us to the Change Data Capture feature of SQL Server 2008. After showing how to configure Change Data Capture on a database Scott shows a sample SSIS package that extracts the changed data from the database.

 

Automate Report Delivery in SQL Server Reporting Services Using Subscriptions

This video shows how to create report subscriptions to au This demo shows how to: configure a shared data source to store credentials (required for subscriptions), configure SSRS for SMTP email delivery, create a subscription that delivers reports to a file share and create a subscription that delivers reports via email.

 

Setting up a Maintenance Plan to Backup Databases

In this video Scott Golightly shows us how to create database maintenance plans using the Maintenance Plan wizard and SQL Server Integration Services (SSIS). Scott also shows how to customize plans that were created with the wizard.

 

Create and Modify Reports Using the Report Builder Tool

In this video Scott Golightly shows us how to use SQL Server 2008 Business Intelligence Studio to create a report model. We then use the report model and the Report Builder tool to create a report. The report is saved to the SQL Server Reporting Services reporting site where it can later be run or modified by other users who want a slightly different report.

 

Manage SQL Server 2008 Spatial Data

In this video Scott Golightly takes us through an overview of spatial data in SQL Server 2008. Using the geometry data type Scott looks at some of the functions that a DBA might have to know to properly understand and maintain the data being stored in the database. The session ends with a look at spatial indexes and how to create them.

 

Streamline an Upgrade from SQL Server 2005 to SQL Server 2008

In this video Scott Golightly introduces the SQL Server Upgrade Advisor. He then shows us two methods for using the command line to upgrade a SQL server without having to wait for dialog boxes or provide further input.

 

Synchronizing Remote SQL Server Databases with the Sync Framework

In this video Scott Golightly shows how to install the synchronization framework and then how to use it in conjunction with SQL Server 2008 change tracking to update remote, occasionally connected databases.

 

Configure Report Security in SSRS

This video shows how configure SSRS report security using both default and custom roles This demo shows how to: grant a user access to a report & folder, grant group access to a report & folder, create a custom role in SSRS, configure users to utilize the custom role and set different permissions for different folders in SSRS.

 

The New SQL Audit Feature of SQL Server 2008

In this video Scott Golightly shows us the new SQL Audit feature of SQL Server 2008. Scott shows us how to set up a server audit using Transact SQL. He also shows us how to create a database audit using SQL Server Management Studio. Using SQL Audit you can audit different actions on your database to ensure compliance and protect valuable information.

 

Use Policy Based Management Features of SQL Server 2008 to Manage SQL Servers

In this video Scott Golightly shows us how to use the new policy based management features of SQL Server 2008 to manage our SQL Servers. Scott shows us how to use facets, create conditions, and create policies to manage our servers. The video demonstrates cases where the policy can automatically update your servers to ensure they are in compliance. Finally we look at the policies based on the best practices analyzer provided by Microsoft.

 

The SQL Server 2008 Resource Governor

In this video Scott Golightly explores the SQL Server 2008 resource governor. The resource governor can help you allocate your CPU and memory effectively and ensure that rougue queries do not take over your server. Scott examines resource pools, resource groups, and the classifier function. Scott shows how to monitor the resource pools using performance monitor.

 

Create a Report with Visual Studio 2008 and Deploy it to SQL Server Reporting Services 2008

This video shows how to build a report using Visual Studio 2008 and then deploy it to SQL Server Reporting Services. This demo shows how to: create report project in Visual Studio, create an shared data sources, how to design a report, how to add interactive sorting, change the project settings to deploy the report,deploy the report and run the report in SSRS.

 

Enable Transparent Data Encryption

In this video Scott Golightly shows how to use the transparent data encryption (TDE) feature of SQL Server 2008 Enterprise Edition. In addition to showing how to create the keys and certificates necessary to enable TDE, Scott encrypts the AdventureWorks sample database. After encrypting the database, Scott discusses the effect encryption has on your server and demonstrates what happens with database restores when the proper keys are not available.

 

Improve Performance and Storage Utilization With Data Compression

In this video Scott Golightly shows how to use the data compression feature of SQL Server 2008 Enterprise and Developer Editions. In addition to showing how to enable data compression, Scott discusses the effect it can have on the size of your table, when you may not want to use it, and shows examples of how to see if compression is having the desired result.

 

Exporting SQL Server Data to Excel

This video demonstrates how to use the SQL Server Import and Export Wizard to create a package that exports data from a SQL Server database to a Microsoft Excel spreadsheet.

 

How to: Call a Web Service by Using the Web Service Task

Learn how to call a Web service successfully from an Integration Services package by configuring an HTTP connection manager and the Web Service task. Then, set up an XML task to read the information that you have retrieved, and use that information in the package.

 

How to: Implement a Lookup Transformation in Full Cache Mode

This video demonstrates how to implement a Lookup transformation in Full Cache mode by using a Lookup transformation, Cache connection manager, and reference data set that is stored in cache.

 

How to: Use the Data Profiling Task

Learn how to use this powerful new task in SQL Server 2008 to become familiar with an unfamiliar database, or to look for problems in existing data. Take a quick look at all eight of the profiles that the task can compute.

 

Creating an Analysis Services Project

This video demonstrates how to create an Analysis Services project, define a data source, and define a data source view. You will also learn how to change the value of the FriendlyName property for objects in the data source view to increase the user-friendliness of their names.

 

Defining and Deploying a Cube

This video demonstrates how to define and deploy an OLAP cube using Analysis Services.

 

Modifying Measures, Attributes and Hierarchies

This video demonstrates how to modify the measures, attributes and hierarchies in a cube.

Comments (1)

  1. christi parks says:

    I am not a programmer but I have this SQL subject this session and have to prepare for it. What all topics should be covered in it?

    And has anyone studied from this course http://www.wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance…

    would really appreciate help

Skip to main content