Creating a Heterogeneous Join between SQL Server and Active Directory

All employees at the Fabrikam corporation are reviewed every six months. Review ratings are stored in the Human Resource database in SQL Server. To create a view of this data, Joe Worden, the enterprise administrator, must, first, create an employee performance review table.

CREATE TABLE EMP_REVIEW
(
userName varChar(40),
reviewDate datetime,
rating decimal 
)

Joe can then insert a few records.

INSERT EMP_REVIEW VALUES('Julie Adam', '2/15/1999', 4 )
INSERT EMP_REVIEW VALUES('Julie Bankert', '7/15/1999', 5 )
INSERT EMP_REVIEW VALUES('Chris Gray', '2/15/1999', 3 )
INSERT EMP_REVIEW VALUES('Chris Gray', '7/15/1999', 4 )

Now Joe can join the Active Directory user objects to the SQL Server table.

SELECT ADsPath, userName, title, ReviewDate, Rating 
FROM EMP_REVIEW, viewADUsers
WHERE userName = Name

This command gets the result from both SQL Server and Active Directory. AdsPath and Title are from Active Directory, whereas username, ReviewDate, and Rating are from the SQL table. He can even create another view for this join.

CREATE VIEW reviewReport
SELECT ADsPath, userName, title ReviewDate, Rating 
FROM EMP_REVIEW, viewADUsers
WHERE userName = Name