Can you rewrite
WHERE Col1 = Col2 OR (Col1 IS NULL AND Col2 IS NULL) so that each column name is only used once without using the substitution method (that is, without doing something like
WHERE ISNULL(Col1, '-2,147,483,648') = ISNULL(Col2, '-2,147,483,648'))?
WHERE EXISTS(SELECT Col1 INTERSECT SELECT Col2)
What…what?! That can’t be possible! I thought
INTERSECT compares two result sets, not two columns from the same row of data. Continue reading
Manipulating data directly in a table isn’t always practical. On occasion, performance requirements may dictate that the revised or replacement data set first be assembled in a separate table (a staging table) then switched in to replace the currently live data. Continue reading
Since a view is a saved query and queries can specify ordering, adding ORDER BY a view definition might seem a reasonable proposition. Try it and Microsoft SQL Server chokes. Then you learn the trick: include a TOP clause and SQL Server will be a-ok with ordering clauses in views. You want all rows returned so you add “TOP 100 PERCENT” to your definition and SQL Server is happy!
Yet you notice something strange. Sometimes the rows returned aren’t sorted according to the view’s ORDER BY clause. As you ponder this puzzlement, your mind wonders back to the roundabout syntax required to finagle ORDER BY into the view definition. Is something funny going on? But the answer eludes you. Continue reading
Did you know that IDENTITY columns can count downward?! According to MSDN, IDENTITY’s second argument, named increment, “is the incremental value that is added to the identity value of the previous row that was loaded.” No constraint is given that increment must be positive (remember from Algebra that both positive and negative numbers can be added). Set increment to a negative number and IDENTITY will generate a descending sequence of numbers. Continue reading
SELECT returns all relevant rows from the database.
GROUP BY applies aggregate functions, condensing
SELECTed data into one summary row per grouping. What if mixing the two approaches is desired—what if data rows need to be combined with aggregate totals in the same result set?
OVER clause allows several kinds of functions to be applied to non-
BY rows. Using
OVER, a simple
SELECT returning data rows can be expanded to include columns containing summary statistics. Traditionally, aggregate functions can only be used in conjunction with
GROUP BY or to produce a single row summary result set; when used with
OVER, these limitations are removed. Continue reading
Just recently, I realized that T-SQL allows variables to be declared and set with a single statement:
DECLARE @UserID int = 1592;
Up until this discovery, I only knew about the more traditional, two-step declaration and assignment syntax:
DECLARE @UserID int;
SET @UserID = 1592;
In its plain vanilla form,
GROUP BY applies aggregate functions to groups of data, returning one result row per data group. Did you know that it can do more than this? Let’s explore the possibilities! Continue reading
Internet search results are typically broken into batches. The initial page displays results 1 through perhaps 10, page two shows matches 11–20 and so on. Splitting results up like this is known as (no surprise) paging.
To implement results paging with Microsoft SQL Server (version 2012 or later), try modifying your
ORDER BY clause with
OFFSET tells SQL Server to start returning x rows into a result set.
FETCH defines the number of rows to return. Continue reading
Alphabetical sorting is simple: A comes after B, next comes C, then D and so on all the way to Z…right?!
If only it were so easy! Consider the character Á—should it be sorted before A, after A or just like it was A (ignoring the accent)? The answer depends on language: Hungarian’s Á comes after A; in Spanish, the accent should be ignored. In many languages, Y comes between X and Z; however, Lithuanian orders Y before J. French has an interesting twist: sometimes strings are sorted by first comparing their letters from left to right then by comparing diacritics from right to left. Some languages even treat certain multi-character combinations as single letters, like CD in Welsh. If this doesn’t sound complex enough, alphabetization rules can change: for example, in 1994, the Congress of the Association of Spanish Language Academies decreed that Spanish’s CH would no longer be considered a single, muilt-character letter combination! Continue reading