Microsoft SQL Server 2012 New Features

1. AlwaysOn Availability Groups -- This feature takes database mirroring to a whole new level. With AlwaysOn, users will be able to fail over multiple databases in groups instead of individually. Also, secondary copies will be readable, and can be used for database backups. The big win is that your DR environment no longer needs to sit idle.

Below are a few feature sets of AAG:

·         Numerous secondary replica copies: up to four secondary db copies.

·         It allows control over the automatic failover process using flexible failover policy for each availability group.

·         Provides secure, high performing transport to replicas via Encryption and compression.

·         Other availability modes: Asynchronous-commit mode and Synchronous-commit mode

·         Several failover modes: automatic failover, planned manual failover, and forced manual failover. For more information

·         Through better source utilization of spare hardware permits Active secondary which increase IT efficiency and reduce cost.

·         Provides fast application-failover via Availability group listeners.

·         Automatic page healing for protection against page corruption.

·         Forcing WSFC quorum

2. Always-On SQL Failover Cluster

·         Failover clusters across subnets
A SQL Server multisubnet failover cluster is a design where all failover cluster node is connected to a different networks or different set of networks. These networks can be in the same physical location or in phsicallydispersed . Clustering across global sites is sometimes referred to as Stretch-clusters. As there is no need for shared storage that all the nodes can access, data should be replicated among the data storage on the multiple networks. With data replication, there is further than one copy of the data available. Therefore, a multi-subnet failover cluster offers a disaster recovery solution in addition to HA.

·         Flexible policy for cluster health detection
In a SQL Server failover-cluster instance, only one node can own the cluster which is sometimes known as a single copy cluster. The client demands are served through this primary node for that failover cluster instance. In the case of a hardware failure, the group ownership is moved to another node in the failover cluster node. In this case the process is called failovering over a cluster.

3. Windows Server Core Support -- If you don't know what Windows Server Core is, you may want to come up to speed before Windows 8 (MS is making a push back to the command line for server products). Core is the GUI-less version of Windows that uses DOS and PowerShell for user interaction. It has a much lower footprint (50% less memory and disk space utilization), requires fewer patches, and is more secure than the full install. Starting with SQL 2012, it is supported for SQL Server.

4. Columnstore Indexes -- This a cool new feature that is completely unique to SQL Server. They are special type of read-only index designed to be use with Data Warehouse queries. Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries.

5. User-Defined Server Roles -- DBAs have always had the ability to create custom database role, but never server wide. For example, if the DBA wanted to give a development team read/write access to every database on a shared server, traditionally the only ways to do it were either manually, or using undocumented procedures. Neither of which were good solutions. Now, the DBA can create a role, which has read/write access on every DB on the server, or any other custom server wide role.

6. Enhanced Auditing Features -- Audit is now available in all editions of SQL Server. Additionally, users can define custom audit specifications to write custom events into the audit log. New filtering features give greater flexibility in choosing which events to write to the log.

7. BI Semantic Model -- This is replacing the Analysis Services Unified Dimensional Model (or cubes most people referred to them). It's a hybrid model that allows one data model will support all BI experiences in SQL Server. Additionally, this will allow for some really neat text infographics

8. Sequence Objects -- For those folks who have worked with Oracle, this has been a long requested feature. A sequence is just an object that is a counter -- a good example of it's use would be to increment values in a table, based a trigger. SQL has always had similar functionality with identity columns, but now this is a discrete object.

9. Enhanced PowerShell Support -- Windows and SQL Server admins should definitely start brushing up on their PowerShell scripting skills. Microsoft is driving a lot of development effort into instrumenting all of their server-based products with PowerShell. SQL 2008 gave DBAs some exposure to it, but there are many more in cmdlets in SQL 2012.

10. Distributed Replay -- Once again this is answer to a feature that Oracle released (Real Application Testing). However, and in my opinion where the real value proposition of SQL Server is, in Oracle it is a (very expensive) cost option to Enterprise Edition. With SQL, when you buy your licenses for Enterprise Edition, you get everything. Distributed replay allows you to capture a workload on a production server, and replay it on another machine. This way changes in underlying schemas, support packs, or hardware changes can be tested under production conditions.

11. PowerView -- You may have heard of this under the name "Project Crescent" it is a fairly powerful self-service BI toolkit that allows users to create mash ups of BI reports from all over the Enterprise.

12. SQL Azure Enhancements -- These don't really go directly with the release of SQL 2012, but Microsoft is making some key enhancements to SQL Azure. Reporting Services for Azure will be available, along with backup to the Windows Azure data store, which is a huge enhancement. The maximum size of an Azure database is now up to 150G. Also Azure data sync allows a better hybrid model of cloud and on-premise solutions

13. Big Data Support -- I saved the biggest for last, introduced at the PASS (Professional Association for SQL Server) conference last year, Microsoft announced a partnership with Hadoop provider Cloudera. One part of this involves MS releasing a ODBC driver for SQL Server that will run on a Linux platform. Additionally, Microsoft is building connectors for Hadoop, which is an extremely popular NoSQL platform. With this announcement, Microsoft has made a clear move into this very rapidly growing space.

SQL 2012 is a big step forward for Microsoft -- the company is positioning itself to be a leader in availability and in the growing area of big data. As a database professional, I look forward to using SQL 2012 to bring new solutions to my clients.

14. Indirect checkpoints
This new feature offers a database-specific substitute to automatic checkpoints, which are shaped by a server property. An indirect checkpoint implements a new checkpointing formula for the DB Engine. This offers a more accurate assurance of database recovery time in DR event or a failover than is provided by automatic-checkpoints. To guarantee that database recovery does not top allowable downtime for a given db, you can stipulate the maximum acceptable downtime for that database.

T-SQL Enhancements

SQL Server 2012 brings many new features to the T-SQL language. Some were added merely to ease the transition to SQL Server from other languages and platforms. Others were added to provide very powerful, new ways to solve cumbersome problems. A few examples:

·         TRY_CONVERT()
I’ve been bitten many times by bad data in a column using the wrong data type. ISNUMERIC(), for example, is not always reliable; values that return true are not convertible to all numeric types. Today I might try to perform something like this:

      SELECT CONVERT(INT, column) … WHERE ISNUMERIC(column) = 1;

However, this will fail for values like ‘e’ which are considered numeric but cannot be converted to an integer. TRY_CONVERT() allows you to ignore invalid conversions and return NULL for those values instead of returning an error for the entire query.

·         OFFSET / FETCH
Many web applications use paging to show 10 or 50 rows per page and allow the user to scroll through each page of results rather than download the entire set. MySQL has had the non-standard LIMIT clause for some time now, and SQL Server users have longed for similarly simple syntax. ORDER BY … OFFSET / FETCH syntax, which is standards-compliant, has been added to the SQL Server 2012. While it doesn’t provide significant performance improvements over the tedious CTE solutions we use today, it certainly makes the code easier to write, as I demonstrated in a blog post last November.

·         FORMAT()
Using CLR under the covers, we will now finally have relative parity with the .format() function we are used to using in .NET languages like C#. This means no more memorizing cryptic style codes like 101 and 103 for converting datetime values to localized presentation formats, or using all kinds of messy string manipulation to present numeric values with dollar signs and thousands separators. In August I wrote a lengthy blog post about FORMAT() with many examples.

·         Window Function Enhancements
If you’ve heard of Itzik Ben-Gan, you’re almost certainly aware of what a big fan of window functions he is. I can tell you from first-hand experience that he is absolutely ecstatic about SQL Server 2012’s addition of window offset and distribution functions, as well as enhanced windowed aggregates (including window framing). You can see an intro to these features in his recent article on sqlmag.com, and watch for future articles, as well as an upcoming book devoted entirely to the topic.

These are just a few of the T-SQL enhancements in SQL Server 2012; for a more complete list, see the Programmability Enhancements page in Books Online.

ROLLBACK functionality

Note ROLLBACK functionality
Although the ROLLBACK statement returns the data to its prior state, some functionalities,
such as seed values for identity columns, are not reset.

SET IDENTITY_INSERT

Allows explicit values to be inserted into the identity column of a table.

Syntax:
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

