tSQLt - Database Unit Testing for SQL Server

Database Unit Testing for SQL Server

  • Home
  • Docs
    • Documents
    • Quick Start
    • Articles
  • Training
  • Downloads
tSQLt » Documents » Isolating Dependencies » FakeTable

FakeTable

Syntax

tSQLt.FakeTable [@TableName = ] 'table name'
                , [[@SchemaName = ] 'schema name']
                , [[@Identity = ] 'preserve identity']
                , [[@ComputedColumns = ] 'preserve computed columns']
                , [[@Defaults = ] 'preserve default constraints']

Arguments

[@TableName = ] ‘table name’
The name of the table for which to create a fake table. Should contain both the schema name and the table name.
[@SchemaName = ] ‘schema name’ – Deprecated: do not use, will be removed in future version
[@Identity = ] ‘preserve identity’ – Indicates if the identity properties of an identity column should be preserved. If @Identity = 1, the identity properties will be preserved, otherwise the faked table will have the identity properties removed. The default is @Identity = 0.
[@ComputedColumns = ] ‘preserve computed columns’ – Indicates if the computations on computed columns should be preserved. If @ComputedColumns = 1, the computations will be preserved, otherwise the faked table will have computations removed from computed columns. The default is @ComputedColumns = 0.
[@Defaults = ] ‘preserve default constraints’ – Indicates if the default constraints on columns should be preserved. If @Defaults = 1, default constraints will be preserved for all columns on the faked table, otherwise the faked table will not contain default constraints. The default is @Defaults = 0.

Return Code Values

Returns 0

Error Raised

If the specified table does not exist an error is thrown: FakeTable could not resolve the object name, ‘%s’.

Result Sets

None

Overview

We want to keep our test cases focused and do not want to insert data which is irrelevant to the current test. However, table and column constraints can make this difficult.

FakeTable allows tests to be written in isolation of the constraints on a table. FakeTable creates an empty version of the table without the constraints in place of the specified table. Therefore any statements which access the table during the execution of the test case are actually working against the fake table with no constraints. When the test case completes, the original table is put back in place because of the rollback which tSQLt performs at the end of each test case.

FakeTable can be called on Tables, Views and Synonyms (A Synonym has to point to a table or view in the current database.)

Limitations

