Getting a row count from a DataSource vs GridView

There HAS to be an easier way to do this...

I have 2 seperate SqlDataSources for 2 distinct filters. How do I get a simple row count for each SqlDataSource? It seems the only way is by using the ObjectDataSource and going through that whole mess (paging, etc.). And unfortunately, you can't simply get the number of rows in the GridView that represents each DataSource if AllowPaging is true for the GridView.

This is frustrating.

Dave

You gotta setup the Selected Event

protected void SqlDataSource1_Selected( object sender, SqlDataSourceStatusEventArgs e )
{
e.AffectedRows;
}

|||

Can you give me a little more insight...

Thanks,

Dave

|||

Not sure how much more detailed I can get. When you setup your SqlDataSources, put

<asp:SqlDataSourceID="SqlDataSource1"runat="server"OnSelected="SqlDataSource_Selected" ... />
<asp:SqlDataSourceID="SqlDataSource2"runat="server"OnSelected="SqlDataSource_Selected".../>

They both should be able to use the same event since you want them both to do the same thing.

Then in your code-beside, setup the event as follows

protectedvoid SqlDataSource_Selected(object sender,SqlDataSourceStatusEventArgs e )
{
// Do something with e.AffectedRows
Response.Write( e.AffectedRows );
}

Getting A Query Parameter from Elsewhere

I have a query on a table. This table has a datetime column.
I have another table. This table also has a datetime column. It has only one
row. It basically to store some simple configuration data.
There is no relation between the tables.
I want to be able to limit the entries returned by the query on the first
table by only returning rows that have a date that is less than the date in
the row in the second table.
I'm actually having trouble getting this to work properly. I've tried
subqueries, passing it through the report as a parameter, etc... None are
working.
How should i attack this?It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
possibility for you. I am sure there are much better ways, but this is off
the cuff:
Select
MyDate, Field1, Field2 from table1
Group by
MyDate,Field1,Field2
HAVING MyDate < (Select LookUpDate from Table2)
You could always do it in a stored procedure and call in the value of Table2
into a variable @.LokUpDate that yo could compare. I assume that the
LookUpDate in my example to match your description changes on a regular
basis?
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
>I have a query on a table. This table has a datetime column.
> I have another table. This table also has a datetime column. It has only
> one
> row. It basically to store some simple configuration data.
> There is no relation between the tables.
> I want to be able to limit the entries returned by the query on the first
> table by only returning rows that have a date that is less than the date
> in
> the row in the second table.
> I'm actually having trouble getting this to work properly. I've tried
> subqueries, passing it through the report as a parameter, etc... None are
> working.
> How should i attack this?|||If I use GROUP BY, will that not require aggregation of the results of the
query?
Perhaps it would be helpful to see the existing query:
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID =MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE ='Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID =INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm')
AND (SALESTABLE.SALESSTATUS = 1)
That is table one. I want to limit on a column called CREATEDDATE.
The other query looks like:
SELECT CUTOFFDATE from CONFIGDATA
"Rodney Landrum" wrote:
> It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
> possibility for you. I am sure there are much better ways, but this is off
> the cuff:
> Select
> MyDate, Field1, Field2 from table1
> Group by
> MyDate,Field1,Field2
> HAVING MyDate < (Select LookUpDate from Table2)
> You could always do it in a stored procedure and call in the value of Table2
> into a variable @.LokUpDate that yo could compare. I assume that the
> LookUpDate in my example to match your description changes on a regular
> basis?
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.apress.com
> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> >I have a query on a table. This table has a datetime column.
> >
> > I have another table. This table also has a datetime column. It has only
> > one
> > row. It basically to store some simple configuration data.
> >
> > There is no relation between the tables.
> >
> > I want to be able to limit the entries returned by the query on the first
> > table by only returning rows that have a date that is less than the date
> > in
> > the row in the second table.
> >
> > I'm actually having trouble getting this to work properly. I've tried
> > subqueries, passing it through the report as a parameter, etc... None are
> > working.
> >
> > How should i attack this?
>
>|||If I use GROUP BY, will that not require aggregation of the results of the
query?
Perhaps it would be helpful to see the existing query:
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID =MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE ='Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID =INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm')
AND (SALESTABLE.SALESSTATUS = 1)
That is table one. I want to limit on a column called CREATEDDATE.
The other query looks like:
SELECT CUTOFFDATE from CONFIGDATA
"Rodney Landrum" wrote:
> It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
> possibility for you. I am sure there are much better ways, but this is off
> the cuff:
> Select
> MyDate, Field1, Field2 from table1
> Group by
> MyDate,Field1,Field2
> HAVING MyDate < (Select LookUpDate from Table2)
> You could always do it in a stored procedure and call in the value of Table2
> into a variable @.LokUpDate that yo could compare. I assume that the
> LookUpDate in my example to match your description changes on a regular
> basis?
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.apress.com
> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> >I have a query on a table. This table has a datetime column.
> >
> > I have another table. This table also has a datetime column. It has only
> > one
> > row. It basically to store some simple configuration data.
> >
> > There is no relation between the tables.
> >
> > I want to be able to limit the entries returned by the query on the first
> > table by only returning rows that have a date that is less than the date
> > in
> > the row in the second table.
> >
> > I'm actually having trouble getting this to work properly. I've tried
> > subqueries, passing it through the report as a parameter, etc... None are
> > working.
> >
> > How should i attack this?
>
>|||You really do not have to add an aggregate function. You can always set a
variable and use that in the Where clause if you do not want to use the
GROUP BY. Something like ( and I added CREATEDATE to the WHERE clause also)
:
Declare @.CUTOFFDATE as DATETIME
SELECT @.CUTOFFDATE=CUTOFFDATE from CONFIGDATA
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS
QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID = MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE = 'Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID = INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID ='acm')
AND (SALESTABLE.SALESSTATUS = 1) AND CREATEDATE < @.CUTOFFDATE
You may have to make this a stored procedure if it will not work on the IDE
for reporting Services.
Rodney Landrum -Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:211E0BCB-A461-485A-B888-333A33E33162@.microsoft.com...
> If I use GROUP BY, will that not require aggregation of the results of the
> query?
> Perhaps it would be helpful to see the existing query:
> SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS
> QTY,
> (SELECT SUM(MARKUPTRANS.VALUE)
> FROM MARKUPTRANS
> WHERE SALESTABLE.RECID => MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
> MARKUPTRANS.MARKUPCODE => 'Freight') AS FreightValue,
> (SELECT SUM(INVENTSUM.POSTEDVALUE)
> FROM INVENTSUM
> WHERE SALESLINE.INVENTDIMID => INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
> FROM SALESTABLE INNER JOIN
> SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
> WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID => 'acm')
> AND (SALESTABLE.SALESSTATUS = 1) >
> That is table one. I want to limit on a column called CREATEDDATE.
> The other query looks like:
> SELECT CUTOFFDATE from CONFIGDATA
> "Rodney Landrum" wrote:
>> It would be possible to use a GROUP BY / HAVING in the SELECT if that was
>> a
>> possibility for you. I am sure there are much better ways, but this is
>> off
>> the cuff:
>> Select
>> MyDate, Field1, Field2 from table1
>> Group by
>> MyDate,Field1,Field2
>> HAVING MyDate < (Select LookUpDate from Table2)
>> You could always do it in a stored procedure and call in the value of
>> Table2
>> into a variable @.LokUpDate that yo could compare. I assume that the
>> LookUpDate in my example to match your description changes on a regular
>> basis?
>> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
>> http://www.apress.com
>> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
>> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
>> >I have a query on a table. This table has a datetime column.
>> >
>> > I have another table. This table also has a datetime column. It has
>> > only
>> > one
>> > row. It basically to store some simple configuration data.
>> >
>> > There is no relation between the tables.
>> >
>> > I want to be able to limit the entries returned by the query on the
>> > first
>> > table by only returning rows that have a date that is less than the
>> > date
>> > in
>> > the row in the second table.
>> >
>> > I'm actually having trouble getting this to work properly. I've tried
>> > subqueries, passing it through the report as a parameter, etc... None
>> > are
>> > working.
>> >
>> > How should i attack this?
>>|||I might be missing something but this looks like the following to me:
select a.* from maintable a, configtable b where a.datetimecolumn <
b.datetimecolumn
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> I have a query on a table. This table has a datetime column.
> I have another table. This table also has a datetime column. It has only
one
> row. It basically to store some simple configuration data.
> There is no relation between the tables.
> I want to be able to limit the entries returned by the query on the first
> table by only returning rows that have a date that is less than the date
in
> the row in the second table.
> I'm actually having trouble getting this to work properly. I've tried
> subqueries, passing it through the report as a parameter, etc... None are
> working.
> How should i attack this?

Getstring question

how can i add two columns in each row (both strings) in a table only using getstring?

i'm getting an error from this statement... thanks

dim row as string

while dr.read()

problem with this statment

--->> row = dr.getstring(1) + dr.getstring(2)

end whileExactly what is the error message?|||the error message is

Index was outside the bounds of the array.|||Index was outside the bounds of the array.|||Can you verify that your query is in fact returning 2 fields?|||select lastname,firstname from employees

i'm using the northwind db in sql server 2000|||I am not familiar with VB.NET syntax, but in C#, GetString() is zero based, so you'd have:

row = dr.getstring(0) + dr.getstring(1)

Not sure if its the same in VB.NET, but you might want to give that a try.

Geting the last inserted row for each CLIENT. How?

Hi,

I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
where the conversation data is registered (and other columns).

Now I need to retrieve, for each client, the last N (for some clients,
eventually, less then N) conversations with one T-SQL statement.

Does anyone knows how to do this? Is it possible with T-SQL only?

Thanks.

Antonio:

Is one of this close to what you are looking for?

Dave

-- --
-- I have used a LEFT JOIN in this case because I think that it
-- is probably important that a client that has not been called
-- be listed.
--
-- I went ahead and added the "recordsToPull" column to provide
-- for the future change when you want to be able to vary the
-- number of conversations returned on a client-by-client
-- basis
-- --
set nocount on

