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.
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.
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.
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.
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.
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.
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().
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.
Problem: I have a table for customer information with Customer Ids and Customer Type. The customer may fall into different types i.e. the customer may belong to Type A or Type B or may belong to both the groups. The requirement is to find out the count of the customers as per the type in each group and if the customer falls under more than one group, it must not be counted under the individual group rather it should be counted under the group with both the types.
Here are some ways described to find out the total no. of days in a given month.
Don’t have an account? Create one, its simple
Recent Comments