How to get SQL Server Restore history using T-SQL?

How to find SQL Server databases Restore History?
How to find Last Restore Date od SQL Server database?

Few developers asked me how to find the last time a database was restored using T-SQL. I have formed the following query using MSDB..RestoreHistory table.

SELECT *
FROM MSDB..RestoreHistory WITH (nolock)
WHERE destination_database_name = 'DatabaseName'
ORDER BY restore_date DESC

What is lock escalation?

Lock escalation is the process of converting a lot of low level locks (For example row locks, and page locks) into higher level locks (e.g. table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.

In the hierarchy of the objects in a SQL Server instance, at the top level we have Database, followed by Schema, Tables, Table Partitions, Pages and then finally the individual Rows. If you acquire a lock at higher level, it can cover more resources there by you consume fewer lock resources (each lock structure takes approximately 100 bytes) and the locking overhead but this comes at a price of lower concurrency. So for example, if you want to select all the rows of a table, if you acquire a lock at table level, you will not need to lock individual rows or pages but then it will block any concurrent update transaction. Similarly, if you lock individual rows, you will get higher concurrency but then you will incur the overhead of acquiring/releasing locks on each row and lot more locking resources depending upon the isolation level of your transaction, as you may need to hold the locks on all the rows till the end of transaction.

Depending upon the estimates during query compilation, the SQL Server recommends the locking granularity (i.e. row, page or table) appropriately and during query execution, depending on the concurrent work load, the appropriate locking granularity is applied. User can override the locking granularity option explicitly by providing locking hints and/or by executing sp_indexoption stored procedure. While locking granularity is chosen at the start of query execution but during the execution, the SQL Server may choose to escalate the lock to lower level of granularity depending on the number of locks acquired and the availability of memory at run time.

T-SQL Challenge

What will be the output of below T-SQL code:

CREATE TABLE #TestDate
(
   [ID] int IDENTITY(1,1)
   ,[FullDate] datetime DEFAULT (GETDATE()),
)
GO
INSERT INTO #TestDate VALUES
('01/07/2010'),('2010/07/01'),('07/01/2010')
GO
SELECT COUNT([FullDate]) FROM #TestDate
WHERE CAST([FullDate] as int) = 40358
GO
DROP TABLE #TestDate
GO

Answer this without cheating (without executing the Query). Here are the options:

A.   1
B.   2
C.   3
D.   Error
F.   None of the above