SQL Server – Page 3 – Jitendra Zaa's Blog

Difference in “varchar” and “nvarchar” in SQL Server

This article explains the difference between varchar and nvarchar datatype in SQL Server.


nVarchar	Varchar
varchar stores UNICODE data. Means it store UNICODE or multilingual data.	Varchar Stores ASCII data
UNICODE requires 2 bytes for each character you store. This Means single row of a single nvarchar column can only be nvarchar (4000) Because because of the row size limitations of Sql Server is the same as the page size limit, which is 8060 bytes	ASCII only requires 1 byte for each character. This means a single row of a single varchar column can be varchar(8000) because of the row size limitations of Sql Server is the same as the page size limit, which is 8060 bytes

Get Foreign Key related details for the Table

Getting the foreign key detail of particular table is the most common tasks used by DBA or database developers. every time go to left panel, select table and right click to get foreign key details wastes most of the time.

So below is the script which will return the detail about foreign key of particular table:

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME(f.parent_object_id)='TABLE_NAME'

Drop Function if exist – SQL Server

Below script will help to drop the scalar function present in SQL Server 2005 if exist.

It is always good practice to write the drop statement before creating any stored procedure or the database function.
Lets say the function name is fn_GetFirstDayOfWeek

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetFirstDayOfWeek]')
AND type in (N'FN', N'IF',N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetFirstDayOfWeek]

Get the FortNight Start and End date – SQL Server

in some scenario we comes in very strange requirement. with me it happens. i needed to calculate date in which fort night the date is coming and on the basis of that calculate start and end date. so i have created below script. I hope it will be useful to readers.


DECLARE @InputDate DATETIME
SET @InputDate = '17-Jul-2010'

DECLARE @FortStartDate DATETIME
DECLARE @FortEndDate DATETIME

IF DAY(@InputDate) < 16 --First FortNight
BEGIN
SET @FortStartDate = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01' AS DATETIME)
SET @FortEndDate = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/15' AS DATETIME)
END
ELSE --Second FortNight
BEGIN
SET @FortStartDate = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/16' AS DATETIME)
SET @FortEndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@InputDate)+1,0))
END

SELECT @FortStartDate as 'StartDate'
SELECT @FortEndDate as 'EndDate'

Get First Day and Last Day of Week – SQL Server

This time lets create the Scalar function in SQL Server to get the First Day and Last Day of the Week.

Create below Scalar function in SQL Server to get the First Day of Week.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetFirstDayWeek
(
	-- Add the parameters for the function here
	@InputDate DateTime
)
RETURNS DateTime
AS
BEGIN
	-- Declare the return variable here
	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, @InputDate), @InputDate)

	-- Return the result of the function
	RETURN @Result

END
GO

Create below Scalar function in SQL Server to get the Last Day of Week.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetLastDayWeek
(
	-- Add the parameters for the function here
	@InputDate DateTime
)
RETURNS DateTime
AS
BEGIN
	-- Declare the return variable here
	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, @InputDate), @InputDate)

	-- Return the result of the function
	RETURN @Result

END
GO

To get the values from function, run below script

 select dbo.GetFirstDayWeek(GETDATE()) as StartDate
 select dbo.GetLastDayWeek(GETDATE()) as EndDate

as i said, remember that to call function we have to call like dbo.functionName

Get First Day and Last Day of Month – SQL Server

One of the features i love about SQL Server is function supports. Divide your task in functions just like other programming language. In this article i am going to show you that how to display the First Day and Last Day of Month in SQL Server.

Create a below function to get the First Day of Month:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetFirstDay
(
-- Add the parameters for the function here
@InputDate DateTime
)
RETURNS DateTime
AS
BEGIN
-- Declare the return variable here
DECLARE @Result DateTime

-- Add the T-SQL statements to compute the return value here
SELECT @Result = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01' AS DATETIME)

-- Return the result of the function
RETURN @Result

END
GO

Now, Create Below Function to get the Last day of month.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetLastDay
(
-- Add the parameters for the function here
@InputDate DateTime
)
RETURNS DateTime
AS
BEGIN
-- Declare the return variable here
DECLARE @Result DateTime

-- Add the T-SQL statements to compute the return value here
SELECT @Result = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@InputDate)+1,0))

-- Return the result of the function
RETURN @Result

END
GO

To get the values from function, run below script

select dbo.GetFirstDay(GETDATE()) as StartDate
select dbo.GetLastDay(GETDATE()) as EndDate

remember that to call function we have to call like dbo.functionName

Drop Stored Procedure if exist in SQL Server

Its very frequent that we need to check whether particular store procedure exist in database or not?

If stored procedure exist then delete the stored procedure, otherwise we have to toggle between Create Procedure and Alter Procedure.

Below is the SQL to delete the stored procedure if exist.

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[SPNAME]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

BEGIN

DROP PROCEDURE dbo.SPNAME

END

Search for Table in SQL Server

In organization, where huge number of tables present in database. its very hard to search the particular table.

Here few SQL queries to search the table name by pattern.

Search For Table whose name starts with A

SELECT DISTINCT [Table] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE
OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
AND OBJECT_NAME(OBJECT_ID) like 'A%'

Clustered Index speeds up performance of the query ran on that table. Clustered Index are usually Primary Key but not necessarily. I frequently run following query to verify that all the developers are creating all the tables with Clustered Index.

SELECT DISTINCT [Table] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE
INDEX_ID = 0 AND
OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1

List of Non clustered Tables in SQL Server 2005