Pages

Thursday 21 March 2013

Difference between MDX and T-SQL


 MDX  Vs  T-SQL

i)                    The  ability of MDX  to reference multiple dimensions but SQL refers to only two dimensions, columns and rows when processing queries.

ii)                  In SQL, the SELECT clause is used to define the column layout for a query, while the WHERE clause is used to define the row layout. However, in MDX the SELECT clause can be used to define several axis dimensions, while the WHERE clause is used to restrict multidimensional data to a specific dimension or member.

iii)                The process of creating an SQL query is also different than that of creating an MDX query.

iv)                The visualization of an SQL result set is intuitive; the set is a two-dimensional grid of columns and rows. The visualization of an MDX result set is not as intuitive, however. Because a multidimensional result set can have more than three dimensions.

v)                  To refer to such two-dimensional data in SQL, the name of a column and the unique identification of a row, in whatever method is appropriate for the data, are used to refer to a single cell of data, called a field. However, MDX uses a very specific and uniform syntax to refer to cells of data, whether the data forms a single cell or a group of cells.

Saturday 9 March 2013

Difference Between Script Task and Script Component

Script Task VS Script Component :

While both the objects provide scripting environments using the VSTA engine, yet there are inherent differences in their purpose and functions. This is due to their design architecture and the placement in development environment.

Ø  The main difference of Script Task and Script Component is the way the script is executed by both the objects. The Script Task executes the script only once for each execution instance of the task, while the Script Component generally executes the script for each row, as it applies the custom transformation on each of the data row reads.

Ø  The Script Task is available in the Control Flow task tab, while the Script Component works in the pipeline or the Data Flow Task.

Ø  You can choose Script Component as a Source, a transformation, or a destination. The choice of function and the metadata and properties you configure in the editor results in creation of different members of the base classes in the auto-generated code. The Script Task does not provide any such facility, as the code generated is always the same.

Ø  The Dts property works with only the Script Task and it is not available in the Script Component.

Ø  The Script Task supports breakpoints while Script Component does not support breakpoints. 

Monday 4 March 2013

Nonblocking, Partial Blocking and Blocking Transformations In SSIS


Classifying Data Flow Transformations on performance considerations:

The three classifications of Data Flow transformations that are based mainly on
performance considerations.

Nonblocking Synchronous Row-Based Transformations:

These transformations work on a row-by-row basis and modify the data by changing
the data in columns, adding new columns, or removing columns from the data rows,
but these components do not add new rows to the data flow. The rows that arrive at the
input are those that leave at the output of these transformations. These transformations
have synchronous outputs and make data rows available to the downstream components
straightaway. In fact, these transformations do not cause data to move from one buffer
to another; instead, they traverse over the data buffer and make the changes to the data
columns. So these transformations are efficient and lightweight from the process point
of view. The transformations that readily pass the data to the downstream components are classified as Row Transformations.

The following Row Transformations are examples of Nonblocking synchronous row-based transformations:

Ø  Audit transformation
Ø  Character Map transformation
Ø  Conditional Split transformation
Ø  Copy Column transformation
Ø  Data Conversion transformation
Ø  Derived Column transformation
Ø  Export Column transformation
Ø  Import Column transformation
Ø  Lookup transformation
Ø  Multicast transformation
Ø  OLE DB Command transformation
Ø  Percentage Sampling transformation
Ø  Row Count transformation
Ø  Row Sampling transformation
Ø  Script Component transformation configured as Nonblocking Synchronous
Row–based transformation
Ø  Slowly Changing Dimension transformation

Partially Blocking Asynchronous Row-Set-Based Transformations:

Integration Services provides some transformations that essentially add new rows in the
data flow and hold on to the data buffers before they can perform the transformation.
The nature of such transformations is defined as Partially Blocking because these
transformations hold on to data for a while before they start releasing buffers. As
these transformations add new rows in the data flow, they are asynchronous in
nature. For example, a Merge transformation combines two sorted data sets that may
be the same data, but it essentially adds new rows in the data flow. The time taken
by this transformation before starting to release the buffers depends on when these
components receive matching data from both inputs.

Following is a list of Partially Blocking asynchronous row set-based transformations:

