SQL Server Archives - Falafel Software Blog

SQL Server in the Google Cloud

By | Cloud Platform, Google, SQL Server | One Comment

If you are planning to or even considering jumping in to Google Cloud Platform and you work with .NET technologies, you’ll almost certainly want to know how to run SQL Server in the Google Cloud. Google Cloud has made a big effort lately to more fully support the .NET stack, including SQL Server. This is good news for everyone, because more options means more chances to find the right fit for your development project!

Read More

Database Change Management With SQL and BAT Files

By | SQL Server | No Comments

Intro In my previous post I gave an overview of some different approaches to database change management that I’ve had personal experience with, finishing with a teaser about a new strategy that I cooked up for a scenario where I wanted something easy to manage without needing to install anything on the target machine. Using a combination of SQL and BAT files, this strategy is pretty bare-bones, but offers complete control over how scripts are applied, so it has a certain minimalist appeal. It also does not rely on a schema version table, but instead just queries metadata to decide…

Read More

Database Change Management Strategies Compared

By | SQL Server, Visual Studio | 2 Comments

Intro It is a challenging problem to manage database change in any project that uses a database. In this post, I’ll recap some of the approaches I’ve tried in the past and what I like (or don’t like) about them; then in the next post I’ll describe a new one that I recently composed with one BAT file and multiple SQL files and have been quite pleased with. This post assumes that the target database is SQL Server. Some of the techniques described could be modified to apply to other databases, and some are vendor-specific. Comparison of Database Change Management Strategies Monolithic Upgrade Script The…

Read More

Valid Polygons and Ring Orientation with SQL Spatial

By | .NET, Entity Framework, SQL Server | No Comments

SQL Server spatial types make it easier to work with geographical data, but it is important to understand ring orientation or “handedness” when working with polygon data. On a recent project, I was building a web-based tool for users to draw a polygon to define an area that would be used for finding points within its bounds. Each click captures the coordinates which is combined into a WKT (Well-Known Text) Polygon. Everything was working great until I drew a polygon that returned this error when I tried saving the DbGeography object: 24205: The specified input does not represent a valid geography instance because it…

Read More

Head scratcher of the day, Ad Hoc SQL and reentrancy?

By | Azure, Data & Storage, News, SQL Server | No Comments

Reentrant and thread-safe are often used interchangeably. Both speak to a body of code’s ability to properly handle multiple threads. While thread safety and reentrancy are fundamental to the design of a database engine, we rarely need concern ourselves with either when writing SQL. It’s one of those little cases of “rarely” I’d like to discuss here. Reentrancy in a nutshell  I’ll briefly touch on the following two concepts that underly my case of rarely. Thread safe refers to code which can properly multiple threads without failing or intermingling threads-specific data while safely interacting with data. Thread safety is accomplished using a number of…

Read More

Azure Mobile Apps: Writing a Fast Custom DomainManager, Part 3

By | Azure, C#, JavaScript, SQL Server | 4 Comments

This is post 8 of 11 in the series “Azure for Developers” Picking Up From Last Time Last time, I finished the overview of the helpers afforded by the MappedEntityDomainManager and how they could be used to easily implement a custom DomainManager that maps an ITableData DTO class to a database table with an integral primary key, and I profiled the SQL that was generated by performing an ID lookup as well as the SQL that was generated by querying the OData endpoint of a TableController that uses this custom DomainManager. I explained why the SQL generated by this custom…

Read More

Azure Mobile Apps, Tables with Integer Keys, and You

By | Azure, C#, SQL Server | 5 Comments

This is post 5 of 11 in the series “Azure for Developers” Microsoft Azure Mobile Apps  (MAMA) is one of the four prongs of the Microsoft Azure App Service platform, bringing Mobile, Web, Logic, and API apps all together in a single container. While it doesn’t offer anything that you couldn’t create on your own, it does offer value by providing boilerplate plumbing that simplifies common tasks such as database CRUD, offline data sync, and universal push notifications. In this post, I’m going to discuss some of the behavioral details of database CRUD out of the box. If you create a brand-new Azure Mobile App today,…

Read More

The Joy of SQL Server Database Projects

By | .NET, SQL Server, Testing, Tools | 5 Comments

I’ve had the pleasure of using SQL Server Database Projects that come with SQL Server Data Tools in my latest project and I’ve really come to like them. Let me take you on a little tour to tell you why. Reason #1: They solve the database version control problem Putting your database into version control has traditionally been a bit of a challenge. In past projects, I’ve taken several approaches, from scripting every change and having a massive rolling upgrade script that just keeps growing with every change, to using third party tools like Red Gate SQL Source Control. The former…

Read More

Inspecting your Entity Framework SQL

By | .NET | 5 Comments

If you use Entity Framework, you know it greatly simplifies database access for the developer. But it can also introduce its own problems particularly when you are writing more complex LINQ to Entities queries which may generate poorly-performing SQL or unexpected results. This is no reason to avoid using Entity Framework, but it helps to know how to inspect and troubleshoot the SQL your code is executing. The following are a few ways I like to inspect and troubleshoot my generated SQL when developing new code or troubleshooting data-related issues. 1. LINQPad If you haven’t tried LINQPad, you should go download…

Read More

Entity Framework and Enum Flags

By | .NET, C# | 2 Comments

If you have ever had the need to store one or more possible values in a single field, you may have used the [Flags] attribute on an enum in C# which would look something like this:

The numeric values that we assign are important, because it allows us to do bit-wise operations on the number.  The values themselves can easily be calculated by raising 2 to the power of a zero-based sequence of numbers.  So this: 2⁰, 2¹, 2², 2³, 2⁴, 2⁵, 2⁶ yields this (yes, 2 to the zero power is one): 1, 2, 4, 8, 16, 32,…

Read More

Liberate your files from SQL varbinary

By | Microsoft, Tools | 8 Comments

While there are other ways of storing data files for an application, using the SQL Server varbinary data type is a straightforward way to persist your files as BLOBs (binary large objects) along with the rest of your application data.  Reading and writing that data from your application is straightforward since you typically store the data as a byte array. But here is a challenge you may run across: what if you want an easy way to export that varbinary data as the original file?  Sure, you could write a simple utility application to read the content and save it as…

Read More

How To Write a SARG-able Query on a Nullable Column

By | SQL Server | 3 Comments

A question came up the other day: what is the best way to write a query that searches a nullable column but treats null = null, without setting ANSI_NULLS OFF? To answer this question, I set up a test table full of plenty of data to test query plans against. here is the SQL I used to generate the test table:

The result is 10,000 rows of integer values between 0 and 2 million, with about 5% nulls on average. There are two main ways that I know of to write a query that searches for a value in…

Read More