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

The Voice of the DBA

A T-SQL Code Testing Guide

This editorial was originally published on Oct 7, 2014. It is being re-published as Steve is on holiday.

Do we need a guide for code like this one? The piece linked is from a programmer at Google who gives some code review items that should be tested for. I'm wondering we we might want to have something similar for T-SQL code?

I've given a few talks on how I to get started with tqslt, which is a great framework for unit testing T-SQL code. As I've learned more about it, and experimented, I've been amazed by how flexible it is and how it makes it easy to setup and run tests on T-SQL code. However I can only experiment with the way I code and the tests I think of, and I'm sure there are many other ways in which we can better verify that our queries work as expected.

Testing has changed since I developed software in VB and C++. It seems that much thought has been given to building better unit testing that can not only catch bugs, but also ensure the code performs as we expect it to. There's also the idea that we can run these tests in an automated fashion. That helps prevent some bug when we first write code, but it more often ensures code continues to work as we re-factor our work and enhance our applications. More and more I think that the constant and continuous regression testing is at least as important as initial testing, if not more so.

I really hope that we improve our development processes and testing methodologies for SQL code. It seems that it's an area where we can also increase the speed at we build better database applications, and improve the quality at the same time. If any of you are formalizing the testing of your T-SQL code, we would be interested in publishing your thoughts and results for others to learn from. Please feel free to submit an article.

Steve Jones from SQLServerCentral.com

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

How to make your SQL Server development GDPR ready

Redgate’s SQL Data Privacy Suite helps you to build a data catalog of your SQL Server estate, and put in processes to protect and monitor personal information. Find out more about how our tools help with GDPR compliance

Become a more efficient SQL developer with SQL Prompt

Learn how to write SQL faster and more efficiently with these exclusive short videos from Data Platform MVPs and SQL Server experts. With SQL Prompt you can strip out the repetition of coding and write SQL 50% faster. Check out the tips

Featured Contents

Soundex - Experiments with SQLCLR Part 3

David Poole from SQLServerCentral.com

Acknowledging fundamental design flaws lead to a more flexible, maintainable phonetic framework More »

How Check Constraints MIGHT Improve Your Queries and Missing Index Requests

Additional Articles from Brent Ozar Unlimited Blog

In theory, you get better execution plans when SQL Server understands the contents of your tables. In practice, well, it's hit or miss. More »

The Benefits of Adopting Compliant Database DevOps at PASS

The GDPR presented a challenge for PASS - a community of over 250,000 data professionals with almost 300 Local Groups around the world and 24 Virtual Groups. This whitepaper shows how PASS were able to introduce compliance by default with the new data privacy regulations by using Redgate's solutions to apply DevOps practices to the database. More »

From the SQLServerCentral Blogs - Power BI Monthly Digest – November 2018

Devin Knight from SQLServerCentral Blogs

In this month’s Power BI Digest with my friend Manuel Quintana [Blog | Twitter] and I will again guide you through... More »

From the SQLServerCentral Blogs - Parameterized dynamic SQL is parameterized.

Kenneth Fisher from SQLServerCentral Blogs

Ok, that title sounds silly, but it’s actually a real point. The first parameterized refers to using parameters within dynamic... More »

Question of the Day

Today's Question (by Steve Jones):

I have this code:

 DECLARE @tags NVARCHAR(400) = N'Eagles,,Broncos,,Seahawks,Ravens' SELECT value FROM STRING_SPLIT(@tags, ',')

How many rows are returned?

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: SPLIT_STRING().

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

The Phoenix Project: A Novel about IT, DevOps, and Helping Your Business Win

The company's new IT initiative, code named Phoenix Project, is critical to the future of Parts Unlimited, but the project is massively over budget and very late. The CEO wants Bill to report directly to him and fix the mess in ninety days or else Bill's entire department will be outsourced. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I have a table of places in a US Census survey. I run this:

 select name from census_survey where place_name like 'Turkey%'

