Archive

Archive for the ‘SQL Server’ Category

What are SQL Query Hints?

July 5, 2014 Leave a comment

SQL server usually selects the best query plan required to execute a T-SQL query. However, there might be cases where the plans selected are not the best. In such situations, you can use the query hints to suggest the best possible plan/method of executing a query. The Query hints can be applied on all operators in a query. These hints apply to SELECT, DELETE, INSERT, UPDATE and MERGE. You can use the Query hints in the OPTION clause after the query.

Few commonly used Query hints with examples are as below.

Read more…

 

SSIS : Using and Configuring For Loop Container

July 5, 2014 Leave a comment

What is For Loop Container?

For loop defines a repeating control flow. It has similar behavior as the “FOR” loop available in programming languages.  The For Loop container uses 3 expressions as definition

  1. Initialization expression:  it assigns value to the loop counters. This expression is optional.
  2. Evaluation expression : It contains the expression to test whether the loop should continue executing  or exit.
  3. An optional iteration expression that increments or decrements the loop counter.

The expressions used must be valid SSIS expression. A variable is generally used in the expression.

Using and Configuring For Loop Container

(Task in hand: print counter variable value for each iteration times.)

Drag a for loop container into the SSIS Control flow window from the Toolbox. Double click on the for loop container to view the “For Loop Editor”. As mentioned earlier it contains 3 expressions. Now create a variable by name “TestVariable” of type “int32”.

Use this variable as counter in the container using expressions as shown below.

Read more…

 

 

Advantages and differences between CHAR and VARCHAR datatypes in SQL Server

April 25, 2014 1 comment

Everyone reading this must have come across these datatypes, but have you ever thought what are the differences and advantages between these two?

The obvious difference that comes to our mind is related to the length. Yes! You are right. The varchar supports variable length and Char supports fixed length. This is the main advantage varchar datatype. for example,  let us consider varchar(10) and char(10) . To store “GEEKEPISODE”, both the datatypes will use the complete length allocated and to store “GEEK”, CHAR datatype will use the complete 10 characters  allocated, but VARCHAR will use only 4.

From the above examples it might seem that VARCHAR is better than CHAR all the time. But remember, CHAR was included as a separate datatype because it has certain advantages over VARCHAR.

Compound Operators in SQL Server

July 5, 2011 Leave a comment

There was a time when we all wished SQL Server supports compound operators just like any other programming languages. Wouldn’t it be nice to have a shorter syntax for assigning the result of an arithmetic operator?  Well, SQL server 2008 introduced Compound operators that allow you to perform several arithmetic operations using an operand along with equal to (=) operand.

 For example:

Declare @var1  int

Set @var1 = 150

Set @var1 += 100

Read more…

 

 

Understanding MERGE Transact SQL Statement

July 5, 2011 1 comment

By using “Merge”, you can perform insert, delete and update on a table in a single SQL statement. This helps in synchronizing the tables as well.

However while using merge there are several points that you need to keep in mind. Few

  • In a “Merge” statement, a “When Matched” clause with a search condition cannot appear after a ‘When Matched’ clause with no search condition.
  • A “Merge” statement must be terminated by a semi-colon (;).
  • An action of type ‘DELETE’ is not allowed in the ‘WHEN NOT MATCHED’ clause of a MERGE statement.
  • At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable cannot be updated more than once in the same MATCHED clause.
  • Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints.

Few examples of merge are as given below.

Read more…

 

 

SSIS : Sequence Container

July 5, 2011 Leave a comment

Sequence containers is used to group the package into multiple separate control flows, each containing one or more tasks and containers that run within the overall package control flow. There are several advantages in using sequence container

  1. Provides facility to set property of multiple tasks by setting the property of sequence container.
  2. Provides the facility of disabling groups of tasks to focus debugging on one subset of the package control flow.
  1. Scope for variables that a group of related tasks and containers use can be controlled easily.
  2. Managing and visualization of group of tasks becomes easier using sequence container.
  3. We can also expand and collapse the container, hence making the package look neat.

Using Sequence Container

Create a new SSIS package ( refer video to create new SSIS package). Drag the sequence container from the toolbox on the left hand side into the package.

Read more…

 

Creating Excel Files (.xls) dynamically from SSIS

April 25, 2011 1 comment

In this blog, I will walk you through the creation of excel files ( xls / Excel 2003)  dynamically through SSIS.

Scenario: every day one of my process runs to pull data from SQL server table to excel file. I need to use unique file everyday with name as “taxonomy_<date>.xls” , for example on 14th of January 2011 the file name should be “Taxonomy_01142011.xls” with the excel sheet name “TaxonomyValues”.

It is pretty simple to create this file dynamically. At first we need to set up an excel connection manager pointing to the file. The connection manager needs to be dynamically configured to point to the correct file everyday, in our case “Taxonomy_01142011.xls” on 14th Jan. To do this,

  1. go to properties window of the excel connection manager
  2. click on expression and the browse ( … symbol) and choose “excel File Path” property. ( please refer the pictures below)
  3. Copy and paste the expression given below or develop similar expression. Click on evaluate expression and it will display the file path as evaluated value.

Continue reading..

.