Blog

Talk To Me At PASS Summit 2019

PASS
I say this all the time, but shockingly few people take advantage. Say hi to me. Please. I'm going to be at PASS Summit next week. I'll be presenting several times, multiple sessions, both community and Redgate. I'll be at the Redgate booth. I'll be walking the halls. I'll hang out at the Community Zone (on the Skybridge) when I can. Heck, I may even attend a session. I want to talk to you. Understand, I may not have a lot of time to sit down and have a long chat. However, please, stop me (unless I'm running because that means I'm late for something), and say hello. This is your formal, open invitation, please, talk to me at PASS Summit 2019. This is what I look like:
Read More

Well Trained Staff

Professional Development
I've had the opportunity in the last month to do a couple of different consulting visits, one private and one through my employer, Redgate. The goals of each of the visits was different, but I received an impression at each that I want to share. We couldn't be talking about two more different organizations. One is a large, global concern, headquartered in the US (named with permission, ChannelAdvisor, go here to get a job after you read about them). The other was a more mid-sized (although with quite a few more servers, as in hundreds more) concern focused in a single European country. I can't share more detail about the organizations than that. Sorry. Teaching Educated People Going into these, I expected to have to lay a lot of groundwork…
Read More

Passwords = A Good Thing

DevOps, Professional Development
Those my friends are, in my opinion, one of the single most wonderful things on earth, white chocolate macadamia nut cookies. Now, you may not like those. So, picture your own, special, favorite indulgence. Not a common indulgence either. Something truly lovely and special. Something important to you. Got it? Good. Now, I want you to equate that indulgence, whatever it might be, with the fundamental security of your systems. Let's imagine for just a moment, that you're developing a new system using the ElasticSearch database, one of the most popular data management systems on the planet right now. Did you know, by default, the basic and trial versions of ElasticSearch have security disabled? So, probably, if you're in development, you started with a trial version. If you just moved…
Read More

Every Execution Plan Is An Estimated Plan

SQL Server
I consider myself to be the most responsible for making such a huge deal about the differences between what is labeled as an Estimated Plan and an Actual Plan. I walked it back in the second edition of the Execution Plans book. Hugo and I completely debunked the issue in the third edition of the Execution Plans book. That is the one you should all be referencing now. As I like to joke, the guy who wrote the first two editions of the book was an idiot (and lest anyone take offense, let's be clear, I'm the idiot). Now, I'm trying my best to make this whole issue more clear. Let's talk about the "different" plans you can capture in SQL Server. Estimated Plan This is where you have a…
Read More

Database Fundamentals #24: Filtering Data

Database Fundamentals, SQL Server
If you've been reading these Database Fundamentals posts, you've already seen the WHERE clause because of your use of it when manipulating data with DELETE and UPDATE statements. It's also been used several times earlier in this series to limit the values returned from a SELECT statement. The primary places where people run into trouble with T-SQL is in the JOIN criteria and the WHERE clause criteria. This occurs because they don’t understand well enough what the filters and operators they’re using will do. They end up returning too much data because they didn’t us the WHERE clause or misapplied it. They also filter too much data out. Just remember, there are even more functions than we go over here in this series. While these basic operators answer most needs,…
Read More

SQL in the City/PASS Summit

Uncategorized
My great organization, Redgate, is doing things a little different this year at PASS Summit. Instead of hosting our own event, we've decided to host a pre-con at the Summit itself to pass the word on all the amazing work we're doing. It's still going to be called SQL in the City. Follow the link to see the schedule and all the excellent topics. This means, if you're coming to Summit, you can sign up for a precon that will be given by the great team at Redgate all about all the great Redgate tools. Follow this link to get registered. I'll be talking about 10 Steps you can take to make your data compliant, world-wide. You'll also get to hear from Kendra Little, Steve Jones, Arneh Eskandari, Ike Ellis…
Read More

SQL Injection Mitigation in SQL Server 2019

SQL Server
I've been teaching a lot more about SQL Injection lately (including blog posts). I've been doing this because, despite this being a 21 year-old problem with well defined solutions, we're still dealing with it. Recently, while sitting in the speaker room at Techorama Netherlands (fantastic event, strongly recommended), I had the opportunity to spend a little time with Niko Neugebauer. I was freaking out because my demos were failing (fixed 'em finally). Niko was talking to me about the new Feature Restrictions and their effect on SQL Injection in SQL Server 2019. I didn't know what he was talking about, so I had to look it up. Of course, top resource, Niko's blog. Feature Restrictions in SQL Server 2019 The Feature Restrictions in SQL Server 2019 are actually being added…
Read More

Humbled

Uncategorized
I've received several notes of thanks over the last couple of weeks. I'm not sure exactly what's prompted this sudden outpouring because it's all been about stuff I've done over the years, not anything recent. I've tried to always thank privately those who've sent something along these lines. I may not always do it though. So, just in case, let me publicly state: Thank you for saying that anything I do helps. I freely admit, I'm actively trying to help out. I'm still regularly surprised that any of it's useful. I'm quite humbled by your kind words. I truly appreciate them. I vow to continue to try to be useful. I'll do my best to earn what you've given.
Read More

Database Fundamentals #22: Using the Join Operator, CROSS JOIN

Database Fundamentals
While the CROSS JOIN is not used much, and, depending on the size of your data it can be dangerous, there are some uses for it. For example, you may want to write a query that will summarize all the sales for the companies and people in your system. You can do this using what is called an aggregate operation or a GROUP BY: SELECT c.CompanyName, p.LastName, SUM(ft.TransactionAmount) AS 'TransactionTotals' FROM Management.Company AS c JOIN Finance.FinancialTransaction AS ft ON c.CompanyID = ft.CompanyID JOIN Personnel.Person AS p ON p.PersonID = ft.PersonID GROUP BY c.CompanyName, p.LastName; This will add all the values up in the SUM operation for each company and each person that has values so that your data will look like this: The only problem with this is, you can’t…
Read More

Get the Last Actual Plan With sys.dm_exec_query_plan_stats

SQL Server, T-SQL
I've always felt responsible for making such a big deal about the differences between estimated and actual plans. I implied in the first edition of the execution plans book (get the new, vastly improved, 3rd edition in digital form for free here, or you can pay for the print version) that these things were so radically different that the estimated plan was useless. This is false. All plans are estimated plans. However, actual plans have some added runtime metrics. It's not that we're going to get a completely different execution plan when we look at an actual plan, it's just going to have those very valuable runtime metrics. The problem with getting those metrics is, you have to execute the query. However, this is no longer true in SQL Server…
Read More