Installing SQL Server Best Practices

Highly recommend that you store all user databases, user database logs, and backups on a drive separate from the system drive. So in other words, if your SQL Server is installed on the C drive of a computer, you should be putting all of your data files on a separate drive, and the reason being... » read more

Implicit transactions: Don’t use it.

By default the database engine uses what’s known as an auto commit. Every T-SQL statement is committed or rolled back when it completes.  The database engine will always use this auto commit functionality unless a transaction is explicitly specified with BEGIN TRAN. Implicit transaction are rarely used in SQL server and when the option is... » read more

Read Committed Snapshot Isolation (RCSI)

Similar to Read Committed Isolation level but you can still read the old version of the data. Before a row is locked in preparation for changes, that means an update statement that might be run to a given row, that row is then placed in a version store (TempDB). The big advantage that our SCI... » read more

Database Isolation Level

Low isolation levels do allow for more users to be able to access the data so you can get greater concurrency, but they can also affect data integrity by creating the effect of lost updates and dirty reads.Dirty reads are a phenomenon where you read uncommitted data. That data could be wrong, because its transaction... » read more

CHAR, NCHAR, VARCHAR and NVARCHAR Data Types

Table of Differences   char nchar varchar nvarchar Character Data Type ASCII Unicode ASCII Unicode Maximum Length up to 8,000 characters up to 4,000 characters up to 8,000 characters up to 4,000 characters Character Size takes up 1 byte per character takes up 2 bytes per Unicode/Non-Unicode character takes up 1 byte per character takes... » read more

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 option

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