Here are three T-SQL functions that you can use to extract the month from a date in SQL Server.
Continue readingCategory: DBMS
Database Management Systems
MongoDB $anyElementTrue
In MongoDB, the $anyElementTrue aggregation pipeline operator evaluates an array as a set and returns true if any of the elements are true.
If none of the elements is true, then it returns false.
An array’s element is true if it’s not false, null, 0, or undefined.
MongoDB $allElementsTrue
In MongoDB, the $allElementsTrue aggregation pipeline operator evaluates an array as a set and returns true if no element in the array is false.
If the array contains an element that is false, then $allElementsTrue returns false.
An array’s element is true if it’s not false, null, 0, or undefined.
5 Ways to Insert Documents in MongoDB
MongoDB provides many ways to insert documents into a collection.
Here are 5 ways to insert documents into a collection when using the mongo shell.
Continue readingGet the Underlying Columns of a View Based on its Result Set
One of the great things about database views, is that they allow you to run complex queries without needing to know the underlying database schema.
Yes, it’s true that you need to know the underlying schema when you create the view, but you only need to do that once. Once you’ve created it, you can query that view all day long without needing to remember all the table and column names, etc.
Views typically combine data from multiple tables into a single, virtual table, which makes it kind of like a “black box”. As long as it works as designed, you don’t need to concern yourself with the hidden details.
But what if you do want to check a view for its underlying tables and columns?
Continue readingOverride the Query Optimizer for your T-SQL Joins with FORCEPLAN
The SET FORCEPLAN statement overrides the logic used by the SQL Server query optimizer to process a T-SQL SELECT statement.
More specifically, when FORCEPLAN is set to ON, the query optimizer processes a join in the same order as the tables appear in the FROM clause of a query.
This also forces the use of a nested loop join unless other types of joins are required to construct a plan for the query, or they are requested with join hints or query hints.
Continue readingRename a Field in a MongoDB Collection
In MongoDB, you can rename a field when updating documents in a collection.
To rename a field, call the $rename operator with the current name of the field and the new name. This renames the field in all matching documents that have a field with that name.
Import CSV Data as an Array in MongoDB using mongoimport
MongoDB’s import utility – mongoimport – introduced a new parameter that allows you to import CSV data as an array.
The --useArrayIndexFields parameter interprets natural numbers in fields as array indexes when importing CSV or TSV files.
2 Ways to Replace a Substring in MongoDB
MongoDB 4.4 introduced nine new aggregation pipeline operators, including two new operators for finding and replacing a substring.
The two new operators that allow you to find and replace a substring are the $replaceOne and $replaceAll operators.
About the GO Command in SQL Server
If you’ve used T-SQL you will probably be familiar with the GO command. This command is often placed at the end of a batch of statements.