Static Data - how many rows max?

robinw

Hi

We are currently experimenting with including static data in our source control repos.

Question is how much static data would be a realistic maximum before the pull speed is compromised?

Lets say we have a table with 10 columns and mostly made up of char data types, how many rows before performance or functionality starts to degrade?

Thoughts?

Thanks in advance.

AlexYates

SELECT length
FROM string
WHERE environment = 'unknown'

As a basic rule try to keep it under 1000 rows. That said, obviously the number of cols, data types and number of other static data tables has a major effect too so (ahem) it depends.

For a table with 10 cols and inexpensive data types you can probably stretch it quite a bit more than 1000 before hitting massive problems. I've seen customers get up to the 10s of thousands without too much hassle. At this point you might want to think about whether your source control system can handle files that big without falling over.

For most people the issue they hit is maxing out the 2GB RAM limit in SSMS as SQL Source Control parses and scripts out the data in memory. However, you can also toggle the data comparison on and off if performance is an issue.

Bottom line: give it a whirl and see.

robinw

Hi Alex. Thanks for your reply.

We've already given it a whirl and found that our recordset (well over 10000 rows I might add) had massive issues in trying to download and compare.

I'll take what you've recommended, have a word with the developers and see if there's a workaround for their requirements or omit the static data completely.

Thanks again!