Tag: TSQL

I tried to create an average in sql server by dividing two figures. Once I got the error of “Divide by zero error occured”, this is caused by dividing the figure by null value, to handle the error we can use “NULLIF”

DECLARE @ThisMonthAvg decimal(8,2)
SET @ThisMonthAvg = @ThisMonthPlayers / NULLIF(@ThisMonthEvents, 0)

DECLARE @LastMonthAvg decimal(8,2)
SET @LastMonthAvg = @LastMonthPlayers / NULLIF(@LastMonthEvents, 0)

DECLARE @ThisYearAvg decimal(8,2)
SET @ThisYearAvg = @ThisYearPlayers / NULLIF(@ThisYearEvents, 0)

DECLARE @LastYearAvg decimal(8,2)
SET @LastYearAvg = @LastYearPlayers / NULLIF(@LastYearEvents, 0)

My friend has been asked to optimize the SQL query for a forum application. Basically the Query is used to list the thread as well as the last person who posted on the forum. He and me tried to optimize that and we can fasten up the query up to only 13 secs which is not really well 🙁 . then the sql guru is coming to help him and show him the better way in writing the query and it performs only 1 sec. I thought it’s gonna be a good lesson to be shared it on my blog. There you go:

--Non Optimized Version
SELECT
	ForumTopic.ForumTopicID, ForumTopic.Title, Members.Username AS MemberName,
	ForumTopic.Views, ForumTopic.Replies,
	(
		SELECT TOP 1
			ForumPost.EnteredDate
		FROM
			ForumPost
		WHERE
			ForumPost.ForumTopicID = ForumTopic.ForumTopicID
		ORDER BY
			ForumPost.ForumPostID DESC
	) AS LastPostEnteredDate,
	(
		SELECT TOP 1
			Members.Username
		FROM
			ForumPost
		INNER JOIN
			Members
		ON
			ForumPost.EnteredBy = Members.MemberID
		WHERE
			ForumPost.ForumTopicID = ForumTopic.ForumTopicID
		ORDER BY
			ForumPost.ForumPostID DESC
	) AS LastPostMemberName
FROM
	ForumTopic
INNER JOIN
	Members
ON
	ForumTopic.EnteredBy = Members.MemberID
WHERE
	ForumID = @ForumID
AND
	ForumTopic.Valid = 1
ORDER BY
	LastPostEnteredDate DESC

--optimized version
SELECT
	ForumTopic.ForumTopicID,
	ForumTopic.Title,
	Members.UserName as MemberName,
	ForumTopic.Views,
	ForumTopic.Replies,
	ForumPost.EnteredDate as LastPostEnteredDate,
	Members1.Username as LastPostMemberName
FROM
	(
		SELECT
			ForumTopicID, MAX(ForumPostID) AS ForumPostID
		FROM
			ForumPost
		WHERE
			ForumTopicID IN (
				SELECT
					ForumTopicID
				FROM
					ForumTopic
				WHERE
					ForumTopic.ForumID = @ForumID
				AND
					ForumTopic.Valid = 1)
		GROUP BY
			ForumTopicID
	) RecentPost
INNER JOIN
	ForumPost
ON
	ForumPost.ForumPostID = RecentPost.ForumPostID
INNER JOIN
	ForumTopic
ON
	ForumTopic.ForumTopicID = RecentPost.ForumTopicID
INNER JOIN
	Members
ON
	ForumTopic.EnteredBy = Members.MemberID
INNER JOIN
	Members Members1
ON
	ForumPost.EnteredBy = Members1.MemberID
ORDER BY
	ForumPost.EnteredDate DESC

How to do a recursive query in SQL Server 2005, Recursive in the terms of you got a table where it has foreign key to the other record on the same table. Assume a table employee where a record on that table can be an employee or a manager which is an employee as well, or let’s call it when you have nested structure within one table, call it as a tree structure. I’ve googled and found the way in doing it so I believe it’s worthy for me to share with everyone

Table Schema

How to get the whole structure by using one SQL query, we can utilize CTE(Common table expression) and using UNION ALL to do the recursive and do the INNER JOIN with the CTE it self

--get all contentPageID for the page and its children
WITH dynamicPages(contentPage_id, parentContent_id, contentPageName) AS
	(
		SELECT
			c.contentPage_id, c.parentContent_id, c.ContentPageName
		FROM
			tblContentPage c
		WHERE
			c.contentPage_id = @contentPage_id
		UNION ALL
		SELECT
			c.contentPage_id, c.parentContent_id, c.contentPageName
		FROM
			tblContentPage c
		INNER JOIN
			dynamicPages b
		ON
			c.parentContent_id = b.contentPage_id
	)
SELECT * FROM dynamicPages

