Languages and coding

Rajendra Gupta
difference between RANK() and DENSE_RANK() functions

Overview of SQL RANK functions

July 3, 2019 by

We perform calculations on data using various aggregated functions such as Max, Min, and AVG. We get a single output row using these functions. SQL Sever provides SQL RANK functions to specify rank for individual fields as per the categorizations. It returns an aggregated value for each participating row. SQL RANK functions also knows as Window Functions.

Read more »
Sifiso Ndlovu
Data imported using Custom SQL Statement.

Monitor batch statements of the Get Data feature in Power BI using SQL Server extended events

July 1, 2019 by

One shared characteristic among popular data visualization tools such as Power BI is the ease at which data can be extracted from a variety of disparate data sources, usually at a click of a button, as shown in Figure 1. Such convenience, though, tends to come at a cost as you often have little control over how background scripts used to extract data are generated. Yet, this should be of utmost concern for data architects and BI developers alike as rarely do you find auto-generated scripts that are efficient and optimal. In this article, join me as I put on my DBA hat and trace, monitor and review SQL batch statements that are auto-generated by the Get Data feature in Power BI using SQL Server Extended Events.

Read more »
Rajendra Gupta

Overview of SQL Server Rounding Functions – SQL Round, Ceiling and Floor

June 26, 2019 by

Developers deal with numerous data types on a day- to-day basis. We need to change the data type or format as per the user requirement. We use ‘SQL Server rounding function’ like SQL Round, Ceiling and Floor to round the values to the nearest numbers. We perform an arithmetic calculation on data as well. It is a challenging task to change the value of a number to an approximate number. We do not want to display decimal numbers in the application front end.

Read more »
Rajendra Gupta
Get-DbaTable DBATools command

Script SQL Server objects using DBATools

June 20, 2019 by

This article gives an overview to generate scripts for SQL Server objects with Windows PowerShell tool DBATools.

Database administrators or developers require generating scripts for SQL Server objects. We might need scripts to store a copy of object script before object change, create specific objects into other database environments such as development, UAT or non-prod environment. It is an excellent practice to keep a copy of the object before making a change to it. We can easily refer to the old script and roll back if required. Usually, we use SSMS Generate Scripts wizard to get these scripts.

Read more »
Rajendra Gupta
insert sample data

SQL Between Operator overview and examples

June 11, 2019 by

We extract data from SQL Server tables along with various conditions. Usually, we have data in large amounts and SQL Between operator helps to extract a specific range of data from this huge data. For example, suppose we want to know the product sales between Jan 2019 to May 2019. In this case, we can use this operator in a SQL query.

Read more »
Sifiso Ndlovu
Successful Execution of SSIS components

Bulk-Model Migration in SQL Server Master Data Services

May 30, 2019 by

Just over a year ago, I published How to migrate SQL Server 2017 Master Data Services Models into another server article, that essentially demonstrated the migration of a SQL Server Master Data Services (MDS) model from one MDS instance to another. Like many of my previously published articles, I have since used the aforementioned article as a technical reference (i.e. syntax lookup for using MDSModelDeploy.exe utility) during the implementation of MDS-related projects for my clients.

Read more »
Timothy Smith
In Settings under our container in our Azure Cosmos DB, we see the options that we’ll use for our template

Creating Containers with PowerShell For Azure Cosmos DB

May 30, 2019 by

In many situations, we will develop, test or prove new concepts by horizontally scaling new SQL API containers in Azure Cosmos DB over possibly using existing containers. As we’ve seen in previous tips, we can create and remove Cosmos database accounts and databases by using the Azure Portal or PowerShell’s Az module along with making some updates to the configuration, such as the RUs for performance reasons. Similarly, we can create and remove a container through the Azure Portal along with creating and removing the container with PowerShell’s Az module.

Read more »
Timothy Smith
We see the same restrictions in the Azure Portal

Increasing or Decreasing Scale for Azure Cosmos DB

May 28, 2019 by

Now that we can create and remove an Azure Cosmos DB and databases that we can use for automation purposes, along with obtaining some information about these accounts, we’ll look at making some changes to these accounts for contexts related to performance. It’s possible that our unit and security testing or development with proof-of-concepts may face performance problems where we need to upgrade the settings of our database account. In this tip, we’ll be working with the SQL API database layer in a Cosmos database account by building on our get, create and remove automation to update its performance.

Read more »
Timothy Smith
The Azure Portal view of our SQL API database in our Azure Cosmos DB

Creating and Removing Databases with PowerShell In Azure Cosmos DB

May 27, 2019 by

Our testing or development may call for dynamic creation on the database level for Azure Cosmos DB rather than the account level. As we’ve seen with dynamically working with a Cosmos database account using PowerShell, we can create, remove, and obtain properties of the account. Identically, we can do this on the database level as well and we may use this in testing if we need the same Cosmos database account for other testing purposes. Development situations may also involve use cases where we want to test a concept and dynamically create a database within our Cosmos database account. In this tip, we’ll look at working with our Azure Cosmos database account on the database object level where we do nothing to manipulate the account itself, only add databases to the account once it’s been setup.

Read more »