Tag Archives: software-development

Where to Store Index DDL

Recently I was asked my opinion, via Twitter, on where to store the index DDL for a single database that had multiple clients with varied usage patterns.  You can see the whole thread here.

It’s a great question and there were some interesting suggestions made.  My approach to this scenario is kind of a hybrid of all the suggestions and comments.

I’ve had to deal with this kind of thing in the past and what I found worked best is to create a single file for each client that contains the client specific DDL.  I wish I could take credit for this idea, but it wasn’t mine, it belonged to a co-worker.  At first I resisted and thought it was a bad idea.  I mean really, mixing DDL for more than one object in a single script just seemed wrong and goes against every fiber of my OCD organizational self.  But in the end, this is what worked best in our environment.

Our initial thought was to include our index DDL with the table, but use variables to name the index objects that were specific to the client.  This way the index names would never collide, but then that kind of defeated the whole purpose of different indexing strategies for different clients.  Thankfully we scrapped that idea before we implemented it.

We tried creating separate files for each table that had client specific DDL in each file.  That became a nightmare when it came time to deploy and maintain.  We had to build logic in our pre & post deployment scripts to handle that.

Then we tried separating the index DDL files out by client, so we ended up with a bazillion index DDL files for each table.  Okay, may not a bazillion, but it was a lot and it was even more of a nightmare to maintain.

We settled on the approach I mentioned earlier, one DDL file per client that held all the DDL that was specific to the client, not specific to any object.  We found it was much easier to maintain and deploy.  We defaulted each of our client specific DDL files to be NOT included in the build.  When it came time to do a build/deploy for a specific client, we would set the option to include the client specific file in the build.  We were not using continuous integration, so this may not work if that is what your shop is doing.  Or it may work with just a few tweaks to your process.  It did work for our situation and it worked well.

I don’t think there is a single correct answer to this question.  Like almost everything else in our tech world, the answer is going to be, “it depends”.  Figure out what works in your environment and then go with it.  It might take a few trial and error runs to get it right, but you’ll figure out what works best over time with a little persistence.

I’d love to hear your thoughts on this.

Visual Studio 2017 Irritants From the SSIS Developer View

I was among the many Integration Services (SSIS)/Database (DB) developers out there that cheered when Microsoft finally announced support for SSIS in Visual Studio 2017.  I mean really, VS2017 was released back on March 7, 2017, but if you wanted to develop any SSIS packages in VS2017, you had to wait for the release of SSDT on August 23, 2017.  That’s more than 5 months.  All the other kids got to play with the shiny new toy right away, but we had to wait.  I know there are a ton of reasons for this, but it still sucks.

That being said, I was very happy.  I could finally do ALL my development in the same tool: DB, SSAS, SSRS & SSIS.  But after only 30 days of using it, I have discovered a few things that just irritate me.  I always thought of myself as open to change, but maybe I’m not as open to change as I thought I was.

1 – ISO Is No Longer An Option

That’s right, you can’t just download an ISO and stick it on your network, you have to essentially create your own if you need to do an offline install.  As the title of this post says, this is an “Irritant”, not a deal breaker, but definitely an irritant.

2 – Start Page – It’s Baaack!

There is NO WAY to turn this annoying page off once you close a solution.  You can turn the Start Page off when you first open VS2017 (Tools | Options| Environment | Startup), but as soon as you close a solution, it shows up – ARGH!  See this thread, I’m not the only one annoyed by this behavior.  I’m a very visual person and seeing a list of projects with really long paths don’t help me.  I need my Source Control Explorer to make sure I’m opening the right solution in the right branch.


3 – Work Items Open in Browser By Default

One of the cool things about using VS as your IDE is that it has this great add-in for source control, especially if you use VSTS, it’s almost a seamless integration (and everyone IS using some kind of source control, right?!).  You can open your list of User Stories based on iteration.  Once you have your list of User Stories, all you have to do is double click the one you want and it opens right there in your VS environment.  Well, the default behavior for where your work items has changed.  Your work items no longer open in VS, they open in a web browser.  You can change this default behavior back to opening in VS (Tools | Options | Work Items | General), but this behavior will be deprecated in the next major version of the product.  There is nothing more irritating than having to switch back and forth between applications when doing development work.  Pro Tip:  If you want folks to use Source Control, make it easy and all in ONE location!!!



4 – With Each New Update, Something Breaks

Okay, this is not new behavior in VS2017, unfortunately, it’s continuing behavior from previous versions of VS. <sigh>  I was really hoping that this behavior would stop, but sadly it still persists.  For example, since SSMS is now based on the VS Shell, if I update VS, it usually horks up (yes, that is a very technical term) the settings in my SSMS.

5 – Closing Solutions Take FOREVER!

Holy cow, in previous versions of VS, all I had to do was click File | Close Solution and in a split second my solution would close.  Now, it takes forever.  I have a solution with two projects in it and when I click File | Close Solution it takes anywhere from 15-30 seconds to close (I feel really bad for those that have more than 2 projects in their solution).  Not only that, it says it’s unloading 3 projects, not the 2 that I actually have!  WTH?  According to this thread, the problem has been fixed, but I can confirm that it most definitely has NOT been fixed as of version 15.3.5.  Maybe they only fixed it for Non-SSDT project types?


Okay, my rant is over, nothing to see here, move along.

Paying it Forward

My company has recently put forth an effort to hire fresh college graduates and interns.  I think this is a great thing.  A long time ago, in a galaxy far far away, I was a fresh graduate, with no real world experience and my first employer took a big chance in hiring me.  I was really lucky with my first employer, I had a couple of fabulous mentors that taught me not just technical skills, but life skills, that I still use today.  Employers are getting in on the ground floor and it’s in their best interest to invest resources in what will hopefully be a long, successful and rewarding career.  “Paying it forward”, as it were.

As a data professional, there are some essentials will make your life so much easier down the road.  The first one is Standards and Best Practices.  Establish these early and they will become habit.  Now I could drone on forever about particular Standards and Best Practices, but that’s not my point.  My point is that if you start off your career by adhering to standards and best practices, it won’t come as a shock to you later when they are forced upon you.  You may not like the particulars of them, but it won’t feel so icky the first time you have to use them if you are used to following something.  In working with our new employees, I always take the time to point them to our standards and explain why we have them.   They are always appreciative of this, especially the explanation.

Another essential is code review.  This is a big bang for your buck.  It’s a learning experience for everyone involved.  We use an Agile Methodology in our development life cycle.  So our teams are fairly small, they usually consist of two to three general developers a technical architect and a data architect or DBA type person.  Following the Agile Methodology, anybody should be able to work on anything in the product/iteration back log.  For the general developer, they can usually write T-SQL, but most times it won’t be nearly as efficient (or dare I say pretty) as if the data architect/DBA type person wrote it.  Code review allows for the team to become familiar with a task they are not directly involved with and provides valuable feedback to the person who wrote it.  They may not be as familiar with the subtleties of the business rules or have no idea that when you use three nested select statements in the predicate you will most likely end up doing a table scan on that table with 30 million records, three times!  All they know is that their query returns and it returns the correct answer.

Take the time to “pay it forward”, it can only benefit you in the long run and the person you “paid” will be that much better for it.