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 […]

Yesterday I brought up the topic of logical database modeling. In the discussion, and often considered in comments on the editorial, was this concept that the current trend is not to do modeling at all. There are still some holdouts such as myself, finding models easy to generate and useful for communication. Unlike the typical ERD, if one is created, […]

Logical schemas were quite popular back in the days of Database Engineers. We studied database storage techniques and data modeling. We learned tools allowing us to efficiently create diagrams of our database schema, the purpose of which was to communicate to the world what our intentions were. Today, database engines are so efficient; some software is developed with no centralized […]

What job title most represents the way your work day goes? 911 Operator: Watch call queue and handle next incoming. Team Member: Lots of meetings and interruptions to keep everybody on the same page. Isolationist: No email, no phone, focused on work. Which method produces the best product for you team as individuals and as a corporate whole? I’d say […]