How do I explain that SQL Server should not be used as a code repository?

Question

How do I explain that SQL Server should not be used as a code repository?

Bannister

Newbie

Points: 2
More actions
May 6, 2026 at 4:27 am

#4811584
This week my BI Developer colleague proudly showed me a new Power BI report that he'd vibe-coded. Here's how it works:
- Write a SQL query that selects the data needed for the report, concatinates it into one massive row, then format that row as a JavaScript array.
- Write your custom report as a html web-page, complete with styles and JS functions.
- Put the whole web page code file into one large string. Put the JS array containing your data from step 1 into your code string so that you now have a JS variable containing all of your raw data hardcoded into your html.
- You now have a large string of html + JS that contains your custom report complete with data! Sadly the string exceeds the length of VARCHAR(MAX), so you'll need to chop it up, and insert each chunk into a table. Now all you need to do is set the table as a data source in PBI, re-join the rows into one long string, and voilà! A custome Power BI visual in 4 simple steps!
I'm fairly new to the data engineering role (transitioned from software dev) but this is insane right? My colleage has very strong SQL skills but isn't really a programmer, so I'm guessing this is a case of 'when all you have is a hammer, everything looks like a nail'.
I don't even know how to begin trying to explain the problems with this approach to my colleague, or what to suggest as an alternative (maybe just make a custom visual using the dev tools provided by PBI?). I don't want to come off sounding condescending but I have to say something before this becomes our standard way of creating custom reports.
bloxorz

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 742130 More actions · Answer 1

Lol, that's an expensive use of creating a report and sticking it in a table. Is this a one time thing? IF so, not sure it makes sense to store the data as a variable. This is static.

If this is to be run, then hydrating that HTML instead of refreshing the report from data would likely be a more efficient use of SQL Server resources. re=running the query and chunking the data isn't something SQL Server is good at doing.

The vibe coding thing makes some sense, but maintaining htis, even with LLM help, is a time sink. A better use if a custom visual is needed might be to create one. Or try to re-use existing visuals in a create way.

Jeff Moden SSC Guru Points: 1004720 More actions · Answer 2

The resulting code was more than the 2 billion bytes that could be stored in a VARCHAR(MAX)...

Whoever it is, they need a little training on the BI and, I'll say, on the SQL Side of the house. I have a presentation where I demonstrate with working code how to reduce 100 Million rows of sales data across 10 years to a "CROSSTAB" (much more capable than a PIVOT") with totals and subtotals 7 different ways from Sunday in 19 MILLI seconds for 2 year and about 270 MILLI seconds for all 10 years using just one query. The total size of the result table (can be loaded into BI) is only 1.4 MEGA bytes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dplaut 49149 SSC Eights! Points: 909 More actions · Answer 3

dplaut 49149

SSC Eights!

Points: 909

May 7, 2026 at 2:37 pm

#4817894

FileStream?

frederico_fonseca SSCoach Points: 16323 More actions · Answer 4

dplaut 49149 wrote:

FileStream?

better not even suggest that exists - what is being done is plainly wrong, and giving them an option to make the insert work is just opening it up further for them to do it.

Steve Jones - SSC Editor SSC Guru Points: 742130 More actions · Answer 5

Filestream feels slighlty abandoned by MS, so I wouldn't recommend that. It's supposed, but no work done for a number of versions.

Ross McMicken SSCarpal Tunnel Points: 4543 More actions · Answer 6

Explain that SQL Server is a data repository only. Everything else can be done in PowerBI. PBI can do a multitude of things on the presentation side.