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

 

Query Optimization Tips

Before optimize any query clean buffer first after we can see what's exactly going on

  - DBCC dropcleanbuffer
  - DBCC FreeProcCatch

For See Statistsics Also See In Execution Plan

  - Set Statistics IO ON
  - Set Statistics Time ON

For Optimization We are Concentrate on 3 Most Statistic

1) NO of logical reads
2) CPU Time
3) Query Cost (QC)

Both 1st and 2nd You will Get From Upper Commands
But For QC Use Execution Plan

See and Note All Statistic and Then Apply This Instruction For optimization that Given Below:

1) Limit no of colums return from query
2) Create Primary Key
3) Create index which are using in workhours
4) Limit the no of rows by using top
5) Working with join have index on the column in the join
6) If you use multiple columns in where or order by clause then create compound index
7) If you are trying to fetch unique values use group by instead of distinct
8) Use exists in behalf of in().
9) set nocount on

Query Optimization OR and Union

When we execute query with OR clause,filtering the records from two 
different field. Oracle Database can not use index to filter the that.

1) create Index Idx_empno on emp(empno)

2) create Index Idx_salary on emp(salary)

Select * from emp
where empno = 7319 or salary > 15000

In this query Both the field have an index then also you can see
that it takes to much time because of "or" indexing field not work because of OR

so use Union to overcome this,

Select * from emp where empno = 7319
Union
Select * from emp where salary > 15000

 

Replace word in all table..

--To replace all occurences of 'America' with 'USA' in all Tables of database
EXEC SearchReplace 'America', 'USA'


CREATE PROC SearchReplace
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
      ) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' =  REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END

SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

System Configuration,Files and paths of Database,OS information

1) System Configuration
SELECT *
FROM sys.configurations
ORDER BY name OPTION (RECOMPILE);

2) Filename and Paths of Database
SELECT DB_NAME([database_id])AS [DBName],
name, physical_name, type_desc, state_desc,
CONVERT( bigint, size/128.0) [SizeinMB]
FROM sys.master_files
ORDER BY DB_NAME([database_id])

3)OS level information
SELECT *
FROM sys.dm_os_sys_info

Xp_cmdshell For OS commands

Introduced in sql server 2005
xp_cmdshall option is a server configuration option that enables system administrator.


"xp_cmdshell" is an extended stored procedure provided by Microsoft and stored
in the master database. This procedure allows you to issue operating system commands
 directly to the Windows command shell via T-SQL code


-- To allow advanced options to be changed.


EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
________________________________________________________________
exec master.dbo.xp_cmdshell 'dir c:\temp\*.sql'
________________________________________________________________
exec master.dbo.xp_cmdshell 'mkdir "c:\temp\SQL Agent Output\new_job\"'
________________________________________________________________
DECLARE @rc int
EXEC @rc = master.dbo.xp_cmdshell 'copy c:\temp\doesnotexist.txt c:\temp\workfile.txt'
print @rc
IF @rc <> 0
BEGIN
  PRINT 'Copy Failure Skip work'
END
ELSE
BEGIN
  Print 'Copy worked now we can do some more stuff'
  ....
END
________________________________________________________________