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
25May 2016 by Grant Fritchey 8 Comments

A Moment For Reflection…

Misc
While this is a personal blog, I try to keep it focused on either technical topics or personal development and leadership related to technical topics. This post is a complete deviation from all of the above. Please, read it anyway. I went to Auschwitz and Birkenau. Words are failing me here. Before I went, I was terribly conflicted about the trip. I had the opportunity to visit another Nazi concentration camp, Dachau, and I deferred. I know horror was committed there. Why should I voluntarily subject myself to it? As part of a trip to Poland, Aaron Bertrand suggested we should take a couple of extra days to see the sights, including visiting Auschwitz. I didn't want to go. Then I started thinking. Obligation. Honor. Remembrance. Humility. Respect. I truly…
Read More
24May 2016 by Grant Fritchey 3 Comments

On the Buckeye Blitz!

T-SQL
In just a few weeks I'll be doing the Buckeye Blitz. That is, a tour of user groups in Ohio (aka, the Buckeye state, after a tree, not a sports team), one per day across a week. Here's how it breaks down: Cleveland: 6/13 Toledo: 6/14 Columbus: 6/15 Cincinnati: 6/16 I'll be talking on this topic at each of the groups: Change Your Habits: Tips to Tune Your T-SQL T-SQL proides many different ways to accomplish the same task, and as you might expect, some ways are better than others. In this session, you will learn specific techniques, that when followed make you a better T-SQL developer. The session is jam-packed with practical examples and is designed for administrators and developers who want to bring their T-SQL skills to the…
Read More
23May 2016 by Grant Fritchey No Comments

PASS Board 2016: Update #3

PASS
I didn't post an update last month, but I did do something more important: I elicited your feedback on a question the Board needs to act on. Here's what I've been up to. First, based on your feedback (thanks) and conversations that I've had with members of the Board, I'm putting together recommendations for how we deal with payment and PASS-branded (but not PASS run, that's already covered in the By-Laws) events. I'll be presenting that in June at the in-person Board meeting. Speaking of the Board meeting, I've also been soliciting topics (although really, my priceless partner at PASS HQ, Vicki, is doing all the real work). We're also preparing the budget for FY 2017 (again, the real work is being led by an equally priceless Sandy at HQ). Beyond a series of…
Read More