Getting started with SQL Server Query Tuning

April 21, 2020 by Esat Erkec

This article will cover some essential techniques for SQL query tuning. Query tuning is a very wide topic to talk about, but some essential techniques never change in order to tune queries in SQL Server. Particularly, it is a difficult issue for those who are a newbie to SQL query tuning or who are thinking about starting it. So, this article will be a good starting point for them. Also, other readers can refresh their knowledge with this article. In the next parts of this article, we will mention these techniques that help to tune queries.

An overview of ETL and ELT architecture

April 21, 2020 by Aveek Das

This article explains what the basic features and differences between ETL and ELT are. I’m also going to explain in detail what an ELT pipeline is and a relevant architecture for the same in Azure. So far, we have come a long way dealing with ETL tools which basically are Extract, Transformation and Load technique used in populating a data warehouse. ELT, on the other hand, is another way to load data into a warehouse that implements the process of Extract, Load and Transform.

Importance of SQL Server Max Degree of Parallelism

April 21, 2020 by Jignesh Raiyani

In this article, we will discuss how the Max Degree of Parallelism works in SQL Server and how does it improve the query performance. SQL Server Degree of Parallelism is the processor conveyance parameter for a SQL Server operation, and it chooses the maximum number of execution distribution with the parallel use of different logical CPUs for the SQL Server request. Microsoft SQL Server allows setting this Degree of Parallelism parameter value at the SQL Server instance level or Query level. If you do not specify the SQL Server Degree of Parallelism value at the SQL Server instance, then each request or operation has to rely on SQL Server default value and random CPU allocations.

Learn SQL: SQL Server date and time functions

April 21, 2020 by Emil Drkusic

So far, we haven’t talked about SQL Server date and time functions. Today we’ll change that. We’ll take a close look at the ones most frequently used and mention all other date and time functions as well. This will be also the first step to create reports, including date and time functions. We’ll do that in upcoming articles in this series.

Calculate Moving Averages in SQL, Power BI and MS Excel

April 21, 2020 by Aveek Das

In this article, I’m going to talk in detail about the moving average in SQL and how to find out the same in Power BI and Excel. This might be a new topic for many newbies who have started out in this field, but I’m sure this will help anyone who is trying to work on smoothing averages in SQL or in Power BI.

Learn SQL: Create a report manually using SQL queries

April 16, 2020 by Emil Drkusic

In the previous two articles, we’ve practiced SQL queries and went through a few more examples. Today we’ll use SQL queries and create a report from scratch. We’ll start from the simplest possible query and finish with the query returning the actual report data.

Configuring RDS environment in AWS for SQL Server

April 16, 2020 by Aveek Das

In this article, I’m going to explain how to configure an RDS Environment in Amazon Web Services (AWS) for SQL Server. Amazon RDS, also known as the Amazon Relational Database Service, is a Database-as-a-Service offered by Amazon. This enables us to create an instance of a relational database in RDS including many databases like SQL Server, MySQL, PostgreSQL etc. The entire hosting is managed by AWS, so there is no worry to maintain any on-premises data center or infrastructure from the customer’s end.

Introduction to Row-Level Security in Power BI

April 15, 2020 by Aveek Das

Analyzing data with Power BI has always been fascinating to me. With ever-growing data comes more risk, and hence more security is needed. In this article, I am going to introduce what row-level security in Power BI is and how does it work. I’ll also demonstrate in detail how to implement the same in Power BI Desktop. Later, we will also discuss how to implement this row-level security in Power BI service as well. To learn about row-level security in SQL Server, please have a look at my previous article, where I have explained how to implement the same.

Enterprise Data Masking in MySQL

April 15, 2020 by Nisarg Upadhyay

In this article, I am going to explain Data Masking feature in MySQL. The data masking is the MySQL enterprise edition feature. You can download the trial version of MySQL enterprise edition from here. Data Masking is a process of hiding confidential data like credit card information, SSNs, addresses etc. to safeguard sensitive data.

SQL Server Hardware Performance Tuning

April 14, 2020 by Jignesh Raiyani

SQL Server Performance Tuning can be a difficult assignment, especially when working with a massive database where even the minor change can raise a significant impact on the existing query performance. Performance Tuning always plays a vital role in database performance as well as product performance. A query can be optimized with the proper evaluation of a piece of code or SQL Statements. The important thing in Query Optimization is to understand, which process or expression needs evaluation in the piece of code. Everyone expects to get a quick response from the query on the production, but How?

Implement Artificial Neural Networks (ANNs) in SQL Server

April 14, 2020 by Dinesh Asanka

