SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

The Voice of the DBA

Why Don't You Unit Test Code?

First, I'll apologize to those that do use formal, automated tests in advance. I'm not really writing for you, and I'm sure you can teach me more about testing. Perhaps you can comment about how and why you do this, or even write some articles to help others.

For everyone else, why don't you test in a repeatable fashion? Let me know with a comment today.

One thing I've noticed as I've talked DevOps and testing for a number of years is that so many developers agree testing is a good idea, but claim they have no time, no support from management, or no need to repeat tests once code is written. Sometimes I hear all three excuses at the same time. The same people will also admit that they have problems with code at times, and that quality could be improved.

Perhaps it's me, but I've never had my boss sit there and watch me code. He or she doesn't know how I might write code. I certainly write a query, run it, and look at a bunch of rows and see if I think the code is correct. Perhaps I need to run other queries to verify values in the results. However, I could also build a quick test with something like tSQLt, like the one I set up for checking COALESCE that does the same thing. With my unit test, I can run it over and over, modifying the test as I discover new edge cases to validate. Which way do you want to write code?

Even if I have a set of data I need to assemble, it doesn't take much longer for me to write a test, especially when I need to also solve the problem. I find that putting a test together forces me to slow down and think about the rules for my code. Since I need to come up with some result set, building that into a test of some sort is fairly easy and let's me double check how I think data will change. I can then repeat the test over and over as I modify code. I can even grab sample data from production and use that as the basis for my unit test if I need to check a specific entity's values.

I know many things I write in SQL might not be worth a test. Often the simple CRUD queries and basic aggregations aren't things I'd test, but when my query becomes complex, includes APPLY or outer joins, and logic to decide how to filter or format data, wouldn't a unit test make sense? What about if you want to be sure you're processing the correct rows from a large CUBE or ROLLUP operator? Certainly if I'm fixing a bug, writing tests makes sense, at least I think so.

Let me know what you think today.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.0MB) podcast or subscribe to the feed at iTunes and Libsyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

SQL Monitor

Don’t just fix SQL Server problems, prevent them from happening

SQL Monitor helps teams looking after SQL Server be more proactive. Not only does SQL Monitor alert you to current issues, it gives you the information you need to stop them happening in the future. Download SQL Monitor now and get a 14 day free trial

SQL Prompt

Could your SQL coding be more efficient?

Data Platform MVPs and SQL Server experts share their tips on how to standardize formatting whilst stripping out the repetition of coding with SQL Prompt. Over these 20 short videos we will help you write better, shareable SQL faster. Check out the tips

Featured Contents


8 Ways to Export SQL Results To a Text File

Daniel Calbimonte from SQLServerCentral.com

This tip will show eight ways to export the results of a query to a text file. More »


SQL Server Integration Services SSIS 2016 Tutorial

Additional Articles from MSSQLTips.com

SQL Server Integration Services (SSIS) is the integration and ETL (extract – transform – load) tool in the Microsoft Data Platform stack. SSIS is typically used in data warehousing scenarios, but can also be used in common data integration use cases or just to move data around. SSIS is used behind the scenes in the Maintenance Plans of SQL Server and in the Import/Export wizard. More »


The SQL Prompt Functionality Finder

SQL Prompt does a lot more than just intellisense - in this article, Phil Factor provides a handy table showing all of the tool's functions and where to access them. More »


From the SQLServerCentral Blogs - Getting a New R Package–#SQLNewBlogger

Steve Jones from SQLServerCentral Blogs

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as... More »


From the SQLServerCentral Blogs - The docker kill command

Andrew Pruski from SQLServerCentral Blogs

When running demos and experimenting with containers I always clear down my environment. It’s good practice to leave a clean... More »

Question of the Day

Today's Question (by Steve Jones):

What is returned by this code?

SELECT '1,,,,,,'+$0

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: T-SQL.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.


Pro SQL Server Relational Database Design and Implementation

Learn effective and scalable database design techniques in a SQL Server 2016 and higher environment. This book is revised to cover in-memory online transaction processing, temporal data storage, row-level security, durability enhancements, and other design-related features that are new or changed in SQL Server 2016. Get your copy today from Amazon.

Yesterday's Question of the Day

Yesterday's Question (by Avinash):

