SQL Server Partitioned Views

November 18, 2016 by Ahmad Yaseen

SQL Server table partitioning is a great feature that can be used to split large tables into multiple smaller tables, transparently. It allows you to store your data in many filegroups and keep the database files in different disk drives, with the ability to move the data in and out the partitioned tables easily. A common example for tables partitioning is archiving old data to slow disk drives and use the fast ones to store the frequently accessed data. Table partitioning improves query performance by excluding the partitions that are not needed in the result set. But table partitioning is available only in the Enterprise SQL Server Edition, which is not easy to upgrade to for most of small and medium companies due to its expensive license cost.

How to create and configure Microsoft Azure HDInsight

November 18, 2016 by Daniel Calbimonte

Introduction

In our chapter about PolyBase, we presented this new SQL Server 2016 feature to query CSV files stored in Azure Storage accounts. We mentioned that in PolyBase you can query data in Hadoop (HDInsight) using SQL Server. HDInsight is a very popular system in Azure that eventually you will need to interact with if you use SQL Server. That is why we will give an explanation for newbies about it.

What’s new in SQL Server 2014 Cardinality Estimator?

November 14, 2016 by Ahmad Yaseen

The Cardinality Estimator is a SQL Server Query Processor component that is responsible for predicting the number of rows that the query will return. This estimation of the number of rows in addition to the SQL Server statistics will be used by the SQL Server Query Optimizer to create the optimal and the most accurate execution plan for your query that has the lowest processing cost to execute.

Creating reports based on existing stored procedures with SQL Server Reporting Services

November 10, 2016 by Nesha Maric

Basic extraction of the SQL Server database data is usually achieved by querying the databases and creating stored procedure to automate the extraction process. Unfortunately, extracting the information in this way will not yield high-end reports, and only basic table-shaped reports are available when extracting the information via SQL Server Management Studio or similar tools. In order to create high-end reports that will include additional projections of the data, such are graphs, lists, charts… SQL Server offers powerful reporting options within the SQL Server Reporting service.

How to use SQL Server Data Quality Services to ensure the correct aggregation of data

November 9, 2016 by Steve Simon

Introduction

An interesting opportunity arose at a client site during early October which provided a phenomenal opportunity to do a Data Quality Services implementation. My client (a grocer) had been requested to produce summary reports detailing the amount of funds spent during 2016 (YTD) with the myriad of manufacturers from whom the chain purchases their inventory. All “accounts payable” entries are done manually and as such are prone to errors.

Two methods for restoring a data warehouse/data mart environment

November 8, 2016 by Sifiso Ndlovu

Implementing best data warehouse designs and practices such as data lineage reduces the need to ever have to restore an entire relational data warehouse. However, sometimes there are instances whereby you have inherited poorly designed data warehouse environments that leaves you with no other options but to perform an entire database restore in an event of a sudden disaster. I recently found myself in a similar situation of having to recover one of my data mart following a data integrity issue wherein all data of a type 1 dimension was updated/overwritten using an incorrect source file. In this article I take a look at how different approaches can be utilised to restore the compromised SQL Server-based data mart back to its “good state”.

Have your database server talk to you: Send emails directly from SQL Agent Jobs

November 7, 2016 by Aamir Syed

Recently, I was asked if I can write and run a specific query and have the results, (if any) emailed to a specific group of people every day? But only send the email if the query returns results. Who wants to open and empty email after all?

As with any DBA, we need a way to automate this process, as our time is valuable. Who wants to manually run a script, paste it into excel and then email it directly to users every morning?

How to create an image cataloging system using SQL Server Reporting Services

November 2, 2016 by Steve Simon

Introduction

With Christmas just around the corner, in today’s “get together” I thought that we would have some fun by cataloging a collection of ‘your favorite items’. Whether it be a coin collection, a china plate collection or a stamp collection, the process is the same and certainly something that you will enjoy creating and maintaining. For today’s example, we are going to construct a “Postage Stamp” cataloging system. We are going to see how we are able to get from this…

How to check if a date is a holiday in SQL server

November 1, 2016 by Gerald Britton

Introduction

There are plenty of scripts to compute the date of various holidays given the year. Let’s look at the problem in reverse: Given a date, determine if it is a given holiday or not.

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 create and configure the Tree Map Chart in SQL Server 2016 Reporting Services

October 28, 2016 by Rajendra Gupta

In the previous article, in this series, How to create and configure a Sunburst chart in SQL Server 2016 Reporting Services, we have explored new chart types in SQL Server 2016; the Sunburst chart and the Tree Map chart, that are used to display hierarchical data. The Sunburst chart is a way of presenting relational datasets together in a compact form. The Tree Map Chart is also a way of showing hierarchical data.

In this article, we are going to show how to create a Tree Map chart and its comparison with the Sunburst chart.

Securing SQL Server Surface Area

October 26, 2016 by Artemakis Artemiou

