Temp Tables vs Table Variables

There are two types of temporary tables that you’ll use: local and global, and these have a specific designation. Local tables are designated with one pound sign and global temporary tables are designated with two pound signs. If you create a table that doesn’t have a pound sign, or two pound signs, in front of... » read more

SQL Server Pages

SQL Server stores data in 8K pages. The narrower your rows are, so, for example, if you had a table that was just simply an integer in a varchar, the more rows will fit on a single page. SQL Server reads those pages off of disk and into memory in order to service your queries... » read more

GUID

GUIDs or unique identifiers have the benefit of being globally unique across machines and databases. This gives us a tremendous benefit in that we know that we’re always going to have a unique value.  It’s composed partially of a seed that starts with the MAC address of the computer it’s on, and then the time... » read more

Things to Avoid with SQL Server Database

Avoid storing files in the database Makes database size very hard to manage. Backups and restore. Instead store files else where and store pointers to files in the database. Avoid storing business logic in the database Hard to maintain. Example: don’t use database to send emails or move files. Move business logic to application layer.... » read more

Statistics IO and Time

SET STATISTICS IO, TIME ON Statistics IO is going to give us the number of IO operations that SQL Server’s going to do to service our queries, and it’s going to break that down by logical reads from memory and physical reads from disk.  The time statistic is just going to give us the actual... » read more

Reading Execution Plans

Estimate Plan vs Actual Plan Depends on the statistics your database has about your data. If you see a large differential between the estimated number of rows and the actual number of rows, it can be an indication that SQL Server does not have the proper statistics on the underlying tables and indexes that are... » read more

Query Execution Plan

What happens when you issue a query to SQL Server. #1 SQL Server is going to parse the syntax of your query.  SQL Server is going to check to see if your T-SQL that you’re submitting is valid and that all of the objects exist. This is a basic check just to make sure all... » read more

Singleton Pattern

The singleton pattern is a software design pattern that restricts the instantiation of a class to one “single” instance. This is useful when exactly one object is needed to coordinate actions across the system. In C#, you use the “static” declaration. The singleton design pattern solves problems like:[5] How can it be ensured that a... » read more

MVC Design Pattern

An architectural pattern commonly used for developing user interfaces that divides an application into three interconnected parts. This is done to separate internal representations of information from the ways information is presented to and accepted from the user. The MVC design pattern decouples these major components allowing for efficient code reuse and parallel development. A variation is the... » read more

Distributed Design

A distributed system is an application that executes a collection of protocols to coordinate the actions of multiple processes on a network, such that all components cooperate together to perform a single or small set of related tasks. Why build a distributed system? There are lots of advantages including the ability to connect remote users... » read more