Converting Rows to Columns (PIVOT) and Columns to Rows (UNPIVOT) in SQL Server

In my last article “Converting Comma Separated Value to Rows and Vice Versa in SQL Server”, I talked about how you can convert comma separated (or separated with some other character) values in a single column into rows and vice versa. In this article, I demonstrate how you can convert rows values into columns values (PIVOT) and columns values into rows values (UNPIVOT) in SQL Server. For more information click here.

Converting Comma Separated Value to Rows and Vice Versa in SQL Server

Often while reporting you will encounter a situation where you will have comma separated (or separated with some other character) values in a single column but you want to report them in rows whereas in some other cases you might have values in multiple rows and want them to be a single value separated by comma or some other character. In this article, I am going to demonstrate how you can write queries in SQL Server to handle these scenarios quickly. For more information, click here. 

Getting Starting with Database Engine Tuning Advisor in SQL Server

There are different techniques to optimize the performance of SQL Server queries, like keeping required and updated statistics, creating required indexes, partitioning tables, etc., but wouldn’t it be great if we had some recommendations before we started planning or optimizing queries so that we didn’t have to start from the scratch every time and in every scenario? This is where you can use the Database Engine Tuning Advisor utility to get recommendations based on your workload. I will be talking about Database Engine Tuning Advisor, how it works and its different interfaces, in this article series. For more information click here for Part 1 and here for Part 2.

Importance of Statistics and How It Works in SQL Server

Statistics refers to the statistical information about the distribution of values in one or more columns of a table or an index. The SQL Server Query Optimizer uses this statistical information to estimate the cardinality, or number of rows, in the query result to be returned, which enables the SQL Server Query Optimizer to create a high-quality query execution plan. For example, based on these statistical information SQL Server Query Optimizer might decide whether to use the index seek operator or a more resource-intensive index scan operator in order to provide optimal query performance. In this article series, I am going to talk about statistics in detail. For information click here for Part 1 and here for Part 2.

Backup and Restore strategies in SQL Server

There are several high availability solutions that can be used with SQL Server, like AlwaysOn, Fail-over clustering, or Database mirroring. While these high availability solutions ensure maximum uptime for your databases, you need to setup backup and restore strategies to recover the data or minimize the risk of data loss in case a failure happens. In this article series, I am going to discuss backup and restore strategies in SQL Server in detail. For more information click here for Part 1 and here for Part 2.

Importance of Recovery Model in SQL Server and why its important

Have you ever wondered, especially in the case of a data warehousing scenario, why the transaction log file grows bigger and bigger and sometimes even much bigger than your actual database's data files? What caused it to happen? How do you control it? How does the recovery model of a database control the growing size of the transaction log? These are some of the questions I am going to explain to you in this article. For information click here.

Getting Started with Hashing in SQL Server

In my most recent articles, I’ve talked about encryption in detail and demonstrated its usage at the entire database level with Transparent Data Encryption and at the column level with granular\cell level encryption. In this article, I am going to discuss hashing in SQL Server and how it is different from encryption. For more information click here.

Getting started with Power View Reports with SharePoint, Excel and SQL Server

Microsoft Business Intelligence stack provides several tools for analyzing data from different perspectives, in different format and different ways. Power View is a new addition to this arsenal which provides intuitive ad hoc reporting capabilities for business users to provide an interactive data exploration, visualization, and presentation experience. But how does it work and how to get started with it? For information click here.

Backup and Restore with Cloud services (Windows Azure Blob Storage Service) using PowerShell

The two main PowerShell cmdlets introduced in SQL Server 2012 to do backup and restore operations are Backup-SqlDatabase and Restore-SqlDatabase. These cmdlets have been further enhanced and new cmdlets have been introduced in SQL Server 2012 SP1 CU4 to allow backing up your database to Windows Azure Blob storage and restoring from there as needed.  In this tip we look at how to write PowerShell scripts for backing up and restoring a database to Windows Azure Blob storage. For more information click here.

Backup and Restore SQL Server databases with Azure cloud services and SMO

As DBAs, we don't only have the responsibility to backup databases on regular basis, but also to ensure that backup drives and tapes are secure so databases can be restored when needed. I heard that SQL Server 2012 supports backups and restores using a Windows Azure Blob Storage account.  How does this work and how do I get started using SMO? For more information click here.

SQL Server Database Backup and Restore with Windows Azure Blob Storage Cloud Services using T-SQL Commands

SQL Server 2012 introduces a new feature to issues backups on the Windows Azure Blob Storage service directly and restore from there when needed. But how does it work and how to get started using T-SQL commands for backup and restore operation? For more information click here. 

Getting started with SQL Data Sync

Windows Azure SQL Database has a feature called SQL Data Sync which synchronizes data across multiple SQL Server and Windows Azure SQL Database instances. SQL Data Sync is a cloud-based data synchronization service built on Microsoft Sync Framework technologies. It provides single direction as well as bi-directional data synchronization and data management capabilities allowing data to be easily shared across Windows Azure SQL Databases across multiple data centers or between on-premises SQL Server databases and Windows Azure SQL databases. For more information refer to below articles:

