Press "Enter" to skip to content

Month: July 2022

The Siren Song of Platform-Portable SQL

Published 2022-07-18 by Kevin Feasel

Shane O’Neill gets on the soapbox:

Part of why I’m ranting about database-ubiquitousness SQL instead of ORMs is that I’ve also witnessed SQL code that has to work everywhere

I’m going to call this SQL “DUh SQL” from now on since I only have so many vowels on my keyboard and shouldn’t waste them re-writing the word “ubiquitousness”

Them: “Nope, we can’t use DATEFROMPARTS; it’s not available in other databases! Yeah, we use GETDATE() everywhere; what’s your point? WHAT!?!”

I strongly agree with this. Lowest common denominator SQL just means your code is likely to perform poorly in all environments. Even if you know you need to support multiple platforms, it’s better to get the most out of each and have whichever data access layer implementation you inject figure out the details of how to do it.

Comments closed

Working with Synapse Link for SQL

Published 2022-07-18 by Kevin Feasel

Steve Howard gives us an overview of a preview:

Azure Synapse Link for SQL greatly simplifies analytics pipelines as Microsoft manages the orchestration process for you. Since being announced at Microsoft Build, many of you have had the opportunity to try it out in a POC so now seems like a good time to take a deep dive on some implementation aspects that may save you time later.

This blog post will assume you have experience with Synapse dedicated SQL pools and that you have some basic working knowledge of Synapse Link for SQL from following the quick start or from doing an initial POC.

Looking at this, I am a bit concerned about what it means to sync actively changing tables, especially ones large enough to benefit from being in a dedicated SQL pool. “Just reload all the data” may be the right answer but it doesn’t sound like a convenient one.

Comments closed

SQL Server 2012 Migration Plan

Published 2022-07-18 by Kevin Feasel

Lee Markum says farewell to SQL Server 2012:

Today is end of support for SQL Server 2012. May it rest in peace.

Migrating a SQL Server can be a lot of work. There are so many things to think about. It’s a pain.

It is a pain but Lee does have a few tips on how to get started with a migration plan. And as you get closer to present-day SQL Server (remember: there were 4 versions of SQL Server released after 2012 and we’re getting another one this year), being able to set up distributed Availability Groups for version migration can make life a lot easier for you.

Comments closed

Quality Checks for Power BI Visuals

Published 2022-07-18 by Kevin Feasel

Meagan Longoria has a checklist:

For more formal enterprise Power BI development, many people have a checklist to ensure data acquisition and data modeling quality and performance. Fewer people have a checklist for their data visualization. I’d like to offer some ideas for quality checks on the visual design of your Power BI report. I’ll update this list as I get feedback or new ideas.

Read on for the list, as it’s a good one. For the most part, these also apply to visuals created in other tools.

Comments closed

Proofs of Concept and Pilots

Published 2022-07-15 by Kevin Feasel

Kenneth Fisher strikes a chord:

If your POC does not follow your companies best practices and standards then it is not a valid POC.

There are way to many settings that will change it’s performance, cause security issues, etc. On top of that, almost every POC I’ve ever seen ends up becoming the test environment if not the actual production environment. So all of those little compromises end up in your actual, non POC environment because it’s way too much work to fix them now. You should have said something when we set this up.

To use one of my favorite lines, “Short answer: yes with an if; long answer: no with a but.”

Before I get to the answer, I do want to differentiate between a proof of concept and a pilot. The idea of a proof of concept is to see if I can make this thing work. Can I get these two processes to talk to each other? Can I build a website which accepts user input and displays something? Can I get this idea from my head into code? Can I process 500,000 records per second using our existing hardware? One important thing about a proof of concept is that it always has the possibility of failure. “No” is a valid answer here based on the conditions. By contrast, a pilot is a starter for the full project. You might work with one business unit instead of all of them, migrate a small amount of traffic to the new system, or only handle data from a single branch office. Also, you want that answer as fast as is reasonable so that your business decision-makers can make business decisions on that information. By contrast, when we do a pilot, we already know the answer is yes; we just need to build it out and answer the technical details along the way.

Returning to the line above: Yes, I agree with Kenneth if your company lacks the discipline to differentiate between proofs of concept and pilots (and that’s not as denigrating a remark as it sounds…though it’s somewhat denigrating). No, do not follow the same practices for a proof of concept that you would for a full product, but you need to ensure that code gets destroyed and you start over with new code which does follow those practices.

2 Comments

CREATE VIEW with Variables and the XY Problem

Published 2022-07-15 by Kevin Feasel

Ronen Ariely tackles a challenge:

how to pass parameters to view in SQL Server. I need create views with variable as below.

Create view view_name asSelect * from table where tas_id = V_Taskid

V_taskid is variable which are passing during run time from ADF pipeline

Is it possible to pass variable like above in view[?]

Ronen provides an answer but also notes that this probably isn’t the right question. In many cases, we ask for a specific detail because we think we know what to do but are stuck. In practice, we’re stuck because we’re asking the wrong questions. Most of the time, we don’t even know the right question to ask, making things even more challenging.

Comments closed

Filtered XML Deadlock Reports with Extended Events

Published 2022-07-15 by Kevin Feasel

Grant Fritchey digs into a useful Extended Event:

One of my favorite little bits of information about Extended Events is the fact that everyone running a full instance of SQL Server has deadlock information available to them, even if they never enabled Trace Flag 1222 through the system_health session. That captures the xml_deadlock_report which has the full deadlock graph. However, what if you want to capture deadlock info, but, you’re dealing the GDPR, and transmitting query values could be problematic? Enter xml_deadlock_report_filtered.

Read on to see how it works, though note Grant’s warning that this is a non-documented event.

Comments closed

Have One Data Model per Business Area

Published 2022-07-15 by Kevin Feasel

James McGillivray offers us an important piece of advice:

I cannot stress this enough. If people are consuming your data in multiple places, the data needs to come from the same data model. That can be an Enterprise Data Warehouse, a Data Mart, a Power BI Model, or any other data source, but at some point you need to be able to track the data back to a single place. If you don’t do this, you will spend THE REST OF YOUR DAYS explaining the differences between the data models to business and customers, and reconciling the differences over and over again.

Read on to learn why this is so important.

Comments closed

Saving DateOnly and TimeOnly to Databases

Published 2022-07-14 by Kevin Feasel

Hasan Savran gives us some mixed news:

DateOnly and TimeOnly are the new members of .NET family, they are introduced in version 6. Developers have been asking to declare only a date or only a time without using the DateTime object for a long time. These two new functions will make the developer’s life easy for sure. You can declare them easily but saving them to a database can be a challenge. Before we go into those details for saving them, Let’s look at how to declare and use them in the code.

But right now, they aren’t so useful for SQL Server. Which is a shame, as there are obvious mapping candidates : DATE and TIME, respectively. My hope is that the Microsoft.Data.SqlClient library gets an update pretty soon to handle them. But read on to learn how Cosmos DB handles these new types.

Comments closed