Does sql server have something like dual

In oracle you can run queries against some built in virtual table called dual. Like below:

SELECT SEQ.NEXTVAL FROM DUAL

does sql server have anything similar?Do you tried 'Select SEQ.NEXTVAL' That should work!|||SQL Server does not have such virtual table for that exact purpose like oracle has, for example in Oracle you can run:

select sysdate from dual

Same would be in SQL Server:

select getdate()

e.g any virtual table is not needed, statement just consists of SELECT plus then a function or T-SQL specific stuff. With identities (same as sequences in Oracle) there are SCOPE_IDENTITY(), @.@.IDENTITY AND IDENT_CURRENT, for example to query current identity value:

SELECT IDENT_CURRENT('TABLE_NAME')

Exact difference with all three:

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@.@.IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

So to reply completely to your question, there are functions etc which do not need any virtual table when they are general (table-independant, querying the date is good example) and then there are stuff which need the table to be specified in the query like previous identity query.sql

Does SQL have a "sequencename.nextval" like Oracle?

Helo
Does anyone know if SQL Server has the equivilant for
Oracle's sequencename.nextval?
Thanks you
HI'm not familiar with sequencename.nextval, but are you familiar with the
IDENTITY property and does that do what you need?
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:02be01c48af5$33117820$a601280a@.phx.gbl...
> Helo
> Does anyone know if SQL Server has the equivilant for
> Oracle's sequencename.nextval?
> Thanks you
> H|||No, I'm looking for a built in function from SQL that will
do a n+1 calculation automatically on a value (i.e. not a
seed value + 1 for each new row like the id property will
do).
Thanks
H
>--Original Message--
>I'm not familiar with sequencename.nextval, but are you
familiar with the
>IDENTITY property and does that do what you need?
>>.
>|||Unfortunately, that does not exist yet (SQL Server 2005 will have a built-in
ROW_NUMBER function to do this).
There are various ways to do this (see this article:
http://www.aspfaq.com/show.asp?id=2427 ), but it's recommended that you do
it on the client-side instead if possible.
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:03a401c48af9$83e79aa0$a301280a@.phx.gbl...
> No, I'm looking for a built in function from SQL that will
> do a n+1 calculation automatically on a value (i.e. not a
> seed value + 1 for each new row like the id property will
> do).
> Thanks
> H
> >--Original Message--
> >I'm not familiar with sequencename.nextval, but are you
> familiar with the
> >IDENTITY property and does that do what you need?
> >>.
> >|||FYI, the sequence in Oracle isn't related to a row number. It is just an
object used to generate a number sequence, and isn't associated with a table
or resultset. This would be a good feature for a future version of SQL
Server.
You can do a bare bones simulation as follows:
CREATE TABLE sequences (
-- sequence is a reserved word
seq varchar(100) primary key,
seq_id int
);
MS SQL Server stored procedure:
CREATE PROCEDURE nextval
@.seq varchar(100),
@.seq_id INT OUTPUT
AS
-- return an error if sequence does not exist
-- (in case table is truncated or sequence deleted)
SET @.seq_id = -1
UPDATE sequences
SET @.seq_id = seq_id = seq_id + 1
WHERE seq = @.seq
RETURN @.seq_id
It isn't nearly as functional as the sequence object, but it works in some
situations.
--
Michael D. Long
Microsoft MVP - Windows SDK
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:evHhsqviEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Unfortunately, that does not exist yet (SQL Server 2005 will have a
built-in
> ROW_NUMBER function to do this).
> There are various ways to do this (see this article:
> http://www.aspfaq.com/show.asp?id=2427 ), but it's recommended that you do
> it on the client-side instead if possible.
>
> "H" <anonymous@.discussions.microsoft.com> wrote in message
> news:03a401c48af9$83e79aa0$a301280a@.phx.gbl...
> > No, I'm looking for a built in function from SQL that will
> > do a n+1 calculation automatically on a value (i.e. not a
> > seed value + 1 for each new row like the id property will
> > do).
> > Thanks
> > H
> >
> > >--Original Message--
> > >I'm not familiar with sequencename.nextval, but are you
> > familiar with the
> > >IDENTITY property and does that do what you need?
> > >>.
> > >
>|||"Michael D. Long" <michael.d.long-nospam@.comcast.net> wrote in message
news:u54YGiwiEHA.2436@.TK2MSFTNGP09.phx.gbl...
> FYI, the sequence in Oracle isn't related to a row number. It is just an
> object used to generate a number sequence, and isn't associated with a
table
> or resultset. This would be a good feature for a future version of SQL
> Server.
Thanks for the clarification. What situations do you use the sequence
object in?|||If you wish to provide a value and SQL returns that value + 1 , you could
write a scalar User defined function for this in several minutes...
See user defined functions in Books on line.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:02be01c48af5$33117820$a601280a@.phx.gbl...
> Helo
> Does anyone know if SQL Server has the equivilant for
> Oracle's sequencename.nextval?
> Thanks you
> H|||Basically, in Oracle this is used whenever an IDENTITY is used in
SQL-Server :-)
Gert-Jan
Adam Machanic wrote:
> "Michael D. Long" <michael.d.long-nospam@.comcast.net> wrote in message
> news:u54YGiwiEHA.2436@.TK2MSFTNGP09.phx.gbl...
> > FYI, the sequence in Oracle isn't related to a row number. It is just an
> > object used to generate a number sequence, and isn't associated with a
> table
> > or resultset. This would be a good feature for a future version of SQL
> > Server.
> Thanks for the clarification. What situations do you use the sequence
> object in?
--
(Please reply only to the newsgroup)sql