18Jul 2016 by Grant Fritchey 14 Comments

Common Table Expression, Just a Name

SQL Server, SQL Server 2016, T-SQL
The Common Table Expression (CTE) is a great tool in T-SQL. The CTE provides a mechanism to define a query that can be easily reused over and over within another query. The CTE also provides a mechanism for recursion which, though a little dangerous and overused, is extremely handy for certain types of queries. However, the CTE has a very unfortunate name. Over and over I've had to walk people back from the "Table" in Common Table Expression. The CTE is just a query. It's not a table. It's not providing a temporary storage space like a table variable or a temporary table. It's just a query. Think of it more like a temporary view, which is also just a query. Every time I explain this, there are people who don't…
Read More
11Jul 2016 by Grant Fritchey 26 Comments

Same Query, Different Servers, Different Performance. Now What?

SQL Server, SQL Server 2016, T-SQL
Based on the number of times I see this question on forums, it must be occurring all the time. You have two different servers that, as far as you know, are identical in terms of their options and setup (although not necessarily in terms of power, think a test or pre-production system versus production). On these servers you have a database on each that, as far as you know, is the same as the other in terms of options, objects, maybe even data (although, this does mean that you have unmasked production information in your QA environment, which potentially means you're going to jail, might want to address this, especially now that I've told you about it, mens rea, you're welcome). On each database you run, as far as you know, the exact same query (whether…
Read More
05Jul 2016 by Grant Fritchey 11 Comments

Carpenters vs. DBAs

Professional Development
Let's get the caveat out of the way up front, I work for a tool vendor. If you look around at the tools landscape for the Microsoft Data Platform, it's really interesting. There are a few tools vendors, primarily clustered around monitoring tools, and then there are a bunch of point tools for helping with various aspects of operations against the Data Platform (mostly SQL Server). Some of these are free tools. Some are pay only. Some are a mix. There are variables in the quality of these tools, and I'm sure not going to comment on that. Instead, I find one thing really interesting. Let's step back a bit. My neighbors have both worked as carpenters (well, one carpenter, and one general contractor who also does carpentry). They both…
Read More
28Jun 2016 by Grant Fritchey 6 Comments

CASE Statement in GROUP BY

SQL Server, SQL Server 2016
Set based operations means you should put everything into a single statement, right? Well, not really. People seem to think that having two queries is really bad, so when faced with logical gaps, they just cram them into the query they have. This is partly because SQL Server and T-SQL supports letting you do this, and it's partly because it looks like a logical extension of code reuse to arrive at a query structure that supports multiple logic chains. However, let's explore what happens when you do this on particular situation, a CASE statement in a GROUP BY clause. You see this a lot because a given set of data may be needed in slightly different context by different groups within the company. Like many of my example queries, this…
Read More
13Jun 2016 by Grant Fritchey 9 Comments

Choosing the Right SQL Server Edition

SQL Server 2016
Post #6 of #entrylevel #iwanttohelp in support of Tim Ford's (b|t) beginner's initiative. If you're just getting started with SQL Server, the choices you have in front of you are legion. Which drive do you install your instance on? Which drives hold the databases? How many files do you need for a database? What do the tables look like? Which column or columns should be the primary key? Clustered index? Stored procedures? In-Memory? MAXDOP? Et, as they say, cetera. Ad, as they also say, nauseum. Before any of that though, you need to pick the correct type of SQL Server to install. That's right, just saying "SQL Server" is not enough. You must pick between: SQL Server Developer's Edition SQL Server Express Azure SQL Database SQL Server Standard SQL Server…
Read More
06Jun 2016 by Grant Fritchey 2 Comments

Elastic Query in Azure SQL Database and Views

Azure
The question came up, how do the constructs necessary for Elastic Query within Azure SQL Database affect your ability to create views that join across databases. Since I haven't tested this myself, and I haven't seen anyone else doing anything with it yet, I decided to set up a test. I recreated my cross database queries using the scripts posted here. Let's create a view: CREATE VIEW dbo.JoinedView AS SELECT dt.Val, dt2.Val AS Val2 FROM dbo.DB1Table AS dt LEFT JOIN dbo.DB2Table AS dt2 ON dt2.ID = dt.ID; If I run the query, I get back results. Done. We can create views that join between Azure SQL Databases... But, views are all about masking right? What if I wanted to change the name of the table on my database. Could I…
Read More
02Jun 2016 by Grant Fritchey 2 Comments

Resources for Learning Azure Data Platform

Azure
You want to start working with Azure and the Azure Data Platform, but getting started is not easy. Just knowing where to go to find useful information isn't easy. I'm here to help. I've started a GitHub repository that is meant to provide a community-based resource that documents where and how you can learn about the Azure Data Platform. This somewhat duplicates my listing of Data Platform Instructors, but it actually frees that up so I can curate the list the way I want. I'll probably make it a ranking soon. Why not. Anyway, I want to make sure you're aware of this resource so that you can consume it or contribute to it. Please help me out if you have something to contribute. Otherwise, please help yourself to what…
Read More
01Jun 2016 by Grant Fritchey 1 Comment

Independent Azure Data Platform Instructors

Azure
The Azure Data Platform is taking off. I'm seeing more and more interest on the forums, at conferences and in my personal interactions. I've been teaching the data platform for six years. Almost as soon as it was available, I started working with it, putting up blog posts and setting up sessions. I've had stuff in production on the platform for almost that long too. I'm an advocate and, I hope, an independent voice on the topic. By independent in this case, I mean non-Microsoft. Don't get me wrong, most of the people I learn from work for Microsoft. They are excellent instructors and more knowledgeable on the topic than I'll ever be. I'm not questioning the ability of Microsoft people to deliver the very best Data Platform content. I…
Read More
31May 2016 by Grant Fritchey 3 Comments

Precedence Goes to Query Store or Plan Guide?

SQL Server 2016
While presenting at SQLDay in Wroclaw, Poland, on the Query Store, I was asked a pretty simple question, which takes precedence, the Query Store or a Plan Guide? One of my favorite answers to questions is "I don't know" because it gives me the opportunity to learn. Let's figure this one out together. I'll post the code to recreate this experiment within AdventureWorks at the end of the article. I'm doing this because the code for forcing execution plans using Plan Guides can be pretty doggone long (you may need to generate your own XML from a plan on your own system, fair warning). I have a stored procedure that I use frequently to demonstrate parameter sniffing and bad parameter sniffing, AddressByCity (listed below). This query when passed the value…
Read More