SQL Server Tips, Articles and Training

SQL Server resources to solve real world problems for DBAs, Developers and BI Pros - all for free. Check out tips, articles, scripts, videos, tutorials, live events and more all related to SQL Server.

SQL Server on VMWare Performance Optimization

While VMWare hosts mission critical SQL Server applications around the world, there are several configuration and design choices that can bring your databases to a grinding halt. Learn how to optimally configure & optimize SQL Servers running on VMWare.

Author: Joseph D'Antoni and Jennifer Joyce

Memory-Optimized TempDB Metadata in SQL Server 2019

TempDB Performance | Configuration - In this article we look at a new feature in SQL Server 2019 that can help improve TempDB performance.

Author: Aaron Bertrand

Time Series Data Fact and Dimension Tables for SQL Server

This article looks at the steps needed to build a data warehouse for the collection of stock data to use for time series analysis.

Author: Rick Dobson

Vote for your favorite MSSQLTips authors

Vote now and vote often for your favorite Rookie and Author of the Year!

Author: MSSQLTips

Configure SQL Server Transparent Data Encryption with PowerShell

In this article we look at how to configure and enable transparent data encryption for a SQL Server database using PowerShell.

Author: Filip Holub

Get SQL Server Delayed Durability Advantages Without Configuration Changes

In this article we look at why a SQL Server batch process runs so much faster in TempDB versus running in a user database.

Author: Simon Liew

General Purpose Tier for Azure SQL Database

The article shows how to deploy an Azure SQL database (General Purpose edition) and compare the cloud to on-premises execution times to gauge how fast the Azure virtualized environment is.

Author: John Miner

Read API Data with Power BI using Power Query

In this tip we will walk through how to load API data into Power BI using Power Query.

Author: Semjon Terehhov

SQL Server CASE Statement Example

In this article we look at different ways to use the SQL Server CASE statement to handle sorting, grouping and aggregates.

Author: Sergey Gigoyan

Using SQL Server RAND Function Deep Dive

This tip explores the concepts of random variable and distribution by introducing three named discrete probability distributions and gives practice in the use of the universality of the uniform theorem for generating random variables from specific probability distributions.

Author: Nai Biao Zhou

Collecting Time Series Data for Stock Market with SQL Server

Collecting stock symbol data over multiple years can allow you do to time series analysis on stock prices. In this tip we look at how to download stock prices for all stocks over a set period of time and load the data into SQL Server for analysis.

Author: Rick Dobson

Power Query Source for SQL Server Integration Services

In this article we look at how to use a Power Query Source to load data in to SQL Server using SQL Server Integration Services.

Author: John Miner

Simple DBCC CHECKDB process to report on database corruption for all SQL Server databases

In this tip we look at a SQL Server scripts to collect DBCC CHECKDB information and a SQL Agent job to schedule data collection. In addition, there is a PowerShell version included as well.

Author: Alejandro Cobar

How to stop and start SQL Server services

In this article we look at several different ways to stop and start SQL Server services.

Author: Daniel Farina

Different Ways to Write a Cursor in SQL Server

In this article we look at different ways you can initialize SQL Server cursors as well as a way to write a loop process without a cursor.

Author: Simon Liew

Use caution with sys.dm_db_database_page_allocations in SQL Server

This article covers the new sys.dm_db_database_page_allocations dynamic management view in SQL Server 2019 and things to be aware of when using this on large databases.

Author: Aaron Bertrand

Azure Data Factory Multiple File Load Example - Part 2

In this article we will create a metadata-driven pipeline using the ForEach activity in Azure Data Factory to load files in parallel.

Author: Koen Verbeeck

10 Items to Bring to an Onsite Interview

In this article we look at 10 things you should bring to all interviews to make sure you are prepared and land that next job.

Author: Cate Murray

Update SQL Server 2017 on Linux Container

This article covers the steps to update SQL Server with a new cumulative update when it is running on a Linux Container.

Author: Edwin Sarmiento

Azure Data Factory Pipeline to fully Load all SQL Server Objects to ADLS Gen2

In this article I will demo the process of creating an end-to-end Data Factory pipeline to move all on-premises SQL Server objects including databases and tables to Azure Data Lake Storage gen 2 with a few pipelines that leverage dynamic parameters.

Author: Ron L'Esteve

Queue table issues with Availability Groups in SQL Server

In this article we look at an approach to handle an issue with ghost record cleanup when there is a high volume of deletes occurring with SQL Server Availability Groups.

Author: Aaron Bertrand

The tale of two Azure Hubs - IoT Hub and Event Hub

In this article we look at storing messages from systems using Azure Event Hubs. We will walk through the process of collecting and reporting off of this collected data.

Author: John Miner

