Data Management - Author(s): George Mastros (gmmastros)

Finding Exact Duplicate Indexes

George Mastros (gmmastros) — Thu, 12 Apr 2012 17:14:00 +0000

This blog is not meant to be a comprehensive explanation of indexes. It is meant to help you determine if there are duplicate indexes within your database. There appears to be some debate regarding what is a duplicate index. This article defines an “Exact Duplicate Index” where there are multiple non-clustered indexes with exactly the same keys and include columns.

This query will ignore clustered indexes.
It is possible to create a non-clustered index with keys that match a clustered index. Sometimes, this could be considered a duplicate index, and sometimes it should not be considered a duplicate. For example, suppose you have a wide table so that there is only one row per data page. Also suppose you have a query that only needs to use the key columns. It will be faster to use a non-clustered index for the query instead of the clustered index because the non-clustered index would only include the key columns, and you will get more columns per data page. The non-clustered index would require less file I/O, and therefore increased performance.

If you have a wide table with many rows, a non-clustered index that matches the key columns of the clustered index will improve performance.

This query will ignore partial duplicates with similar keys.
It is possible to create indexes with similar keys, and sometimes it is beneficial to do so. For example, suppose you had an index with key columns (Col1, Col2) and another with (Col1, Col2, Col3). You probably don’t need the first index because the second index will suffice. However, there are cases where Col3 could be wide and using it would cause extra I/O compared to the first index. It is probably rare that you would see any difference in performance between these indexes. I will be writing another article that explains this in depth and provides a query that will return these possible duplicates.

This query will ignore partial duplicates with the same keys and similar includes.
Column ordering for include columns does not matter, but which columns are included can make a big difference in the performance of a query. There could be similar indexes that have the same key columns but different include columns. This can affect the size of the index and cause more I/O. There will be another blog that explains this in depth and provides a query to identify duplicate indexes with similar includes.

The query to identify exact duplicates:

Code: tsql

; With IndexColumns As
(
select I.object_id,
I.index_id,
SubString(
(
select ',' + Convert(VarChar(10), Column_id)
from sys.index_columns as k
where k.object_id = i.object_id
and k.index_id = i.index_id
and is_included_column = 0
Order By key_ordinal
for xml path('')
), 2, 1000) As KeyColumns,
SubString(Coalesce(
(
select ',' + Convert(VarChar(10), Column_id)
from sys.index_columns as k
where k.object_id = i.object_id
and k.index_id = i.index_id
and is_included_column = 1
Order By column_id
for xml path('')
), ''), 2, 1000) As IncludeColumns
From sys.indexes As I
Inner Join sys.Tables As T
On I.object_id = T.object_id
Where I.type_desc <> 'Clustered'
And T.is_ms_shipped = 0
)
Select Object_Name(AIndex.object_id) As TableName,
AIndex.name As Index1,
bIndex.Name As Index2,
'Drop Index [' + bIndex.Name + '] On [' + Object_Name(AIndex.object_id) + ']' As DropCode
From IndexColumns As A
Inner Join IndexColumns As B
On A.object_id = B.object_id
And A.index_id < B.index_id
And A.KeyColumns = B.KeyColumns
And A.IncludeColumns = B.IncludeColumns
Inner Join sys.indexes As AIndex
On A.object_id = AIndex.object_id
And A.index_id = AIndex.index_id
Inner Join sys.indexes As BIndex
On B.object_id = BIndex.object_id
And B.index_id = BIndex.index_id

When you run this query, you will see two columns in the output showing you the duplicates. Since these are exact duplicates, you can drop one of them. Which one you drop is up to you.

Original post blogged on LessThanDot.

Do not use reserved keywords for your column names

George Mastros (gmmastros) — Mon, 19 Mar 2012 16:16:00 +0000

Column names and table names should not use reserved keywords in your database. You can use reserved keywords because SQL Server will allow it. However, this should not be done because it makes writing and reading queries more difficult. When you use a reserved keyword for a column or table name, you need to use [square brackets] around the name.

The following query compares your column names against a list of identified keywords. Some of the keywords are SQL Server reserved words, some are ODBC reserved words, and the rest are future reserved words.

How to detect this problem:

tsql

Line number Off | Hide | Select all

Declare @Temp Table(Data VarChar(50))
Insert Into @Temp Values('ABSOLUTE')
Insert Into @Temp Values('ACTION')
Insert Into @Temp Values('ADA')
Insert Into @Temp Values('ADD')
Insert Into @Temp Values('ADMIN')
Insert Into @Temp Values('AFTER')
Insert Into @Temp Values('AGGREGATE')
Insert Into @Temp Values('ALIAS')
Insert Into @Temp Values('ALL')
Insert Into @Temp Values('ALLOCATE')
Insert Into @Temp Values('ALTER')
Insert Into @Temp Values('AND')
Insert Into @Temp Values('ANY')
Insert Into @Temp Values('ARE')
Insert Into @Temp Values('ARRAY')
Insert Into @Temp Values('AS')
Insert Into @Temp Values('ASC')
Insert Into @Temp Values('ASSERTION')
Insert Into @Temp Values('AT')
Insert Into @Temp Values('AUTHORIZATION')
Insert Into @Temp Values('AVG')
Insert Into @Temp Values('BACKUP')
Insert Into @Temp Values('BEFORE')
Insert Into @Temp Values('BEGIN')
Insert Into @Temp Values('BETWEEN')
Insert Into @Temp Values('BINARY')
Insert Into @Temp Values('BIT')
Insert Into @Temp Values('BIT_LENGTH')
Insert Into @Temp Values('BLOB')
Insert Into @Temp Values('BOOLEAN')
Insert Into @Temp Values('BOTH')
Insert Into @Temp Values('BREADTH')
Insert Into @Temp Values('BREAK')
Insert Into @Temp Values('BROWSE')
Insert Into @Temp Values('BULK')
Insert Into @Temp Values('BY')
Insert Into @Temp Values('CALL')
Insert Into @Temp Values('CASCADE')
Insert Into @Temp Values('CASCADED')
Insert Into @Temp Values('CASE')
Insert Into @Temp Values('CAST')
Insert Into @Temp Values('CATALOG')
Insert Into @Temp Values('CHAR')
Insert Into @Temp Values('CHAR_LENGTH')
Insert Into @Temp Values('CHARACTER')
Insert Into @Temp Values('CHARACTER_LENGTH')
Insert Into @Temp Values('CHECK')
Insert Into @Temp Values('CHECKPOINT')
Insert Into @Temp Values('CLASS')
Insert Into @Temp Values('CLOB')
Insert Into @Temp Values('CLOSE')
Insert Into @Temp Values('CLUSTERED')
Insert Into @Temp Values('COALESCE')
Insert Into @Temp Values('COLLATE')
Insert Into @Temp Values('COLLATION')
Insert Into @Temp Values('COLUMN')
Insert Into @Temp Values('COMMIT')
Insert Into @Temp Values('COMPLETION')
Insert Into @Temp Values('COMPUTE')
Insert Into @Temp Values('CONNECT')
Insert Into @Temp Values('CONNECTION')
Insert Into @Temp Values('CONSTRAINT')
Insert Into @Temp Values('CONSTRAINTS')
Insert Into @Temp Values('CONSTRUCTOR')
Insert Into @Temp Values('CONTAINS')
Insert Into @Temp Values('CONTAINSTABLE')
Insert Into @Temp Values('CONTINUE')
Insert Into @Temp Values('CONVERT')
Insert Into @Temp Values('CORRESPONDING')
Insert Into @Temp Values('COUNT')
Insert Into @Temp Values('CREATE')
Insert Into @Temp Values('CROSS')
Insert Into @Temp Values('CUBE')
Insert Into @Temp Values('CURRENT')
Insert Into @Temp Values('CURRENT_DATE')
Insert Into @Temp Values('CURRENT_PATH')
Insert Into @Temp Values('CURRENT_ROLE')
Insert Into @Temp Values('CURRENT_TIME')
Insert Into @Temp Values('CURRENT_TIMESTAMP')
Insert Into @Temp Values('CURRENT_USER')
Insert Into @Temp Values('CURSOR')
Insert Into @Temp Values('CYCLE')
Insert Into @Temp Values('DATA')
Insert Into @Temp Values('DATABASE')
Insert Into @Temp Values('DATE')
Insert Into @Temp Values('DAY')
Insert Into @Temp Values('DBCC')
Insert Into @Temp Values('DEALLOCATE')
Insert Into @Temp Values('DEC')
Insert Into @Temp Values('DECIMAL')
Insert Into @Temp Values('DECLARE')
Insert Into @Temp Values('DEFAULT')
Insert Into @Temp Values('DEFERRABLE')
Insert Into @Temp Values('DEFERRED')
Insert Into @Temp Values('DELETE')
Insert Into @Temp Values('DENY')
Insert Into @Temp Values('DEPTH')
Insert Into @Temp Values('DEREF')
Insert Into @Temp Values('DESC')
Insert Into @Temp Values('DESCRIBE')
Insert Into @Temp Values('DESCRIPTOR')
Insert Into @Temp Values('DESTROY')
Insert Into @Temp Values('DESTRUCTOR')
Insert Into @Temp Values('DETERMINISTIC')
Insert Into @Temp Values('DIAGNOSTICS')
Insert Into @Temp Values('DICTIONARY')
Insert Into @Temp Values('DISCONNECT')
Insert Into @Temp Values('DISK')
Insert Into @Temp Values('DISTINCT')
Insert Into @Temp Values('DISTRIBUTED')
Insert Into @Temp Values('DOMAIN')
Insert Into @Temp Values('DOUBLE')
Insert Into @Temp Values('DROP')
Insert Into @Temp Values('DUMMY')
Insert Into @Temp Values('DUMP')
Insert Into @Temp Values('DYNAMIC')
Insert Into @Temp Values('EACH')
Insert Into @Temp Values('ELSE')
Insert Into @Temp Values('END')
Insert Into @Temp Values('END-EXEC')
Insert Into @Temp Values('EQUALS')
Insert Into @Temp Values('ERRLVL')
Insert Into @Temp Values('ESCAPE')
Insert Into @Temp Values('EVERY')
Insert Into @Temp Values('EXCEPT')
Insert Into @Temp Values('EXCEPTION')
Insert Into @Temp Values('EXEC')
Insert Into @Temp Values('EXECUTE')
Insert Into @Temp Values('EXISTS')
Insert Into @Temp Values('EXIT')
Insert Into @Temp Values('EXTERNAL')
Insert Into @Temp Values('EXTRACT')
Insert Into @Temp Values('FALSE')
Insert Into @Temp Values('FETCH')
Insert Into @Temp Values('FILE')
Insert Into @Temp Values('FILLFACTOR')
Insert Into @Temp Values('FIRST')
Insert Into @Temp Values('FLOAT')
Insert Into @Temp Values('FOR')
Insert Into @Temp Values('FOREIGN')
Insert Into @Temp Values('FORTRAN')
Insert Into @Temp Values('FOUND')
Insert Into @Temp Values('FREE')
Insert Into @Temp Values('FREETEXT')
Insert Into @Temp Values('FREETEXTTABLE')
Insert Into @Temp Values('FROM')
Insert Into @Temp Values('FULL')
Insert Into @Temp Values('FUNCTION')
Insert Into @Temp Values('GENERAL')
Insert Into @Temp Values('GET')
Insert Into @Temp Values('GLOBAL')
Insert Into @Temp Values('GO')
Insert Into @Temp Values('GOTO')
Insert Into @Temp Values('GRANT')
Insert Into @Temp Values('GROUP')
Insert Into @Temp Values('GROUPING')
Insert Into @Temp Values('HAVING')
Insert Into @Temp Values('HOLDLOCK')
Insert Into @Temp Values('HOST')
Insert Into @Temp Values('HOUR')
Insert Into @Temp Values('IDENTITY')
Insert Into @Temp Values('IDENTITY_INSERT')
Insert Into @Temp Values('IDENTITYCOL')
Insert Into @Temp Values('IF')
Insert Into @Temp Values('IGNORE')
Insert Into @Temp Values('IMMEDIATE')
Insert Into @Temp Values('IN')
Insert Into @Temp Values('INCLUDE')
Insert Into @Temp Values('INDEX')
Insert Into @Temp Values('INDICATOR')
Insert Into @Temp Values('INITIALIZE')
Insert Into @Temp Values('INITIALLY')
Insert Into @Temp Values('INNER')
Insert Into @Temp Values('INOUT')
Insert Into @Temp Values('INPUT')
Insert Into @Temp Values('INSENSITIVE')
Insert Into @Temp Values('INSERT')
Insert Into @Temp Values('INT')
Insert Into @Temp Values('INTEGER')
Insert Into @Temp Values('INTERSECT')
Insert Into @Temp Values('INTERVAL')
Insert Into @Temp Values('INTO')
Insert Into @Temp Values('IS')
Insert Into @Temp Values('ISOLATION')
Insert Into @Temp Values('ITERATE')
Insert Into @Temp Values('JOIN')
Insert Into @Temp Values('KEY')
Insert Into @Temp Values('KILL')
Insert Into @Temp Values('LANGUAGE')
Insert Into @Temp Values('LARGE')
Insert Into @Temp Values('LAST')
Insert Into @Temp Values('LATERAL')
Insert Into @Temp Values('LEADING')
Insert Into @Temp Values('LEFT')
Insert Into @Temp Values('LESS')
Insert Into @Temp Values('LEVEL')
Insert Into @Temp Values('LIKE')
Insert Into @Temp Values('LIMIT')
Insert Into @Temp Values('LINENO')
Insert Into @Temp Values('LOAD')
Insert Into @Temp Values('LOCAL')
Insert Into @Temp Values('LOCALTIME')
Insert Into @Temp Values('LOCALTIMESTAMP')
Insert Into @Temp Values('LOCATOR')
Insert Into @Temp Values('LOWER')
Insert Into @Temp Values('MAP')
Insert Into @Temp Values('MATCH')
Insert Into @Temp Values('MAX')
Insert Into @Temp Values('MIN')
Insert Into @Temp Values('MINUTE')
Insert Into @Temp Values('MODIFIES')
Insert Into @Temp Values('MODIFY')
Insert Into @Temp Values('MODULE')
Insert Into @Temp Values('MONTH')
Insert Into @Temp Values('NAMES')
Insert Into @Temp Values('NATIONAL')
Insert Into @Temp Values('NATURAL')
Insert Into @Temp Values('NCHAR')
Insert Into @Temp Values('NCLOB')
Insert Into @Temp Values('NEW')
Insert Into @Temp Values('NEXT')
Insert Into @Temp Values('NO')
Insert Into @Temp Values('NOCHECK')
Insert Into @Temp Values('NONCLUSTERED')
Insert Into @Temp Values('NONE')
Insert Into @Temp Values('NOT')
Insert Into @Temp Values('NULL')
Insert Into @Temp Values('NULLIF')
Insert Into @Temp Values('NUMERIC')
Insert Into @Temp Values('OBJECT')
Insert Into @Temp Values('OCTET_LENGTH')
Insert Into @Temp Values('OF')
Insert Into @Temp Values('OFF')
Insert Into @Temp Values('OFFSETS')
Insert Into @Temp Values('OLD')
Insert Into @Temp Values('ON')
Insert Into @Temp Values('ONLY')
Insert Into @Temp Values('OPEN')
Insert Into @Temp Values('OPENDATASOURCE')
Insert Into @Temp Values('OPENQUERY')
Insert Into @Temp Values('OPENROWSET')
Insert Into @Temp Values('OPENXML')
Insert Into @Temp Values('OPERATION')
Insert Into @Temp Values('OPTION')
Insert Into @Temp Values('OR')
Insert Into @Temp Values('ORDER')
Insert Into @Temp Values('ORDINALITY')
Insert Into @Temp Values('OUT')
Insert Into @Temp Values('OUTER')
Insert Into @Temp Values('OUTPUT')
Insert Into @Temp Values('OVER')
Insert Into @Temp Values('OVERLAPS')
Insert Into @Temp Values('PAD')
Insert Into @Temp Values('PARAMETER')
Insert Into @Temp Values('PARAMETERS')
Insert Into @Temp Values('PARTIAL')
Insert Into @Temp Values('PASCAL')
Insert Into @Temp Values('PATH')
Insert Into @Temp Values('PERCENT')
Insert Into @Temp Values('PLAN')
Insert Into @Temp Values('POSITION')
Insert Into @Temp Values('POSTFIX')
Insert Into @Temp Values('PRECISION')
Insert Into @Temp Values('PREFIX')
Insert Into @Temp Values('PREORDER')
Insert Into @Temp Values('PREPARE')
Insert Into @Temp Values('PRESERVE')
Insert Into @Temp Values('PRIMARY')
Insert Into @Temp Values('PRINT')
Insert Into @Temp Values('PRIOR')
Insert Into @Temp Values('PRIVILEGES')
Insert Into @Temp Values('PROC')
Insert Into @Temp Values('PROCEDURE')
Insert Into @Temp Values('PUBLIC')
Insert Into @Temp Values('RAISERROR')
Insert Into @Temp Values('READ')
Insert Into @Temp Values('READS')
Insert Into @Temp Values('READTEXT')
Insert Into @Temp Values('REAL')
Insert Into @Temp Values('RECONFIGURE')
Insert Into @Temp Values('RECURSIVE')
Insert Into @Temp Values('REF')
Insert Into @Temp Values('REFERENCES')
Insert Into @Temp Values('REFERENCING')
Insert Into @Temp Values('RELATIVE')
Insert Into @Temp Values('REPLICATION')
Insert Into @Temp Values('RESTORE')
Insert Into @Temp Values('RESTRICT')
Insert Into @Temp Values('RESULT')
Insert Into @Temp Values('RETURN')
Insert Into @Temp Values('RETURNS')
Insert Into @Temp Values('REVOKE')
Insert Into @Temp Values('RIGHT')
Insert Into @Temp Values('ROLE')
Insert Into @Temp Values('ROLLBACK')
Insert Into @Temp Values('ROLLUP')
Insert Into @Temp Values('ROUTINE')
Insert Into @Temp Values('ROW')
Insert Into @Temp Values('ROWCOUNT')
Insert Into @Temp Values('ROWGUIDCOL')
Insert Into @Temp Values('ROWS')
Insert Into @Temp Values('RULE')
Insert Into @Temp Values('SAVE')
Insert Into @Temp Values('SAVEPOINT')
Insert Into @Temp Values('SCHEMA')
Insert Into @Temp Values('SCOPE')
Insert Into @Temp Values('SCROLL')
Insert Into @Temp Values('SEARCH')
Insert Into @Temp Values('SECOND')
Insert Into @Temp Values('SECTION')
Insert Into @Temp Values('SELECT')
Insert Into @Temp Values('SEQUENCE')
Insert Into @Temp Values('SESSION')
Insert Into @Temp Values('SESSION_USER')
Insert Into @Temp Values('SET')
Insert Into @Temp Values('SETS')
Insert Into @Temp Values('SETUSER')
Insert Into @Temp Values('SHUTDOWN')
Insert Into @Temp Values('SIZE')
Insert Into @Temp Values('SMALLINT')
Insert Into @Temp Values('SOME')
Insert Into @Temp Values('SPACE')
Insert Into @Temp Values('SPECIFIC')
Insert Into @Temp Values('SPECIFICTYPE')
Insert Into @Temp Values('SQL')
Insert Into @Temp Values('SQLCA')
Insert Into @Temp Values('SQLCODE')
Insert Into @Temp Values('SQLERROR')
Insert Into @Temp Values('SQLEXCEPTION')
Insert Into @Temp Values('SQLSTATE')
Insert Into @Temp Values('SQLWARNING')
Insert Into @Temp Values('START')
Insert Into @Temp Values('STATE')
Insert Into @Temp Values('STATEMENT')
Insert Into @Temp Values('STATIC')
Insert Into @Temp Values('STATISTICS')
Insert Into @Temp Values('STRUCTURE')
Insert Into @Temp Values('SUBSTRING')
Insert Into @Temp Values('SUM')
Insert Into @Temp Values('SYSTEM_USER')
Insert Into @Temp Values('TABLE')
Insert Into @Temp Values('TEMPORARY')
Insert Into @Temp Values('TERMINATE')
Insert Into @Temp Values('TEXTSIZE')
Insert Into @Temp Values('THAN')
Insert Into @Temp Values('THEN')
Insert Into @Temp Values('TIME')
Insert Into @Temp Values('TIMESTAMP')
Insert Into @Temp Values('TIMEZONE_HOUR')
Insert Into @Temp Values('TIMEZONE_MINUTE')
Insert Into @Temp Values('TO')
Insert Into @Temp Values('TOP')
Insert Into @Temp Values('TRAILING')
Insert Into @Temp Values('TRAN')
Insert Into @Temp Values('TRANSACTION')
Insert Into @Temp Values('TRANSLATE')
Insert Into @Temp Values('TRANSLATION')
Insert Into @Temp Values('TREAT')
Insert Into @Temp Values('TRIGGER')
Insert Into @Temp Values('TRIM')
Insert Into @Temp Values('TRUE')
Insert Into @Temp Values('TRUNCATE')
Insert Into @Temp Values('TSEQUAL')
Insert Into @Temp Values('UNDER')
Insert Into @Temp Values('UNION')
Insert Into @Temp Values('UNIQUE')
Insert Into @Temp Values('UNKNOWN')
Insert Into @Temp Values('UNNEST')
Insert Into @Temp Values('UPDATE')
Insert Into @Temp Values('UPDATETEXT')
Insert Into @Temp Values('UPPER')
Insert Into @Temp Values('USAGE')
Insert Into @Temp Values('USE')
Insert Into @Temp Values('USER')
Insert Into @Temp Values('USING')
Insert Into @Temp Values('VALUE')
Insert Into @Temp Values('VALUES')
Insert Into @Temp Values('VARCHAR')
Insert Into @Temp Values('VARIABLE')
Insert Into @Temp Values('VARYING')
Insert Into @Temp Values('VIEW')
Insert Into @Temp Values('WAITFOR')
Insert Into @Temp Values('WHEN')
Insert Into @Temp Values('WHENEVER')
Insert Into @Temp Values('WHERE')
Insert Into @Temp Values('WHILE')
Insert Into @Temp Values('WITH')
Insert Into @Temp Values('WITHOUT')
Insert Into @Temp Values('WORK')
Insert Into @Temp Values('WRITE')
Insert Into @Temp Values('WRITETEXT')
Insert Into @Temp Values('YEAR')
Insert Into @Temp Values('ZONE')
Select object_name(C.object_id), C.Name
From sys.columns As C
Inner Join sys.objects As O
On C.object_id = O.object_id
Inner Join @Temp T
On C.Name = Data
Where is_ms_shipped = 0
Order By object_name(C.object_id), C.Name

