Inside Common Table Expressions (CTE)

Hello all.
I would like to know exacly what improvements does CTE offers to compare its
performance to Table Variables for the perpouse of 'Paging' through data.
I curently build a table variable (i.e: @.return(rowid int identity, matchid
int)), populate it with matching ids and select from @.return where rowid
between N and N.
What advantages, if any does CTE have over the above solution?
What advantages, if any does CTE have over Cursor 'Paging'?
What details can you provide about the CTEs inner workings?
Thank you very much, in advance, for all your help!
- Eyal ZinderSome of what you are asking with regard to paging
may be answered here
http://www.aspfaq.com/show.asp?id=2120
There isn't a CTE sample but my own using
ROW_NUMBER()...OVER didn't perform significantly
better than the "RowCount" version.

Inserts per Second

SQL Server 2005;
What rank of inserts per second could be archived
on a modern desktop (1CPU/3GHz/1GB) hardware?
How this compare to a dedicated server hardware?
And most important question;
In a scenario when an applications writes received data to a database.
Which insert method would give the fastest results?
And which method do you recommended for actual use?
(bulk insert, asynchronous inserts, packaged inserts, or something else)
Thanks,
Mitja SemolicYour question is impossible to answer. There are too many variables.
Your "on a modern desktop" question --
Do you mean that you would have SQL Server installed on this "modern
desktop" and you would be inserting data locally?
Or do you mean that a client application would be running on this desktop
and inserting data to a SQL Server ON THE NETWORK?
How is your application written? How are your stored procedures or insert
statements? Do you SET NOCOUNT ON or do you receive the "n rows affected"
from your insert statement?
Did you drop the non-clustered indexes on your tables before running the
inserts?
If you want a super fast "just get the data in there" routine, BULK INSERT
is probably your fastest bet. If you want to be able to create an error
file you will need to use BCP. If you want to insert your data one row at a
time, read the file and call an insert stored procedure or build the T-SQL
within your application to perform the insert.
Keith Kratochvil
"Mitja Semolic" <mitja.semolic@.ensico.si> wrote in message
news:yTDcg.3320$oj5.1032258@.news.siol.net...
> SQL Server 2005;
> What rank of inserts per second could be archived
> on a modern desktop (1CPU/3GHz/1GB) hardware?
> How this compare to a dedicated server hardware?
> And most important question;
> In a scenario when an applications writes received data to a database.
> Which insert method would give the fastest results?
> And which method do you recommended for actual use?
> (bulk insert, asynchronous inserts, packaged inserts, or something else)
> Thanks,
> Mitja Semolic
>|||I was expecting an obsolete answer,
so I wondered if it was worth posting?
I don't want to confuse you with a simple question.
But are there any concrete benchmark result
that could showcase the SQL server performance
in different scenarios and different hardware?
Let me ask you a similar question:s
How fast a car can go, what is its track time?
How many frames per second can draw a graphics card for a specific game?
Are this questions also impossible to answer?
Some thinks should be left simple.
Mitja Semolic

Inserts per Second

SQL Server 2005;
What rank of inserts per second could be archived
on a modern desktop (1CPU/3GHz/1GB) hardware?
How this compare to a dedicated server hardware?
And most important question;
In a scenario when an applications writes received data to a database.
Which insert method would give the fastest results?
And which method do you recommended for actual use?
(bulk insert, asynchronous inserts, packaged inserts, or something else)
Thanks,
Mitja SemolicYour question is impossible to answer. There are too many variables.
Your "on a modern desktop" question --
Do you mean that you would have SQL Server installed on this "modern
desktop" and you would be inserting data locally?
Or do you mean that a client application would be running on this desktop
and inserting data to a SQL Server ON THE NETWORK?
How is your application written? How are your stored procedures or insert
statements? Do you SET NOCOUNT ON or do you receive the "n rows affected"
from your insert statement?
Did you drop the non-clustered indexes on your tables before running the
inserts?
If you want a super fast "just get the data in there" routine, BULK INSERT
is probably your fastest bet. If you want to be able to create an error
file you will need to use BCP. If you want to insert your data one row at a
time, read the file and call an insert stored procedure or build the T-SQL
within your application to perform the insert.
Keith Kratochvil
"Mitja Semolic" <mitja.semolic@.ensico.si> wrote in message
news:yTDcg.3320$oj5.1032258@.news.siol.net...
> SQL Server 2005;
> What rank of inserts per second could be archived
> on a modern desktop (1CPU/3GHz/1GB) hardware?
> How this compare to a dedicated server hardware?
> And most important question;
> In a scenario when an applications writes received data to a database.
> Which insert method would give the fastest results?
> And which method do you recommended for actual use?
> (bulk insert, asynchronous inserts, packaged inserts, or something else)
> Thanks,
> Mitja Semolic
>|||I was expecting an obsolete answer,
so I wondered if it was worth posting?
I don't want to confuse you with a simple question.
But are there any concrete benchmark result
that could showcase the SQL server performance
in different scenarios and different hardware?
Let me ask you a similar question:s
How fast a car can go, what is its track time?
How many frames per second can draw a graphics card for a specific game?
Are this questions also impossible to answer?
Some thinks should be left simple.
Mitja Semolic