{"id":290,"date":"2007-07-31T00:00:00","date_gmt":"2007-07-30T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/close-these-loopholes-in-your-database-testing\/"},"modified":"2021-09-29T16:22:16","modified_gmt":"2021-09-29T16:22:16","slug":"close-these-loopholes-in-your-database-testing","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/close-these-loopholes-in-your-database-testing\/","title":{"rendered":"Close These Loopholes in Your Database Testing"},"content":{"rendered":"
\n <\/p>\n

DevOps, Continuous Delivery & Database Lifecycle Management<\/strong>
Continuous Integration<\/p>\n<\/div>\n\n

Incorrect or incomplete database unit tests may run successfully, but they may not expose all the problems in your application – they can only give you a false sense of security. The following guidelines will help you improve your database unit tests. <\/p>\n

Have settings on your Test Server match the Production Server.<\/h3>\n

Clearly this is an extremely trivial recommendation. However, I cannot emphasize enough how important it is. Discrepancies in database settings may cause your application to silently behave differently, without raising any errors – such problems may be very difficult to detect. For instance, the following code snippet will run differently against databases with different default collations:<\/p>\n

<\/pre>\n
CREATE TABLE t(a CHAR(1), b CHAR(1))<\/p>\n
INSERT t(a, b) VALUES(‘a’, ‘A’)<\/p>\n
SELECT COUNT(*) FROM t WHERE a=b<\/p>\n
You can create the following two databases, run the code snippet against both, and see for yourself that it returns 1 on your case insensitive database, and 0 on your case sensitive one:<\/p>\n
<\/pre>\n
CREATE DATABASE [TestCI] COLLATE SQL_Latin1_General_CP1_CI_AS
\nGO
\nCREATE DATABASE [TestCS] COLLATE SQL_Latin1_General_CP1_CS_AS
\nGO<\/p>\n
There are many other settings that may affect your application, such as default isolation level (READ COMMITTED<\/b> vs. READ COMMITTED SNAPSHOT<\/b>), and settings on nested and\/or recursive triggers. Providing a complete list of settings and explaining how they may affect your application is beyond the scope of this article. For an example on how snapshot isolation may affect your modifications, refer to a series of Hugo Kornelis’s posts named ” Snapshot isolation: A threat for integrity?” and published on sqlblog.com. I describe how settings on nested and\/or recursive triggers have broken an application in my article ” Avoid These Common Business Rule Implementation Mistakes” <\/p>\n
(http:\/\/www.devx.com\/dbzone\/Article\/31985\/)<\/p>\n
Impersonate your user when you test.<\/h3>\n
Let’s suppose that you created a stored procedure but forgot to grant any privileges to execute it. When you, the developer, then unit test your code against your development server, you may connect as a dbo – this is a common practice. Unfortunately, so long as you connect as dbo, you will not detect your missing permissions. This problem is very easy to fix. For example, you can just create a mock user and add it to your database role.<\/p>\n
<\/pre>\n
CREATE USER mockuser WITHOUT LOGIN 
\nGO 
\nsp_addrolemember @rolename = ‘YourRoleName’, @membername = ‘mockuser’ 
\nGO<\/p>\n
In your unit test, impersonate the mock user before executing the stored procedure, as follows:<\/p>\n
<\/pre>\n
SETUSER ‘mockuser’<\/p>\n
If the permission to execute your stored procedure is not granted to either the mock user or to the role, you will get an exception. Do not forget to revert to dbo after running the test:<\/p>\n
<\/pre>\n
SETUSER<\/p>\n
Note: <\/b>This is a just very simple example of impersonation. It is good to use when you just run your unit tests on your local machine. It is not a substitute for a more realistic test environment when a real network account is used. A more detailed discussion of database security and impersonation is beyond the scope of this article.<\/p>\n
Populate enough test data before your test.<\/h3>\n
We’ll suppose that you have failed to provide a WHERE clause in your stored procedure:<\/p>\n
<\/pre>\n
\/*
\nI deliberately stripped this stored procedure from all the bells and
\n             whistles that are not relevant to the point I am making.
\n*\/
\nCREATE PROCEDURE Writers.DeleteCustomer @CustomerID INT
\nAS
\nDELETE FROM Data.Customers –WHERE clause is missing
\nRETURN @@ERROR
\nGO<\/p>\n
If you only had one row in your table before running the stored procedure, your error would not be detected by your unit test. Make sure you have enough test data before running the test. After running the test, make sure that all the data except for the one deleted row is still in the table.<\/p>\n
Test how your code handles errors.<\/h3>\n
Now suppose that you have changed your DeleteCustomer stored procedure, as follows:<\/p>\n
<\/pre>\n
CREATE PROCEDURE Writers.DeleteCustomer @CustomerID INT
\nAS
\nDECLARE @error INT, @rowcount INT
\nDELETE FROM Data.Customers WHERE CustomerID = @CustomerID
\nSELECT @error = @@ERROR  — forgot to populate @rowcount = @@ROWCOUNT
\nIF @rowcount = 0 BEGIN
\n  RAISERROR(‘Could Not Find Customer’, 16, 1)
\n  SET @error = -1
\nEND
\nRETURN @error
\nGO<\/p>\n
Suppose you want to verify that you get an exception if you provide invalid customer ID, and you have written a unit test in C# using NUnit, as follows:<\/p>\n
<\/pre>\n
[Test]
\n[ExpectedException(typeof(SqlException))]
\npublic void TestDeleteCustomerInvalidID()
\n{
\n            SqlCommand cmd = this.connection.CreateCommand();
\n            cmd.CommandText = “EXEC Writers.Delete_Customer -1”;
\n            cmd.ExecuteNonQuery();
\n}<\/p>\n
Apparently the test succeeds, but wait a moment: because the name of the stored procedure is misspelled, you are getting the wrong kind of SQL exception! Your procedure did not even execute, and your unit test should not succeed. The following unit test verifies that the right exception is raised:<\/p>\n
<\/pre>\n
[Test]
\npublic void TestDeleteCustomerInvalidID()
\n{
\n            bool gotTheRightException = false;
\n            cmd.CommandText = “EXEC Writers.Delete_Customer -1”;
\n            try
\n            {
\n                cmd.ExecuteNonQuery();
\n            }
\n            catch (SqlException e)
\n            {
\n                string s = e.Message;
\n                gotTheRightException = 
\n                  s.Contains(“Could Not Find Customer”);
\n            }
\n            Assert.IsTrue(gotTheRightException);   
\n            \/\/You still need to verify that no one row is deleted
\n}<\/p>\n
Note that even though you verified that you received the right exception, you still need to verify that no rows are deleted. The reason is simple: <\/p>\n
<\/pre>\n
ALTER PROCEDURE Writers.DeleteCustomer @CustomerID INT
\nAS
\nDECLARE @error INT, @rowcount INT
\nDELETE FROM Data.Customers 
\n— deliberately removed WHERE CustomerID = @CustomerID
\nSELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
\nIF @rowcount <> 1 BEGIN
\n  RAISERROR(‘Could Not Find Customer’, 16, 1)
\n  SET @error = -1
\nEND
\nRETURN @error
\nGO<\/p>\n
This incorrect procedure first deletes all the rows and then raises the right exception if there were more than one row in the table. For more details on error handling in Transact-SQL refer to articles on Erland Sommarskog’s Web site. <\/p>\n
Testing stored procedures which modify data will be described in more detail in a later article.<\/p>\n
<\/b><\/p>\n
Do Not Forget To Consider Concurrency.<\/h3>\n
In some cases, stored procedures, will work as expected as long as you unit test them from a single connection, but fail intermittently in a more realistic multi-connection environment. For example, consider a requirement to implement a stored procedure that should return a unique integer as an output parameter every time it is called.<\/p>\n
The following three simple stored procedures all attempt to implement the requirement:<\/p>\n
<\/pre>\n
CREATE TABLE Data.Counter(Counter INT NOT NULL)
\nINSERT Data.Counter(Counter) VALUES(1)
\nGO
\nCREATE PROCEDURE Writers.GetNextCounter1 @NextCounter INT OUT
\nAS
\nSET TRANSACTION ISOLATION LEVEL READ COMMITTED
\nBEGIN TRAN
\nUPDATE Data.Counter SET Counter = Counter + 1
\nSELECT @NextCounter = Counter FROM Data.Counter
\nCOMMIT
\nGO
\nCREATE PROCEDURE Writers.GetNextCounter2 @NextCounter INT OUT
\nAS
\nSET TRANSACTION ISOLATION LEVEL READ COMMITTED
\nSELECT @NextCounter = Counter + 1 FROM Data.Counter
\nUPDATE Data.Counter SET Counter = Counter + 1
\nGO
\nCREATE PROCEDURE Writers.GetNextCounter3 @NextCounter INT OUT
\nAS
\nSET TRANSACTION ISOLATION LEVEL SERIALIZABLE
\nBEGIN TRAN
\nUPDATE Data.Counter SET Counter = Counter + 1
\nSELECT @NextCounter = Counter FROM Data.Counter
\nCOMMIT
\nGO<\/p>\n
If you run any one of these three stored procedures from a single connection only, all three would appear to work correctly. However, if you run them thousands of times simultaneously from several connections, the first one will still reliably provide unique numbers, while the second one may provide some duplicates, and the third one is likely to end up in a deadlock. <\/p>\n
As you have seen, simple unit testing from one connection is not sufficient in this case.<\/p>\n
The next article will explain in more detail how to utilize C# and NUnit to unit test result sets returned from stored procedures.<\/p>\n
\n  <\/p>\n
\n
 DevOps, Continuous Delivery & Database Lifecycle Management<\/strong>
 Go to the Simple Talk library<\/a> to find more articles, or visit www.red-gate.com\/solutions<\/a> for more information on the benefits of extending DevOps practices to SQL Server databases.<\/p>\n<\/div>\n<\/div>\n\n","protected":false},"excerpt":{"rendered":"
Alex starts of a series of articles on ‘Unit Testing’ your database development work. He starts off by describing five simple rules that make all the difference.…<\/p>\n","protected":false},"author":6776,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4178,4168,4150,4252,4810],"coauthors":[6821],"class_list":["post-290","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-bi","tag-database","tag-sql","tag-t-sql-programming","tag-unit-test-tsql-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/290","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/6776"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=290"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/290\/revisions"}],"predecessor-version":[{"id":68520,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/290\/revisions\/68520"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=290"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=290"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=290"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=290"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}