Example:
CREATE TABLE dbo.Tool(
ID INT IDENTITY NOT NULL PRIMARY KEY,
Name VARCHAR(40) NOT NULL
)
GO
-- Inserting values into products table.
INSERT INTO dbo.Tool(Name) VALUES ('Screwdriver')
INSERT INTO dbo.Tool(Name) VALUES ('Hammer')
INSERT INTO dbo.Tool(Name) VALUES ('Saw')
INSERT INTO dbo.Tool(Name) VALUES ('Shovel')
GO

-- Create a gap in the identity values.
DELETE dbo.Tool WHERE Name = 'Saw'
GO

SELECT * FROM dbo.Tool
GO

-- Try to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON
GO

-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO

SELECT * FROM dbo.Tool
GO

--Try to insert name with out ID
--should return a warning.
INSERT INTO dbo.Tool(Name) VALUES ('Shovel')

-- SET IDENTITY_INSERT to OFF
SET IDENTITY_INSERT dbo.Tool OFF

--Try to insert name with out ID
INSERT INTO dbo.Tool(Name) VALUES ('Shovel')

SELECT * FROM dbo.Tool
GO

--Insert with 10 as id
--should return a warning.
INSERT INTO dbo.Tool (ID, Name) VALUES (10, 'Garden shovel')

--If you need to have gap between id then set ON
SET IDENTITY_INSERT dbo.Tool ON

--Insert with 10 as id
INSERT INTO dbo.Tool (ID, Name) VALUES (10, 'Garden shovel')

SELECT * FROM dbo.Tool
GO
--should return a warning.
INSERT INTO dbo.Tool (ID, Name) VALUES ('New Garden shovel')

SET IDENTITY_INSERT dbo.Tool OFF

--Insert and continue identity value from 10
INSERT INTO dbo.Tool (Name) VALUES ('New Garden shovel')

-- Drop products table.
DROP TABLE dbo.Tool
GO

SQL SERVER – LEN Vs DATALENGTH of NULL

Simple but interesting – In recent survey I found that many developers making this generic mistake. I have seen following code in periodic code review. (The code below is not actual code, it is simple sample code)
DECLARE @MyVar VARCHAR(10)
SET @MyVar = NULL
IF (LEN(@MyVar) = 0)

…
I decided to send following code to them. After running the following sample code it was clear that LEN of NULL values is not 0 (Zero) but it is NULL. Similarly, the result for DATALENGTH function is the same. DATALENGTH of NULL is NULL.

Sample Test Version:
DECLARE @MyVar VARCHAR(10)
SET @MyVar = NULL
IF (LEN(@MyVar) = 0)
PRINT 'LEN of NULL is 0'
ELSE
PRINT 'LEN of NULL is NULL'
Result Set:
LEN of NULL is NULL

String Functions

1. CHARINDEX string function takes 2 arguments. 1st argument specifies the character whose index is to be retrieved and 2nd argument takes as a string from which character index is carried out.

Example:
Select CHARINDEX ('S','MICROSOFT SQL SERVER 2000')
Result: 6

2. LEFT string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns first characters of specified length starting from the left side of the string entered as 1st argument.

Example:
Select LEFT ('MICROSOFT SQL SERVER 2000',4)
Result: MICR

3. RIGHT string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns last characters of specified length starting from the right side of the string entered as 1st argument.

Example:
Select RIGHT ('MICROSOFT SQL SERVER 2000',4)
Result: 2000

4. LEN string function takes 1 argument as string value and returns the length of entered string.

Example:
Select LEN ('MICROSOFT SQL SERVER 2000')
Result: 25

5. REPLACE string function takes 3 arguments.
1st argument as string value.
2nd argument is a part of string entered as 1st argument which is to be replaced.
3rd argument as a new string value that is to be placed at the place of 2nd argument.

Example:
Select REPLACE ('MICROSOFT SQL SERVER 2000','MICROSOFT','MS')
Result: MS SQL SERVER 2000

6. STUFF string function takes 4 arguments. It is used to replace specified length of characters with provided pattern.
1st argument as string value.
2nd argument as integer value specifying the starting point of characters to be replaced.
3rd arguments as integer value specifying the length of characters.
4th argument as string value specifying the new pattern of characters.