How to correct it:
Correcting for this type of problem can be challenging because you cannot just willy nilly go around changing column names. You are likely to have code that uses the column with the original name and this code will need to change to accommodate the changed column name. To make matters more difficult, you could have systems outside of the database that are sending dynamic SQL making it more difficult to find all of the occurrences in code.

Original post blogged on LessThanDot.

SQLCop integration with Red Gate's SQL Test

George Mastros (gmmastros) — Wed, 04 Jan 2012 18:52:00 +0000

Approximately a year and a half ago, friends of mine and I created SQLCop. Our motivation was to provide a tool that users can download and run against their database. This tool is very effective at detecting common problems with database configurations and TSQL code. Not every issue highlighted by SQLCop requires a fix, but you are very likely to discover potential problems that you didn't even know you had.

About a year ago, I met Dennis Lloyd, Jr. and Sebastian Meine at a presentation they were giving for tSQLt (a unit testing framework for SQL Server) that they were working on. I was intrigued because I had always wanted to write unit tests for my tSQL code but hadn’t been able to implement anything because of time constraints on my end. The tSQLt framework allowed me to forget about the mechanics of implementing and running the tests and allowed me to focus on writing the tests.

Several weeks ago, I was approached by Justin Caldicott at Red Gate Software. He informed that they had recently released SQL Test which is a SSMS add-in that makes writing and executing tSQLt tests easier.

