Reading Time: 5 minutes (just do it!) There is a lot of confusion when it comes to designing tables in SQL Server around whether to pluralize names or not. How do you choose whether to pluralize or not? If we want to store a list of people and their details do we use “Person”, “Persons”, “People” or “Peoples”? Some people will use “People” and some will use “Person”, other persons or people would go for “Peoples” or “Persons”.

Reading Time: 15 minutes (it is quite hard going, but you need to know this if you need to work with the DacFx) DacFx - Getting the data type of a column on a table, a rehash of an old blog post This post has been years in the making, I did a version of it years ago here: https://sqlserverfunctions.wordpress.com/2014/09/27/querying-the-dacfx-api-getting-column-type-information - well fast forward 5 years to now and I can now understand how it works and whereas before I basically sat in a debugger trying to figure out what method to use.

Reading Time: 4 minutes (not a minute longer) TLDR; You probably don’t want to actually merge dacpac’s you probably want to deploy multiple and use /p:IncludeCompositeObjects=true. If you do really want to merge dacpacs you can start with this sample: https://github.com/GoEddie/DacpacMerge Why? I was reading the dacfx issues on the dacextensions sample’s github site: https://github.com/Microsoft/DACExtensions/issues and one of the issues https://github.com/Microsoft/DACExtensions/issues/23 asked about how to merge dacpac’s and I have been asked a few times how to do this and I thought there was a sample but I couldn’t find it so I knocked a quick one up.

Over the last couple of years I have been using VSCode more and more and Visual Studio less and less, this is great but there isn’t first class support for SSDT in VSCode, it would be great if there was but there isn’t today. This means I need to use Visual Studio to work with SSDT but often I find I just need to change a stored procedure or table and I don’t actually need the full SSDT experience, just a subset - can I change something and does it build, and can I deploy that build and run the tests?

Sometimes you don’t have the perfect development environment for SQL Server, sometimes you deploy to things like SQL Azure and want to test locally, for various reasons it is possible that you want to deploy to one version of SQL Server but set the project properties to a different version of SQL Server. If you find yourself in this situation you might need to use the parameter AllowIncompatiblePlatform which essentially says “I know I said the project is for SQL 2017 but I am deploying to SQL 2014”, anytime you say this you also sign the contract that says “If I am deploying to a lower version then I have made sure I haven’t used any keywords or object types that didn’t exist in that version and also if everything fails then so be it, I live with my choices every day”.

I have been asked a few times now for course completion certificates so I have decided to give out certificates to anyone who pays for a plan and sends me a screen shot of a unit test they have written and answer a question on unit testing. I will generate the certificates by hand so i’m not doing them for everyone otherwise I would have hundreds to do (over 300 people have now taken the online self-paced course).

TLDR: There is a new exciting community slack channel where you can find a mentor, a mentee or both if that floats your boat. The great Chrissy LeMaire (https://twitter.com/cl) from dba tools and sqlcommunity.slack.com fame and general Powershell and SQL legend has started a new mentor’s slack channel. Come and join the fun, it is a community-driven informal process. I have put up a google docs sheet with a list that people can add themselves to or use to contact other people who might be a good fit for them.

I had a requirement to run MySQL on a VSTS hosted build agent and then to be able to run commands from outside of the container and this gave me the chance to have a play around with VSTS support for containers and builds as YAML (YAML builds / builds as YAML??). I struggled getting started with this for a little bit longer than I would have hoped, if you have a container and want to run commands inside the container then the process is pretty straight forward, although on vsts you don’t get a TTY so need to do “docker exec -i” rather than “docker exec -it” as all the examples show.

Unit testing using tSQLt is really great, you call FakeTable on any tables your code touches and then insert the columns and rows your test actually need, nothing more, nothing less but what happens when you have more complicated processes like an ETL process? TLDR: the same as unit testing, just you probably don’t use tSQLt to mock the tables. What types of test are there? Tests get larger and more complicated the further away from the code, so first off we have unit tests which are very close to the code.

I had a template that deployed a premium managed disk and a virtual machine. I defined the disk like: { "comments": "Managed Disk.", "type": "Microsoft.Compute/disks", "name": "[variables('diskName')]", "apiVersion": "2016-04-30-preview", "location": "[parameters('location')]", "tags": { "displayName": "Data Disk 1 - Presented as LUN 0" }, "properties": { "accountType": "Premium_LRS", "creationData": { "createOption": "Empty" }, "diskSizeGB": 1024 }, "dependsOn": [] }, Then in the same template, I had a virtual machine with the storage section that looked like: