I had for my study goal to review the JSON implementation in SQL Server 2016. The implementation is simple, only interacting with data, or properties, of JSON objects. Unlike XML, JSON has the ability to contain functions as well as data, because JSON is simply serialization of JavaScript objects. In JavaScript, even functions are objects. I am both pleased and […]

We’ve been talking about data retention. AZ Jim reminds us in his comments, that the best way to manage data, primarily the deleting of data, is to establish a policy and implementation prior to turning the system on. He also suggests that, for those situations where data may not be deleted, that we have different levels of storage, where data […]

Many systems don’t like to physically delete data. I find this practice has one or more of the following reasons: If I mark a record as deleted with a status flag, I can bring it back easily Referential integrity requires me to delete data in relationship hierarchy order, or implement cascading deletes There may be a need to keep the […]

SQL Server has three distinct join methods: Hash, Loop, and Merge. It has three methods so that joining tables is optimized for various conditions, based on data distribution, indexes, volume of data in tables being joined, and the availability the information about those tables. You can work successfully with SQL Server for years without having to know any of this. […]

The thought of using indexes in Temp Tables is counter intuitive. The whole point of temp tables is to be temporary, short lived, intermediate steps in query execution, with the purpose to optimize aspects of a query. If a temp table has a short life, why would you take the time to create an index on it’s contents? The answer […]

Peter Heller posts a question in response to something I was writing about how non-traditional data storage can increase performance over traditional techniques used today, such as indexing. He writes, in response to my statement that new techniques do not use indexes at all, “will this be a function of the database or not? This functionality will be independent of […]

One of the reasons I wrote about Always On Availability Groups yesterday was because I was looking for a good disaster recovery option for the MSDB database. The MSDB database retains a lot of information used by the SQL Server infrastructure systems. For example, the SQL Agent is a popular service that works with the SQL Server Engine. The Agent […]

Always On Availability Groups (AOAG), add in SQL Server 2012, provide you with more than just failover. I look at AOAG as a combination of the best from database mirroring and clustering. Mirroring and clustering are two distinctly different failover implementations. Mirroring allows you to have two distinct copies of a database, and all database activity is sent to both […]

What happens when you add an index toi a table? Think about it for a moment. Every time you modify a record, (insert, update or delet), then every index in that table will also have to be modified. Perhaps I am hanging onto this topic a little too long, but, this is really important when it comes to tuning the […]