[SQL Server 2005] Sub-query and "WITH" CTE, which statement would be faster for database p

1.
===================================================================== SELECT * FROM ( SELECT TOP 20010 *, ROW_NUMBER() OVER( ORDER BY [posttime]
DESC ) AS [rowcounting] FROM [topics] ORDER BY [posttime] DESC ) AS
[temptable] WHERE [rowcounting] > 20000
=====================================================================
and
2.
===================================================================== WITH [temptable] AS ( SELECT TOP 20010 *, ROW_NUMBER() OVER( ORDER BY
[posttime] DESC ) AS [rowcounting] FROM [topics] ORDER BY [posttime] DESC )
SELECT * FROM [temptable] WHERE [rowcounting] > 20000
=====================================================================
Which one will be run faster inside sql server?
Which one can get the most performance?Asked and answered in .programming -- please refrain from multiposting.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Parco" <parco@.nowlover.com> wrote in message
news:eqHzFq5dGHA.380@.TK2MSFTNGP04.phx.gbl...
> 1.
> =====================================================================> SELECT * FROM ( SELECT TOP 20010 *, ROW_NUMBER() OVER( ORDER BY [posttime]
> DESC ) AS [rowcounting] FROM [topics] ORDER BY [posttime] DESC ) AS
> [temptable] WHERE [rowcounting] > 20000
> =====================================================================> and
> 2.
> =====================================================================> WITH [temptable] AS ( SELECT TOP 20010 *, ROW_NUMBER() OVER( ORDER BY
> [posttime] DESC ) AS [rowcounting] FROM [topics] ORDER BY [posttime]
> DESC )
> SELECT * FROM [temptable] WHERE [rowcounting] > 20000
> =====================================================================> Which one will be run faster inside sql server?
> Which one can get the most performance?
>

[?] DataSets execution order

Hello,
How to set the fixed order of DataSets execution? For example, I need
DataSet2 to be started only when DataSet1 is finished.
Thanks,
Lucy.If you mark the data source to "Use single transaction", the queries will
have to execute in series (unless you happen to have a custom data provider
that supports transactions spanning multiple connections, which would allow
us to execute them in parallel... but we didn't ship any such data
providers)
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OybWKscaEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Hello,
> How to set the fixed order of DataSets execution? For example, I need
> DataSet2 to be started only when DataSet1 is finished.
> Thanks,
> Lucy.
>|||Yes I see if I check "Use single transaction", the queries runs
sequentially. But is the way to be sure that query from DataSet1 will start
prior to query from DataSet2?
Thanks,
Lucy
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:uwyZ2DdaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> If you mark the data source to "Use single transaction", the queries will
> have to execute in series (unless you happen to have a custom data
provider
> that supports transactions spanning multiple connections, which would
allow
> us to execute them in parallel... but we didn't ship any such data
> providers)
> --
> My employer's lawyers require me to say:
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:OybWKscaEHA.2972@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > How to set the fixed order of DataSets execution? For example, I need
> > DataSet2 to be started only when DataSet1 is finished.
> >
> > Thanks,
> > Lucy.
> >
> >
>|||When running sequentially, they'll execute in the order they appear in the
RDL file.
(Unfortunately, we don't have anything in the design tool to let you reorder
them. If you need them in a different order, you'll have to hand-edit the
RDL to rearrange them).
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OsB0AKdaEHA.3596@.tk2msftngp13.phx.gbl...
> Yes I see if I check "Use single transaction", the queries runs
> sequentially. But is the way to be sure that query from DataSet1 will
start
> prior to query from DataSet2?
> Thanks,
> Lucy
>
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:uwyZ2DdaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> > If you mark the data source to "Use single transaction", the queries
will
> > have to execute in series (unless you happen to have a custom data
> provider
> > that supports transactions spanning multiple connections, which would
> allow
> > us to execute them in parallel... but we didn't ship any such data
> > providers)
> >
> > --
> > My employer's lawyers require me to say:
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> >
> > "Lusy Crown" <evesq@.uk2.net> wrote in message
> > news:OybWKscaEHA.2972@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > How to set the fixed order of DataSets execution? For example, I need
> > > DataSet2 to be started only when DataSet1 is finished.
> > >
> > > Thanks,
> > > Lucy.
> > >
> > >
> >
> >
>

[.NET and SQL Server 2000] Explicit order Insert

