Tag: SQL Server

Creating a SQL Server 2019 Demo Environment in a Docker Container

About a month ago, I learned something new. I learned how to run SQL Server 2019 in Docker and how to set up my demo environment in a container. Cool stuff! I like whales. Whales are cool.

While learning, I started writing this blog post. Then I got distracted and never finished it. This weekend, I had to set up my demo environment again. It was the perfect opportunity to update the content and finally publish this post.

(Why did I have to set up everything again? Oh, it’s a long story that involves disk cleanup and a Cathrine who likes to delete things to keep her computer tidy. Ok, it’s not really a long story. It was more like “oops, I accidentally deleted my container”.)

Anyway! Back to the actual content.

In this post, I share my approach and code snippets for:

Installing Docker
Getting SQL Server 2019
Running SQL Server 2019 in a Docker Container
Restoring Demo Databases (AdventureWorks and WideWorldImporters)

Installing Docker

I knew nothing about Docker or containers a month ago. But! I’m lucky to have smart friends :) Andrew Pruski (@dbafromthecold) wrote Running SQL Server 2019 CTP in a Docker container as part of his brilliant blog post series on containers.

I decided to start with his walkthrough and do exactly what he did. It worked pretty well for me! See below :)

Continue reading →

Azure Data Factory v2 with Mike Flasko

One of the sessions I was most looking forward to at Microsoft Ignite 2017 was New capabilities for data integration in the cloud with Mike Flasko. In that session, he talks about Azure Data Factory (ADF) v2 and its new first-class SSIS support.

After the session, I convinced Mike Flasko and Sanjay Krishnamurthi to have a chat with me :) We talked about what’s new in Azure Data Factory v2, including the updated pipeline application model with a new visual design canvas, new Software Development Kits (SDKs) for working with Azure Data Factory, the new Integration Runtime, and the ability to run SSIS packages inside Azure Data Factory v2.

Azure Data Factory v2 with Mike Flasko

Follow Mike Flasko on Twitter @mflasko, and keep an eye out for more news about ADF and SSIS! I may or may not have convinced him to do another interview with me in a couple of months :)

Thank you Mike and Sanjay for sitting down with me on a busy day! If you were unable to attend Microsoft Ignite 2017, you can now watch Mike’s session New capabilities for data integration in the cloud on-demand:

Other interviews from Microsoft Ignite 2017

Data Science with Buck Woody
SQL Server 2017 with Bob Ward
High Performance Analytics with Sunil Agarwal

High Performance Analytics with Sunil Agarwal

At Microsoft Ignite 2017, I had planned an interview with Sunil Agarwal, and was very excited about it. Then Sunil asked if he could bring Kevin Farlee. Of course! Then he asked if he could also bring their customer, Aaron Gerdeman from FIS. Even better! :)

In this interview, we chat about SQL Server 2017, Resumable Index Builds, Adaptive Query Processing, Columnstore Indexes, High Availability, Real-time Analytics, Real-time Dashboards and the SQL Tiger Team.

High Performance Analytics with Sunil Agarwal, Kevin Farlee, Aaron Gerdeman

You can learn more in the SQL Server Database Engine Blog, follow Sunil Agarwal on Twitter @S_u_n_e_e_l and follow Kevin Farlee on Twitter @kfarlee.

I’m very happy they all managed to find time to talk to me, hope you enjoy the interview! To learn more, watch Sunil and Aaron’s session Delivering high performance analytics with columnstore index on traditional DW and HTAP workloads from Microsoft Ignite 2017:

Other interviews from Microsoft Ignite 2017

Data Science with Buck Woody
SQL Server 2017 with Bob Ward
Azure Data Factory v2 with Mike Flasko

SQL Server 2017 with Bob Ward

During Microsoft Ignite 2017, I got to interview one of the nicest guys in Microsoft, Bob Ward! :)

In this interview, we chat about SQL Server 2017, SQL Server on Linux, Adaptive Query Processing, Auto Plan Correction and Columnstore Indexes.

SQL Server 2017 with Bob Ward – Microsoft Ignite 2017

You can read all of Bob Ward’s articles on his blog SQL Server According to Bob. Most of his slide decks and demos are published on aka.ms/bobwardms, and you can follow him on Twitter @bobwardms.

I know Bob had a very busy schedule at Microsoft Ignite, so I’m very thankful he was able to spend a few minutes with me! I hope you find this as interesting as I did :) If you want to learn even more, you can watch his session Experience Microsoft SQL Server 2017: The fast and the furious from Microsoft Ignite 2017:

Other interviews from Microsoft Ignite 2017

Data Science with Buck Woody
High Performance Analytics with Sunil Agarwal
Azure Data Factory v2 with Mike Flasko

Data Science with Buck Woody

I got to interview Buck Woody about Data Science at Microsoft Ignite 2017! :)

In this interview, we chat about Microsoft Business Analytics and AI (formerly known as Cortana Intelligence Suite), Artificial Intelligence in Excel, intent-based programming, Predictive Analytics, DevOps for Data Scientists and life-long learning.

Data Science with Buck Woody – Microsoft Ignite 2017

Read all of Buck Woody’s great posts on his blog Backyard Data Science and follow him on Twitter @BuckWoodyMSFT. For more career advice, read his article Your Career is Your Fault on LinkedIn.

Oh, and we did get more coffee after we recorded this :)

Interview recorded and @BuckWoodyMSFT got his extra triple quad shot coffee. Great start to Day 2 of #MSIgnite :D pic.twitter.com/6dYbCeSiQu
— Cathrine Wilhelmsen (@cathrinew) September 26, 2017

