Rows as Colums

I know this is possible in DB2 and Oracle, but what about for SQL-server 2005

1) select X number of rows from table1

2) I need colums for each row of table1 in a new table

3) As such, Select (select * from X where x.id = @.ID), a,b,c from table Y where y.Id = @.ID

And I dont want to use IfExists.

Thanks

DK

I don't think it is possible the way your are describing.

You could try the pivot method described in this article:

http://dotnet.sys-con.com/read/45543.htm

rows

how do i find number of rows ?

i search almost all through google but i did not find any answer to my question

1) number of rows per page

2) total number of rows

?

thanks a lot in advance

nobody |||

Number of rows in a dataset: you could use for instance the Count() aggregate function. See MSDN for more details: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_1l6b.asp

Number of rows on a page - you can use aggregate functions in the page header or footer that will count how often a certain textbox (e.g. located in list/table details) occurs on the current page. E.g. =Count(ReportItems!textbox1.Value)
See also: http://msdn2.microsoft.com/en-us/library/ms159677.aspx

-- Robert

RowNumber?

I want to add a record number to each record in my report.
I don't have any grouping on my report, so not sure if using
the rownumber() function works in that case'
C.The following should work:
=RowNumber(Nothing)sql

rownum equivalent ?

Hi,
Rownum returns the serial number for the records in Oracle.
Id there an equivalent for the same in SQL Server ?
select rownum from test_table;
Please advise,
Thanks
Samsqlserver has none, the clostest match is to add an identifier-column.

ROWLOCK usage

