Reading up on some of the new features found in SQL Server 2016 I came across a couple tips for optimizing earlier version. These performance options are implemented by setting trace flags at the startup of your SQL Server instance. They refer to T1117 for TempDb and T1118 for user databases. T1118 is a trace flag that, when turned on, […]

JSON, an acronym for JavaScript Object Notation, has often been compared to XML as a leaner form for serializing objects into data, where the contents may be understood by humans without de-serialization. It is very popular for passing objects over HTTP, as found in REST services, and is used elsewhere as a data transfer technique. However, JSON, unlike XML, also […]

For decades Microsoft SQL Server supports one of the coolest comparison operators available; SOUNDEX. SOUNDEX is used to convert strings into a representative code, allowing words with different spellings to be considered the same. This is very useful in imprecise languages, such as English, having multiple ways to spell the same thing. The way the SOUNDEX function works is that […]

Today I was discussing with a colleague how we might implement a search against a database containing a table of phrases. We want to allow a user to specify a list of words that may be found in a list of phrases. One search method would determine those records where one or more of the search words were found. Another […]

There are two popular Microsoft Technologies used for database migrations that are often considered mutually exclusive. You can use Entity Framework by designing your application code first, and have it create database migrations for your database. You can also use a Database project, and have it generate your database migrations through a database comparison, or even code comparison. These two […]

Do you ever try to optimize the performance of your Entity Framework queries? If so, there are a couple of things you can do to gain performance without a lot of gymnastics. Having Database Statistics on columns from which you filter result sets can be a big boost, if you are connected to an SQL Server database. Having those statistics […]

We’ve considered a number of ways to pass sets of data to an SQL query from a client application. Often those sets are not complex. They may simply be a set of filter options that may be expressed as a series of OR operations. For example, Where LastName = ‘Taylor’ OR LastName = ‘Smith’ This is easy SQL to write. […]

I’ve been reading a lot lately about the difference in Entity Framework for finding records based on the primary key. The.Find method has been tuned to locate the record. However, some find better performance using a Linq To SQL query. The difference can be rather dramatic. When using a Linq query a round trip to the database is ALWAYS executed. […]

Today I want to share some tips for writing and maintaining database change or migration scripts. Never assume the state of the target database. If your queries are written in such a way that they can be executed over and over, you will have accomplished this goal. This is helpful so that you don’t have to worry about accidentally running […]