I’m so thankful that Buck took some time out of his busy schedule to chat with me, and I hope all of you enjoy this interview. If you want to learn more, watch his full session DevOps for data science from Microsoft Ignite 2017:

Other interviews from Microsoft Ignite 2017

SQL Server 2017 with Bob Ward
High Performance Analytics with Sunil Agarwal
Azure Data Factory v2 with Mike Flasko

Table Partitioning in SQL Server – Partition Switching

This post is part 2 of 2 in the series Table Partitioning in SQL Server

Inserts, updates and deletes on large tables can be very slow and expensive, cause locking and blocking, and even fill up the transaction log. One of the main benefits of table partitioning is that you can speed up loading and archiving of data by using partition switching.

Partition switching moves entire partitions between tables almost instantly. It is extremely fast because it is a metadata-only operation that updates the location of the data, no data is physically moved. New data can be loaded to separate tables and then switched in, old data can be switched out to separate tables and then archived or purged. All data preparation and manipulation can be done in separate tables without affecting the partitioned table.

Continue reading →

Using a Numbers Table in SQL Server to insert test data (T-SQL Tuesday #65)

T-SQL Tuesday #65 is hosted by Mike Donnelly (@SQLMD). There is no specific topic to write about this month, Mike simply wants us to learn something new and then write a blog post to teach it to others. I want to share something that I only recently learned, something I wish I had known about years ago, something that became part of my toolbox as soon as I discovered it: the Numbers Table. It is so simple and solves so many problems that everyone should know about it :)

A Numbers Table (perhaps most known as a Tally Table, sometimes called an Auxiliary Table of Numbers and even referred to as the Swiss Army Knife of SQL Server) is a one-column helper table that contains the numbers 1, 2, 3, 4, 5 and so on all the way up to the-highest-number-you-could-possibly-need.

It can be used to replace slower loops and row-by-row operations with faster set-based operations, generate dates, split strings, find gaps in data sets, expand data sets, insert test data and probably hundreds of other things. There are so many great and detailed articles already published about this topic, so I will stick to the T-SQL Tuesday topic of “Teach Something New” and share the two most recent things I learned: different ways to create a numbers table, and different ways to quickly insert test data by using a numbers table.

Continue reading →

Table Partitioning in SQL Server – The Basics

This post is part 1 of 2 in the series Table Partitioning in SQL Server

SQL Server Table Partitioning Cheat Sheet

There are many benefits of partitioning large tables. You can speed up loading and archiving of data, you can perform maintenance operations on individual partitions instead of the whole table, and you may be able to improve query performance. However, implementing table partitioning is not a trivial task and you need a good understanding of how it works to implement and use it correctly.

Being a business intelligence and data warehouse developer, not a DBA, it took me a while to understand table partitioning. I had to read a lot, get plenty of hands-on experience and make some mistakes along the way. (The illustration to the left is my Table Partitioning Cheat Sheet.) One of my favorite ways to learn something is to figure out how to explain it to others, so I recently did a webinar about table partitioning. (Update in 2020: The webinar has now been archived. Please contact Pragmatic Works if you would like to watch it, as they are the owners and publishers.) I wanted to follow that up with focused blog posts that included answers to questions I received during the webinar. This post covers the basics of partitioned tables, partition columns, partition functions and partition schemes.

Continue reading →

SQL Server, SSIS and Biml Data Types

The table below is a simplified mapping between SQL Server, SSIS and Biml Data Types. It does not include all possible mappings or all data types, but is meant as a quick reference while developing and learning Biml. I mainly created it as a cheat sheet for myself, but I hope other Biml developers will find it useful!

Can’t remember this post’s URL? Neither can I 😅 You can use this short URL instead: cathrinew.net/BimlDataTypes

Cheat Sheet for SQL Server, SSIS and Biml Data Types

SQL Server	SSIS	Biml
bigint	DT_I8	Int64
binary	DT_BYTES	Binary
bit	DT_BOOL	Boolean
char	DT_STR	AnsiStringFixedLength
date	DT_DBDATE	Date
datetime	DT_DBTIMESTAMP	DateTime
datetime2	DT_DBTIMESTAMP2	DateTime2
datetimeoffset	DT_DBTIMESTAMPOFFSET	DateTimeOffset
decimal	DT_NUMERIC	Decimal
float	DT_R8	Double
geography	DT_IMAGE	Object
geometry	DT_IMAGE	Object
hierarchyid	DT_BYTES	Object
image *()**	DT_IMAGE	Binary
int	DT_I4	Int32
money	DT_CY	Currency
nchar	DT_WSTR	StringFixedLength
ntext *()**	DT_NTEXT	String
numeric	DT_NUMERIC	Decimal
nvarchar	DT_WSTR	String
nvarchar(max)	DT_NTEXT	String
real	DT_R4	Single
rowversion	DT_BYTES	Binary
smalldatetime	DT_DBTIMESTAMP	DateTime
smallint	DT_I2	Int16
smallmoney	DT_CY	Currency
sql_variant	DT_WSTR	Object
text *()**	DT_TEXT	AnsiString
time	DT_DBTIME2	Time
timestamp *()**	DT_BYTES	Binary
tinyint	DT_UI1	Byte
uniqueidentifier	DT_GUID	Guid
varbinary	DT_BYTES	Binary
varbinary(max)	DT_IMAGE	Binary
varchar	DT_STR	AnsiString
varchar(max)	DT_TEXT	AnsiString
xml	DT_NTEXT	Xml

(* These data types will be removed in a future version of SQL Server. Avoid using these data types in new projects, and try to change them in current projects.)

Continue reading →