A New Sample Database

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716539

    Comments posted to this topic are about the item A New Sample Database

  • Japie Botma

    SSCrazy

    Points: 2931

    A good idea. Maybe Microsoft should pay you consultation fees?

    But I chuckled about that first date when I read the first 2 sentences. 🙂

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • David.Poole

    SSC Guru

    Points: 75198

    @steve-2, I'm pretty sure that you started using the pubs database much earlier than SQL2014. I suspect that you used it pre-SQL Server 6.x.

    I liked the way that Adventureworks had an AdventureworksDW equivalent.

    The range of features in SQL Server is mind-boggling. Is it possible to produce a single schema that embraces all use cases,even for a defined area such as OLTP?

    For Microsoft the value in producing Adventureworks would have been in demonstrating and gaining adoption for the SQL Server features. As long as a sample database continues to aid adoption then enhancing it makes sense.

    As someone who writes the occasional article it is useful to have a sample database that is available to all that can be used as a test lab for whatever I am writing at the time.

  • Andy Leonard

    SSCrazy Eights

    Points: 9915

    Sample databases are definitely an exercise in balance. You want to show off new features. You want stability. These two requirements are sometimes mutually exclusive. Perhaps a base schema is part of the answer, and naming the feature-specific versions with easy-to-identify names. No, the Production databases will not be named in this fashion. But this is a demo database. Is it that important to cram all the features into one version? I think not.

    I worry about all the demos using AdventureWorks recorded in presentations. I think it was a difficult switch for many when people had to go to codeplex.com to get the sample databases. Not everyone is familiar or comfortable with the idea of downloading a demo database from somewhere else, especially when they've effectively been trained to expect the sample database in the installation process.

    My thoughts / suggestions:

    1. Commit to a location store sample database files forever.

    2. Create multiple versions of sample databases that highlight specific features. Name them accordingly. Include basic and more-data OLTP, basic and more-data ROLAP, and others.

    3. Invest time producing training on how and where to obtain sample databases.

    :{>

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • Robert Sterbal

    SSChampion

    Points: 10969

    If you want a significant amount of data to use in test I recommend the stack exchange database as detailed in this blog post:

    https://www.brentozar.com/archive/2014/01/how-to-query-the-stackexchange-databases/

    412-977-3526 call/text

  • SQL_Padre

    Right there with Babe

    Points: 768

    The best thing I like about the new "sample" databases is the inclusion of actual tools. The ETL package designed to take OLTP to the DW database is not only cool but could be a great learning tool. The ability to have a tool to auto insert records is great as well. Microsoft is branching out, providing more than just tables and rows!

    Good job Microsoft! (IMHO)

    SQL Padre
    aka Robert M Bishop

    "Do or do not, there is no try" -- Yoda

  • Andrew..Peterson

    SSCertifiable

    Points: 6657

    Steve, thanks for the tip on the new GitHub database. I'm sure many of us did not know it existed. At least I did not know about it.

    The more you are prepared, the less you need it.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716539

    Yeah, earlier than 2014. My typo. I was thinking 4.2 and typed 2014. I've been doing a lot of 2014 stuff for demos and my instances is .\SQL2014.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716539

    Certainly it's hard to make a stable schema, but I also think that databases are supposed to evolve over time. We don't really keep stable schemas. We add things.

    So.

    AdventureWorks. If we go to World Wide Importers, we can keep the same schema as AdventureWorks, as is. Let's add a new schema for Audit, where we have temporal tables that contains some changes. Or maybe we add a Design schema that has ideas for products and we encrypt things, have RLS, etc. We could have a Finance schema that has Stretch or other features.

    I'd like to see a stable schema that grows over time, but with new names for the database to allow us to easily determine which is the base version of SQL Server. Don't change anything that we've already built on, but expand on it.

    Like most of the applications we work on.

  • Gary Varga

    SSC Guru

    Points: 82166

    Steve Jones - SSC Editor (6/6/2016)


    ...I'd like to see a stable schema that grows over time, but with new names for the database to allow us to easily determine which is the base version of SQL Server. Don't change anything that we've already built on, but expand on it.

    Like most of the applications we work on.

    This gets my vote.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • Eric M Russell

    SSC Guru

    Points: 125032

    Starting with 2016, all editions of SQL Server (including Express Edition) are essentially feature complete, so there is no reason for this new sample database to leverage only a least common denominator approach with the physical data model design, even if it means the database won't be downward compatible.

    Let's introduce partitioning, page compression, and maybe a couple of tables using Clustered ColumnStore. Also, for those tables containing what should be protected information (ex: SSN in Employee table or CreditCardNumber in Payment table), let's do it right and demonstrate usage of symmetric key encryption, data masking, and row level security along with least privilege roles.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply