2015

Get hierarchical data in order

In this post, we are going to learn how we can get hierarchical data in order. We are dealing with a user defined hierarchical data which can have “n” number of siblings and their siblings can also have “n” number of siblings in turn. For demo purpose, we have a Product_Master table, in which a product can be marked as a parent […]

Get hierarchical data in order Read More »

Role playing dimension

We have explored Junk dimension and Degenerate dimension in previous posts, now we are going to discuss Role playing dimension in this post. Lets start with the definition followed by an example. Role playing dimension A fact table keeps the facts of a business process. It is built of two types of columns: measure columns and dimension key columns. Measure are the quantitative business data

Role playing dimension Read More »

Degenerate dimension

We have discussed the use of junk dimensions in data modelling and in this post we are going to explore Degenerate dimension. Lets start with the definition. Degenerate Dimension A high cardinality attribute column in the fact table which does not have any other content except its natural key and is required as a dimension for analysis or

Degenerate dimension Read More »

Create, Alter and Drop – Database and Table

Now that you are familiar with database concepts and SQL Server Management Studio, it is time to begin our journey with T-SQL programming. In my opinion, to shine as a good T-SQL programmer, one must have a good understanding of T-SQL concepts and its capability. In this chapter “Create, Alter and Drop – Database and Table”, we are

Create, Alter and Drop – Database and Table Read More »

Junk dimension

What is a Junk Dimension? Have you ever come across a scenario where many small dimension tables connect (using foreign key) to the fact table? In data warehouse, we have two types of tables, Fact and Dimension. Fact table contains business facts as measures and references to the dimension tables. Dimension tables have attributes which contains textual information of business facts and

Junk dimension Read More »

Exploring SQL Server Management Studio

In this SQL Server Tutorial, we have gone through DBMS, RDBMS and SQL Server, Table Relationships, Data Integrity, Types of keys and Database normalization chapters. Now that we have a good understanding of database concepts, it is time to put our feet in T-SQL. Before we begin our journey with T-SQL (MS SQL Server programming language) we need to be familiar with SQL Server Management Studio, also

Exploring SQL Server Management Studio Read More »

Generate serial number for each consecutive set of numbers

In this post, we are going to generate serial number for each consecutive set of numbers in a given column. We have a table tbl_Donation with columns EmpId and DonationYear. For each consecutive set of years of each employee, we need to generate serial numbers. If employee “X” donated in 2006, 2007 and 2008 years consecutively and

Generate serial number for each consecutive set of numbers Read More »

Database normalization

In previous chapter, “Types of keys“, we had a good discussion on keys and their types in SQL Server. In this chapter “Database normalization”, we are going to discuss database normalization process and normal forms. Lets start our discussion with definition of Normalization. Normalization Normalization is a process of minimizing redundant data from database by decomposing the columns of a table

Database normalization Read More »

CHAR, NCHAR, VARCHAR and NVARCHAR default length

What is the default length of CHAR, NCHAR, VARCHAR and NVARCHAR when we don’t specify their length in their length argument? If we omit the size of CHAR, NCHAR, VARCHAR and NVARCHAR, how they behave? In this post “CHAR, NCHAR, VARCHAR and NVARCHAR default length”, we are going to discuss the default length of CHAR, NCHAR, VARCHAR and NVARCHAR data types,

CHAR, NCHAR, VARCHAR and NVARCHAR default length Read More »