SQL Server 2012 Database Engine Limitations

Isolation Levels in SQL Server

The Isolation levels are followed as a continuity of the previous one . I am not explaining the first two Isolation levels (Read Committed and Read Uncommitted) as everybody is familiar with those. We can briefly explain the others as below:

Repeatable Read:- Repeatable read guarantees records queried by a previous select will not be changed or deleted, it does not stop new records being inserted but it is still possible to get Phantom reads (Subsequent reads of the data in the same transaction could be different) at this isolation Level.

Serializable:- This isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads. It does this by placing range locks on the queried data. This causes any other transactions trying to modify or insert data touched on by this transaction to wait until it has finished.

Snapshot:- This provides the same guarantees as serializable. So the difference is, it doesn't block other queries from inserting or updating the data touched by the snapshot transaction. Instead row versioning is used so when data is changed the old version is kept in tempdb so existing transactions will see the version without the change. When all transactions that started before the changes are complete the previous row version is removed from tempdb. This means that even if another transaction has made changes you will always get the same results as you did the first time in that transaction.

 

The below table will give you an elaborate idea about Isolation Levels:

                                                                                                                                               

Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads
Read Uncommitted	Y	Y	Y
Read Committed	N	Y	Y
Repeatable Read	N	N	Y
Serializable	N	N	N

Weak

         

Strong

FORCESEEK HINT is extended in Denali

When SQL Server chooses seek over scan while retrieving record details, it will compare key value with search input, based on comparison result, Query engine will move to appropriate page. If index has multiple columns, if we don't want all key columns to consider, in SQL Server 2012, we can mention the index columns to consider when the index has multiple key columns.

SELECT EmployeeID,EmployeeName,Account

FROM Employees 

WITH (FORCESEEK(Idx_Employee(EmployeeID)))
WHERE EmployeeID = 1024

Online Index Rebuild in Denali

The previous versions of SQL Server never allowed index operations (CREATE, DROP, REBUILD) on ONLINE mode, if the index includes a Large Value Type column (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) etc).

This limitation has been removed in SQL Server 2012 (Denali). The above code runs without an error in a SQL Server 2012 instance.

 

Restrictions on TEMPDB

• Adding filegroups.
• Backing up or restoring the database.
• Changing collation. The default collation is the server collation.
• Changing the database owner. tempdb is owned by dbo.
• Creating a database snapshot.
• Dropping the database.
• Dropping the guest user from the database.
• Enabling change data capture.
• Participating in database mirroring.
• Removing the primary filegroup, primary data file, or log file.
• Renaming the database or primary filegroup.
• Running DBCC CHECKALLOC.
• Running DBCC CHECKCATALOG.
• Setting the database to OFFLINE.
• Setting the database or primary filegroup to READ_ONLY.

How the TempDB becomes Full?

• DBCC CHECKDB will perform its work in tempdb
• DBCC DBREINDEX or similar DBCC commands with "Sort in Tempdb" option can make the tempdb full
• Large resultsets involving unions, order by, group by, joins, temp tables etc. can also fill up tempdb
• Any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb
• An ODBC DSN with the option 'Create  temporary stored procedures' set can leave objects in tempdb.

Compatibility Levels

• 60 = SQL Server 6.0
• 65 = SQL Server 6.5
• 70 = SQL Server 7.0
• 80 = SQL Server 2000
• 90 = SQL Server 2005
• 100 = SQL Server 2008