MS SQL Server

SQL User Define Function to Replace String

Create User Define Function that will Replace string from Column. So that Query or Stored Procedure remain neat.

Eg.

Create function dbo.ReplaceAddress (@input Varchar(250))

Returns Varchar(250)

Begin

Declare @Address Varchar(250)

Set @Address = @Input

Set @Address = Replace(Replace(Replace(Replace(@Address,' Drive' , ' .Dr'), ' Avenue', ' Ave.'), ' Road' ,' Rd.'), ' Street' , ' St.')

Return @Address

End

Here is Before and After Screenshot using UDF

Calculated Age Column in SQL Server Using Function

Function to Calculate Age Column based on Floor.

Create FUNCTION [dbo].[fnCalcAge]

(

@DOB datetime, -- Provide Column name of BirthDate.

@CurrentDate datetime -- Provide Date or getdate() for Current Date.

)

RETURNS real

BEGIN

RETURN FLOOR(((DATEDIFF(dd, @DOB, @CurrentDate) +

CASE WHEN DATEPART(mm, @DOB) = DATEPART(mm, @CurrentDate) AND

DATEPART(dd, @DOB) = DATEPART(dd, @CurrentDate)

THEN 1 ELSE 0 END) / 365.25) / .25) * .25

END

Use function with calculated age column :

For eg. Column name is BirthDate

([dbo].[fnCalcAge]([BirthDate],getdate()))

Display All Records on Single Page in SSRS

Display all Record in SSRS on single page instead of Multiple Pages.

Before

Change Property of Report

Change Interactive Size Height to 0in

You might get following error message.

This is due to Performance impact. If you have huge number of record it is not recommended to change page size and keep it on single page.

After

SSRS Expression to change Total Number of Pages on Top

SSRS Expression to show total number of Pages

-- Drag Text Bot in Page Header
--Use following Expression

="Page " & Globals.PageNumber & " of " & Globals.TotalPages

Before Expression

After Expression

List Of Active Trigger in Database

Following Query use to find all Active Trigger in Database

SELECT

sysobjects.name AS TriggerName,

OBJECT_NAME(parent_obj) AS TableName,

OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS UpdateTrigger,

OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS DeleteTrigger,

OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS InsertTrigger,

OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS AfterTrigger,

OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS InsteadOfTrigger

FROM

sysobjects INNER JOIN

sysusers

ON sysobjects.uid = sysusers.uid INNER JOIN

sys.tables t

ON sysobjects.parent_obj = t.object_id INNER JOIN

sys.schemas s

ON t.schema_id = s.schema_id

WHERE

sysobjects.type = 'TR' and

OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') <> '1'

List all Default Constraints in Table

Find Default Constraints with Default to in Particular Table in Database.

SELECT

c.name AS ColumnName,

d.name AS DefaultConstraintName,

d.definition AS [Default],

t.name AS TableName

FROM

sys.default_constraints d INNER JOIN

sys.columns c

ON d.parent_object_id = c.object_id

AND d.parent_column_id = c.column_id INNER JOIN

sys.tables t

ON t.object_id = c.object_id INNER JOIN

sys.schemas s

ON s.schema_id = t.schema_id

Where t.Name = 'YourTableName'

Find Trigger for Table in SQL Server

Find Trigger for Particular Table in SQL Server

Select

so.Name, Text

From

sysobjects so, syscomments sc

Where

Type = 'TR'

and so.id = sc.id

and Text like '%YourTableName%'

Find Primary Key, Foreign Key, Constrain in SQL Table

Find Column Name, Primary Key, Foreign Key, Constrain for particular Table in Database.

SELECT distinct

Col.Column_Name,

Col.CONSTRAINT_Name as [Key] ,

CONSTRAINT_TYPE as Constraint_Type

From

INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col

WHERE

Col.Constraint_Name = Tab.Constraint_Name

AND Col.Table_Name = Tab.Table_Name

AND Col.Table_Name = 'YourTableName'

Order By in SQL Server with Condition or Clause

When you order by column in table in SQL Server but with the clause here is the example :

Suppose we have StateCity table.

Now we want to in a order that State NY is first and Else in Alphabetic Order here is the Query :

Select * from StateCity

Order By Case When [State] = 'NY' Then '1'

When [State] = 'NJ' Then '2'

Else [State] END ASC

Result:

Suppose you want to Order By Particular State and Particular City here is the example of the Query :

Select * from StateCity

Order By Case

When [State] = 'NY' Then '1'

When [State] = 'NJ' Then '2'

Else [State] END ASC,

Case

When [City] = 'New York' Then '1'

When [City] = 'Atlanta' Then '1'

