Relational database management systems and Non-Relational database management systems are fighting with each other in the world of data. MongoDB is one of the most popular non-relational databases that is trying to solve the scalability issue. MS-SQL is a popular relational database management system that uses T-SQL to solve many issues. This article explains the technical difference between MongoDB and MS-SQL.
In the MS-SQL, data is stored in tables while in MongoDB the data is stored in the collection. The difference between MongoDB and MSSQL is that SQL Server enforces a schema rule while MongoDB doesn’t follow the schema rule. The MSSQL manages relations between the different data. In MSSQL, data will not be saved in the database if users don’t follow rules or constraints such as a foreign key or primary key. Table joins can be applied efficiently to explore or query the data.
The tables are vertically scalable and storing and retrieving are relatively more passive when data size is enormous. In collections, no relation is managed between the data. The collection allows users to collect the document of any data type. The SQL tables are vertically scalable while the MongoDB collections are horizontally scalable. This can be accomplished by adding more servers. The collection allows faster storing and retrieving of data. The constraints such as foreign key support are available but it is not much used because then you have to maintain the relationships.
CREATE TABLE sales(
cust_id INT PRIMARY KEY IDENTITY (1, 1),
cust_name VARCHAR (50) NOT NULL,
purchase_date DATETIME,
phno VARCHAR(20),
item_no INT NOT NULL,
FOREIGN KEY (item_no) REFERENCES sales.purch (item_no)
);
> use mydb
switched to db mydb
db.mydb.insert({
name: "Jeff",
age: 65,
website: "abc.com"
})
In MongoDB, a stored procedure is actually a JavaScript function. The condition is that this JS function should be stored in a particular collection named db.system.js. In MSSQL, the stored procedure is a saved SQL code that can be reused or called anytime in your program. It is used to group one more T-SQL statements. In MSSQL, the stored procedures are stored as named objects. In MongoDB, you can store javascript in the proper system.js collection. It can work as a stored procedure but this is executed by using javascript. The application of the SystemJS class is rather irrelevant. The most complicated part is preparing the argument moving right between the Python lambda passed by *getattr* and the call of the stored javascript procedure.
In MSSQL, by collecting or by grouping various SQL statements, you can call or execute a particular stored procedure perform certain database operations. For example, if you want to update the salary of 3000 employees of department no. 40, then in such cases the update command can be used in the stored procedure to modify the salary of all employees. This decreases the application of sluggish networks, decreases network transactions, and updates round-trip reply period.
> db.system.js.save
(
{
_id:"additionoftwonumbers",
value:function(x,y)
{
return x+y
}
}
);
CREATE PROCEDURE MyProce (@PID INT) AS BEGIN SET NOCOUNT ON SELECT P.ID,P.ItemName,P.ItemDetails FROM Sales P INNER JOIN ItemDetails PD ON P.ItemID=PD.ItemID WHERE P.ItemID=@PID END
The drop is one of the most used methods in a non-relational database. If you want to drop a collection in MongoDB, db. collection. drop() method can be used. This method completely eliminates a collection. In MSSQL, the drop command is used to eliminate the table from the database.
>use mysampledb switched to db mysampledb >show collections mytestcol mytestcollection system.indexes > >db.mytestcollection.drop() true >
DROP TABLE AdventureWorks2012.dbo.Sales;
The above drop command can also be used to drop any particular column of a table in MSSQL.
A trigger is a particular stored procedure that works when an event happens. It can be used to fire a certain kind of code after or before an event in the database. For example, the DML triggers can be fired when a user wants to change or update data by using the data manipulation language (DML) event. In DML events, users can use INSERT, UPDATE, or DELETE statements. The triggers are available only in a relational database management system. In MongoDB, the triggers are not available.
CREATE TRIGGER sample_item_audit
ON sales.products
AFTER INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO sales.product(
item_id,
item_name,
itembrand_id,
category,
model,
group_price,
updated_at,
work
)
SELECT
i.item_id,
item_name,
brand,
category,
model,
i.group_price,
GETDATE(),
'INS'
FROM
inserted i
UNION ALL
SELECT
d.item_id,
item_name,
brand,
category,
model,
d.group_price,
GETDATE(),
'DEL'
FROM
deleted d;
END
{
_id: ObjectId("59cf444"),
customerId: ObjectId("59cf444"),
orderDate: ISODate("2020-06-12T18:30:32.411Z"),
shipDate: ISODate("2020-06-14T09:30:33.221Z"),
orderItems: [
{ typ: 1, name: "PQR Bags - 1000ct", price: NumberDecimal("50.89") }
],
shippingLocation: [
{ location: "New York", time: ISODate("2020-06-14T19:30:37.217Z") },
]
}
Backup is the most important utility of any relational or non-relational database. By using T-SQL, users can take the backup of the databases in MSSQL. This is a simple process and you only require a few commands to do this. MongoDB also provides the backup utility. In both, the relational and non-relational databases the backup and restore utility are available. As you increase the volume of data that you save, you consequently enhance the probability of need to recover data. Data is a necessary means and any company that is incompetent to recover important data within a short period will find it difficult to last.
BACKUP DATABASE TestDB TO DISK = 'C:\TestDB.BAK' WITH DIFFERENTIAL GO
To take a backup of the database in MongoDB, the mongodump command can be used. This command is used to dump the whole data of the server into the dump directory. This mongodump command can also be used with various options.
In MongoDB, projection is choosing only the required data or information rather than choosing the complete data from the document. By default, queries in MongoDB give complete data or one can say all fields in the documents. To restrict or check the number of records or data from the collection, users can always add a projection document in the query process.
For example, if a document has 10 fields and you want only 5 fields, then select only 5 fields from them. In MSSQL, the “where” clause can be used to filter the data from the table. For example, if you want to select such employees who have a salary more than 5000 then where clause can be used to filter the T-SQL query. The where clause is one of the most important clauses in RDBMS.
>db.mycol.find({},{"title":1,_id:0})
{"title":"My File"}
{"title":"My Table”}
{"title":"Hello World"}
>
SELECT
item_id,
item_name,
category,
model,
group_price
FROM
sales.items
WHERE
category = 2
ORDER BY
group_price DESC;
An index in MongoDB is a specific data structure that contains the data of fields of documents on which the index is built. Indexes increase the activity of search services in the database. In MSSQL and MongoDB, Indexes are responsible for speeding up the search query by giving fast access. Indexes in the relational database management system or non-relational database management system are not always relevant. Indexes are great at reducing the time it requires to search and obtain data using the SELECT statements. Sometimes index can really decrease the query performance to UPDATE, INSERT, or DELETE.
>db.users.createIndex({"tags":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 5,
"numIndexesAfter" : 6,
"ok" : 1
}
>
CREATE INDEX idx_client_city ON employee.clients(city);
About the Author
ByteScout Team of Writers
ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.
Go is an open-source, statically typed, compiled (Go's run-time does not include a virtual machine), garbage-collected computer language from the C family. It is primarily...
A common table expression (CTE) is a collection of query outputs. Such query outputs are collected from an uncomplicated query defined within a WITH clause...