FakeTable cannot be used with temporary tables (tables whose names begin with # or ##).

Warnings

Remember if you are faking a table, you are not testing the constraints on the table. You will want test cases that also exercise the constraints on the table. See Also: ApplyConstraint.

Examples

Example: Faking a table to create test data to test a view

In this example, we have a FinancialApp.ConvertCurrencyUsingLookup view which accesses the FinancialApp.CurrencyConversion table. In order to test the view, we want to freely insert data into the table. FakeTable is used to remove the constraints from the CurrencyConversion table so that the data record can be inserted.

CREATE PROCEDURE testFinancialApp.[test that ConvertCurrencyUsingLookup converts using conversion rate in CurrencyConversion table]
AS
BEGIN
    DECLARE @expected MONEY; SET @expected = 3.2;
    DECLARE @actual MONEY;
    DECLARE @amount MONEY; SET @amount = 2.00;
    DECLARE @sourceCurrency CHAR(3); SET @sourceCurrency = 'EUR';
    DECLARE @destCurrency CHAR(3); SET @destCurrency = 'USD';

------Fake Table
    EXEC tSQLt.FakeTable 'FinancialApp.CurrencyConversion';

    INSERT INTO FinancialApp.CurrencyConversion (id, SourceCurrency, DestCurrency, ConversionRate)
                                         VALUES (1, @sourceCurrency, @destCurrency, 1.6);
------Execution
    SELECT @actual = amount FROM FinancialApp.ConvertCurrencyUsingLookup(@sourceCurrency, @destCurrency, @amount);

------Assertion
    EXEC tSQLt.assertEquals @expected, @actual;
END;
GO

Example: Calling FakeTable with @Identity = 1
We need to specify the @Identity parameter explicitly because the second parameter (@SchemaName) has been deprecated. This same pattern applies to @ComputedColumns and @Defaults.

EXEC tSQLt.FakeTable 'FinancialApp.CurrencyConversion', @Identity = 1;

See Also

  • ApplyConstraint
  • ApplyTrigger
  • FakeFunction
  • FakeTable
  • RemoveObject
  • RemoveObjectIfExists
  • SpyProcedure

Since

V1.0.4462.23207

Share and Enjoy:
  • FacebookFacebook
  • TwitterTwitter
  • LinkedInLinkedIn
  • RedditReddit
  • StumbleUponStumbleUpon
  • TechnoratiTechnorati
  • PrintPrint
42 comments
  Livefyre
  • Get Livefyre
  • FAQ
Sign in
+ Follow
Post comment
 
Link
Newest | Oldest
Davidtsqlt
Davidtsqlt 5pts

Can you use FakeTable in the SetUp procedure?

@sqlity
@sqlity moderator 5pts

Yes.

smithmartin1
smithmartin1 5pts

Is it a known issue with FakeTable that it does not preserve the scale for datetime2?


It seems that when faking a table with datetime2(0) the fake table  column has datetime2(7) 

@sqlity
@sqlity moderator 5pts

@smithmartin1  yes, this is a known issue. There is a fix in the pipeline and will be included in the next release. It has not gone through all tests yet, but you can get the new version of the offending function here: https://github.com/saperry/tSQLt/blob/b08796d95baada90fc19941d6a82230c8212fba7/Source/tSQLt.Private_GetFullTypeName.sfn.sql

alvinalvin
alvinalvin 5pts

With tSQLt, can I setup one DB just for the tSQLt code to test my development DB? Or does tSQLt have to be installed in the same DB that is being tested?

@sqlity
@sqlity moderator 5pts

@alvinalvin  - Currently, cross-database usage is not supported for many of the tSQLt functions. That means you have to install tSQLt in the database that is being tested.


rehobeth_data
rehobeth_data 5pts

I tried to use the cross database view and the results were not as expected.

I created two databases: test_tsqlt_1 and test_tsqlt_2. I istalled the tSQLt framework on both databases. I then created the following physical table:

CREATE TABLE test_tsqlt_1.dbo.phys_src (

col1 int NOT NULL,

col2 nvarchar(MAX) NOT null


)

Then in test_tsqlt_2 I created the following cross database view:


CREATE VIEW dbo.view_src AS

SELECT *

FROM test_tsqlt_1.dbo.phys_src



I then created the following test:


USE [test_tsqlt_2];GO



ALTER PROCEDURE [recreate_issue].[test1]AS    BEGIN        DECLARE @msg NVARCHAR(MAX);

        EXEC tSQLt.FakeTable @TableName = N'dbo.view_src';

        INSERT  INTO dbo.view_src                ( col1, col2 )        

        VALUES  ( 0, -- col1 - int                  N'insert into cross DB view'  -- col2 - nvarchar(max)                  );



        SELECT  't1' AS t1 ,                *        FROM    dbo.view_src;        

        SELECT  't2' AS t2 ,                *        FROM    test_tsqlt_1.dbo.phys_src;

        SELECT  @msg = 'Finished at ' + CONVERT(NVARCHAR, GETDATE());

        EXEC tSQLt.Fail @msg;      END;



If I understand your recommendation correctly, I would expect to see two identical result sets. However, the first result set has data and the second result set does not.


The reason I need t2 to return data is that I am testing a stored procedure which pulls data from one database and puts it into a table in another database. The proc execute a query roughly equivalent to the query in t2. So I need to put fake data into a table in another database. I know this may not be a great design, but I'm new on the team and the opportunity to design with synonyms, etc, is long, long gone.


I see from other comments in the thread that the workaround is to call FakeTable on the physical table in test_tsqlt_1 using the tSQLt framework installed on that database. You mention this can create maintenance problems and I totally understand that. I'm hoping to avoid that issue, but it looks like the strategy you recommend won't fit the bill. Or maybe I'm missing something.


Any help you can suggest would be greatly appreciated!

@sqlity
@sqlity moderator 5pts

@rehobeth_data Your call to FakeTable "unlinks" the view from the table. So the result you are getting is expected. FakeTabe creates a new table in the current database that resembles the faked object. Inserting data into it, will not in any way modify any object that existed before the FakeTable call. That is, what we are trying to achieve with FakeTable after all.

I am not sure what you are trying to do. My recommendation is that no code in one database directly reference objects in another database. Instead I recommend to put a view (or synonym) layer in between. That way you can test the code in one database without having to deal with the complexities on cross database tests.

Chivogelo
Chivogelo 5pts

Hello, How can I read real data from a table, which was Faked. Not from Fake Table.

@sqlity
@sqlity moderator 5pts

@Chivogelo There is no direct way. You need to copy the data you need before you fake the table. However, using existing data is a bad practice. Consider setting up the data you need for your test inside the test without relying on existing data.

EthanSteiner
EthanSteiner 5pts

Can FakeTable be used to fake a table in another Database?

The Stored Procedure I'm testing  is in DB1.  

It queries the [cust] table in DB2.


When I run 

EXEC tSQLt.FakeTable @TableName = 'DB2.dbo.cust'

it gives  me the error "FakeTable could not resolve the object name."



@sqlity
@sqlity moderator 5pts

@EthanSteiner Not currently. Hardcoding other databases in your T-SQL code is a bad practice. Instead use auto-generated Views or Synonyms. Cross database Views can already be faked. Synonyms I am working on.

PavelVakar
PavelVakar 5pts

@@sqlity @EthanSteiner

hi guys, 


I use following construction and it works fine.


tSQLt framework should be installed on both DB1 and DB2 DBs


use DB1    

exec tSQLt.FakeTable 'dbo.test1';

exec DB2.tSQLt.FakeTable 'dbo.test2';

@sqlity
@sqlity moderator 5pts

@PavelVakar @EthanSteiner - Thanks Pavel. This certainly works. But keep in mind that it is carrying the bad practice of hard-coding database names into your tests. This is a maintenance nightmare, if one of the database names ever changes and prevents you from having two installations of your product on the same server.

EthanSteiner
EthanSteiner 5pts

@PavelVakar @@sqlity @EthanSteiner Ah, that's what I was missing.  Installed the framework on DB2 and used your syntax above and we're in business.  :)

