Languages and coding

SQL Server Database Recovery Process Internals – database STARTUP Command

February 14, 2017 by Prashanth Jayaram

A database recovery process is an essential requirement for database systems, It can be a tedious job and the process of recovery varies on lot of scenarios. The desire to improve recovery results has resulted in various procedures, but understood by few and prone to errors. In this article, I’ll illustrate the impact of stopping the database instance in the middle of a large transaction that was running and discuss several techniques and tools that are available for faster and successful recovery.

How to Import / Export CSV Files with R in SQL Server 2016

February 9, 2017 by Jeffrey Yao

Introduction

Importing and exporting CSV files is a common task to DBAs from time to time.

How to create a simple SQL Server database login dialog using PowerShell

January 30, 2017 by Nemanja Popovic

In this article, we’ll be creating a SQL Server login form, as shown in the example below, using PowerShell to accept user input. We’ll show the code behind the form, how to capture the user input and utilize it:

Using Variables in Dynamic SQL

January 26, 2017 by Aamir Syed

Before we delve into these SQL concepts, note that I like to do all my development in SQL Management Studio. So if you want to follow along go ahead and open that up.

How to generate random SQL Server test data using T-SQL

January 26, 2017 by Daniel Calbimonte

Introduction

In this article, we will talk about generating random values for testing purposes.

I once had a customer with software that worked fine in the demo with 30 rows, but after some months, the software had more than a million rows and it became very slow. The problem was not SQL Server, the problem was the application, which was not designed for tables with millions of rows. The customer sued to the software provider and lawyers were needed to create a resolution. If the provider had tested the software with millions of rows, this problem would have never happened.

Connecting PowerShell to SQL Server – Using a Different Account

January 24, 2017 by Shawn Melton

Introduction

In a previous article on Connecting PowerShell to SQL Server I went over how you use various methods in PowerShell to connect to SQL Server. In those examples though I only touched on using the current user that is running “PowerShell.exe”. In this article I want to pick up and go over how you would use the same methods, but as a different account. I will cover using both Windows Authentication (where supported) and SQL Login authentication.

How to administer SQL Server Reporting Services (SSRS) subscriptions using PowerShell

January 23, 2017 by Sifiso Ndlovu

In the article Report Subscription Changes in SQL Server Reporting Services 2016, I covered several changes to standard and data-driven subscriptions that were introduced in the release of SQL Server 2016. However all of those changes related to administering report subscriptions using a GUI (i.e. Report Manager Portal, SSRS Configuration Manager). The release of SQL Server Core, SQL Server on Linux and enhancements on Windows PowerShell, reinforces the growing shift by Microsoft to have SQL Server professionals adopt scripting as one of the ways to carry out their daily tasks. In this article we continue to explore SQL Server Reporting Services (SSRS) subscription feature by discussing some of the Windows PowerShell commands that can be utilised to administer report subscriptions.

Microsoft SQL Server Non-Contained Object Migration Deployment Procedure using Powershell

January 12, 2017 by Musab Umair

As DBAs, we all face a lot of challenges while performing the migration of SQL Server databases from one server to another or even the whole servers at some times. The Database Migration process is not always a simple Backup and Restore process so we might need a huge amount of effort if we have to migrate objects which are not included in the native backups for a specified database, these objects are called Non-Contained Objects.

Native JSON Support in SQL Server 2016

January 6, 2017 by Manjuke Fernando

With the introduction of SQL Server 2016, Microsoft introduced many new features which had taken SQL Server another step forward and they made sure that it stands in front among many major Relational Database Systems.

One such feature which has been lacking but desperately required was the native support towards JSON.

Java HashCode in TSQL

December 28, 2016 by Ed Pollack

The Java HashCode method is used to determine uniqueness or similarity of strings. While implemented in Java, there can be many benefits of creating a similar or customized version of this method.

Handy features of the dbatools Read-DbaBackupHeader command

December 21, 2016 by Aaron Nelson

If you haven’t heard of dbatools, it’s an open source project out on GitHub dedicated to making life easier for Data Professionals. If you have an idea that you don’t see a command for, you can build it and contribute it to the project. Or you can ask if someone has time to write if for you.

How to use SQLCMD commands in the SSMS query editor

December 13, 2016 by Marko Zivkovic

SQLCMD Mode allows creating, testing, executing SQLCMD commands or scripts in SQL Server Management Studio directly in the query editor. This option is available since SQL Server 2005.

3 ways to improve T-SQL performance

November 22, 2016 by Daniel Calbimonte

Introduction

When customers used to ask for advice to solve some T-SQL Problem, they would show me their scripts, stored procedures and cursors. I saw horrible things doing that job, some things I do not want to remember, and even some I cannot tell you about 😉 In this article, we will talk about performance problems when using while loops in T-SQL. We will also talk about problems with UNIONsand finally the use of JOINS of two or more tables using the where clause (ANSI 89) instead of using INNER, LEFT or RIGHT JOINS using the from clause (ANSI 92).

PowerShell: Get a daily database status email

