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
![Smile [:)]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_szCx3AYMovkfwa24kkVtYlfpoXpapWIcplRZqPlxpDSasyHuNoaAaWoHh-RL-Iiy_iSWUbXnZN94IoR9RKMxjrWMTuxhoLsKuK2T6V3pXffxiHhyHFGQ=s0-d)
). 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.
