SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

Don't Use Code Coverage

I got asked in a seminar recently how to perform code coverage for T-SQL. There aren't a lot of options, but there is a project from Ed Elliot called SQLCover that looks to examine how much of your code gets called from a test. There may be other methods, but my question back was why does code coverage matter? There were a few reasons given by various people, but ultimately the original questioner said that their boss wanted a report.

That's fine, and it's a valid reason to try and measure code coverage, but is that an effective use of developer time? I don't think so, especially as trying to meet some code coverage goal is fraught with all sorts of issues. Apart from various good points on Stack Overflow, no shortage of people have blogged about the problems of relying heavily on a code coverage metric. I won't repeat all the arguments, but I'll give a few of my own.

First, someone pointed out that you want to be sure all your code is tested. I disagree with that, especially in many  long dev cycle applications. There are all sorts of enhancements I've seen in many applications that never get used. While you should certainly check them for security issues, if the features aren't being used, remove them. Since most of you don't implement tests for security issues, I'm not sure there's value in telling you to write a unit test for logic or functionality.

The other issue with code coverage is that anyone other than a developer using this metric will put pressure on the developers to write more tests. Some developers may write good tests, but I wouldn't be surprised to have tests written that always pass and aren't checked for correctness. Tests like these are misleading and potentially create more bugs since anyone refactoring code might think their changes haven't caused an issue if the test passes. Bad tests, IMHO, are worse than no tests.

I do think that code coverage can be valuable. If a developer is looking to ensure that some part of their code is being checked, especially in long stored procedures, then code coverage can guide them to a place that might need  testing. Since most code needs more than one test, covering more than one case, code coverage doesn't help. I'd prefer a developer spent more time thinking about how to build better tests (or experimenting) than looking at coverage reports.

And if you need a code coverage report, try my technique for building one

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 ( 3.8MB) 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 Clone

SQL Clone: Now supporting databases up to 64TB

Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. Try it free

SQL Compare

The industry standard for comparing and deploying SQL Server database schemas

Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. Download your free trial

Featured Contents


Azure DWH part 18: Working with SQL Server Analysis Services

Daniel Calbimonte from SQLServerCentral.com

In this new article, we will learn how to create a cube extracting data from ADWH. More »


Unearthing Bad Deployments with SQL Monitor and Redgate’s Database DevOps Tools

Sudden performance issues in SQL Server can have many causes, ranging all the way from malfunctioning hardware, through to simple misconfiguration, or perhaps just end users doing things they shouldn’t. But one particularly common culprit is when deployments go wrong: I don’t know a single DBA who hasn’t been burned by a bad release. More »


Indexing for Windowing Functions: WHERE vs. OVER

Additional Articles from Brent Ozar Unlimited Blog

Erik Darling shows that if you take SQL Server's word when it asks for an index, things can go horribly awry. More »


From the SQLServerCentral Blogs - Checking Out SQL Vulnerability Assessment

Arun Sirpal from SQLServerCentral Blogs

Apparently there is a new tool from Microsoft where you can discover, track, and remediate potential database vulnerabilities. This tool... More »


From the SQLServerCentral Blogs - SQLCLR vs. SQL Server 2017, Part 6: “Trusted Assemblies” – Whitelisted Assemblies can’t do Module Signing

Solomon Rutzky from SQLServerCentral Blogs

“Trusted Assemblies”, a new feature starting in SQL Server 2017, is a means of whitelisting Assemblies that one feels pose... More »

Question of the Day

Today's Question (by Steve Jones):

A smalldatetime type uses half the storage of a datetime or datetime2 type. In which year will I run out of storage capability for smalldatetime?

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: smalldatetime.

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


Exam Ref 70-761 Querying Data with Transact-SQL

Prepare for Microsoft Exam 70-761–and help demonstrate your real-world mastery of SQL Server 2016 Transact-SQL data management, queries, and database programming. Designed for experienced IT professionals ready to advance their status, Exam Ref focuses on the critical-thinking and decision-making acumen needed for success at the MCSA level. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

What is returned by this code?

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

Answer: 1.00


The $0 is a money datatype. This is a higher precedence than char, so the conversion occurs from char to money. In this case, the commas are treated as separators. Since the value is 1, there are no thousands.

Ref: Data Type Precedence - click here

» Discuss this question and answer on the forums

Featured Script

SQL Server procedure to script tables

Gerrit Mantel from SQLServerCentral.com

In the SSMS you can generate CREATE statements per object by using the object context menu, or generate CREATE statements for multiple objects by using the "Generate script" wizard.
To generate CREATE statements of objects by using T-SQL is not natively implemented in the SSMS. However the system table sys.sql_modules contains the CREATE statements of procedures, functions, triggers and views. It's not that hard to derive that code and dump it to files.
I always wanted to script tables too, in a way SSMS can do it for me. I searched in several blogs for a way to script tables. Most of the solutions are scripts that extract definitions from sys tables, but they all had flaws. How does SSMS do it? Well it uses dll's on the background. You can use them in Powershell by yourself too, but I like to keep everything on board of a procedure. So I created two procedures (dbo.prc_script_tables_multi and dbo.prc_script_tables_one) to script tables to files. The first one scripts every table (of a given database) to an individual file, the second one scripts all tables to one file. Both procedures create a temporarily Powershell script, launches it, and will be deleted afterwards. The Powershell script delivers the scriptfile(s) to the given path.

