How can we improve SQL Test?

reduce the footprint

The install requires that you create schema, tables, procedures, functions etc in each database you want to test. I don't really like the idea of having 'foreign' objects in my databases. Could this work from one place? I.e. A single tSQLt database?

138 votes
Sign in
Sign in with: facebook google
Signed in as (Sign out)
You have left! (?) (thinking…)
kevriley shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →


Sign in
Sign in with: facebook google
Signed in as (Sign out)
  • Marc Fearby commented  ·   ·  Flag as inappropriate

    Having just bought SQL Treeo I can vouch for its sanity-restoring powers! At €50 it's quite steep, however, when faced with a Stored Procedures folder with over 200 items, it's money well spent.

    Microsoft really should allow us to organise things into sub-folders, though.

  • David Atkinson commented  ·   ·  Flag as inappropriate

    One option that doesn't fix the database footprint issue, but makes the Object Explorer tree much less cluttered is to use SQL TreeO ( ). This is a neat little utility that allows sub folders to be created in the Object Explorer. I've used it to organize my tSQLt objects and it works really well.

  • William Relf commented  ·   ·  Flag as inappropriate

    Have we got anywhere with this? I commented back in January 2012 and there doesn't seem to be much movement.

  • William Relf commented  ·   ·  Flag as inappropriate

    I have experimented creating a tSQLt database, each test I write then calls a number of procedures\tables with full three part qualified object names (MyDB.MySchema.ObjectName).

    The limitation is the tSQLt framework procedures for mocking\faking tables only except partly qualified two part object names (e.g. MySchema.ObjectName). Trying to do this (EXEC tSQLt.FakeTable 'MyDB.MySchema.ObjectName') simply results in error.

    One thing I have started doing (although isn't greate) is creating views in my test database which simply SELECT * FROM.... a fully qualified object allowing me to fake the view in the tSQLt database side.

    Overcoming these little "problems" will vastly improve the whole SQL Test\tSQLt package and address the "reduction of footprint".

  • Anonymous commented  ·   ·  Flag as inappropriate

    I see no reason for keeping the test objects in same database, the risk of delivering such objects to a production environment by mistake should be avoided as much as possible, I'm currently working with TST framework, it is a pain but fulfills our best practices as long as all test objects are created in a <ProductionDatabaseName>_Test database which keeps <ProductionDatabaseName> database clean of foreing objects.

    I wouldn't propose having only one database because maintaining hundreds objects that are related to different databases could easily become a nightmare, I am more likely to agree with having a one additional "Test" database with all tSQLt objects created in it per database added, eventually one may commit all that "Test" database objects to a source safe in a separate folder/project.

  • AdminDavid Atkinson (Admin, SQL Test home page) commented  ·   ·  Flag as inappropriate

    @Peter - I agree regarding the separate folder. That would make a lot of sense. At the moment, the folder just for schema and static data scripts. With the upcoming release of SQL Compare and SQL Source Control we've introduced a concept of saving 'migration scripts' so we're having to think hard about the folders we require. Most likely we will end up with a 'project', with the database scripts, migration scripts, tests, etc as top level subfolders of this.

  • Peter Henell commented  ·   ·  Flag as inappropriate

    @David Atkinson - We use SQL Compare to generate version based scripts that we deliver to the release. There is no problem to filter out the objects in the test schemas at this stage of the process.
    However, it is a matter of purity and convenience. We do not want the tsqlt product mixed up with our own source code and we do not want the inconvenience while working with Redgate SourceControl to always have to remove the check boxes for the tsqlt objects while committing.
    On top of this, as a bonus, it would be wonderful if the test procedures themself got committed to a separate folder in the SourceControl structure. This would be more in line with regular programming structure where you store your tests separate from the source itself.

  • AdminDavid Atkinson (Admin, SQL Test home page) commented  ·   ·  Flag as inappropriate

    @rk - sounds like you have a very mature and agile setup! The next release of SQL Source Control and SQL Compare supports a 'migrations'-style deployment methodology that is described in Pramod's writings. This will be designed to work in a completely automated fashion for use in continuous integration. We very much hope to bring in SQL Test / tSQLt into the CI story.

  • David Atkinson commented  ·   ·  Flag as inappropriate

    @Peter - is there any reason you can't just commit your tests to source control, which means the rest of the team will be able to 'get' them and run them on their own dev dbs? Is the problem making sure that you don't deploy the framework and tests to production? What process do you currently use for this?

  • rk commented  ·   ·  Flag as inappropriate

    @David Atkinson: Seems that you are wondering if we use the VS 2010 Database projects. No we don't. We rather work as described in
    We rely on handwritten SQL code we check in manually. So we have absolute control about what ends where.
    We use PowerShell scripts to deploy a new version of the database.
    When developing we add new scripts to our source tree and start a local build that in turn calls said PS Skript to create a local instance of the database. The build also has a target for running unit tests against the database. This target deploys tSQLt and Testcode and runs the tests.

  • Peter Henell commented  ·   ·  Flag as inappropriate

    @David Atkinsson - We are about 8 DB developers all using the Redgate Source Control connected to our personal dev databases. We really want to have all our tests in the source control aswell but not have to worry about what items to commit or not.

  • David Atkinson commented  ·   ·  Flag as inappropriate

    @rk - are you developing offline using database projects, or are you developing against a connected development database? In the latter case, I can see that it might not feel right having framework objects in the database that is actively being developed, and care would have to be taken not to commit the framework objects to source control, if it's important to keep this 'clean'.

  • rk commented  ·   ·  Flag as inappropriate

    Keep it as it is - please!
    I see no point in moving the testing framework or the test code to different databases. We are already using tSQLt since a couple of month.
    We build the latest version of the database from our repository (just TFS Source Control, no SQL Source Control - sorry redgate). Only when we are developing or running unit tests against the db wer are actually deploying tSQLt and the test code. Right now we do this for example within our continuous integration build on every check in.
    We would never run unit tests in a database that we handed over to test or production. We just build those dbs from a version of our repository that successfully survived the ci build.
    So, please keep it pure.

  • jonathanallen69 commented  ·   ·  Flag as inappropriate

    Having a dedicated database for tSQLt gets my vote. There would be lots of reasons that we wouldnt use this while it 'invades' other databases as much as it does. Not least the fact that it would get the blame for each and every fault that arises subsequent to its installation

  • Dave Green commented  ·   ·  Flag as inappropriate

    Whilst I prefer the idea of the objects being stored separately, I'd want to at least remove them when packaging a database in SQL Packager. Not sure if this would work on the back of a modification to SQL Compare or not.
    It may be though that the 'nuke button' could be called separately (is it an SP?) so that could be a CI build step.

  • Stephen commented  ·   ·  Flag as inappropriate

    I had assumed that when I told the SQL Test dialog to remove the database from SQL Test it would have done the necessary... It certainly wasn't intuitive to find it in the right-click list. Thanks for educating others where to find the "nuke button".

  • Adrian Tompkins commented  ·   ·  Flag as inappropriate

    Could you use schemas for this? If two developers are running tests on the same database today, won't they collide anyway? Either a schema inside the target database that can be filtered in SQL Compare or multiple schemas with a shared tSQLt database, one per target database. I prefer the second option too.

  • kevriley commented  ·   ·  Flag as inappropriate

    Whilst I see the benefit of 'hiding' these objects from SQL Compare, I still don't like the idea of them being there in the first place......sorry!

← Previous 1

Feedback and Knowledge Base