In this article, we are going to explore Analysis Service for AWS RDS SQL Server in detail.
Read more »Lever T-SQL for Pinpoint Control of ORDER BY in a Stored Procedure
June 1, 2020Introduction
The T-SQL ORDER BY clause sorts SQL Server SELECT statement result sets, and it becomes important when we build stored procedures. Unfortunately, the syntax offers no flexible way to directly control the ORDER BY clause behavior with argument values. This means we don’t have an easy way to control the specific column or columns that the ORDER BY clause sorts. Additionally, SQL Server does not offer a flexible way to directly control the ascending or descending order of any ORDER BY clause column with argument values. Of course, we can certainly hard-code the ORDER BY clause in a stored procedure, but this approach becomes fixed in stone. We could try a dynamic SQL solution, involving a stored procedure code that dynamically builds and executes SQL Server statements inside a stored procedure. However, this technique becomes tricky, and it can lead to SQL injection attacks. Other techniques might rely on CASE statements, and their complexity can become overwhelming as the column count grows. This article spotlights a clean, efficient, pinpoint T-SQL stored procedure technique that directly sorts one, some, or all SELECT statement result set columns. The technique avoids dynamic SQL, and it operates directly in a stored procedure. The article also shows how to set the ascending or descending sort order of specific columns.
Read more »Explore the DATABASEPROPERTYEX() function for SQL Server databases
May 28, 2020In this article, we explore a SQL function to extract SQL database metadata using the DATABASEPROPERTYEX function.
Read more »Getting Started with the SQLPackage Utility
May 28, 2020In this article, I’m going to explain in detail about the SQLPackage utility that is provided by Microsoft in order to automate database deployments. In my previous article regarding Data-Tier Applications in SQL Server, I have mentioned how to create a DACPAC and a BACPAC file from an existing SQL Server Database. The underlying technology behind creating the DACPAC and the BACPAC files is the SQLPackage utility, which helps to create those files.
Read more »Learn AWS CLI – Explore IAM users, roles, policies using AWS CLI
May 28, 2020AWS provides a command-line interface (AWS CLI) tool to work with its various cloud services. It is a single tool with many useful commands and allows you to automate a particular task using scripts. You might need to do specific tasks regularly. You can use the AWS Web Console for it. However, it requires you to go through all configuration options all over again.
Read more »Impact of the Column order in SQL Server Composite Indexes
May 28, 2020In this article, we will explore the Composite Index SQL Server and the impact of key order on it. We will also view SQL Server update statistics to determine an optimized execution plan of the Compositive index.
Read more »SQL Server 2019 new features: Batch Mode on Rowstore
May 27, 2020In this article, we will explain batch mode on rowstore feature, which was announced with SQL Server 2019. The main benefit of this feature is that it improves the performance of analytical queries, and it also reduces the CPU utilization of these types of queries. Behind the scene, this performance enhancement uses the batch mode query processing feature for the data, which is stored in row format. Also, this feature has been using by columnstored indexes for a long time.
Read more »How to identify and resolve SQL Server Index Fragmentation
May 27, 2020In this article, we will learn how to identify and resolve Index Fragmentation in SQL Server. Index fragmentation identification and index maintenance are important parts of the database maintenance task. Microsoft SQL Server keeps updating the index statistics with the Insert, Update or Delete activity over the table. The index fragmentation is the index performance value in percentage, which can be fetched by SQL Server DMV. According to the index performance value, users can take the indexes in maintenance by revising the fragmentation percentage with the help of Rebuild or Reorganize operation.
Read more »How to configure an Amazon RDS environment for MySQL
May 26, 2020In this article, I am going to explain how to configure the RDS MySQL Environment in AWS. RDS is the Relational Database Service Available from the Amazon which enables us to spin up any relational database instance without having to worry about the infrastructure behind it. Users can connect to this instance very easily and start building database applications with ease. This article will focus more on how to configure the RDS MySQL Environment and not much on the MySQL details.
Read more »Programmatic access to Power BI PowerShell commandlets
May 25, 2020In this article, I am going to demonstrate how to access the Power BI PowerShell commandlets. In the previous article in this series, we discussed how to programmatically access the Power BI Service using the REST APIs available. PowerShell is one of the most frequently used and preferred ways of automating monotonous manual jobs and scripting those. PowerShell comes integrated with Windows, so it can be easily used from any Windows machine.
Read more »SQL Interview Questions for the second round
May 22, 2020In this article, we will talk about SQL interview questions and answers that can be asked in the second-round technical stage. These interview questions are based on real-life experiences. Therefore, it can be a very useful source when preparing for the second round of SQL technical interviews.
Read more »Working with Azure SQL databases using Azure CLI 2.0
May 22, 2020In my previous article, Getting started with Azure CLI 2.0, I have explained how to get started with the Azure CLI 2.0. I have also explained how to install it on a local machine and demonstrated a few commands. In this article, I’m going to explain how to create a SQL Database using Azure CLI 2.0. Further, in the later part of the article, I’ll also show how to create a backup of the SQL Database using Azure CLI. Creating a SQL Database using the Azure CLI helps us to get things automated and removes the error-prone manual creation of the databases and backups.
Read more »Using tSQLt for Test-Driven Data Warehouse Development (TDWD)
May 21, 2020This is the second part of a conceptual article for data professionals and enthusiasts interested to work on a test-driven data warehouse development concept by using tSQLt an industry recognized SQL unit testing framework.
Read more »Import XML documents into SQL Server tables using SSIS packages
May 21, 2020This article guides you through importing XML documents into SQL tables using SSIS packages.
Read more »Learn SQL: SQL Server export to Excel
May 20, 2020In the previous article, Learn SQL: SQL Server Pivot Tables, we’ve discussed how to create a report using a PIVOT table query. We’ve created such a report directly in the SQL Server. In this article, we’ll do the same, but this time we’ll use SQL Server export to Excel. So, let’s start.
Read more »Melissa Data Quality free components for SSIS
May 19, 2020In this article, we will talk briefly about data quality in SQL Server. Then, we will give a brief overview of the Melissa Data Quality for SQL Server Integration Services (SSIS), and we will demonstrate the components available in the community edition.
Read more »The JSON_QUERY() function to extract objects from JSON Data
May 19, 2020In this article, we will explore JSON_QUERY() functions in SQL Server to extract JSON objects and array from the JSON Data.
Read more »How to access Power BI REST APIs programmatically
May 19, 2020In this article, I am going to explain how to use the Power BI REST API to programmatically access the Power BI Service features. I’ll also discuss the basis of REST API and how to use the APIs using Postman. Using the Power BI REST API, you can programmatically manage the administrative services like getting a list of dashboards and workspaces, refreshing a dataset, copying a dashboard from one workspace to another, and so on.
Read more »An Overview of the XML Task in SSIS Packages
May 18, 2020Introduction
We can use an SSIS package to perform various tasks such as data import, transform, and get output in various formats. Once we launch Visual Studio 2019 and create an integration services project, it shows you various tasks in the SSIS toolbox. In this article, we will explore the XML Task in the SSIS.
Read more »Text Mining in SQL Server
May 18, 2020In this article, we will be discussing how Text Mining can be done in SQL Server. For text mining in SQL Server, we will be using Integration Services (SSIS) and SQL Server Analysis Services (SSAS). This is the last article of the Data Mining series during which we discussed Naïve Bayes, Decision Trees, Time Series, Association Rules, Clustering, Linear Regression, Neural Network, Sequence Clustering. Additionally, we discussed the way to measure the accuracy of the data mining models. In the last article, we discussed how models can be extracted from the Data query.
Read more »Connecting to Apache Hive and Apache Pig using SSIS Hadoop components
May 14, 2020In our previously published articles in this series, we talked about many SSIS Hadoop components added in SQL Server 2016, such as the Hadoop connection manager, Hadoop file system task, HDFS file source, and HDFS file destination.
Read more »Creating a visualization in Power BI Report View
May 14, 2020The Report View in Power BI can be used to create beautiful visualizations in Power BI.
Read more »Server and Database dashboards in Azure Data Studio
May 14, 2020Azure Data Studio (ADS) is an integrated, lightweight database development for supporting both on-premises and cloud databases. It can be installed on Windows as well as Linux operating system. I am assuming you are familiar with the SQL Server Management Studio. If you work on Linux based SQL Server instance, you can install only ADS in those systems. We can use integrated terminals to execute the PowerShell and Terminal sessions for the Linux system.
Read more »Differences between the M Language and DAX in Power BI
May 14, 2020Microsoft Power BI supports two different languages, M language and DAX (Data Analysis Expression) that can be used to filter, manage, and visualize data.
Read more »MySQL Create Table statement with examples
May 13, 2020In this article, I am going to explain the MySQL CREATE TABLE statement with examples. The following syntax contains basic statements to create a table in MySQL.
Read more »