Languages and coding

SQL formatter tools

March 8, 2018 by SQLShack

This collection of content will provide an overview of SQL formatter tools, with product page links for each of the tools. If you noticed a SQL formatter tool we missed, please let us know in the comments below.

How to upload multiple images to SQL Server

March 6, 2018 by Daniel Calbimonte

Introduction

Sometimes we need to store information including photos in our database. For example, the photo of the product, the photo of the team members. But how can we store images in SQL Server?

We could create an application in .NET or Java, but if we do not have experience in those programming languages, we could use SQL Server tools to do it.

In this new article, we will learn the following tips that will help us to work with images including how to:

insert one image into SQL Server
store multiple files into a table
verify that the images were inserted

How to create advanced analytics using Power BI and R scripts

February 27, 2018 by Esat Erkec

In this article, we will discover how to find the associations and hidden patterns in a dataset. The tool we will use for this is Power BI and not use any statistical functions or calculation.

Explanation

Power BI is one of the most powerful business intelligence solutions in the market. A cloud-based, user-friendly interface and basic development abilities provide this solution to be more convenient for people to use.

As you may know, Microsoft now supports R script in SQL Server and Power BI. R script support opens magical gates of statistics world to Power BI. Custom Visual offers very useful and interesting visual components for Power BI. In this store, many useful visualizations have been created by Microsoft or the community itself. Some of these visuals use R script infrastructure and this feature allows us to create advanced visual analytic in Power BI. Only a Power BI account is enough to connect and download theses visuals. We will use these visuals in our examples.

SQL Server table hints – WITH (NOLOCK) best practices

February 14, 2018 by Ahmad Yaseen

SQL Server table hints are a special type of explicit command that is used to override the default behavior of the SQL Server query optimizer during the T-SQL query execution This is accomplished by enforcing a specific locking method, a specific index or query processing operation, such index seek or table scan, to be used by the SQL Server query optimizer to build the query execution plan. The table hints can be added to the FROM clause of the T-SQL query, affecting the table or the view that is referenced in the FROM clause only.

How to create charts from SQL Server data using PowerShell

February 8, 2018 by Jefferson Elias

Introduction

Intended audience

This document is intended for application developers and database administrators who are willing to learn how to generate charts from data stored in Microsoft SQL Server using PowerShell.

Context

In previous article entitled Overview of PowerShell Charting capabilities we’ve seen some components of an assembly called System.Windows.Forms.DataVisualization assembly that is part of the .NET Framework and how to load it inside PowerShell.

Now, it’s time to use what we’ve learned so far.

Overview of PowerShell charting capabilities

February 6, 2018 by Jefferson Elias

Introduction

Intended audience

This document is intended for application developers and database administrators who are willing to learn how to generate charts from data stored in Microsoft SQL Server using PowerShell.

Introduction to mssql-cli, a new interactive command line tool for SQL Server

January 31, 2018 by Bojan Petrovic

Microsoft recently released a new and interactive command-line query tool for SQL Server with modern features such as auto-completion and syntax highlighting. It’s called mssql-cli and this fully open source tool works on cross-platform under the BSD-3 license.

Migrating SSRS content with PowerShell

January 29, 2018 by Craig Porteous

With a distinct lack of up-to-date, fully featured or built-in options to get Reporting Services content cleanly from A to B, it can often be a challenging task maintaining proper Development and QA environments or even moving reports from a SharePoint integrated installation to a native mode one, and vice versa.

I want to explore the two most efficient methods of bulk-migrating Reporting Services content & also explore other options I’ve used over the years and those that have come and gone.

How to implement array-like functionality in SQL Server

January 16, 2018 by Daniel Calbimonte

Introduction

I was training some Oracle DBAs in T-SQL and they asked me how to create arrays in SQL Server.

I told them that there were no arrays in SQL Server like the ones that we have in Oracle (varray). They were disappointed and asked me how was this problem handled.

Some developers asked me the same thing. Where are the arrays in SQL Server?

The importance of Python in SQL Server Administration

January 8, 2018 by Prashanth Jayaram

