Systems Engineering and RDBMS

Archive for August 7th, 2008

SQL Server 2008 Web Edition

Posted by decipherinfosys on August 7, 2008

SQL Server 2008 also has a new SKU: Web Edition. Web Edition has a better scalability than the Express or Workgroup Editions – it has no limits on the RAM usage, no limits on the database size and can use up-to 4 CPUs. You can get more information on this edition from this MSDN post here or on the web edition page on the Microsoft’s SQL Server site over here.

Posted in SQL Server | Leave a Comment »

Self Joins

Posted by decipherinfosys on August 7, 2008

One of the recent questions that was asked by a reader for her particular problem needed to include a self join. So, what is a self join? A self join is a join that is done between the same table by using aliases. Such joins are useful for displaying data when there is a relationship between different columns of the data in the same table OR when you wish to retrieve data in a single row based on comparisons between different rows in the same table. They are pretty useful in certain scenarios. We will cover some of those scenarios in this post to give you an idea on where and how you can make use of that information.

A classic example that is always given whenever one talks about self joins is that of an employee table in which there is a recursive 1:N relationship between the employee and their manager. Here is an example:

set nocount on
go
DECLARE @EMP_MASTER TABLE
(
EMP_NBR INT NOT NULL PRIMARY KEY,
EMP_NAME NVARCHAR(20),
MGR_NBR INT NULL
);

INSERT INTO @EMP_MASTER VALUES (1, ‘DON’, 5);
INSERT INTO @EMP_MASTER VALUES (2, ‘HARI’, 3);
INSERT INTO @EMP_MASTER VALUES (3, ‘RAMESH’, 5);
INSERT INTO @EMP_MASTER VALUES (4, ‘JOE’, 5);
INSERT INTO @EMP_MASTER VALUES (5, ‘DENNIS’, NULL);
INSERT INTO @EMP_MASTER VALUES (6, ‘NIMISH’, 7);
INSERT INTO @EMP_MASTER VALUES (7, ‘JESSIE’, 4);
INSERT INTO @EMP_MASTER VALUES (8, ‘KEN’, 2);
INSERT INTO @EMP_MASTER VALUES (9, ‘AMBER’, 5);
INSERT INTO @EMP_MASTER VALUES (10, ‘JIM’, 5);

select empl.EMP_NAME as employee_name, mgr.EMP_NAME as manager_name
from @EMP_MASTER as empl
inner join @EMP_MASTER as mgr
on empl.MGR_NBR = mgr.EMP_NBR

employee_name        manager_name
-------------------- --------------------
DON                  DENNIS
HARI                 RAMESH
RAMESH               DENNIS
JOE                  DENNIS
NIMISH               JESSIE
JESSIE               JOE
KEN                  HARI
AMBER                DENNIS
JIM                  DENNIS

Another example is that of a product line where you might want to build a report to see which products have the same name but differ in their packaging or aisle location where they are placed in the warehouse or a shop. Example:

declare @product table (product_name nvarchar(30), aisle nvarchar(10));
insert into @product values (‘Starbucks-Coffee’, ‘A’);
insert into @product values (‘Starbucks-Coffee’, ‘B’);
insert into @product values (‘Earl Grey’, ‘A’);

select a.product_name, a.aisle as first_aisle, b.aisle as second_aisle
from @product as a
inner join @product as b
on a.product_name = b.product_name
where a.aisle < b.aisle

product_name                   first_aisle second_aisle
------------------------------ ----------- ------------
Starbucks-Coffee               A           B

The same logic can be applied in a multitude of situations – some of the situations that come to mind (besides the ones mentioned above are):

a) Schedule log – like a train station or an airline departure/arrival running log comparisons,

b) A Web server hit stats,

c) Geological data,

d) Stock values for NASDAQ, NYSE etc.

In all these, relative comparisons based on time are typically made when one does a self join.

Posted in DB2 LUW, Oracle, SQL Server | 1 Comment »

SQL Server 2008 is released

Posted by decipherinfosys on August 7, 2008

Read on a technet flash a few minutes back that the wait is finally over. SQL Server 2008 is released and you can download the RTM version from technet site here and you can read more on it at the technet blog here.

There are a lot of very good features for DBAs and Developers in this release.  We have been working with SQL Server 2008 since the initial CTPs (Community Technical Previews).  You can search for “SQL Server 2008” on this blog site to get all the articles.  On this blog site, we will soon be releasing e-books/guides on topics like T-SQL Enhancements/Changes, High Availability and DR Enhancements/changes, Security & Protection Enhancements/Changes etc.  Watch this space.

Posted in SQL Server | Leave a Comment »