Unpacking and Packing Solutions with PowerApps CLI (PAC)

As a developer, I have two things I have to do: 1. Check code into source control and 2. Make sure I can use that code to deploy to any and all environments repeatably and successfully. The question then becomes, how do you do this when the development environment is in a portal?

Deploy PowerApps – Solutions vs Apps

One of the big challenges I had was how to move the app from different environments. Following best software development practices, we have a development environment in our Power Platform that uses a development database as well as a production environment that points to a different production database. This has been a multi-step process with hurdles along the way.

PowerApps Delegation & Database Design

When I write a SQL Query, I never have to worry if the way I’m writing a query will automatically create a limit on the number of records being returned if I’m not specifying TOP or OFFSET…FETCH. If my query wants to return 1 million records, it will return 1 million records.

PowerApps doesn’t work that way. It uses something called delegation to figure out if the data source can do the filter work required. Otherwise it puts a limit on the number of rows returned. By default, the value is 500 but you can change the limit to 2000, as seen below.

Working with PowerApps

I mentioned a while back that I’ve had a chance to work on a PowerApps proof of concept. It’s a little unusual for a database person like me, but it makes sense for what we wanted – a relatively simple way to expose data were collecting in a SQL Server database to allow people to view and in some cases, update what we’ve been collecting.

Working through this, I hit all sorts of problems that took a lot of baby steps for me to work through. Some of these points required my understanding of PowerApps worked and some just took me being curious and investigating on my own.

The issues came down to:

• Making database design decisions needed because of delegation limits and the inability to call a stored procedure to make things like complex selects or even writes easier.
• A misleading error as to why I couldn’t deploy the app
• Using PowerApps CLI to unpack and pack the code for deployment
• Using different SQL Server instances for different environment

This is a proof of concept still waiting for final decisions for the go-ahead. Whether we use this or not, I feel like I learned a lot from these issues that was worth sharing. I’ll share of these lessons learned so stay tuned for the links going forward.

Implicit Conversion to Smaller Data Types?

I’ve been working on a project where I have to do a lot of interesting data manipulation. Unfortunately, dealing with implicit conversions has been part of the norm. So naturally, I managed to run across an interesting scenario that had me stumped, thinking that I ran into a strange edge case.

The tl;dr version is that this wasn’t an edge case or some undocumented issue or a bug or anything other than the db engine doing its job. I was looking at one scenario and missed the rest of the clues. But it’s worth sharing how these things are easily missed.

T-SQL Tuesday #171 – Last ticket closed

Happy T-SQL Tuesday!

This month, our host is Brent Ozar (b). Brent’s mission for us: write a blog post about the last ticket you closed.

My initial reaction to this was: I’m on the development side of the house. I don’t close issues, I create them. 🙂

My second thought went to the type of tickets I get. The first type are the ones for new functionality and features. The second types are the issues, otherwise known as “bugs.” Some of them are caused by things I did, like an incorrect calculation, or are solving performance issues due to things I may not be able to predict, like cardinality estimator changes between SQL Server versions.

TOP @n Estimated Rows – Part 2

When I originally posted about the estimated cost = 100, I didn’t expect there to be a follow up. But then Magnus Ahlkvist (LI), a fellow member of #TeamConstraints, had a great comment that was worth following up on:

Was the n-value sent to the proc as a parameter or did the proc have a declare statement where it was set? If it’s the later, it makes total sense to me, because at compile time the n-value is unknown to the optimizer. If it’s a parameter and not a local variable, the initial value would be available at compile time.

I have a few times used parametrised dynamic SQL in a proc just to get the local variable value into the plan, and at other times used an OPTIMIZE FOR hint in the query, using a value substantially bigger than 100.

From <https://www.linkedin.com/feed/update/urn:li:activity:7142522604854079491/>

T-SQL Tuesday #170 – Abandoned Projects

Happy first T-SQL Tuesday of 2024!

Reitse Eskens (b | t) is our host this month. Our topic: What have we learned from abandoned SQL projects.

I definitely had a few projects that I worked on directly or indirectly over the years that got abandoned for a few reasons.

Looking Forward

It’s the end of the year so it seems appropriate that there be a look back. But I’m going to take things back a bit farther than 2023.

I was going through old digital files and cleaning them up. (No really – I was doing digital clean up. I have witnesses!!!) And I ran across this gem of a document: “Deb_the_DBA persona”. I started it back at the beginning of 2012. I think there had been one or two SQL Saturday Bostons at this point but I wasn’t really going to user group meetings even though I knew when they were. But I was starting to keep track of the community and learning who to follow so I could learn more. I kept seeing others blog and their online “personas” and kept hearing those people say “start a blog!” and finally thought to myself: if I were to do this, how would I do that? 

Estimated Rows = 100

A little while back, my coworker was having trouble with a query and asked me about a strange thing they were seeing in the execution plan: The estimated rows for the query was showing 100.

100 felt like an awfully specific number. And there were two scenarios I knew of where the SQL Server cardinality estimator immediately used that value – table variables and multi-statement table value functions with SQL 2016 compatibility or earlier or as part of an APPLY where interleaved execution doesn’t apply instead of a JOIN.

The statement in question didn’t use either so what was the issue?