Some of my previous articles on Python provided insight of the basics and the usage of Python in SQL Server 2017.

This article is an effort to collect all the missing pieces and try to showcase the importance of using Python programming in SQL Server.

Get more out of Python on SQL Server 2017

January 5, 2018 by Gerald Britton

Introduction

One of the new features announced with SQL Server 2017 is support for the Python language. This is big! In SQL Server 2016, Microsoft announced support for the R language – an open source language ideally suited for statistical analysis and machine learning (ML). Recognizing that many data scientists use Python with ML libraries, the easy-to-learn-hard-to-forget language has now been added to the SQL Server ML suite.

There’s a big difference between R and Python though: R is a domain-specific language while Python is general purpose. That means that the full power of Python is available within SQL Server. This article leaves ML aside for the moment and explores a few of the other possibilities.

How to plot a SQL Server 2017 graph database using SQL Server R

January 3, 2018 by Sifiso Ndlovu

A few years ago, one common business case I came across in my professional career that required modelling of data into a many-to-many entity relationship type was the representation of a consultants and their projects. Such a business case became a many-to-many entity relationship type because whilst each project can be undertaken by several consultants, consultants can in turn be involved in many different projects. When it came to storing such data in a relational database engine, it meant that we had to make use of bridging tables and also make use of several self-joins to successfully query the data.

6 methods to write PowerShell output to a SQL Server table

December 25, 2017 by Prashanth Jayaram

PowerShell has become the ultimate choice for many database administrators because of its efficient way of handling and managing automation in a simple, quick way. It’s built on .NET Framework and uses Object Models such as COM, ADSI, ADO, and WMI. PowerShell has replaced the traditional way of scripting that used many legacy scripting practices to monitor SQL instances.

I’ve been asked on several occasions about how to store the output of PowerShell WMI data into the SQL table. The question comes up so frequently that I decided to write this article.

When sending data within a system (such as a PowerShell object to a cmdlet), the process is straightforward. However, with non-native data interchange (for instance, WMI to SQL), the process can potentially get complicated. Due to this, many purists suggest sticking to simple interchange formats, such as CSV, JSON or in some cases, XML.

How to use sargable expressions in T-SQL queries; performance advantages and examples

December 22, 2017 by Esat Erkec

The challenge

One of the main tasks of a SQL Server database administrator is performance tuning. Sometimes, though, coders or developers don’t always prioritize database performance or query optimization. Here is a typical scenario

Imagine that developers create a new table and then insert some records in a test environment and test their queries to retrieve data from it
The query executed successfully and does not exhibit any symptoms of performance problems
The developer team release this table and query into production
One day you take a telephone from your colleague and he says my report is very slow
Bingo! In production, this table contains a lot of records and this is resulting in performance bottlenecks when querying it

How to return data use, index compression, and row information with PowerShell

December 6, 2017 by Timothy Smith

Background

We recently inherited a database environment where we’re facing significant data growth with limits on the sizes we can allow our databases to grow. Since we maintain multiple development, QA and production environments and these environments must be sized appropriately. We set a few standards about tables that exceed certain sizes – rows, data or both, or have certain growth patterns and our standards force compression at thresholds we set for our different environments (including using page, row, or clustered columnstore index compression) We’re looking for how we can get information about data and compression in tables and options we have so that we can quickly determine candidates that don’t currently match our best practices design we’ve set for our environments.

What to choose when assigning values to SQL Server variables: SET vs SELECT T-SQL statements

November 29, 2017 by Ahmad Yaseen

SQL Server provides us with two methods in T-SQL to assign a value to a previously created local SQL variable. The first method is the SET statement, the ANSI standard statement that is commonly used for variable value assignment. The second statement is the SELECT statement. In addition to its main usage to form the logic that is used to retrieve data from a database table or multiple tables in SQL Server, the SELECT statement can be used also to assign a value to a previously created local variable directly or from a variable, view or table.

R script word-cloud in SQL Server Report Builder

November 28, 2017 by Esat Erkec

What is R

R is a very popular data programing language. R is especially used in data analysis, statistics calculations, predictions, data mining and machine learning. R is used by data scientist, economist, genetic scientists, and statisticians. R has very wide usage in real life. Healthcare, finance, marketing, and manufacturing are some of them.