Example:
Select STUFF ('MICROSOFT SQL SERVER 2000', 11, 3,'S.Q.L.')
Result: MICROSFT S.Q.L. SERVER 2000

7. SUBSTRING string function returns the sub string of specified length starting from the entered start position. It takes 3 arguments.
1st argument as string value.
2nd argument as integer specifying the start position.
3rd argument as integer specifying the length

Example:
Select SUBSTRING ('MICROSOFT SQL SERVER 2000', 11, 3)
Result: SQL

8. LOWER string function returns the lower case string whether the entered string has upper case letters. It takes 1 argument as string value.

Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING’)
Result: microsoft asp .net web hosting

9. UPPER string function returns the upper case string whether the entered string has lower case letters. It takes 1 argument as string value.

Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING with SQL Database’)
Result: MICROSOFT ASP .NET WEB HOSTING WITH SQL DATABASE

10. REVERSE string function returns the string in reverse order. It takes 1 argument as string value.

Example:
select REVERSE(‘ASP.NET’)
Result: TEN.PSA

11. LTRIM function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.

Example:
select LTRIM (‘ ASP ’)
Result: ASP-----
blanks at the right side not removed.

12. RTRIM function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.

Example:
select RTRIM (‘ ASP ’)
Result: -----ASP
blanks at the left side not removed.

13. PATINDEX function returns the position of first occurrence of specified pattern in the provided string. It takes 2 arguments.
1st argument as string value specifying the pattern to match
2nd argument as string value specifying the string to compare.

Example:
select PATINDEX('%RO%','MICROSOFT')
Results: 4

14. STR function returns character data converted from numeric data. It takes 3 arguments.
1st argument as float data
2nd argument as integer value specifying the length of the string including decimal that is to be retrieved.
3rd argument as integer specifying the number of places to the right of the decimal point.

Example:
select STR(140.15, 6, 1)
Result: 140.2

15. ASCII function returns the ASCII code value from the leftmost character specified character expression. It takes 1 argument as string/character expression.

Example:
select ASCII('A')
Result: 65

Reference:

http://sqlservercodebook.blogspot.com/2008/03/sql-string-functions-tutorial.html

System Functions - CAST and CONVERT

The CAST and CONVERT functions are used to convert an
expression from one data type to another.

Date and Time Functions

SYSDATETIME, SYSDATETIMEOFFSET, and SYSUTCDATETIME functions all retrieve the current system time from the server.

GETDATE and GETUTCDATE date functions use the same format of including parentheses after the function name, the CURRENT_TIMESTAMP function does not. The CURRENT_TIMESTAMP and GETDATE functions produce the same results.

DATEPART function returns the numeric value of the part specified. For example, “1” is returned
for January.

DATEADD
SELECT DATEADD(YY, 2, GETDATE()) as NewDate, 'Add 2 years from current date' as Description

DATEDIFF syntax follows: DATEDIFF (datepart, startdate, enddate)
SELECT DATEDIFF(YEAR, GETDATE(), '1/1/2001')
SELECT DATEDIFF(DD, GETDATE(), '1/1/2009')

CROSS APPLY versus OUTER APPLY

OUTER APPLY returns all rows from the outer table along with the results returned by
the function when rows match.
while CROSS APPLY returns only the rows from the
outer table where a match exists within the function results.

Here we can you match 2 table or we match by using function

Example 1:
CREATE TABLE Account
(
AccountID INT IDENTITY PRIMARY KEY,
AccountName VARCHAR(50),
CreateDate SMALLDATETIME DEFAULT (CURRENT_TIMESTAMP),
IsActive BIT DEFAULT (1)
)
GO

CREATE TABLE [Transaction] (
TransactionId INT IDENTITY PRIMARY KEY,
AccountID INT,
Amount MONEY,
TransactionDate SMALLDATETIME DEFAULT (getdate())
)
GO

