SQL Joins

--INNER JOIN: Returns all rows when there is at least one match in BOTH tables

--LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

--RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

--FULL JOIN: Return all rows when there is a match in ONE of the tables

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       INNER JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       LEFT JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       RIGHT JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       FULL JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       LEFT JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

WHERE

              A.ColumnA IS NULL

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       RIGHT JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

WHERE

              A.ColumnA IS NULL

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       LEFT JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

WHERE

              B.ColumnB IS NULL

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       RIGHT JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

WHERE

              B.ColumnB IS NULL

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       FULL OUTER JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       FULL OUTER JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

WHERE

               A.ColumnA IS NULL

OR

              B.ColumnB IS NULL

Setting Up Resource Governor

-- CREATE RESOURCE POOL FOR SQL AGENT JOBS

CREATE RESOURCE POOL

       SQLAgentJobsPool

WITH

(

       MIN_CPU_PERCENT=0,

       MAX_CPU_PERCENT=85,

       MIN_MEMORY_PERCENT=0,

       MAX_MEMORY_PERCENT=85

)

GO

-- CREATE WORKLOAD GROUP for SQL AGENT JOBS

CREATE WORKLOAD GROUP

       SQLAgentJobsGroup

USING

       SQLAgentJobsPool;

GO

-- CREATE UDF TO ASSIGN WORK LOAD GROUP

CREATE FUNCTION dbo.UDFAssign_RG_WorkLoadGroup()

RETURNS SYSNAME

WITH SCHEMABINDING

AS

BEGIN

DECLARE @WorkloadGroup AS SYSNAME

IF(SUSER_NAME() = 'Put SQLSERVERAgent Service Account Here')

       SET @WorkloadGroup = 'SQLAgentJobsGroup'

ELSE

       SET @WorkloadGroup = 'default'

RETURN @WorkloadGroup

END

GO

-- ALTER RESOURCE GOVERNOR TO USE WORK LOAD GROUP UDF

USE [master]

GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[UDFAssign_RG_WorkLoadGroup]);

GO

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO