Yukon:Regress or progress - you decide

From: Dr. StrangeLove (steve.nospam._at_rac4sql.net)
Date: 03/04/04 

Date: Thu, 4 Mar 2004 10:55:34 -0500

The following can be found at:
http://www.developer.com/db/article.php/10920_3099771_1

"SQL Server "Yukon" now has a robust development environment that allows
programmers to branch out into the world of data. Many of these people have
only learned what they need to know in order to coexist with the database.
SQL Server "Yukon" is blurring the line between being a developer and a
database person. For me, it is the best things since sliced bread, because
it combines the two technical joys of my life - development and database
work."

Just what Joe C., Fabian Pascal and Anith want to hear!:)

"Many of these people have only learned what they need to know
in order to coexist with the database."
Sadly true and the reason d'etre of the ng:)

And while were at it:

" T-SQL now has the ability to pivot and unpivot data. No more dynamic T-SQL
or overly complex SELECT statements - now you can use this new ability to
create crosstabulated data results. These two SELECT statements will give
the same results, but obviously, the second is a lot easier to write,
especially if we are dealing with more than three pivoted output columns.

SELECT ProductID, ProductName,
     SUM(CASE TheYear WHEN 1996 THEN Extended ELSE 0 END) AS [1996],
     SUM(CASE TheYear WHEN 1997 THEN Extended ELSE 0 END) AS [1997],
     SUM(CASE TheYear WHEN 1998 THEN Extended ELSE 0 END) AS [1998]
FROM vwOrderDetails
GROUP BY ProductID, ProductName

SELECT * FROM vwOrderDetails
PIVOT (SUM(Extended) FOR TheYear IN ([1996], [1997], [1998])) AS PVT "

This doesn't even come close to the *Access* crosstab.
RAC anybody?:)

" Common Table Expressions are new to SQL Server "Yukon" as well. At first,
they appear to be another means of creating derived-tables, and they do
indeed possess many of the same qualities. But, more in-depth research shows
that they also give us the ability to write recursive queries. So if we want
to iterate up or down a hierarchal table structure, we can create a query
that references itself and will recurse as needed (with a default maximum of
100 levels deep) or until we specify otherwise. The following example uses
the Employee table in the Northwind database to demonstrate how to find all
employees and their managers, and those managers' managers, etc....

WITH EmpCTE (EmpID, EmpName, MgrID, Level)
AS
   (SELECT E.EmployeeID, E.LastName, E.ReportsTo, 1
   FROM Employees E
   WHERE ReportsTo IS NULL
   UNION ALL
   SELECT E.EmployeeID, E.LastName, E.ReportsTo, Level + 1
   FROM Employees E
      INNER JOIN EmpCTE ON EmpCTE.EmpID = E.ReportsTo
   WHERE Level <= 5)

SELECT EmpID, EmpName, MgrID, Level
FROM EmpCTE "

Copied right out of DB2 and probably just as slow:)

Just some brainfood for those that are hungry:)

RAC v2.2 and QALite @
www.rac4sql.net

Relevant Pages

  • Re: Suggestions for creating new SQL Server backend from code
    ... acTable, "Employees", "Employees" ... database on SQL Server 2000 that I'm using for development testing. ... This function no longer applies now that the backend is in SQL Server so I'm exploring options for implementing the same functionality with the ...
    (comp.databases.ms-access)
  • Re: SQL Query for Dummies
    ... other tables that I'm used to running queries. ... I can run queries using the Pers_Table with employees and other tables. ... SQL Client program) on a DB2 database? ... Now you've mention Access and DB2, but where does MS SQL Server come ...
    (comp.databases.ms-sqlserver)
  • Re: Trusted Connection
    ... I know the SQL Server team has to be very concerned about backward ... > WSS will not work with Yukon. ... >> I tried to transfer the WSS database from MSDE to Sql Yukon ...
    (microsoft.public.sharepoint.windowsservices)
  • Using AD or creating a new LDAP database
    ... first inclanation was to create a database in SQL Server. ... then you have departments and employees in departments and employees use ...
    (microsoft.public.windows.server.active_directory)
  • Re: A way to test if a table already exists in sql?
    ... For example, if you create a table named employees, the table can be used by ... any person who has the security permissions in the database to use it, ... deleted when you disconnect. ... SQL Server deletes it when both of you disconnect." ...
    (microsoft.public.sqlserver.programming)
Loading