Windows Server 2008

As we head into the big Heroes launch next week, Microsoft today introduced the Windows Essential Server Solutions family of products.  < ?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Available later this year, Essential Server Solutions – Windows Small Business Server 2008 and Windows Essential Business Server 2008 – will be the ideal way for small and midsized companies to realize the benefits of Windows Server 2008, Exchange Server 2007, SQL Server 2008 and other new Microsoft server technologies and services.  The goal is to make the benefits of enterprise-class IT accessible, affordable and simpler for smaller organizations.  One of our main purposes for presenting these two products within a family is to clarify for customers and partners that these are indeed integrated, “all-in-one” solutions, versus scaled down versions of Windows Server, or just various products packaged together.

In our announcement today, we unveiled details about Small Business Server 2008 (official name!), including the technologies and services that are integrated with it. A couple of the notable advancements for customers and partners (among many, outlined in the press release) are:

SBS 2008 Premium Edition is now a two-box solution, including a second copy of Windows Server 2008 and SQL Server Standard Edition, making it an ideal platform for a world of third-party business applications.  Great news for software vendors and solution providers.

SBS 2008 integrates a number of new Microsoft-hosted services, such as Office Live Small Business, Windows Live OneCare for Server and Forefront Security for Exchange Small Business Edition, as well as Windows Server Update Services. In this way, SBS 2008 represents the company’s expanding software-plus-services strategy of giving customers the right combinations of hosted services and on-premise software.

We revealed details about Essential Business Server (formerly known as “Centro”) in November.  Both products will be demonstrated at the Heroes launch in L.A. next week.

Joel Sider

There are at least 5 ways to return data from one table which is not in another table. Two of these are SQL Server 2005 and greater only. This is a post mostly for beginners but hopefully everyone will get something out of it.

Here are the 5 different ways

NOT IN
NOT EXISTS
OUTER JOIN
OUTER APPLY (2005+)
EXCEPT (2005+)

Let's see how this all works
First create these two tables with the Celko approved naming convention.

 

CREATE TABLE #testnulls (ID INT)

INSERT INTO #testnulls VALUES (1)

INSERT INTO #testnulls VALUES (2)

INSERT INTO #testnulls VALUES (null)

 

CREATE TABLE #testjoin (ID INT)

INSERT INTO #testjoin VALUES (1)

INSERT INTO #testjoin VALUES (3)

NOT IN
Run the following Code

 

SELECT * FROM #testjoin WHERE ID NOT IN(SELECT ID FROM #testnulls)

What happened? Nothing gets returned! The reason is because the subquery returns a NULL and you can't compare a NULL to anything

Now run this

SELECT * FROM #testjoin

WHERE ID NOT IN(SELECT ID FROM #testnulls WHERE ID IS NOT NULL)

That worked because we eliminated the NULL values in the subquery

This also works

SELECT * FROM #testjoin j

WHERE j.ID NOT IN(SELECT ID FROM #testnulls n WHERE n.ID = j.ID)

 

NOT EXISTS
NOT EXISTS doesn't have the problem that NOT IN has. Run the following code

 

SELECT * FROM #testjoin j

WHERE NOT EXISTS (SELECT 1

FROM #testnulls n

WHERE n.ID = j.ID)

Everything worked as expected

LEFT and RIGHT JOIN
Plain vanilla LEFT and RIGHT JOINS

 

SELECT j.* FROM #testjoin j

LEFT OUTER JOIN #testnulls n ON n.ID = j.ID

WHERE n.ID IS NULL

With a RIGHT Join you just switch the tables around

SELECT j.* FROM #testnulls n

RIGHT OUTER JOIN #testjoin j ON n.ID = j.ID

WHERE n.ID IS NULL

 

And we can also do a full outer join

SELECT j.* FROM #testnulls n

FULL OUTER JOIN #testjoin j ON n.ID = j.ID

WHERE n.ID IS NULL

AND j.ID IS NOT NULL

You might wonder why we have LEFT and RIGHT Joins, here is why:
<AttemptToBeFunny>LEFT joins are for people who tend to vote for the democrats, RIGHT joins are for people who tend to vote for Republicans. FULL Joins are for independents/undecided people. </AttemptToBeFunny>

You can be real silly and do a subquery LEFT join
 

SELECT j.* FROM #testjoin j

LEFT OUTER JOIN (SELECT ID FROM #testnulls ) n ON n.ID = j.ID

WHERE n.ID IS NULL

 

Now let's talk about SQL 2005 and up

OUTER APPLY (SQL 2005 +)
OUTER APPLY is something that got added to SQL 2005

SELECT j.* FROM #testjoin j

OUTER APPLY

(SELECT id FROM #testnulls n

WHERE n.ID = j.ID) a

WHERE a.ID IS NULL

 

EXCEPT(SQL 2005 +)
EXCEPT is something that got added to SQL 2005. It basically returns everything from the top table which is not in the bottom table

 

SELECT * FROM #testjoin

EXCEPT

SELECT * FROM #testnulls

I am also mentioning INTERSECT since some people might not have seen it before. INTERSECT returns what ever is in both tables(like a regular join)

 

SELECT * FROM #testjoin

INTERSECT

SELECT * FROM #testnulls

 

So there you have it, most likely you already know all these types of joins. If you learned something from this post that is a good thing also.