Understanding cursors and replacing them with JOINs in SQL Server

November 24, 2017 by Ben Richardson

Relational database management systems including SQL Server are very good at processing data in sets.

However, if you want to process data on row-by-row basis rather than in sets, cursors are your only choice. Unfortunately, cursors are extremely slow and so where possible should be replaced with JOINS.

Data Interpolation and Transformation using Python in SQL Server 2017

November 21, 2017 by Prashanth Jayaram

As a continuation to my previous article, How to use Python in SQL Server 2017 to obtain advanced data analytics, a little bit of curiosity about Deep Learning with Python integration in SQL Server led me to write this latest article.

With Python running within SQL Server, you can bring the existing data and the code together. Data is accessible directly, so there’s no need to extract query data sets, moving data from storage to the application. It’s a useful approach, especially considering issues of data sovereignty and compliance, since the code runs within the SQL Server security boundaries, triggered by a single call from T-SQL stored procedures.

An introduction to the bcp Utility (bulk copy program) in SQL Server

November 9, 2017 by Daniel Calbimonte

Introduction

The Bulk copy program aka bcp is the console application used to export and import data from text files to SQL Server or vice versa.

It is very popular because it is fast and easy to download.

An overview of Python vs PowerShell for SQL Server Database Administration

November 2, 2017 by Prashanth Jayaram

Today, Microsoft claims that Linux runs like a First-Class citizen on Azure, .NET Core has been open-sourced, and has been ported over to Linux, taking PowerShell along. PowerShell runs really well on Ubuntu, CentOS, RedHat Linux, and even Mac OS X. There are Alpha builds available for a few other platforms as well, all available for exploitation under the MIT License on GitHub. “Manage anything, anywhere” is what Microsoft is offering to its customers. Keeping with that, we now have:

ETL optimization using SQL TRY Functions

October 24, 2017 by Sifiso Ndlovu

Introduction

An enterprise data warehouse ETL solution typically includes, amongst other steps, a data transformation step that converts source data from one data type into another. It is during this step that type conversion errors may occur and depending on the type of exception handling techniques implemented in the ETL solution (or lack thereof), frustration may occur for both ETL developers and DBAs when trying to identify and resolve type conversion errors. In this article we take a look at a trio of SQL TRY built-in functions that were introduced in SQL Server 2012, namely, TRY_PARSE, TRY_CAST, and TRY_CONVERT and how they could be utilized to reduce type conversion errors in ETL solutions and thereby saving developers needless troubleshooting exercise.

Why would a SQL Server DBA be interested in Python?

October 23, 2017 by Prashanth Jayaram

If we follow blogs and publications on the technological advancement with respect to SQL, we notice the increase in the number of references to Python, of late. Often, that makes us think:

Why so much emphasis on Python these days?
Isn’t knowing PowerShell scripting sufficient for the automation requirements of today?
Is it the time DBAs started learning a programming language such as Python in order to handle their day-to-day tasks more efficiently?
Why do so many job postings these days include “knowledge of scripting” as a requirement?
Is all of this happening because the paradigm is shifting? Can’t the current Microsoft-specific languages such as PowerShell handle the shift?

Working with the SQL Server command line (sqlcmd)

October 18, 2017 by Daniel Calbimonte

Introduction

Sqlcmd allows executing queries, T-SQL sentences and SQL Server scripts using the command line.

In the previous article How to work with the command line and Azure to automate tasks, we worked with the sqlcmd in Azure.

In this new chapter, we will show the following examples in a local SQL Server using sqlcmd:

Monitoring SQL Server database status changes using T-SQL and PowerShell scripts

October 17, 2017 by Prashanth Jayaram

Monitoring a SQL Server database is a critical component of database administration. Ninety percent of the organizations expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the availability of databases. Hence, SQL Server database monitoring is considered a critical responsibility of a database administrator. Organizations tend to spend a lot of their funds towards enterprise solutions. And due to the sensitive and growing nature of business and user needs, application availability is very important nowadays.