Tuesday, May 7, 2013

[SQL Server Data Warehousing] Data warehouse


No, your approach is not right.


SQL Server is RDBMS (Realational database managment system) and it should have keys (natural primary keys and surrogate keys for DWH model). Data warehouse model is more simple then normalized OLTP model, and it is often called dimensional model. It can be denormalized to snowflaked or star schema to gain better join performance for reports - BUT IT SHOULD ALLWAYS have keys defined.


Problem that arrives if table has no primary (clustered index) key defined is with working of sql server. Without keys defined MSSQL stores data internaly as heap structure, as oposite of B-Tree structure when you have clustered index defined.Heap is pretty simple structure and in this perspective you should know that SQL Server always do full table scan on heap structures (so to find any row MSSQL usualy read whole table), and this is not desirable in most scenarios.


You should post your DDL at least if you are expecting some constructive and better help, please do that


Thnx



if (helpful) then Vote();



.

social.technet.microsoft.com/Forums

No comments:

Post a Comment

Search This Blog