Last Thursday I published an editorial demonstrating one use of the ROW_NUMBER function as a technique to get the last value for each group contained within a set of groups, A Little ROW_NUMBER Magic. See the example below in figure 1 as executed against my instance of AdventureWorks2012. Figure 1 – ROW_NUMBER Grouping Query WITH MyPay AS ( SELECT BusinessentityId […]
Author: Ben Taylor
Run Two Queries in Parallel
Before I leave the topic of Parallel Queries, and Connections using Multiple Result Sets, I want to share a couple things about the syntax that make this worthwhile. Just because something is written to use asynchronous methods doesn’t mean it runs concurrently. That is a big point. If you truly have two queries that are completely non-dependent, and you wish […]
A Little ROW_NUMBER Magic
I really like the ROW_NUMBER() function in SQL Server as a means of getting the first or last instance of a record for each specific group within record set containing more than one group. Ok, let me break that down a little bit. It’s pretty easy to get the last record of the line items for a single purchase order. […]
The Power of Coalesce
Cascading Style Sheets (CSS) are an HTML technique for formatting the display of html documents in a standard fashion. They are called Cascading because there is a hierarchy to how the styles are defined and implemented. Styles implemented in the HTML document itself override other styles. The concept I want to communicate here is that the same HTML tag may […]
Concurrent Queries – Going to MARS
Multiple Active Result Sets (MARS) were introduced with SQL Server 2005. When you connect to an SQL Server database you are able to request and receive a TDS (Tabular Data Stream) from the database. Using MARS you are able to receive more than one TDS stream concurrently over the same connection. MARS controls the routing of each stream to the […]
Where Should We Handle Null Values?
Where do you deal with null? The reality is that if you work with a relational database, you will most likely have to address the value, or non-value, of null. Some believe you should do everything you can to remove null from a database. For example, if an individual is working in the USA they must report their income to […]
DataTable Extension To Resolce Column Ordinal Positions By Name
Today I am expanding on the demonstration of Extension methods from yesterday, with the goal to increase performance of processing data from a DataTable while maintaining flexibility. Yesterday we created new methods to read columns from a DataTableReader, and handle the value if it was null by providing a default value. This modification simplified code readability and provided less opportunity […]
DataTableReader Extension Methods
Today I would like to demonstrate something you can do with the DataTableReader to make it easier and more efficient to use. The first thing I want to demonstrate is the creation of an Extension Method that allows you to emulate the SQL ISNULL function. The SQL ISNULL function tests a value for null, and if it is null, returns […]
Multiple Results From Queries
When using ADO.Net it is possible to have a query return more than one result set, translated into an DataSet , or by returning more than one DataTable sequentialy. If you use a DataSet to get each of the individual query results you can simply use the [DataSetInstance].Tables collection to interact with the results of each query. If you prefer, […]
What’s a Cartesian Product?
One of my favorite interview questions is to as the candidate, “what is a Cartesian Join?” Sometimes the candidate won’t know what a Cartesian is, but they are familiar with the SQL Server syntax of a Cross Join. The answer is that a Cartesian join returns all the records in one table joined to all the records in a second […]