SQL Joins

Very good article regarding basics of JOINS...

SQL JOINS

OUTPUT Clause in SQL SERVER 2008 - Update

 --Decalring temp table
  DECLARE @OUTPUTTABLE Table
  (
ID INT,
NAME VARCHAR(100),
OLDNAME VARCHAR(100),
PRICE Decimal(18,2),
OLDPRICE Decimal(18,2) 
  ); 
  
  UPDATE  dbo.SourceTable
  SET Name='TestUpdate',
 Price=64
  OUTPUT
 inserted.ID,
 inserted.Name,
 deleted.Name,
 inserted.Price,
 deleted.Price
  INTO @OUTPUTTABLE(ID,NAME,OLDNAME,PRICE,OLDPRICE)
  WHERE ID=3
  
  
  SELECT *  FROM [EVSSQL].[dbo].[SourceTable]
  
  SELECT *  FROM @OUTPUTTABLE

OUTPUT Clause in SQL SERVER 2008 - INSERT

In SQL Server 2008, you can add an OUTPUT clause to your data manipulation language (DML) statements. The clause returns a copy of the data that you have inserted into or deleted from your tables. This contains the copy of data you are inserting or updating or deleting.


Here is the example code of OUTPUT clause.


CREATE TABLE [dbo].[SourceTable1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Price] [money] NULL
)


 Here is the output clause code


 --Decalring temp table
  DECLARE @OUTPUTTABLE Table
  (
ID INT,
NAME VARCHAR(100),
PRICE decimal(18,2)
  );  
  
   INSERT INTO [dbo].[SourceTable]
OUTPUT 
INSERTED.*
INTO @OUTPUTTABLE
  VALUES('Test','25')  
  
  
  SELECT *  FROM [EVSSQL].[dbo].[SourceTable]
  
  SELECT *  FROM @OUTPUTTABLE


You can also define the column names in the query.


 --Decalring temp table
  DECLARE @OUTPUTTABLE Table
  (
ID INT,
NAME VARCHAR(100),
PRICE decimal(18,2)
  ); 
  
  
  INSERT INTO [dbo].[SourceTable]
OUTPUT
INSERTED.Name,
inserted.Price
INTO @OUTPUTTABLE(NAME,Price)
  VALUES('Test','25')  
  
  
  SELECT *  FROM [EVSSQL].[dbo].[SourceTable]
  
  SELECT *  FROM @OUTPUTTABLE

APPLY OPERATOR IN T-SQL

APPLY operator is used in the FROM clause of a query. It allows you to call a Table valued function for each row of your outer TABLE. We can pass outer table's columns as function arguments.

It has two options:

1. CROSS APPLY, and
2. OUTER APPLY

CROSS APPLY will not return the outer tables row if function table has no row corresponding to it, whereas OUTER APPLY returns NULL values instead of function columns.

Let's take an example.

First I will make a table valued function and then I will try to show you how to apply the "APPLY" operator.

USE [AdventureWorks]
GO
/****** Object:  UserDefinedFunction [dbo].[fnGetDepartmentId]    Script Date: 07/15/2012 21:54:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnGetDepartmentId] (@empid int)
RETURNS TABLE
AS
RETURN
(
--Outer Query
SELECT D.DepartmentID,D.Name 
FROM [AdventureWorks].[HumanResources].[EmployeeDepartmentHistory] EDH
INNER JOIN [AdventureWorks].[HumanResources].[Department] D
ON EDH.DepartmentID=D.DepartmentID
WHERE Employeeid=@empid
) 


This function will return the department name and its ID according to the each employee ID.


Now I will use the APPLY operator to get my required results.


SELECT E.EmployeeID,E.Title,DepartmentID,Name AS DepartmentName

FROM [AdventureWorks].[HumanResources].[Employee] E
CROSS APPLY
dbo.fnGetDepartmentId(E.EmployeeID)

Adventureworks Sample Database ER Diagrams

Adventureworks is the sample database from the Microsoft for the SQL Server. It is very helpful to understand any database if you have its ER diagram. So here is the all the ER diagrams I searched from internet.
ER diagrams

Restoring SQL server database ".bak" file

Right click on the database tab –> All Tasks –> Restore Database –> From device –> Select Devices –> Add your .bak file. Click OK. 

Now click the options tab. Here you will see the logical file name of your database. At its right side there will be a physical path of this file. Make sure this path is redirecting to the location where your database files exist, normally default location is "C:\Program Files\Microsoft SQL Server\MSSQL\Data" at the end of this path concatenate your file name. After concatenation your path should be something like this

C:\Program Files\Microsoft SQL Server\MSSQL\Data\test.mdf

C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_Log.ldf.

Now click ok. Large databse normally takes lot of time.

Relationship types

Association

 Association is the relationship in which each object has its own life cycle but have no ownership. It is a weak relationship.

For example Teacher and Student relationship. A teacher can have more than one teacher and one student can have more than one teachers. So there is no ownership.

Aggregation

It is a stronger relationship. It is a specialization of Association. Objects have their own life cycle but there is ownership. Let's take example of Teacher and Department. A department has many teachers but a teacher can have only one department. If we delete the department then teacher would exist.