The Database, one we create during application development is OLTP database.
It means our main point of interest is Transactions. We create database keeping maintenance more in mind than reporting.
What exactly mean by this?
It means, we normalize data so that there will not be any data repletion and thus data will be more efficient and correct. We connect our front applications created using .net, java or any other technology and perform day to day transactions.
In this kind of systems (OLTP systems) Data management becomes easier because not data will be repeating only references will be there.
What’s the problem?
Problem is, in such systems data retrieval becomes difficult. There will be many number of joins required in order to fetch the complete record.
What’s the solution?
This is where Data Warehouse comes to picture. As the name implies our data will be retrieved from our normalized database and stored in data warehouse in denormalized manner. Usually data warehouse is also a database where data will be stored in denormalized manner. Because data is stored in denormalized manner select query works faster here.
Is that the only difference?
No, there is one more. In data warehouse data from more than one data source (OLTP databases) will be collected and stored.
For technical trainings on various topics like WCF, MVC, Business Intelligence, Design Patterns, WPF, TFS and Basic fundamentals feel free to contact SukeshMarla@Gmail.com or visit www.sukesh-marla.com
Also see another SQL Server interview questions video on RowNumber, Partition, Rank and DenseRank:-