Understanding SQL Data Sync for SQL Server

Getting started with SQL Data Sync

Managing SQL Server Services with PowerShell

PowerShell provides a command-line shell and scripting language (built in the .NET Framework) especially designed for administrative task automation and configuration management. PowerShell includes commands (called cmdlets) to administer SQL Server along with having cmdlets for administering other Microsoft server products. This gives administrators a common scripting language across servers’ platforms. For more details click here.

New Enhanced Column Store Index in SQL Server 2014

Column Store Index, which improves performance of data warehouse queries several folds, was first introduced in SQL Server 2012. Unlike regular indexes or heaps, which store data in B-Tree structure (in row-wise fashion), the column store index stores data in columnar fashion and uses compression aggressively to reduce the disk I/O needed to serve the query request. Apart from the benefits it provides, it has several limitations in SQL Server 2012. SQL Server 2014 enhances the columnstore index and overcomes several of the earlier limitations. In this article, I am going to talk about the new enhanced columnstore index feature in SQL Server 2014. For more information click here.

SQL Server Data Tools in SQL Server 2012

As developers, we have always wanted to have a single Integrated Development Environment (IDE) for not only writing application code but to also to write database code in a single place without switching the environments. SQL Server Data Tool (SSDT) is a new feature in SQL Server 2012 that provides this capability primarily intended for developers and lets developers write their application as well as database code in a single development environment of Visual Studio. For more information, click here for part 1 and click here for part 2.

 

Managing Slowly Changing Dimension with MERGE Statement in SQL Server

In my last article I talked about Slowly Changing Dimension (SCD) Transformation, which is available out of box in the SSIS toolkit and can be used easily and configured quickly for managing smaller slowly changing dimension. The Slowly Changing Dimension Transformation is good if you want to get started easily and quickly but it has several limitations (I talked about these limitations in my last article, Managing Slowly Changing Dimension with Slow Changing Transformation in SSIS) and does not perform well when the number of rows or columns gets larger and larger.

In this article I am going to talk about some of the alternatives that you can take for managing larger slowly changing dimensions. For more information click here.

Partitioning in SQL Server: Managing Sliding Window Scenario

Earlier articles of this series discussed what partitioning in SQL Server is; the different kinds of partitioning options, why and when we should go for partitioning and the benefits a partitioned table/index provides. Then I talked about different partitioning concepts like partition function, partition scheme, guidelines on choosing a partitioning column and creating a partition on table or index. We also learned about partitioned index and how an index is aligned and storage aligned with the base table partitioning, with an example.

This article provides you the steps and guidance needed to manage a sliding window scenario, with an example. For more information, click here.

Partitioning Using the Wizard in SQL Server

In my earlier articles of the series, I discussed what partitioning in SQL Server is; the different kinds of partitioning options that we have, why and when we should go for partitioning and what benefits partition table/index provides. Then I talked about different partitioning concepts, such as partition function, partition scheme, guideline on choosing partitioning column and creating partition on a table or index. We also learned about partitioned index and how an index is aligned and storage aligned with the base table partitioning.

In my last article of this series, I provided a step-by-step guide on creating a partition table/index using T-SQL commands. In this article I am going to provide you a step-by-step guide on creating partition table/index using the Wizards in SQL Server Management Studio (SSMS) and talk about Partition Aligned Index Views. For more details, click here.

Partitioning in SQL Server - Part 3

In my earlier articles of the series, I talked about what partitioning is in SQL Server; the different kinds of partitioning options, why and when we should go for partitioning and the benefits partition table/index provides. Then I talked about the different partitioning concepts like partition function, partition scheme, choosing partitioning columns and creating a partition on a table or an index. We also learned about partitioned index and how an index is aligned and storage aligned with the base table partitioning.

In this article of the series, I am going to provide a step-by-step guide on creating a partition table/index. For more information click here.

Partitioning in SQL Server - Part 2

When a table grows large or huge in size it becomes really difficult to load new data, remove old data, maintain indexes and queries involving this table, and runs extremely slow. In this situation, we can partition the table, which essentially breaks the table and its indexes into logically smaller chunks called partitions. Further, with a partitioned table, maintenance works, lock escalation settings and compression settings can be applied individually at each partition rather than on a single huge table. Not only that, Query Optimizer references only partitions needed to serve the query requests and eliminates the partitions that don’t contain data needed by the query during query optimization and execution, and also it uses parallelism to process multiple partitions in parallel. SQL Server 2008 introduced partition table parallelism for better performance and for better resource utilization (of modern multi-processors hardware). With SQL Server 2012, we are now allowed to even create up to 15K partitions on a single table.

In my last article of the series, I discussed what partitioning in SQL Server is, the different kinds of partitioning options available, why and when we should go for partitioning and all of the benefits partition table/index provide. In this article I am going to examine the different concepts of partitioning in SQL Server and provide a step-by-step guide on creating a partition table/index. For more information click here.