[SigmundA]

> Sql function:sorry but they are most often crap and useless, better to in-line or use TVF, and no its not code logic duplication.

Functions have helped me tremendously in SQL server, but you do have to know the issues, which can be taken advantage of to some degree.

Code reuse is the obvious use case, but due to lack of inlining up to SQL Server 2019 meant you could reduce performance compared to hand inlined case statement or whatever. Hopefully now that functions can be inlined in 2019 this will be a non issue going forward

They are an optimization barrier which can be a good thing. I have used to this my advantage to stabilize tricky queries that where using views for code reuse. The performance becomes consistent and predictable rather than going pathological on some databases even though it may be slightly slower on others.

[pjungwir]

Can an MS SQL Server expert explain why creating table variables inside sprocs is so popular? I've done lots of Postgres & MySQL (and long ago lots of Oracle), but now I find myself maintaining some MS SQL Server projects, and I see `DECLARE @intermediateResults` all over the place. I thought it was just the past developers being more familiar with imperative style than SQL, but it seems to be part of the broader MS SQL Server culture, and somewhere I came across some passing reference to read locking being a reason, but it was too brief for me to understand. Is there any reason to do this so often in MS SQL Server vs other RDMBSes?

[monkeycantype]

A great thing about Table Variables is that you can pass them as parameters to stored procedures, which gives a straight forward way to have procs do set based operations, avoiding the need to call procs in a loop.

I find this approach allows me to write smaller procs that become reusable in different contexts, effectively composable.

Be careful that for over a thousand records table variables perform poorly, so I usually dump the data into a temp table inside the proc, or a permanent table with a key unique to this execution of the proc, this could be the SPID

[SigmundA]

I use them all the time as well.

Basically they are just very convenient compared to temp tables (they are basically function / procedure scoped temp tables rather than connection scoped) and yes allow you to go for an imperative execution you can count on. They also allow you to share results across statements not just the same statement like a CTE.

I haven't used Postgres very much but SQL's declarative style is great until it isn't. When the optimizer is being dumb its real nice to have the tools to go imperative and just tell it what to in what order like you know a normal programming language.

Maybe its because many SQL server programmers are also .Net programmers and are used to just throwing results in a list or dictionary to process data. This is partially why I use them, I let SQL do it thing, but when it doesn't I can just stuff it into a table variable and do the next step with a separate isolated statement or cursor or whatever thing your not supposed to do in SQL to actually get the job done.

This goes for query hints too, which seems to be a big no no in the PG world.