When you write a query to return the first few rows from a potential result set, you’ll often use the TOP clause.
To give a precise meaning to the TOP operation, it will normally be accompanied by an ORDER BY clause. Together, the TOP…ORDER BY construction can be used to precisely identify which top ‘n’ rows should be returned.
You might recall from Inside the Optimizer: Row Goals In Depth that query plans containing a row goal tend to favour nested loops or sort-free merge join over hashing.
This is because a hash join has to fully process its build input (to populate its hash table) before it can start probing for matches on its other input. Hash join therefore has a high start-up cost, balanced by a lower per-row cost once probing begins.
In this post, we will take a look at how row goals affect grouping operations.
One of the core assumptions made by the SQL Server query optimizer cost model is that clients will eventually consume all the rows produced by a query.
This results in plans that favour the overall execution cost, though it may take longer to begin producing rows.
Is it possible to view the contents of a local temporary table, created on a different connection?
Say we run this code on connection 1:
CREATE TABLE #Private
(
[data] nvarchar(30) NOT NULL
);
GO
INSERT #Private
(
[data]
)
VALUES
(
N'Only I can see this'
);
Is there a way to see the contents of the #Private table from connection 2?
It isn’t particularly easy, but a user with sysadmin permissions, a bit of internal knowledge (and some patience) can do it.
From time to time, I encounter a system design that always issues an UPDATE against the database after a user has finished working with a record — without checking to see if any of the data was in fact altered.
The prevailing wisdom seems to be “the database will sort it out”. This raises an interesting question: How smart is SQL Server in these circumstances?
In this post, I’ll look at a generalisation of this problem: What is the impact of updating a column to the value it already contains?
The specific questions I want to answer are:
UPDATE generate any log activity?SQL Server uses an extensible architecture for query optimization and execution, using iterators as the basic building blocks.
Iterators are probably most familiar in their graphical showplan representation, where each icon represents a single iterator. They also show up in XML query plan output as RelOp nodes:
Each iterator performs a single simple function, such as applying a filtering condition, or performing an aggregation. It can represent a logical operation, a physical operation, or (most often) both.
Investigating an optimizer transformation that exposes a bug in SQL Server’s MERGE implementation.