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.
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.
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.
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.
Don’t have an account? Create one, its simple
Recent Comments