One of the charts on the MS site that compares SQL editions indicates that
SQL Express supports the creation of indexed views but not something with
"query matching" that I don't understand.



I've spent the last couple of years workign on a project that should be able
to use MSDE 2k. We're using the SQL 2k Developer edition to develop the
system. Some of our clients won't mind paying for SQL 2k standard if they
have to but we're hoping to get away with MSDE where possible (none of them
will have trouble with the throttling and other restrictions of MSDE).



Anyway, we have a few views that are indexed in order to enforce uniqueness
on certain columns. Will this function properly under MSDE 2k? Also, will it
work properly under SQL Express if we choose to upgrade?



Also, we're wondering about the agent and jobs. We are not planning on doing
anything other than creatign jobs to do maintenance such as shrinking and
backups. Will MSDE 2k allow for this?





Here's an example of what we are doing with indexed views:



CREATE TABLE [dbo].[WrkStns] (
[WrkStnID] [int] IDENTITY (1, 1) NOT NULL ,
[CreatedWhen] [datetime] NOT NULL ,
[CreatedBy] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ModWhen] [datetime] NOT NULL ,
[ModBy] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WrkStnName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WrkStnCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO



ALTER TABLE [dbo].[WrkStns] WITH NOCHECK ADD
CONSTRAINT [PK_Wrkstns] PRIMARY KEY CLUSTERED
(
[WrkStnID]
) ON [PRIMARY]
GO



ALTER TABLE [dbo].[WrkStns] ADD
CONSTRAINT [DF_WrkStns_CreatedWhen] DEFAULT (getdate()) FOR [CreatedWhen],
CONSTRAINT [DF_WrkStns_CreatedBy] DEFAULT ('ADMIN') FOR [CreatedBy],
CONSTRAINT [DF_WrkStns_ModWhen] DEFAULT (getdate()) FOR [ModWhen],
CONSTRAINT [DF_WrkStns_ModBy] DEFAULT ('ADMIN') FOR [ModBy],
CONSTRAINT [IX_Wrkstns_WrkstnName] UNIQUE NONCLUSTERED
(
[WrkStnName]
) ON [PRIMARY]
GO



======================



SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



CREATE VIEW dbo.WrkStnCodesUnique
WITH SCHEMABINDING
AS
SELECT WrkStnCode
FROM dbo.WrkStns
WHERE (WrkStnCode IS NOT NULL)





GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



set
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIF
IER,ANSI_NULLS on
GO



set NUMERIC_ROUNDABORT off
GO



CREATE UNIQUE CLUSTERED INDEX [ixWrkStnCodesUnique] ON
[dbo].[WrkStnCodesUnique]([WrkStnCode]) ON [PRIMARY]
GO



set NUMERIC_ROUNDABORT off set arithabort OFF
GO



SET QUOTED_IDENTIFIER OFF









Thanks,



Keith

Thank you Andrea.

Keith

hi Keith,
[quoted text, click to view]

I do thinnk the term "query matching" is little to closed, as query matching
is used every where in SQL engines to provide accurate results...
BTW, MSDE (2k is currently the only 1 available, as MSDE 1.0 version, based
on SQL Server 7.0, has been retired long ago :D) allow you to create indexed
views as all SQL Server 2000 edition do, but only the Enterprise edition can
directly benefit from it in optimization step.. all other editions require
you to force indexed views usage via the query hint WITH NO EXPAND
(http://msdn2.microsoft.com/en-us/library/ms188783.aspx)

[quoted text, click to view]

yes they should, and on SQLExpress too, as long as do not intersect with
deprecated behaviours.. start reading at
http://msdn2.microsoft.com/en-us/library/ms143532.aspx
[quoted text, click to view]

MSDE supports and installs the SQL Server Agent, while SQLExpress does not..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.19.0 - DbaMgr ver 0.63.0 and further SQL Tools
--------- remove DMO to reply