SQL Server

SQL Server 2016 Stretch Database

SQL Server 2016 Stretch Database

One of the features in SQL Server 2016 that you will want to explore is the ability to store your historical data in the Azure cloud.  By leveraging the Stretch Database feature your applications can silently migrate data to the cloud without having to change your...Read More »


SQL Server Polybase (2016)

What is SQL Server Polybase?

Coming in SQL Server 2016 is the ability to query relational tables as well as data stored in Hadoop or Azure Blob storage.  This technology will allow you to continue to leverage SQL Server for your data warehousing and access data stored in different formats and...Read More »


Leveraging SQL Server Database Schema

Leveraging SQL Server Database Schema

Data warehouses typically pull data from various sources and combine the data into a common repository.  Keeping up with the source systems and what data is being pulled from each system can be challenging.  One method that can be used to easily identify the source and...Read More »

SQL Server 2014 Deprecated Features

SQL Server 2014 Deprecated Features – Database Engine

Everyone looks at the new features when trying to determine when to upgrade from an older version of SQL Server to a newer release.  There are many factors to consider and for every environment certain features have a higher priority and need for...Read More »


T-SQL EXCEPT and INTERSECT

Except and Intersect Venn Diagram

T-SQL EXCEPT and INTERSECT

Both T-SQL EXCEPT and INTERSECT are set based operators that combine multiple query results back in the same result set.  EXCEPT returns the records from the query on the left that are not found in the right query.  INTERSECT returns the distinct rows that are in both the...Read More »


T-SQL UNION Set Operator

T-SQL UNION

Set operators allow you to combine data from two or more queries and return the results in a single set.  There are 3 set operators available T-SQL
EXCEPT, INTERSECT and UNION.
This article is focused on the UNION operator.  UNION is used when you need to combine data from multiple queries...Read More »

SSRS Farm to Scale-Out Performance

SSRS Scale Out

SSRS Farm Overview

Building a SSRS farm will require Enterprise or Business Intelligence editions of SQL Server for versions 2012 and 2014.  SQL Server allows for the deployment of 2 or more servers running SQL Server Reporting Services to increase the performance of your reporting environment.

SSRS stores its data in...Read More »


T-SQL Random Numbers using RAND() for Test Data

T-SQL Random Numbers using RAND()

SQL Server includes the T-SQL RAND() function to create a random value between 0 and 1 of float datatype.  To create a random number execute Select Rand(), in my example it returned .0131039082850364.  If I wanted to always return the same number I can include a...Read More »


ROW_NUMBER SQL Server

row_number Order By

Row_Number SQL Server

SQL Server includes several Ranking Functions that can be called in T-SQL.  One of these is the Row_Number() function.  You can use this function to return a sequential number in your result set that begins at 1.

There are 2 arguments that can be passed into the function that...Read More »


SQL Server Management Studio Reports

SQL SSMS Server Reports

SQL Server Management Studio Reports

Whether you are an Application, SQL, BI Developer or a DBA.  Knowing what is occurring within your database is critical to the performance of your applications.  One tool you can leverage to see what is happening are the built in reports in SSMS.  SQL Server includes...Read More »


SQL Server Table Partition Example

Table Partition SQL Server

Why Use a SQL Server Table Partition

Data warehouses and data repositories often have tables that store millions of records and each day adding thousands more.  As the data ages, the older records generally require fewer updates.  If all of these records are kept in a single physical table.  Queries and...Read More »

SQL Server TABLESAMPLE

TABLESAMPLE

Using TABLESAMPLE in SQL Server

SQL Server has several ways for you to limit the number of records returned from a query.  For example, the AdventureWorks [sales].[salesorderdetail] table has 121,317 rows loaded in sequential order starting with SalesOrderDetailID 1.  By Using a top 1000 clause I can limit the results to...Read More »


SQL Server Checksum

SQL Server Checksum

Use SQL Server Checksum in Data Warehousing

When building data warehouses you...Read More »


Common Table Expressions (CTE)

Common Table Expresseion (CTE)

A common table expression (CTE) is a derived table that is defined and only stored for the duration of the query. CTE’s can be used for recursive queries, creating a view on demand and referencing the same table multiple times in the same query.  CTE’s make your...Read More »


