Date Functions- get week, month, day of week

--get week of the given date as integer
select datepart(week,getdate())

--get day of week of given date as integer
select datepart(dw,getdate())

--get month of given date as integer
select datepart(month,getdate())

--converting month to varchar from calendar

select case when month(getdate()) = '1' then 'jan'
when month(getdate()) = '2' then 'feb'
when month(getdate()) = '3' then 'mar'
when month(getdate()) = '4' then 'apr'
when month(getdate()) = '5' then 'may'
when month(getdate()) = '6' then 'jun'
when month(getdate()) = '7' then 'jul'
when month(getdate()) = '8' then 'aug'
when month(getdate()) = '9' then 'sep'
when month(getdate()) = '10' then 'oct'
when month(getdate()) = '11' then 'nov'
when month(getdate()) = '12' then 'dec'
end

Get FinYear

-- user defined funtion to get financial year starting from 01st Jul to 30th June
ALTER FUNCTION [dbo].[ufGetFinYr]
(
@CurDate datetime
)
RETURNS varchar(9) AS
BEGIN
Declare @CurrentMonth tinyint,@FinYear varchar(9)
select @CurrentMonth = month(@CurDate)
if @CurrentMonth >=1 and @CurrentMonth <=6 select @FinYear = convert(varchar,Year(@CurDate)-1) + '-' + convert(varchar,Year(@CurDate)) else select @FinYear = convert(varchar,Year(@CurDate)) + '-' + convert(varchar,Year(@CurDate)+1) return @FinYear END

--to query this
select dbo.ufgetfinyr(getdate())

Duplicate records in a table

--to find out duplicate records in a table
select salesordernoitem from tbltransdetails group by salesordernoitem
having count(salesordernoitem) > 1

Search a string in all stored procedures

-- To search string "repid" through all stored procedures this limits to 4000 characters

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%repid%'
AND ROUTINE_TYPE='PROCEDURE'

-- To search string "repid" through all stored procedures

SELECT
sys.objects.name,
sys.objects.type_desc,
sys.sql_modules.definition
FROM
sys.sql_modules inner join sys.objects
on sys.sql_modules.object_id = sys.objects.object_id
WHERE
sys.sql_modules.definition LIKE '%tblShipToMaster%'

Find all Table size in SQL 2005

DECLARE @TableName VARCHAR(100) --For storing values in the cursor
--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
--Open the cursor
OPEN tableCursor
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName
--Get the next table name
FETCH NEXT FROM tableCursor INTO @TableName
END
--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
--Select all records so we can use the reults
SELECT * into tbltables_size
FROM #TempTable
--Final cleanup!
DROP TABLE #TempTable
GO
SELECT * from tbltables_size ORDER BY CAST(LEFT(dataSize,LEN(dataSize)-3) AS NUMERIC(18,0)) DESC

System Requirements for Database Mirroring

· Database mirroring is fully supported on standard, developer and enterprise edition.
· We need the principal and mirror server. Witness is a option in our case we can keep it as manual changeover.

· Operation can be synchronous (obviously adds some latency cost to complete transaction across two servers --- advantages high availability and high protection)

o or asynchronous( more speed – high performance - without waiting for mirror to write on the log)

· sql server instances should be of same service pack
· verify we have the enough disk space where the mdf and ldf is stored on the both the servers
· the principal database should be on FULL database recovery mode
· please note all transactions are written to transaction log and not truncated

Longest running sql query

--sql 2005 & 2008
--to identify the longest running sql query -- run under the master database

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO