Despite the neglect of the basic ODBC drivers over the years, they still afford a neat way of reading from, and writing to, CSV files; and to be able to do so in SQL as if they were tables is somewhat magical. Just to prove it is possible, Phil creates a CSV version of AdventureWorks as a linked server.… Read more
The 'bin packing' problem isn't just a fascination for computer scientists, but comes up in a whole range of real-world applications. It isn't that easy to come up with a practical, set oriented solution in SQL that gives a near-optimal result. … Read more
SQL Server's functions are a valuable addition to TSQL when used wisely. Jeremiah provides a complete and comprehensive guide to scalar functions and table-valued functions, and shows how and where they are best used.… Read more
As part of our long-running Cribsheet series, we asked William to come up with a brief summary of what was involved in bringing database development work under source control. What are the advantages it brings, and are there disadvantages?… Read more
Voting systems can become very complex, and some of them are easy to manipulate by tactical voting. Joe takes a couple of voting systems and wonders how you would implement them in SQL. He's even more curious as to how you, the reader, would do so. … Read more
Unlike the hierarchical database model, which created a tree structure in which to store data, the network model formed a generalized 'graph' structure that describes the relationships between the nodes. Nowadays, the relational model is used to solve the problems for which the network model was created, but the old 'network' solutions are still being implemented by programmers, even when they are less effective.… Read more
The sequential nature of early data storage devices such as punched card and magnetic tape once forced programmers to devise algorithms that made the best of sequential access. These ways of doing data-processing have become so entrenched that they are still used in modern relational database systems. There is now a better way, as Joe explains.… Read more
Phil Factor records, as closely as possible, the twists and turns of creating a SQL Server T-SQL stored procedure, describing the methods that work for him.… Read more
Are Common Language Runtime routines in SQL Server faster or slower than the equivalent TSQL code? How would you go about testing the relative performance objectively? Solomon Rutzky creates a test framework to try to answer the question and comes up with some surprising results that you can check for yourselves, and offers some good advice.… Read more
The basic texts for developing SQL code tend to leave unsaid the basic techniques for building routines such as stored procedures in T-SQL. Phil is well-known for his more lengthy and complex stored procedures, so we asked him to explain in more detail how he goes about developing things like that without the comfort of Visual Studio.… Read more
Identifiers aren't locators, and they aren't pointers or links either. They are a logical concept in a relational database, and, unlike the more traditional methods of accessing data, don't derive from the way that data gets stored. Identifiers uniquely identify members of the set, and it should be possible to validate and verify them. Celko somehow involves watches and taxi cabs to illustrate the point.
… Read more
If you are still using the old Sybase DateTime datatype, it is a good idea to move your code to the more standard datatypes that were introduced in SQL Server 2008. Joe Celko explains why, and walks through some of the history of the TSQL way of storing and manipulating dates and times.
… Read more
The PATINDEX function of SQL Server packs powerful magic, but it is easy to get it wrong. Phil Factor returns to the Workbench format to give a tutorial of examples, samples and cookbook ideas to demonstrate the ways that this underrated function can be of practical use. It is intended to be pasted into SSMS and used as a basis for experiment… Read more
Why don't we emphasize the huge advantages of parameterized queries over ad-hoc queries in SQL Server? There is a severe impact on resources and performance from repeatedly using similar ad-hoc queries, instead of reusing the existing query plans. David Berry shows how you can measure this impact, and springs a surprise or two in the process
… Read more
SQLs CASE expressions can be powerful magic, but can trap the unwary who are used to the more familiar CASE statements of procedural languages. … Read more
Lookup tables can be a force for good in a relational database. Whereas the 'One True Lookup Table' remains a classic of bad database design, an auxiliary table that holds static data, and is used to lookup values, still has powerful magic. Joe Celko explains....
… Read more
Although the methodology of refactoring code has been adopted enthusiastically, the same has not really been the case with databases. Nick argues that the reason could lie in the extent of the task of unpicking complex databases systems sufficiently to make them more efficient and effective; and this will only be ameliorated with better tools and planning to support the techniques.
… Read more
Most of the time, you do not have to worry about implicit conversion in SQL expressions, or when assigning a value to a column. Just occasionally, though, you'll find that data gets truncated, queries run slowly, or comparisons just seem plain wrong. Robert explains why you sometimes need to be very careful if you mix data types when manipulating values.
… Read more
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
