Tag Archives: Data

Make It Easier for the DBA: Give SQL Connections the Application’s Name!

• , , , , ,

Imagine you’re a database administrator tracing query activity on a server. You’d want all the clues you can get to help you figure out what’s going on. One clue is the name of the client program associated with each database session. This name, passed from client to Microsoft SQL Server when a connection is established, can be used by the DBA to differentiate between multiple applications running on a single client and to focus on (or filter out) activity from the same application across multiple clients.

Does your application pass a meaningful program name to the database server?

Continue reading →

T-SQL Ordering Myth #2: ORDER BY in Views

• , ,

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 →

SQL Refactoring – Comparing Result Sets with EXCEPT

• ,

I often refactor SQL, cleaning up convoluted statements using language features such as views, common table expressions and nested joins. It’s one thing to rework a gnarly query into something legible; it’s another thing to verify that the rewritten query returns the same data as the original.

The starting point for verification is comparing the result sets returned by the old and new queries. I’ve recently found Microsoft T-SQL’s EXCEPT operator quite handy for this purpose. Continue reading →

SET FMTONLY ON Changes SQL Control-of-Flow Statement Processing

•

The other day, my friend Jonathan (of Camenisch Creative) surprised me by pointing out that SET FMTONLY ON changes the way Microsoft SQL Server processes control-of-flow statements (IF…ELSE…END, GOTO, RETURN, etc.)

When SET FMTONLY is OFF (the default), SQL Server processes control-of-flow statements normally. In the case of an IF statement, SQL Server determines which conditional expression evaluates to true and then executes the code associated with that expression. Turn FMTONLY to ON and SQL Server executes every conditional branch, even those associated with conditions evaluating to false!

Continue reading →

SSRS: Dataset Field List Is Empty Even After Refresh Fields

• ,

Problem: Microsoft SQL Server Reporting Services won’t populate the data set’s field list. Clicking Refresh Fields does nothing—the field list remains empty.

Is something wrong with the query? Is there a database permissions issue? Let’s check. Click the Query Designer button, then the red “!” (execute) icon. The query runs and…a normal result set is returned. Since the query executes correctly, the query and permissions must be fine. So, the blank field list indicates an SSRS bug—correct? Not necessarily. Continue reading →

SET_ANSI_NULLS Does Not Affect Column-to-Column Comparisons

•

I learned this fact the hard way the other day! A MERGE statement would not update null values in the destination table with the appropriate non-null data from the source table. My AND clause looked something like “Source.Name != Destination.Name”. I thought that setting ANSI_NULLS to OFF would cause this clause to catch differences when one of the values was null.

Nope! Continue reading →