In this article, we will be discussing Microsoft Neural Network in SQL Server. This is the seventh article of our SQL Server Data mining techniques series. Naïve Bayes, Decision Trees, Time Series, Association Rules, Clustering, and Linear Regression are the other techniques that we discussed until this article.

Pagination in SQL Server

April 14, 2020 by Esat Erkec

Pagination is a process that is used to divide a large data into smaller discrete pages, and this process is also known as paging. Pagination is commonly used by web applications and can be seen on Google. When we search for something on Google, it shows the results on the separated page; this is the main idea of the pagination.

SQL Server Update Statistics using database maintenance plans

April 13, 2020 by Rajendra Gupta

This article explores SQL Server Update Statistics using the database maintenance plan.

Using the Merge Join Transformation in SSIS packages

April 10, 2020 by Rajendra Gupta

This article explores the Merge Join Transformation in SSIS packages.

Scheduling SQL Notebooks in Azure Data Studio

April 10, 2020 by Rajendra Gupta

SQL Notebooks are an interactive way of creating documents, executing T-SQL queries along with Python and PowerShell codes. It is available in Azure Data Studio, a cross-platform client tool that works on both Windows and Linux operating systems.

Fuzzy Lookup Transformations in SSIS

April 10, 2020 by Dinesh Asanka

This article helps you to understand the usage of the Fuzzy Lookup Transformation in SQL Server Integration Services (SSIS).

An overview of Chart Types in Power BI

April 9, 2020 by Aveek Das

In this article, I am going to give an overview of the most widely used chart types in Power BI. Power BI, as everyone might be aware, is one of the SaaS offerings from Microsoft in the field of BI and Analytics. It provides a very intuitive way for non-technical users to do analyses and create reports on their own without having to rely on a technical team. One of the main purposes of Microsoft behind Power BI is to promote self-service BI and it is one of the BI tools in the market with a comparatively lower learning curve as there are many similarities compared to Microsoft Excel.

How to deploy SSAS cubes

April 9, 2020 by Aveek Das

In this article, I’m going to discuss the different ways in which we can deploy SSAS cubes. SSAS or SQL Server Analysis Services is an enterprise-grade analytical data engine which helps business to create semantic models that can be used for reporting and making better decisions. The semantic layer created by SSAS can be accessed by client tools like Power BI, or Excel and reports can be prepared from thereon. SSAS comes with a few different versions, which are as follows.

Understanding the deadlock definition in SQL Server

April 7, 2020 by Esat Erkec

This article explains the deadlock definition in SQL Server, and it also mentions how to capture deadlocks with extended events.

How to update the T-SQL Toolbox database

April 6, 2020 by Frank Solomon

Introduction

In an earlier article, Solve Time Zone, GMT, and UTC problems using the T-SQL Toolbox database, I described T-SQL Toolbox, a free, open-source SQL Server database that handles time zone, date, and time calculations in a clean and efficient way. Available here at the CodePlex Archive, and here at GitLab, T-SQL Toolbox relies on time zone and time zone adjustment data in its two tables for many of its own calculations. However, T-SQL Toolbox does not update that data. I built primitive C-Sharp and VB.net applications that extract the latest time zone and time zone adjustment data from the Windows registry, and I discussed those applications in that earlier SQL Shack article. In the article, I explained that we can build SQL Server UPDATE statements with this extracted data, and then update the T-SQL Toolbox tables. This article describes a better approach and a better solution.

Start your journey with Azure Cosmos DB

April 6, 2020 by Ahmad Yaseen

In this article, we will discuss why we need to use Azure Cosmos DB and how to configure it to store and query our data.

Table-Valued Parameters in SQL Server

April 6, 2020 by Esat Erkec

Table-Valued Parameters aka TVPs are commonly used to pass a table as a parameter into stored procedures or functions. They are helpful in a way, we can use a table as an input to these routines and we can get rid of dealing more complex steps to achieve this process.

Analyze coronavirus data with Power BI Desktop visualizations

April 6, 2020 by Rajendra Gupta

This article gives you an overview of representing coronavirus spread using Power BI Desktop visualizations.

Working with images in Power BI Desktop reports

April 6, 2020 by Rajendra Gupta

Introduction

In the article Web URL configuration in a Power BI Desktop report, we explored a different method to use Web URL in a Power BI report. Images help to improve the visual appearance of a report. You can convey messages easily using these images in a report.

Warnings in SSAS Cubes

April 3, 2020 by Aveek Das

In this article, I’m going to list out some of the most common warnings we encounter while developing SSAS Cubes and the reason for those warnings. As you are aware, SSAS cubes are developed using the SQL Server Data Tools (SSDT) for Analysis Services (Visual Studio), all the examples will be taken from Visual Studio 2017 only.