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.

How to compare two SQL databases from Visual Studio

April 3, 2020 by Nemanja Popovic

Visual Studio is an application that is used by many developers and it has different kinds of features that can facilitate the developer’s work. Among these features, Visual Studio offers users the feature to compare two SQL databases via its Schema compare feature.

Dynamic Pivot Tables in SQL Server

April 2, 2020 by Aveek Das

In this article, I am going to explain how we can create a dynamic pivot table in SQL Server. Pivot tables are a piece of summarized information that is generated from a large underlying dataset. It is generally used to report on specific dimensions from the vast datasets. Essentially, the user can convert rows into columns. This gives the users the ability to transpose columns from a SQL Server table easily and create reports as per the requirements.

An overview of the DTExec utility in SSIS

April 2, 2020 by Hadi Fadlallah

DTExec is a tool used to configure and execute SQL Server Integration Services (SSIS) packages. Many developers and database administrators use this tool, especially when automating the package execution using third-party applications or non-supported programming languages.

Understanding Dynamic Data Masking in SQL Server

April 2, 2020 by Aveek Das

In this article, I’m going to explain the Dynamic Data Masking feature in SQL Server. As it goes by the name, Dynamic Data Masking is one of the security features that was introduced in SQL Server 2016. In an ever-growing digital era, the security of ones’ data has become one of the most important and expensive deals. Everyone expects their data to be protected as their own asset and like to treat it with the highest efficiency possible.

Accessing Azure Blob Storage from Azure Databricks

April 2, 2020 by Gauri Mahajan

We introduced Azure Databricks, how it is created and some of its important components in my previous article here. We will look at how we can work with Azure Blob Storage in Azure Databricks in this article.

Learn SQL: SQL Query examples

March 31, 2020 by Emil Drkusic

In the previous article we’ve practiced SQL, and today, we’ll continue with a few more SQL examples. The goal of this article is to start with a fairly simple query and move towards more complex queries. We’ll examine queries you could need at the job interview, but also some you would need in real-life situations. So, buckle up, we’re taking off!

Scroller visual for Stock price movements in Power BI Desktop

March 31, 2020 by Rajendra Gupta

In the article, Candlestick chart for stock data analysis in Power BI Desktop, we explored Power BI Desktop to analyze stock prices. If you follow the stock market, you might have noticed a ticker (similar to the following image) showcasing stock prices and changes since the last close price. It shows an up and down indicator depending upon the positive or negative change in the stock price.

Introduction to Row-Level Security in SQL Server

March 31, 2020 by Aveek Das

In this article, I’m going to discuss Row-Level Security in SQL Server. RLS or Row-Level Security as the name suggests is a security mechanism that restricts the records from a SQL Server table based on the authorization context of the current user that is logged in. This means the records from the tables are displayed based on who the user is and to which records do the user has access to. This is usually done to allow specific users to have access to their data only without permission to view other users’ data.

Overview of Solutions and Projects in SSMS

March 30, 2020 by Rajendra Gupta

DBAs and developers manage a broad set of queries, codes, applications. It is a good practice to organize code or t-SQL scripts so that we can easily access them without wasting time searching for them. You might use Visual Studio, GitHub source control for it. In this article, we look at how Solutions and Projects help to organize T-SQL scripts.

Working with SQL Server ROWCOUNT

March 30, 2020 by Ahmad Yaseen

In this article, we will discuss the SQL Server @@ROWCOUNT and ROWCOUNT system objects by going through practical examples that cover most of these system objects usage.

How to use a Web data source in Power BI Desktop reports

March 30, 2020 by Rajendra Gupta

This article explores various ways to use a Web data source in Power BI Desktop reports.

Learn to write basic SQL Queries

March 27, 2020 by Esat Erkec

Essentially, SQL language allows us to retrieve and manipulate data on the data tables. In this article, we will understand and gain the ability to write fundamental SQL queries. At first, we will take a glance at the main notions that we need to know about in order to write database queries.

INSERT INTO T-SQL Statement in SQL Server

March 27, 2020 by Ahmad Yaseen

In this article, we will go deeply through the INSERT INTO statement by showing the different syntax formats and usage scenarios for that statement.

Introduction to Many-to-Many Relationships in SSAS

March 26, 2020 by Aveek Das

In this article, I’m going to explain what many-to-many relationships in SSAS are and how to implement the same in a SQL Server Analysis Services (SSAS) project. For the sake of this article, we will only consider the Multidimensional Cube and not the Tabular. Also, this article assumes that you have some fair knowledge of building SSAS cubes from scratch.

Biml alternatives: Building SSIS packages programmatically using EzAPI

March 26, 2020 by Hadi Fadlallah

In the previously published article, Biml alternatives: Building SSIS packages programmatically using ManagedDTS, we talked about building SSIS packages using the managed object model of the SSIS engine (ManagedDTS). In this article, we will illustrate another Biml alternative, which is the EzApi class library, and we will make a comparison between both technologies.

Learn SQL: Practice SQL Queries

March 25, 2020 by Emil Drkusic

Today is the day for SQL practice #1. In this series, so far, we’ve covered most important SQL commands (CREATE DATABASE & CREATE TABLE, INSERT, SELECT) and some concepts (primary key, foreign key) and theory (stored procedures, user-defined functions, views). Now it’s time to discuss some interesting SQL queries.

Biml alternatives: Building SSIS packages programmatically using ManagedDTS

March 25, 2020 by Hadi Fadlallah

In the previously published articles in this series, we have explained how to use Biml to create and manage SQL Server Integration Services (SSIS) packages. In this article, we will talk about the first alternative of this markup language which is the Integration Services managed object model (ManagedDTS and related assemblies) provided by Microsoft.

In this article, we will first illustrate how to create, save and execute SSIS packages using ManagedDTS in C#, then we will do a small comparison with Biml.

Removing duplicates in an Excel sheet using Python scripts

March 24, 2020 by Rajendra Gupta

In the article, Python scripts to format data in Microsoft Excel, we used Python scripts for creating an excel and do various data formatting. Python is an interesting high-level programming language. You can go through various use cases of Python on SQLShack.

Microsoft Linear Regression in SQL Server

March 24, 2020 by Dinesh Asanka

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