SQL Server 2014 Cardinality Estimator

SQL Server 2014 Cardinality Estimator

The MSDN

Database Engine

Included in the new...Read More »


SQL Server Data Encryption Options

SQL Server Data Encryption Types

Businesses today have a greater need than ever to protect their data from security breaches.  Data stored in databases are a prime target for hackers since it contains data that is normalized and contained in a central location.  Once a hacker accesses a database all of...Read More »


(SSAS) SQL Server Analysis Services Tabular Basics

SSAS Tabular Model Project

What is (SSAS) SQL Server Analysis Services Tabular Mode?

SQL Server 2012 ships Analysis Services that can be installed in the traditional OLAP engine or in the Tabular engine mode.  Tabular mode is an in-memory database that compresses and queries the data with great performance.  In most cases, you may find...Read More »


SQL Server PDW the Microwave of BI

PDW Technical Architecture

The Path to the PDW

For decades we had the traditional electric oven to cook with and people thought it was great.  This technology allowed for consistent and dependable cooking for its users.  Then something magical happened, the microwave was invented and used for food preparation.  By leveraging the a different...Read More »


SQL Server Analysis Services - SSAS Fundamentals

SQL Server Analysis Services (SSAS) Fundamentals

SSAS comes with your purchase of Microsoft’s SQL Server.  It is a separate component and can be installed in two different modes, Multidimensional or Tabular.  Multidimensional is the traditional method of building and OLAP cube.  Tabular mode using in memory techniques to aggregate relational tables...Read More »


SQL Server Data Compression

SQL Server Data Compression Overview

I have been reviewing the options for data compression in SQL Server and believe it can be a great value to Reporting and Business Intelligence databases.  By their nature BI databases typically store more data than any other database in your environment.  Even with the cost...Read More »


SQL Server Filtered Index Performance

Filtered Index – Database Performance

SQL Server 2012 includes filtered indexes and is a great feature for Business Intelligence and Reporting databases.  Filtered indexes allow you to reduce the overhead of having a index on a tables and increase the performance of the query optimizer by already reducing the number of...Read More »


SQL Server Sparse Columns

SQL Server Sparse Column

Often stored in the columns of a reporting database are troublesome columns that are populated with limited amounts of data.  One method to optimize these types of columns in your reporting database is to utilize the SPARSE keyword in a CREATE or ALTER table statement.  For instance...Read More »


SQL Server Index Performance for BI Databases

SQL Server Index Performance Tips for Business Intelligence

If you are new to Reporting Databases you need to be aware that they indexing needs are different from OLTP database operations.  While you will still have clustered and nonclustered indexes how you utilize them will be different with Reporting systems.

Having a lot...Read More »


How to create a Columnstore Index

Each table can have only 1 columnstore index, data from the index is stored in memory in the xVelocity engine.  Once a columnstore index has been created on a table you cannot update the table.  Microsoft recommends you take the following actions to update the data in a table containing...Read More »


Data Mining in SQL Server 2012 (SSAS)

Data Mining SQL Server 2012

Included in your purchase of Microsoft’s SQL Server 2012 is the ability to perform data mining techniques.  This feature comes as part of Analysis Services via the Data Mining model.

There are 9 total algorithms included broken into 5 types within SQL Server.  The types of Data...Read More »


SQL Server 2012 - Columnstore Indexes

If you have a large reporting data and have not explored the new Columnstore Indexes.  Take some time to review what you are missing.  Unlike regular database indexes (rowstore format) that index tables based on the data tied to a row.  Columnstore indexes compress the data within a column to...Read More »


PowerPivot Value for IT and Business Analyst

What is PowerView and why is it important to IT and business users?

PowerView is an Excel 2013 feature that enables users to build integrated charts and graphs within Excel.  Given a completed mashup of data, a user can then create a wide selection of reports in the familiar Excel interface....Read More »


SQL Server 2012 Feature Backup to URL

SQL Server Backup to URL

A new feature to SQL Server 2012 (requires SP1 CU2) is the backup to URL functionality.  This feature allows you the option to backup your SQL Server database directly to the Windows Azure service.  This adds value to an infrastructure by decreasing on-site hosted storage, increasing...Read More »