SQL Varchar vs Nvarchar

Nvarchar stores UNICODE data. If you have requirements to store UNICODE or multilingual data, nvarchar is the choice. Varchar stores ASCII data and should be your data type of choice for normal use. Regarding memory usage, nvarchar uses 2 bytes per character, whereas varchar uses 1. VARCHAR is stored as regular 8-bit data. But NVARCHAR strings are stored in the database as UTF-16 — 16 bits... » read more

Index and Column Limits

SQL Server gives us a limit of 900 bytes for clustering keys, and 1700 bytes for non-clustered indexes.  Have to use full-text index for VARCHAR(MAX) or nVARCHAR(MAX) datatypes.

Database Logical Read

The I/O from an instance of SQL Server is divided into logical and physical I/O. A logical read occurs every time the database engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read is then performed to read the page into the buffer cache. If... » read more


FillFactor is how much free space Microsoft leaves available in the index pages so that if an insert or an update happens, mainly an update, where the data grows, you don’t want those page splits to be within a row. So, FILLFACTOR can reduce your fragmentation. Typically on systems that see a lot of updates... » read more

When you want to use a non-clustered index

Nonclustered indexes are secondary indexes used to help the performance of queries not served by the clustered index.  You’re typically going to want to add a nonclustered index to the column that’s in your WHERE clause and your most commonly run queries. You want to use these involved on columns that are in joins and... » read more

When you might not want to use a clustered index

In some scenarios, it might be beneficial to not have a clustered index on a table. E.T.L. staging tables is one scenario that you might not want to have a clustered index. We’re loading into a data warehouse a few times a day, and that could be hundreds of thousands of records at a time,... » read more

Database Heaps

A heap is a table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap. Data is stored in the heap without specifying an order. Usually data is initially stored in the order in which is the rows are inserted into the table, but the Database Engine... » read more

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


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