SQL Server UPDATE lock and UPDLOCK Table Hints

Deadlocks | Code Hint | Example - In this article we look at why deadlocks occur in SQL Server and a way to avoid deadlock situations using the UPDLOCK hint.

Author: Sergey Gigoyan

Dealing with a No NULL Requirement for Data Modeling in SQL Server

Database Design | NOT NULL - In this article we look at two different approaches to storing data in a SQL Server database when NULL values are not an option.

Author: K. Brian Kelley

5 Topics Often Neglected During Interviews

Professional Development | Interview Advice - This article covers 5 of the most common topics we have seen not been brought up organically during an interview that we would encourage you to inquire about.

Author: Erica Woods

Building SQL Server Indexes in Ascending vs Descending Order

In this tip we look at the impact of building SQL Server indexes in ascending versus descending order and the impact.

Author: Greg Robidoux

Load Time Series Data with SQL Server

Code Samples | Time Series | Step by Step - In this article we look at how to load time series data into SQL Server both for an initial load as well as adding new data.

Author: Rick Dobson

How to Load Multiple Files in Parallel in Azure Data Factory - Part 1

Multiple Delimiters | ADF Configurations | Automation - In this article we look at how to load multiple data files in parallel using Azure Data Factory with an example and a step by step tutorial.

Author: Koen Verbeeck

SQL Server Analysis Services Migration from SQL Server 2012 to SQL Server 2017

Migration | Step By Step | Code Samples - In this tip we cover the steps for a SSAS migration from SQL 2012 to SQL 2017 using backup and restore as well as using a project deployment method for tabular models.

Author: Svetlana Golovko

Power BI Advanced Q&A

Ease of Use | Configuration - In this tip we look at how to use Power BI Q and A visualization to make reports and data access more intuitive for the users.

Author: Scott Murray

Azure Event Hub Service Telemetry Example with PowerShell

Azure Event Hub | Code Samples - In this article we explore how to simulate sending telemetry data to an Azure Event Hub service with PowerShell and JSON.

Author: John Miner

Automate SFTP File Transfer with SQL Server Agent and WinSCP

SFTP | Step By Step | Scripting - In this article we cover how to transfer files using SFTP with a SQL Server Agent job.

Author: Joe Gavin

Basic Concepts of Probability Explained with Examples in SQL Server and R

Probability | Examples | Step By Step - The purpose of this tip is to provide enough knowledge of probability to evaluate pattern interestingness using SQL Server and R.

Author: Nai Biao Zhou

Real-Time Anomaly Detection Using Azure Stream Analytics

Machine Learning | Anomaly Detection | Power BI - Azure Stream Analytics offers built in machine learning based anomaly detection to monitor temporary and persistent anomalies. This anomaly detection coupled with Power BI's real time streaming service makes for a powerful real-time anomaly detection service.

Author: Ron L'Esteve

Save SQL Server Database Structure as Json

JSON | Copy, Paste and Go Code | Database Objects - In this article we look at various T-SQL scripts that can be used to generate SQL Server object structures (tables, indexes, primary keys, foreign keys, check constraints and unique keys) in JSON format.

Author: Mircea Dragan

SQL Server Maintenance Plan Shrink Database Task

Shrink Database | Step by Step - In this article we look at how to setup the shrink database task when using SQL Server Maintenance Plans.

Author: Sergey Gigoyan

AWS Cloud Costs and Usage Report

Cost Monitoring | Email Reporting - In this article we look at how to build a report that can be sent via email to notify you about our AWS resource usage.

Author: Maria Zakourdaev

Script to determine free space to support shrinking SQL Server database files

In this tip we look at a script that can be used to see how much free space there is per SQL Server database file in order to assist you with determining which files you should shrink.

Author: Ken Simmons

SQL Server Attach and Detach Database Examples

Administration | SSMS and T-SQL | Step By Step - In this article we look at how to detach and attach a SQL Server database using the SSMS GUI and using T-SQL commands.

Author: Dinesh Asanka

Using Parameters for SQL Server Queries and Stored Procedures

One of the benefits of SQL is the ability to write a query and use parameters to dynamically act upon the resultset. Depending on the situation, there can be benefits to parameterizing queries, but it is not always clear when or how to do this. In this tip we look at different ways to pass in values as parameters to queries and the advantages and disadvantages.

Author: Tim Wiseman

Concatenate SQL Server Columns into a String with CONCAT()

In this tip we look at different ways to concatenate SQL Server string data and how to deal with NULL values when concatenating strings.

Author: Chad Churchwell

Prevent SQL Server Blocking using Lock_Timeout

Concurrency | T-SQL | Code Samples - In this tip we look at a way to minimize the length of blocking using SQL Server lock timeout.

Author: Bhavesh Patel