INSERT Account(AccountName) VALUES ('Roger Smith')
INSERT Account(AccountName) VALUES ('Linda White')
INSERT Account(AccountName) VALUES ('Corner Hardware')
INSERT Account(AccountName) VALUES ('Laptop Land')
INSERT Account(AccountName) VALUES ('Cellphone City')

INSERT [Transaction] (AccountID, Amount) VALUES(1, 1400.0)
INSERT [Transaction] (AccountID, Amount) VALUES(1, 1200.0)
INSERT [Transaction] (AccountID, Amount) VALUES(1, 1300.0)
INSERT [Transaction] (AccountID, Amount) VALUES(1, 1100.0)
INSERT [Transaction] (AccountID, Amount) VALUES(2, 400.0)
INSERT [Transaction] (AccountID, Amount) VALUES(2, 200.0)
INSERT [Transaction] (AccountID, Amount) VALUES(2, 300.0)
INSERT [Transaction] (AccountID, Amount) VALUES(2, 900.0)
INSERT [Transaction] (AccountID, Amount) VALUES(3, 33400.0)
INSERT [Transaction] (AccountID, Amount) VALUES(3, 11200.0)
INSERT [Transaction] (AccountID, Amount) VALUES(3, 22300.0)
INSERT [Transaction] (AccountID, Amount) VALUES(3, 12100.0)
GO

-- CROSS APPLY returns only matching data
-- SQL cross apply
-- SQL derived table
-- SQL correlated subquery

SELECT a.AccountName, TopAmount=tt.Amount FROM ACCOUNT a CROSS APPLY
(SELECT TOP ( 1 ) Amount FROM [Transaction] t WHERE t.AccountID = a.AccountID
ORDER BY Amount DESC) tt
ORDER BY AccountName

/*
AccountName TopAmount
Corner Hardware 33400.00
Linda White 900.00
Roger Smith 1400.00
*/

-- OUTER APPLY returns matching and non-matching data
-- SQL outer apply
SELECT a.AccountName, TopAmount=tt.Amount FROM ACCOUNT a
OUTER APPLY (SELECT TOP ( 1 ) Amount FROM [Transaction] t
WHERE t.AccountID = a.AccountID
ORDER BY Amount DESC) tt
ORDER BY AccountName

/*
AccountName TopAmount
Cellphone City NULL
Corner Hardware 33400.00
Laptop Land NULL
Linda White 900.00
Roger Smith 1400.00

*/

--- Cleanup
DROP TABLE tempdb.dbo.Account
DROP TABLE tempdb.dbo.[Transaction]
------------

EXAMPLE2:

CREATE FUNCTION fnMinimumMaximum(@Input1 MONEY,@Input2 MONEY)

RETURNS @MinMax TABLE (Minimum money, Maximum money)

AS

BEGIN

IF @Input1 is NULL and @Input2 is NULL RETURN

INSERT @MinMax

SELECT CASE

WHEN @Input1 < @Input2 THEN @Input1

WHEN @Input2 < @Input1 THEN @Input2

ELSE COALESCE(@Input1,@Input2)

END AS Minimum,

CASE

WHEN @Input1 > @Input2 THEN @Input1

WHEN @Input2 > @Input1 THEN @Input2

ELSE COALESCE(@Input1,@Input2)

END AS Maximum;

RETURN

END -- function

GO

-- SELECT * FROM dbo.fnMinimumMaximum(NULL, NULL)

-- SQL table variable create and population

-- Cross or outer apply will select the maximum dimension

DECLARE @Product TABLE (ProductName varchar(30), Width int, Height int)

INSERT @Product VALUES ( 'PortraitFrame', 8, 11)

INSERT @Product VALUES ( 'LandscapeFrame', 12, 7)

INSERT @Product VALUES ( 'Notepad Computer', NULL, NULL)

INSERT @Product VALUES ( 'CircleFrame', 10, 10)

INSERT @Product VALUES ( 'Ringbinder', NULL, NULL)

-- OUTER APPLY includes nomatches (null data)

-- SQL outer apply

-- Outer apply T-SQL

-- Outer apply mssql

-- SQL outer apply with table-valued function

SELECT ProductName, MaxDimension=Maximum

FROM @Product p

OUTER APPLY dbo.fnMinimumMaximum (Width, Height)

