$C:\Users\Marko\AppData\Local\Microsoft\Windows\INetCacheContent.Word\JSON.PNG$

How to import/export JSON data using SQL Server 2016

March 7, 2017 by Marko Zivkovic

JSON is an abbreviation for JavaScript Object Notation. JSON is very popular and currently the most commonly used data exchange format. Most modern web and mobile services return information formatted as JSON text, all database web services, web browsers (Firefox, Internet Explorer) return results formatted as JSON text or accept data formatted as JSON. Since external systems format information as JSON text, JSON is also stored in SQL Server 2016 as text. You can use standard NVARCHAR columns to store JSON data in SQL Server 2016.

Temporal Table applications in SQL Data Warehouse environments

March 7, 2017 by Hans Michiels

Today the subject of investigation is the Temporal Table, which is a new feature in SQL Server 2016. My focus will slightly be on how to use it in Data Warehouse environments, but there is some general information passing by as I write.

$C:\Dropbox\Blog Articles\Tabular_02.PNG$

How to automate SSAS tabular model processing in SQL Server 2016

March 6, 2017 by Craig Porteous

There are many ways to process your SSAS Tabular Model. This can be achieved in SSIS using the Analysis Services Execute DDL Task or manually, through Management studio GUI but to have a little fun & make the task more flexible I’m going to script this with ASSL/TMSL & build a notification round it. We can then schedule this as a step in a SQL agent job, call it from SSIS or PowerShell.

The impact of Residual Predicates in a SQL Server Index Seek operation

March 6, 2017 by Simon Liew

Abstract

It is common assumption that an Index Seek operation in a query plan is optimal when returning a low number of output rows. In a scenario involving residual predicates, an Index Seek operation could be reading a lot more rows than it needs into the memory, then each row is evaluated and discarded in memory based on the residual predicate and returns low number of output rows.

This article will explain the concept and the impact of Residual Predicates in a SQL Server Index Seek operation.

How to reconnect to a SQL Server instance when all credentials have been lost

March 3, 2017 by Musab Umair

The Problem

I came across a strange and rare situation at a client recently, where they asked me to resolve a problem on Microsoft SQL Server but did not give me any credentials to connect to the system. I asked them to state the actual problem and the reply was “we lost all credentials”. A number of questions started to generate in my mind. How are applications running? The Answer was “The application login is embedded in a DLL and it’s a third party application which we don’t have support”. How can you lose all the SQL Server credentials? The Answer “It was saved in a file and it was lost and the employee who knew it is no more working with the company”. Anyways, I had no choice but to have a solution for this strange problem.

Configuring the Azure SQL Database Firewall

February 28, 2017 by Minette Steynberg

Introduction

The Azure SQL Database firewall lets you decide which IP addresses may or may not have access to either your Azure SQL Server or your Azure SQL database.

When creating an Azure SQL Database, the firewall needs to be configured before anyone will be able to access the database. By default, no external access to your SQL Database will be allowed until you explicitly assign permission by creating a firewall rule.

$E:\Desktop\Drawing1.jpg$

Hybrid Cloud and Hekaton Features in SQL Server 2014

February 28, 2017 by Mustafa EL-Masry

Introduction

Microsoft SQL Server 2014 is considered to be the first version that supports Hybrid Cloud by adding a lot of exciting new features.

In this article, I will cover some of the top new features in these main points including Hekaton and Hybrid Cloud enhancements:

Benefits of Partitioning an SSAS Multidimensional Cube

February 28, 2017 by Koen Verbeeck

Introduction

In the article How to partition an SSAS Cube in Analysis Services Multidimensional, we explained how you can partition your measure groups in an SSAS cube. In this article, we’ll look at the expected benefits of the partition strategy. Time to reap the benefits of our hard work.

How to partition an SSAS Cube in Analysis Services Multidimensional

February 28, 2017 by Koen Verbeeck

Partitioning is the concept where you divide your data from one logical unit into separate physical chunks. This can have several advantages, such as improved performance or easier maintenance. You can for example partition a table in a SQL Server database, but you can also partition your measure groups inside an Analysis Services (SSAS) Multidimensional cube. In this article, we’ll discuss how you can set-up partitioning. For more information about the expected benefits, take a look at Benefits of Partitioning your SSAS Multidimensional Cube.

Discovering SQL server instance information using system views

February 28, 2017 by Gerald Britton

Introduction

Out of the box, SQL Server comes with a substantial and – release by release – ever-growing set of system tables, views, stored procedures and functions. There’s a good chance you’ve never directly used more than a handful of them. That’s certainly the case with me!

This is the first article in a series designed to explore this world that lives just below the surface of our everyday interactions with SQL Server through the same objects we create to enable the applications we write and support.

How to use the SQL Server Database Experimentation Assistant (DEA) tool

February 24, 2017 by Vitor Montalvão

Introduction

This is my second article about Database Experimentation Assistant (DEA). Please refer to my previous article to see how to download and install DEA.

The Database Experimentation Assistant is the new A/B testing solution for SQL Server upgrades. It enables customers to gather performance insights for upgrades by customers to conduct experiments on production database workloads across two versions of SQL Server.

How to use Windows hosted file shares to support SQL Server containers

February 23, 2017 by Paul Stanton

In a previous article, Automate Delivery of SQL Server Production Data Environments Using Containers, we introduced SQL Server containers for delivery of production data environments to development and QA teams. In this article we look at the methods used for working with SQL Server data, and use of file shares to support delivery of production databases with containers.

