SQL Server

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: Writing a Fast Custom DomainManager, Part 2

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

This is post 7 of 11 in the series “Azure for Developers” Picking Up Where We Left Off Last time, I wrote about how to develop a custom DomainManager to plug into an Azure Mobile Apps TableController. The goal is to implement a DomainManager that works seamlessly with the Azure Mobile App client SDKs, enables CRUD against database tables with non-string keys, and generates good SQL that can be supported with ordinary general-purpose indexes. I covered which class to inherit from, the methods you’ll need to implement, and some of the helpful utilities that the parent class affords you. In this…

Read More

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

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

This is post 6 of 11 in the series “Azure for Developers” My last post took you on a journey with me as I analyzed the Azure Mobile App default base entity class and talked about some of the things I saw there. To summarize, I was most disturbed to note that the default choice of primary key was a Unicode string, which as a database and query guy immediately raised all sorts of alarms, flags, flashing lights, klaxons, sirens… well, you get the idea. Strings make inefficient keys to begin with, and Unicode keys literally doubly so. I found a…

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

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