Info Recursive Queries

Hello all,

for computig graphs and its transitive closure I need recursive SQL-Queries. Best it will be fast.

Now I heard of a new syntax in SQL: the WITH RECURSIVE-Clause.

Which Database-Editions and versions support those recursive queries?

Can anyone tell me where can I find more info about it?

Thanks and best regards

yelmin

It is implemented in SQL Server 2005 with the new CTE ( common table expression ) the base requirements your tables must be UNION compatible by default or through conversion because the sections are connected by UNION ALL. It is very complex if you need to go many levels deep you may want to check out Joe Celko's Trees book. Hope this helps.

Microsoft docs to get you started.

http://msdn2.microsoft.com/en-us/library/ms186243.aspx

http://msdn2.microsoft.com/en-us/library/ms175972.aspx

The book to browse at your local book store.

http://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management-Systems/dp/1558609202

indexing problem

Hi!
I have a table with name "Bestand". The table has 280
columns. I thought to do an index for some columns,
because i heard that you are going to get faster results
if you do some indexes.
But i get this error:
'BESTAND' table
- Unable to create index 'IX_BESTAND'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Cannot create more than 249 nonclustered indices
or column statistics on one table.
Is there anyone who know the answer why i get that
error?
Thank you for helping me
Kind Regards
Mehran
Mehran,
You cannot create an unlimited number of indexes on one table, and you
have reached SQL Server's limit. The error message means just what it
says, and is probably occurring when you try to create the 250th index
on the table.
Steve Kass
Drew University
Mehran wrote:

>Hi!
>I have a table with name "Bestand". The table has 280
>columns. I thought to do an index for some columns,
>because i heard that you are going to get faster results
>if you do some indexes.
>But i get this error:
>'BESTAND' table
>- Unable to create index 'IX_BESTAND'.
>ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
>Server]Cannot create more than 249 nonclustered indices
>or column statistics on one table.
>Is there anyone who know the answer why i get that
>error?
>Thank you for helping me
>Kind Regards
>Mehran
>

indexed views... how to set up?

I've heard SQL2K can have indexed views, but I havent seen any place to set
up the indexes (in the GUI)...
so, A) is it only scriptable and B) does it really help?
Eric Newton
eric.at.ensoft-software.com
www.ensoft-software.com
C#/ASP.net Solutions developerYes, it can. See the white paper
http://msdn.microsoft.com/library/d...
xedviews1.asp
for details or SQL Server Books Online topics Designing an Indexed View and
Creating an Indexed View.
There's no special GUI for indexed views because it's really nothing more
than a creating regular view (see the white paper for
requirements/restrictions) and then creating a clustered index on that
view. I haven't tried it, but you should be able to use the normal GUI for
creating views and indexes in Enterprise Manager to do both of these tasks.
Whether or not they help depends, of course, on your situation. If you have
existing views that do a lot of table joins or aggregates data, then indexed
views may significantly improve the performance of those views. However,
you'll also be using more disk space because the result set of the view is
actually materialized and stored in the leaf level of the clustered index
just like a clustered index on a table. Plus the index will be maintained
whenever the underlying base table(s) are modified. The white paper goes
into more details on the pros and cons.
HTH,
Gail Erickson [MS]
SQL Server Doc Team
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eric Newton" <eric@.ensoft-software.com> wrote in message
news:%233yGYMv%23DHA.1956@.TK2MSFTNGP10.phx.gbl...
> I've heard SQL2K can have indexed views, but I havent seen any place to
set
> up the indexes (in the GUI)...
> so, A) is it only scriptable and B) does it really help?
>
> --
> Eric Newton
> eric.at.ensoft-software.com
> www.ensoft-software.com
> C#/ASP.net Solutions developer
>

indexed views... how to set up?

I've heard SQL2K can have indexed views, but I havent seen any place to set
up the indexes (in the GUI)...
so, A) is it only scriptable and B) does it really help?
--
Eric Newton
eric.at.ensoft-software.com
www.ensoft-software.com
C#/ASP.net Solutions developerYes, it can. See the white paper
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/indexedviews1.asp
for details or SQL Server Books Online topics Designing an Indexed View and
Creating an Indexed View.
There's no special GUI for indexed views because it's really nothing more
than a creating regular view (see the white paper for
requirements/restrictions) and then creating a clustered index on that
view. I haven't tried it, but you should be able to use the normal GUI for
creating views and indexes in Enterprise Manager to do both of these tasks.
Whether or not they help depends, of course, on your situation. If you have
existing views that do a lot of table joins or aggregates data, then indexed
views may significantly improve the performance of those views. However,
you'll also be using more disk space because the result set of the view is
actually materialized and stored in the leaf level of the clustered index
just like a clustered index on a table. Plus the index will be maintained
whenever the underlying base table(s) are modified. The white paper goes
into more details on the pros and cons.
HTH,
Gail Erickson [MS]
SQL Server Doc Team
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eric Newton" <eric@.ensoft-software.com> wrote in message
news:%233yGYMv%23DHA.1956@.TK2MSFTNGP10.phx.gbl...
> I've heard SQL2K can have indexed views, but I havent seen any place to
set
> up the indexes (in the GUI)...
> so, A) is it only scriptable and B) does it really help?
>
> --
> Eric Newton
> eric.at.ensoft-software.com
> www.ensoft-software.com
> C#/ASP.net Solutions developer
>