I am in the midst of a TTT (Train The Trainer) training for SQL 2005 and came across these interesting points that I think would affect LLBL's future support for SQL2005

1- Schema: now SQL objects have 4 part names that includes a schema part instead of the user name part. This allows for the creation of namespace like functionality for objects

2- Synonyms: similar to alias in an SQL statement BUT the alias can actually be saved as a DB object and utilized instead of the expanded object path

3- CTE (Common Table Expressions) and derived tables

4- PIVOT UNPIVOT

5- Event notification through the service broker

P.S: use of schma instead of schema views is also recomended by MS specially that querying the meta data requires to have the necessary permissions

omar wrote:

I am in the midst of a TTT (Train The Trainer) training for SQL 2005 and came across these interesting points that I think would affect LLBL's future support for SQL2005

1- Schema: now SQL objects have 4 part names that includes a schema part instead of the user name part. This allows for the creation of namespace like functionality for objects

Ah, so the 'schema' is not tied to a user anymore? cool!

2- Synonyms: similar to alias in an SQL statement BUT the alias can actually be saved as a DB object and utilized instead of the expanded object path

Sounds like what Oracle has or is this meant for queries (i.e. it's not possible to define a synonym for a table/view/proc/sequence ?) (have to look it up in the manual perhaps instead of torturing you with questions )

3- CTE (Common Table Expressions) and derived tables

Derived tables as in the feature Postgresql supports? I'm not familiar with CTE's, do you care to give an example?

4- PIVOT UNPIVOT

This is more for OLAP I think

5- Event notification through the service broker

Something I'll look into to include it if it's possible.

Thanks for the info!

Sounds like what Oracle has or is this meant for queries (i.e. it's not possible to define a synonym for a table/view/proc/sequence ?)

just asked the instructor and he says u can create them for all of the above except synonyms (can't create a synonym for a synonym)

I'm not familiar with CTE's, do you care to give an example?

Common Table Expression (CTE) is "A temporary named result set, which is derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement" example: USE AdventureWorks ; GO

WITH DirReps(ManagerID, DirectReports) AS ( SELECT ManagerID, COUNT(*) FROM HumanResources.Employee AS e WHERE ManagerID IS NOT NULL GROUP BY ManagerID ) SELECT * FROM DirReps ORDER BY ManagerID GO basically CTEs help u to make ur code more maintainable and readable instead of SubQuerys. CTEs can be recursive and have certain rules for referncing each other.

Quote:

4- PIVOT UNPIVOT

This is more for OLAP I think

actually these commands are now part of the standard T-SQL supported by SQL2005

omar wrote:

Sounds like what Oracle has or is this meant for queries (i.e. it's not possible to define a synonym for a table/view/proc/sequence ?)

just asked the instructor and he says u can create them for all of the above except synonyms (can't create a synonym for a synonym)

Ah thanks for that . They'll be of a great help to define fine grained security

I'm not familiar with CTE's, do you care to give an example?

Common Table Expression (CTE) is "A temporary named result set, which is derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement"

example: USE AdventureWorks ; GO

WITH DirReps(ManagerID, DirectReports) AS ( SELECT ManagerID, COUNT(*) FROM HumanResources.Employee AS e WHERE ManagerID IS NOT NULL GROUP BY ManagerID ) SELECT * FROM DirReps ORDER BY ManagerID GO basically CTEs help u to make ur code more maintainable and readable instead of SubQuerys. CTEs can be recursive and have certain rules for referncing each other.

Ah, but isn't that similar to: SELECT * FROM ( SELECT ManagerID, COUNT(*) FROM HumanResources.Employee AS e WHERE ManagerID IS NOT NULL GROUP BY ManagerID ) AS DirReps ORDER BY ManagerID, which is already possible in 2000 ? (though I've to read more about them I think to see the additional power of this, especially the recursive feature

Otis wrote:

Ah, but isn't that similar to: SELECT * FROM ( SELECT ManagerID, COUNT(*) FROM HumanResources.Employee AS e WHERE ManagerID IS NOT NULL GROUP BY ManagerID ) AS DirReps ORDER BY ManagerID, which is already possible in 2000 ?

True! But, this way your code is much more readable. Plus, you can use the definition as many times as you want within the execution scope, I think.

Bashar Lulu

ah, that last remark is indeed perhaps the core thing: re-use the temp set by simply using the name. You can also use a temptable, but that is awkward because you first have to set it up etc.

Some interesting things to implement in the Sqlserver 2005 DQE

another interesting new feature in the ADO.NET2.0 is "Multiple Active Result Sets".

MARS gives the ability to have more than one default result set (firehose cursor) outstanding while other operations can execute within the same session. By default, all .NET2.0 drivers that support MARS will establish a MARS enabled connection.

Its worth noting that Ms is only supporting MARS in the OLEDB, ODBC and SQL .NET2.0 drivers and NOT in the oracle .NET2.0 driver.

An exmaple of using this:

using System;
using System.Data;
using System.Data.SqlClient;

namespace NetAcademia.Whidbey.Demos
{
  class App
  {
    /*
    static bool Read(SqlDataReader reader)
    {
      Console.WriteLine(reader.FieldCount);
      return reader.Read();
    }
    */
    static void Main()
    {
      try
      {
        using (SqlConnection conn =
          new SqlConnection(
          @"Server=.\yukon;Integrated Security=SSPI;
            Database=Northwind;use mdac9=true"))
        {
          SqlCommand cmd1 = new SqlCommand(
            "SELECT TOP 2 * FROM Employees", conn);
          SqlCommand cmd2 = new SqlCommand(
            "SELECT TOP 3 * FROM Orders", conn);

          conn.Open();

          // Az elso reader olvas
          using (SqlDataReader rdr1 = cmd1.ExecuteReader())

          // Az második reader olvas, UGYANAZON a connectionön
          using (SqlDataReader rdr2 = cmd2.ExecuteReader())
          {
            bool hasData1 = true, hasData2 = true;

            while ((hasData1 && (hasData1 = rdr1.Read())) |
                   (hasData2 && (hasData2 = rdr2.Read())))
            {
              if (hasData1)
              {
                Console.WriteLine("-----------------------------------------");
                Console.WriteLine("Employees");
                for (int i = 0; i < rdr1.FieldCount; i++)
                {
                  Console.WriteLine(rdr1[i]);
                }
              }

              if (hasData2)
              {
                Console.WriteLine("-----------------------------------------");
                Console.WriteLine("Orders");
                for (int i = 0; i < rdr2.FieldCount; i++)
                {
                  Console.WriteLine(rdr2[i]);
                }
              }
            }
          }
        }
      }
      catch (Exception e)
      {
        Console.WriteLine(e);
      }
    }
  }
}

A new isolation level has been added in SQL 2005; Snapshot Isolation which

Permits readers to not block writers and writers to not block readers

Perhaps this would be off interest to you.

I'll have more on this in a while!

omar wrote:

another interesting new feature in the ADO.NET2.0 is "Multiple Active Result Sets".

MARS gives the ability to have more than one default result set (firehose cursor) outstanding while other operations can execute within the same session. By default, all .NET2.0 drivers that support MARS will establish a MARS enabled connection.

Its worth noting that Ms is only supporting MARS in the OLEDB, ODBC and SQL .NET2.0 drivers and NOT in the oracle .NET2.0 driver.

The Oracle provider from MS is crippled anyway, there is no Save on OracleTransaction for example, while it is supported on ODP.NET.

On Oracle, MARS can be implemented using 2 or more REF CURSOR output parameters which are converted to an OracleDataReader.

I've read some docs about MARS and executing multiple statements at once. I'm not sure if that is a good thing to have, because it opts for 'obscure programming', i.e.: you don't know the order in which the statements are executed, as SqlServer schedules the batches coming in over the connection. Most problems can be solved as well with moving selects out of the transaction completely (i.e. do them before the transaction) which is more efficient as well.

Otis wrote:

Most problems can be solved as well with moving selects out of the transaction completely (i.e. do them before the transaction) which is more efficient as well.

Agree... UnitOfWork is perfect for this.

Otis wrote:

Ah, but isn't that similar to: SELECT * FROM ( SELECT ManagerID, COUNT(*) FROM HumanResources.Employee AS e WHERE ManagerID IS NOT NULL GROUP BY ManagerID ) AS DirReps ORDER BY ManagerID, which is already possible in 2000 ? (though I've to read more about them I think to see the additional power of this, especially the recursive feature

Hi, my first post on the forum (thinking about buying LLBLGen...)

The key point about CTEs is the one Omar made about them being recursive, this is where they get their real power, as they increase the expressive power of the SQL language itself, it is not just a syntax change.

SQL as a language has some limits to its expressiveness, there are (to my knowledge) two classes of problem that cannot be directly represented in the language (in the same way as you would an INSERT). They are

a) graph problems, such as transitive closure or TSP b) powerset (the set of all the possible combinations from a set of items)

These show up more often than you would expect in real business situations, the project I work on has great examples of both. For example, graph problems show up any time you have a hierarchy (of menus, BOM, categories or whatever) or travelling-salesman type optimisation, and powerset can show up in packing/optimisation problems.

The algorithm for solving these problems can be thought of as "start here...are we done..no..get more stuff...are we done...no...get more stuff..." and so on. SQL represents "get more stuff" with a JOIN, but if you don't know how many times you need to do it (just how many levels are there in your menu?) then you can't write it in a single SQL statement.

So these problems are usually solved by recursion (call to a stored proc) or iteration (with a CURSOR). CTEs will allow them to be solved directly in one statement.

http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/default.aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp

Excellent stuff, Phil .

I think I need some time to adjust to the fact CTE's exists, but I also think they will simplify the usage of SQL a lot in some situations indeed, as the urge to 're-use a set you just build, but with another query', is quite common indeed