Skip to main content

Posts

Showing posts from July, 2016

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.
Syntax:

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])

Result:

{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…

$not operator in MongoDB

Before explaining $not operator I want to remind you about NOT IN in SQL Server. In T-SQL query we used NOT operator like below. <SELECT QUERY> <WHERE ID> NOT IN (<a sub query>) Same like this $not operator behave in mongodb.
$not performs a logical NOT operation on the specified <operator-expression> and selects the documents that do not match the <operator-expression>. This includes documents that do not contain the field.
Syntax:

Syntax: { field: { $not: { <operator-expression> } } }

Example: Suppose we have a collection of books inventory like below.

{ "_id" : 1, "item" : "item1", "qty" : 1, "Price" : 500 } { "_id" : 2, "item" : "item1", "qty" : 2, "Price" : 200 } { "_id" : 3, "item" : "item1", "qty" : 4, "Price" : 300 } { "_id" : 4, "item" : "item1", "qty&…

Text Index in MongoDB

Text index supports to text search queries on string content. We can any add fields which value is string or an array of string elements. Text index is introduced in mongodb version 3 but some key features added in mongodb version 3.2, the new features are:
1-Case sensitive: Previous versions of the text index are case insensitive for [A-z] only; i.e. case insensitive for non-diacritics Latin characters only. For all other characters, earlier versions of the text index treat them as distinct.
2-Diacritic Insensitivity: There are no facility in index to distinguish between characters that contain diacritic marks and non marks corresponding item for example: é, ê, and e.
3-Tokenization Delimiters: In previous version text index uses the delimiters categorized under dash and hyphen. For example: if given a string "Il a dit qu'il «était le meilleur joueur du monde»", the text index treats «, », and spaces as delimiters.
Create text index: Suppose we have a collection “Inventory …

$and operator in MongoDB

$and: This operator works same as AND in SQL Server. $and operator uses to perform logical operation on two or more than two arrays and its select the documents that satisfy to all expression.
Syntax:

{ $and: [ { <expression1> }, { <expression2> }, ... , { <expressionN> } ] }

Example: Suppose we have a collection “Order” and want to select those records which quantity is 2 and price is 200. See following collection.

{ "_id" : 1, "item" : "item1", "qty" : 1, "Price" : 500 } { "_id" : 2, "item" : "item1", "qty" : 2, "Price" : 200 } { "_id" : 3, "item" : "item1", "qty" : 4, "Price" : 300 } { "_id" : 4, "item" : "item1", "qty" : 8, "Price" : 700 } { "_id" : 5, "item" : "item1", "qty" : 2, "Price" : 500 }

Ru…

$or operator in MongoDB

$or: If you aware with SQL Server, $or operator behave same like OR in SQL Server. $or operator uses to perform logical OR operation on two or more than two arrays and its select the documents that satisfy at least one of the expression.
Syntax:

{ $or: [ { <expression1> }, { <expression2> }, ... , { <expressionN> } ] }

Example: Suppose we have a collection “Order” and want to select those records which quantity is 1or price is 200. See following collection.

{ "_id" : 1, "item" : "item1", "qty" : 1, "Price" : 500 } { "_id" : 2, "item" : "item1", "qty" : 2, "Price" : 200 } { "_id" : 3, "item" : "item1", "qty" : 4, "Price" : 300 } { "_id" : 4, "item" : "item1", "qty" : 8, "Price" : 700 } { "_id" : 5, "item" : "item1", "qty…

Drop and Disable trigger using query in SQL Server

Drop Trigger: We can drop trigger using following query.
Example:

USE[DB_NAME] GO DROPTRIGGERTGGR_NAME GO

Disable Trigger: You can disable/enable trigger using following query
Example:


--Disable trigger USE[DB_NAME] GO DISABLETRIGGERTGGR_NAMEONTBL_NAME
--Enable trigger USE[DB_NAME] GO ENABLETRIGGERTGGR_NAMEONTBL_NAME

Case statement in SQL Server

SQL Server gives us facility to fetch or modify records on condition basis. Suppose we have an “Order” table and we need to fetch records with conditions if user order price is less than 200 Rs it means user is “silver”, if price is greater than 200Rs and less than 500Rs it means user is Gold same like this if price is greater than 500Rs it means user is “Platinum” .
We can easily solve this problem using SQL Server.
Example:

CREATETABLE#Order( IDINTIDENTITY(1,1)PRIMARYKEY, USR_NAMEVARCHAR(100), SKUVARCHAR(100), QTYINT, PRICEDECIMAL(18,2), CREATED_DATEDATETIME )
INSERTINTO#Order(USR_NAME,SKU,QTY,PRICE,CREATED_DATE) SELECT'User1','9876543210',2,200,GETDATE() UNIONALL SELECT'User2','9876543210',2,300,GETDATE() UNIONALL SELECT'User3','9876543210',2,400,GETDATE() UNIONALL SELECT'User4'

Find all stored procedures related to table in database in SQL Server

There are many different ways to find all stored procedures related to specific table, some are described below.
Using sp_depends: This is the system stored procedure which can use to find all users defined stored procedures related to specific table.

USE[DB_NAME] GO
EXECsp_depends@objname=N'TBL_NAME'; GO

Note: sp_depends does not always return accurate results.
See following script this will help you to find stored procedures related to specific table.

----Script 1 SELECTDISTINCTso.name FROMsyscommentssc INNERJOINsysobjectssoONsc.id=so.id WHEREsc.TEXTLIKE'%tablename%'
----Script 2 SELECTDISTINCTo.name,o.xtype FROMsyscommentsc INNERJOINsysobjectsoONc.id=o.id

$in and $nin operator in MongoDB

$in operator is responsible to select those records where value of the fields is equal to the any elements specified in the array.
Same as $nin operator is responsible to select those records where value of field is not equal to the any elements specified in the array.
Syntax:

{ field: { $in: [<value1>, <value2>, ... <valueN> ] } }


{ field: { $nin: [ <value1>, <value2> ... <valueN> ]} }

Example: Suppose we have a collection “order” and we have to select those records which quantity is 2, 4(we will use here $in operator) same as if we want to select those records which quantity is not equals to 2, 4 (we will use here $nin operator).

{ "_id" : 1, "item" : "item1", "qty" : 1, "Price" : 500 } { "_id" : 2, "item" : "item1", "qty" : 2, "Price" : 200 } { "_id" : 3, "item" : "item1", "qty" : 4, "Price"…