Sunday, March 13, 2016

Comparison Sql Server and MongoDB for a database developer

Comparison Sql Server and MongoDB for a database developer


This article is all about the comparison between SQL Server(Relational database) to MongoDB(No-Sql database).

This morning just visited mongodb.com found mongo announced mongo 3.2. After going through their master manual for mongo 3.2 which is published on dated 11 March 2016, Friday. I just compared stuff which will help you to understand mongoDB operation quickly. MongoDB is not difficult for a relation database developer.


The following tables presents the various SQL terminology and concepts and the corresponding MongoDB terminology and concepts.

Terms Comparison

SQL Terms
Mongo Terms
Brief.
Database
Database
Organized collection of information
Table
Collection
Representation of row columns
Row
Document

Columns
Fields

Index
Index
Created to speed up the execution
Table joins
Embedded document & linking
Interlinked information
Primary key
Primary key
Quickly identify a record set/query result
Sql Terms, functions
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT
SUM()
COUNT()
join
Aggregation operators
$match
$group
$match
$project
$sort
$limit
$sum
$sum
$lookup
Matching ,grouping ,choosing, aggregate information to provide require result set/query result





















Schema Comparison

SQL Schema
Mongo Schema
Brief.

CREATE TABLE table1(
  id int not null Identity(1,1) Primary key,
  Name varchar(50),
  Shift char(1),
  Experience int
)

db.createCollection("table1")

db.table1.insert( {
Name: "Sunit Kanyan",
Shift: “G”
Experience: 5
} )



db.createCollection will create empty collection. Insert implicity create the collection.
The primary key _id will automated added in mongo.

Alter Table table1 add join_date DATETIME


db.table1.update(
 { }
{ $set: { join_date: new Date() } {  multi: true }
 )


Collection does not enforce structure of documents so there is no structure alteration at collection level. Update can add new fields in existing documents.

Alter Table table1 DROP COLUMN join_date


db.table1.update(
 { }
{ $unset: { join_date: “”  }
{  multi: true }
 )


Collection does not enforce structure of documents.
Update can remove a field by unset operator.

CREATE INDEX idx_table1_experience
ON table1(Experience)


db.table1.createIndex(
{ Experience: 1 } )


DROP Table table1

db.table1.drop()

















Insert Statement Comparison

SQL Insert Statements
Mongo Insert Statements
Brief.
Single Row Insert:-

INSERT INTO table1
 (Name
 ,Shift
 ,Experience)
VALUES 
('SunitKanyan'           ,'G'
,5)


INSERT INTO table1
VALUES       
('Sunit Kanyan'           ,'G'
,5)





db.table1.insert(
{ Name: “SunitKanyan”,
 Shift: “G”, Experience: 5 }
)






db.table1.insertOne(
{ Name: “Sunit Kanyan”,
 Shift: “G”, Experience: 5 }
)
 

Inserting document/record
Multiple Row Insert:-

INSERT INTO table1
 (Name
 ,Shift
 ,Experience)
VALUES 
('Sunit Kanyan'           ,'G'
,5),
('Sumit Ror'           ,'G'
,4),
('Sunit Ror'           ,'G'
,6),






db.table1.insertMany([
{ Name: “Sunit Kanyan”,
 Shift: “G”, Experience: 5 },
{ Name: “Sumit Ror”,
 Shift: “G”, Experience: 4 },
{ Name: “Sunit Ror”,
 Shift: “G”, Experience: 6 }
])


Inserting documents/records


















Update Statement Comparison

SQL Update Statements
Mongo Update Statements
Brief.
Single Row Update:-

Update table1
Set
Shift='E'          
Where           Experience =4



db.users.Update(
 { Experience: { $eq: 4} },
{ $set: { Shift: “E”,  }}
)


db.users.UpdateOne(
 { Experience: { $gt: 4} },
{ $set: { Shift: “E”,  }}
)

 

updating document/record
Multiple Row Update:-

Update table1
Set
Shift='E'          
Where           Experience > 4



db.users.UpdateMany(
 { Experience: { $gt: 4} },
{ $set: { Shift: “E”,  }}
)




Updating  documents/records











Select/Querying Statement Comparison

SQL Querying  Statements
Mongo Querying  Statements
Brief.
SELECT * FROM table1

db.table1.find()
Retrieve all record
SELECT id,Name,Shift,Experience FROM table1

db.table1.find(
{ },
{ Name:1, Shift:1, Experience:1)
Specified fields/columns
SELECT Name,Shift,Experience FROM table1

db.table1.find(
{ },
{  Name: 1, Shift: 1, Experience: 1, _id: 0 })
Specified fields/columns
SELECT * FROM table1
WHERE  Shift = 'E'          

db.table1.find(
{  Shift:  "E" })
Where clause
SELECT Name,Experience FROM table1
WHERE  Shift = 'E'          

db.table1.find(
{  Shift:  "E" },
{  Name: 1, Experience: 1 })
Where clause
SELECT *  FROM table1
WHERE  Shift = 'E'          

db.table1.find(
{  Shift:  {  $qe: "E" } } )
equal
SELECT *  FROM table1
WHERE  Shift != 'E'  
And   Experience = 5

db.table1.find(
{  Shift:  {  $ne: "E" } ,  Experience: 5} )
Not equal
SELECT *  FROM table1
WHERE  Shift = 'E'  
And   Experience = 5

db.table1.find(
{  Shift:  {  $eq: "E" } ,  Experience: 5} )
And  condition
SELECT *  FROM table1
WHERE  Shift = 'E'  
OR  Experience = 5

db.table1.find(
{  $or:  [Shift:  {  $qe: "E" } ,  
Experience: 5}
]  }
  )
Or condition
SELECT *  FROM table1
WHERE Experience > 4

db.table1.find(
Experience: { $gt: 4}} )
Greater than
SELECT *  FROM table1
WHERE Experience < 4

db.table1.find(
Experience: { $lt: 4}} )
Less than
SELECT *  FROM table1
WHERE Experience > 4
And   Experience <= 10

db.table1.find(
Experience: { $gt: 4, $lte: 10 } } )
And less/greater than
SELECT *  FROM table1
WHERE Name LIKE '%Sunit%'

db.table1.find(
Name: /Sunit/} )
Like
SELECT *  FROM table1
WHERE Experience in (3,4,5,6)
db. table1.find(  { Experience:  { $in:  [ 3,4,5,6 ] } } )
In
SELECT TOP 1 *  FROM table1

Db.table1.findOne()
Top
SELECT *  FROM table1 Order by Name

Db.table1.find().sort( { Name: 1})
Sort
SELECT DISTINCT Name FROM table1

db.table1.distinct(: "Name")
Distinct


























Delete Statement Comparison

SQL Delete  Statements
Mongo Delete  Statements
Brief
Delete FROM table1

db.table1.remove( { } )
All delete
Delete FROM table1
WHERE Experience = 4
db.table1.remove( { Experience: 4 } )
Conditional remove
Delete TOP (1) from table1  WHERE Experience = 4
db.table1.remove( { Experience: 4 } , true)
Top one delete











--
Sunit