EthanSteiner
EthanSteiner 5pts

@@sqlity @PavelVakar @EthanSteiner I'll definitely keep this in mind.  How would you suggest dealing with this situation without hard-coding the DB names into the test?  I'm just starting out with testing and want to do it as right as I can from the start.

@sqlity
@sqlity moderator 5pts

@EthanSteiner @@sqlity @PavelVakar Put a layer in between. In your code, instead of referencing DB2.dbo.table reference a view db2_dbo.table that lives in DB1 and points to DB2. Then you can call tSQLt.FakeTable 'db2_dbo.table';


To generate the view, just create a stored procedure in DB1 that takes a database name (DB2), loops through all tables in that database and creates a view with SELECT * FROM DB2.dbo.table;


That way the only reference to DB2 is in generated objects that can easily be regenerated if the database name changes.


If you go that way, all tests can be written to be local to one database only.


You don't need to transition to this at once. Instead, create the view layer, and every time you need to write a test in a cross database situation, replace the hardcoded reference with a reference to the generated view. That way you can make slow progress without overwhelming yourself at first.


The same thing works for other object types like procedures and functions.


Oh, and instead of DB2_dbo you should really call that schema based on the purpose of DB2. For example, if DB2 contains all your purchase orders, call it orders_dbo.


(I'd really like to recommend you use synonyms instead of views, however I am not sure right now how far I got with implementing that. I believe there are still a few gaps. Maybe you can give it a try?)

EthanSteiner
EthanSteiner 5pts

@@sqlity @EthanSteiner @PavelVakar sqlity: This has taken me a while to process, but I think I see it now.  I really like the flexibilty this pattern would give.  


I'll do some testing.  In general, do you see the added layer of Local Views of remote data have an impact on Stored Procedure performance?  


Thanks for your recommendation.  This could have a pretty big impact on how I go at things.  :)

@sqlity
@sqlity moderator 5pts