November 22, 2016 by Aamir Syed

The need for this script came about when I took a new job as a DBA. One of my responsibilities was to make sure all databases were available after the maintenance window which ran during the weekend. Rather than log into each database server and check that the databases were online, I had a script do the work for me and shoot me an email.

How to create and manage T-SQL code snippets

October 28, 2016 by Artemakis Artemiou

Introduction

Transact-SQL (T-SQL) snippets were first introduced in SQL Server 2012 Management Studio. T-SQL snippets are templates containing one or more T-SQL statements which you can easily use them when you develop T-SQL scripts. The main concept behind code snippets is code reuse. With code reuse you develop faster, easier and with less syntax errors. If you frequently use specific T-SQL statements, then you should consider creating snippets with these statements as they will help you a lot.

How to effectively extract data from an OLAP cube by relying upon T-SQL

October 19, 2016 by Steve Simon

Introduction

Last month I ran two Business Intelligence pre-conferences in South Africa. A interesting request arose during the course of the preconference in Cape Town. The individual wanted an approach to extracting data from an OLAP cube that would avoid intensive utilization of MDX and more reliance upon T-SQL. His main concern was with filtering the data at run time, via the report front end.

In this “fire side chat” we shall do just that, utilizing the cube that comes with the new Microsoft database “WideWorldImporters” and we shall learn how we can get from this

How to import a sample bacpac file to an Azure SQL Database using sqlpackage and PowerShell

October 18, 2016 by Daniel Calbimonte

Introduction

In our first part of this series, we showed how to import a bacpac file using the Azure Portal with Microsoft Azure Storage Explorer (MASE) and SQL Server Management Studio (SSMS) to create a Database in Azure.

In the second part, we will show how to import a bacpac file to create a Database using the sqlpackage.exe tool and PowerShell.

5 handy Transact-SQL tips you may (or may not) know

September 23, 2016 by Minette Steynberg

Introduction

Even if you are a SQL veteran, actually especially if you are, you sometimes get really stuck doing something in the old way not realizing that in newer versions of SQL Server some handy functions have been added that can make your life easier.

In this article, I will mention a couple of these which appeared in SQL Server 2008 or later versions or might have even been there before but most people just didn’t know about.

How to use the xp_cmdshell extended procedure

September 12, 2016 by Daniel Calbimonte

Introduction

The xp_cmdshell is a very powerful extended procedure used to run the command line (cmd). This is very useful to run tasks in the operative system like copying files, create folders, share folders, etc. using T-SQL.

In this new article, we will show some useful examples about how to use it.

New PowerShell Cmdlets in SQL Server 2016

August 18, 2016 by Daniel Calbimonte

Introduction

PowerShell is Windows Shell that can be used to automate tasks in Windows, Exchange, Azure, SQL Server and more.

SQL backup reports with PowerShell

August 15, 2016 by Aamir Syed

Let me preface this post by saying that this was a process that evolved over time. It started as a simple sql statement that emailed me a csv file. From there the actual SQL code evolved to display cleaner results. When I learned what powershell could do, I spent some time learning it during my lunch breaks at work. Even when I implemented a powershell script for the first time, it still wasn’t a clean looking report, but it was still better than what we had prior. Even now there is room for improvement but I am happy with the results and hope that it can provide some help to other SQL DBA’s out there.

Top 5 T-SQL functions introduced in SQL Server 2016

August 1, 2016 by Sifiso Ndlovu

One of the exciting things to look out for in a release of SQL Server are the new built-in T-SQL functions that are usually introduced. Some of the reasons for the new functions may be because SQL Server is catching up to other competitors, attempting to stay ahead of the competition or simply responding to SQL Server feature-requests from the Microsoft Connect site. In this article, I take you through my top 5 T-SQL functions released in SQL Server 2016.

Reporting in SQL Server – Combine T-SQL and DAX queries to produce effective reports

July 27, 2016 by Steve Simon

Introduction

With today’s challenging economic times it has become more and more important to manage and rectify changing sales patterns and trends.

In today’s “get together” we shall be expanding our outlook by creating efficient and effective reports utilizing SQL Server Reporting Service 2016 and T-SQL, together with the DAX code that we created in our last “fire side chat”.

Convert SQL Server results into JSON

July 12, 2016 by Sifiso Ndlovu

In my article, Warehousing JSON Formatted Data in SQL Server 2016, we had a look at available T-SQL options for converting JSON data into rows and columns for the purposes of populating a SQL Server based data warehouse. The increased popularity of JSON in modern web applications may create a requirement for data teams to expose some of their data to client applications (i.e. reporting tools, web services etc.) in a JSON format. In this article we take a look at how such a requirement can be implemented by data teams using SQL Server 2016 FOR JSON clause

Warehousing JSON formatted data in SQL Server 2016

June 22, 2016 by Sifiso Ndlovu

In this article, I continue to review the exciting features available in SQL Server 2016. One such feature is the long awaited T-SQL support for JSON formatted data. In this article we take a look at how JSON support will impact data warehouse solutions.