Tag Archives: SQL Server

Equals Is Not Always Equivalent: When Query Folding Does Not Produce Identical Results

, , , ,

Query folding is supposed to be transparent, as far as results go. Whether or not a Power Query expression is folded should have no effect on the data returned. You should receive back identical results either way. At least, that’s the theory.

Unfortunately, this is not always the case!

The fact that query folding sometimes changes the results that are returned can bite unexpectantly. You have an M expression that produces exactly what you want. Then you make what should be an innocuous edit, but behind the scenes the change affects whether or how the query is folded. The results you now receive back are no longer what you expect, and puzzlingly the divergence seems to have no obvious relation to your edit. Or, maybe you didn’t edit anything at all: instead, a Power Query update changed the foldability of your query without you touching it. You made no changes, yet the data returned is now different.

Continue reading

SQL for C# Developers

, ,

You’re a developer. You know how to write code that interacts with databases. Using tools like Entity Framework and LINQ, you can push and pull data to and from data sources. But how comfortable are you directly talking to a relational database in its native dialect—SQL? Maybe you can cobble together a basic SELECT statement—but when you do, do you feel like a traveler in a strange land where you barely know the language?


Continue reading

Why You Can’t Directly Bulk Load Values
or See Them in Traces

, ,

Bulk loading seems shrouded in mystery. You can’t directly bulk insert values using a query. However, you can bulk load using a tool like BCP, an API like SqlBulkCopy or via a query that tells SQL Server to read the rows out of a file and insert them (BULK INSERT or OPENROWSET(BULK…)). Then, if you use one of these means to do a bulk load and watch server activity using a Profiler trace or extended events, you’ll see an insert query but no row data. What’s going on?!

Continue reading

T-SQL on the Wire: What takes place between client and server?

, , ,

The answer to this question might shed light on some Microsoft® SQL Server® mysteries, might answer—or raise—security concerns and might help you administer, develop and debug better.

In this presentation, learn about Tabular Data Stream (TDS)—the protocol used for client-to-SQL Server interactions. In addition to a  high-level understanding of how TDS works, you’ll (hopefully!) come away with practical applications of this knowledge that should benefit you as a database administrator or developer.

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

For a Future Version: Re-Engineer Microsoft Access to Use a SQL Server Backend

,

At the core, both Microsoft Access and Microsoft SQL Server are relational database management systems (RDBMS), yet each uses a separate file format and code base. These differences add friction to the process of integrating the two technologies. Developers interacting with both back ends need to learn the idiosyncrasies of two systems. For administrators, the fact that each uses a different driver requires maintaining multiple driver configurations on systems that need to communicate with both. Power users are faced with the pain of figuring out how to cross this technology divide when growth dictates switching a home-grown Access application to a SQL Server backend. Continue reading

Object IDs: Unique Instance-Wide or Database-Wide?

, ,

The versatility of T-SQL’s object_id() function, which accepts one-part (object_name), two-part (schema.object_name) and three-part (database.schema.object_name) object names as its first argument, could seem to imply that object identification numbers (object IDs) are global across a Microsoft SQL Server instance. After all, if object_id() accepts a multi-part object name which uniquely identifies the specified object relative to the server instance, wouldn’t the object ID returned by that function also uniquely identify the same object across the server instance? Continue reading