MSDE 2000 or SQL Server 2005 Express (Beta) for new project?

Hello guys! I have to develop a new web based project. It's a webportal for events. At the moment it's runnung on an Access database.The new version should be running on a SQL server - the desicion is:MSDE 2000 or SQL Server 2005 Express (Beta). What do you recommend? Thewebpages are developed under .NET 1.1.
SQL Server 2005 Express is not going to be as stable and is not aproven technology as is MSDE. But if your release date somewhatdistant (several months), you may want to use it (hopefully the RTMversion will be relased before you finish.) You'll still be anearly adopter, though, and may have to deal with bugs. In theend, it's a question of: What features do you need in Express that MSDEdoesn't provide? And do these features counter-balance the issuesof working with a beta?
|||Well ... it's a company philosophy to work always on the newsestplattform. If you are common with the MSDE and the SQL Express 2005edition you can surly say if there are some performance improvements.The project leaders think that the MSDE version is not so fast. Specialfeatures are not requested, only SQL querys and stored procedures (...and performance ). Is a change of database engine (from MSDEto SQL 2005) a problem in the future?
Thanks a lot!!!
|||

Here my first look at SQL Server 2005, an events web site needs Time Interval, it is built in SQL server 2005 and index Column include without Composite index. It also schreds and decomposes XML native. Hope this helps.

SQL SERVER 2005 Development

What is new for ASP.NET

The most important is CLR integration write your stored procedures in C#.NET and VB.NET.

INDEX COLUMN INCLUDE, NEW DRI(Declarative Referential Integrity) CASCADE DELETE SET NULL AND CASCADE UPDATE SET NULL, When used with Portal Templates allow the user to browse before login. This is almost like Denormalization with Composite index without the Primary Key restriction of NOT NULL. ANSI NULL enabling on index columns.

Varchar (max), Nvarchar(max) Varbinary(max) Variable length data that may exceed 8000 and 4000 with 2gig limit, in the past these are stored in Text and Image, SQL Server creates an

Arithmetic pointer to the data because it is not table row based.

DTS is now Integration Service with pipeline.

Service Broker can be used to Queue shipping data while SQL Server will process sales Transactions.

XML DATATYPE

XML native Datatypes including XML indexes and XML decomposition.

XML QUERY EXAMPLE using XML PATH

SELECT CustomerID as "@.CustomerID",

(SELECT OrderID as "@.OrderID"

FROM Orders

WHERE Orders.CustomerID = Customers.CustomerID

FOR XML PATH('Order'), TYPE),

(SELECT DISTINCT LastName as "@.LastName"

FROM Employees

JOIN Orders ON Orders.EmployeeID = Employees.EmployeeID

WHERE Customers.CustomerID = Orders.CustomerID

FOR XML PATH('Employee'), TYPE)

FROM Customers

FOR XML PATH('Customer')

COMMON TABLE EXPRESSIONS CTE VIRTIUAL VIEWS

VIEWS are Query Rewrites in ANSI SQL

ANSI SQL 99 with Recursive QUERY Implementation some restrictions.

Tables must be equal INNER JOIN

CONVERT clause can be used to meet the UNION operator requirement.

But multiple Recursive members can only be connected by UNION ALL not UNION because UNION removes duplicates just like SELECT DISTINCT .

Recursion Control

Default server wide - 100

Configurable maximum iterations

OPTION MAXRECURSION <value>

1-32767

Recursive Member Restrictions

No operations leading to DISTINCT

Only UNION ALL allowed between anchor and recursive

SELECT DISTINCT

GROUP BY

HAVING

Scalar Aggregation

TOP

Outer Joins

1 CTE reference per recursive member

The example below in the executing statement, the CTE is referenced twice; that is the reason for the OUTER JOIN, the OUTER JOIN is not allowed with Recursive CTE because in ANSI SQL OUTER JOIN has a default NULL condition and is limited to four iterations while a Recursive CTE can run to 100.

USE AdventureWorks;

GO

WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)

AS

(

SELECT SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

)

SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,

E.ManagerID, OM.NumberOfOrders, OM.MaxDate

FROM HumanResources.Employee AS E

JOIN Sales_CTE AS OS

ON E.EmployeeID = OS.SalesPersonID

LEFT OUTER JOIN Sales_CTE AS OM

ON E.ManagerID = OM.SalesPersonID

ORDER BY E.EmployeeID;

GO

Recursive CTE using CONVERT clause to satisfy the UNION ALL operator requirement.

USE AdventureWorks ;

Go

WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)

AS (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),

e.Title,

e.EmployeeID,

1,

CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)

FROM HumanResources.Employee AS e

JOIN Person.Contact AS c ON e.ContactID = c.ContactID

WHERE e.ManagerID IS NULL

UNION ALL

SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +

c.FirstName + ' ' + c.LastName),

e.Title,

e.EmployeeID,

EmployeeLevel + 1,

CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +

LastName)

FROM HumanResources.Employee as e

JOIN Person.Contact AS c ON e.ContactID = c.ContactID

JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID

)

SELECT EmployeeID, Name, Title, EmployeeLevel

FROM DirectReports

ORDER BY Sort ;

GO

The following example is using MAXRECURSION to cancel a statement.

USE AdventureWorks ;

GO

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS

(

SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel

FROM HumanResources.Employee

WHERE ManagerID IS NULL

UNION ALL

SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1

FROM HumanResources.Employee e

INNER JOIN DirectReports d

ON e.ManagerID = d.EmployeeID

)

SELECT *

FROM DirectReports

OPTION (MAXRECURSION 4) ;

GO

First look at SQL Server 2005 development. Comments appreciated.