When [City] = 'Parsippany' Then '1'

Else [City] END ASC

Result:

SQL Query to Convert Multiple CSV Column to Row

Suppose you have following kind of table that has multiple CSV and you want to convert it to Row.

Want Result like :

First Create Following Table- Valued Functions in SQL Server

Create FUNCTION [dbo].[Splitstrings] (@List NVARCHAR(MAX),

@Delimiter NVARCHAR(255))

RETURNS @split_tab TABLE (

RN INT IDENTITY(1, 1),

SPLIT_VAL VARCHAR(100))

WITH SCHEMABINDING

AS
   BEGIN ;

;WITH E1(N)

AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),

E2(N) AS (SELECT 1 FROM E1 a,E1 b),

E4(N) AS (SELECT 1 FROM E2 a, E2 b),

E42(N) AS (SELECT 1 FROM E4 a, E2 b),

cteTally(N)

AS (SELECT 0

UNION ALL

SELECT TOP (Datalength(Isnull(@List, 1))) Row_number()OVER (

ORDER BY (SELECT NULL))

FROM E42),

cteStart(N1)

AS (SELECT t.N + 1

FROM cteTally t

WHERE ( Substring(@List, t.N, 1) = @Delimiter

OR t.N = 0 ))

INSERT INTO @split_tab

SELECT SPLIT_VAL= Substring(@LIST, S.N1, Isnull(NULLIF(Charindex(@DELIMITER, @LIST, S.N1), 0) - S.N1, 8000))

FROM CTESTART S;

RETURN

END

Then Use Following Query (CTE) to Convert in to Row.

;WITH StudentSubjects

     AS
(

SELECT          StudentID,

                StudentName,

                Subjects,

                HoursPerWeek,

                Days,

                Time
FROM StudentClass

),

SUBJECTS
AS
(
SELECT    RN, -- RN FROM SPITSTRING FUNCTION

          StudentID,

          StudentName,

          Subjects=SPLIT_VAL

FROM StudentClass

CROSS apply Splitstrings (Subjects, ',')),

HOURSPERWEEK
AS
(
SELECT
          RN,

          StudentID,

          StudentName,

          HoursPerWeek=SPLIT_VAL

FROM StudentClass

CROSS apply Splitstrings (HoursPerWeek, ',')),

Days

AS
(
SELECT
          RN,

          StudentID,

          StudentName,

          Days=SPLIT_VAL

FROM StudentClass

CROSS apply Splitstrings (Days, ',')),

Time

AS
(
SELECT
           RN,

           StudentID,

           StudentName,

           Time=SPLIT_VAL

FROM StudentClass

CROSS apply Splitstrings (Time, ','))

SELECT
           D.STUDENTID,

           D.STUDENTNAME,

           RTRIM(LTRIM(S.SUBJECTS)) AS SUBJECTS,

           RTRIM(LTRIM(H.HOURSPERWEEK)) AS HOURSPERWEEK,

           RTRIM(LTRIM(DA.DAYS)) AS DAYS,

           RTRIM(LTRIM(T.TIME)) AS TIME

FROM StudentClass D

FULL OUTER JOIN SUBJECTS S

          ON D.StudentID = S.StudentID

FULL OUTER JOIN HOURSPERWEEK H

          ON D.StudentID = H.StudentID

          AND H.RN = S.RN

FULL OUTER JOIN DAYS DA

           ON D.StudentID = DA.StudentID

           AND DA.RN = COALESCE(S.RN, H.RN)

FULL OUTER JOIN TIME T

           ON D.StudentID = T.StudentID

           AND T.RN = COALESCE(S.RN, H.RN, DA.RN)

---------------------------------------------------------------------

SQL Create Table and Insert Value Script :

CREATE TABLE [dbo].[StudentClass](

[StudentID] [int] NULL,

[StudentName] [nvarchar](150) NULL,

[Subjects] [nvarchar](150) NULL,

[HoursPerWeek] [nvarchar](150) NULL,

[Days] [nvarchar](150) NULL,

[Time] [nvarchar](150) NULL

) ON [PRIMARY]

GO
INSERT [dbo].[StudentClass] ([StudentID], [StudentName], [Subjects], [HoursPerWeek], [Days], [Time]) VALUES (1, N'Ria S', N'Maths, Science', N'10,15', N'Sun, Mon', N'10:20, 11:50')

GO
INSERT [dbo].[StudentClass] ([StudentID], [StudentName], [Subjects], [HoursPerWeek], [Days], [Time]) VALUES (2, N'Robert T', N'Economics, Graphics, Accounting', N'15,18,16', N'Tue, Wed, Thur', N'10:60,13:20, 18::50')

GO