Static Data - how many rows max?

robinwrobinw London, UKPosts: 17 New member
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.

Best Answer

  • AlexYatesAlexYates Cambridge, UKPosts: 255 Rose Gold 2
    edited June 4, 2019 4:01PM Accepted Answer
    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.
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Co-organisor for Data Relay
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn

Answers

  • robinwrobinw London, UKPosts: 17 New member
    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!
Sign In or Register to comment.