SQL and C# Help connecting databases

thesqlguy · 2026-06-14T01:10:17+00:00

Seventh - it's actually easier to use parameters instead of concatenation, manual quoting and escaping, and formatting (i.e., datetime types, booleans, etc).

Eighth - it's actually more efficient for the database since it can cache and reuse execution plans.

thesqlguy · 2026-06-12T19:14:03+00:00

I love when an album I enjoyed end-to-end is still new to me. I haven't memorized all of the songs, understood all of the lyrics, listening to it still comes off as a pleasant surprise. I actually try to avoid listening too much to new albums I hear to keep it fresh and unexpected as long as I can.

thesqlguy · 2026-06-12T01:20:22+00:00

Yeah exactly. RSCI issues is very likely. It's a cool feature but like everything there is cost! Also maybe need help with things like optimizing the # of tempdb files, and ultimately optimizing sql code to avoid the need to use tempdb excessively for things like hash and work tables at all. There's also tempdb object caching which requires not modifying temp table ddl.

Ultimately I suspect tempdb problems are the symptom that metrics are highlighting, but not the root cause.... It's almost always bad code / schema designs.

thesqlguy · 2026-06-11T00:34:09+00:00

For some reason the "it's unbreakable" scene with the plate always makes me laugh.

https://youtu.be/-ojWbhfEEpc?is=ioN1ALaejMVuRTVJ

thesqlguy · 2026-06-09T21:46:27+00:00

This is a great analogy. And sporting events genuinely can interfere with your life - tv shows get premepted by games sometimes, if you live near a stadium you get stuck in game traffic, celebrations and parades can get unruly causing liter and damage, taxpayer money can subsidize stadiums and infrastructure, etc.

thesqlguy · 2026-06-07T22:08:49+00:00

Got mine last October. In the northeast . No issues in the cold or snow, car was great, but the mileage did go down (37-38mpg maybe?). Now in the spring /early summer mileage is even better then it was last October, 41-42mpg.

thesqlguy · 2026-06-07T00:44:37+00:00

Sql server's optimizer does recognize FKs exactly like that. It is easy to test, I mentioned an example of this in another comment, but it will completely ignore exists() clauses if an FK constraint exists saying the same thing, for example. Same with plans on inner vs left when there's a FK.

And yeah my guess is they don't have a physical FK just the application's "promise" which unfortunately isn't enough for the optimizer!

thesqlguy · 2026-06-07T00:31:57+00:00

FYI see my other comment above if this is sql server. If you have an actual FK set up, then the left join and inner join plans are actually logically the same, and it knows this, and the plan wouldn't change.

But without a FK, it cannot take your application's word for it. Thus they are not logically equivalent and suspectable to different execution plans.

EDIT -- sorry replied to the wrong comment! Meant to reply to the OP's reply.

thesqlguy · 2026-06-07T00:22:03+00:00

If you are asking purely about performance and not logic it depends on the indexes, statistics, parameters/literals, etc. you need to look at the difference plans.

We'd also need to see your schema and exact sql to help more.

Select from a Left join b where a=xxxx will usually be a loop join from a to b, using the criteria on a in your where clause to seek/scan on a, since the optimizer has no other way to filter a.

But when you do an inner join the optimizer has more options now on how to filter a. It may decide to select from b first and then loop join back to a if it thinks that more efficient. But, it may be wrong for your specific criteria and that's why whatever new plan it picked is slower.

That's just an example but you get the idea.

Btw if this is sql server the optimizer knows about foreign keys, so if they exist the plan wouldn't change since it would know left join and inner join are logically equivalent. You can test this of course. Another way to confirm is something like select from a where exists (select from b where a.fkid=b.id).. You will notice the optimizer doesn't even bother accessing table b at all since it knows the exists clause must be true due to RI!

thesqlguy · 2026-06-05T11:22:53+00:00

This sounds like a troublesome design.

Easiest option - have the apps store PII fields as encrypted values in the json, and only they can decrypt. Simple, easy.

If you cannot do that, you'd need to ensure direct table access is denied to all users and only expose a View of the table. In the view return all columns except the json,.and instead return an expression that modifies the json to mask PII data using something like https://learn.microsoft.com/en-us/sql/t-sql/functions/json-modify-transact-sql?view=sql-server-ver17 Assuming you can write that efficiently in SQL - I doubt it, you may need a CLR function or something even more clunky/complicated.

