It has been more than two months since I blogged. I have been blogging in my Organization internal blogging site on Management topics for last couple of months (just a side effect of my new project lead position).
In parallel, I have started giving session on Performance tuning and Storage internals for my Organization (Cognizant - India) Academy.

Currently, I am focusing on understanding transaction logs and how to read t-log records. Let me write an article on this shortly.

In between, I just wanted to share my favorite SQL Server myths and realities with you all.

References are given in all suitable places.

Note for Newbies: Some myths listed here are talking about behaviour change of a feature/command in a specific scenario and may
not be the case for all the scenarios. Read experts books to understand internal implementations in detail.

 Myth 1: Table variables are created and manipulated in memory instead of the Tempdb database

Reality: It’s proved that even table variables can consume space in Tempdb.

http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/

Myth 2: Truncate cannot be rolled back because it’s not a fully logged and only page deallocations is recorded.

Reality: You can rollback data from TRUNCATE if surrounded by a transaction and session is not closed. 
http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-10-When-are-pages-from-a-truncated-table-reused.aspx

http://blog.sqlauthority.com/2007/12/26/sql-server-truncate-cant-be-rolled-back-using-log-files-after-transaction-session-is-closed/

Myth 3: An Update to a row in the table is immediately saved to disk

Reality: Updates are affecting pages in memory and T-Log immediately. Dirty pages are updated into disk later (Checkpoint, Lazywriter)

http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

Myth 4: Query Optimizer can perform SEEK in a Composite index if any one key column part of composite index is referred in WHERE/JOIN clauses.

Reality:

Index SEEK is possible only if first column of any usable Index is present in WHERE or JOIN clauses.

Update: If a column is used in WHERE or JOIN clauses and if that column is not a first column of any available index, SEEK is not possible.

Myth 4, 6 and 13 are closely related. they talk about the usage of Indexes by query processor.

Myth 5: Rebuilding a clustered index always rebuilds all non clustered indexes

Reality: As per Paul, this is not the case for SQL 2005 and SQL 2008. Product is improved a lot.

http://www.devconnections.com/updates/LasVegas_Spring10/SQL/Randal-SQL-SDB306-Mythbusters.pd

Myth 6: SELECT COUNT (*) will force a table/clustered index scan

Reality: smallest  Non clustered index (non filtered) scan will be performed if available.
Queries having COUNT (*) has possibility of using any non clustered index (non filtered) with less size if the columns in index (Index key(s), included columns) can serve the purpose.
A query can make use of any smaller size Non Clustered Index (NCI) to execute count(*) with no filter.

http://www.devconnections.com/updates/LasVegas_Spring10/SQL/Randal-SQL-SDB306-Mythbusters.pd

Myth 7: Column values are stored sequentially in same order columns are present in table structure

Reality: Nope. Fixed length columns and Variable length columns are stored separately

http://www.sqlservercentral.com/blogs/livingforsqlserver/archive/2010/12/21/sql-server-storage-internals-part-4-how-to-read-a-heap-page.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-page.aspx

Myth 8: NULL won’t occupy space in SQL Server

Reality: Yes for Variable length columns and No for Fixed length columns

You can create a table with nullable fixed & variable length columns and check PAGE content.

Myth 9: Rows in Clustered index table are physically sorted based on clustered index key

Reality: Yes. But you can’t expect the same at all the times. SQL Server can make use of
 Row offset array entries to maintain index order.

We can find lots of demonstrations on web.

Myth 10: Data type of a table is not a key criteria while doing query tuning?

Reality: Data types decide how many rows can fit into a page. A wrong data type may consume 1000’s of excess data pages. This will directly affect IO.
A simple char(100) can do this. Be cautious in choosing data types.

Myth 11: All Indexes in my table will be used in query processor

Reality:  Query processor may keep your index untouched for many reasons.
Lots of Non unique values in index keys or duplicate indexes on same columns can make an index unusable.

Myth 12 An index FILLFACTOR is used by regular DML operations

Reality: Thanks to Paul. FILLFACTOR only applies during index build and rebuild operations.

http://www.devconnections.com/updates/LasVegas_Spring10/SQL/Randal-SQL-SDB306-Mythbusters.pdf

Myth 13: I have created some indexes on frequently used columns. my work is done. All my indexes will be used by query processor.

Reality:

1. Query Processor may decide to go for TABLE SCAN/Clustered Index SCAN if record count of a table is very less (its more faster to refer few data pages instead of refering index pages and perform lookups) .

2. Query Processor may decide to go for TABLE SCAN/Clustered Index SCAN if bigger range of rows need to refered in Non Clustered Index (lookup of huge records is a pain)

3. Query processor may simply keep an  Index unused if the Indexed column is having huge number duplicate values (like city, comments or some datetime columns).

Conclusion:

Paul's  complete myths and realities collection is available in http://www.sqlskills.com/BLOGS/PAUL/ (also available in pdf version)

Will catch you in my next blog. till then happy reading.