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


Junk dimension 4

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


Generate serial number for each consecutive set of numbers 2

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 then in 2010 and 2011, we have to assign 1 for 2005, 2 for 2006, 3 for 2007 followed by 1 for 2010 and 2 for 2011. We need to generate incremental series for each consecutive set of years but as and when a gap occurs between donation years, series has to be restarted with 1.

PARTITION BY clause in a ROW_NUMBER() window function could be able to generate the required output, if we had same number along with donation years for each consecutive set of years for all employees. Initially, we … 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, if length is not specified in their argument.

I would strongly suggest to define the length of CHAR, NCHAR, VARCHAR and NVARCHAR data types as per the need. In this post, I just explain the behavior of CHAR, NCHAR, VARCHAR and NVARCHAR data types, if length is omitted, and not encouraging to omit the size of these data types.

We can define these data types as below:

char [ ( n ) ]
nchar [ ( n ) ]
varchar [ ( n | max ) ]
nvarchar [ More


Insert into table with one identity column only 2

One of my friend asked me a question that “How we can insert values in a table with only one column which is identity, without using IDENTITY_INSERT ON?”. In this post “Insert into a table with one identity column only”, I am going to share a nice method to achieve this.

We know that using “SET IDENTITY_INSERT TableName ON” command, we can easily insert rows in an identity column. As per MSDN, we need to keep few things in mind related to “IDENTITY_INSERT ON”:

  1. Only one table can have IDENTITY_INSERT as ON in one session at a time. We must have to use “SET IDENTITY_INSERT TableName OFF” to use IDENTITY_INSERT on other tables.
  2. To execute IDENTITY_INSERT on a table, a user must own the table or has ALTER permission on the table. Only below types of user can execute IDENTITY_INSERT on a table:
    1. A member of sysadmin fixed server role
More

Delete all rows from all tables 2

Have you ever come across the problem to delete all rows from all tables available in a database? One of my colleague asked me this question that how can we delete all rows from all tables of a database. So, in this post, we are going to discuss, how we can delete all rows from all tables of a SQL Server database.

To delete all rows from a table named ProductCategory, we can simply use “DELETE FROM ProductCategory” command. But in case the table is being referred by a foreign key constraints from some tables and / or fires a trigger to insert few rows in an another table in the database, we must have to delete all related rows from child tables before we start deleting parent table rows. We also need to delete records from those table which are getting inserted rows as a result of a … More


Mutable logical condition in CASE expression 6

In our previous blog post Different data types in case expression, we have explored the behavior of CASE expression in case of different data types in “THEN” and “ELSE” part. Now in this post “Mutable logical condition in CASE expression”, we are going to unfold the behavior of CASE expression when we have a non deterministic function in logical expression (WHEN part).

Mutable logical condition in CASE expression

Have a look on the below query:

--Query 1
SELECT
CASE ABS(CHECKSUM(NEWID())) % 3
WHEN 0 THEN 'Case 0'
WHEN 1 THEN 'Case 1'
WHEN 2 THEN 'Case 2'
END

 

In above query, logical expression “ABS(CHECKSUM(NEWID())) % 3” of CASE expression, has a non deterministic function NEWID() wrapped inside a CHECKSUM function to generate some random number on fly. Going further the CHECKSUM value has also wrapped inside a ABS function which insures a positive number. Finally there is a … More


Different data types in case expression 5

In this blog post titled as “Different data types in case expression”, we will explore the behavior of CASE expression when dealing with different data types in THEN part (true) and ELSE part (false) of CASE expression. We will limit the scope of discussion of this post to data conversion issue only. If you are interested to explore more about the CASE expression, visit this MSDN link.

What is the Return type of CASE expression

As per MSDN, CASE expression returns the highest precedence type from the given set of types in THEN and ELSE part (true and false part). To explore data type precedence, you can visit Data Type Precedence link here.

Lets play with a simple demo to see this return type in action. This demo is for test and may look a bit silly. In below SQL query, we have two variables, @RetValThen as DATETIME … More