Monthly Archives: July 2015


SELECT Statement

Now that we have been familiar with Create, Alter and Drop – Database and Table, it is time to be familiar with T-SQL statements. Let’s start this chapter with the definition of T-SQL followed by a detailed discussion on T-SQL’s SELECT statement.

Transact SQL (T-SQL)

Transact SQL (T-SQL) is a proprietary extension of SQL (structured Query Language) by Microsoft and Sybase to communicate with Microsoft SQL Server. T-SQL has support for procedural programming using control flow (if-else, Case), loop (while), cursor, recursion and local variable, etc. It has many inbuilt functions to support string operation, date and time processing, mathematical calculation, aggregate processing and many more.

In short, T-SQL is capable to deal all kinds of SQL Server requirements like; creating database, adding tables, creating relationship, constraints, managing users and roles, creating a backup, restore database etc.

T-SQL Statements

T-SQL Statements are the basic constructs of T-SQL Programming … More


Get hierarchical data in order 2

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 of another product, and that child product can also be marked as a parent of another product, such kind of hierarchy is known as user defined hierarchy. As any product can be marked as another product’s parent, we can have any number of nested products in the hierarchy. We have to retrieve all these products with their associated parent in ordered way.

Ordered hierarchical data from above user defined hierarchy, can be achieved with a recursive common table expression (CTE) or using a loop. We … 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 about the business process and dimension keys reference to the dimension tables which hold the textual attributes of the business process.

Typically a dimension key column of a fact table refers a dimension table but its also very common where multiple columns of a fact table refer to a single dimension table. When a single dimension table is linked from multiple dimension key columns of a fact table, that dimension table is known as role playing dimension. As multiple columns of a fact table … 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 drill-down purpose, is called a degenerate dimension. As this degenerate dimension is constructed from a fact table item and is placed in the fact table, it is also known as fact dimension. It helps to reduce duplicate data by placing high cardinality dimension key in the fact table.

Degenerate dimension keys are significantly unique and are nearly as large as fact table. Moving this attribute in a single column dimension table requires a considerably large dimension table, and you would also need to join the surrogate key of the fact … 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 going to learn, how we can create, alter and drop a database and table. We would also explore how to add columns to an existing table, change column definition and delete column from the table.

Below is the abstract of this chapter:

  1. {CREATE | ALTER | DROP} DATABASE – Used to create / alter / delete a database respectively.
  2. {CREATE | ALTER | DROP} TABLE – Used to add / modify / remove table in a database respectively.

 

1 {CREATE | ALTER | DROP} DATABASE STATEMENT

1.1 CREATE DATABASE

We … More


Junk dimension 2

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 are used to filter and label the data.

Typically, fact tables are deeper whereas dimension tables are wider. Sometimes it happens that we have many narrow and low cardinality (low cardinality : few number of rows in the table) dimensions in the system; like flags and indicators. To keep all these information in fact table, we need to connect all these dimension tables (available in system or created from flag and indicator attributes) with fact table. Connecting all these small dimension tables converts fact table in … More


Exploring SQL Server Management Studio

In this SQL Server Tutorial, we have gone through DBMS, RDBMS and SQL ServerTable RelationshipsData IntegrityTypes 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 known as SSMS, an application software developed by Microsoft, to manage, configure and administer Microsoft SQL Server and all its components like Database Engine, Analysis services, Integration Services, Reporting Services etc. SQL Server Management Studio provides a centralized management platform for all kind of SQL related work. We can create a database, add tables, create relationships, constraints, functions, procedures, views, triggers etc. We can also define authorization on objects like who is authorized on which object and for what.

Lets … More