My current project, which I am programming in .NET, requires me to insert a
variable number of rows, which make up a set, in a specific order. A
collection of sets that are inserted one after the other is a batch. Rows in
each set MUST be kept together, and sets in each batch MUST be kept together
.
What is the best way to implement this, both on the .NET side and also the
SQL Server side? Should I lock the table from inserts and updates (updating
won't be a problem, but inserting will be) before I start inserting rows? Ho
w
would I implement a system that would roll back all the inserts that have
occured in that batch if an error occurs?
Thank you very much,
Yohan MacDonaghLooking more into it, it looks like the best way is to use the DataSet and
DataAdapter objects in .NET.
Can anyone answer this, however: when a datasource is being updated via a
DataAdapter, is the table locked from inserts during the update?
"Yohan" wrote:

> My current project, which I am programming in .NET, requires me to insert
a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
in
> each set MUST be kept together, and sets in each batch MUST be kept togeth
er.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates (updatin
g
> won't be a problem, but inserting will be) before I start inserting rows?
How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
> Thank you very much,
> Yohan MacDonagh|||"Yohan" <Yohan@.discussions.microsoft.com> wrote in message
news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> My current project, which I am programming in .NET, requires me to insert
> a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
> in
> each set MUST be kept together, and sets in each batch MUST be kept
> together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates
> (updating
> won't be a problem, but inserting will be) before I start inserting rows?
> How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
A subset of rows must be defined in terms of shared column values. So give
each row a BatchID and a SetID.
David|||Unfortuantly, I cannot. I am limited by an existing (and very old) data
schema. There are no relationships. Each property of an object in .NET is a
new row in this schema (very weird, I know).
"David Browne" wrote:

> "Yohan" <Yohan@.discussions.microsoft.com> wrote in message
> news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> A subset of rows must be defined in terms of shared column values. So giv
e
> each row a BatchID and a SetID.
> David
>
>

[.NET and SQL Server 2000] Explicit order Insert

My current project, which I am programming in .NET, requires me to insert a
variable number of rows, which make up a set, in a specific order. A
collection of sets that are inserted one after the other is a batch. Rows in
each set MUST be kept together, and sets in each batch MUST be kept together.
What is the best way to implement this, both on the .NET side and also the
SQL Server side? Should I lock the table from inserts and updates (updating
won't be a problem, but inserting will be) before I start inserting rows? How
would I implement a system that would roll back all the inserts that have
occured in that batch if an error occurs?
Thank you very much,
Yohan MacDonagh
Looking more into it, it looks like the best way is to use the DataSet and
DataAdapter objects in .NET.
Can anyone answer this, however: when a datasource is being updated via a
DataAdapter, is the table locked from inserts during the update?
"Yohan" wrote:

> My current project, which I am programming in .NET, requires me to insert a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows in
> each set MUST be kept together, and sets in each batch MUST be kept together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates (updating
> won't be a problem, but inserting will be) before I start inserting rows? How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
> Thank you very much,
> Yohan MacDonagh
|||"Yohan" <Yohan@.discussions.microsoft.com> wrote in message
news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> My current project, which I am programming in .NET, requires me to insert
> a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
> in
> each set MUST be kept together, and sets in each batch MUST be kept
> together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates
> (updating
> won't be a problem, but inserting will be) before I start inserting rows?
> How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
A subset of rows must be defined in terms of shared column values. So give
each row a BatchID and a SetID.
David
|||Unfortuantly, I cannot. I am limited by an existing (and very old) data
schema. There are no relationships. Each property of an object in .NET is a
new row in this schema (very weird, I know).
"David Browne" wrote:

> "Yohan" <Yohan@.discussions.microsoft.com> wrote in message
> news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> A subset of rows must be defined in terms of shared column values. So give
> each row a BatchID and a SetID.
> David
>
>

[.NET and SQL Server 2000] Explicit order Insert

My current project, which I am programming in .NET, requires me to insert a
variable number of rows, which make up a set, in a specific order. A
collection of sets that are inserted one after the other is a batch. Rows in
each set MUST be kept together, and sets in each batch MUST be kept together.
What is the best way to implement this, both on the .NET side and also the
SQL Server side? Should I lock the table from inserts and updates (updating
won't be a problem, but inserting will be) before I start inserting rows? How
would I implement a system that would roll back all the inserts that have
occured in that batch if an error occurs?
Thank you very much,
Yohan MacDonaghLooking more into it, it looks like the best way is to use the DataSet and
DataAdapter objects in .NET.
Can anyone answer this, however: when a datasource is being updated via a
DataAdapter, is the table locked from inserts during the update?
"Yohan" wrote:
> My current project, which I am programming in .NET, requires me to insert a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows in
> each set MUST be kept together, and sets in each batch MUST be kept together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates (updating
> won't be a problem, but inserting will be) before I start inserting rows? How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
> Thank you very much,
> Yohan MacDonagh|||"Yohan" <Yohan@.discussions.microsoft.com> wrote in message
news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> My current project, which I am programming in .NET, requires me to insert
> a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
> in
> each set MUST be kept together, and sets in each batch MUST be kept
> together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates
> (updating
> won't be a problem, but inserting will be) before I start inserting rows?
> How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
A subset of rows must be defined in terms of shared column values. So give
each row a BatchID and a SetID.
David|||No such thing as an ordered INSERT. What you need to do is add a batch
number or othetr identifier to tell you what batches belong together.
--
David Portas
SQL Server MVP
--|||Unfortuantly, I cannot. I am limited by an existing (and very old) data
schema. There are no relationships. Each property of an object in .NET is a
new row in this schema (very weird, I know).
"David Browne" wrote:
> "Yohan" <Yohan@.discussions.microsoft.com> wrote in message
> news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> > My current project, which I am programming in .NET, requires me to insert
> > a
> > variable number of rows, which make up a set, in a specific order. A
> > collection of sets that are inserted one after the other is a batch. Rows
> > in
> > each set MUST be kept together, and sets in each batch MUST be kept
> > together.
> >
> > What is the best way to implement this, both on the .NET side and also the
> > SQL Server side? Should I lock the table from inserts and updates
> > (updating
> > won't be a problem, but inserting will be) before I start inserting rows?
> > How
> > would I implement a system that would roll back all the inserts that have
> > occured in that batch if an error occurs?
> A subset of rows must be defined in terms of shared column values. So give
> each row a BatchID and a SetID.
> David
>
>|||In that case please explain what you mean by a batch being "kept
together". Are you referring to an IDENTITY column here? Please post
DDL and sample data so that we can understand the problem:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--