@EthanSteiner @@sqlity @PavelVakar I cannot say that there will be no impact. However, the impact is only during the compilation phase (as SQL Server unwraps views during that phase - the execution plan it comes up with is the same) and therefore unnoticeable in 99.9% of the cases. 

tsmruti10
tsmruti10 5pts

@@sqlity @EthanSteiner  Is it a good practice to create a separate object(view) specifically for TSQLt.

rehobeth_data
rehobeth_data 5pts

@@sqlity @EthanSteiner

PavelVakar
PavelVakar 5pts

hi guys, thank you a lot for the tSQLt!


I have an issue using tSQLt.FakeTable method:


In case regular table and user-defined table type have the same name it returns following  error:

"Either the parameter @objname is ambiguous or the claimed @objtype ((null)) is wrong."


This is because of sp_rename SP specific: tSQLt.FakeTable uses following syntax: sp_rename 'object_name' , 'new_name'  without the third optional  parameter: 'object_type', and SQL doesn't understand what object should be re-named. 


Do you have any workaround for this ? Thanks !




PavelVakar
PavelVakar 5pts

/* steps to reproduce*/


create table dbo.test (c int);

CREATE TYPE dbo.test AS TABLE(c int);


exec tSQLt.FakeTable 'dbo.test'; -- ERROR

EXEC sp_rename 'dbo.test', 'test1'; -- ERROR

EXEC sp_rename 'dbo.test', 'test1', 'OBJECT'; -- this one works !!


--Microsoft SQL Server 2008 R2 (SP1) - 10.50.2772.0 (X64) 


@sqlity
@sqlity moderator 5pts

@PavelVakar There is a simple change that has made it into the code base already, but I have not been able to cut the next release yet. To make the change manually, check out this link: https://github.com/tSQLt-org/tSQLt/commit/a5c992583be561b2b826eb0538e5f01b3c83601a

PavelVakar
PavelVakar 5pts