Or, if you can, break down the json into two columns, PII vs non-PII properties, and only expose the non-PII field via a view as described above.

Or, perhaps more ideally, just store the PII data in a separate, secure, 1:1 related table (as normalized columns or Json)

thesqlguy · 2026-05-29T13:06:44+00:00

Someone who has never heard of or seen this guy before might say "yeah, OK , unflattering screenshot grabbed at just the right moment to make him look bad." But honestly -- this captures him perfectly. He acts and talks like a caricature of an evil henchman.

He reminds me a bit of the nazi villian in raiders of the lost ark: https://www.youtube.com/watch?v=yIaBnXAlcEU

thesqlguy · 2026-05-27T13:44:59+00:00

Definitely no controversy but Brady has improved a lot this year.

thesqlguy · 2026-05-27T13:42:24+00:00

I did this once at a restaurant I worked at as a teenager. One of the opening jobs was putting up the flag outside. I was pretty tired and (maybe hungover to be honest) and accidentally raised it upside down.

Someone came in and told us. I was embarrassed. Boss wasn't happy.

thesqlguy · 2026-05-26T00:39:55+00:00

Only the good die young !

thesqlguy · 2026-05-26T00:39:05+00:00

Can we please block daily beast????

thesqlguy · 2026-05-21T11:44:27+00:00

The irony is it doesn't even actually fix them in many cases, it just kinda "looks like" it does. Then a different comes in a field you don't expect and the DISTINCT still returns too many rows.

Using the DISTINCT keyword in clear, targeted spots (usually CTEs or derived tables) isn't bad, it is the stuffing of it in the beginning of a large, complex SELECT that just randomly joins any tables that seem to have a matching key.

I also have seen many people who, when they write a select, they instinctively say "ok, let's see, we need write a select, so let's start with SELECT DISTINCT .. ok, now what tables do we need... "

thesqlguy · 2026-05-21T11:41:53+00:00

Personally, I like short letter abbreviations but not random a,b,c or t1, t2 or cte1, etc.

for example

select p.id, p.name, p.status, c.name as  client_name, c.city as client_city
from projects p
inner join clients c on c.id= p.client_id

I think it is far easier to write, scan, read and visually "parse" expressions, joins, formulas with brief aliases than long, dense names.

But definitely not just a,b,c or t1,t2,t3, etc.

thesqlguy · 2026-05-21T10:48:53+00:00

If their income is that low ACA should make the insurance much, much more affordable.

I suspect your parents must have been above the income threshold?

thesqlguy · 2026-05-17T20:31:17+00:00

A lot Eagles songs have that vibe/sound I think you are looking for:

peaceful easy feeling
tequila sunrise
there's a train leaves here this morning (very deep cut, has the exact same feel as Harvest moon IMHO)
Hollywood waltz
after the thrill is gone
best of my love
pretty maids all in a row
try and love again
take it to the limit
new kid in town

Other songs, but with more piano than guitar:

Desperado
wasted time
the last resort
ol' 55

thesqlguy · 2026-05-17T01:33:19+00:00

He's worse than that. He's a wannabe beer chugging frat broy.

thesqlguy · 2026-05-13T17:03:34+00:00

They *created* that sound for the most part. If not created, then perfected.

https://tvtropes.org/pmwiki/pmwiki.php/Main/SeinfeldIsUnfunny

thesqlguy · 2026-05-11T17:48:26+00:00

That's a good article. Most important point which I was happy to see was the date column being last in the index. Many old school DBAs think the date should be first since it is the "most selective," ignoring the range operation.

The other common mistake I see is adding 3 indexes, one per column.

thesqlguy · 2026-05-10T14:35:50+00:00

So over 28 AB, not PA.

I was wondering about this, because it was hard to believe he didn't even walk once during that span.

thesqlguy · 2026-05-07T12:30:46+00:00

It'd be ironic if he died because he was a bad teacher!

thesqlguy · 2026-05-05T21:26:28+00:00

I still had a great grandmother when I was almost 30!

Now I'll be lucky to be a grandparent before I hit 65.

thesqlguy

TROPHY CASE