Author : Gopal Krishna Ranjan


Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 8 years of industry experience in Software development. I have a head down experience in Database, Data Warehouse, Big Data and cloud technologies and have implemented end to end Database, Data Warehouse,  Big Data and Cloud Solutions.
I have extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). I also have good experience in windows and web application development using ASP.Net and C#.


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 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


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


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


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 into multiple tables. This process increases the number of tables in the database but it minimizes the redundant (duplicate), inaccurate and inconsistent data in database. Decomposed tables are connected using relationships (primary keys and foreign keys) to reduce the insert, update and delete anomalies. During normalization process, database designer decides the actual structure of the tables and their relationships. It is easy to find and fix any design problem at this early stage. During normalization process, we have various normal forms with some conditions. A table … 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


Types of keys

In previous chapter, we had a good discussion on “Data Integrity” and in this chapter “Types of Keys”, we are going to discuss Keys and their types in SQL Server. Lets start this topic by defining keys”.

What is Key?

Keys are fields in a table which participate in below activities in RDBMS systems:

  1. To create relationships between two tables.
  2. To maintain uniqueness in a table.
  3. To keep consistent and valid data in database.
  4. Might help in fast data retrieval by facilitating indexes on column(s).

SQL Server supports various types of keys, which are listed below:

  1. Candidate Key
  2. Primary Key
  3. Unique Key
  4. Alternate Key
  5. Composite Key
  6. Super Key
  7. Foreign Key

Before discussing each type in brief, have a look on the below image used as an an example to define types of keys.

Types of keys

Types of keys

Lets discuss each type in detail:

Candidate Key

Candidate key is a … 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


Data Integrity

After having a good discussion on “Table Relationships” in previous chapter, we are going to discuss Data Integrity in this chapter. First, we would discuss the term Data Integrity, followed by a discussion on data integrity types, and finally, we would go through an image which summarizes the data integrity concepts in one sight view. Lets start the discussion with the definition first.

Data Integrity

In relational database management systems, data integrity ensures the accuracy, reliability and consistency of the data during any operation like as data manipulation, data retrieval, data storage, backup or restore operation etc. It also guarantees that the recording of data is accurate and as intended. Having any bad or unintended data in the database, is a failure of data integrity. For example, having sales detail in Sales table of a product not available in Product master is a failure of referential integrity.

Below … More