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…
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…
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.
Read more…
Here are some ways described to find out the total no. of days in a given month.
Read more…
Optimizing Indexes in SQL Server: Although there are many bright faces of indexes but every coin have two sides. So it’s always advisable to implement indexes wisely on a table. Here we will check out different perspectives to view indexes on a given table.
Read more…
Types of indexes: We have basically two types of indexes in SQL Server.
- Clustered index: It’s a type of index where the data itself is arranged at the indexed column i.e. we have the actual data at the leaf nodes. For example, in a science definition pocket book, every word is arranged alphabetically and the definition of the word is given along with it. Here there is no need to look up for the referencing page, as in case of other example like in a general book with indexes at the end of the book, contains page number along with the term or word which is then followed to get the information about the term.
Read more…
Indexing has become a critical buzzword in the arena of databases. Not only database users, but also all of us are using indexes in our daily life. Got puzzled? Let me give you one scenario. Have you ever noticed how did a librarian arranges and orders all the books in the library? And when you are asking for a particular book, he immediately gives it to you within some couple of minutes without searching the whole library. This is possible because he has already indexed each book and categorized them according to the author and the publisher and again ordered all the books falling under same author and publisher in an alphabetical order of the title. So when you ask for a particular book, he just refers to the shelf belonging to the author and publisher of the book and the book is with you in no time.
Read more…
SQL Server 2005 has some unique features to deal with the Transaction system in the database world. It has some unique sets to take care of every possibility of transactions or types of transaction. Technically, it will give us discrete ways to isolate the transactions from occurrence of deadlocks or crashes.
Before going deeper to the Isolation level that SQL Server provides to distinguish types of transaction, let’s have a look on the definition of the TRANSACTION. What does transaction means in real world and in a database scenario?
Read more…
Because of some unorganized OLTP (transactional system), sometimes it may occur that duplicate records get entered in the OLTP database. It is the most common thing in case of legacy database and bad database. For a better output in analysis service or in reporting, the data must be cleaned, scrubbed and must be flawless. And it becomes worse when the records are alike even for values in each column.
Before taking any step to clean the redundant data, you must first see and check for data redundancy.
Read more…
Recent Comments