Quickie about virtual tables.

Will yukon be implementing anything like these 2 UDFS?
Select * from numbers (7, 9)
--
number
7
8
9
Select * from dates ('3-Mar-2001', '7-Mar-2001')
where date <> '6-Mar-2001'
order by date desc
--
date
7-Mar-2001 00:00:00.0
5-Mar-2001 00:00:00.0
4-Mar-2001 00:00:00.0
3-Mar-2001 00:00:00.0
It would be incredibly helpful lads. Thanks a lot.I do not know about it, but it could be implemented in 2000 using an
auxiliary numbers table.
Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
Example:
use northwind
go
select
identity(int, 0, 1) as number
into
dbo.number
from
sysobjects as a cross join sysobjects as b
go
alter table dbo.number
add constraint pk_number primary key clustered (number asc)
go
create function dbo.ufn_gen_numbers (
@.f int,
@.t int
)
returns table
as
return (select number from dbo.number where number between @.f and @.t)
go
create function dbo.ufn_gen_dates (
@.f datetime,
@.t datetime
)
returns table
as
return (select dateadd(day, number, @.f) as the_date from dbo.number where
number <= datediff(day, @.f, @.t))
go
select
number
from
dbo.ufn_gen_numbers(7, 9)
order by
number asc
select
*
from
dbo.ufn_gen_dates('20010303', '20010307')
order by
the_date desc
go
drop function dbo.ufn_gen_dates, dbo.ufn_gen_numbers
go
drop table dbo.number
go
AMB
"Ian" wrote:

> Will yukon be implementing anything like these 2 UDFS?
> Select * from numbers (7, 9)
> --
> number
> 7
> 8
> 9
> Select * from dates ('3-Mar-2001', '7-Mar-2001')
> where date <> '6-Mar-2001'
> order by date desc
> --
> date
> 7-Mar-2001 00:00:00.0
> 5-Mar-2001 00:00:00.0
> 4-Mar-2001 00:00:00.0
> 3-Mar-2001 00:00:00.0
> It would be incredibly helpful lads. Thanks a lot.
>|||You could do that today using a table-valued UDF. But why bother?
Auxiliary tables are a more efficient, more flexible and more portable
method to achieve the same thing.
David Portas
SQL Server MVP
--|||I agree, I could do it using tables, but frankly I'm not interested in
portability. A table that doesn't exist needs no I/O at all to perform
a loop join against it, if it's intrinsic to the server.|||I agree, and this is the way I've always done it. I was hoping though
that as it's such a powerful way for solving relational -> non
relational problems, that it would be created.
Also, no disk space is required either.