Solve Query...

I have One table Like

Table1:
======

ID      Name           Date             Value
--------------------------------------------------
1        naimish        10/4/12          50
2        jugal             12/4/12        150
3        vimal            15/4/12        300
4        mohit            20/4/12        450

Display Output Like Below (using Query Only)

ID      Name            Date              Value          Date_Modified        New_Value
------------------------------------------------------------------------------------------------
1        naimish         10/4/12          50              12/4/12                  100
2        jugal              12/4/12         150             15/4/12                  150
3        vimal             15/4/12         300             20/4/12                  150
4        mohit             20/4/12         450             Null                        Null

Ans:

SELECT t1.id, t1.name,t1.date,t1.value , t2.date Date_Modified , t2.value - t1.value New_Value FROM `temp` t1
join (SELECT id - 1 as id ,name,date,value FROM `temp`) as t2
on (t1.id = t2.id)
union all
select id, name ,date ,  value , null ,null from temp
where id in(select max(id) from temp)

Cache Memory Information

To See What type of information is stored in cache memory

SELECT COUNT(*) AS cached_pages_count,
name AS BaseTableName, IndexName,
IndexTypeDesc
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT s_obj.name, s_obj.index_id,
s_obj.allocation_unit_id, s_obj.OBJECT_ID,
i.name IndexName, i.type_desc IndexTypeDesc
FROM
(
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id ,allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id, allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS s_obj
LEFT JOIN sys.indexes i ON i.index_id = s_obj.index_id
AND i.OBJECT_ID = s_obj.OBJECT_ID ) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id, IndexName, IndexTypeDesc
ORDER BY cached_pages_count DESC;
GO

To clean Buffer Use utility.

DBCC DROPCLEANBUFFERS