When one considers the amount of time and effort that Unit Testing consumes for the Database Developer, is surprising how few good SQL Server Test frameworks are around. tSQLt , which is open source and free to use, is one of the frameworks that provide a simple way to populate a table with test data as part of the unit test, and check the results with what should be expected. Sebastian and Dennis, who created tSQLt, explain.
… Read more
The BIT data type is an awkward fit for a SQL database. It doesn't have just two values, and it can do unexpected things in expressions. What is worse, it is a flag rather than a predicate, and so its overuse, along with bit masks, is a prime candidate for being listed as a 'SQL Code Smell'. Joe Celko makes the case.
… Read more
Every year, we ask Phil Factor to celebrate the holiday season with an article on SQL Server Programming that is fun. This year, he responded with 'The Parodist'. This is a SQL Server application, the like of which I doubt if you've seen before.
… Read more
Bad things tend to happen to developments where the scripts for the database layer are left out of source control. Now that we have the means to do it properly, there are many reasons to make the database an equal partner in the development process. Troy discusses some of those reasons.
… Read more
The use of constraints is a valuable way of improving query performance as well as maintaining the integrity of the data, but this is, inevitably, a trade-off: The data uses up more storage, and the modifications are slower and more difficult. In SQL Programming, there are few 'best-practices' that are universally appropriate.
… Read more
SQL Storage Compress does just that, and encrypts as well, allowing the files associated with a live database to take much less file-space, and securing them from unauthorised access. Why would this be useful, and is it really a free lunch? Brad puts it to the test to find out.
… Read more
As a dedicated 'twitcher', Michael's eye is caught by a 'tweet' from Red Jungle. In this case, it was not an exotic species, but a user of SQL Source Control who was pleased with the way that it had saved them considerable time in the development process. Out of curiosity, Michael contacted them to find out more.
… Read more
Alex Kuznetsov is credited with a clever technique for creating a history table for SQL that is designed to store contiguous time periods and check that these time periods really are contiguous, using nothing but constraints. This is now increasingly useful with the DATE data type in SQL Server. The modification of data in this type of table isn't always entirely intuitive so Alex is on hand to give a brief explanation of how to do it.… Read more
Of the big four DML statements in SQL Server, the DELETE is the one least written about. This is odd considering the extra power conferred on the statement by the addition of the WITH common_table_expression; and the OUTPUT clause that essentially allows you to move data from one table to another in one statement.
… Read more
When Nigel Rivett wrote his article about Executing SSIS packages for Simple-Talk, four years ago, it proved very popular and has since been read by nearly 150,000 visitors. Now, Patrick Index, like Boswell to Nigel Rivett's 'Dr Johnson', recounts another technique for deploying and executing SSIS packages.
… Read more
While we all know that the Query Optimizer uses Indexes to produce better execution plans, we don't all know exactly which indexes will give the best results. Benjamin Nevarez has done the research, and in this sample from his upcoming book, "Inside the SQL Server Query Optimizer", he helps us to understand how the Query Optimizer selects indexes to improve execution plans.… Read more
It is always better, and more efficient, to maintain referential integrity by using constraints rather than triggers. Sometimes it is not at all obvious how to do this, and the history table, and other temporal data tables, presented problems for checking data that were difficult to solve with constraints. Suddenly, Alex Kuznetsov came up with a good solution, and so now history tables can benefit from more effective integrity checking. Joe explains...
… Read more
Did you ever wonder how and why your indexes affect the performance of joins? Once you've read Fabiano's unforgettable explanation, you'll learn to love the MERGE operator, and plan your indexes so as to allow the Query Optimiser to use it.
… Read more
It has always seemed strange to Phil that SQL Server has such complete support for XML, yet is completely devoid of any support for JSON. In the end, he was forced, by a website project, into doing something about it. The result is this article, an iconoclastic romp around the representation of hierarchical structures, and some code to get you started.… Read more
TRY...CATCH error handling in SQL Server has certain limitations and inconsistencies that will trap the unwary developer, used to the more feature-rich error handling of client-side languages such as C# and Java. In this article, abstracted from his excellent new book, Defensive Database Programming with SQL Server, Alex Kuznetsov offers a simple, robust approach to checking and handling errors in SQL Server, with client-side error handling used to enforce what is done on the server.… Read more
With SQL Server 2005, SQL Server introduced some of SQL's window functions, that apply, not to the full set, but a partitioned 'window'. Although the ROW_NUMBER, RANK, NTILE and DENSE_RANK bring great power to TSQL, the full versatility will not be available until SQL Server delivers the full implementation. As usual, Robert Sheldon explains all.
… Read more
The use of the comma-separated list of parameters to a SQL routine, that Phil Factor calls the 'comedy-limited list, is a device that makes seasoned SQL Database developers wince. The best choice of design for passing variable numbers of parameters or tuples to SQL Routines varies according to the importance to you of SQL Standards. Joe Celko discusses the pros and cons of the front-runners
… Read more
SQL Server Statistics assist the query optimiser to calculate the best way of running the query. Holger describes every common way that things can go wrong with statistics, and how to put matters right.
… Read more
In a perfect world, we should just be able to write queries and leave the RDBMS to decide the perfect strategy to get the data. Until then we have to understand the Query Optimizer and the way it uses distribution statistics. … Read more
Corrin Lakeland's busy role as a Data Manager includes supervising the work of all database development. Although he was already a keen user of SQL Compare, the arrival of SQL Source Control came as a godsend to him. He explains why.… Read more