this is only a simple trigger and table to monitor the activity on an update activity to “members” table. it tracks the date when the update query is being executed and who execute it, it will be quite useful for a database that has many applications accessing it with different SQL User

Holding table :

CREATE TABLE [dbo].[MemberUpdateTracking](
	[MemberTrackingID] [int] IDENTITY(1,1) NOT NULL,
	[MemberID] [int] NULL,
	[BeforeMemberCode] [varchar](50) NULL,
	[BeforeFirstName] [varchar](255) NULL,
	[BeforeSurname] [varchar](255) NULL,
	[BeforeUserName] [varchar](255) NULL,
	[BeforePassword] [varchar](255) NULL,
	[BeforeEmailAddress] [varchar](255) NULL,
	[AfterMemberCode] [varchar](50) NULL,
	[AfterFirstName] [varchar](255) NULL,
	[AfterSurname] [varchar](255) NULL,
	[AfterUserName] [varchar](255) NULL,
	[AfterPassword] [varchar](255) NULL,
	[AfterEmailAddress] [varchar](255) NULL,
	[ModifiedDate] [datetime] NOT NULL,
	[SQLUser] [varchar](255) NOT NULL,
 CONSTRAINT [PK_MemberUpdateTracking] PRIMARY KEY CLUSTERED
(
	[MemberTrackingID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Triggers:

CREATE TRIGGER [dbo].[tr_Member_UPDATE]
	ON [dbo].[Members]
	FOR UPDATE
AS
BEGIN
	SET	NOCOUNT ON

	DECLARE @MemberID			int
	DECLARE @AfterFirstName		varchar(255)
	DECLARE @AfterSurname		varchar(255)
	DECLARE @AfterMemberCode	varchar(50)
	DECLARE @AfterUserName		varchar(255)
	DECLARE @AfterPassword		varchar(255)
	DECLARE @AfterEmailAddress	varchar(255)

	DECLARE @BeforeFirstName	varchar(255)
	DECLARE @BeforeSurname		varchar(255)
	DECLARE @BeforeMemberCode	varchar(50)
	DECLARE @BeforeUserName		varchar(255)
	DECLARE @BeforePassword		varchar(255)
	DECLARE @BeforeEmailAddress	varchar(255)


	--get information what will been inserted
	SELECT @MemberID = MemberID,
		   @AfterFirstName = FirstName,
		   @AfterSurname = Surname,
		   @AfterUserName = Username,
		   @AfterPassword = [Password],
		   @AfterMemberCode = MemberCode,
		   @AfterEmailAddress = EmailAddress
	FROM
		Inserted

	--only need for strange behaviour issue that we had before otherwise not necessary
	IF (@AfterFirstName = '' OR @AfterSurname = '' OR @AfterFirstName IS NULL OR @AfterSurname IS NULL)
	BEGIN
		--get the original information
		SELECT  @BeforeFirstName = FirstName,
				@BeforeSurname = Surname,
				@BeforeUserName = Username,
				@BeforePassword = [Password],
				@BeforeMemberCode = MemberCode,
				@BeforeEmailAddress = EmailAddress
		FROM
			Deleted

		--track the changes
		INSERT INTO MemberUpdateTracking(MemberID, BeforeMemberCode, BeforeFirstName,
					BeforeSurname, BeforeUserName, BeforePassword, BeforeEmailAddress,
					AfterMemberCode, AfterFirstName, AfterSurname, AfterUserName, AfterPassword,
					AfterEmailAddress, ModifiedDate, SQLUser)
		VALUES
			(@MemberID, @BeforeMemberCode, @BeforeFirstName, @BeforeSurname,
					@BeforeUserName, @BeforePassword, @BeforeEmailAddress, @AfterMemberCode,
					@AfterFirstName, @AfterSurname,
					@AfterUserName, @AfterPassword, @AfterEmailAddress,
					GETDATE(), SYSTEM_USER)
	END
END

I was having a problem when adding a stored procedure into table adapter. The error was “Invalid object” of my temporary table name inside stored procedure.

It’s a bit strange since what i thought the table adapter only care about the results being returned but what it is actually checking up the existence of the table it self. FMTONLY is used to “Returns only metadata to the client. Can be used to test the format of the response without actually running the query”.

--uncomment this to regenerate it in table adapter
--comment it once you are done with generating the table adapter
SET FMTONLY OFF

I’ve been assigned a task to delete data from a table with a subtree structure which means the foreign key can be contained in another rows in the same table. It’s a self contained foreign key on the same table.

Theoritically when it comes to my mind, i was thinking of recursive delete. But how????I’ve tried to google about it, I found this article from Microsoft. These are coming from Microsoft Article as well.

These are the dummy data that you can play around with in your DB

CREATE TABLE Employees
(empid int NOT NULL,
 mgrid int NULL,
 empname varchar(25) NOT NULL,
 salary money NOT NULL,
 CONSTRAINT PK_Employees_empid PRIMARY KEY(empid))

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 1, NULL, 'Nancy',  $10000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 2,    1, 'Andrew',  $5000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 3,    1, 'Janet',   $5000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 4,    1, 'Margaret',$5000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 5,    2, 'Steven',  $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 6,    2, 'Michael', $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 7,    3, 'Robert',  $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 8,    3, 'Laura',   $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 9,    3, 'Ann',     $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(10,    4, 'Ina',     $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(11,    7, 'David',   $2000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(12,    7, 'Ron',     $2000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(13,    7, 'Dan',     $2000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(14,   11, 'James',   $1500.00)

I’m going to delete employee no 3(Janet) which means it need to delete 7(Robert),8(Laura),9(Ann) and 11(David),12(Ron),13(Dan), 14(James) based on the managerial structure level.

What you need to do is to create a trigger for cascading delete

CREATE TRIGGER trg_d_employees_on_delete_cascade ON Employees FOR DELETE
AS

IF EXISTS(SELECT *
          FROM
              Employees AS E
            JOIN
              deleted   AS D ON E.mgrid = D.empid)

  DELETE FROM Employees
  FROM
      Employees AS E
    JOIN
      deleted   AS D ON E.mgrid = D.empid

  GO

It will self join based on the deleted table. It’s not finish yet, you need to set the recursive trigger to active by executing command like

ALTER DATABASE DMO
SET RECURSIVE_TRIGGERS ON

Action Time:

DELETE FROM employees WHERE empid = 3

and you will see that it deletes all the subordinates of Janet

In this post, I will discuss on how to optimize join. Based on my experience. When you join record you need to join as fewer as possible otherwise it will result in performance. And please make sure you put the index on the key that you are using for join.

Within SQL Server 2005, You can use temporary view, It is not a table variable and it is not a temporary table. You also need to try to avoid cursor as well if possible.

First sample is a traditional join where I joined all the tables that required. In this sample EvenResult table has 20 million rows and I need to count the evenresult table based on the EventID that I get from the join criteria.

1. Join all tables./Traditional Way and it takes about 21secs

    SELECT
                E.EventID
            FROM
                Events E
            INNER JOIN
                Venues V
            ON
                E.VenueID = V.VenueID
            INNER JOIN
                Regions R
            ON
                R.RegionID = V.RegionID
            INNER JOIN
                EventResults er
            ON
                E.EventID = er.EventID
            WHERE
                (DATEDIFF(d, E.EventDate, @DateFrom) = 0)
            AND
                ((@StateID IS NULL) OR (R.StateID = @StateID))
            AND
                ((@RegionID IS NULL) OR (R.RegionID = @RegionID))
            AND
                ((@VenueID IS NULL) OR (V.VenueID = @VenueID))
            AND
                ((@EventScheduleID IS NULL) OR (E.EventScheduleID = @EventScheduleID))

2. Using Temporary Views, In this query. I try to filter down all the required eventID based on the criteria and insert it into a temporary view(tblEvents). Once i get the filtered result then I joined it with EventResults table and It takes only 16secs considering the number of records.

WITH tblEvents(EventID) AS
(
            SELECT
                E.EventID
            FROM
                Events E
            INNER JOIN
                Venues V
            ON
                E.VenueID = V.VenueID
            INNER JOIN
                Regions R
            ON
                R.RegionID = V.RegionID
            WHERE
                (DATEDIFF(d, E.EventDate, @DateFrom) = 0)
            AND
                ((@StateID IS NULL) OR (R.StateID = @StateID))
            AND
                ((@RegionID IS NULL) OR (R.RegionID = @RegionID))
            AND
                ((@VenueID IS NULL) OR (V.VenueID = @VenueID))
            AND
                ((@EventScheduleID IS NULL) OR (E.EventScheduleID = @EventScheduleID))
)

            SELECT
                COUNT(*) As Total
            FROM
                tblEvents te
            INNER JOIN
                EventResults er
            ON
                te.EventID = er.EventID
            GROUP BY
                er.MemberID
            HAVING
                COUNT(*) >= @MinGame
            AND
                COUNT(*) <= @MaxGame

I was having a problem this morning in using a ORDER BY in UNION Clause. The solution is to use keyword of “TOP 100 PERCENT” on the related ORDER BY SELECT query.

CREATE PROCEDURE MyAPLRank
@MemberID int
AS
      SET NOCOUNT ON;
BEGIN
      SELECT r.ResultName,r.PlayerOfTheYearID,r.MemberID,r.RankID,r.memberCode,
            r.FirstName,r.Surname,r.State,r.NumEvents,r.Points,r.AvgPoints,
                  r.MeetsRequirements,r.Valid,r.IsLeaderBoard,r.RowNum
      FROM
      (
            SELECT
                  'Player of The Year' As ResultName,PlayerOfTheYearID,MemberID,
                  RankID,memberCode,FirstName,Surname,State,NumEvents,Points,
                  AvgPoints,MeetsRequirements,Valid,'0' as IsLeaderBoard,1 as RowNum
            FROM
                  PlayerOfTheYearQuery
            WHERE
                  PlayerOfTheYearID
                  =
                  (
                        SELECT
                              TOP 1 poy.PlayerOfTheYearID
                        FROM
                              PlayerOfTheYear poy
                        INNER JOIN
                              Schedule s
                        ON
                              (s.ScheduleID = poy.ScheduleID)
                        WHERE
                              (s.MonthID IS NULL AND s.WeekID IS NULL AND s.QuarterID IS NULL)
                        ORDER BY
                              s.ScheduleID
                        DESC
                  ) AND MemberID = @MemberID AND MeetsRequirements = 1
            UNION
            SELECT
                  'Player of The Month' As ResultName,PlayerOfTheYearID,MemberID,
                  RankID,memberCode,FirstName,Surname,State,NumEvents,Points,
                  AvgPoints,MeetsRequirements,Valid,'0' as IsLeaderBoard,2 as RowNum
            FROM
                  PlayerOfTheYearQuery
            WHERE
                  PlayerOfTheYearID
                  =
                  (
                        SELECT
                              TOP 1 poy.PlayerOfTheYearID
                        FROM
                              PlayerOfTheYear poy
                        INNER JOIN
                              Schedule s
                        ON
                              (s.ScheduleID = poy.ScheduleID)
                        WHERE
                              (s.MonthID IS NOT NULL AND s.WeekID IS NULL)
                        ORDER BY
                              s.ScheduleID
                        DESC
                  ) AND MemberID = @MemberID AND MeetsRequirements = 1
            UNION
            SELECT
                  'Player of The Week' As ResultName,PlayerOfTheYearID,MemberID,
                  RankID,memberCode,FirstName,Surname,State,NumEvents,Points,
                  AvgPoints,MeetsRequirements,Valid,'0' as IsLeaderBoard,3 as RowNum
            FROM
                  PlayerOfTheYearQuery
            WHERE
                  PlayerOfTheYearID
                  =
                  (
                        SELECT
                              TOP 1 poy.PlayerOfTheYearID
                        FROM
                              PlayerOfTheYear poy
                        INNER JOIN
                              Schedule s
                        ON
                              (s.ScheduleID = poy.ScheduleID)
                        WHERE
                              (s.MonthID IS NOT NULL AND s.WeekID IS NOT NULL)
                        ORDER BY
                              s.ScheduleID
                        DESC
                  ) AND MemberID = @MemberID  AND MeetsRequirements = 1
      UNION

                SELECT TOP 100 PERCENT
                         v.venue As ResultName,l.leaderboardID as PlayerOfTheYearID,lq.MemberID,
                         lq.RankID,lq.membercode,lq.name as FirstName,lq.name as Surname,s.State,
                        lq.NumEvents,lq.Points,lq.points as AvgPoints,lq.valid as MeetsRequirements,
                        lq.Valid, '1' as IsLeaderBoard,4 as RowNum
                FROM
                        leaderboardquery lq
                INNER JOIN
                        leaderboard l
                ON
                        (
                                l.leaderboardid = lq.leaderboardid
                            AND
                                l.venueid IS NOT NULL
                            AND
                                lq.MemberID = @MemberID
                            AND
                                l.scheduleID =
                                        (
                                         SELECT    ScheduleID
                                         FROM    Schedule
                                         WHERE    GETDATE() BETWEEN FromDate AND ToDate
                                                AND MonthID IS NOT NULL
                                        )
                        )
                INNER JOIN
                        venues v
                ON
                        (v.venueID = l.venueid)
                INNER JOIN
                        states s
                ON
                        l.StateID = s.StateID
                ORDER BY
                    l.ScheduleID
                DESC
      ) r
ORDER BY
      RowNum
END

This is the function in SQL server to get the last day for each month until the day that you define in a variable which is in this case is “@genDate”.

I used this function to iterate some stored procedure every last day in the month

DECLARE @genDate datetime
SET @genDate = '1/31/2006'

WHILE @genDate <= getdate()
	BEGIN
		PRINT @genDate
		SET @genDate = DateAdd(month,1,@genDate)

		--this is used to get the last day for each month
		SET @genDate = DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @genDate)+1, 0))
	END

NOTE: “-1” in this statement means that it’s minus one day from the beginning of the month which gives you the last day for each month
SET @genDate = DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @genDate)+1, 0))