05Apr 2016 by Grant Fritchey 3 Comments

Views and Simplification

SQL Server, SQL Server 2016, T-SQL
I've been getting lots of questions on views lately. Must be something in the water. Because SQL Server allows you to treat a view as if it was a table, lots of people pretty much assume that it is a table since they get to treat it that way. The thing is, a view is not a table. It's a query. Let's explore this just a little bit. Here's a relatively straight forward view: CREATE VIEW dbo.PersonInfo AS SELECT a.AddressLine1, a.City, a.PostalCode, a.SpatialLocation, p.FirstName, p.LastName, be.BusinessEntityID, bea.AddressID, bea.AddressTypeID FROM Person.Address AS a JOIN Person.BusinessEntityAddress AS bea ON a.AddressID = bea.AddressID JOIN Person.BusinessEntity AS be ON bea.BusinessEntityID = be.BusinessEntityID JOIN Person.Person AS p ON be.BusinessEntityID = p.BusinessEntityID; GO I can query this view like this: SELECT * FROM dbo.PersonInfo AS pni…
Read More
01Apr 2016 by Grant Fritchey No Comments

Speaker of the Month: April 2016

Professional Development
THIS IS NOT AN APRIL FOOL POST! Seriously. My Speaker of the Month for April 2016 is Keith Tate (b|t) and his session at SQL Saturday Chicago called Profiler is Dead, Long Live Extended Events. I actually suspected very strongly from the start of the session that it was going to be good. The reason for this, Keith was having issues with his machine, but he started the session anyway. It was an excellent beginning. Then, he started to talk about Extended Events and use his slide deck to emphasize the points he was making, and it was wonderful. For example, as he talked about the way the number of events has grown in each version of SQL Server since 2008, he used larger and larger fonts with the bigger…
Read More
29Mar 2016 by Grant Fritchey 2 Comments

Do You Teach Azure Data Platform?

Azure, Professional Development
I offer instruction on the Azure Data Platform, and have for about six years, since shortly after it came out. I started using Azure SQL Database (although it had a different name then) Day 1. I know a few other people who don't work for Microsoft, but have been actively pursuing Azure SQL Database, SQL Server on Azure VMs, and pretty much all the Microsoft Data Platform. I'm not counting the BI people who have dived into PowerBI and related tech. The BI people, who are generally pretty smart, jumped on Azure with both feet. I'm talking about the data platform aspect of Azure. The people that I know who regularly teach classes are (in no particular order, sheesh, you people): Karen Lopez(b|t) Denny Cherry(b|t) Jes Borland (b|t) Thomas LaRock (b|t) Joe D'Antoni…
Read More
28Mar 2016 by Grant Fritchey 8 Comments

Query Store and Optimize For Ad Hoc

SQL Server 2016
I love presenting sessions because you get so many interesting questions. For example, what happens with Optimize for Ad Hoc when Query Store is enabled? Great question. I didn't have the answer, so, on to testing. For those who don't know, Optimize for Ad Hoc is a mechanism for dealing with lots and lots of ad hoc queries. When this is enabled, instead of storing an execution plan the first time a query is called, a plan stub, basically the identifying mechanisms, for the plan is stored in cache. This reduces the amount of space wasted in your cache. The second time the query is called, the plan is then stored in cache. I'm going to set up Optimize for Ad Hoc and Query Store and, to clean the slate,…
Read More
25Mar 2016 by Grant Fritchey 4 Comments

Happy Dance!

Azure
I'm all like: Because I saw this on an eval: I've been trying to ramp up to take advantage of my MSDN subscription and haven't known where to start. I don't have that excuse now. And then I was all like: Because: We are moving a lot of stuff to Azure. I had some experience using SQL Azure but felt blind when doing it. Grant made me feel better about my experience as it is very much like he explained. and: Azure is becoming a REAL THING. It's nice to get such a great primer of it. <calming down> I'm quite pleased to see that Azure sessions are getting such an improved reception. <SQUEEE>
Read More
24Mar 2016 by Grant Fritchey No Comments

PASS Board 2016: Update #2

PASS
Time flies. I didn't notice that I hadn't posted an update in February. There’s been a lot going on since I last posted! I've attended the executive committee meetings. I've also hosted my first board meetings and I took part in my first Town Hall. I've been working with PASS HQ to set the agenda for upcoming meetings and we're starting the budgeting process for FY2017. I've got a couple of blog posts I've put together on the Board Elections (for my blog) and on the goals and plans for the EVP (on the PASS blog) that are going through an editing process. I should be able to share those with you soon. Today, I'm going to discuss a couple of things that I've been mulling over. They're things that…
Read More
21Mar 2016 by Grant Fritchey 56 Comments

Cross Database Query in Azure SQL Database

Azure
You can't query across databases in Azure SQL Database... or can you? Let's check. I've created two new databases on an existing server: I've created two tables on each respective database: CREATE TABLE dbo.DB1Table ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Val VARCHAR(50) ); CREATE TABLE dbo.DB2Table ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Val VARCHAR(50) ); Now, let's query the DB2 table from the DB1 database: SELECT * FROM DB2.dbo.DB2Table AS dt; And here's the lovely error message: Msg 40515, Level 15, State 1, Line 35 Reference to database and/or server name in 'DB2.dbo.DB2Table' is not supported in this version of SQL Server. So, like I said, you can't do three part name cross-database queries in Azure SQL Database... oh wait, that's not quite what I said…
Read More
17Mar 2016 by Grant Fritchey 2 Comments

Opportunities To Talk

Misc, PASS
It's weird being an introvert who likes to talk to people, but what can I do. I like talking to people. I have a number of upcoming trips, quite literally all over the world, that provide us with the opportunities to get together and have a chat. First, I'll be at SQL Saturday Boston (the 500th SQL Saturday event, HUZZAH!), this weekend, March 19th 2016. I'll be talking about the Query Store and I'll be doing a presentation for PASS since this is a milestone event. The first SQL Saturday event in Boston was #34, six years ago, which I helped organize. It's been quite the journey. I'm going to SQL Saturday Madison on April 9th. I'll be talking about the Query Store and how to automate your database deployments.…
Read More
14Mar 2016 by Grant Fritchey 1 Comment

Leadership Lessons

Professional Development
Not for you, for me. I'm sure you've heard the statement: Praise in public. Criticize in private. I agree with this approach. However, I find it extremely difficult to do. It's one of the fundamental proofs that all leadership, all life for that matter, is about constant practice and discipline. It's not enough to know something. It's not enough to practice something occasionally. To get good at this stuff, you need to practice a lot. Let me tell you about a recent failure on my part. My 17 year old daughter had friends for a sleepover (yeah, they still do that). She makes her own breakfast and starts eating. I remind her to ask her friends what they want. She does so in this really irritated manner. Of course, the…
Read More
07Mar 2016 by Grant Fritchey 2 Comments

SQL Server Backups Are A Business Decision

SQL Server, SQL Server 2016
Blog post #3 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel Read more about Tim's challenge here. It's very easy to think of SQL Server backups as a technical problem. You have so much stuff going on, BACKUP DATABASE commands, recovery models, BACKUP LOG commands, Differential backups. Getting them all into the correct order and automating the processes sure seems like a technical problem. It isn't. It's all about the business. If you're taking on the duties of a DBA whether you're an accidental DBA, a reluctant DBA or you were voluntold into the DBA position, you need to plan to sit down with responsible parties from the business and get an understanding with them regarding RPO and RTO. RPO is a TLA for Recovery Point Objective. The easiest way to…
Read More