In a previous article, we have discussed about the top 10 security factors that you should take into consideration in order to secure your SQL Server instances. In this article I will try to deep dive into one of those factors, that is SQL Server Surface Area.

How to create and configure SQL Server Agent Alerts

October 24, 2016 by Minette Steynberg

Introduction

If you have ever wanted to run a job dependent on a certain performance condition, then this article is for you.

Usually SQL Server agent jobs are configured to run on a schedule. But what if instead of a schedule you want a job to be executed when a certain performance threshold is exceeded?

SQL Server 2016 Database Scoped Configuration

October 21, 2016 by Ahmad Yaseen

SQL Server 2016 introduces a new set of configurations that can be applied at the database level to control its performance and behavior. These configurations were previously applicable only at the SQL instance level before version SQL Server 2016, With the ability to configure settings at the database level, many databases that are hosted in the same instance can now be isolated from each other, with each database has its own customized configurations. The database level configurations allow us also to set different database configurations for the Always On Availability Groups primary and secondary databases to meet the different types of workloads.

SQL Server 2016 – PolyBase tutorial

October 21, 2016 by Daniel Calbimonte

Introduction

This article will teach you how to install PolyBase and will show you a simple example to start.

PolyBase is a new feature in SQL Server 2016. It is used to query relational and non-relational databases (NoSQL). You can use PolyBase to query tables and files in Hadoop or in Azure Blob Storage. You can also import or export data to/from Hadoop.

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

SQL Server 2016 Parameter Sniffing

October 19, 2016 by Ahmad Yaseen

SQL Server tries always to generate the most optimized execution plan for each stored procedure the first time that the stored procedure is executed. The SQL Server Engine looks at the stored procedure passed parameter values when compiling the stored procedure, the first execution, in order to create the optimal plan including the parameters and keep that plan for future use in the plan cache. This parameter analysis process is called the Parameter Sniffing.

Reporting in SQL Server – Manipulating the data via the grouping functions (provided by the Tablix control)

October 18, 2016 by Steve Simon

Introduction

In many of our past “get togethers” we have discussed pivoting raw data in order to best represent corporate data within our business reporting. As we may remember, in many instances we pivoted the data within the report query.

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.

Using Dynamic Data Masking in SQL Server 2016 to protect sensitive data

October 17, 2016 by Ahmad Yaseen

Dynamic Data Masking is a new security feature introduced in SQL Server 2016 that limits the access of unauthorized users to sensitive data at the database layer.

As an example of the need for such a feature is allowing the applications developers to access production data for troubleshooting purposes and preventing them from accessing the sensitive data at the same time, without affecting their troubleshooting process. Another example is the call center employee who will access the customer’s information to help him in his request, but the critical financial data, such as the bank account number or the credit card full number, will be masked to that person.

How to create and configure a Sunburst chart in SQL Server 2016 Reporting Services

October 12, 2016 by Rajendra Gupta

Microsoft SQL Server 2016 introduces several significant new features, and enhances some existing ones in reporting services. We have summarised these new features in the previous article. We’ve also explored how to configure the SQL Server Mobile reports in my earlier article.

SQL Server Management Studio 2016 Productivity Enhancements

October 11, 2016 by Samir Behara

Problem Statement

Developers who work in both SQL and .NET world must have experienced the differences between Visual Studio IDE and SQL Server Management Studio IDE.

Being a full-stack developer myself, I always felt that SSMS lacked a lot of basic features when compared to the functionalities which Visual Studio IDE provided out of the box. With SSMS being tightly coupled with SQL Server Core engine, the IDE had quite a technical debt associated with it over a period of time.

My journey to PASS Summit 2016 via SQL Shack

October 3, 2016 by Ahmad Yaseen

My name is Ahmad Yaseen and I am a senior SQL Server database administrator at Aramex International Company for more than six years, where I started building my experience in the SQL Server administration field. Being a SQL Server Database administrator in such big company is not that easy, as the difficulties that you may face is not only at the beginning. What makes the DBA job special is that it is a very deep job, in which you will learn new things, have to solve new problems, fall in strange situations and be under pressure every day, but it is an interesting job for me.

Reporting in SQL Server – Customize the visual appearance of your reports

October 3, 2016 by Steve Simon

Introduction

As we are nearing the end of the North American summer, I thought that we would take a lighter look at nifty ways of reporting information. In today’s “fireside chat” we have a look at a unique way of displaying our information using charts and line graphs all in one single chart (see below). We shall take things one step further and work with the color fill of the vertical bars to reflect the values that they represent.

How to filter and block the data access using SQL Server 2016 Row-Level Security

September 28, 2016 by Ahmad Yaseen

SQL Server 2016 came with many new features and enhancements for existing ones, that concentrate on the aspect of SQL Server security. One of the new security features introduced in SQL Server 2016 is Row-Level Security. This feature allows us to control access deeply into the rows level in the database table, based on the user executing the query. This is done within the database layer, in complete transparency to the application process, without the need to manage it with complex coding at the application layer.