Author : Gopal Krishna Ranjan


Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 6+ years of industry experience in Software development using Microsoft technologies. I have a head down experience in Database development, performance tuning in SQL Server, T-SQL optimization, BI (Business Intelligence) project implementation, reporting in SSRS, using SSIS for ETL, implementing multi dimensional and tabular data-warehouse in SSAS, querying cubes using MDX and DAX, Windows and Web Applications development with C#.


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


Table Relationships

We have already been familiar with “DBMS, RDBMS and SQL Server” in previous chapter. In this chapter named “Table Relationship”, we are going to understand the relationship between tables. In relational database management systems, tables are used to store every information in the form of columns and rows. Before moving to the topic “Table Relationship”, we would have a look on the definition of Database, Table, Column and Row, as these entities participate in relationships.

Database

Database is an organized collection of data for fast processing on these data. These data are arranged in such form that enables fast retrieval of information and manipulation on these data. In RDBMSs, database organizes its data in relational form only using tables, column and rows. We can define relationships between these tables.

Table

A table is a collection of related data in a structured form using columns and rows within a database. … More


DBMS, RDBMS and SQL Server

Lets start this tutorial with the chapter “DBMS, RDBMS and SQL Server”. Before starting T-SQL part of the SQL Server tutorial, we will learn basic concepts of database systems. In this chapter, we will explore “Database management systems” and its relational extension “Relational database management systems”. We will also have an introduction to Microsoft SQL Server at the end of the chapter. This chapter has necessary images to explain the concepts in pictured way which will help you to understand the topics with ease.

Database management system (DBMS)

Database management system is a software used to define, record, query, update and manage data in a database. DBMS stores data in a structured way (relational, hierarchical, flat files, objects etc), and executes operations requested by various users on these data. It interacts with user, receives commands, and runs it against the database. DBMS can interact with some other applications, if required. … More


SQL Server Tutorial – Prerequisite

To start with this tutorial, you need to download and install SQL Server (Latest version recommended) from Microsoft and to do some hands-on throughout this tutorial and for assignments purposes, you need to download and attach AdventureWorksDatabase also. Below you have some links, which might help you out in these prerequisites.

Prerequisite

Just two things for this “SQL Server Tutorial” and you all set to go with this tutorial.

  1. Install SQL Server – [Recommended – SQL Server 2014 or 2012]
  2. Download and attach “AdventureWorksDatabase”

If you don’t have installed SQL Server yet, no worries, here are the links which may help you.

To download SQL Server 2014;

https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2014

For Step by Step installation of SQL Server 2014

http://social.technet.microsoft.com/wiki/contents/articles/23878.installing-sql-server-2014-step-by-step-tutorial.aspx

To download AdventureWorks2014 Database

https://msftdbprodsamples.codeplex.com/releases/view/125550

How to restore “AdventureWorks2014” Database

Download the MS Word file from below link and follow the instructions mentioned in “Install Adventure Works 2014 OLTP database from More


SQL Server Tutorial – About

SQL Server Tutorial – Step by Step

About this tutorial

You are welcome at my first tutorial, titled as “SQL Server Tutorial“. If I am not wrong, it seems that you are keen to learn SQL Server database programming. This “SQL Server Tutorial” is specially written for novice programmers. It starts with simple Database concepts and takes a deep dive into T-SQL. You just have to follow this tutorial to learn the skill. Well organised chapters in easy to understand language, complemented with good assignments to work on, should help you acquire the knowledge swiftly.

This SQL Server Tutorial written for Microsoft SQL Server database programming and has its primary focus in T-SQL (Transact SQL, Microsoft proprietary extension to SQL). I have worked a lot to make the content easy to understand, yet impressive. You will get a lot of real life examples, images to understand the concepts … 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