What is returned?

Answer: 4

Explanation:

Happy Thanksgiving.

According to the US Census, there are four places in the US with turkey in the name.

Turkey Creek, AZ
Turkey City, TX
Turkey Creek Village, LA
Turkey Town, NC.

Ref: US Census Thanksgiving - 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 2017 : SQL Server 2017 - Development

Exif data into a database. - Hello All, With the ExifTool by Phil Harvey I make a number of .CSV files from different media (harddisk, USB-stick, CD-rom's...

How to study the layout of a database? - Hello friends, Suppose you download a sample database, whether it be AdventureWorks, Northwind, or whatever other database. What steps do you...

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

how do i do a partial string search in a Full Text Index? - Hi I've set up a full text catalog and index on a column in my table. I need to mimic the exact...

Inner joins - Hi all. Our current version is 2008 and we are going to migrate it to 2016 or 2017. In many stored procedures,...

SQL Server 2014 : Administration - SQL Server 2014

slow query - the query has a Where clause that 2 conditions combined via AND. If only the first condition is run it...

Slow run time - I have a query which is running slow for approx 15 mins, however, the slow run time has no impact...

Unable to execute SP - Hi, I have a colleague we cannot execute an SP they get an error saying: User does not have permission...

SQL Server 2014 : Development - SQL Server 2014

Case when inside of partition by? - Hi, I have the following situation: CREATE TABLE #TAB1( ID INT ,MY_STATE VARCHAR(10) ,EVENT_TS DATETIME ) INSERT INTO #TAB1(ID, MY_STATE, EVENT_TS) VALUES (1, 'STATE_1', GETDATE()) ...

Select only one register with condition - Hi Guys, I need a help for a query development that returns only one record according to the condition. But if...

SSIS Package Foreach Loop Container not finding files when deployed (fine in DEV) - Hi there, I have a SSIS 2014 package using the Project Deployment model and parameters. I have a project parameter called...

SQL Server 2012 : SQL 2012 - General

Basic Question: Do you have to restart the instance if you change database or log file growth - Hi All, I have changed the default autogrowth to 300000MB. Do I need to restart the server instance in order...

How to add new column in a CDC(Change Data Capture) script for a table - we have a requirement where we want to enable CDC on newly added columns of table, but I cannot disable...

SQL Agent Jobs showing NO history - Hi - We migrated to SQL 2012 and now a few SQL Jobs don't seem to have job history (and I...

SQL Server 2008 : SQL Server 2008 - General

how to find the lowest usage of a database - Hi Guys How do we find the lowest usage of a database ? I need to find the best time to ...

Programming : Powershell

parse string and check for valid date - I have a flat file with a bar(|) delimiter. It has no headers, but I want to check columns(11,12,13) for...

Programming : SSDT

What is the right answer here, or how do I determine what the right answer is? - I put in a ticket to get my team into TFS to manage our database, IT is asking me this...

Data Warehousing : Integration Services

SSIS: How to load a data from a table to a file, into 50 000 batches (50 000 rows at a time) - Hi, I have a table with 200 000 records, I need to send a file with 50 000 records. I'm using...

Data Warehousing : Data Transformation Services (DTS)

DTS and Type Mismatch error - Any solutions to the DTS and Type Mismatch error (MS KB ref: Q310473). Microsoft says that it is an error...

SQL Server 2005 : SQL Server 2005 Integration Services

Problem using expression in SSIS - Hi, Below is the expression i am trying to make work : substring ("Archive_Full_20120731200002",1, (FINDSTRING( "Archive_Full_20120731200002","Full_", 1)-2)) Whe I use the exact string(Archive_Full_20120731200002)...

SQL Server 7,2000 : T-SQL

Natural Sort Order - Hello. I have a table that has a column of nvarchar that has rows containing both numbers and letters... Ex:...

This email has been sent to newsletter@newslettercollector.com. 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.