Ø  Data Mining Query transformation
Ø  Merge Join transformation
Ø  Merge transformation
Ø  Pivot transformation
Ø  Term Lookup transformation
Ø  UnPivot transformation
Ø  Union All transformation
Ø  Script Component transformation configured as Partially Blocking Asynchronous
Row-set-based transformation

Blocking Asynchronous Full Row-Set-Based Transformations:

These transformations require all the rows to be assembled before they can perform
their operation. These transformations can also change the number of rows in the
data flow and have asynchronous outputs. For example, the Aggregate transformation
needs to see each and every row to perform aggregations such as summation or
finding an average. Similarly, the Sort transformation needs to see all the rows to
sort them in proper order. This requirement of collecting all rows before performing
a transformation operation puts a heavy load on both processor and memory of the
server. You can understand from the nature of the function they perform that these
transformations block data until they have seen all the rows and do not pass the data to
the downstream component until they have finished their operation.

The following are Blocking asynchronous full row-set-based transformations:

Ø  Aggregate transformation
Ø  Fuzzy Grouping transformation
Ø  Fuzzy Lookup transformation
Ø  Sort transformation
Ø  Term Extraction transformation



Resources:
For more detailed information regarding this study, please see
Ø  Microsoft SQL Server 2008 Integration Services by Ashwani Nanda
Ø  Microsoft Books Online




Saturday 2 March 2013

STORED PROCEDURE AND RULES FOR CREATING STORED PROCEDURE


STORED PROCEDURE AND RULES FOR CREATING STORED PROCEDURE IN T-SQL  

Stored Procedure:

To create Stored procedure, the CREATE PROCEDURE Statement is used. Stored procedures are created in the current database unless a temporary stored procedure is created in tempdb. Because stored procedure are precompiled, they usually provide the best performance of any type of query.

Rules for creating Stored procedure:

i)                    The name must follow the rules for identifiers.
ii)                  Referenced objects must exist when your stored procedure runs.
iii)                Object with the same name in a single stored procedure can’t be created and then drop or re-create.
iv)                Temporary tables can be referenced within the stored procedure. Local temporary tables disappear when the procedure ends.
v)                  Upto 1024 parameters can be allowed.
vi)                Procedure can be nested within procedure upto 32 levels deep.
vii)              Stored Procedure can’t have the following T-SQL create statement in them:
Ø  CREATE DEFAULT
Ø  CREATE RULE
Ø  CREATE PROCEDURE
Ø  CREATE TRIGGER
Ø  CREATE VIEW


TRIGGERS, TRIGGERS PERFORMANCE AND RULES FOR CREATING TRIGGERS


TRIGGERS, TRIGGERS PERFORMANCE AND RULES FOR CREATING TRIGGERS IN T-SQL

Triggers:

A Trigger is a special type of stored procedure that’s automatically invoked when the user try to modify data that it’s designed to protect. Triggers help to secure data’s integrity  by preventing unauthorized or inconsistent changes from being made.

Triggers Performance:

From the performance point-of-view, triggers have a relatively low amount of overhead. Most of the time involved in running a trigger is used up by referencing other tables. The referencing can be fast if the other tables are in memory or a bit slower if they read from disk.

Rules for creating Triggers:

i)                    Triggers can’t be created on temporary tables. They can, however, reference views and temporary tables.
ii)                  Triggers can’t return result sets. Using the IF EXISTS clause as a part of a SELECT Statement in trigger code is a common practice.
iii)                Triggers should be used to maintain data integrity, maintain referential integrity, and encapsulate business rules.
iv)                Triggers can be encrypted in the syscomments table if you specify the WITH ENCRYPTION option.
v)                  WRITETEXT statement don’t activate triggers. They are used to modify text or image data, and it’s a nonlogged transaction.
vi)                The following SQL Statement can’t be used in a trigger:
Ø  All CREATE Statements
Ø  All DROP Statements
Ø  ALTER TABLE and ALTER DATABASE
Ø  TRUNCATE TABLE
Ø  RECONFIGURE
Ø  LOAD DATABASE or TRANSACTION
Ø  UPDATE  STATISTICS
Ø  SELECT INTO
Ø  DISK STATEMENT