Users of SQL Test and the tSQLt framework upon which it is based have noted in the past that because test objects are stored in the database, they can be difficult to differentiate from the objects under test. This has been a barrier to some users adopting tSQLt for unit testing, as it prevented separate management of tests and raises the potential danger of tests being accidentally deployed within a production environment.
Recently I have been using tSQLt within SSDT (following this method by Ken Ross), and it occurs to me that this method of controlling unit tests allows us to keep the code under test in a different project to our tests, and therefore overcome the difficulty of keeping our tests as database objects. When we have our tests in a separate project within the same solution, we can choose to deploy either the tests with our code under test or simply the code itself without the tests. By configuring the test project to ensure that it requires the code under test to also be deployed at the same time and into the same database, we can set up publish definitions for the tests to our development machine and also a publish definition for just the code under test. It's the latter which we would use to deploy outside of our development workstation, for example to UAT. It also means that if you deploy by DACPAC the tests have never been in that package, so it is ready to deploy to each environment without your needing to take any additional steps.
Of course, a Continuous Integration engine has access to both projects within the solution from source control so it can either include unit tests or not, depending on your desired build action.
By having both the code under test and the tests themselves within the same solution, we can use the same source control process allowing both the code under test and the tests themselves to be in one place, which prevents drift between the test and the code under test.
When developing databases in SSDT, using this method gives me the best of both worlds; tests which to live with the database under development including within source control, and yet do not need to be removed from the database as part of or after the deployment process.