SQL Server Common Table Expressions (CTE)

February 23, 2017 by Syed Shanu

What is a Common Table Expression

A Common Table Expression, also called as CTE in short form, is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. The CTE can also be used in a View.

In this article, we will see in detail about how to create and use CTEs from our SQL Server.

Saving the Plan Cache storage using the Optimize for Ad hoc Workloads option

February 23, 2017 by Ahmad Yaseen

When you run a query in SQL Server, the SQL Server Query Optimizer will draw the road map for that query, specifying the optimal way to execute it, which is called the query execution plan.

Generating the execution plan will take few milliseconds from the CPU cycles, which is negligible for one query or small load, but it will be considerable for a very heavy transactional workload. Because of this, SQL Server caches these generated plans in a special type of memory called the Plan Cache to eliminate the overhead generated by the query plan if the same query is executed again. When you submit your query to the SQL Server Engine, it will search in the plan cache if there is any existing execution plan that can be reused, if an available execution plan is found in the plan cache, the plan will be used to execute that query, otherwise, the SQL Server Query Optimizer will create a new plan and keep it in the plan cache for future use.

How to analyze Storage Subsystem Performance in SQL Server

February 23, 2017 by Mustafa EL-Masry

Introduction

To improve performance, it is common for DBAs to search in each aspect except analyzing storage subsystem performance even though in many times, issues are, in fact, caused by poor storage subsystem performance. Therefore, I want to give you some tools and recommendation that you can use it to prevent your storage subsystem from being a performance issue for you.

How to import flat files with a varying number of columns in SQL Server

February 22, 2017 by Brian Bønk Rueløkke

Ever been as frustrated as I have when importing flat files to a SQL Server and the format suddenly changes in production?

Commonly used integration tools (like SSIS) are very dependent on the correct, consistent and same metadata when working with flat files.

So I’ve come up with an alternative solution that I would like to share with you.

When to Use SQL Temp Tables vs. Table Variables

February 21, 2017 by Aamir Syed

It is very beneficial to store data in SQL Server temp tables rather than manipulate or work with permanent tables. Let’s say you want full DDL or DML access to a table, but don’t have it. You can use your existing read access to pull the data into a SQL Server temporary table and make adjustments from there. Or you don’t have permissions to create a table in the existing database, you can create a SQL Server temp table that you can manipulate. Finally, you might be in a situation where you need the data to be visible only in the current session.

How to Merge and Split CSV Files Using R in SQL Server 2016

February 21, 2017 by Jeffrey Yao

Introduction

From time to time, we may encounter the following scenarios when dealing with data processing:

How to retrieve information about SSIS packages stored in MSDB Database

February 21, 2017 by Jefferson Elias

Introduction

Nowadays, most mid-size companies have implemented a Data Warehouse (DWH) solution. This DWH can be designed using a set of tools (or features) from Microsoft SQL Server. One of them is SQL Server Integration Services, also known as SSIS.

Monitoring SQL Server Deadlocks – the easy way

February 21, 2017 by Musab Umair

SQL Server is a very powerful tool and wherever I go, I see the tool being way much underutilized. Some people even don’t know about the features which are already in the SQL Server and they have not used it, like SQL Server Extended Events. Though, Extended Events is a way more complex and detailed topic which I cannot cover in just one article I will discuss one of its very good utilities out of the countless.

Monitoring Memory Clerk and Buffer Pool Allocations in SQL Server

February 20, 2017 by David Alcock

The following article applies to SQL Server versions 2008 +

Adequate memory is one of the most important factors for a well-functioning instance of SQL Server. By design SQL Server manages its own memory allocations via the SQLOS rather than having the servers Operating System perform this task.

$C:\Users\Marko\AppData\Local\Microsoft\Windows\INetCacheContent.Word\14.png$

How to query data in a System-Versioned Temporal Tables in SQL Server

February 20, 2017 by Marko Zivkovic

This article will cover the querying of temporal tables in SQL Server by using the FOR SYSTEM_TIME clause and its four sub clauses AS OF, FROM TO, BETWEEN AND, CONTAINED IN. Also, we’ll cover how to clean up the history table to keep it a manageable size.

Functions and stored procedures comparisons in SQL Server

February 20, 2017 by Daniel Calbimonte

Introduction

In a previous article, Functions vs stored procedures in SQL Server, we compared Functions vs stored procedures across various attributes. In this article, we will continue the discussion. We will talk also about Table-valued functions and compare performance with stored procedures with table valued functions and scalar functions.

Top 5 Deprecated Features in Reporting Services 2016

February 17, 2017 by Sifiso Ndlovu

It’s not often that I write negative articles surrounding SQL Server’s latest release but ever since we upgraded one of our BI boxes to run SQL Server Reporting Services 2016 (SSRS 2016), I have picked up on some frustrations from my team when using the upgraded Report Manager portal due to the unavailability of features that used to exist in versions prior to SSRS 2016. I have since realized that in spite of the many exciting features and improvements introduced in SSRS 2016, there is a downside to this latest version of reporting services that is likely to leave many administrators frustrated.

Sanitizing Inputs: Avoiding Security and Usability Disasters

February 17, 2017 by Ed Pollack

Introduction

In any application, we will likely have some need to control input data, either altering, filtering or otherwise changing text to fit our application’s needs.