The Powershell script is based on this blog thread:
click here

Documentation of the Microsoft.SqlServer.Management.Smo ScriptingOptions Class
click here

The two main procedures use 2 functions (dbo.fun_FolderExist and dbo.fun_FileExist) and 1 other procedure (dbo.prc_save_text_to_file) all are in the source code. Also OLE Automation and xp_cmdshell has to be enabled on the server.

The current settings produce (in my opinion) a normal CREATE TABLE script. Only the IF NOT EXISTS BEGIN END block addition can be influenced by the parameter @includeifnotexists.

With a little tweaking it is possible to change the procedure to your own wishes.

Remember to always test new procedures on a test environment.

Happy computing!

Gerrit Mantel, LUMC, The Netherlands

Comments for the main procedures

SQL Server procedure dbo.prc_script_tables_multi

Script all tables for a given database to individual script files on a given path.

 @dbname NVARCHAR(128), default '', Database name
 @path VARCHAR(265), default '', Path to dump scripts to
 @includeifnotexist BIT, default False, Include "IF NOT EXIST {table} BEGIN ... END" logics surrounding CREATE TABLE statement.
Database must be on current server.
Path to dump files to must exist, and you must have read and write permissions.
The script files will be created on the path: @path\@dbname\TABLE
The filenames are in format [{schema}].[{tablename}].sql

Illegal DOS characters in tablenames will cause the powershell script to fail: \ / : * ? " < > |

EXEC dbo.prc_script_tables_multi @dbname='PROG', @path='C:\Data\Prog';
EXEC dbo.prc_script_tables_multi @dbname='PROG', @path='C:\Data\Prog', @includeifnotexists=1;

Used objects:
 - dbo.fun_FolderExist (User Defined-Function in local database).
 - dbo.fun_FileExist (User Defined-Function in local database).
 - dbo.prc_save_text_to_file (Stored procedure in local database)

SQLServer configuration:
 - OLE Automation has to be enabled.
 - xp_cmdshell has to be enabled

SQL Server procedure dbo.prc_script_tables_one

Script all tables for a given database to one script file on a given path

 @dbname NVARCHAR(128), default '', Database name
 @path VARCHAR(265), default '', Path to dump scripts to
 @includeifnotexist BIT, default False, Include "IF NOT EXIST {table} BEGIN ... END" logics surrounding CREATE TABLE statement.

Database must be on current server.
Path to dump file to must exist, and you must have read and write permissions.
The script file that will be created is: @path\@dbname$TABLE.sql

EXEC dbo.prc_script_tables_one @dbname='PROG', @path='C:\Data\Prog';
EXEC dbo.prc_script_tables_one @dbname='PROG', @path='C:\Data\Prog', @includeifnotexists=1;

Used objects:
 - dbo.fun_FolderExist (User Defined-Function in local database).
 - dbo.fun_FileExist (User Defined-Function in local database).
 - dbo.prc_save_text_to_file (Stored procedure in local database)

SQLServer configuration:
 - OLE Automation has to be enabled.
 - xp_cmdshell has to be enabled

More »

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

How to troubleshoot this database update or insert schedule scenraio? - Hello  We have  sql Database which is fetching modified records  from a  linked server  of another team. There is a  stored...

Always-On. ALLOW_CONNECTIONS - Hi  I have 2 servers connecting by hadr  with listner. All my reports and ETL work on the secondary . I have reports that run...

SQL Server Audit logs - Is there anyway to export SQL Audit logs into notepad or Excel for auditing purpose? After successfully created the auditing...

SSMS version for SQL Server 2016 - We are in the process of upgrading our servers from SQL Server 2008R2 to 2016. What version of SSMS would...

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...

SSIS : Convert MM/DD/YYYY into DD/MM/YYYY - Dear all, I am receiving from a excel file a string with the format (MM/DD/YYYY). I would like to transform it...

SQL Server 2014 : Development - SQL Server 2014

query format - CREATE TABLE .(  NULL,   (50) NULL ) ON INSERT INTO .(,) VALUES(1,'Architect') INSERT INTO .(,) VALUES(2,'Developers') INSERT INTO .(,) VA

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...

SQL Server 2012 : SQL 2012 - General

SSRS average column group value for a row group - Hi All, So I'm having a fight with Reporting Services at the minute when trying to compute an average at the...

SQL Server 2012 : SQL Server 2012 - T-SQL

Compare two columns - Hi guys,   need quantity from previous row , when prev_orderid=orderid from previous row. for ex:  If prev_orderid=44086564 matches orderid = 44086564 then i need to...

SQL Server 2008 : SQL Server Newbies

IO and drive latency - Good morning Experts, What is a good value for avg_io_stall_ms and pending_io_requests? I am asking this to see if my server is...

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