Articles tagged

Operator Precedence versus the Confusing Constraint Translation

0
2
The other day, a person I work with was creating some fairly complex CHECK constraints to protect data in a new table that was being created (a practice I am 100% for: https://www.red-gate.com/simple-talk/blogs/eliminating-annoying-space-characters-via-check-constraints/. The multiple constraints were combined into just one, with several conditions condensed into a single Boolean expression, using several ANDs, ORs, and … Read more

Finding overlapping ranges of data

0
2
This week, I had a problem where I needed to find and eliminate from the results of my query, data with overlapping ranges. I have written about this topic before, in my database design book book, in regards to building a trigger to avoid overlapping ranges. But even though I have written on the topic … Read more

T-SQL Window Function Speed Phreakery: The FIFO Stock Inventory Problem

Sometimes, in the quest for raw SQL performance, you are forced to sacrifice legibility and maintainability of your code, unless you then document your code lavishly. Phil Factor's SQL Speed Phreak challenge produced some memorable code, but can SQL features introduced since then help to produce code that performs as well and is also easy to understand? Aunty Kathi investigates.… Read more

Maintaining T-SQL Code Quality Using SQL Code Guard

Sometimes you need some functionality in your database build process that isn't quite available. Visual Studio Database projects don't have great ways of reviewing SQL code for potential errors, but is it possible to adapt a good command-line tool like SQL Code Guard that does the job well? Lukasz shows how to do it by using NuGet packages to integrate such tools with the project and the build process.… Read more

Top 10 Most Common Database Scripts

What are the scripts that a working DBA uses so often that they become part of the 'muscle memory'? Grant Fritchey asked the question on the forums of SQL Server Central. From the large response, Grant was able to pick out the ten most popular T-SQL commands, scripts, or fragments. It seems as if, despite the range of tasks we do, there are some common tools we use.… Read more

How to Confuse the SQL Server Query Optimizer

Gail Shaw examines three common forms of generic SQL that can and will confuse the SQL Server Optimizer to the point that it generates and reuses very inefficient execution plans. Want to 'trick' SQL Server into performing millions of logical reads to return the data, when only are few thousand were really required? Try writing catch-all queries, or using control flow statements to create general-purpose procedures, or switching parameter values within a procedure. Just don't expect the resulting queries and procedures to perform well, or consistently. The ideal solution to the problem of generic T-SQL is not to write it, but failing that Gail demonstrates techniques such as recompiling the query on each execution, using hints, dynamic SQL, or splitting generic procedures into sub-procedures.… Read more

How to Avoid Conditional JOINs in T-SQL

Relational databases go out of their way to execute SQL, however bad the crimes against Codd and relational theory within the query. The 'conditional join', can be executed but at great cost. As always, it is much better to sit back and restate the problem in a set-based way. The results can be rewarding.… Read more

Reusing T-SQL Code

Database programmers are often caught in the paradox of wanting to implement business rules and complex functionality in one place only, but being concerned by the performance hit of having generic functions or procedures that have a wide range of parameters and outputs. Alex Kuznetsov, in an article taken from his book 'Defensive Database Programming with SQL Server', shows how DRY principles can be put in practice with constraints, stored procedures, triggers, UDFs and indexes.… Read more

Using the T-SQL PERCENTILE Analytic Functions in SQL Server 2000, 2005 and 2008

Percentiles give meaning to measurements by telling you the percentage of the population being measured who get higher or lower values. They are now easier to calculate in SQL, and are useful for reporting; but are the new analytic functions faster and more efficient than the older methods? Dwain Camps demonstrates, and investigates their relative performance performance.… Read more

Documenting your SQL Server Database

One of the shocks that a developer can get when starting to program in T-SQL is that there is no simple way of generating documentation for routines, structures and interfaces, in the way that Javadocs or Doxygen provides. To embed the documentation in the source is so obvious and easy that it is a wrench to be without this facility. Phil Factor suggests a solution. … Read more

Questions About T-SQL Control-of-Flow Language You Were Too Shy to Ask

Surely, we all know how T-SQL Control-of-flow language works? In fact it is surprisingly easy to get caught out. What, for example, do the BREAK, RETURN and CONTINUE keywords do, precisely, in their various contexts? the answers to this and other questions aren't all entirely obvious, but we're too shy to ask them in public, and risk displaying our ignorance.… Read more

Why Developers Need to Understand Execution Plans

As a coder, the more you know about the business, the better code you're going to write. The more you know about the language in which you code regularly, the better code you're going to write. Similarly, if you're tasked with writing code against a relational database engine, you're going to need to know more about that engine. If you can examine and understand execution plans, you can achieve better understanding of the database system and you will write better database code. Grant Fritchey shows you how.… Read more

Questions About T-SQL Transaction Isolation Levels You Were Too Shy to Ask

Every time you access a relational database to make a query, you have an important decision to make: What is the appropriate isolation level for your query? If you get this wrong, the consequences can be serious. Deadlocks, Dirty reads, Non-repeatable reads, or poor performance. We're in luck, because Robert Sheldon once more answers those questions that are awkward to ask in public.… Read more

Bitwise Operations in T-SQL

How can you, in T-SQL, get a list of columns that have changed within a trigger? How can you see what bits are set within a varbinary or integer? How would you pass a bitmap parameter to a system stored procedure? Phil Factor shows how, introduces the bitwise operations, illustrates ways of peeking at bit-level operations, and explains the concepts behind the coding of integer datatypes.… Read more