Justin was interested in including SQLCop tests within SQL Test. I downloaded and installed SQLTest. I was surprised to see that it recognized all of the tests that I had written manually. I also like how easy it is to run tests and create new ones. This tool has already saved me time.

I spent a relatively short amount of time re-writing a couple of the SQLCop tests to run within the tSQLt framework. At this point, there are several tests already written and included with SQL Test (Preview 2).

If you haven't done so already, I encourage you to download and install SQLCop. I would be extremely surprised if you didn’t find something useful with it.

I also encourage everyone to download and install SQL Test so that you can see for yourself how easy it is to run unit tests on your database.

Original post blogged on LessThanDot.

Zero-One-Some Testing

George Mastros (gmmastros) — Tue, 13 Dec 2011 11:33:00 +0000

I met Denis and Sebastian over a year ago when I attended their session on test driven database development. Since then, I have been using tSQLt to add unit tests to my database. The following post was written by Denis Lloyd Jr.

About the Series

Test case heuristics are patterns used to help decide the next test to write and ensure test coverage of requirements. This is the second post on a series of test case heuristics pertaining to database testing.

I'm trying out a new way of delivering series to a wider audience - post sharing! The series home is at: http://testdrivendatabases.com/test-heuristics where you can find links to all articles in the series. The posts will be scattered over a variety of websites and blogs.