When columns with different datatypes are compared in SQL joins or if we assign variables of one datatype with variables of other datatype, then SQL server implicitly converts datatype based on their precedence. If value can't be converted SQL will generate data type conversion error. What will be the output datatype from this code:

    @txt VARCHAR(20),
    @dt  DATETIME,
    @i   INT,
    @bin VARBINARY(20);
SET @txt = '2017-01-01';
SET @dt = '2001-01-01';
SELECT result = @dt + @txt

Answer: datetime


The result is a datetime variable. The precedence will convert the string to a datetime and that is the result.

This can be checked by using this select statemetn at the end and exec.

SELECT result = @dt + @txt
 INTO t;

EXEC sp_help t

Ref: Data Type Precedence - click here

» Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2016 : SQL Server 2016 - Administration

Seriously poor performance after upgrading to 2016.... - A database was upgraded to 2016 and certain queries (views) are exhibiting terrible performance.  On the previous server (2008 R2),...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Using Date in Where Condition - I Have issues in selecting records when using date in  my where clause   1 )  I am trying to fetch records for...

Stored Procedure comparison -same SP with text changes - We have 2 different same stored procedures in 2 different databases .We want to see the changes done before and...

SSIS : csv File with different structure - Dear all, I receive from a provider a csv file. Sometimes this file end on column 3 but other times after the...

Having clause - Hi, Need to add having clause as tansaction_count>1 after group by clause in below query . Transaction_count is exist in table dbo.TABLEW Any...

SQL Server 2014 : Administration - SQL Server 2014

Replication between versions - Howdy all.   I am attempting to set up transactional replication between on prem SQL 2014 SP1 to AWS SQL server 2016....

SQL Server 2014 : Development - SQL Server 2014

SQl to calculate average time for call centre agents - A row is created in the CALLSTATUSLOGGING table every time an agent's state is changed. A row is also inserted...

Comparing 2 functions for any differences in the result set. - Recently, we've done some optimizations to the functions and now the BI Manager has tasked me with a request to...

SQL Server 2012 : SQL 2012 - General

Migration from SQL 2005 to 2012 - Hi I am involved in a project of server migration which needs moving from SQL 2005 (Enterprise Edition - 64 bit - SP2)...

SQL Server 2012 : SQL Server 2012 - T-SQL

Assistance with Query - Hello All I have the following table... CREATE TABLE .(      (128) NOT NULL,      (128) NOT NULL,      (20, 3) NOT NULL,      (5)...

Indexing strategy on a highly transient table - Hi, I'm just looking for a disscussion about the best indexing strategy for a table I have that's in a...

Execution time Improvement - Knowing the problem, but not the solution - Hi all, A query I've got isn't running particulary fast (a bout 2 and a bit minutes). I know, in the...

SQL Server 2008 : SQL Server 2008 - General

SSPI handshake failed with error code 0x8009030c...Someone help please - a new instance of SQL Server 2008 was installed and it would only allow you enter with Windows authentication. When...

SQL Server 2008 : T-SQL (SS2K8)

Find out the number of records in all those tables where a given field appears - Hello Experts I want to look at the various characteristics of the tables in which a column with a specified name...

SQL Server 2008 : SQL Server 2008 Administration

Deadlock Graph from the xml from system_health extended event - How to get the Deadlock Graph from the xml extracted from system_health extended event. I'm using SQL Server 2008R2 SP2, and...

Data Warehousing : Integration Services

SSIS package shows successful but not working - I have an SSIS package that was executing successfully for some time but last week started erroring.  The error on...

SQL Server 2005 : Administering

Hash value of sql server login - Hi  I have passwords I need to change on various sql server versions...2005 upwards to 2012 It would be useful to be...

SQL Server 2005 : SQL Server 2005 General Discussion

Data Modelers - Alternatives to Erwin - I have been asked to find data modeling tools to serve as an alternative to Computer Associate's Erwin. I downloaded...

SQL Server 2005 : SQL Server 2005 Strategies

Tool for DATA MODELLING - Dear All, Can anyone suggest me a good tool for DATA MODELLING pls...? I have never used one. Thanks in advance. Santhu.

SQL Server 7,2000 : In The Enterprise

SQL ADSI Query limitation - Hi, I am trying to query a list of 2500 users out of the AD in SQL Server with the following...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com