May 27, 2014

SQL Server - Row_Number() vs Rank() vs Dense_Rank()

ROW_NUMBER(), RANK() AND DENSE_RANK() are the ranking functions which helps to assign the value for every row. These function are not only useful for assign the value but it also helps in solving complex queries such as getting top row of each value of the column/table then deleting or updating it, deleting or updating duplicate value etc.

ROW_NUMBER() helps to assign new row number for every row, regardless of duplicates within a partition.

RANK() also helps to assign new row number but it assigns for every distinct row, leaving gaps between groups of duplicates within a partition.

DENSE_RANK() is same as a rank() but it assigns new row number for every distinct row, leaving no gaps between groups of duplicates within a partition.

Syntax:

ROW_NUMBER() OVER (ORDER BY CLAUSE)
ROW_NUMBER() OVER (PARTITION BY CLAUSE ORDER BY CLAUSE)

RANK() OVER (ORDER BY CLAUSE)
RANK() OVER (PARTITION BY CLAUSE ORDER BY CLAUSE)

DENSE_RANK() OVER (ORDER BY CLAUSE)
DENSE_RANK() OVER (PARTITION BY CLAUSE ORDER BY CLAUSE)


For Example:

DECLARE @RANKORDER TABLE
      (ID INT,
       FIRSTRANK INT,
       SECONDRANK INT)

INSERT INTO @RANKORDER VALUES (1, 1, 1)
INSERT INTO @RANKORDER VALUES (2, 2, 1)
INSERT INTO @RANKORDER VALUES (3, 2, 2)
INSERT INTO @RANKORDER VALUES (4, 3, 2)
INSERT INTO @RANKORDER VALUES (5, 3, 3)

SELECT *,
ROW_NUMBER() OVER (ORDER BY SECONDRANK DESC) AS ROWNUMBER,
ROW_NUMBER() OVER (PARTITION BY FIRSTRANK ORDER BY SECONDRANK DESC) AS ROWNUMBERPARTITION,
RANK() OVER (ORDER BY SECONDRANK DESC) AS RANK,
RANK() OVER (PARTITION BY FIRSTRANK ORDER BY SECONDRANK DESC) AS RANKPARTITION,
DENSE_RANK() OVER (ORDER BY SECONDRANK DESC) AS DENSERANK,
DENSE_RANK() OVER (PARTITION BY FIRSTRANK ORDER BY SECONDRANK DESC) AS DENSERANKPARTITION
FROM @RANKORDER
ORDER BY SECONDRANK DESC


The above examples shows ROWNUMBER column returns sequential number to each row based on the sorting order where as the ROWNUMBERPARTITION column returns the values based on the partition or grouped by FIRSTRANK.

Similarly, RANK column also returns sequential number to each row but if duplicate value is found then it assigns same rank number and leaves the gaps between the group of duplicates.

DENSERANK returns value same as RANK does but it fills the gaps. 

May 23, 2014

SQL Server Management Studio - Save change is not permitted

When we try to make change in the existing table and save it in SQL Server 2008 Management Studio, we may get this error:

"Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."


This problem occurs because the Prevent saving changes that require the table re-creation option is enabled in the SQL Server Management Studio which can be found in the Designer section of SQL Server Management Studio Tools - Options. This option is enabled so that user won't be able to make any structural change through SSMS that require table re-creation such as

  • Adding a new column
  • Reordering the columns in the table
  • Changing the datatype of a column
  • Changing the Allow Nulls setting of a column


To get rid of this problem, you need to Uncheck/Turn off Prevent saving changes that require table re-creation but keep in mind that after you turn off this option, the existing change tracking information is completely deleted when the table is re-created.

To ensure change tracking information is enabled in the table you need to go to Change Tracking item which can be found in the table properties of the table and check whether the value is True or false. If the value is false, you need to make it true.

May 19, 2014

Introduction to MongoDB

MongoDB is an open source non-relational JSON store. It does not store data in relational tables, it is schema less.

MongoDB is document oriented and has dynamic schema. It does not support joins and does not have transaction against multiple document.

JSON (Java Script Object Notation) is the way we express data inside MongoDB.

JSON (Java Notation Object Notation):

JSON is light-weight data-interchange format. There are two basic data structure inside JSON:
1)  Array - Lists of things
2) Dictionaries - Associated Maps

These can be combined in arbitrary ways. Array inside JSON is represented inside square brackets and list of items in between. E.g; [...]. Whereas Dictionaries are represented inside curly brackets and its a bunch of key and value pairs. E.g; {keyword:value}. The arbitrary number separated by commas.

For more: http://json.org/

The JSON document is composed of field and value pairs. The value may consists of other documents, arrays or arrays of documents. There will be unique identifier called ObjectID.

For Example:

{Name: "Avesh", Sex: "Male", Group: ["Developer", "Analyst"]}

MongoDB provides high performance, high availability and automatic scaling.

High Performance:

MongoDB provides high performance data persistence. Supports for embedded data models reduces I/O activity on database system. Indexes support faster queries and can include keys from embedded documents and arrays.

High Availability:

MongoDB provides high availability with the help of MongoDB's replication facility, called replica sets. This replica sets provides automatic failoverand data redundancy.

Replica set is agroup of MongoDB servers that maintain the same data set, providing redundancy and increasing data availability.

Automatic Scaling:

MongoDB provides horizontal scalability as a part of its core functionality such as automatic sharding distributes data across a cluster of machines and replica sets can provide eventually-consistent reads for low-latency high throughput deployments.


The latest version of MongoDB can be found on:

http://www.mongodb.org/downloads

Source:
http://docs.mongodb.org/manual/core/introduction/