What are the challenges you face when working across database platforms? Take the survey

Static Data - how many rows max?

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?

Thanks in advance.

Best Answer

  • Options
    AlexYatesAlexYates Posts: 264 Rose Gold 2
    edited June 4, 2019 4:01PM 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
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn


  • Options
    robinwrobinw Posts: 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.