/*

ProductName MaxDimension

PortraitFrame 11.00

LandscapeFrame 12.00

Notepad Computer NULL

CircleFrame 10.00

Ringbinder NULL

*/

-- CROSS APPLY excludes nomatches (null data)

-- SQL cross apply

-- Cross apply T-SQL

-- Cross apply mssql

-- SQL cross apply with table-valued function

SELECT ProductName, MaxDimension=Maximum

FROM @Product p

CROSS APPLY dbo.fnMinimumMaximum (Width, Height)

GO

/*

ProductName MaxDimension

PortraitFrame 11.00

LandscapeFrame 12.00

CircleFrame 10.00

*/

Reference
http://www.sqlusa.com/articles2005/crossapplyversusouterapply/

EXCEPT And INTERSECT

EXCEPT
The EXCEPT operator returns all rows that exist in the table to the left of the operator and
that do not have matching rows in the table to the right.

Example:
select customerkey from Fact_OpenSalesOrdersHeader EXCEPT select customerkey from dim_customer

INTERSECT
The INTERSECT operator returns all rows that exist and matching rows in both tables.
Example:
select customerkey from Fact_OpenSalesOrdersHeader intersect select customerkey from dim_customer

How To Find Not Matching Records From 2 Tables

Using except

select customerkey from Fact_OpenSalesOrdersHeader EXCEPT select customerkey from dim_customer

Using Join

SELECT a.customerkey FROM Fact_OpenSalesOrdersHeader a LEFT OUTER JOIN dim_customer b ON a.customerkey = b.customerkey WHERE b.customerkey IS NULL

Using Not In

select distinct customerkey from Fact_OpenSalesOrdersHeader where customerkey notin(select customerkey from dim_customer)

How To Find Matching Records in 2 Tables

Using join

SELECT fos.customerkey FROM Fact_OpenSalesOrdersHeader as FOS inner join dim_customer as cus on fos.customerkey = cus.customerkey

Using Intersect

select customerkey from Fact_OpenSalesOrdersHeader intersect select customerkey from dim_customer

Using In

select distinct customerkey from Fact_OpenSalesOrdersHeader where customerkey in(select customerkey from dim_customer)

Difference between a primary key and a unique key?

Primary Key doesnt allow the Null Values. where as in
Unique accept the Null values.
One table may have more than one Unique Key Constraint.
But it sholud have only one Primary Key Constraint column.
But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default.
Primary key Supports Auto Increment value
but
Unique doesn't support auto Increment value

CUBE, ROLLUP IN SQL SERVER

Reference
http://chiragrdarji.wordpress.com/2008/09/09/group-by-cube-rollup-and-sql-server-2005/

SQL SERVER – Introduction to JOINs – Basic of JOINs

INNER JOIN

This join returns rows when there is at least one match in both the tables.

OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. 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.

CROSS JOIN

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

Additional Notes related to JOIN:

The following are three classic examples to display where Outer Join is useful. You will notice several instances where developers write query as given below.

SELECT t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
GO

The query demonstrated above can be easily replaced by Outer Join. Indeed, replacing it by Outer Join is the best practice. The query that gives same result as above is displayed here using Outer Join and WHERE clause in join.

/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

The above example can also be created using Right Outer Join.

NOT INNER JOIN
Remember, the term Not Inner Join does not exist in database terminology. However, when full Outer Join is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner Join. This join will give all the results that were not present in Inner Join.

Example SLQ SCRIPT:

CREATE TABLE table1
(ID INT, Value VARCHAR(10))
INSERT INTO Table1 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO
CREATE TABLE table2
(ID INT, Value VARCHAR(10))
INSERT INTO Table2 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 6,'Sixth'
UNION ALL
SELECT 7,'Seventh'
UNION ALL
SELECT 8,'Eighth'
GO
SELECT *
FROM Table1
SELECT *
FROM Table2
GO

/* INNER JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* RIGHT JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* OUTER JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
GO
/* RIGHT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL
GO
/* OUTER JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL OR t2.ID IS NULL
GO
/* CROSS JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
CROSS JOIN Table2 t2
GO