@@sqlity Wow! Thank you !  Your change looks good. I've found the place to update too, hope this doesn't effect any other functionality. It  took me a while to find the root cause of the issue ((( 

regis757
regis757 5pts

What happens if you use FakeTable on a Table that has already been Faked?


Example:


EXEC tSQLt.FakeTable 'dbo.Table1'


INSERT INTO dbo.Table1 (Row1)


EXEC tSQLt.FakeTable 'dbo.Table1'


INSERT INTO dbo.Table1 (Row2)


After the above statements, would both Row1 and Row2 be available, or only Row 2? Thanks

@sqlity
@sqlity moderator 5pts

@regis757 FakeTable does not take the current fake-status into consideration. That means, that a completely new copy of the table is created every time you call FakeTable on it.


In your example only Row2 would exist at the end.

bitbitz
bitbitz 5pts

I'm trying to use FakeTable, but I'm running into the following SQLException:


failed: (Error) System.Data.SqlClient.SqlException: Cannot rename the table because it is published for replication


Is there a way to faketable of these tables? Thanks in advance.

@sqlity
@sqlity moderator 5pts

@bitbitz You cannot fake a replicated table, nor should you. Remember, never run tSQLt in a production system. tSQLt is a unit testing framework and should only be used in a development environment. There is just too much that can go wrong when using tSQLt in production.

bitbitz
bitbitz 5pts

@@sqlity Good point. Thank you!

kmylapalli
kmylapalli 5pts

I am trying to insert fake data into a computed column of a table by using EXEC tSQLt.FakeTable 'Test' and I am getting error msg -



The column "TestColumn" cannot be modified because it is either a computed column or is the result of a UNION operator.


I also tried EXEC tSQLt.FakeTable 'Test' @ComputedColumn = 0 but still I am getting the same error. Am I doing anything wrong here? Please advise. Thank you in advance.

@sqlity
@sqlity moderator 5pts

@kmylapalli , You are running into a SQL Server limitation here. The easiest workaround is to execute the INSERT as dynamic SQL: 


EXEC('INSERT INTO dbo.a_faked_table(id, a_computed_colum)VALUES(1, 42);');

Saugat Mukherjee
Saugat Mukherjee 5pts

Would fake table work with non clustered columnstore tables? In my unit test, I create the fake table, drop the CStore index and then try to insert test data and then create the CStore index back.

But when I run the test, it fails with the error that DML isn't allowed, but in my code I drop the index and then insert- why does it not recognize it?

@sqlity
@sqlity moderator 5pts

@Saugat Mukherjee , I am not quite sure what the problem is. Could you send your test to the tSQLt google group or publish it on stackoverflow with the tSQLt tag?

Saugat Mukherjee
Saugat Mukherjee 5pts

@@sqlity @Saugat Mukherjee Thanks. I have posted it on stack overflow with tsqlt tag

StandardCommodity
StandardCommodity 5pts

Getting the following error when running FakeTable:


System.Data.SqlClient.SqlException: Object '[dbo].[XXXX]' cannot be renamed because the object participates in enforced dependencies.


I would think this would be fairly common, yet I see no mention of how this may be handled (if at all). Are there any workarounds or solutions?

@sqlity
@sqlity moderator 5pts

@StandardCommodity any form of schema binding is not currently supported by FakeTable. I have started the development to address this however, so it will be included in one of the next versions. Until then you have to drop the schema bound objects manually within the test. (They will be recreated when the test finishes do to the automatic rollback.)

StandardCommodity
StandardCommodity 5pts

@@sqlity Yep, it's the schema binding. Okay, thanks!

applicationista
applicationista 5pts

@@sqlity @StandardCommodity Did this ever get done? I'm having the same issue!

@sqlity
@sqlity moderator 5pts

@applicationista @@sqlity @StandardCommodity  Still working on it.

Travis
Travis 5pts

I am trying to preserve 'not null' constraints on columns using FakeTable with @Defaults=1 and it is not working. Has this functionality been added in yet? BTW, very good work so far on this project. It has become very useful to me. Thanks, Travis

Sebastian
Sebastian 5pts

Hi Travis. There is currently no way to preserve NOT NULL constraints. (They are not really constraints but column attributes, so tSQLt.ApplyConstraint cannot be used.) The usual use case for tSQLt.FakeTable is a test that is testing a module (e.g. stored procedure) that uses that table. In that case your "object under test" is that procedure, not the table. So when writing your tests make sure that the procedure does not insert a NULL in a not-nullable column. You don't need the column to be not-nullable to write this test. In fact, in that test you should not rely on column nullability but instead use its own assertions. The reason we use nullability constraints is to protect the data from stray programming or ad hoc alterations. So the only test you need to write that is concerned with column nullability is the one test that confirms that you cannot insert a NULL into the column in question. For that case however we have not found or implemented a good answer yet. So, currently you have to assert the correct nullability status of a column by looking at the is_nullable column in sys.columns. I hope this helps.

Navigation

  • Full user guide
  • Why you should use the tSQLt framework
  • How to get more out of tSQLt
  • Join the conversation
  • Downloads
  • Articles
  • Release Notes
  • New Logo
  • Training
  • Sponsors & Contributors
  • Why you should unit test SQL Server Code
  • Documents
    • Quick Start
    • tSQLt Tutorial
    • tSQLt Keyboard Shortcuts
    • Test Creation and Execution
      • NewTestClass
      • DropClass
      • RunAll
      • Run
      • RenameClass
    • Assertions
      • AssertNotEquals
      • AssertEmptyTable
      • AssertEquals
      • AssertEqualsString
      • AssertEqualsTable
      • AssertObjectExists
      • AssertResultSetsHaveSameMetaData
      • Fail
      • AssertObjectDoesNotExist
      • AssertEqualsTableSchema
      • AssertLike
    • Expectations
      • ExpectException
      • ExpectNoException
    • Isolating Dependencies
      • ApplyConstraint
      • FakeTable
      • SpyProcedure
      • FakeFunction
      • RemoveObjectIfExists
      • ApplyTrigger
      • RemoveObject

Links

  • tSQLt on GitHub
  • tSQLt Mailing List
  • twitter hashtag (#tsqlt)
  • tSQLt tag on stackoverflow.com
  • SQL Server Community on Slack (#tsqlt)

Sponsors

sqlity.net
redgate.com

A sqlity.net llc Web Property. | ©2010 - 2020, All Rights Reserved. | Privacy Policy | Terms of Use