Yesterday we took a look at how Entity Framework 6.1 communicates with SQL Server when retrieving data from multiple related tables. It builds inline views, adapting the data from multiple, related tables, transforming them to fit into a single, de-normalized view, looking much like a jagged array when the null values are ignored. This results in one round trip to […]

Have you ever run a trace on Entity Framework query execution? It’s really interesting on the SQL Server side, when you see the SQL that Entity Framework designs. I have an application that causes a Stack Overflow if I try and use lazy loading with my Entity Framework classes. This is due to a recursive (hierarchical) design in the data, […]

Let’s say you are using Dot Net to interact with your SQL database. You want to sort the data being returned from your database. But, you want to keep the load in your middle tier, with the intention to reduce the load on your database server. What options are available to you at this point? There are many different ways […]

Today I demonstrate a more efficient method to retrieve data using a stored procedure, while providing different sort options. If you show the query plan from yesterday you will see that it calculates the sort for all options, and then uses the one specified by the user. This query differs in that it creates a separate sub plan for each […]

The best place to sort data from your database is not on your SQL Server. There are times when you have no choice. So, now you have to sort your results from a query. Your DBA says you can’t use dynamic SQL. You are only allowed to use stored procedures. Now What do you do? Today I am demonstrating a […]

I have been a proponent of set operations in SQL Server as being preferred over procedural logic. If possible, work with sets instead of loops, and or cursors. The primary reason is that SQL Engines are tuned to do set operations. It does filtering and joins very quickly. Below, I have a VERY contrived query demonstrating how you can use […]

Tonight I was helping my son with his first term paper. He was using a program from school to layout his thoughts according to the format required by his teacher. I could have done the work in any different number of tools with ease. But he had to use the tool provided, and it was different than anything I had […]

What SQL permissions do an application really need in order to work? The requirements will be based on the functions exposed by an application. If you have an off the shelf application that is self-contained, it may require permissions for database backup and restore actions. Bulk Copy for import and export is often included. If your application allows users to […]

When you use a relational database engine for an application, where shared credentials are used to gain access, it is very important how those shared credentials are configured. We’ll take a little look into why. I have consulted with a few companies that used this approach of a single login for database access. Oftentimes, they used the SA account, or […]