{"id":70,"date":"2009-11-09T20:25:08","date_gmt":"2009-11-10T01:25:08","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=70"},"modified":"2014-11-13T14:04:32","modified_gmt":"2014-11-13T19:04:32","slug":"faking-views","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/70\/faking-views\/","title":{"rendered":"Faking Views"},"content":{"rendered":"

We've seen FakeTable in tSQLt in action in a few demos now. Did you know that you can use FakeTable to fake a view?<\/span><\/p>\n

<\/p>\n

You use FakeTable on a table when you want to test a procedure (or function or view) that relies on a table. It prevents you from needing to concern yourself with all of the other constraints a table might have, such as foreign keys to other tables or not-nullable columns which have nothing to do with your procedure. This is particularly useful if you have a schema where many tables are interrelated through foreign keys because you can focus on the procedure you're testing instead of trying to create test data for dozens of unrelated tables.<\/span><\/p>\n

<\/p>\n

Similarly, you would want to fake views. A procedure that uses a view, for example, shouldn't care about how the view got the data, only that it did (at least at the unit test level). For example, a view may join together and filter data and perform some calculations. It is useful to unit test this behavior in isolation. Likewise, it is useful to unit test the functionality of the procedure in isolation from the view's functionality. You can simply call FakeTable and pass a schema name and view name instead of a table name:<\/span><\/p>\n

<\/p>\n\n\n\n
\n

EXEC [tSQLt]<\/span>.<\/span>[FakeTable]<\/span> 'HumanResources'<\/span>,<\/span> 'RetiredEmployee'<\/span>;<\/span><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/table>\n

<\/p>\n

Then you can insert records directly into the faked view without worrying about all the dependencies of the view or it's underlying tables.<\/span><\/p>\n

<\/p>\n

Except for one problem...<\/strong>Sometimes when parsing your T-SQL code, SQL Server will attempt to determine if you are trying to insert into a non-updatable view. A non-updatable view is one where SQL Server cannot use the view definition to accurately decide how to create or update records in the underlying tables. While parsing, SQL Server does not recognize, that FakeTable is replacing your view with a table; even though this works just fine at run-time.<\/span><\/p>\n

<\/p>\n

To get around this, the latest version of tSQLt provides two new procedures: SetFakeViewOn and SetFakeViewOff. You place a call to SetFakeViewOn at the very top of your file that defines your test procedures. You place a call to SetFakeViewOff at the very bottom of your file. Both procedures take the schema name for the views you want to fake as a parameter.<\/span><\/p>\n

<\/p>\n

For example, suppose the RetiredEmployee view is non-updatable:<\/span><\/p>\n

<\/p>\n\n\n\n
\n

EXEC<\/span> [tSQLt].<\/span>[SetFakeViewOn] 'HumanResources'<\/span>;<\/span><\/span><\/span><\/span><\/span><\/p>\n

GO<\/span><\/span><\/span><\/span><\/p>\n

<\/p>\n

EXEC<\/span> [tSQLt]<\/span>.<\/span>[NewTestClass]<\/span> 'HRTests'<\/span>;<\/span> <\/span><\/span><\/span><\/p>\n

GO<\/span><\/span><\/span><\/span><\/p>\n

<\/p>\n

CREATE<\/span> PROCEDURE<\/span> [HRTests]<\/span>.<\/span>[test that GetRetiredEmployeeCount returns number of retired employees]<\/span><\/span><\/span><\/span><\/p>\n

AS<\/span> <\/span><\/span><\/span><\/p>\n

BEGIN<\/span> <\/span><\/span><\/span><\/p>\n

EXEC [tSQLt]<\/span>.<\/span>[FakeTable]<\/span> 'HumanResources'<\/span>,<\/span> 'RetiredEmployee'<\/span>;<\/span> <\/span><\/span><\/span><\/span><\/p>\n

<\/p>\n

INSERT INTO [HumanResources]<\/span>.<\/span>[RetiredEmployee]<\/span> (<\/span>id<\/span>)<\/span> VALUES (<\/span>1<\/span>);<\/span> <\/span><\/span><\/span><\/span><\/p>\n

INSERT INTO [HumanResources]<\/span>.<\/span>[RetiredEmployee]<\/span> (<\/span>id<\/span>)<\/span> VALUES (<\/span>2<\/span>);<\/span> <\/span><\/span><\/span><\/span><\/p>\n

INSERT INTO [HumanResources]<\/span>.<\/span>[RetiredEmployee]<\/span> (<\/span>id<\/span>)<\/span> VALUES (<\/span>3<\/span>);<\/span> <\/span><\/span><\/span><\/span><\/p>\n

<\/p>\n

DECLARE @count<\/span> INT;<\/span> <\/span><\/span><\/span><\/span><\/p>\n

SELECT @count<\/span> =<\/span> [HumanResources]<\/span>.<\/span>[GetRetiredEmployeeCount]<\/span>();<\/span> <\/span><\/span><\/span><\/span><\/p>\n

<\/p>\n

EXEC [tSQLt]<\/span>.<\/span>[AssertEquals] 3<\/span>,<\/span> @count<\/span>;<\/span> <\/span><\/span><\/span><\/span><\/p>\n

END<\/span>;<\/span> <\/span><\/span><\/span><\/p>\n

GO<\/span><\/span><\/span><\/span><\/p>\n

<\/p>\n

EXEC<\/span> [tSQLt]<\/span>.<\/span>[SetFakeViewOff]<\/span> 'HumanResources'<\/span>;<\/span> <\/span><\/span><\/span><\/p>\n

GO<\/span><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/table>\n

<\/p>\n

SetFakeViewOn<\/strong> creates an instead-of trigger on all views in the given schema, allowing the compilation of your test cases which modify the contents of the faked view. However, what this has done is modify your schema - it has changed your system under test in a potentially unsafe way.<\/span><\/p>\n

<\/p>\n

SetFakeViewOff<\/strong> restores balance to the force. It removes the instead-of triggers right after your test cases are compiled. This means, that your system is back in a good state before you run your tests.<\/span><\/p>\n

<\/p>\n

All of the run test procedures have been enhanced to check that SetFakeViewOff has been called appropriately. If it was not called, then tSQLt will raise an error and your test cases will not complete successfully.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

We’ve seen FakeTable in tSQLt in action in a few demos now. Did you know that you can use FakeTable to fake a view? You use FakeTable on a table when you want to test a procedure (or function or [more…]<\/a><\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[5],"tags":[],"class_list":["post-70","post","type-post","status-publish","format-standard","hentry","category-general"],"acf":[],"yoast_head":"\nFaking Views<\/title>\n<meta name=\"description\" content=\"The tSQLt.FakeTable procedures allows not only to fake table but can be used to fake views as well. This blog post shows how.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/70\/faking-views\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Faking Views\" \/>\n<meta property=\"og:description\" content=\"The tSQLt.FakeTable procedures allows not only to fake table but can be used to fake views as well. This blog post shows how.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/70\/faking-views\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2009-11-10T01:25:08+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T19:04:32+00:00\" \/>\n<meta name=\"author\" content=\"dennis\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dennis\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/70\\\/faking-views\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/70\\\/faking-views\\\/\"},\"author\":{\"name\":\"dennis\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/da93e377379dd64d1428fd3d130269fd\"},\"headline\":\"Faking Views\",\"datePublished\":\"2009-11-10T01:25:08+00:00\",\"dateModified\":\"2014-11-13T19:04:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/70\\\/faking-views\\\/\"},\"wordCount\":575,\"commentCount\":3,\"articleSection\":[\"General\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/70\\\/faking-views\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/70\\\/faking-views\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/70\\\/faking-views\\\/\",\"name\":\"Faking Views\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2009-11-10T01:25:08+00:00\",\"dateModified\":\"2014-11-13T19:04:32+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/da93e377379dd64d1428fd3d130269fd\"},\"description\":\"The tSQLt.FakeTable procedures allows not only to fake table but can be used to fake views as well. This blog post shows how.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/70\\\/faking-views\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/70\\\/faking-views\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/70\\\/faking-views\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Faking Views\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/da93e377379dd64d1428fd3d130269fd\",\"name\":\"dennis\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/166ceda64e242f6ea01d467d0b6121b7264f9127efd3531ece1126385c50a385?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/166ceda64e242f6ea01d467d0b6121b7264f9127efd3531ece1126385c50a385?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/166ceda64e242f6ea01d467d0b6121b7264f9127efd3531ece1126385c50a385?s=96&d=mm&r=g\",\"caption\":\"dennis\"},\"sameAs\":[\"http:\\\/\\\/www.curiouslycorrect.com\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Faking Views","description":"The tSQLt.FakeTable procedures allows not only to fake table but can be used to fake views as well. This blog post shows how.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/70\/faking-views\/","og_locale":"en_US","og_type":"article","og_title":"Faking Views","og_description":"The tSQLt.FakeTable procedures allows not only to fake table but can be used to fake views as well. This blog post shows how.","og_url":"https:\/\/sqlity.net\/en\/70\/faking-views\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2009-11-10T01:25:08+00:00","article_modified_time":"2014-11-13T19:04:32+00:00","author":"dennis","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"dennis","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/70\/faking-views\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/70\/faking-views\/"},"author":{"name":"dennis","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/da93e377379dd64d1428fd3d130269fd"},"headline":"Faking Views","datePublished":"2009-11-10T01:25:08+00:00","dateModified":"2014-11-13T19:04:32+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/70\/faking-views\/"},"wordCount":575,"commentCount":3,"articleSection":["General"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/70\/faking-views\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/70\/faking-views\/","url":"https:\/\/sqlity.net\/en\/70\/faking-views\/","name":"Faking Views","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2009-11-10T01:25:08+00:00","dateModified":"2014-11-13T19:04:32+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/da93e377379dd64d1428fd3d130269fd"},"description":"The tSQLt.FakeTable procedures allows not only to fake table but can be used to fake views as well. This blog post shows how.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/70\/faking-views\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/70\/faking-views\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/70\/faking-views\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Faking Views"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/da93e377379dd64d1428fd3d130269fd","name":"dennis","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/166ceda64e242f6ea01d467d0b6121b7264f9127efd3531ece1126385c50a385?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/166ceda64e242f6ea01d467d0b6121b7264f9127efd3531ece1126385c50a385?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/166ceda64e242f6ea01d467d0b6121b7264f9127efd3531ece1126385c50a385?s=96&d=mm&r=g","caption":"dennis"},"sameAs":["http:\/\/www.curiouslycorrect.com"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-18","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/70","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=70"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/70\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=70"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=70"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=70"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}