SQL Server and MS BI With a Geek Touch

Rank() function is used to give ranking to the records chosen through a select clause.  Rank function is very commonly used in the industry for many purposes.

The general syntax of rank function is

RANK ( )  OVER ( [  partition by < column_list > ]  order by  <column_list> )

The PARTITION BY clause divides the result set produced by the FROM clause into separate partitions. Rank is applied to each of these partion.

The ORDER BY  clause determines the order in which the RANK values are applied to the records in a partition.

Read more…

 In SQL server, creating a database is as simple as counting 1..2…3…

All you need to do is

1. open SQL server management studio
2. Expand your server node.
3. Right click on databases and choose the option new databases.

 

4. In general tab – Specify the name of the database and the path of data and log files

Read more…

Another major milestone for Microsoft SQL Server. The SQL server 2012 RCO is now available for you to use.

You can download it from Microsoft Download Center. So goahead, what are you waiting for.

Several new features are now introduced and lot more are removed.  keep checking this blog for updates on the new features.

Bulk insert task is used to copy large amount of data into SQL Server tables from text files. For example, imagine a data analyst in your organization provides a feed from a mainframe system to you in the form of a text file and you need to import this into a SQL server table. The easiest way to accomplish this is in SSIS package is through the bulk insert task.

Configuring Bulk Insert Task

Drag the bulk insert task from the toolbox into the control flow window.

Read more…

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…

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…

Query to track all the indexes with fragmentation in a database.

SELECT 

OBJECT_NAME(object_id) AS TablelName

,(SELECT name FROM sys.indexes WHERE object_id = a.object_id and index_id = a.index_id) IndexName

,avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(‘<<DatabaseName>>’), NULL, NULL, NULL, NULL) a

WHERE avg_fragmentation_in_percent > 40

AND index_type_desc IN(‘CLUSTERED INDEX’, ‘NONCLUSTERED INDEX’)

ORDER BY avg_fragmentation_in_percent DESC

The <<DatabaseName>> needs to be filled with the database of your choice.

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…

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…

 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…