Definition:

Zero-One-Some says that if multiple instances of a value are allowed, then there should be a test for zero of them; one of them; and some of them. Zero-One-Some is sometimes referred to as Zero-One-Many and is often related to cardinality in the database.

For example, a view may return multiple records. When testing the view, a test should be written where we expect zero records returned; another test for exactly one record returned; and another test for several rows returned.

Zero-One-Some may be considered both on the input (e.g. a loop that may process multiple values) or the output (e.g. a query that returns multiple rows).

Purpose:

Zero-One-Some testing helps:

Focus on correct behavior when there are multiple inputs or outputs
Clarify the requirements when zero records should be processed; a common source of database defects.
Prevent mistakes when using grouping in queries

Example:

The business would like a report of the number of orders and the total revenue from those orders for each of the last 3 months. The report may look like this:

Month             Number of Orders              Revenue from Orders
Nov 2011          52                            $3582.00
Oct 2011          70                            $12399.50
Sep 2011          30                            $899.55

It is clear from this requirement that multiple orders must be processed. By applying zero-one-some, we are forced to ask the following questions:

If there are no orders in the past 3 months, what should the report display? Should it list each month with 0 orders and $0?
If there are no orders for any particular month, should that month still be listed in the report?
Aggregations are always interesting spots to test. If there is a null value for an order amount, how should that be treated in the sum? If it’s not included in the revenue, should it also not be included in the count?

Notes:

Tests for zero records seem to uncover missing requirements or defects in code involving aggregations or in places where programmers assume that there will simply be data (perhaps because their test database already has data in it).

Whereas tests for one and some records seem to uncover more problems in loops when a specific exit condition is needed.

Tests for multiple ("some") records may also be useful when data can be duplicated. Often we assume that data being processed is unique, but asking the question, "what if there are multiple instances of the same record?" can be illuminating.

Special Cases:

Joins: When multiple tables are joined together in a query, we must often consider the cardinality of the relationship between the tables. Is there a one-to-one relationship between the tables (and is that relationship enforced)? How about a one-to-many or a many-to-many relationship? These impact what tests are needed.

The join type (e.g. inner, left or right outer, full) must also be considered. These are a few of the possibilities:

A record exists in the left table, but there are no matches in the right table.
A record exists in the left table and there is exactly one match in the right table.
A record exists in the left table and has multiple matches in the right table.
A record exists in the right table, but has no matches in the left table.
And so on...

Filters: Zero-one-some is also particularly useful in filters, such as WHERE clauses. Consider the following sub-query, for example:

Code: tsql

