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

Query Store, Forced Plans, and New Plans

Azure, SQL Server 2016
I love questions. I recently received one about new plans in the Query Store (available in Azure SQL Database now and in SQL Server 2016 after June 1). Let's say you have selected a plan that you want to force. You set it up. Now, let's say the plan ages out of cache or even goes through a recompile. During the recompile, due to out of date statistics or skew in the statistics, you would, under normal circumstances, get a new plan. However, with Query Store and plan forcing, the plan that's going to be used is the plan that is being forced. But, does that other plan, the one not used, get stored in Query Store? I have no idea. Let's find out. The Setup To start with, a small stored procedure…
Read More
12May 2016 by Grant Fritchey 9 Comments

Use The Correct Data Type

SQL Server, SQL Server 2016, T-SQL
Blog post #5 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel. Read about it here. Saying that you should use the correct data type seems like something that should be very straight forward. Unfortunately it's very easy for things to get confusing. Let's take a simple example from AdventureWorks. If I run this query: SELECT a.ModifiedDate FROM Person.Address AS a WHERE a.AddressID = 42; The output looks like this: 2009-01-20 00:00:00.000 Normal right? You see the year, the month and the day followed by the time in hours, minutes, and seconds as a decimal. Ah, but there is an issue. This query is supposed to be for the reporting system, and the business only cares about the date that the values in the Person.Address table have been modified, so they don't want…
Read More
03May 2016 by Grant Fritchey 5 Comments

Implicit Conversion and Performance

SQL Server, SQL Server 2016, T-SQL
Letting SQL Server change data types automatically can seriously impact performance in a negative way. Because a calculation has to be run on each column, you can't get an index seek. Instead, you're forced to use a scan. I can demonstrate this pretty simply. Here's a script that sets up a test table with three columns and three indexes and tosses a couple of rows in: CREATE TABLE dbo.ConvertTest ( BigIntColumn BIGINT NOT NULL, IntColumn INT NOT NULL, DateColumn VARCHAR(30) ); CREATE INDEX BigIntIndex ON dbo.ConvertTest (BigIntColumn); CREATE INDEX IntIndex ON dbo.ConvertTest (IntColumn); CREATE INDEX DateIndex ON dbo.ConvertTest (DateColumn); WITH Nums AS (SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT 1 )) AS n FROM master.sys.all_columns ac1 CROSS JOIN master.sys.all_columns ac2 ) INSERT INTO dbo.ConvertTest (BigIntColumn, IntColumn, DateColumn ) SELECT Nums.n, Nums.n,…
Read More