hi. i don't have much experience with locking using lock hints so wondered i
f
someone could help me with usage of ROWLOCK. i am writing a number of procs
which will perform validation on data prior to performing updates. i need
read consistency for the duration of these procs whilst guaranteeing maximum
concurrency. therefore i intend to use the following pattern:
create procedure MyProcedure
@.MyParam int
as
-- wrap in transaction
begin transaction
-- obtain lock
select 1
from MyTable (HOLDLOCK, UPDLOCK)
where MyPKColumn = @.MyParam
-- other operations here involving SELECTs on MyTable
-- perform update
update MyTable
set MyOthercolumn = 'NewValue'
where MyPKColumn = @.MyParam
-- release all locks
commit transaction
what i don't like about this is that i am beginning my transaction earlier
than i would prefer, but otherwise this appears to meet my requirements. is
this a good strategy, or is there a better way? what issues might i face?
many thanks
kh> what i don't like about this is that i am beginning my transaction earlier
> than i would prefer, but otherwise this appears to meet my requirements.
> is
> this a good strategy, or is there a better way? what issues might i face?
You are probably also locking pages (or perhaps even the entire table). IF
you take this path you might want to look into specifying ROWLOCK so that
you only lock the particular row that you are working on.
It seems like you are trying to reinvent the wheel.
You don't account for the situation where the data has changed since the
user initially retrieved the data.
User A retrieves the data for PKcol = 1
User B retrieves the data for PKcol = 1
User A goes to lunch
User B starts updating the data (within the application GUI)
Userr B hits "save" and writes the data to the database
User A comes back from lunch, finishes updating the data within the GUI, and
clicks save.
The data that User B entered is overwritten by User A.
One way around this problem is to pass the old and new values to the stored
procedure. The WHERE clause would use the primary key and it would compare
the @.old params to the data that is in the table. If @.@.rowcount = 0 the
data was different and the update did not happen.
Now that I have you worried about that type of concurrency issue, lets get
back to your validation question.
Can't you validate data within the GUI?
If not you should perform data validation (I assume that this is the "--
other operations here involving SELECTs on MyTable" outside of a
transaction. Heck, I don't even see why you need a transaction in this
stored procedure. It does not seem to buy you anything.
I don't know what type of data validation you need to do. Lets say that you
cannot have multiple UserNames or FileNames within a table. You could do
something like this before update statement. The RETURN will cause the
stored procedure to end. It will also pass back (via the return code) the
value within the parens.
--validation check
IF EXISTS (SELECT * FROM dbo.MyTable WHERE UserName = @.MyUserName )
BEGIN
RETURN (1)
END
--validation check
IF EXISTS (SELECT * FROM dbo.MyTable WHERE FileName= @.MyFileName )
BEGIN
RETURN (2)
END
--everything is a-ok, lets update
UPDATE dbo.MyTable SET MyOthercolumn = 'NewValue'
WHERE MyPKColumn = @.MyParam
RETURN (0)
Keith Kratochvil
"kh" <kh@.newsgroups.nospam> wrote in message
news:FBDE5CBC-4CB8-4CE2-8928-13BB3624F0C4@.microsoft.com...
> hi. i don't have much experience with locking using lock hints so wondered
> if
> someone could help me with usage of ROWLOCK. i am writing a number of
> procs
> which will perform validation on data prior to performing updates. i need
> read consistency for the duration of these procs whilst guaranteeing
> maximum
> concurrency. therefore i intend to use the following pattern:
> create procedure MyProcedure
> @.MyParam int
> as
> -- wrap in transaction
> begin transaction
> -- obtain lock
> select 1
> from MyTable (HOLDLOCK, UPDLOCK)
> where MyPKColumn = @.MyParam
> -- other operations here involving SELECTs on MyTable
> -- perform update
> update MyTable
> set MyOthercolumn = 'NewValue'
> where MyPKColumn = @.MyParam
> -- release all locks
> commit transaction
> what i don't like about this is that i am beginning my transaction earlier
> than i would prefer, but otherwise this appears to meet my requirements.
> is
> this a good strategy, or is there a better way? what issues might i face?
> many thanks
> kh
>|||keith. many thanks. some notes for clarity:

> You are probably also locking pages (or perhaps even the entire table). I
F
> you take this path you might want to look into specifying ROWLOCK so that
> you only lock the particular row that you are working on.
sorry, copy and paste error: the lock hints should of course be (HOLDLOCK,
ROWLOCK) and since I am selecting using the Primary Key I am (hopefully) onl
y
locking a single record.

> You don't account for the situation where the data has changed since the
> user initially retrieved the data. <snip>
there is no user access to the database accept via our app server. users can
go for lunch as often as they like, data will never be left in an uncommitte
d
state other than during stored procedure execution. the usage of ROWLOCK
hopefully avoids the situation you describe since it is only used during wel
l
defined units of execution.

> One way around this problem is to pass the old and new values to the store
d
> procedure. The WHERE clause would use the primary key and it would compar
e
> the @.old params to the data that is in the table. If @.@.rowcount = 0 the
> data was different and the update did not happen.
i am intentionally taking a 'pessimistic concurrency' approach here

> Can't you validate data within the GUI? <snip>
the validation involves selects and inserts into other tables (auditing,
etc) and relates to the requirements of downstream applications rather than
business rules within our own application. it is therefore not appropriate t
o
perform this validation within our UI or app server.

> Heck, I don't even see why you need a transaction in this
> stored procedure. It does not seem to buy you anything.
the only reason that the validation takes place within a transaction is so
that the ROWLOCK is held and i can guarantee that the data has not changed
between the beginning of the validation and the ultimate commit of this data
to the database.
kh|||> users can go for lunch as often as they like
That sounds great. I would like to take 3 or 4 lunches per day!

> the only reason that the validation takes place within a transaction is so
> that the ROWLOCK is held and i can guarantee that the data has not changed
> between the beginning of the validation and the ultimate commit of this
> data
> to the database.
That sounds reasonable. You know your system better than any of us. I
guess you are taking the correct approach.
Keith Kratochvil|||cheers keith. so in summary:
- i know my app better than you
- you know sql server better than me
- my users will shortly need a strict exercise regime
kh

rowcount help

i'm trying to get total rows found by query that uses top clause...
for example:
select top 10 myTable.* from myTable where myTable.number > 200
let's say there are 13 rows matching that condition, and by using
@.@.rowcount my result would be: 10.
is there any way to get total row count, without affecting the TOP
clause? i believe that the mysql equivalent would be
SQL_CALC_FOUND_ROWS().
tnx...> is there any way to get total row count, without affecting the TOP
> clause?
One method is with a subquery:
SELECT TOP 10
myTable.*,
(SELECT COUNT(*)
FROM myTable
WHERE myTable.number > 200) AS TotalRows
FROM myTable
WHERE myTable.number > 200
Hope this helps.
Dan Guzman
SQL Server MVP
"D.B." <dejan.bukovic@.gmail.com> wrote in message
news:1148128944.554703.296710@.38g2000cwa.googlegroups.com...
> i'm trying to get total rows found by query that uses top clause...
> for example:
> select top 10 myTable.* from myTable where myTable.number > 200
>
> let's say there are 13 rows matching that condition, and by using
> @.@.rowcount my result would be: 10.
>
> is there any way to get total row count, without affecting the TOP
> clause? i believe that the mysql equivalent would be
> SQL_CALC_FOUND_ROWS().
>
> tnx...
>|||well, it helped...
but i'm worried about the execution time when using full-text search...
anyway, thanx for your help...|||You could select the entire set into a temp table or table variable.
Then, get the count of the temp table, then select the number you want.
e.g.
declare @.tAllRows as table(...)
insert into @.tAllRows
select ... from myTable where myTable.number>200
declare @.iTotalCount int
set @.iTotalCount = @.@.rowcount
select top 10 myTable.*, @.@.rowcount from @.tAllRows
While I usually try to avoid temp tables and table variables,
_sometimes_ they help a lot with performance. Benchmarking would be a
good idea, though, as my approach could be a disaster.
Also, are you paging based on an identity field? If so, you might not
get the results you expect when the numbers are not continuous.|||err...
select top 10 myTable.*, @.iTotalCount from @.tAllRows
my bad...

rowcount help

i'm trying to get total rows found by query that uses top clause...
for example:
select top 10 myTable.* from myTable where myTable.number > 200
let's say there are 13 rows matching that condition, and by using
@.@.rowcount my result would be: 10.
is there any way to get total row count, without affecting the TOP
clause? i believe that the mysql equivalent would be
SQL_CALC_FOUND_ROWS().
tnx...> is there any way to get total row count, without affecting the TOP
> clause?
One method is with a subquery:
SELECT TOP 10
myTable.*,
(SELECT COUNT(*)
FROM myTable
WHERE myTable.number > 200) AS TotalRows
FROM myTable
WHERE myTable.number > 200
--
Hope this helps.
Dan Guzman
SQL Server MVP
"D.B." <dejan.bukovic@.gmail.com> wrote in message
news:1148128944.554703.296710@.38g2000cwa.googlegroups.com...
> i'm trying to get total rows found by query that uses top clause...
> for example:
> select top 10 myTable.* from myTable where myTable.number > 200
>
> let's say there are 13 rows matching that condition, and by using
> @.@.rowcount my result would be: 10.
>
> is there any way to get total row count, without affecting the TOP
> clause? i believe that the mysql equivalent would be
> SQL_CALC_FOUND_ROWS().
>
> tnx...
>|||well, it helped...
but i'm worried about the execution time when using full-text search...
anyway, thanx for your help...|||You could select the entire set into a temp table or table variable.
Then, get the count of the temp table, then select the number you want.
e.g.
declare @.tAllRows as table(...)
insert into @.tAllRows
select ... from myTable where myTable.number>200
declare @.iTotalCount int
set @.iTotalCount = @.@.rowcount
select top 10 myTable.*, @.@.rowcount from @.tAllRows
While I usually try to avoid temp tables and table variables,
_sometimes_ they help a lot with performance. Benchmarking would be a
good idea, though, as my approach could be a disaster.
Also, are you paging based on an identity field? If so, you might not
get the results you expect when the numbers are not continuous.|||err...
select top 10 myTable.*, @.iTotalCount from @.tAllRows
my bad...

rowcount help

i'm trying to get total rows found by query that uses top clause...

for example:
select top 10 myTable.* from myTable where myTable.number > 200

let's say there are 13 rows matching that condition, and by using
@.@.rowcount my result would be: 10.

is there any way to get total row count, without affecting the TOP
clause? i believe that the mysql equivalent would be
SQL_CALC_FOUND_ROWS().

tnx...I answered this question in microsoft.public.sqlserver.server.

It's bad etiquette to post the same question independently to multiple
groups because that causes unnecessary duplication of effort by persons
trying to help. If you have a question appropriate for multiple groups,
specify all groups in a single post so that all responses appear in all
groups.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"D.B." <dejan.bukovic@.gmail.com> wrote in message
news:1148128056.678229.59640@.i39g2000cwa.googlegro ups.com...
> i'm trying to get total rows found by query that uses top clause...
> for example:
> select top 10 myTable.* from myTable where myTable.number > 200
>
> let's say there are 13 rows matching that condition, and by using
> @.@.rowcount my result would be: 10.
>
> is there any way to get total row count, without affecting the TOP
> clause? i believe that the mysql equivalent would be
> SQL_CALC_FOUND_ROWS().
>
> tnx...sql

Rowcount and SQLDataReader

Hi, from what I can find, there isn't a way to get the number of rows returned from a SQLDataReader command. Is this correct? If so, is there a way around this? My SQLDataReader command is as follows:

Dim commandIndAsNew System.Data.OleDb.OleDbDataAdapter(strQueryCombined, connInd)

Dim commandSQLAsNew SqlCommand("GetAssetList2", connStringSQL)

Dim resultDSAsNew Data.DataSet()

'// Fill the dataset with values

commandInd.Fill(resultDS)

'// Get the XML values of the dataset to send to SQL server and run a new query

Dim strXMLAsString = resultDS.GetXml()

Dim xmlFileListAs SqlParameter

Dim strContainsClauseAs SqlParameter

'// Create and execute the search against SQL Server

connStringSQL.Open()

commandSQL.CommandType = Data.CommandType.StoredProcedure

commandSQL.Parameters.Add("@.xmlFileList", Data.SqlDbType.VarChar, 1000).Value = strXML

commandSQL.Parameters.Add("@.strContainsClause", Data.SqlDbType.VarChar, 1000).Value = strContainsConstruct

Dim sqlReaderSourceAs SqlDataReader = commandSQL.ExecuteReader()

results.DataSource = sqlReaderSource

results.DataBind()

connStringSQL.Close()

And the stored procedure is such:

DROPPROC dbo.GetAssetList2;

GO

CREATEPROC dbo.GetAssetList2

(

@.xmlFileListvarchar(1000),

@.strContainsClausevarchar(1000)

)

AS

BEGIN

SETNOCOUNTON

DECLARE @.intDocHandleint

EXECsp_xml_preparedocument @.intDocHandleOUTPUT, @.xmlFileList

SELECTDISTINCT

AssetsMaster.AssetMasterUID,

SupportedFiles.AssetPath,

FROM

AssetsMaster,

OPENXML(@.intDocHandle,'/NewDataSet/Table',2)WITH(FILENAMEvarchar(256))AS x,

SupportedFiles

WHERE

AssetsMaster.AssetFileName= x.FILENAME

AND AssetsMaster.Extension= SupportedFiles.Extension

UNION

SELECTDISTINCT

AssetsMaster.AssetMasterUID,

SupportedFiles.AssetPath,

FROM

AssetsMaster,

OPENXML(@.intDocHandle,'/NewDataSet/Table',2)WITH(FILENAMEvarchar(256))AS x,

SupportedFiles

WHERE

AssetsMaster.AssetFileName<> x.FILENAME

ANDCONTAINS((Description, Keywords), @.strContainsClause)

AND AssetsMaster.Extension= SupportedFiles.Extension

ORDERBY AssetsMaster.DownloadsDESC

EXECsp_xml_removedocument @.intDocHandle

END

GO

How can I access the number of rows returned by this stored procedure?

Thanks,

James

I would suggest changing your datareader into a dataset. Then you can bind to the dataset, and also check how many rows are in it.|||

Thanks Motley. What is wrong with this? I am calling a stored SELECT procedure. It says there is a syntax error.

Dim connStringSQLAsNew SqlConnection("Data Source=*;Database=*;User ID=*;Password=*;Trusted_Connection=*")

'// Create the new OLEDB connection to Indexing Service

Dim commandSQLAsNew SqlCommand("GetAssetList2", connStringSQL)

Dim resultDSAsNew Data.DataSet()

Dim resultDAAsNew SqlDataAdapter()

'// Fill the dataset with values from the previous query

commandInd.Fill(resultDS)

'// Get the XML values of the dataset to send to SQL server and run a new query

Dim strXMLAsString = resultDS.GetXml()

Dim xmlFileListAs SqlParameter

Dim strContainsClauseAs SqlParameter

'// Create and execute the search against SQL Server

commandSQL.Parameters.Add("@.xmlFileList", Data.SqlDbType.VarChar, 1000).Value = strXML

commandSQL.Parameters.Add("@.strContainsClause", Data.SqlDbType.VarChar, 1000).Value = strContainsConstruct

resultDA.SelectCommand = commandSQL

resultDA.Fill(resultDS)

Dim sourceAsNew Data.DataView(resultDS.Tables(0))

resultCount.Text = source.Count.ToString

results.DataSource = source

results.DataBind()

|||

Never mind, I got it by added

commandSQL.CommandType = Data.CommandType.StoredProcedure

But now I get one extra row returned per query. It's empty. Any ideas?

RowCount

Hi,

want to get the number of rows i'm retrieving from a source. This count should be written as " No: of roes retrieved" + varname

I have used OleDbSource, RowCount,Script [ To write in a file ]. Rows is the package level variable name used in rowcount. when i do this way it always writes as 0 in the file.

[code in Script]

Dim sw As New StreamWriter("D:\Vijay1.txt")

s = Variables.Rows

sw.WriteLine(s.ToString)

sw.close

[/Code]

Can anyone help on this

you should store the row count in an ssis variable, then retreive the value from the script...|||

Hi,

I have done the same way. you can see in the code i have added. Rows is the package level variable I have used. In script I used Variables.Rows to access the value. when i write into a file it rights as 0

Thanks

|||

Can you try as follows:

Dim sw As New StreamWriter("D:\Vijay1.txt")

sw.WriteLine(Dts.Variables("Rows").Value.ToString())

sw.close()

Thanks,
Loonysan

|||

ManjuVijay wrote:

Hi,

I have done the same way. you can see in the code i have added. Rows is the package level variable I have used. In script I used Variables.Rows to access the value. when i write into a file it rights as 0

Thanks

the code should be:

s = Dts.Variables("Rows").Value

|||

Hi,

I am getting error saying DTS is not declared.

Thanks

|||

Hi,

I want to know whether i'm missing anyother thing.

I beleive I have to set only the variable name in RowCount. Any thing else I have to do?

|||

If i use script task it works properly

why i am not able to do so in script transform component

|||

The Script Task and Script Component are very different beasts. It is wrong to assume that because you can do something in one then you can also do the same in the other.

Its also true to say there are different ways of doing the same thing. For example, the syntax for accessing variables in the script component is different to that for accessig them in the script task.

What exactly are you unable to do?

-Jamie

|||> If i use script task it works properly

>why i am not able to do so in script transform component

The script component is used within a DataFlow task. The DataFlow task "snapshots" a variable value when it begins execution and cannot modify the variable until it has completed.

So, your row count = 0 at the beginning of execution. Your script component accesses the "snapshot" value and writes out 0.

The RowCount component only updates the row count variable, when execution of the data flow has completed. Your script task accesses the value after this and writes out the final rowcount.

Why does SSIS snapshot variable values? Well imagine a conditional split where the data is split on a variable value. If that could change during execution of a data flow, the behaviour of the split would be unpredictable - rows would be directed depending on whether they just happened to reach the split before or after the variable changed.

Donald

sql

Row size in SS7

Hello,
I created a table and got the following:
The total row size (15676) for table 'MyTable' exceeds the maximum number of
bytes per row (8060). Rows that exceed the maximum number of bytes will not
be added.
I have a 5 fields that are set to varchar (2000) plus some others. Should I
use the text data type? I was reading that the text data is not stored in
the table but in a separate page. Will this be a problem in doing searches
and pushing data to the web using stored procedures and ASP?
--
Thanks in advance,
StevenUsing the text datatype won't cause you any problems searching or pushing
data; the data's physical storage is of no concern to you when querying the
data.
Whether or not you SHOULD use it is an architectural question that I can't
answer without more information about what kind of database you're building
and what the columns will be used for.
"Steven K" <skaper@.troop.com> wrote in message
news:O82Zp1XpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I created a table and got the following:
> The total row size (15676) for table 'MyTable' exceeds the maximum number
of
> bytes per row (8060). Rows that exceed the maximum number of bytes will
not
> be added.
> I have a 5 fields that are set to varchar (2000) plus some others. Should
I
> use the text data type? I was reading that the text data is not stored in
> the table but in a separate page. Will this be a problem in doing
searches
> and pushing data to the web using stored procedures and ASP?
> --
> Thanks in advance,
> Steven
>|||Using varchar datatype you can have a "declared" row length of larger than
8060. As long as the total combined REAL DATA length does not exceed the
limit, you are fine -- with some composite index you may receive a warning.
However, when the real length of data for a record exceeds the limit, you
will have to use text, ntext or image type. Have a look at "Managing ntext,
text, and image Data" in BOL.
"Steven K" <skaper@.troop.com> wrote in message
news:O82Zp1XpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I created a table and got the following:
> The total row size (15676) for table 'MyTable' exceeds the maximum number
of
> bytes per row (8060). Rows that exceed the maximum number of bytes will
not
> be added.
> I have a 5 fields that are set to varchar (2000) plus some others. Should
I
> use the text data type? I was reading that the text data is not stored in
> the table but in a separate page. Will this be a problem in doing
searches
> and pushing data to the web using stored procedures and ASP?
> --
> Thanks in advance,
> Steven
>sql

Row sequence number

Hello all,

Im currently using a SQL Serve 2K. Would like to do a select
which returns the row number - this should not be physically stored in
the database. So for example, I would like to do a query against the
CUSTOMER table and receive:

* rowID || name
1 Evander
2 Ron
3 Scoth
4 Jane

I dont want to store the ID, because if I change the order by
clause, the sequence may modifiy, and, for another example, having the
same set of data, I would receive:

* rowID || name
1 Scoth
2 Ron
3 Jane
4 Evander

could someone help me ?

best regards,
Evandrohttp://support.microsoft.com/defaul...b;EN-US;q186133

--
Anith|||Use Front end application to number the result

Madhivanan

Row sequence number

Hello all,

Im currently using a SQL Serve 2K. Would like to do a select
which returns the row number - this should not be physically stored in
the database. So for example, I would like to do a query against the
CUSTOMER table and receive:

* rowID || name
1 Evander
2 Ron
3 Scoth
4 Jane

I dont want to store the ID, because if I change the order by
clause, the sequence may modifiy, and, for another example, having the
same set of data, I would receive:

* rowID || name
1 Scoth
2 Ron
3 Jane
4 Evander

could someone help me ?

best regards,
EvandroLet's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.

>> could someone help me ? <<

You can help yourself by doing about one week's worth of reading on
RDBMS. You are making a fool of yourself by not knowing the basics.|||SQL 2000 does not have this pseudo-column. It is introduced in 2005.
The only way I know how to way around it is to dump your query into
temp. table that has anextra column (let's call it rowID ) which is an
auto-increment.|||Sergey (afanas01@.gmail.com) writes:
> SQL 2000 does not have this pseudo-column. It is introduced in 2005.
> The only way I know how to way around it is to dump your query into
> temp. table that has anextra column (let's call it rowID ) which is an
> auto-increment.

Neither does SQL 2005 have any pseudo-column. row_number() is a function,
and you can set it up so that it restarts on some defined partition.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Row performance

Hi.
I have a simple question.
What is the maximum number of rows in a table before MS SQL takes more
time to execute a query or the performance will slow down ' Is it
possible that the number is 1 000 000 ? If I remember, I saw that on
the web but I'm not sure. Consider that my table have been indexed.
Am I right or wrong '
Thanks.
Jonathan Chretien
Analyst/ProgrammerI would also expect for a non-indexed table, that performance would scale
linearly corresponding directly to the number of records in the table.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
news:3F27BEA0.41743AB0@.amisco.moc...
> Hi.
> I have a simple question.
> What is the maximum number of rows in a table before MS SQL takes more
> time to execute a query or the performance will slow down ' Is it
> possible that the number is 1 000 000 ? If I remember, I saw that on
> the web but I'm not sure. Consider that my table have been indexed.
> Am I right or wrong '
>
> Thanks.
>
> Jonathan Chretien
> Analyst/Programmer
>|||Hi.
My table is correctly indexed. I have only one index and my query is base on the
same field of my index. This table come from an AS400 and when I execute this
query on the AS400, it takes 0 to 1 secondes. When I execute the same query on
my MS SQL Server, it takes 4 to 5 secondes to be execute. The AS400 is more load
then my MS SQL Server. I execute the query when the server is not load,
approximatly an average of 2 to 5% and the maximum peak that I receive from my
SQL Server is 50%.
Specification of my MS SQL Server:
Version: MS SQL Server 7.0
CPU: 2 PII Xeon 550
Memory: 512 meg
Disk: 40 gig
Database size 1.6 gig
Avg CPU utilization: Between 10 to 20 %
Jonathan Chretien
Analyst/Programmer
Wayne Snyder a écrit :
> I would also expect for a non-indexed table, that performance would scale
> linearly corresponding directly to the number of records in the table.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it community
> of SQL Server professionals.
> www.sqlpass.org
> "Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
> news:3F27BEA0.41743AB0@.amisco.moc...
> > Hi.
> >
> > I have a simple question.
> >
> > What is the maximum number of rows in a table before MS SQL takes more
> > time to execute a query or the performance will slow down ' Is it
> > possible that the number is 1 000 000 ? If I remember, I saw that on
> > the web but I'm not sure. Consider that my table have been indexed.
> >
> > Am I right or wrong '
> >
> >
> > Thanks.
> >
> >
> >
> > Jonathan Chretien
> > Analyst/Programmer
> >|||Yes.
Query plan is using my index and have a cost of 81%.
Jonathan Chretien
Analyst/Programmer
Gert-Jan Strik a écrit :
> Check the execution plan to see if SQL-Server is actually using the
> index. There might be some reason why the index is disqualified. For
> example when your statistics are out of date, or when your predicate is
> not SARGuable.
> Hope this helps,
> Gert-Jan
> Jonathan Chretien wrote:
> >
> > Hi.
> >
> > My table is correctly indexed. I have only one index and my query is base on the
> > same field of my index. This table come from an AS400 and when I execute this
> > query on the AS400, it takes 0 to 1 secondes. When I execute the same query on
> > my MS SQL Server, it takes 4 to 5 secondes to be execute. The AS400 is more load
> > then my MS SQL Server. I execute the query when the server is not load,
> > approximatly an average of 2 to 5% and the maximum peak that I receive from my
> > SQL Server is 50%.
> >
> > Specification of my MS SQL Server:
> > Version: MS SQL Server 7.0
> > CPU: 2 PII Xeon 550
> > Memory: 512 meg
> > Disk: 40 gig
> > Database size 1.6 gig
> > Avg CPU utilization: Between 10 to 20 %
> >
> > Jonathan Chretien
> > Analyst/Programmer
> >
> > Wayne Snyder a écrit :
> >
> > > I would also expect for a non-indexed table, that performance would scale
> > > linearly corresponding directly to the number of records in the table.
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Computer Education Services Corporation (CESC), Charlotte, NC
> > > www.computeredservices.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it community
> > > of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
> > > news:3F27BEA0.41743AB0@.amisco.moc...
> > > > Hi.
> > > >
> > > > I have a simple question.
> > > >
> > > > What is the maximum number of rows in a table before MS SQL takes more
> > > > time to execute a query or the performance will slow down ' Is it
> > > > possible that the number is 1 000 000 ? If I remember, I saw that on
> > > > the web but I'm not sure. Consider that my table have been indexed.
> > > >
> > > > Am I right or wrong '
> > > >
> > > >
> > > > Thanks.
> > > >
> > > >
> > > >
> > > > Jonathan Chretien
> > > > Analyst/Programmer
> > > >|||Jonathan,
1. Is it performing an index SEEK (good) or index SCAN (bad)?
2. Is it a clustered or non-clustered index?
Gert-Jan
Jonathan Chretien wrote:
> Yes.
> Query plan is using my index and have a cost of 81%.
> Jonathan Chretien
> Analyst/Programmer
> Gert-Jan Strik a écrit :
> > Check the execution plan to see if SQL-Server is actually using the
> > index. There might be some reason why the index is disqualified. For
> > example when your statistics are out of date, or when your predicate is
> > not SARGuable.
> >
> > Hope this helps,
> > Gert-Jan
> >
> > Jonathan Chretien wrote:
> > >
> > > Hi.
> > >
> > > My table is correctly indexed. I have only one index and my query is base on the
> > > same field of my index. This table come from an AS400 and when I execute this
> > > query on the AS400, it takes 0 to 1 secondes. When I execute the same query on
> > > my MS SQL Server, it takes 4 to 5 secondes to be execute. The AS400 is more load
> > > then my MS SQL Server. I execute the query when the server is not load,
> > > approximatly an average of 2 to 5% and the maximum peak that I receive from my
> > > SQL Server is 50%.
> > >
> > > Specification of my MS SQL Server:
> > > Version: MS SQL Server 7.0
> > > CPU: 2 PII Xeon 550
> > > Memory: 512 meg
> > > Disk: 40 gig
> > > Database size 1.6 gig
> > > Avg CPU utilization: Between 10 to 20 %
> > >
> > > Jonathan Chretien
> > > Analyst/Programmer
> > >
> > > Wayne Snyder a écrit :
> > >
> > > > I would also expect for a non-indexed table, that performance would scale
> > > > linearly corresponding directly to the number of records in the table.
> > > >
> > > > --
> > > > Wayne Snyder, MCDBA, SQL Server MVP
> > > > Computer Education Services Corporation (CESC), Charlotte, NC
> > > > www.computeredservices.com
> > > > (Please respond only to the newsgroups.)
> > > >
> > > > I support the Professional Association of SQL Server (PASS) and it community
> > > > of SQL Server professionals.
> > > > www.sqlpass.org
> > > >
> > > > "Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
> > > > news:3F27BEA0.41743AB0@.amisco.moc...
> > > > > Hi.
> > > > >
> > > > > I have a simple question.
> > > > >
> > > > > What is the maximum number of rows in a table before MS SQL takes more
> > > > > time to execute a query or the performance will slow down ' Is it
> > > > > possible that the number is 1 000 000 ? If I remember, I saw that on
> > > > > the web but I'm not sure. Consider that my table have been indexed.
> > > > >
> > > > > Am I right or wrong '
> > > > >
> > > > >
> > > > > Thanks.
> > > > >
> > > > >
> > > > >
> > > > > Jonathan Chretien
> > > > > Analyst/Programmer
> > > > >

Row order

Hi,

I want to show rows order (row number) in my query result set.
How can I do?

Example:

SELECT GetRowNumber(), Field1 FROM MY_TABLE WHERE ...

GetRowNumber(), Field1

1 Value1
2 Value2
3 Value4
.....

GetRowNumber() is a sp, udf, anyway

If you are using SQL Server 2005 consider the ROW_NUMBER() feature; look this up in books online. It is a useful feature that is new to SQL Server 2005.|||

Here is an example using the row_number() function:

USE Northwind
GO

SELECT
RowNumber = row_number() OVER ( ORDER BY LastName, FirstName ),
FirstName,
LastName
FROM Employees

|||

I'm using SQL server 2000

|||

These resources should give you the help you need.

Row Number (or Rank) from a SELECT Transact-SQL statement (includes Paging)
http://support.microsoft.com/default.aspx?scid=kb;en-us;186133
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/03/4945.aspx
http://www.projectdmx.com/tsql/ranking.aspx

|||What is the purpose of the row number? Why do you need it? Is it local to the results returned from the query? If so, why don't you generate it on the client-side? It is much easier to do and consumes less resources. You can use ROW_NUMBER or temporary table with identity column or sub-query to generate sequence numbers. But performance will not be that good with any of these methods so it depends on why you need to generate it in the first place?|||

Arnie,

This info is very helpful..however, i do have another question. How do i set the data types into varchar (6) for the row number? for example:

row firstname lastname

000001 richie rich

000002 john anderson

000003 will smith

000004 amber white

.

.

.

.

000010 william smith

instead of:

row firstname lastname

1 richie rich

2 john anderson

3 will smith

4 amber white

.

.

.

.

.

10 william smith

|||

Something like this:

Code Snippet

SELECT right(( '000000' + cast( row as varchar(6))), 6 )

|||

Thanks Arnie,

I have another issue to discuss. I have two different data to be entered into one sql table. I have done the first one which has let say, 2000 records. the second data needs to be entered into the table right after the first one.

let's say,

First data comes from a table named Service,

Second data comes from a table named File.

both different tables need to be mapped into one table with a primary key created with row order.

I have mapped Service with primary key 1 until 2000. Now, i want to continue from 2001 for File.

How do i generate a primary key that would continue from what i have left sequentially?

Thanks in advance,

Jul.

|||

One option would be to have an IDENTITY column for the new table, and set the start value as 2001.

Another option would be to use the same type of numbering query as posted above, and add 2000 to the values. Something like this:

Code Snippet

USE Northwind
GO

SELECT
c1.ContactName,
Rank = ( COUNT(*) + 2000 )
FROM Customers c1
JOIN Customers c2
ON c2.ContactName <= c1.ContactName
GROUP BY c1.ContactName
ORDER BY c1.ContactName;

|||

It gives me an error:

Column 'FBSourceTable..Case.CaseID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

|||Please post your entire query.

Row order

Hi,

I want to show rows order (row number) in my query result set.
How can I do?

Example:

SELECT GetRowNumber(), Field1 FROM MY_TABLE WHERE ...

GetRowNumber(), Field1

1 Value1
2 Value2
3 Value4
.....

GetRowNumber() is a sp, udf, anyway

If you are using SQL Server 2005 consider the ROW_NUMBER() feature; look this up in books online. It is a useful feature that is new to SQL Server 2005.|||

Here is an example using the row_number() function:

USE Northwind
GO

SELECT
RowNumber = row_number() OVER ( ORDER BY LastName, FirstName ),
FirstName,
LastName
FROM Employees

|||

I'm using SQL server 2000

|||

These resources should give you the help you need.

Row Number (or Rank) from a SELECT Transact-SQL statement (includes Paging)
http://support.microsoft.com/default.aspx?scid=kb;en-us;186133
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/03/4945.aspx
http://www.projectdmx.com/tsql/ranking.aspx

|||What is the purpose of the row number? Why do you need it? Is it local to the results returned from the query? If so, why don't you generate it on the client-side? It is much easier to do and consumes less resources. You can use ROW_NUMBER or temporary table with identity column or sub-query to generate sequence numbers. But performance will not be that good with any of these methods so it depends on why you need to generate it in the first place?|||

Arnie,

This info is very helpful..however, i do have another question. How do i set the data types into varchar (6) for the row number? for example:

row firstname lastname

000001 richie rich

000002 john anderson

000003 will smith

000004 amber white

.

.

.

.

000010 william smith

instead of:

row firstname lastname

1 richie rich

2 john anderson

3 will smith

4 amber white

.

.

.

.

.

10 william smith

|||

Something like this:

Code Snippet

SELECT right(( '000000' + cast( row as varchar(6))), 6 )

|||

Thanks Arnie,

I have another issue to discuss. I have two different data to be entered into one sql table. I have done the first one which has let say, 2000 records. the second data needs to be entered into the table right after the first one.

let's say,

First data comes from a table named Service,

Second data comes from a table named File.

both different tables need to be mapped into one table with a primary key created with row order.

I have mapped Service with primary key 1 until 2000. Now, i want to continue from 2001 for File.

How do i generate a primary key that would continue from what i have left sequentially?

Thanks in advance,

Jul.

|||

One option would be to have an IDENTITY column for the new table, and set the start value as 2001.

Another option would be to use the same type of numbering query as posted above, and add 2000 to the values. Something like this:

Code Snippet

USE Northwind
GO

SELECT
c1.ContactName,
Rank = ( COUNT(*) + 2000 )
FROM Customers c1
JOIN Customers c2
ON c2.ContactName <= c1.ContactName
GROUP BY c1.ContactName
ORDER BY c1.ContactName;

|||

It gives me an error:

Column 'FBSourceTable..Case.CaseID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

|||Please post your entire query.

Row numbers in select statements

Is there an easy way to do get a row number in each row in a select
statement?
Something like:
select rownumber,description price from lineorder order by row number
Thanks,
TomTshad,
Yes.
See:
How to dynamically number rows in a SELECT statement.
http://support.microsoft.com/defaul...kb;en-us;186133
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||Why can't the presentation tier do this? It's the only place that HAS to
loop through each row, one by one. Now you're forcing the database to do it
too, and performance can only suffer for it.
http://www.aspfaq.com/2427
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eV$hgGZ2FHA.636@.TK2MSFTNGP10.phx.gbl...
> Tshad,
> Yes.
> See:
> How to dynamically number rows in a SELECT statement.
> http://support.microsoft.com/defaul...kb;en-us;186133
I tried that:
Select rank=count(*),
Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2 then
r.ItemName end as Description,
Price, PurchaseQty, TotalPrice = Price * PurchaseQty
from PurchaseDetail pd
join PurchaseMaster pm on (pd.PurchaseMasterID = pm.PurchaseMasterID)
left JOIN JobPostingPrices j on (ProductID = JobPostingPriceID)
left JOIN ResumeAccessPrices r on (ProductID = ResumeAccessPriceID)
where CompanyID = 153973
group by Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2
then r.ItemName end,Price,PurchaseQty,Price * PurchaseQty
order by 1
But in my 8 rows returned I got (1,1,1,1,1,1,2,2)'
Is the Joins causing me a problem?
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OUJZsHZ2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Why can't the presentation tier do this? It's the only place that HAS to
> loop through each row, one by one. Now you're forcing the database to do
> it too, and performance can only suffer for it.
Actually, it can.
But that is an extra step,as I am binding it to a datagrid.
Tom
> http://www.aspfaq.com/2427
>
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
>|||If you are using a stored procedure, you can insert your result into a
temporary table that has an identity column, and then select the final
result from that table. For example:
create table #myresult
(
[Seq] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [int] ,
[Col2] [int]
)
insert into #myresult select Col1, Col2 from MyTable
select Seq, Col1, Col2 from MyTable
drop table #MyResult
My philosophy is that rules of database normalization apply only to physical
tables and not to query results.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||> My philosophy is that rules of database normalization apply only to
> physical tables and not to query results.
FWIW, my objection to doing this in the database has nothing to do with
normalization at all, but with the extra work required (whether using a
subquery, or copying all the data to a separate table first). A simple
counter at the presentation layer is the very least impact on performance,
since it has to loop through all rows and display them one by one anyway.
A|||This assumes that the result is being consumed in such a way that the
developer can add the additional computed column. It may be exported from
DTS to a table or file, executed only in Query Analyzer and pasted into
email, or bound to a datagrid.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:utST6ik2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> FWIW, my objection to doing this in the database has nothing to do with
> normalization at all, but with the extra work required (whether using a
> subquery, or copying all the data to a separate table first). A simple
> counter at the presentation layer is the very least impact on performance,
> since it has to loop through all rows and display them one by one anyway.
> A
>|||> This assumes that the result is being consumed in such a way that the
> developer can add the additional computed column. It may be exported from
> DTS to a table or file, executed only in Query Analyzer and pasted into
> email, or bound to a datagrid.
Yep, that's why I asked, "why can't the presentation tier do this?" and did
not say "ONLY the presentation tier can do this!"|||But do you think that only the presentation tier *should* do this?
;-)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhisP5k2FHA.1572@.TK2MSFTNGP10.phx.gbl...
> Yep, that's why I asked, "why can't the presentation tier do this?" and
> did not say "ONLY the presentation tier can do this!"
>

Row Numbers for Groups

I am trying to number a group and I am using the following to do so:
=RunningValue(Fields!DBPROJECTID.Value, CountDistinct, Nothing)
However, I have one little problem. How do I clear out the value and start
over? This is what I want my report to look like:
Group 1 Header
1. Group 2 Header
Detail
2. Group 2 Header
Detail
Group 1 Header
1. Group 2 Header
Detail
Instead I get:
Group 1 Header
1. Group 2 Header
Detail
2. Group 2 Header
Detail
Group 1 Header
3. Group 2 Header
Detail
Any suggestions?Assuming your Group 1 is called "Group1", you can use a RunningValue with an
explicit scope specified:
=RunningValue(Fields!DBPROJECTID.Value, CountDistinct, "Group1")
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"wbarron" <wbarron@.discussions.microsoft.com> wrote in message
news:6018836A-2B4B-4A3B-B637-62805B443285@.microsoft.com...
>I am trying to number a group and I am using the following to do so:
> =RunningValue(Fields!DBPROJECTID.Value, CountDistinct, Nothing)
> However, I have one little problem. How do I clear out the value and
> start
> over? This is what I want my report to look like:
> Group 1 Header
> 1. Group 2 Header
> Detail
> 2. Group 2 Header
> Detail
> Group 1 Header
> 1. Group 2 Header
> Detail
> Instead I get:
> Group 1 Header
> 1. Group 2 Header
> Detail
> 2. Group 2 Header
> Detail
> Group 1 Header
> 3. Group 2 Header
> Detail
> Any suggestions?
>|||Thanks! After I posted, it dawned on me that I needed to specify the scope.
Thanks for the response.
Wendy
"Robert Bruckner [MSFT]" wrote:
> Assuming your Group 1 is called "Group1", you can use a RunningValue with an
> explicit scope specified:
> =RunningValue(Fields!DBPROJECTID.Value, CountDistinct, "Group1")
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "wbarron" <wbarron@.discussions.microsoft.com> wrote in message
> news:6018836A-2B4B-4A3B-B637-62805B443285@.microsoft.com...
> >I am trying to number a group and I am using the following to do so:
> >
> > =RunningValue(Fields!DBPROJECTID.Value, CountDistinct, Nothing)
> >
> > However, I have one little problem. How do I clear out the value and
> > start
> > over? This is what I want my report to look like:
> >
> > Group 1 Header
> > 1. Group 2 Header
> > Detail
> > 2. Group 2 Header
> > Detail
> > Group 1 Header
> > 1. Group 2 Header
> > Detail
> >
> > Instead I get:
> >
> > Group 1 Header
> > 1. Group 2 Header
> > Detail
> > 2. Group 2 Header
> > Detail
> > Group 1 Header
> > 3. Group 2 Header
> > Detail
> >
> > Any suggestions?
> >
> >
>
>

Row Numbers for Group

I have a table with 2 Groups and I want to number only Group2 with row
numbers. I put in a RowNumber(Nothing) function, and i'm getting something
like this:
Group 1
11 Group 2
14 Group 2
28 Group 2
Group 1
35 Group 2
etc...
What can I do to get the numbers to show up correctly?
Thanks!problem solved!
=RunningValue(Fields!fieldname.Value, CountDistinct, Nothing)
"jmann" wrote:
> I have a table with 2 Groups and I want to number only Group2 with row
> numbers. I put in a RowNumber(Nothing) function, and i'm getting something
> like this:
> Group 1
> 11 Group 2
> 14 Group 2
> 28 Group 2
> Group 1
> 35 Group 2
> etc...
> What can I do to get the numbers to show up correctly?
> Thanks!sql

Row numbering unpredictable

Hi,
I need to create a stored procedure that returns the row number (for
paging) AFTER the data has been sorted with an order by. The source is
a view. The code I have is:
SELECT rownum = IDENTITY(1,1,bigint), *
INTO #tmp
FROM viewName
ORDER BY CustomerName -- field name I'm ordering by
When I recieve the results back, the rownum column is not the same
order as the customername (it jumps half way to a high number?!?),
which means I can't page it based on rownum without jumping all over
the dataset.
Anyone got any ideas on how to solve that other than client side paging
(in ADO :-P)
This is SQL 2000 SP3 (pah!)
Cheers,
Chris Smith
http://www.cswd.co.uk/Assuming CustomerName is unique:
select
(select count (*)
from #tmp t1
where t1.CustomerName <= t2.CustomerName) as rownum
, *
from
#tmp t2
order by
t2.CustomerName
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<cseemeuk@.googlemail.com> wrote in message
news:1144755017.455143.6760@.v46g2000cwv.googlegroups.com...
Hi,
I need to create a stored procedure that returns the row number (for
paging) AFTER the data has been sorted with an order by. The source is
a view. The code I have is:
SELECT rownum = IDENTITY(1,1,bigint), *
INTO #tmp
FROM viewName
ORDER BY CustomerName -- field name I'm ordering by
When I recieve the results back, the rownum column is not the same
order as the customername (it jumps half way to a high number?!?),
which means I can't page it based on rownum without jumping all over
the dataset.
Anyone got any ideas on how to solve that other than client side paging
(in ADO :-P)
This is SQL 2000 SP3 (pah!)
Cheers,
Chris Smith
http://www.cswd.co.uk/|||you could create the table first with an ID column, then insert into
it. I suspect (though have no evidence) that the select into #tmp with
an id column created then is having issues with the order by|||(cseemeuk@.googlemail.com) writes:
> I need to create a stored procedure that returns the row number (for
> paging) AFTER the data has been sorted with an order by. The source is
> a view. The code I have is:
> SELECT rownum = IDENTITY(1,1,bigint), *
> INTO #tmp
> FROM viewName
> ORDER BY CustomerName -- field name I'm ordering by
> When I recieve the results back, the rownum column is not the same
> order as the customername (it jumps half way to a high number?!?),
> which means I can't page it based on rownum without jumping all over
> the dataset.
> Anyone got any ideas on how to solve that other than client side paging
Create the table with CREATE TABLE, and then use INSERT with SELECT ORDER
BY. Add OPTION (MAXDOP 1) as an extra precaution. I've been told from MS
people that it's guaranteed to work. Whether that really is true, I'm not
completely convinced of, but fairly. In any case, SELECT INTO is *not*
guaranteed to work that way, so stay away from it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks - works perfectly. The INTO was the problem - appears to be no
guaranteed order to the IDENTITY(bigint, 1,1)
All sorted
Cheers,
Chris Smith
http://www.cswd.co.uk/|||The order is not guaranteed when you use SELECT INTO.
See
http://support.microsoft.com/defaul...kb;en-us;273586
For a list of paging options see
http://www.aspfaq.com/show.asp?id=2120
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<cseemeuk@.googlemail.com> wrote in message
news:1144755017.455143.6760@.v46g2000cwv.googlegroups.com...
> Hi,
> I need to create a stored procedure that returns the row number (for
> paging) AFTER the data has been sorted with an order by. The source is
> a view. The code I have is:
> SELECT rownum = IDENTITY(1,1,bigint), *
> INTO #tmp
> FROM viewName
> ORDER BY CustomerName -- field name I'm ordering by
> When I recieve the results back, the rownum column is not the same
> order as the customername (it jumps half way to a high number?!?),
> which means I can't page it based on rownum without jumping all over
> the dataset.
> Anyone got any ideas on how to solve that other than client side paging
> (in ADO :-P)
> This is SQL 2000 SP3 (pah!)
> Cheers,
> Chris Smith
> http://www.cswd.co.uk/
>|||One would think this type of thing,so common and important,
would have a kb or something written by MS.Are you aware of any
link?If none exists I would ask you to kindly request something in
'writing'.Key points of an enterprise database should not be rattling
around just in someone head! :)
Clarity,clarity and nothing but clarity.
Regards from:
www.rac4sql.net
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A28BF07D031Yazorman@.127.0.0.1...
> (cseemeuk@.googlemail.com) writes:
> Create the table with CREATE TABLE, and then use INSERT with SELECT ORDER
> BY. Add OPTION (MAXDOP 1) as an extra precaution. I've been told from MS
> people that it's guaranteed to work. Whether that really is true, I'm not
> completely convinced of, but fairly. In any case, SELECT INTO is *not*
> guaranteed to work that way, so stay away from it.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Steve Dassin wrote:
> One would think this type of thing,so common and important,
> would have a kb or something written by MS.Are you aware of any
> link?If none exists I would ask you to kindly request something in
> 'writing'.Key points of an enterprise database should not be rattling
> around just in someone head! :)
> Clarity,clarity and nothing but clarity.
http://support.microsoft.com/defaul...kb;en-us;273586
Do not assume that article means that all INSERTs will always cause
IDENTITY to be generated in a predetermined order. There are at least
some situations where that doesn't work - whether by design or a bug I
can't say.
Perhaps the safest course is to assume that you cannot control the
IDENTITY sequence with ORDER BY. In my view the wisest and most logical
solution is to use other methods like the ROW_NUMBER function for
example.
I can think of at least two good reasons for not using IDENTITY the way
proposed by the KB. Firstly IDENTITY is normally intended as an
arbitrary surrogate key - using the values in any "meaningful" way is a
compromise you don't need and is something it just isn't designed for.
Secondly, this supposed behaviour of an "ordered" INSERT looks contrary
to the set-based nature of an INSERT statement. Whether or not it works
today, it seems undesirable to assume that it should always work that
way in future. One would hope and expect that the engine could optimise
out any redundant sorting in INSERT...SELECT queries. That seems to be
what happens in some cases today and maybe it will happen more often in
future versions due to improvements in the optimiser. Just some things
to bear in mind.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> Anyone got any ideas on how to solve that other than client side paging <
<
The basic principle of a tiered architecture is that display is done in
the front end adn NEVER in the database. Why are you sing violating
40 years of Software Engineering?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144803667.948395.290650@.i40g2000cwc.googlegroups.com...
<<
> The basic principle of a tiered architecture is that display is done in
> the front end adn NEVER in the database. Why are you sing violating
> 40 years of Software Engineering?
>
Forty years of a life sentence is enough.Time to let the innocent free.
Convicted on trumped up,unsubstantiated and false charges.In other words,
NONSENSE.
The thread:
Monday, April 10, 2006 9:48 PM
microsoft.public.sqlserver.programming
Re: Membership Timeline Spanning
contains a response that further clarifies things:
"Itzik Ben-Gan" writes
>.
>In my previous reply I mentioned the ANSI OVER clause (with an ORDER BY
>option). It is really brilliant, and I wonder if the designers of the
>feature themselves knew how profound it is. I believe this option to be the
>bridge between cursors and sets; sort of the holy grail of SQL. :-)
To quote Bob Dylan:
'I would not feel so alone if everyone where getting stoned':)
Yes I agree with you in principal.The 'real' paradign shift has
little to do with the clr and everything to do with exploding
the perverted myth of the exclusivity of'set based' constructs.
The idea one can legitimately think in terms of rows without being
labelled an sql Jodus has arrived.But calling this windowing a
'profound' kind of insight and bestowing on the designers the aura
of 'brilliance' would be a mistake.It is at best an example of
'better late than never'.Calling this state of affairs profound
would surely overshadow the accountability that the commericial
database world should be held to.The fact that this mindset change
has taken almost 30 years should be seen as appalling.Neo-cons of
the industry had hijacked sense with sql creationism and marketing.
WMD was replaced with client/server and a tiered approach.A theory
was misapplied to a retrival mechanism and unapplied to a design
mechanism.An approach that vendors marketted that allowed them to
hide both their intellectual and creative shortcomings.Their db
failures made for the 'client'.And now the clr in the db has replaced
the client.And of course the dreaded cursor.This demanded regime
change and the field was bankrupted for 30 years.For this we are to
praise Ceasar?I think not.
It is interesting to look at the fanfare that vendors are using
to usher in this new paradign.In their documentation Oracle refers
to their analytic functions in windows as an example of
'data densification'.This phrase is supposed to illustrate the
flip side of the Group By.It was obviously borrowed from the idea
of pacification,right out of the Pentagon.This is the best they could
come up with?Any army of engineers berefit of language and concepts.
Not to be out done,MS in its highly touted BOL offers the next best
thing - absolutely Nothing!No explanations,no history no seqways.
The functions are thrown around like so much spaghetti on a wall.
If you write about concepts someone may quote you.MS needn't worry
now.Least I be accused of favortism,IBM was too busy pleasing its
shareholders to write anything intelligible.
Finally,to your point about MS leaving out a large chunk of analytic
material this was obviously not an oversight but just insurance
that anything done with sql-99 could most definitly be easily ported
to the competition.Less is more.Please!If they weren't sure of
what they were doing they could have at least looked at Oracle
which is probably about 8 years ahead.Or even looked at RAC to see what
you and I are really talking about :)
Interested readers maybe surprised that many of the ideas in sql
analytics can be found in the SAS (Statistical Analysis System) Data
Step...introduced about 20 years ago!Many of the Oracle extensions
(First/Last) can also be found here.MySql allows mixing of variables
and columns in a SELECT.Most of the analytics can be easily simulated
in a single SELECT.And of course little RAC, way ahead of its time:)
Some musing from:
www.rac4sql.net