SELECT Name
FROM OrderMgmt.Customer
WHERE CustomerId =
(SELECT CustomerId
FROM OrderMgmt.Order
WHERE OrderId = @OrderId)

While this is a simplistic case, the programmer is likely expecting exactly one record to be returned from the sub-query. If the sub-query returns zero or multiple records though, the actual behavior of this query may not be so pleasant.

Original post blogged on LessThanDot.

tSQLt Unit Testing

George Mastros (gmmastros) — Wed, 20 Jul 2011 15:25:00 +0000

Yesterday, I attended a training class at Microsoft’s facility in Malvern, Pennsylvania. This training class was led by Sebastian Meine (sqlity.net) and Dennis Lloyd (curiouslycorrect.com).

The class was from 9:00 am to 5:00 pm with a short break for lunch. During the class, Dennis and Sebastian explained how to use tSQLt (http://tSQLt.org) to write unit tests for your database code. tSQLt is a frame work that can be freely downloaded and applied to your database, allowing you to quickly and easily write unit tests. After learning about the framework and working through the exercises during the class, it is immediately obvious to me how this framework and the techniques explained during the class will benefit my organization.

Specifically, writing unit tests for the database will allow me to re-factor the code in a safe way, making sure that the code doesn’t break because I can easily run all of the unit tests for the database, or just the unit tests associated with the code I am in the process of changing.

Since I already have dozens of views, hundreds of functions and thousands of stored procedures, I cannot take the time to write all the unit tests required for the existing stuff, but I will create unit tests for the new code I write and also unit tests for any bug fixes with the existing code. Over time I will have a set of unit tests for my database code that will undoubtedly allow me to spend less time fixing defects and more time writing new functionality.
With my application, most of the bugs discovered by the end user are data related. The tSQLt unit testing framework will allow me to write tests for those bugs and then have confidence that the bug will not return (in the released version of the software).

Thank you Dennis and Sebastian for teaching this class and showing me this framework. I certainly appreciate it and will be sure to start using it.

Original post blogged on LessThanDot.

Understanding SQL Server 2000 Pivot with Aggregates

George Mastros (gmmastros) — Tue, 08 Mar 2011 13:45:00 +0000

This month's T-SQL Tuesday is being hosted by our very own, Jes Borland (Twitter | Blog). Not only is she hosting this month but she is making it possible for LessThanDot's first T-SQL Tuesday event. The topic that is brought to us is to discuss with everyone how we solved business problems with aggregate functions. I thought this would be a good time to explain how you can write a pivot query in SQL 2000 using aggregate functions. Writing a query is one thing, understanding how it works is another. The goal of this blog post is to explain HOW it works so that it can be applied to other interesting problems.

Why? This database engine is 11 years old, why bother? We are sometimes stuck dealing with an older database engine. Understanding the concept behind this pivot will also allow you to use the technique in other interesting ways. Personally, I think that by understanding how this works is another step in the process of “thinking in sets”.

When we think of pivoting data, we usually want to see several rows of data displayed as additional columns. Often times this is for reporting purposes or GUI displays within an application.

This blog is meant to explain the process of pivoting the data. Sure, many people know how to write a pivot query using aggregate functions, but how many people actually understand it from a set based perspective. Understanding what the code is doing will help us to apply the principals to other queries.

To explain this process, I will use a set of dummy data to aide in visualizing the data. I will also build up to the final query, but examining each step along the way.

The data:

Id          Name                 Value
----------- -------------------- --------------------
1           Name                 George
1           ShoeSize             9.5

2           Name                 Bill
2           ShoeSize             10.5

3           Name                 John
3           ShoeSize             9

4           Name                 Greg
4           ShoeSize             9

The data is relatively simple, but is meant to demonstrate the concept. The goal of this blog is to explain how we can pivot the data shown above in to the following output.

Id          Name                 ShoeSize
----------- -------------------- --------------------
1           George               9.5
2           Bill                 10.5
3           John                 9
4           Greg                 9

As you can see, the original data had the name and shoe size for each person on separate rows. The output has just a single row per person but with additional columns for the data.

Setting up the code....

Code: tsql

Create Table #Temp(Id Int, Name VarChar(20), Value VarChar(20))

Insert Into #Temp Values(1, 'Name','George')
Insert Into #Temp Values(1, 'ShoeSize','9.5')

Insert Into #Temp Values(2, 'Name','Bill')
Insert Into #Temp Values(2, 'ShoeSize','10.5')

Insert Into #Temp Values(3, 'Name','John')
Insert Into #Temp Values(3, 'ShoeSize','9')

Insert Into #Temp Values(4, 'Name','Greg')
Insert Into #Temp Values(4, 'ShoeSize','9')

Our first query will simply show the data.

Code: tsql

Select *
From #Temp

Simple. We see all the data in the table. For our next step, let's set up the output structure. We know we want to see the ID column and the Value column twice, once for the person's name and again for the shoe size.

Code: tsql

Select Id,
Value,
Value
From #Temp

Id          Value                Value
----------- -------------------- --------------------
1           George               George
1           9.5                  9.5
2           Bill                 Bill
2           10.5                 10.5
3           John                 John
3           9                    9
4           Greg                 Greg
4           9                    9

This query is simply duplicating the data in two columns. Ultimately, we will want the second column to show the person's name, and the third column to show the shoe size. For the next step, let's show just the name in the second column and the shoe size in the third. We will do this using a case expression:

Code: tsql

Select Id,
Case When Name = 'Name' Then Value End,
Case When Name = 'ShoeSize' Then Value End
From #Temp

Id                               
----------- -------------------- --------------------
1           George               NULL
1           NULL                 9.5
2           Bill                 NULL
2           NULL                 10.5
3           John                 NULL
3           NULL                 9
4           Greg                 NULL
4           NULL                 9

Take a look at the case expression. Notice that there is no ELSE clause. Without an ELSE, the CASE expression will return NULL. This is extremely important for our end result. However, it's important to realize that the second column has the person's name and NULL, and the third column has shoe size and null. Next we will introduce column aliases.

Code: tsql

Select Id,
Case When Name = 'Name' Then Value End As Name,
Case When Name = 'ShoeSize' Then Value End As ShoeSize
From #Temp

Id          Name                 ShoeSize
----------- -------------------- --------------------
1           George               NULL
1           NULL                 9.5
2           Bill                 NULL
2           NULL                 10.5
3           John                 NULL
3           NULL                 9
4           Greg                 NULL
4           NULL                 9

Nothing fancy here. Just the column names. We still have NULL's in our data that we will want to eliminate. Which leads us to our next step, and the most important part, too. When we use aggregates, it's important to realize that they ignore NULL's in the data. For example, if we have 2 rows with "George" in one row and NULL in the other row, Max(Column) will ignore the NULL and return George. We can use that to our advantage here.

Code: tsql

Select Id,
Min(Case When Name = 'Name' Then Value End) As Name,
Min(Case When Name = 'ShoeSize' Then Value End) As ShoeSize
From #Temp
Group By Id

Id          Name                 ShoeSize
----------- -------------------- --------------------
1           George               9.5
2           Bill                 10.5
3           John                 9
4           Greg                 9

As you can see, we finally got the results we wanted, effectively pivoting the data using code that will comfortably run in SQL2000 (and many other databases, too).

Original post blogged on LessThanDot.

T-SQL Tuesday #13: Is that what you really want?

George Mastros (gmmastros) — Tue, 14 Dec 2010 14:30:23 +0000

Steve Jones (Twitter | Blog), The Voice of the DBA, is hosting the T-SQL Tuesday blogging fest over on SQLServerCentral.com this month. This months topic involves businesses and what they really want.

As many of you know, I am the owner of a small software company that satisfies a niche market. I've been in business for 13 years now, and am still working on the same core application. I constantly find new features and functionality to offer in the app, continually improving it for the benefit of my customers. I think it's safe to say that I understand the technical challenge that my software accommodates, as well as the business implications involved. I also have a very good appreciation for the logistics involved in the business process, and the most efficient ways to accomplish the business goals.

Occasionally, we will come across a customer that thinks their method of accomplishing the business's goals is the only correct one, even though my experience clearly indicates there is a better (more efficient) way. I was talking to Ted/onpnt (Twitter | Blog) yesterday about this very same topic. The conversation went something like this:

George: You should see the frickin hoops I gotta jump through for this one customer. I feel like a whipping boy.
Ted: what did they have you doing??
George: They refuse to use (software) they way it was intended. They want a "Blah Blah Report" where each..... This report is not accurate because (software) wasn't designed this way. Instead, each... then a report showing... should be used instead.
Ted: so they want you to write a report that doesn't even reflect the way the system works.
George: It's like.... well.... trying to use Excel to manage a sql database.
Ted: LOL
George: Yeah. Write a report that magically gets info, never makes a mistake, and base it all on completely unrelated information.
Ted: LOL
George: oh.... and I'm supposed to make it faster, too.
Ted: ROFL but of course!

Whenever I run in to a problem with a customer, I try to use the following steps.

1. Manage expectations at the beginning. This allows you to prevent possible complications in the future by expressing what the customer should reasonably expect from the software.

2. Effectively train the customer in the proper operation of the software. Part of the training process should be the "big picture" because it allows them to see and appreciate your methods for handling the business problems in an efficient manner.

3. When a customer has unrealistic expectations, it's important to explain (at a high level) why they are unrealistic. Explain the benefits of the currently implemented method and the draw backs with their proposed method.

4. When they insist on new/different functionality, you should attempt to work out a compromise that allows your existing functionality to satisfy their requirements. Often times, a small tweak to current functionality will satisfy their requirements.

5. When all else fails, the customer is always right. If you refuse to implement the functionality they want, you run the risk of losing their business, and potentially word-of-mouth business as well.

As developers, we have a responsibility to provide software that makes our customers job easier and more efficient. We also have a responsibility to advise them of alternative methods (new to them) that would improve their efficiency. If all else fails, be prepared to give the customer what they want.

Original post blogged on LessThanDot.

SQLCop update Version 1.1

George Mastros (gmmastros) — Mon, 23 Aug 2010 12:51:00 +0000

This version of SQLCop contains several bug fixes as well as some new features. Many of the items changed in this version were based on feedback received by you, our users. We encourage people to post comments so that we can improve the application.

If you previously downloaded the original version of SQLCop, you should manually download and install this new version: http://sqlcop.lessthandot.com/

Fixes:
1. This version of SQL Cop works with databases that have a binary collation.

2. There was a "load nodes" problem that occurred when your firewall was blocking your connection to our site. This version of the application does require an internet connection the first time it is run so that an XML file can be downloaded. After this first download, you no longer need an internet connection. Without a connection to the internet, you will not see the blog or wiki article that explains the problems, but you will still be able to list the occurrences of the problem in your database.

3. A database connection parameter was changed which will allow SQL Cop to run quicker.

4. Some people were seeing "stub" instead of "No Problems Found". This item has been corrected.

Updates
SQL Cop now checks a very small XML file on startup that is located on our server. If the connection to the server fails, the local version of the issues XML file is used. If SQL Cop can download the configuration xml file, we compare versions of the application and versions of the issues xml. If there is a new application, the user is notified and prompted to download the newer version. If there is a newer version of the issues xml file, it is downloaded and stored automatically.

Checks
We added new checks for:
Code

Procedures with dynamic sql
Procedures using dynamic sql without sp_executesql

Table/View

Unnamed Constraints

Indexes

Forwarded Records

Configuration

Ad Hoc Distributed Queries
CLR
Database and log files on the same physical disk
Database Mail
Deprecated Features
Instant File Initialization
Max Degree of Parallelism
OLE Automation Procedures
Service Account
SMO and DMO
SQL Server Agent Service
xp cmdshell

A new category was added for Health with the following checks.

Buffer cache hit ratio
Page life expectancy

This brings the total number of issues checked to 50 for SQL 2008 and 49 for SQL 2005.

Original post blogged on LessThanDot.

SQL Server Proper Case Function

George Mastros (gmmastros) — Wed, 24 Feb 2010 11:34:43 +0000

SQL Server (T-SQL specifically) is not usually the best place to write a function for modifying the case of your data. String functions are generally slow and often a bit cumbersome to implement. That being said, it's not uncommon to have data in your tables that needs to be "cleaned up a bit". In situations like this, it is acceptable to write and use a function like this.

With SQL, it is easy to convert strings to upper case or lower case, but what about making it mixed case/title case. This functionality is useful in many different situations. There are many examples on the internet that show how this can be done. Some work better than others, and perform better than others. The simplest versions of this will loop through a string, character by character, and capitalize anything following a space. Unfortunately, this is not always adequate.

I have developed a method that actively seeks out any character that is not a letter. Under certain circumstances, this is more appropriate because it satisfies more situations. This method also reduces the number of loops that need to be executed by using the PatIndex function and looking for non-alpha characters followed by lower case alpha characters.

General theory:

Like I stated earlier, this code actively seeks out data from within the string that needs to be changed. The first thing we do is convert the string to lower case except for the first character, which is converted to upper case. For example, suppose you have this: THIS IS-ALL "Caps"

At the beginning of the function, we convert to lower case except for the first character, so it becomes:

This is-all "caps"

Next, we actively seek out positions where one character is non-alphabetic followed by a lower case alphabetic character. In this case, we would return character position 5 (space followed by lower case i). We then use the stuff function to convert this to upper case. This would result in:

This Is-all "caps"

We then repeat this process until there are no more occurrences of non-alpha followed by lower case alpha characters.

This code uses several functions, some that you may not be familiar with. Upper converts characters to upper case. Lower converts characters to lower case. SubString allows you to retrieve data from the middle of string. The less common functions here are PatIndex and Stuff.

PatIndex searches through a string and returns the position where a search pattern is found. In our case, we want to find a "non-alpha character followed by a lower case alpha character". By default, searches are not case sensitive. This is actually controlled by the collation of your database. You can get case sensitive searches by using a binary collation. For this function, I use:

PatIndex('%[^a-zA-Z][a-z]%', @Data COLLATE Latin1_General_Bin)

The other function I use here is stuff. Stuff can be used to insert text in to the middle of a string. It can also be used similar to a Replace function. With replace, you essentially replace all occurrences of one string with another. Stuff is different in that you can replace data based on its position within a string without regard to what is at that position. For example:

Select Stuff('lower case', 7, 1, 'C')

Notice how the 7th character (the lower case c) is replaced with an upper case C.

Code: tsql

Create Function dbo.Proper(@Data VarChar(8000))
Returns VarChar(8000)
As
Begin
Declare @Position Int

Select @Data = Stuff(Lower(@Data), 1, 1, Upper(Left(@Data, 1))),
@Position = PatIndex('%[^a-zA-Z][a-z]%', @Data COLLATE Latin1_General_Bin)

While @Position > 0
Select @Data = Stuff(@Data, @Position, 2, Upper(SubString(@Data, @Position, 2))),
@Position = PatIndex('%[^a-zA-Z][a-z]%', @Data COLLATE Latin1_General_Bin)

Return @Data
End

In my opinion, there are several things that make this function better. It will correctly capitalize all words and it minimizes the number of loops. In fact, it will loop just once for each word that needs to be capitalized.

Original post blogged on LessThanDot.

Missing foreign key constraints

George Mastros (gmmastros) — Fri, 20 Nov 2009 11:32:30 +0000

References are at the heart of a database. It is possible to create a beautiful database with perfectly working front end code that always, 100% of the time, does the right thing with your data. But, writing code is hard. Very hard! Your data is often the most important asset you own. You need to protect it with every bit of technology you can find. At the heart of protecting your data is referential integrity. What does this mean? It means that you shouldn't be missing data, ever!

The code below will check for columns that have ID in the name of the column where that column is not part of a primary key or foreign key constraint. Often times, this represents a missing constraint, but not always. The code presented below exists to highlight potential problems. You must still determine if this potential problem is real, and then act accordingly.

How to detect this problem:

Code: tsql

SELECT C.TABLE_SCHEMA,C.TABLE_NAME,C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
INNER Join INFORMATION_SCHEMA.TABLES T
ON C.TABLE_NAME = T.TABLE_NAME
And T.TABLE_TYPE = 'Base Table'
AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
LEFT Join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE U
ON C.TABLE_NAME = U.TABLE_NAME
And C.COLUMN_NAME = U.COLUMN_NAME
And U.TABLE_SCHEMA = C.TABLE_SCHEMA
WHERE U.COLUMN_NAME IS Null
And C.COLUMN_NAME Like '%id'
ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME

How to correct it: Correcting this problem seems simple at first. Just declare your foreign keys, right? Well, it's not so simple. You see, there could be code running that deletes all the necessary data from the related tables. If you have code that deletes data in related tables in the wrong order, you will get referential constraint errors. Similar problems can occur with updates and inserts. The order in which you do things is important when you have referential constraints.

Level of severity: High

Level of difficulty: High

Original post blogged on LessThanDot.