Archive

Author Archive

For each database – sp_MSForEachDB

August 20th, 2010 Arun Mallick 1 comment

Sp_MSForEachDB is a great procedure coming with MS SQL Server even though it is un-documented. It eases some of the complex operations that we mostly do by using CURSORS.

Read more…

Categories: T - SQL, Tips N Tricks

Common Table Expressions (CTE)

August 15th, 2010 Arun Mallick 6 comments

CTEs are one of the beautiful and the most powerful feature of SQL Server. It is introduced in SQL Server 2005 and I guess is one of the best things in SQL Server. This not only simplifies most of the complex and impossible queries in T-SQL, but also provides one medium to deal with recursive queries. Moreover, I can say it is mainly for recursive queries. It can be used in replacement of derived tables (sub queries), temp tables, table variables, inline user-defined functions etc.

Read more…

Categories: T - SQL

Story of Business Analyst

August 8th, 2010 Arun Mallick 1 comment

Business Analyst as per Wikipedia, “A Business Analyst (BA) analyzes the organization and design of businesses, government departments, and non-profit organizations; they also assess business models and their integration with technology.” and so much that really bounce over my head. Although it’s very true whatever is written over there as a professionalism of a Business Analyst.

But today I would like to tell you something really very interesting about the work of a Business Analyst.  I worked with a team of completely experienced and talented people who, to be honest, knew a lot more than I did as per business perspective. I learned a lot and luckily I had some really great people to learn from.  A business analyst is someone who always takes care of the clients, their problems, their requirements, who delights clients with the trust that whatever he will suggest to them is more than good what the client expects.

Read more…

Categories: BI

Database Design and Modeling I

August 2nd, 2010 Arun Mallick 8 comments

Throughout my experience I come across so many database designing problems. And today I want to share some of the common influences and impacts of most common designing mistakes that bring big disaster to the whole project.

One of the most common designing mistakes is the “Poor Planning / Architecture” of the database or mart.

Read more…

Categories: BI

NTile

July 25th, 2010 Arun Mallick 2 comments

NTILE(): This is one of the most coolest function provided by T-SQL. This is very useful while grouping a dataset into multiple sets i.e. in case if you want to distribute the result set into more than one tables or destination, this function is useful in accomplishing this task.

Read more…

Categories: T - SQL

DENSE_RANK

July 21st, 2010 Arun Mallick 1 comment

DENSE_RANK(): This is almost similar to RANK() function except, it will assign rank continuously i.e. it will always return consecutive integers. It will assign rank to the records as per the condition used in <order by> clause for a partition without any gaps in the ranking values. The ranking values will be same as of RANK() i.e. if the <order by> condition satisfies for more than one record then it will assign the same rank to each record that satisfies the same condition.

Read more…

Categories: T - SQL

Rank

July 21st, 2010 Arun Mallick 3 comments

RANK (): For each partition, it will assign the rank to the records as per the condition used in <order by> clause. If the <order by> condition satisfies for more than one record then it will assign the same rank to each record that satisfies the same condition.

Read more…

Categories: T - SQL

Row_Number

July 18th, 2010 Arun Mallick No comments

ROWNUM(): For each partition, it will return the sequence number starting from 1. It is very useful in case you want any functionality similar to identity column on the fly. As IDENTITY() will work only in case of select into statement i.e. when you are inserting records into some table and not in simple select statement.

Read more…

Categories: T - SQL

T-SQL RowNumber() vs Rank() vs DenseRank() vs NTILE()

July 15th, 2010 Arun Mallick No comments

How if we found some things in life that we want to be done so smoothly, so perfect without any flaw or fracas that something like a wizard or some magic that makes things simpler and fine.

SQL Server comes with some magic words like the above phrase that makes simple the life of a developer or DBA to a great extent. And these magic words are RowNumber(), Rank(), DenseRank() and NTile().

Read more…

Categories: T - SQL

Nth Highest Salary Query

July 13th, 2010 Arun Mallick 5 comments


Problem: Show the list of all employees from the employee table having second highest Salary or Nth highest salary in the organization.

Solution: Well we can achieve the result by so many ways but in this post I would like to do it by using co-related queries.  Co-related queries are very interesting stuff as it gives you option to process data row wise. But at the same time it has a big drawback. Row wise processing causes bottle neck for the performance of the query.

Read more…

Categories: T - SQL