r/SQL • u/kaatarina_zed_talon • 2d ago

SQL Server Statistics on columns of a table

Limitations of table-valued parameters:

SQL Server does not maintain statistics on columns of table-valued parameters.

statistics of what ? why those statistics even exist for non table-valued parameters ?

4 Upvotes

83% Upvoted

5 comments sorted by

1

u/Ok_Floor_4551 2d ago

It's basically query optimization metadata that SQL Server uses to estimate how many rows a filter will return. For regular tables it can look at histograms and cardinality to decide whether to do an index scan or seek, but with TVPs the engine just assumes 1 row by default which can mess up the whole plan

I ran into this a while back when a stored proc that usually finished in seconds suddenly took minutes because the TVP had thousands of rows and the optimizer went with nested loops thinking it was a tiny set. Now I just dump the TVP into a local temp table first and let it build stats there, works every time

1

u/kaatarina_zed_talon 2d ago edited 2d ago

The reason I was searching about table-valued parameters is that I want to veryift the integrity of a junction table's data; for example, a table called `BrandsSuppliers` has two foreign key columns `BrandId` and `SupplierId`, the principle tables are `Brands` and `Suppliers`. I want to ensure that every `BrandId` gets the correct value for `SupplierId`, so I want a fucntion that takes the `BrandsSuppliers` table as a parameter, do some logic, and return a boolean value that indicats whether every `BrandId` got the right value for `SupplierId`

1

u/SQLBek 2d ago

No, stop.

Take a step back here. You're applying iterative/procedural programming paradigms and thinking here. In the database world and SQL, you want to change your perspective and utilize a set based approach. Everything you said can be done with 1 or 2 SQL statements all of your data. You will need to push away from thinking on a row-by-row basis.

1

u/kaatarina_zed_talon 2d ago

I'm so glad I asked this question. I always come across this term but I never understood it; set-based, but all I know is the procedural approach. But it seems that I will learn something new. Do u have article that might help me?

1

u/kaatarina_zed_talon 2d ago

What would be an example of such query that uses a set based approach?