Querying special character like Percentage in Like operator

It is very tricky to search percentage character in some column. The below query will throw an error on execution:

SELECT LedgerName FROM LedgerMaster WHERE LedgerName LIKE '% %%' 

Instead of above query we had to use below query:


SELECT LedgerName FROM LedgerMaster WHERE LedgerName LIKE '% |%%' ESCAPE '|'

"ESCAPE" keyword are used within an Query to tell the SQL Server that the escaped part of the Query string should be handled differently. In above Query ESCAPE character is mentioned as '\' hence character after '|' is processed differently instead of normal there '%' character is search in "LedgerName" column of "LedgerMaster" table.

Get Start and End date of Fortnight

Sometime it is needed to get the start and end date of fortnight.
Use below query to get the result:


DECLARE @DateInput DATETIME
SET @DateInput = '28-Jan-2013'

DECLARE @FNStartDate DATETIME
DECLARE @FNEndDate DATETIME

IF DAY(@DateInput) < 16  -- First Half of Month
BEGIN
SET @FNStartDate = CAST(CAST(YEAR(@DateInput) AS VARCHAR(4)) + '/' + CAST(MONTH(@DateInput) AS VARCHAR(2)) + '/01' AS DATETIME)
SET @FNEndDate = CAST(CAST(YEAR(@DateInput) AS VARCHAR(4)) + '/' + CAST(MONTH(@DateInput) AS VARCHAR(2)) + '/15' AS DATETIME)
END
ELSE -- Second Half of Month
BEGIN
SET @FNStartDate = CAST(CAST(YEAR(@DateInput) AS VARCHAR(4)) + '/' + CAST(MONTH(@DateInput) AS VARCHAR(2)) + '/16' AS DATETIME)
SET @FNEndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateInput)+1,0))
END

SELECT @FNStartDate AS 'StartDate', @FNEndDate AS 'EndDate'

Slow Query Performance in SqlDataReader vs. Management Studio

Today I was working on a query for a web application, I noticed that the query which executes within a second in Management Studio, was taking nearly a minute when executed by a stored procedure called via SqlDataReader.ExecuteReader(). I was surprised to see the result as I had not seen such huge difference of time in query execution. The query performs a LIKE operation on an indexed computed NVARCHAR column. 

The SET ARITHABORT setting which seems to take a different value in Management Studio and .Net libraries. The ARITHABORT setting is by itself a source of many questions.

The setting of SET ARITHABORT is set at execute or run time and not at parse time.

SET ARITHABORT must be ON when creating or manipulating indexes on computed columns or indexed views.

After adding a SET ARITHABORT ON statement to the stored procedure, the query performed the same in both conditions.

Get First and last day of week

Use below function to get the First day of week


CREATE FUNCTION Week1stDay (@DateInput DateTime)
-- Add the parameters for the function here

RETURNS DateTime
AS
BEGIN
DECLARE @Result DateTime

-- Add the T-SQL statements to compute the return value here
-- 1 -> Sunday, 7 -> Saturday
SELECT @Result = DATEADD(DAY, 1- DATEPART(DW, @DateInput), @DateInput)
RETURN @Result
END

Use below function to get the Last day of week

CREATE FUNCTION WeekLastDay (@DateInput DateTime)

-- Add the parameters for the function here

RETURNS DateTime
AS
BEGIN
DECLARE @Result DateTime
-- Add the T-SQL statements to compute the return value here
-- 1 -> Sunday, 7 -> Saturday
SELECT @Result = DATEADD(DAY, 7- DATEPART(DW, @DateInput), @DateInput)
RETURN @Result
END

Run below script to get the values from function:

SELECT dbo.Week1stDay(GETDATE()) AS StartDate

SELECT dbo.WeekLastDay(GETDATE()) AS EndDate

ALTER Schema / Owner name

Query to change the schema / Owner of database objects like 

EXEC sys.sp_changeobjectowner <Object Name>, <New Owner Name>

The Object Name(@objname) parameter should in the format "[owner].[object]". The New Owner Name(@newowner) should be valid name from sysUsers object

SELECT * FROM SysUsers