declare @.client table
( client_id integer not null,
recordsToPull integer not null
)

declare @.conversations table
( client_id integer not null,
conv_id integer not null,
conversation_date datetime not null
)

insert into @.client values (1, 4)
insert into @.client values (2, 2)
insert into @.client values (3, 4)
insert into @.client values (4, 4)
--select * from @.client

insert into @.conversations values (1, 1, '1/14/2006')
insert into @.conversations values (1, 2, '10/31/2006')
insert into @.conversations values (1, 3, '3/17/6')

insert into @.conversations values (2, 4, '2/2/6')
insert into @.conversations values (2, 5, '2/2/6')
insert into @.conversations values (2, 6, '2/4/6')
insert into @.conversations values (2, 7, '2/5/6')
insert into @.conversations values (2, 8, '2/8/6')

insert into @.conversations values (3, 9, '7/1/5')
insert into @.conversations values (3, 10, '11/23/5')
insert into @.conversations values (3, 11, '1/17/6')
insert into @.conversations values (3, 12, '4/1/6')
insert into @.conversations values (3, 13, '10/31/6')
--select * from @.conversations

--
-- SQL Server 2005 Version
--
/*
select a.client_id,
a.recordsToPull,
b.conv_id,
b.conversation_date
from @.client a
left join
(
select client_id,
conv_id,
conversation_date,
row_number ()
over ( partition by client_id
order by conversation_date desc, conv_id desc
)
as seq
from @.conversations
) b
on a.client_id = b.client_id
and a.recordsToPull >= b.seq
order by a.client_id,
b.Seq
*/

--
-- SQL Server 2000 Version
--
select a.client_id,
a.recordsToPull,
b.conv_id,
b.conversation_date
from @.client a
left join
(
select x.client_id,
x.conv_id,
x.conversation_date,
count(*) as seq
from @.conversations x
inner join @.conversations y
on x.client_id = y.client_id
and ( x.conversation_date < y.conversation_date or
x.conversation_date = y.conversation_date and
x.conv_id <= y.conv_id
)
group by x.client_id,
x.conversation_date,
x.conv_id
-- order by x.client_id,
-- x.conversation_date desc,
-- x.conv_id desc
) b
on a.client_id = b.client_id
and a.recordsToPull >= b.seq
order by a.client_id,
b.Seq

--
-- Sample Output:
--

-- client_id recordsToPull conv_id conversation_date
-- -- - -- --
-- 1 4 2 2006-10-31 00:00:00.000
-- 1 4 3 2006-03-17 00:00:00.000
-- 1 4 1 2006-01-14 00:00:00.000
-- 2 2 8 2006-02-08 00:00:00.000
-- 2 2 7 2006-02-05 00:00:00.000
-- 3 4 13 2006-10-31 00:00:00.000
-- 3 4 12 2006-04-01 00:00:00.000
-- 3 4 11 2006-01-17 00:00:00.000
-- 3 4 10 2005-11-23 00:00:00.000
-- 4 4 NULL NULL

|||

It would help if you post the version of SQL Server you are using. In SQL Server 2005, you can do below:

select c.*, t.*

from CLIENTS as c

cross apply (

select top(@.n) *

from CONVERSATIONS as cn

where cn.CLIENT_ID = c.CLIENT_ID

order by cn.CONVERSATION_DATE desc

) as t

|||

Thanks

this is exactly what I needed, worked perfectly 2005 version, didn't try 2000, but I'll need it too...

|||

Hi,

I don't know if you tryed it but didn't work for me. Thanks anyway.

Geting the last inserted row for each CLIENT. How?

Hi,
I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
where the conversation data is registered (and other columns).
Now I need to retrieve, for each client, the last N (for some clients,
eventually, less then N) conversations with one T-SQL statement.
Does anyone knows how to do this? Is it possible with T-SQL only?
Thanks.
See if this may work. If you also need to see clients without a
conversation, change the join to a left join.
select client.client_id, Conversations.conv_id
from client
inner join conversations on conversations.client_id = client.client_id
and conversations.conv_id in
(
select top 15 conv_id
from conversations
where client_id = client.client_id
order by conversation_date desc
)
A.Neves wrote:
> Hi,
> I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
> CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
> where the conversation data is registered (and other columns).
> Now I need to retrieve, for each client, the last N (for some clients,
> eventually, less then N) conversations with one T-SQL statement.
> Does anyone knows how to do this? Is it possible with T-SQL only?
> Thanks.
|||Didn't work,
but look here:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=917505&SiteID=17&mode=1
<Jochen.Markert@.fnf.com> escreveu na mensagem
news:1163189244.535260.305810@.m73g2000cwd.googlegr oups.com...
> See if this may work. If you also need to see clients without a
> conversation, change the join to a left join.
> select client.client_id, Conversations.conv_id
> from client
> inner join conversations on conversations.client_id = client.client_id
> and conversations.conv_id in
> (
> select top 15 conv_id
> from conversations
> where client_id = client.client_id
> order by conversation_date desc
> )
> A.Neves wrote:
>

Geting the last inserted row for each CLIENT. How?

Hi,
I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
where the conversation data is registered (and other columns).
Now I need to retrieve, for each client, the last N (for some clients,
eventually, less then N) conversations with one T-SQL statement.
Does anyone knows how to do this? Is it possible with T-SQL only?
Thanks.See if this may work. If you also need to see clients without a
conversation, change the join to a left join.
select client.client_id, Conversations.conv_id
from client
inner join conversations on conversations.client_id = client.client_id
and conversations.conv_id in
(
select top 15 conv_id
from conversations
where client_id = client.client_id
order by conversation_date desc
)
A.Neves wrote:
> Hi,
> I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
> CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
> where the conversation data is registered (and other columns).
> Now I need to retrieve, for each client, the last N (for some clients,
> eventually, less then N) conversations with one T-SQL statement.
> Does anyone knows how to do this? Is it possible with T-SQL only?
> Thanks.|||Didn't work,
but look here:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=917505&SiteID=17&mode=1
<Jochen.Markert@.fnf.com> escreveu na mensagem
news:1163189244.535260.305810@.m73g2000cwd.googlegroups.com...
> See if this may work. If you also need to see clients without a
> conversation, change the join to a left join.
> select client.client_id, Conversations.conv_id
> from client
> inner join conversations on conversations.client_id = client.client_id
> and conversations.conv_id in
> (
> select top 15 conv_id
> from conversations
> where client_id = client.client_id
> order by conversation_date desc
> )
> A.Neves wrote:
>> Hi,
>> I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table
>> where
>> CONV_ID and CLIENT_ID form the pk, there is another column
>> CONVERSATION_DATE
>> where the conversation data is registered (and other columns).
>> Now I need to retrieve, for each client, the last N (for some clients,
>> eventually, less then N) conversations with one T-SQL statement.
>> Does anyone knows how to do this? Is it possible with T-SQL only?
>> Thanks.
>sql

Geting the last inserted row for each CLIENT. How?

Hi,
I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
where the conversation data is registered (and other columns).
Now I need to retrieve, for each client, the last N (for some clients,
eventually, less then N) conversations with one T-SQL statement.
Does anyone knows how to do this? Is it possible with T-SQL only?
Thanks.See if this may work. If you also need to see clients without a
conversation, change the join to a left join.
select client.client_id, Conversations.conv_id
from client
inner join conversations on conversations.client_id = client.client_id
and conversations.conv_id in
(
select top 15 conv_id
from conversations
where client_id = client.client_id
order by conversation_date desc
)
A.Neves wrote:
> Hi,
> I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
> CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DA
TE
> where the conversation data is registered (and other columns).
> Now I need to retrieve, for each client, the last N (for some clients,
> eventually, less then N) conversations with one T-SQL statement.
> Does anyone knows how to do this? Is it possible with T-SQL only?
> Thanks.|||Didn't work,
but look here:
[url]http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=917505&SiteID=17&mode=1[/
url]
<Jochen.Markert@.fnf.com> escreveu na mensagem
news:1163189244.535260.305810@.m73g2000cwd.googlegroups.com...
> See if this may work. If you also need to see clients without a
> conversation, change the join to a left join.
> select client.client_id, Conversations.conv_id
> from client
> inner join conversations on conversations.client_id = client.client_id
> and conversations.conv_id in
> (
> select top 15 conv_id
> from conversations
> where client_id = client.client_id
> order by conversation_date desc
> )
> A.Neves wrote:
>

GetErrorDescription with oledb destination fast load mode

I have an OLE-DB Command transformation that inserts a row. If the insert SQL command fails for some reason, I use the "Redirect Row" option to send the row to a script component. Inthere, I get the error description into a string variable in order to log the error into an error table.

For example, if a primary key violation arises, I would like the error description to be "The data value violates integrity constraints". I get it using the ComponentMetadata.GetErrorDescription. When I use the "table or view mode", I get the error description above without any problem. But If I use the "table or view fast load", the description is something like "No status available". But, If I use the error output to fail the component, in the OnError, I get the right error description. Is there a way to have both behaviour, I mean, to be able to redirect error rows to an output and have the cotrrect error description (like the one in OnError event handler) using fast load mode?

Thank you!

Ccote

Hi ccote,

I am having exactly the same problem. Did you have yours resolved?

|||You can't get an error description using "Fast Load" as that is a bulk load operation.|||Even if I don't use the fast load option, the error description is vague. It returns something like "data violate integrity constraints". If I let the component fail and don't redirect the row, it gives the name of the constraint violated. It there a way to capture this detail error message?|||

I do not believe this information is available within the data flow. In any event, I've never been able to get more information than what you have described getting, above. Still, this is not necessarily a dead end.

Generally what I will do is set up an "error table" for each table into which I load data with an SSIS package. The error table will have a similar schema as the "real" target table, with a few exceptions:

It has much more lax integrity constraints - there are no foreign keys, and all columns allow NULLs, for example, so the odds of an INSERT failing are greatly reduced.

It has two additional columns, ErrorCode and ErrorDescription, into which I load the values added by the OLD DB Destination component's error output.

GetErrorDescription with oledb destination fast load mode

I have an OLE-DB Command transformation that inserts a row. If the insert SQL command fails for some reason, I use the "Redirect Row" option to send the row to a script component. Inthere, I get the error description into a string variable in order to log the error into an error table.

For example, if a primary key violation arises, I would like the error description to be "The data value violates integrity constraints". I get it using the ComponentMetadata.GetErrorDescription. When I use the "table or view mode", I get the error description above without any problem. But If I use the "table or view fast load", the description is something like "No status available". But, If I use the error output to fail the component, in the OnError, I get the right error description. Is there a way to have both behaviour, I mean, to be able to redirect error rows to an output and have the cotrrect error description (like the one in OnError event handler) using fast load mode?

Thank you!

Ccote

Hi ccote,

I am having exactly the same problem. Did you have yours resolved?

|||You can't get an error description using "Fast Load" as that is a bulk load operation.|||Even if I don't use the fast load option, the error description is vague. It returns something like "data violate integrity constraints". If I let the component fail and don't redirect the row, it gives the name of the constraint violated. It there a way to capture this detail error message?|||

I do not believe this information is available within the data flow. In any event, I've never been able to get more information than what you have described getting, above. Still, this is not necessarily a dead end.

Generally what I will do is set up an "error table" for each table into which I load data with an SSIS package. The error table will have a similar schema as the "real" target table, with a few exceptions:

It has much more lax integrity constraints - there are no foreign keys, and all columns allow NULLs, for example, so the odds of an INSERT failing are greatly reduced. It has two additional columns, ErrorCode and ErrorDescription, into which I load the values added by the OLD DB Destination component's error output.

GetBlobData method fails

Hi,

I have s Script Component, that retrieves data from NTEXT column using this code:

Dim b As Byte()

If (Row.OutputXML.Length > 0) And (Not (Row.OutputXML_IsNull)) Then

b = Row.OutputXML.GetBlobData(0, CInt(Row.OutputXML.Length))

End If

I′m getting this error:

[Script Component 1 [838]] Error: System.Runtime.InteropServices.COMException (0x80004005): Error HRESULT E_FAIL has been returned from a call to a COM component. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)

OutputXML column is filled by Ole Db Command from an output parameter (nvarchar(max)) of a stored procedure. In management studio sp works fine, even Execute SQL Task returns correct (but truncated) data.

Please, help!

what is the SSIS data type (not the original SQL data type) assigned in OleDB Command to OutputXML column?|||data type is DT_NTEXT

get value of rowguidcol from last inserted row

How would I get the value of a ROWGUID column of the row I just inserted? (like using @.@.identity for an identity column.)

Thanks!I think that the only way to accomplish this is to use the NEWID() function before your INSERT statement to explicitly assign the value.

David Penton has a stored proceudre which explains this technique:A example of returning a guid as an "Identity" in ADO

Terri|||Why would you like this?

Why do you not create the GUID on the client side?

GUID's are defiend to be unique wherever you create them. There is - contrary to identity fields - no need to have the server define them.

This is the beauty of them - I know them when the object they mark (if they are the PK) is created, not once it is inserted into the database.|||I would like this because I know, like, and respect David Penton.

Yes, having the client create the GUID would be another way to go. I don't see a real advantage of creating the GUID on the client side, however.

To me, a row identifier is something that SQL needs and cares about, and the client couldn't care less about. So why would the client have the job of creating it?

Maybe you can explain further.

Terri|||::Why would you like this?

::Why do you not create the GUID on the client side?

This is what I ended up doing.|||::To me, a row identifier is something that SQL needs and cares about, and the client couldn't
::care less about.

In this casse, why does teh client need to know at all?

If the ROWGUID is simply used as replication identifier, for example, the client can be "ignorant" and just ignore it.

Obviously, for some reason, this is not the case - the client needs to know.

And then, i f it needs to know, and inserts the row anyway, it can also determine it.

::So why would the client have the job of creating it?

Because the client inserts the row and obviously does something with the id, otherwise it would not requrey for it.

I assume it is some sort of primary key, too.

And here is is much easier to work with a guid the moment you create the row, instead of inserting it later.

Get the Row which has Max value of the field

I want to get a field value in the row, which has max value of another field in the same table

I have done with the below query. I want to know is there any other better way of doing it becuase it is taking longer time.

Thanks for your help

SELECT Field1
FROM TableName
WHERE Field2 = ( SELECT MAX(field2)
FROM TableName)That shouldn't take too long to process. How many rows, and do you have an index on Field2?|||I have about 200k records. I have index (non-clustered )on field2. More over it is taking about 7 sec to run the above query

Thanks|||...and of those 200K records, how many are typically being returned in the result set? It may take several seconds to display the result set.

Have you tried viewing the query plan?

get the row counts for each day going back to 6 months (was "query help")

I have a proc to get the rowcounts for the given date range.
I have to get the row counts for each day going back to 6 months on the table.

With this proc i can get one day's row couts.. i need to loop through for all dates.

Please can someone get me the code for this.

create proc p_rowcounts

@.Date1 datetime,
@.Date2 datetime

SELECT
count (*) as 'Number of Rows', @.Date1 as Date
FROM
Table1 (nolock)
WHERE ModifyTime >= @.Date1 and ModifyTime < @.Date2

thanks for the help.I'd do it as:CREATE PROC p_rowcounts
@.Date1 datetime = NULL
, @.Date2 datetime = NULL
AS

IF @.Date1 IS NULL SET @.Date1 = GetDate()
IF @.Date2 IS NULL SET @.Date2 = DateAdd(month, -6, Convert(CHAR(10), @.Date1, 121))

SELECT
Count (*) AS 'Number of Rows'
, Convert(DATETIME, Convert(CHAR(10), ModifyTime, 121)) AS Date
FROM Table1 (nolock)
WHERE ModifyTime BETWEEN @.Date2 AND @.Date1
GROUP BY Convert(CHAR(10), ModifyTime, 121)

RETURN-PatP|||pat, i think sskris wants one count per date in the range|||That query ought to give one count per day in the range. I think you're hinting that you'd like to see rows with zeros for a count for days with no data, which I see as wasteful and poor practice.

If you have code that relies on zeros, you can certainly go to added trouble to make the zeros appear, but in my mind you'd be much better off to fix the code instead of writing SQL to cater to the problems in it.

-PatP

Get the Row Count

I have created a package which is transforming the data from Source OLEDB Sql Server to Destination OLEDB Sql Server programatically in VB.NET

During execution phase, I want to generate an event after every 1000 rows are transformed. As per I think, OnProgress event of IDTSEvent doesn't support this kind of a thing.

And after my Transformation is completed, I want to know how many rows have being transformed.

So how can the above two task be performed?

Add a Row Count component to your data flow to capture the number of rows.

