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…
For proper analyzing of data, sometimes we need to group them under certain categories. These categories are defined under some constraints over the data. For this GROUP BY Clause is very useful.
GROUP BY Clause is used with SELECT Command, and specifies the groups into which output rows are to be placed by the values of one or more columns or expressions. And if aggregated functions are used in the SELECT statements, then GROUP BY clause computes a summary value for each group.
Read more…
As the tempdb database is the common global resource for all the operations going on in SQL Server, so the DBA has to be bit cautious about the use of it. Because any unexpected operations by the applications running under the SQL Server instance or any uneven query by the user can eat all the space available to thetempdb resulting decrease in the performance of all other applications running under the same instance.
So it is necessary to keep track the usage of tempdb database by various applications and processes and to take necessary actions when the size falls down below the threshold limit. Monitoring tempdb over time will help in determining the optimal size of the tempdb.
Read more…
Recent Comments