We looked at a few of the data warehouse concepts before in Microsoft Technologies. First of all, you need to look at the infrastructure planning for a data warehouse. During the data warehouse design, it is important to include surrogate keys to dimension tables. Date dimensions is a special dimension that is used in data warehouse modelling. Historical data is an important aspect in a data warehouse that is used in Slowly Changing Dimensions (SCD).
If you are involving in Data Models, it handy to use a modelling tool. If you are a SQL Server favoured permission, you might be using Database diagram option in the SQL Server. However, there are tailor-made tools for data modelling such as erwin Data Modeler, ER/Studio or Draw.IO.
This article brings you 19 different tools that can be used. Draw.IO is one of the preferred tools as it is free can be shared between users/designers much easier. Nowadays, it is not only one person needs to worry about the design but many other stakeholders.
in Draw.IO you can save your models at any places as below.
There are two myths around BIT column indexes.
Myth #1. Cannot create indexes on BIT columns.
Not sure how this was evolved. But this not absolutely false. Let us try it.
USE tempdb
GO
CREATE TABLE Employee
(ID INT IDENTITY PRIMARY KEY CLUSTERED,
Name Varchar(50),
Status BIT
)
CREATE INDEX idx_employee_status ON Employee(Status)
Above statement is successful and you can create the index is created.
I verified this in SQL Server 2000 SP4 and it was successful.
Myth #2. BIT Column indexes are not useful.
Above argument has a base. BIT column can have possible three values, 0, 1 and NULL. Therefore index selectivity is low. For example, consider the above table which has 10 million rows and status will say whether these are active or not. In large table, active records will be very less so active = 0X1 will be highly selective.
