Download SQL Server Product Samples

 SQL Server 2005 Product Samples

http://www.codeplex.com/SqlServerSamples/Release/ProjectReleases.aspx?ReleaseId=4000

SQL Server 2005 Sample Database

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

SQL Server 2008 Product Samples

http://www.codeplex.com/SqlServerSamples

Assessment Tool for Microsoft Business Intelligence from Microsoft

 

The assessment tool can be used to compare your infrastructure against industry best practices and the latest IT research.  For more information please visit below location.

http://www.microsoft.com/bi/assessment.aspx

Microsoft BI Demos

 

You can find quick demos on Microsoft Business Intelligence at the below location.

http://www.microsoft.com/bi/resources/demos.aspx

Happy Learning…

Harsh Shah

Temporary Tables in SQL Server 2005

About Temporary Tables in SQL Server 2005

1. Temporary Tables are useful in a way when we want to create a table for temporary processing.
2. We can create temporary table in a same way as normal table except temporary table has a pound sign or hash sign (#)  preceding its name.
3. Temporary tables are created in a tempdb
4. Temporary is tables are available only in a batch or procedure that creates it. Once the execution of stored procedure or batch is over they are deleted.
5. If you create a temporary table in a Query analyzer, it will be available until  the end of the session.
6. You can also drop temporary table within the batch.
7. Temporary tables are accessible only those connections which creates the Temporary table. In this way the scope for temporary table is very limited.
8. Temporary tables are created in tempdb with a unique name. The unique name is created on assigned table name + connection identifier. Assigned table name is a name given to temporary table while creating it plus connection identifier.
9. The length of the Temporary table name is 116 characters + last 12 characters makes the name unique.

Let’s look at a simple example.

–Temporary Table Example

/*

Please execute as a batch. Since this will be executed in a SQL Query Browser (Analyzer) temporary table will be available till the session exists. So you might get error if you create table with the same name again in the same session.Because the table already exists.

you can also drop temporary table..Follow the below example 

*/

–Create Temporary Table
CREATE TABLE #Temp1 (Empno int PRIMARY KEY)

–Inserting row into a temporary table
INSERT INTO #Temp1 VALUES (1001)

–Select rows from Temporary Table
SELECT * FROM #Temp1

–Insert rows from other Table
INSERT INTO #Temp1 SELECT EMPNO from Test

–Select rows from Temporary Table
SELECT * FROM #Temp1

–Using Temporary Table in a stored procedure

CREATE PROCEDURE TempTableTest (@NewEmpNo int)
AS
SET NOCOUNT ON

BEGIN
    –Create new Temporary Table
    CREATE TABLE #Temp2 (Empno int PRIMARY KEY)

    –Insert new Record
    INSERT INTO #Temp2 VALUES (@NewEmpNo)

    –Display new Inserted Record
    SELECT * FROM #Temp2
END

EXEC TempTableTest 2

Result

Happy Learning..I will be posting few more articles on Temporary Tables

 

SQL Server 2008 Release Candidate 0

Download SQL Server 2008 Release Candidate – Here

SQL Server 2008 RC0 will automatically expire after 180 days.

SSIS Task Host Container

What is Task Host Container in SSIS?

1. It’s a default container where every single tasks fall into.
2. If we don’t specify a container then the task will fall into Task Host container.
3. The Task Host is not configured separately, instead, it is configured when we set the properties of the task it encapsulates.
4. SSIS extends variables and event handlers to the task through task host container

By Harsh Shah

MaxConcurrentExecutables property in SSIS Packages

Let’s learn about the MaxConcurrentExecutables property in SSIS

1. It’s a property of Control Flow. The value of the property is an Integer that contains the number of threads that a package can create.
2. Gets or sets the maximum number of threads a package can create.
3. Default value is -1 that means add 2 to the number of processor and use that value to execute the number of tasks in parallel. For example , if server has 2 processors and the default value is specified, SSIS will allow upto 4 tasks to be run parallel.
4. Valid values are 1 or higher and -1
5. It is used when parallelism exists in the workflow or package. If workflow is a series of sequential precedence constraints than this property has no effect.
6. If the number of possible parallel tasks are more than the number of allowable parallel tasks then some of the tasks will have to wait till the parallel threads are available.

Happy Learning !!!

By Harsh Shah

Difference between Control Flow and Data Flow

Let us understand the basic difference between Control Flow and Data Flow in SSIS 2005. 

Control Flow:

1. Process Oriented
2. Doesn’t manage or pass data between components.
3. It functions as a task coordinator
4. In control flow tasks requires completion (Success.,failure or completion)
5. Synchronous in nature, this means, task requires completion before moving to next task. If the tasks are not connected with each other but still they are synchronous in nature.
6. Tasks can be executed both parallel and serially
7. Three types of control flow elements in SSIS 2005
   1. Containers
      • Provides structures in the packages
   2. Tasks
      • Provides functionality in the packages
   3. Precedence Constraints
      • Connects containers, executables and and tasks into an ordered control flow.
      • We can control the sequence execution for tasks and also specify the conditions that tasks and containers run.
8. It is possible to include nested containers as SSIS Architecture supports nesting of the containers. Control flow can include multiple levels of nested containers.

Data Flow

1. Streaming in nature
2. Information oriented
3. Passes data between other components
4. Transformations work together to manage and process data. This means first set of data from the source may be in the final destination step while at the same time other set of data is still flowing. All the transformations are doing work at the same time.
5. Three types of Data Flow components
   1. Sources
      • Extracts data from the various sources (Database, Text Files etc)
   2. Transformations
      • Cleans, modify, merge and summarizes the data
   3. Destination
      • Loads data into destinations like database, files or in memory datasets.

Happy Learning…!!!

By Harsh Shah

Difference Between SET and SELECT commands in SQL Server

SET	SELECT
Can assign the value of an expression to the variable	Can assign the value of an expression to the variable
Cannot retrieve data from data source. It can only retrieve data from the expressions.	Can retrieve data from data source and also uses other SELECT clauses (WHERE, FROM etc)
Use SET only when you want to assign value of a function result or constant to a variable	Can use for both i.e. retrieve value of function result

Example

–Variable declaration
DECLARE
    @vName VARCHAR(10),
    @vobject_id VARCHAR(30),
    @vName1 VARCHAR(30);

–assigning a single value
SET @vName1=’Test Value’;

–Selecting and retrieving multiple values in a single select statement
SELECT
    @vName=name,
    @vobject_id=object_id
FROM
    sys.objects
WHERE
    name =’sysrowsetcolumns’

–printing the retrieved values
SELECT  @vName1,@vName, @vobject_id

–By Harsh Shah

Visual Studio 2008 Overview

Visual Studio 2008 Overview 

By this time we all are aware about the new product launch from Microsoft for the developer community. Yes, it’s Visual Studio 2008 and SQL Server 2008. Today we are going to have a quick overview on the new feature set of the product- Visual Studio 2008. 
  

Microsoft vision of smart client application is achieved by enabling developers to create connected applications rapidly and with the highest quality and the rich user experiences. Microsoft Visual Studio 2008 delivers on the Microsoft’s vision of smart client applications. 
  

Visual Studio 2008 enables organizations of every size to rapidly create more secure, manageable, and reliable applications that take advantage of Windows Vista™ and the 2007 Office system. 

  

Visual Studio 2008 delivers key advances for developers in three primary pillars: 

  

·      Rapid application development  

·      Effective team collaboration  

·      Break through user experiences 

  

Developers can take advantage of advanced development tools, debugging features, database functionality, and innovative features introduced in Visual Studio 2008 to create innovative products. 

  

Visual Studio 2008 includes enhancements such as  

  

·      Visual designers for faster development with the .NET Framework 3.5,  

·      Improvements to Web development tools  

·      Language enhancements that speed development with all types of data.  

·      Create AJAX enabled web applications  

  

Microsoft Visual Studio 2008 enables organizations to take full advantage of the .NET Framework 3.5 and the 2007 Microsoft Office system to create client, Office, Web, and mobile applications that deliver high-quality, rich user experiences that add to effective business processes and decision making. 

 

What’s New in Visual C# 

  

There are several new features introduced in C# 3.0.  

  

Visual C# IDE Features 

  

Multi-targeting 

  

with Multi-targeting you can specify the .Net Framework for your project i.e. you can write / develop programs for .NET 2.0/3.0/3.5 from the same IDE. 

  

New Project Types and Templates 

  

·      Several new project templates are provided for  

·      Windows Presentation Foundation,  

·      Windows Communication Foundation  &  

·      Web projects   

  

IntelliSense support for C# 3.0 

  

The Visual C# code editor provides statement completion and Quick Info to support the following new language constructs in C# 3.0: 

 

·         Implicitly Typed Local Variables 

·         Query Expressions 

·         Extension Methods 

·         Object/Collection Initializers 

·         Anonymous Types 

·         Lambda Expressions 

·         Partial Methods 

 

Refactoring support for C# 3.0 

 

The refactoring features, Rename, Signature Change, Extract Method, and Promote Local have been updated to support the following new language constructs: 

 

·         Query Expressions 

·         Extension Methods  

·         Lambda Expressions 

 

Code Formatting 

 

The code editor supports formatting options for several new C# 3.0 language constructs including query expressions. 

 

 

Happy Learning !!! 

Harsh Shah (Sr. Consultant)