Different Type of Joins

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

1 INNER JOIN

The INNER JOIN keyword return rows when there is at least one match in both tables.

2 OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN
The LEFT OUTER JOIN keyword returns all rows from the left table (table1), even if there are no matches in the right table (table2). If there are no columns

matching in the right table, it returns NULL values.

RIGHT OUTER JOIN
The RIGHT OUTER JOIN keyword returns all the rows from the right table (table2), even if there are no matches in the left table (table1). If there are no columns matching in the left table, it returns NULL values.

FULL OUTER JOIN
This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

3 CROSS JOIN

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The following example shows a Transact-SQL cross join.

SELECT p.ID, p.value, t.ID, t.value

FROM table1 p

CROSS JOIN table2 t

ORDER BY p.ID;

Cross-join is the same thing as performing a join where the condition is always true.

For Example

SELECT t1.*, t2.*
FROM t1
INNER JOIN t2
ON ( 1 = 1 )

“SQL Server 2005 Express tools" failed while installing SQL Server 2008 Express Edition.

Today morning when I am going to install SQL Server 2008 R2 Express edition, I am getting below error.
Rule "SQL Server 2005 Express tools" failed.

The SQL Server 2005 Express Tools are installed. To continue, remove the SQL Server 2005 Express Tools.

Solution:

I am just trying to solve this issue By following the steps described. I want to keep SQL Server 2005 and SQL Server 2008 both on my PC. I am going to explain one trick through which you can keep and work on both SQL server 2005 and 2008 and installed on the machine at the same time. The trick is simple, just change a registry directory name of the SQL server 2005 studio with registry editor. Follow the below step.

1. Go to Windows > Run, enter “regedit” and click ok.
2. Browse to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90.
3. Rename “90″ to something else, like “old”

After the installation, make sure you have to change it back.

You can now rerun the process in the SQL Server 2008 Express installation wizard. After installation completed, you will notice that the old SQL Server 2005 doesn’t run when you try to launch it. No worry, to fix it you can change the newly created directory “90″ to “70″. Then change “old” that you changed from “90″ before back to “90″. It should now work for both SQL Server 2005 and 2008 Studio Express.

Enable CLR in SQL Server

The common language runtime (CLR) integration feature is off by default, and must be enabled in order to use objects that are implemented using CLR integration. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

You can disable CLR integration by setting the clr enabled option to 0. When you disable CLR integration, SQL Server stops executing all CLR routines and unloads all application domains.

Note: To enable CLR integration, you must have ALTER SETTINGS server level permission, which is implicitly held by members of the sysadmin and serveradmin fixed server roles.

What is NULL?

Most of them always confused that NULL is false, absent of value. There are few properties of the NULL are universal but the knowledge of the same is not universal.

Let us run following very simple query. Run following T-SQL script.

SELECT SUM(data)FROM (SELECT NULL AS data) t

It will return following error:

Msg 8117, Level 16, State 1, Line 1
Operand data type NULL is invalid for sum operator.

Now error itself explain, here NULL is not the type of Integer by default. We must have to convert it in Integer by casting.

SELECT SUM(data)FROM (SELECT CAST(NULL AS INT) AS data) t

Now when we run this above query we will get NULL value as below.

Most the the SQL Developer convert NULL to 0 by using ISNULL function. But It will produce the different result.

SELECT SUM(data)FROM (SELECT ISNULL(NULL,0) AS data) t

Output would be as below.




Here I just want to explain that NULL can be converted in any type like int, varchar, decimal etc. and perform your action according the datatype query.

CAST(NULL AS INT)

CAST(NULL AS VARCHAR)

CAST(NULL AS DECIMAL)

One more interesting point regarding NULL is that when you add or concatenate anything with NULL, output would be NULL.

SELECT 5 + NULL

SELECT 5 + CAST(NULL AS INT)

OR

SELECT 'Anjum' + NULL

SELECT 'Anjum' + CAST(NULL AS varchar)

Output would be NULL.

I am confident that after reading the post you will have no confusion regarding NULL in future.

Add Computed Column in Table

A computed column is computed from an expression that can use other columns in the same table. The expression can be a non computed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a sub-query. See the below example for details.

CREATE TABLE Authors
(
AuthorId int IDENTITY(1,1) NOT NULL,
FirstName nvarchar(100),
LastName nvarchar(100),
FullName AS (FirstName + SPACE(1) + LastName) -- computed column
)

The above table sample has the FullName computed column defined as the concatenation of two other column values in the same table.
This is a simple expression sample. We define the computed column by "AS" clause and see that we do not define a column type since the type is defined implicitly by the calculation expression.

INSERT INTO Authors (FirstName, LastName) VALUES (N'Roger', N'Wolter')
INSERT INTO Authors (FirstName, LastName) VALUES (N'Dejan', N'Sarka')
SELECT * FROM Authors

We can add computed column after creating the table. Lets take a example, in the same table, we want to add one more column with CASE expression to define the calculated column value.

ALTER TABLE Authors ADD FullName2 AS (CASE WHEN AuthorId <>

Now insert some data into the sql table, and see the results.

INSERT INTO Authors (FirstName, LastName) VALUES (N'Itzik', N'Ben-Gan')
SELECT * FROM Authors

Note: You can add the same functionality with the temporary table also. i.e. you can add computed column in temporary table at the time of creation (with Create table #tmp (T-SQL command) ) or at the time of alteration/modification.

Merge Statement in SQL Server 2008

SQL Server 2008 introduces the MERGE statement which will allow users to perform insert, update and delete operations in a single statement. In the earlier versions of SQL Server to achieve the same functionality the database developer or database administrator needed to write separate statements to perform the insert, update or delete of data in one table based on certain conditions in another table.

Using MERGE statement which Microsoft has introduced with SQL Server 2008 database developers or DBA’s can achieve the same functionality by writing very less TSQL code. The code written using this logic will also have performance issue due to the complexity of joins etc. Even though this feature is introduced very late in SQL Server Product, going forward I am very sure that it will be adopted very quickly in many data warehouse projects.

One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

How does MERGE Statement Internally Works
The MERGE statement internally works as an individual insert, update and delete statement within a single Merge statement. You need to specify the SOURCE and the TARGET table or query which should be joined together. Within the MERGE statement you also need to specify the type of the data modification that needs to be performed when the records between the source and target are matched and what actions needs to be performed when they are not matched. With the introduction of MERGE statement the complex TSQL codes which was used earlier to do checks for the existence or inexistence of data within the data warehouse can be replaced with single Merge statement. The use of Merge statement will also improve the query performance.
Below are the three different matched clauses in MERGE:

• WHEN MATCHED THEN
• Rows that meet the criteria
• WHEN [TARGET] NOT MATCHED THEN
• Rows that do not match with another row in the target table
• WHEN SOURCE NOT MATCHED THEN
• Rows that do not match with another row in the source table

Syntax of MERGE statement is as following:
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( ) ] [ [ AS ] table_alias]
USING
ON
[ WHEN MATCHED [ AND ]
THEN ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND ]
THEN ]
[ WHEN NOT MATCHED BY SOURCE [ AND ]
THEN ]
[ ]
[ OPTION ( [ ,...n ] ) ]
;

Example

CREATE TABLE dbo.tbl_Source (id INT, name NVARCHAR(100), qty INT);

CREATE TABLE dbo.tbl_Target (id INT, name NVARCHAR(100), qty INT);

--Synchronize source data with target

MERGE INTO dbo.tbl_Target AS t

USING dbo.tbl_Source AS s

ON t.id = s.id

WHEN MATCHED AND (t.name != s.name OR t.qty!= s.qty) THEN

--Row exists and data is different

UPDATE SET t.name = s.name, t.qty = s.qty

WHEN NOT MATCHED THEN

--Row exists in source but not in target

INSERT INTO (id, name, qty)

VALUES (s.id, s.name, s.qty)

WHEN SOURCE NOT MATCHED THEN

--Row exists in target but not in source, Then Delete from Target

DELETE ;

MERGE statement is very handy improvement for T-SQL developers who have to update database tables with complicated logic. MERGE statement also improves the performance of database as it passes through data only once.

Output and Output Into clause in SQL Server 2005

SQL Server 2005 introduced new OUTPUT and OUTPUT INTO Clause for showing the result after the Insert/Update/Delete statement in single go. Earlier (SQL Server 2000), we are using two T-SQL statement for getting result after Insert/Update/Delete statement.
For example we have a table “tbl_Employee” and it has three columns (ID, Name, Salary).
Create table tbl_Employee
(
EmpID Int,
EmpName varchar(50),
Salary Decimal(10,2)
)
Example
In SQL Server 2000, first we execute Insert/Update/Delete statement, and then we execute the select statement to see the result.
Example with Insert Statement.
Insert Into tbl_Employee (EmpID, EmpName, Salary)
Values (1, ‘AAA’, 5000)
Go
Select * from tbl_Employee
Go
The same thing we do with UPDATE and DELETE statement in SQL Server 2000. First we update or Delete the record and then see the output (result).
But in SQL Server 2005, there is an OUTPUT clause for providing output (result) after performing Insert/Update/Delete action in single step (in single query).
OUTPUT Clause with INSERT Statement
Insert into tbl_Employee (EmpID, EmpName, Salary)
OUTPUT Inserted.*
Values (1, 'AAA', 3000)
Go
OUTPUT INTO Clause with INSERT Statement
If you want to insert the output (result) in different table, use OUTPUT INTO clause with other table name. We have another table named tbl_EmployeeLog with same column name and data type of tbl_Employee table.

Insert into tbl_Employee (EmpID, EmpName, Salary)
OUTPUT Inserted.* INTO tbl_EmployeeLog
Values (2, 'BBB', 3000)
Go

If you see the record of tbl_EmployeeLog table, you will get following record.







But don’t start to think that you would use OUTPUT INTO clause for maintaining log. This is good option for inserting output in another table, but for maintaining the log, TRIGGER is the best option. Always use TRIGGER wherever you need to maintain the log of any (Insert/Update/Delete) action.