You should be able to fire events using the FireProgress or FireInformation methods of the ComponentMetaData class. See this topic in Books Online: Raising Events in the Script Component (http://msdn2.microsoft.com/en-us/library/aa337081.aspx)

|||

I am creating the package programmatically and not using designer for it.

I have already created the package which has OLEDB and Source and Destination and added to TaskHost. For Row Count Component I think I have to create another Task Host and then add it to main pipe. But it has to be added as For Each Loop. But if I use For Each Loop then one Task Host will process at a time.

So what is the solution for this?

|||

I have added Row Count Component as Transformation (DTSTransform.RowCount) to the ComponentMetaData.

Now I get the number of Row Transformed after post execute event whereas I need to raise an event after every 1000 rows are transformed. According to me after PostExecute event of the DataFlow, the number of Rows Transformed as set into the variable by Row Count Component. So how can I raise event after every 1000 rows?

|||One way to do this would be to add a script transform to monitor the number of rows and fire the event after 1000 rows pass through.|||

I have used Script Component to transform. As I am creating this programmatically, I have copied the code generated in xml format of dtsx file to my application as a string array exactly as in the file to SourceCode property.

I have set PreCompile Property to false. So I don't need to set the BinaryCode property.

I have used code as below:

Dim DFTransform As IDTSComponentMetaData90

DFTransform = DTP.ComponentMetaDataCollection.New()

DFTransform.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

DFTransform.Name = "ScriptTransform"

DFTransform.UsesDispositions = False

Dim TransInst As CManagedComponentWrapper = DFTransform.Instantiate()

TransInst.ProvideComponentProperties()

DTP.PathCollection.New().AttachPathAndPropagateNotifications(DFSource.OutputCollection(0), DFTransform.InputCollection(0))

TransInst.SetComponentProperty("VsaProjectName", "ScriptComponent_3a1cf20682b14906bbdc971f7768e55c")

TransInst.SetComponentProperty("SourceCode", AddSourceCode(DFTransform.ComponentClassID))

TransInst.SetComponentProperty("BinaryCode", AddBinaryCode)

TransInst.SetComponentProperty("PreCompile", False)

TransInst.SetComponentProperty("UserComponentTypeName", "Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

TransInst.AcquireConnections(Nothing)

TransInst.ReinitializeMetaData()

Dim output As IDTSOutput90 = DFTransform.OutputCollection(0)

Dim outputColumn As IDTSOutputColumn90 = output.OutputColumnCollection.New()

outputColumn.Name = "myCount"

outputColumn.SetDataTypeProperties(Wrapper.DataType.DT_I4, 0, 0, 0, 0)

DFTransform.OutputCollection(0).ExternalMetadataColumnCollection.IsUsed = False

TransInst.ReleaseConnections()

But when I compile my application I get following errors:

Error Code :-1073450910

Sub Component :- Script Component [43]

Description :- System.NullReferenceException: Object reference not set to an instance of an object.

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()

Error Code :-1073450901

Sub Component :-DTS.Pipeline

Description :- "component "Script Component" (43)" failed validation and returned validation status "VS_ISBROKEN".

Is there anything which I need to do differently

Get table row size

How can we get the newly inserted row's size from a SQL Server table?

Quote:

Originally Posted by soumyamathewmca

How can we get the newly inserted row's size from a SQL Server table?

select
sys.objects.[name],
sys.objects.[object_id],
count(sys.columns.[name]) As ColumnCount,
sum(sys.columns.max_length) As MaxLength
from
sys.objects
inner join sys.columns on sys.objects.object_id = sys.columns.object_id
where
sys.objects.[name] = Table_Name
group by
sys.objects.[name],
sys.objects.[object_id]

Get row timestamp with no timestamp column

For starters, please feel free to move this if it is in the wrong forum.

The issue I have is this. I have been asked to delete all information from a table that was inserted before May 12 this year. The issue is that when the DB was created, whoever designedd it neglected to add a timestamp column for the user data table (the one I need to purge). Does SQL, by default, happen to store insert times? Would it be something that might hide ina log file somewhere?

Your best bet is to try and find a backup from May 12th or 13th, and restoring it into a new DB. Then you can compare the two, and only keep the newer rows. However, this won't address if any of the fields were updated in the interim.

|||Great Idea! I unfortunately only have access through query analyzer right now, though....|||turns out i cant get access to the server itself, and they only keep backups back 5 days, so no dice.... Any other suggestions?

Get Quick results using "Row Locator"s (fileid+pageid+rowid)

Hi.
I've posted this to the SQL wish center (Connect/sqlserver/feedback) as a
suggested new feature.
(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281531).
Would appreciate your comments and thoughts towards feasibility and
usability.
Suggested feature post as below:
Description
Processing queries involves many steps by the query processing engine.
Eventually the row containing the data queried for is found, i.e. either
involving few steps, if an index is used (generally preferred scenario) or
in case of searching the table for every row (like a heap) if no index is
used (worst case scenario).
If similar queries are repeated, sql server may use the cache to deliver
results faster. However we all know this "quickness" may not be reliable in
very large tables, limited resources or fairly big timegaps (due to cache
timeouts).
It would be great if we could use the "row locator" which is a combination
of fileid, page id and the row id of the row provided as a 'hint' within the
query (like we do for indexes) and get the desired row(s). This would
certainly be a great advantage specifically in searching for columns which
are PKs or have uniquely constrained indexes, or select top 1s.
I feel this feature could have a significant improvement in the time it
takes to get the result especially for a repeat query. Common scenarios
include queries for updates or deletes especially in disconnected scenarios
more common in web applications.
Proposed Solution
Two parts to this Solution:
Part 1: Have a new table linked function called table.GetRowLocatorKey() or
similar construct/syntax to get the row locator (fileid,pageid,rowid) key
for the row.
i.e.
select table.GetRowLocatorKey, <columns> from <table>
Part 2: For queries allow rowlocator hint(s) that could be provided as part
of the query where one or more row locators specified for a table could be
checked first by the query processor for results of the query like so:
Select <columns> from <table> where <conditions>
RowLocationHints:Table(rowlocation1,rowlocation2,rowlocation3)
The query processor would check the row locations first. If a single result
is to be found incase of a select top 1 or conditions involving a column
with unique index / PK then it could stop and return the result.
If the result is not found on the row location (incase of updates or
deleted) or if the situation doesnot involve select top 1s or unique index /
PK then the query processor should continue as it normally would.
regards
ManasvinOn Jun 6, 1:23 pm, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
> It would be great if we could use the "row locator" which is a combination
> of fileid, page id and the row id of the row provided as a 'hint' within the
> query (like we do for indexes) and get the desired row(s).
Translation: Allow us to throw away the relational model and use a
network model database instead.
Ain't gonna happen.
It *might* have utility in a very few performance-related scenarios.
But 99% of the situations that use SQL Server would obtain absolutely
zero benefit.
If you want a network model database, use one. Leave SQL Server alone.|||> It would be great if we could use the "row locator" which is a combination
> of fileid, page id and the row id of the row provided as a 'hint' within
> the query (like we do for indexes) and get the desired row(s). This would
> certainly be a great advantage specifically in searching for columns which
> are PKs or have uniquely constrained indexes, or select top 1s.
Part of the beauty of a relational system is that the physical location of a
piece of data is abstracted from us. How are you going to know the fileid,
page id and row id of a particular row? How expensive is that part of the
lookup going to be? And even now that you have it, how do you know it will
be in the same physical location next week, tomorrow, or even in five
minutes?|||I think this feature is NOT to suggest moving away from a relational system
but a smarter one. A hint based pattern which is already being used on
various other aspects including choosing an index for instance withing a
query. The idea is that requery for the same record or row shouldnt take the
same effort regardless of cache hits or misses.
In a typical scenario which I believe happens often illustrated below:
A record a is queried for viewing.
Its at this stage the query process would anyways be able to accumalate the
rowlocation since its got there to collect the data row anyways.
the application which queried for this data and is now displaying the row(s)
could maintain the rowlocations keys (and which are not meant to be used as
permanent or static values). most times the application has disconnected and
the RDMBS is busy to serve other applications and their queries
Now if there is a requery or an update / delete to the above said row(s) the
rowlocation(s) could be supplied for these specific queries as a 'hint only'
but not to undermine the consistency or reliability of the query process,
rather a smarter way just get to the data quickly. in any case this hint(s)
may not be any good but could very well be enough to make a difference.
i believe the above sequence of application events or actions do happen
quite commonly.amongst many application if not most.
I hope the scenario above makes things a bit clearer. Am I the only one who
sees this as a very useful feature ?
regards
Piyush
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Oxvw2NGqHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> It would be great if we could use the "row locator" which is a
>> combination of fileid, page id and the row id of the row provided as a
>> 'hint' within the query (like we do for indexes) and get the desired
>> row(s). This would certainly be a great advantage specifically in
>> searching for columns which are PKs or have uniquely constrained indexes,
>> or select top 1s.
> Part of the beauty of a relational system is that the physical location of
> a piece of data is abstracted from us. How are you going to know the
> fileid, page id and row id of a particular row? How expensive is that
> part of the lookup going to be? And even now that you have it, how do you
> know it will be in the same physical location next week, tomorrow, or even
> in five minutes?
"rpresser" <rpresser@.gmail.com> wrote in message
news:1181151962.535269.172900@.z28g2000prd.googlegroups.com...
> On Jun 6, 1:23 pm, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
>> It would be great if we could use the "row locator" which is a
>> combination
>> of fileid, page id and the row id of the row provided as a 'hint' within
>> the
>> query (like we do for indexes) and get the desired row(s).
> Translation: Allow us to throw away the relational model and use a
> network model database instead.
> Ain't gonna happen.
> It *might* have utility in a very few performance-related scenarios.
> But 99% of the situations that use SQL Server would obtain absolutely
> zero benefit.
> If you want a network model database, use one. Leave SQL Server alone.
>|||On Jun 6, 12:23 pm, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
> Hi.
> I've posted this to the SQL wish center (Connect/sqlserver/feedback) as a
> suggested new feature.
> (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...).
> Would appreciate your comments and thoughts towards feasibility and
> usability.
> Suggested feature post as below:
> Description
> Processing queries involves many steps by the query processing engine.
> Eventually the row containing the data queried for is found, i.e. either
> involving few steps, if an index is used (generally preferred scenario) or
> in case of searching the table for every row (like a heap) if no index is
> used (worst case scenario).
> If similar queries are repeated, sql server may use the cache to deliver
> results faster. However we all know this "quickness" may not be reliable in
> very large tables, limited resources or fairly big timegaps (due to cache
> timeouts).
> It would be great if we could use the "row locator" which is a combination
> of fileid, page id and the row id of the row provided as a 'hint' within the
> query (like we do for indexes) and get the desired row(s). This would
> certainly be a great advantage specifically in searching for columns which
> are PKs or have uniquely constrained indexes, or select top 1s.
> I feel this feature could have a significant improvement in the time it
> takes to get the result especially for a repeat query. Common scenarios
> include queries for updates or deletes especially in disconnected scenarios
> more common in web applications.
> Proposed Solution
> Two parts to this Solution:
> Part 1: Have a new table linked function called table.GetRowLocatorKey() or
> similar construct/syntax to get the row locator (fileid,pageid,rowid) key
> for the row.
> i.e.
> select table.GetRowLocatorKey, <columns> from <table>
> Part 2: For queries allow rowlocator hint(s) that could be provided as part
> of the query where one or more row locators specified for a table could be
> checked first by the query processor for results of the query like so:
> Select <columns> from <table> where <conditions>
> RowLocationHints:Table(rowlocation1,rowlocation2,rowlocation3)
> The query processor would check the row locations first. If a single result
> is to be found incase of a select top 1 or conditions involving a column
> with unique index / PK then it could stop and return the result.
> If the result is not found on the row location (incase of updates or
> deleted) or if the situation doesnot involve select top 1s or unique index /
> PK then the query processor should continue as it normally would.
> regards
> Manasvin
It's been suggested and even implemented before (Oracle). Search for
"Invalid ROWID" and "ROWID problem" in Oracle newsgroups and start
learning why this idea is not as smart as you think.|||You are absolutely correct Piyush, these actions as you describe them happen
ALL the time! They are called using a PRIMARY/UNIQUE KEY value that is
INDEXED as the lookup for the UPDATE/DELETE for the row originally accessed.
And since the data page associated with this particular row (and it's PK/UK
index) will probably still be in RAM (due to SQL Server's incredibly
effective caching algorithms) this subsequent lookup for the DML statement
will likely occur within a few milliseconds tops.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Manasvin" <piyush-at-manasvin-dot-com> wrote in message
news:uGW5pqHqHHA.1240@.TK2MSFTNGP04.phx.gbl...
>I think this feature is NOT to suggest moving away from a relational system
>but a smarter one. A hint based pattern which is already being used on
>various other aspects including choosing an index for instance withing a
>query. The idea is that requery for the same record or row shouldnt take
>the same effort regardless of cache hits or misses.
> In a typical scenario which I believe happens often illustrated below:
> A record a is queried for viewing.
> Its at this stage the query process would anyways be able to accumalate
> the rowlocation since its got there to collect the data row anyways.
> the application which queried for this data and is now displaying the
> row(s) could maintain the rowlocations keys (and which are not meant to be
> used as permanent or static values). most times the application has
> disconnected and the RDMBS is busy to serve other applications and their
> queries
> Now if there is a requery or an update / delete to the above said row(s)
> the rowlocation(s) could be supplied for these specific queries as a 'hint
> only' but not to undermine the consistency or reliability of the query
> process, rather a smarter way just get to the data quickly. in any case
> this hint(s) may not be any good but could very well be enough to make a
> difference.
> i believe the above sequence of application events or actions do happen
> quite commonly.amongst many application if not most.
> I hope the scenario above makes things a bit clearer. Am I the only one
> who sees this as a very useful feature ?
> regards
> Piyush
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:Oxvw2NGqHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> It would be great if we could use the "row locator" which is a
>> combination of fileid, page id and the row id of the row provided as a
>> 'hint' within the query (like we do for indexes) and get the desired
>> row(s). This would certainly be a great advantage specifically in
>> searching for columns which are PKs or have uniquely constrained
>> indexes, or select top 1s.
>> Part of the beauty of a relational system is that the physical location
>> of a piece of data is abstracted from us. How are you going to know the
>> fileid, page id and row id of a particular row? How expensive is that
>> part of the lookup going to be? And even now that you have it, how do
>> you know it will be in the same physical location next week, tomorrow, or
>> even in five minutes?
>
> "rpresser" <rpresser@.gmail.com> wrote in message
> news:1181151962.535269.172900@.z28g2000prd.googlegroups.com...
>> On Jun 6, 1:23 pm, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
>> It would be great if we could use the "row locator" which is a
>> combination
>> of fileid, page id and the row id of the row provided as a 'hint' within
>> the
>> query (like we do for indexes) and get the desired row(s).
>> Translation: Allow us to throw away the relational model and use a
>> network model database instead.
>> Ain't gonna happen.
>> It *might* have utility in a very few performance-related scenarios.
>> But 99% of the situations that use SQL Server would obtain absolutely
>> zero benefit.
>> If you want a network model database, use one. Leave SQL Server alone.
>|||having read a lot bit about like you said in google, I believe there is a
big difference in the way implementation of this feature is being suggested.
A less hard and more flexible approach can easily be worked out. also kindly
my secondary post attached to the thread
"Alex Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1181163417.397905.229550@.g37g2000prf.googlegroups.com...
> On Jun 6, 12:23 pm, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
>> Hi.
>> I've posted this to the SQL wish center (Connect/sqlserver/feedback) as a
>> suggested new feature.
>> (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...).
>> Would appreciate your comments and thoughts towards feasibility and
>> usability.
>> Suggested feature post as below:
>> Description
>> Processing queries involves many steps by the query processing engine.
>> Eventually the row containing the data queried for is found, i.e. either
>> involving few steps, if an index is used (generally preferred scenario)
>> or
>> in case of searching the table for every row (like a heap) if no index is
>> used (worst case scenario).
>> If similar queries are repeated, sql server may use the cache to deliver
>> results faster. However we all know this "quickness" may not be reliable
>> in
>> very large tables, limited resources or fairly big timegaps (due to cache
>> timeouts).
>> It would be great if we could use the "row locator" which is a
>> combination
>> of fileid, page id and the row id of the row provided as a 'hint' within
>> the
>> query (like we do for indexes) and get the desired row(s). This would
>> certainly be a great advantage specifically in searching for columns
>> which
>> are PKs or have uniquely constrained indexes, or select top 1s.
>> I feel this feature could have a significant improvement in the time it
>> takes to get the result especially for a repeat query. Common scenarios
>> include queries for updates or deletes especially in disconnected
>> scenarios
>> more common in web applications.
>> Proposed Solution
>> Two parts to this Solution:
>> Part 1: Have a new table linked function called table.GetRowLocatorKey()
>> or
>> similar construct/syntax to get the row locator (fileid,pageid,rowid) key
>> for the row.
>> i.e.
>> select table.GetRowLocatorKey, <columns> from <table>
>> Part 2: For queries allow rowlocator hint(s) that could be provided as
>> part
>> of the query where one or more row locators specified for a table could
>> be
>> checked first by the query processor for results of the query like so:
>> Select <columns> from <table> where <conditions>
>> RowLocationHints:Table(rowlocation1,rowlocation2,rowlocation3)
>> The query processor would check the row locations first. If a single
>> result
>> is to be found incase of a select top 1 or conditions involving a column
>> with unique index / PK then it could stop and return the result.
>> If the result is not found on the row location (incase of updates or
>> deleted) or if the situation doesnot involve select top 1s or unique
>> index /
>> PK then the query processor should continue as it normally would.
>> regards
>> Manasvin
> It's been suggested and even implemented before (Oracle). Search for
> "Invalid ROWID" and "ROWID problem" in Oracle newsgroups and start
> learning why this idea is not as smart as you think.
>|||yes and all I am saying then is that cache may not be dependable in larger
time gaps or very big table sets. infact it could be faster than using the
cache and in a high load scenario could make a significant difference for
better.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OyZy88HqHHA.196@.TK2MSFTNGP05.phx.gbl...
> You are absolutely correct Piyush, these actions as you describe them
> happen ALL the time! They are called using a PRIMARY/UNIQUE KEY value
> that is INDEXED as the lookup for the UPDATE/DELETE for the row originally
> accessed. And since the data page associated with this particular row (and
> it's PK/UK index) will probably still be in RAM (due to SQL Server's
> incredibly effective caching algorithms) this subsequent lookup for the
> DML statement will likely occur within a few milliseconds tops.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Manasvin" <piyush-at-manasvin-dot-com> wrote in message
> news:uGW5pqHqHHA.1240@.TK2MSFTNGP04.phx.gbl...
>>I think this feature is NOT to suggest moving away from a relational
>>system but a smarter one. A hint based pattern which is already being used
>>on various other aspects including choosing an index for instance withing
>>a query. The idea is that requery for the same record or row shouldnt take
>>the same effort regardless of cache hits or misses.
>> In a typical scenario which I believe happens often illustrated below:
>> A record a is queried for viewing.
>> Its at this stage the query process would anyways be able to accumalate
>> the rowlocation since its got there to collect the data row anyways.
>> the application which queried for this data and is now displaying the
>> row(s) could maintain the rowlocations keys (and which are not meant to
>> be used as permanent or static values). most times the application has
>> disconnected and the RDMBS is busy to serve other applications and their
>> queries
>> Now if there is a requery or an update / delete to the above said row(s)
>> the rowlocation(s) could be supplied for these specific queries as a
>> 'hint only' but not to undermine the consistency or reliability of the
>> query process, rather a smarter way just get to the data quickly. in any
>> case this hint(s) may not be any good but could very well be enough to
>> make a difference.
>> i believe the above sequence of application events or actions do happen
>> quite commonly.amongst many application if not most.
>> I hope the scenario above makes things a bit clearer. Am I the only one
>> who sees this as a very useful feature ?
>> regards
>> Piyush
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:Oxvw2NGqHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> It would be great if we could use the "row locator" which is a
>> combination of fileid, page id and the row id of the row provided as a
>> 'hint' within the query (like we do for indexes) and get the desired
>> row(s). This would certainly be a great advantage specifically in
>> searching for columns which are PKs or have uniquely constrained
>> indexes, or select top 1s.
>> Part of the beauty of a relational system is that the physical location
>> of a piece of data is abstracted from us. How are you going to know the
>> fileid, page id and row id of a particular row? How expensive is that
>> part of the lookup going to be? And even now that you have it, how do
>> you know it will be in the same physical location next week, tomorrow,
>> or even in five minutes?
>>
>> "rpresser" <rpresser@.gmail.com> wrote in message
>> news:1181151962.535269.172900@.z28g2000prd.googlegroups.com...
>> On Jun 6, 1:23 pm, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
>> It would be great if we could use the "row locator" which is a
>> combination
>> of fileid, page id and the row id of the row provided as a 'hint'
>> within the
>> query (like we do for indexes) and get the desired row(s).
>> Translation: Allow us to throw away the relational model and use a
>> network model database instead.
>> Ain't gonna happen.
>> It *might* have utility in a very few performance-related scenarios.
>> But 99% of the situations that use SQL Server would obtain absolutely
>> zero benefit.
>> If you want a network model database, use one. Leave SQL Server alone.
>>
>|||On 6 Jun, 22:19, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
> yes and all I am saying then is that cache may not be dependable in larger
> time gaps or very big table sets. infact it could be faster than using the
> cache and in a high load scenario could make a significant difference for
> better.
>
The whole suggestion is wrong-headed. Performance is determined solely
by the physical implementation, to which the presence or absence of a
"row locator" adds little or nothing. The advantages of exposing a
physical row locator are tiny when compared to other engine-level
enhancements that could be made but the disadvantages are enormous.
If you want real improvements then let's suggest better support for
Data Independence in the engine.
--
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
--|||On 6 Jun, 21:38, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
> I think this feature is NOT to suggest moving away from a relational system
> but a smarter one.
In that case I suggest you don't know what a relational system is.
> Now if there is a requery or an update / delete to the above said row(s) the
> rowlocation(s) could be supplied for these specific queries as a 'hint only'
> but not to undermine the consistency or reliability of the query process,
> rather a smarter way just get to the data quickly. in any case this hint(s)
> may not be any good but could very well be enough to make a difference.
> i believe the above sequence of application events or actions do happen
> quite commonly.amongst many application if not most.
>
This sounds like a server keyset-based cursor. There is absolutely no
need to return a row locator to the client in order to achieve that.
Let the DBMS handle it. You could I suppose have a hint that pinned
the set of rows in cache, but on the whole SQL Server is pretty good
at cache anyway.
--
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
--|||Do you think a 'row-locator' would be 'cached' later either' Physical I/O
is responsible for at LEAST 80% of the performance issues most database
applications have. How would the engine use this row-locator to get to the
actual row of data stored on some 8K datapage? SOMEHOW, SOMEWAY, some
physical lookup is gonna be required. That information won't be in cache
any longer or more likely than the index page(s) will! Also you are
Completely ignoring the issue of what happens when someone else updates the
row before you try to and, due to making a varchar column value larger that
row no longer fits in the same row-locator slot. Oopsie!! You just had an
error get thrown when you tried to update missing data. Wait, it gets even
better. Say someone did an insert during this delay and the engine placed a
NEW row in that same row-locator slot. Now it is even worse, because you
update the wrong row.
Do yourself a favor and drop this line of thinking. It is REALLY, REALLY
bad from a number of standpoints. :-)
--
TheSQLGuru
President
Indicium Resources, Inc.
"Manasvin" <piyush-at-manasvin-dot-com> wrote in message
news:ugUkQBIqHHA.3312@.TK2MSFTNGP05.phx.gbl...
> yes and all I am saying then is that cache may not be dependable in larger
> time gaps or very big table sets. infact it could be faster than using the
> cache and in a high load scenario could make a significant difference for
> better.
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:OyZy88HqHHA.196@.TK2MSFTNGP05.phx.gbl...
>> You are absolutely correct Piyush, these actions as you describe them
>> happen ALL the time! They are called using a PRIMARY/UNIQUE KEY value
>> that is INDEXED as the lookup for the UPDATE/DELETE for the row
>> originally accessed. And since the data page associated with this
>> particular row (and it's PK/UK index) will probably still be in RAM (due
>> to SQL Server's incredibly effective caching algorithms) this subsequent
>> lookup for the DML statement will likely occur within a few milliseconds
>> tops.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "Manasvin" <piyush-at-manasvin-dot-com> wrote in message
>> news:uGW5pqHqHHA.1240@.TK2MSFTNGP04.phx.gbl...
>>I think this feature is NOT to suggest moving away from a relational
>>system but a smarter one. A hint based pattern which is already being
>>used on various other aspects including choosing an index for instance
>>withing a query. The idea is that requery for the same record or row
>>shouldnt take the same effort regardless of cache hits or misses.
>> In a typical scenario which I believe happens often illustrated below:
>> A record a is queried for viewing.
>> Its at this stage the query process would anyways be able to accumalate
>> the rowlocation since its got there to collect the data row anyways.
>> the application which queried for this data and is now displaying the
>> row(s) could maintain the rowlocations keys (and which are not meant to
>> be used as permanent or static values). most times the application has
>> disconnected and the RDMBS is busy to serve other applications and their
>> queries
>> Now if there is a requery or an update / delete to the above said row(s)
>> the rowlocation(s) could be supplied for these specific queries as a
>> 'hint only' but not to undermine the consistency or reliability of the
>> query process, rather a smarter way just get to the data quickly. in any
>> case this hint(s) may not be any good but could very well be enough to
>> make a difference.
>> i believe the above sequence of application events or actions do happen
>> quite commonly.amongst many application if not most.
>> I hope the scenario above makes things a bit clearer. Am I the only one
>> who sees this as a very useful feature ?
>> regards
>> Piyush
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:Oxvw2NGqHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> It would be great if we could use the "row locator" which is a
>> combination of fileid, page id and the row id of the row provided as a
>> 'hint' within the query (like we do for indexes) and get the desired
>> row(s). This would certainly be a great advantage specifically in
>> searching for columns which are PKs or have uniquely constrained
>> indexes, or select top 1s.
>> Part of the beauty of a relational system is that the physical location
>> of a piece of data is abstracted from us. How are you going to know
>> the fileid, page id and row id of a particular row? How expensive is
>> that part of the lookup going to be? And even now that you have it,
>> how do you know it will be in the same physical location next week,
>> tomorrow, or even in five minutes?
>>
>> "rpresser" <rpresser@.gmail.com> wrote in message
>> news:1181151962.535269.172900@.z28g2000prd.googlegroups.com...
>> On Jun 6, 1:23 pm, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
>> It would be great if we could use the "row locator" which is a
>> combination
>> of fileid, page id and the row id of the row provided as a 'hint'
>> within the
>> query (like we do for indexes) and get the desired row(s).
>> Translation: Allow us to throw away the relational model and use a
>> network model database instead.
>> Ain't gonna happen.
>> It *might* have utility in a very few performance-related scenarios.
>> But 99% of the situations that use SQL Server would obtain absolutely
>> zero benefit.
>> If you want a network model database, use one. Leave SQL Server alone.
>>
>>
>|||Manasvin (piyush-at-manasvin-dot-com) writes:
> Part 2: For queries allow rowlocator hint(s) that could be provided as
> part of the query where one or more row locators specified for a table
> could be checked first by the query processor for results of the query
> like so:
> Select <columns> from <table> where <conditions>
> RowLocationHints:Table(rowlocation1,rowlocation2,rowlocation3)
> The query processor would check the row locations first. If a single
> result is to be found incase of a select top 1 or conditions involving a
> column with unique index / PK then it could stop and return the result.
> If the result is not found on the row location (incase of updates or
> deleted) or if the situation doesnot involve select top 1s or unique index
> / PK then the query processor should continue as it normally would.
(What has this to do with English Query? I removed that newsgroup from
the Newsgroups line.)
Since reads on primary keys are efficient any way, the only time this
could help is when you have TOP 1 on some complex ORDER BY condition.
Eh, wait, not even that, since SQL Server would still have to validate
that this is still the right row to return.
Most queries read multiple rows, in which case there would have to be a
lot of row locations in the hint.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Oxvw2NGqHHA.3512@.TK2MSFTNGP06.phx.gbl...
>.
> Part of the beauty of a relational system is that the physical location of
> a piece of data is abstracted from us. How are you going to know the
> fileid, page id and row id of a particular row? .
You have you head inside when it should be outside. From an application
developers
point of view what your describing is a KEY.
Indexer Expression
http://www.alphora.com/docs/O-System.iIndexer.html
www.beyondsql.blogspot.com

Get Quick results using "Row Locator"s (fileid+pageid+rowid)

> It would be great if we could use the "row locator" which is a combination
> of fileid, page id and the row id of the row provided as a 'hint' within
> the query (like we do for indexes) and get the desired row(s). This would
> certainly be a great advantage specifically in searching for columns which
> are PKs or have uniquely constrained indexes, or select top 1s.
Part of the beauty of a relational system is that the physical location of a
piece of data is abstracted from us. How are you going to know the fileid,
page id and row id of a particular row? How expensive is that part of the
lookup going to be? And even now that you have it, how do you know it will
be in the same physical location next week, tomorrow, or even in five
minutes?
You are absolutely correct Piyush, these actions as you describe them happen
ALL the time! They are called using a PRIMARY/UNIQUE KEY value that is
INDEXED as the lookup for the UPDATE/DELETE for the row originally accessed.
And since the data page associated with this particular row (and it's PK/UK
index) will probably still be in RAM (due to SQL Server's incredibly
effective caching algorithms) this subsequent lookup for the DML statement
will likely occur within a few milliseconds tops.
TheSQLGuru
President
Indicium Resources, Inc.
"Manasvin" <piyush-at-manasvin-dot-com> wrote in message
news:uGW5pqHqHHA.1240@.TK2MSFTNGP04.phx.gbl...
>I think this feature is NOT to suggest moving away from a relational system
>but a smarter one. A hint based pattern which is already being used on
>various other aspects including choosing an index for instance withing a
>query. The idea is that requery for the same record or row shouldnt take
>the same effort regardless of cache hits or misses.
> In a typical scenario which I believe happens often illustrated below:
> A record a is queried for viewing.
> Its at this stage the query process would anyways be able to accumalate
> the rowlocation since its got there to collect the data row anyways.
> the application which queried for this data and is now displaying the
> row(s) could maintain the rowlocations keys (and which are not meant to be
> used as permanent or static values). most times the application has
> disconnected and the RDMBS is busy to serve other applications and their
> queries
> Now if there is a requery or an update / delete to the above said row(s)
> the rowlocation(s) could be supplied for these specific queries as a 'hint
> only' but not to undermine the consistency or reliability of the query
> process, rather a smarter way just get to the data quickly. in any case
> this hint(s) may not be any good but could very well be enough to make a
> difference.
> i believe the above sequence of application events or actions do happen
> quite commonly.amongst many application if not most.
> I hope the scenario above makes things a bit clearer. Am I the only one
> who sees this as a very useful feature ?
> regards
> Piyush
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:Oxvw2NGqHHA.3512@.TK2MSFTNGP06.phx.gbl...
>
> "rpresser" <rpresser@.gmail.com> wrote in message
> news:1181151962.535269.172900@.z28g2000prd.googlegr oups.com...
>
|||On 6 Jun, 22:19, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
> yes and all I am saying then is that cache may not be dependable in larger
> time gaps or very big table sets. infact it could be faster than using the
> cache and in a high load scenario could make a significant difference for
> better.
>
The whole suggestion is wrong-headed. Performance is determined solely
by the physical implementation, to which the presence or absence of a
"row locator" adds little or nothing. The advantages of exposing a
physical row locator are tiny when compared to other engine-level
enhancements that could be made but the disadvantages are enormous.
If you want real improvements then let's suggest better support for
Data Independence in the engine.
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
|||On 6 Jun, 21:38, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
> I think this feature is NOT to suggest moving away from a relational system
> but a smarter one.
In that case I suggest you don't know what a relational system is.

> Now if there is a requery or an update / delete to the above said row(s) the
> rowlocation(s) could be supplied for these specific queries as a 'hint only'
> but not to undermine the consistency or reliability of the query process,
> rather a smarter way just get to the data quickly. in any case this hint(s)
> may not be any good but could very well be enough to make a difference.
> i believe the above sequence of application events or actions do happen
> quite commonly.amongst many application if not most.
>
This sounds like a server keyset-based cursor. There is absolutely no
need to return a row locator to the client in order to achieve that.
Let the DBMS handle it. You could I suppose have a hint that pinned
the set of rows in cache, but on the whole SQL Server is pretty good
at cache anyway.
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
|||Do you think a 'row-locator' would be 'cached' later either? Physical I/O
is responsible for at LEAST 80% of the performance issues most database
applications have. How would the engine use this row-locator to get to the
actual row of data stored on some 8K datapage? SOMEHOW, SOMEWAY, some
physical lookup is gonna be required. That information won't be in cache
any longer or more likely than the index page(s) will! Also you are
Completely ignoring the issue of what happens when someone else updates the
row before you try to and, due to making a varchar column value larger that
row no longer fits in the same row-locator slot. Oopsie!! You just had an
error get thrown when you tried to update missing data. Wait, it gets even
better. Say someone did an insert during this delay and the engine placed a
NEW row in that same row-locator slot. Now it is even worse, because you
update the wrong row.
Do yourself a favor and drop this line of thinking. It is REALLY, REALLY
bad from a number of standpoints. :-)
TheSQLGuru
President
Indicium Resources, Inc.
"Manasvin" <piyush-at-manasvin-dot-com> wrote in message
news:ugUkQBIqHHA.3312@.TK2MSFTNGP05.phx.gbl...
> yes and all I am saying then is that cache may not be dependable in larger
> time gaps or very big table sets. infact it could be faster than using the
> cache and in a high load scenario could make a significant difference for
> better.
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:OyZy88HqHHA.196@.TK2MSFTNGP05.phx.gbl...
>
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Oxvw2NGqHHA.3512@.TK2MSFTNGP06.phx.gbl...
>.
> Part of the beauty of a relational system is that the physical location of
> a piece of data is abstracted from us. How are you going to know the
> fileid, page id and row id of a particular row? .
You have you head inside when it should be outside. From an application
developers
point of view what your describing is a KEY.
Indexer Expression
http://www.alphora.com/docs/O-System.iIndexer.html
www.beyondsql.blogspot.com

Get Quick results using "Row Locator"s (fileid+pageid+rowid)

> It would be great if we could use the "row locator" which is a combination
> of fileid, page id and the row id of the row provided as a 'hint' within
> the query (like we do for indexes) and get the desired row(s). This would
> certainly be a great advantage specifically in searching for columns which
> are PKs or have uniquely constrained indexes, or select top 1s.
Part of the beauty of a relational system is that the physical location of a
piece of data is abstracted from us. How are you going to know the fileid,
page id and row id of a particular row? How expensive is that part of the
lookup going to be? And even now that you have it, how do you know it will
be in the same physical location next week, tomorrow, or even in five
minutes?
You are absolutely correct Piyush, these actions as you describe them happen
ALL the time! They are called using a PRIMARY/UNIQUE KEY value that is
INDEXED as the lookup for the UPDATE/DELETE for the row originally accessed.
And since the data page associated with this particular row (and it's PK/UK
index) will probably still be in RAM (due to SQL Server's incredibly
effective caching algorithms) this subsequent lookup for the DML statement
will likely occur within a few milliseconds tops.
TheSQLGuru
President
Indicium Resources, Inc.
"Manasvin" <piyush-at-manasvin-dot-com> wrote in message
news:uGW5pqHqHHA.1240@.TK2MSFTNGP04.phx.gbl...
>I think this feature is NOT to suggest moving away from a relational system
>but a smarter one. A hint based pattern which is already being used on
>various other aspects including choosing an index for instance withing a
>query. The idea is that requery for the same record or row shouldnt take
>the same effort regardless of cache hits or misses.
> In a typical scenario which I believe happens often illustrated below:
> A record a is queried for viewing.
> Its at this stage the query process would anyways be able to accumalate
> the rowlocation since its got there to collect the data row anyways.
> the application which queried for this data and is now displaying the
> row(s) could maintain the rowlocations keys (and which are not meant to be
> used as permanent or static values). most times the application has
> disconnected and the RDMBS is busy to serve other applications and their
> queries
> Now if there is a requery or an update / delete to the above said row(s)
> the rowlocation(s) could be supplied for these specific queries as a 'hint
> only' but not to undermine the consistency or reliability of the query
> process, rather a smarter way just get to the data quickly. in any case
> this hint(s) may not be any good but could very well be enough to make a
> difference.
> i believe the above sequence of application events or actions do happen
> quite commonly.amongst many application if not most.
> I hope the scenario above makes things a bit clearer. Am I the only one
> who sees this as a very useful feature ?
> regards
> Piyush
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:Oxvw2NGqHHA.3512@.TK2MSFTNGP06.phx.gbl...
>
> "rpresser" <rpresser@.gmail.com> wrote in message
> news:1181151962.535269.172900@.z28g2000prd.googlegr oups.com...
>
|||On 6 Jun, 22:19, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
> yes and all I am saying then is that cache may not be dependable in larger
> time gaps or very big table sets. infact it could be faster than using the
> cache and in a high load scenario could make a significant difference for
> better.
>
The whole suggestion is wrong-headed. Performance is determined solely
by the physical implementation, to which the presence or absence of a
"row locator" adds little or nothing. The advantages of exposing a
physical row locator are tiny when compared to other engine-level
enhancements that could be made but the disadvantages are enormous.
If you want real improvements then let's suggest better support for
Data Independence in the engine.
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
|||On 6 Jun, 21:38, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
> I think this feature is NOT to suggest moving away from a relational system
> but a smarter one.
In that case I suggest you don't know what a relational system is.

> Now if there is a requery or an update / delete to the above said row(s) the
> rowlocation(s) could be supplied for these specific queries as a 'hint only'
> but not to undermine the consistency or reliability of the query process,
> rather a smarter way just get to the data quickly. in any case this hint(s)
> may not be any good but could very well be enough to make a difference.
> i believe the above sequence of application events or actions do happen
> quite commonly.amongst many application if not most.
>
This sounds like a server keyset-based cursor. There is absolutely no
need to return a row locator to the client in order to achieve that.
Let the DBMS handle it. You could I suppose have a hint that pinned
the set of rows in cache, but on the whole SQL Server is pretty good
at cache anyway.
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
|||Do you think a 'row-locator' would be 'cached' later either? Physical I/O
is responsible for at LEAST 80% of the performance issues most database
applications have. How would the engine use this row-locator to get to the
actual row of data stored on some 8K datapage? SOMEHOW, SOMEWAY, some
physical lookup is gonna be required. That information won't be in cache
any longer or more likely than the index page(s) will! Also you are
Completely ignoring the issue of what happens when someone else updates the
row before you try to and, due to making a varchar column value larger that
row no longer fits in the same row-locator slot. Oopsie!! You just had an
error get thrown when you tried to update missing data. Wait, it gets even
better. Say someone did an insert during this delay and the engine placed a
NEW row in that same row-locator slot. Now it is even worse, because you
update the wrong row.
Do yourself a favor and drop this line of thinking. It is REALLY, REALLY
bad from a number of standpoints. :-)
TheSQLGuru
President
Indicium Resources, Inc.
"Manasvin" <piyush-at-manasvin-dot-com> wrote in message
news:ugUkQBIqHHA.3312@.TK2MSFTNGP05.phx.gbl...
> yes and all I am saying then is that cache may not be dependable in larger
> time gaps or very big table sets. infact it could be faster than using the
> cache and in a high load scenario could make a significant difference for
> better.
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:OyZy88HqHHA.196@.TK2MSFTNGP05.phx.gbl...
>
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Oxvw2NGqHHA.3512@.TK2MSFTNGP06.phx.gbl...
>.
> Part of the beauty of a relational system is that the physical location of
> a piece of data is abstracted from us. How are you going to know the
> fileid, page id and row id of a particular row? .
You have you head inside when it should be outside. From an application
developers
point of view what your describing is a KEY.
Indexer Expression
http://www.alphora.com/docs/O-System.iIndexer.html
www.beyondsql.blogspot.com

Get Quick results using "Row Locator"s (fileid+pageid+rowid)

Hi.
I've posted this to the SQL wish center (Connect/sqlserver/feedback) as a
suggested new feature.
(https://connect.microsoft.com/SQLSe...=281
531).
Would appreciate your comments and thoughts towards feasibility and
usability.
Suggested feature post as below:
Description
Processing queries involves many steps by the query processing engine.
Eventually the row containing the data queried for is found, i.e. either
involving few steps, if an index is used (generally preferred scenario) or
in case of searching the table for every row (like a heap) if no index is
used (worst case scenario).
If similar queries are repeated, sql server may use the cache to deliver
results faster. However we all know this "quickness" may not be reliable in
very large tables, limited resources or fairly big timegaps (due to cache
timeouts).
It would be great if we could use the "row locator" which is a combination
of fileid, page id and the row id of the row provided as a 'hint' within the
query (like we do for indexes) and get the desired row(s). This would
certainly be a great advantage specifically in searching for columns which
are PKs or have uniquely constrained indexes, or select top 1s.
I feel this feature could have a significant improvement in the time it
takes to get the result especially for a repeat query. Common scenarios
include queries for updates or deletes especially in disconnected scenarios
more common in web applications.
Proposed Solution
Two parts to this Solution:
Part 1: Have a new table linked function called table.GetRowLocatorKey() or
similar construct/syntax to get the row locator (fileid,pageid,rowid) key
for the row.
i.e.
select table.GetRowLocatorKey, <columns> from <table>
Part 2: For queries allow rowlocator hint(s) that could be provided as part
of the query where one or more row locators specified for a table could be
checked first by the query processor for results of the query like so:
Select <columns> from <table> where <conditions>
RowLocationHints:Table(rowlocation1,rowl
ocation2,rowlocation3)
The query processor would check the row locations first. If a single result
is to be found incase of a select top 1 or conditions involving a column
with unique index / PK then it could stop and return the result.
If the result is not found on the row location (incase of updates or
deleted) or if the situation doesnot involve select top 1s or unique index /
PK then the query processor should continue as it normally would.
regards
ManasvinOn Jun 6, 1:23 pm, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
> It would be great if we could use the "row locator" which is a combination
> of fileid, page id and the row id of the row provided as a 'hint' within t
he
> query (like we do for indexes) and get the desired row(s).
Translation: Allow us to throw away the relational model and use a
network model database instead.
Ain't gonna happen.
It *might* have utility in a very few performance-related scenarios.
But 99% of the situations that use SQL Server would obtain absolutely
zero benefit.
If you want a network model database, use one. Leave SQL Server alone.|||> It would be great if we could use the "row locator" which is a combination
> of fileid, page id and the row id of the row provided as a 'hint' within
> the query (like we do for indexes) and get the desired row(s). This would
> certainly be a great advantage specifically in searching for columns which
> are PKs or have uniquely constrained indexes, or select top 1s.
Part of the beauty of a relational system is that the physical location of a
piece of data is abstracted from us. How are you going to know the fileid,
page id and row id of a particular row? How expensive is that part of the
lookup going to be? And even now that you have it, how do you know it will
be in the same physical location next week, tomorrow, or even in five
minutes?|||I think this feature is NOT to suggest moving away from a relational system
but a smarter one. A hint based pattern which is already being used on
various other aspects including choosing an index for instance withing a
query. The idea is that requery for the same record or row shouldnt take the
same effort regardless of cache hits or misses.
In a typical scenario which I believe happens often illustrated below:
A record a is queried for viewing.
Its at this stage the query process would anyways be able to accumalate the
rowlocation since its got there to collect the data row anyways.
the application which queried for this data and is now displaying the row(s)
could maintain the rowlocations keys (and which are not meant to be used as
permanent or static values). most times the application has disconnected and
the RDMBS is busy to serve other applications and their queries
Now if there is a requery or an update / delete to the above said row(s) the
rowlocation(s) could be supplied for these specific queries as a 'hint only'
but not to undermine the consistency or reliability of the query process,
rather a smarter way just get to the data quickly. in any case this hint(s)
may not be any good but could very well be enough to make a difference.
i believe the above sequence of application events or actions do happen
quite commonly.amongst many application if not most.
I hope the scenario above makes things a bit clearer. Am I the only one who
sees this as a very useful feature ?
regards
Piyush
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:Oxvw2NGqHHA.3512@.TK2MSFTNGP06.phx.gbl...
> Part of the beauty of a relational system is that the physical location of
> a piece of data is abstracted from us. How are you going to know the
> fileid, page id and row id of a particular row? How expensive is that
> part of the lookup going to be? And even now that you have it, how do you
> know it will be in the same physical location next week, tomorrow, or even
> in five minutes?
"rpresser" <rpresser@.gmail.com> wrote in message
news:1181151962.535269.172900@.z28g2000prd.googlegroups.com...
> On Jun 6, 1:23 pm, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
> Translation: Allow us to throw away the relational model and use a
> network model database instead.
> Ain't gonna happen.
> It *might* have utility in a very few performance-related scenarios.
> But 99% of the situations that use SQL Server would obtain absolutely
> zero benefit.
> If you want a network model database, use one. Leave SQL Server alone.
>|||On Jun 6, 12:23 pm, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
> Hi.
> I've posted this to the SQL wish center (Connect/sqlserver/feedback) as a
> suggested new feature.
> (https://connect.microsoft.com/SQLSe...back.aspx?Fe...)
.
> Would appreciate your comments and thoughts towards feasibility and
> usability.
> Suggested feature post as below:
> Description
> Processing queries involves many steps by the query processing engine.
> Eventually the row containing the data queried for is found, i.e. either
> involving few steps, if an index is used (generally preferred scenario) or
> in case of searching the table for every row (like a heap) if no index is
> used (worst case scenario).
> If similar queries are repeated, sql server may use the cache to deliver
> results faster. However we all know this "quickness" may not be reliable i
n
> very large tables, limited resources or fairly big timegaps (due to cache
> timeouts).
> It would be great if we could use the "row locator" which is a combination
> of fileid, page id and the row id of the row provided as a 'hint' within t
he
> query (like we do for indexes) and get the desired row(s). This would
> certainly be a great advantage specifically in searching for columns which
> are PKs or have uniquely constrained indexes, or select top 1s.
> I feel this feature could have a significant improvement in the time it
> takes to get the result especially for a repeat query. Common scenarios
> include queries for updates or deletes especially in disconnected scenario
s
> more common in web applications.
> Proposed Solution
> Two parts to this Solution:
> Part 1: Have a new table linked function called table.GetRowLocatorKey() o
r
> similar construct/syntax to get the row locator (fileid,pageid,rowid) key
> for the row.
> i.e.
> select table.GetRowLocatorKey, <columns> from <table>
> Part 2: For queries allow rowlocator hint(s) that could be provided as par
t
> of the query where one or more row locators specified for a table could be
> checked first by the query processor for results of the query like so:
> Select <columns> from <table> where <conditions>
> RowLocationHints:Table(rowlocation1,rowl
ocation2,rowlocation3)
> The query processor would check the row locations first. If a single resul
t
> is to be found incase of a select top 1 or conditions involving a column
> with unique index / PK then it could stop and return the result.
> If the result is not found on the row location (incase of updates or
> deleted) or if the situation doesnot involve select top 1s or unique index
/
> PK then the query processor should continue as it normally would.
> regards
> Manasvin
It's been suggested and even implemented before (Oracle). Search for
"Invalid ROWID" and "ROWID problem" in Oracle newsgroups and start
learning why this idea is not as smart as you think.|||You are absolutely correct Piyush, these actions as you describe them happen
ALL the time! They are called using a PRIMARY/UNIQUE KEY value that is
INDEXED as the lookup for the UPDATE/DELETE for the row originally accessed.
And since the data page associated with this particular row (and it's PK/UK
index) will probably still be in RAM (due to SQL Server's incredibly
effective caching algorithms) this subsequent lookup for the DML statement
will likely occur within a few milliseconds tops.
TheSQLGuru
President
Indicium Resources, Inc.
"Manasvin" <piyush-at-manasvin-dot-com> wrote in message
news:uGW5pqHqHHA.1240@.TK2MSFTNGP04.phx.gbl...
>I think this feature is NOT to suggest moving away from a relational system
>but a smarter one. A hint based pattern which is already being used on
>various other aspects including choosing an index for instance withing a
>query. The idea is that requery for the same record or row shouldnt take
>the same effort regardless of cache hits or misses.
> In a typical scenario which I believe happens often illustrated below:
> A record a is queried for viewing.
> Its at this stage the query process would anyways be able to accumalate
> the rowlocation since its got there to collect the data row anyways.
> the application which queried for this data and is now displaying the
> row(s) could maintain the rowlocations keys (and which are not meant to be
> used as permanent or static values). most times the application has
> disconnected and the RDMBS is busy to serve other applications and their
> queries
> Now if there is a requery or an update / delete to the above said row(s)
> the rowlocation(s) could be supplied for these specific queries as a 'hint
> only' but not to undermine the consistency or reliability of the query
> process, rather a smarter way just get to the data quickly. in any case
> this hint(s) may not be any good but could very well be enough to make a
> difference.
> i believe the above sequence of application events or actions do happen
> quite commonly.amongst many application if not most.
> I hope the scenario above makes things a bit clearer. Am I the only one
> who sees this as a very useful feature ?
> regards
> Piyush
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:Oxvw2NGqHHA.3512@.TK2MSFTNGP06.phx.gbl...
>
> "rpresser" <rpresser@.gmail.com> wrote in message
> news:1181151962.535269.172900@.z28g2000prd.googlegroups.com...
>|||having read a lot bit about like you said in google, I believe there is a
big difference in the way implementation of this feature is being suggested.
A less hard and more flexible approach can easily be worked out. also kindly
my secondary post attached to the thread
"Alex Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1181163417.397905.229550@.g37g2000prf.googlegroups.com...
> On Jun 6, 12:23 pm, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
> It's been suggested and even implemented before (Oracle). Search for
> "Invalid ROWID" and "ROWID problem" in Oracle newsgroups and start
> learning why this idea is not as smart as you think.
>|||yes and all I am saying then is that cache may not be dependable in larger
time gaps or very big table sets. infact it could be faster than using the
cache and in a high load scenario could make a significant difference for
better.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OyZy88HqHHA.196@.TK2MSFTNGP05.phx.gbl...
> You are absolutely correct Piyush, these actions as you describe them
> happen ALL the time! They are called using a PRIMARY/UNIQUE KEY value
> that is INDEXED as the lookup for the UPDATE/DELETE for the row originally
> accessed. And since the data page associated with this particular row (and
> it's PK/UK index) will probably still be in RAM (due to SQL Server's
> incredibly effective caching algorithms) this subsequent lookup for the
> DML statement will likely occur within a few milliseconds tops.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Manasvin" <piyush-at-manasvin-dot-com> wrote in message
> news:uGW5pqHqHHA.1240@.TK2MSFTNGP04.phx.gbl...
>|||On 6 Jun, 22:19, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
> yes and all I am saying then is that cache may not be dependable in larger
> time gaps or very big table sets. infact it could be faster than using the
> cache and in a high load scenario could make a significant difference for
> better.
>
The whole suggestion is wrong-headed. Performance is determined solely
by the physical implementation, to which the presence or absence of a
"row locator" adds little or nothing. The advantages of exposing a
physical row locator are tiny when compared to other engine-level
enhancements that could be made but the disadvantages are enormous.
If you want real improvements then let's suggest better support for
Data Independence in the engine.
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
--|||On 6 Jun, 21:38, "Manasvin" <piyush-at-manasvin-dot-com> wrote:
> I think this feature is NOT to suggest moving away from a relational syste
m
> but a smarter one.
In that case I suggest you don't know what a relational system is.

> Now if there is a requery or an update / delete to the above said row(s) t
he
> rowlocation(s) could be supplied for these specific queries as a 'hint onl
y'
> but not to undermine the consistency or reliability of the query process,
> rather a smarter way just get to the data quickly. in any case this hint(s
)
> may not be any good but could very well be enough to make a difference.
> i believe the above sequence of application events or actions do happen
> quite commonly.amongst many application if not most.
>
This sounds like a server keyset-based cursor. There is absolutely no
need to return a row locator to the client in order to achieve that.
Let the DBMS handle it. You could I suppose have a hint that pinned
the set of rows in cache, but on the whole SQL Server is pretty good
at cache anyway.
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
--