getProcedureColumns behavior changed from SQLServer 2000 to 2005?

A stored procedure takes an IN parameter, an INOUT parameter, and returns an OUT parameter. When this stored procedure is defined in SQL Server 2000, the JDBC DatabaseMetadata method getProcedureColumns() returns three rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2), and one for OUT parameter (COLUMN_TYPE=5).However, when the same stored procedure is defined in SQL Server 2005 (SP2), the getProcedureColumns() method returns only two rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2).No row for the OUT parameter is returned.

jTDS JDBC driver was used for both of the above tests. When the Microsoft JDBC Driver for 2005 was used against SQL Server 2005, the same behavior (only two rows in the resultset) was observed.

Has someone else run into such a problem? Is this a bug in SQL Server 2005 because the same jTDS driver works as expected against SQL Server 2000 but not against SQL Server 2005? Any feedback will be appreciated.

Thanks,

-- Damodar PeriwalCan someone from Microsoft verify if this is a bug in SQL Server 2005 or not? Thanks.

getProcedureColumns behavior changed from SQLServer 2000 to 2005?

A stored procedure takes an IN parameter, an INOUT parameter, and returns an OUT parameter. When this stored procedure is defined in SQL Server 2000, the JDBC DatabaseMetadata method getProcedureColumns() returns three rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2), and one for OUT parameter (COLUMN_TYPE=5).However, when the same stored procedure is defined in SQL Server 2005 (SP2), the getProcedureColumns() method returns only two rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2).No row for the OUT parameter is returned.

jTDS JDBC driver was used for both of the above tests. When the Microsoft JDBC Driver for 2005 was used against SQL Server 2005, the same behavior (only two rows in the resultset) was observed.

Has someone else run into such a problem? Is this a bug in SQL Server 2005 because the same jTDS driver works as expected against SQL Server 2000 but not against SQL Server 2005? Any feedback will be appreciated.

Thanks,

-- Damodar PeriwalCan someone from Microsoft verify if this is a bug in SQL Server 2005 or not? Thanks.sql

GetDate() in UDF

I understand that you can not use GetDate() in a UDF.
I also can not pass a parameter to the UDF (because this is an Access to SQL
Server conversion, and the program that calls this UDF does not pass any
parameter to it).
So, I am trying to create a View for GetDate() like the codes below.
The issue is, I also need to select from another table (tblA) besides
getting the GetDate() value.
Is the following codes correct and efficient on how to do that ?
Thanks.
create view get_date
as
select getdate()dt
CREATE function dbo.udftemp()
returns @.myTable TABLE(id varchar(10),datex datetime)
AS BEGIN
INSERT INTO @.myTable(id,datex)
select tblA.id,dt
FROM tblA, get_date --select from tblA and the view
WHERE tblA.colA <> 'XYZ'
return
end>I understand that you can not use GetDate() in a UDF.
? This works on my 2005 server:
CREATE FUNCTION dbo.Func1 ()
RETURNS datetime
AS
BEGIN
return getdate()
END
GO
select dbo.Func1()
William|||We are using SQL2000, and unfortunately it does not work there.
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:O97ruV0BGHA.1032@.TK2MSFTNGP11.phx.gbl...
> ? This works on my 2005 server:
> CREATE FUNCTION dbo.Func1 ()
> RETURNS datetime
> AS
> BEGIN
> return getdate()
> END
> GO
> select dbo.Func1()
> --
> William
>|||William Stacey [MVP] (william.stacey@.gmail.com) writes:
> ? This works on my 2005 server:
> CREATE FUNCTION dbo.Func1 ()
> RETURNS datetime
> AS
> BEGIN
> return getdate()
> END
> GO
> select dbo.Func1()
Yes, but it does not work on SQL 2000.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||yes - that should be fine
not much else to do in this case, though [unless you're using sql2005,
which allows non-deterministic functions in UDFs]
fniles wrote:
> I understand that you can not use GetDate() in a UDF.
> I also can not pass a parameter to the UDF (because this is an Access to S
QL
> Server conversion, and the program that calls this UDF does not pass any
> parameter to it).
> So, I am trying to create a View for GetDate() like the codes below.
> The issue is, I also need to select from another table (tblA) besides
> getting the GetDate() value.
> Is the following codes correct and efficient on how to do that ?
> Thanks.
> create view get_date
> as
> select getdate()dt
> CREATE function dbo.udftemp()
> returns @.myTable TABLE(id varchar(10),datex datetime)
> AS BEGIN
> INSERT INTO @.myTable(id,datex)
> select tblA.id,dt
> FROM tblA, get_date --select from tblA and the view
> WHERE tblA.colA <> 'XYZ'
> return
> end
>|||If it is a migration, then why not migrate to 2005 instead of 2000? Just
curious.
William Stacey [MVP]
"fniles" <fniles@.pfmail.com> wrote in message
news:e%23lFUa0BGHA.2644@.TK2MSFTNGP09.phx.gbl...
> We are using SQL2000, and unfortunately it does not work there.
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:O97ruV0BGHA.1032@.TK2MSFTNGP11.phx.gbl...
>|||He never said anything about 2000, so I gave it a shot.
William Stacey [MVP]
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9734F019C8C9DYazorman@.127.0.0.1...
> William Stacey [MVP] (william.stacey@.gmail.com) writes:
> Yes, but it does not work on SQL 2000.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for your reply.
Is SQL 2005 still a beta product or is it a release product ?
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:OynIJo0BGHA.228@.TK2MSFTNGP12.phx.gbl...
> If it is a migration, then why not migrate to 2005 instead of 2000? Just
> curious.
> --
> William Stacey [MVP]
> "fniles" <fniles@.pfmail.com> wrote in message
> news:e%23lFUa0BGHA.2644@.TK2MSFTNGP09.phx.gbl...
>|||RTM. You can get Sql Express 2005 for free currently and buy the Std and
Enterprise versions as normal.
http://www.microsoft.com/sql/default.mspx
William Stacey [MVP]
"fniles" <fniles@.pfmail.com> wrote in message
news:uOzdgy8BGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Thank you for your reply.
> Is SQL 2005 still a beta product or is it a release product ?
>
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:OynIJo0BGHA.228@.TK2MSFTNGP12.phx.gbl...
>|||was it the RTM? or a previous CTP release?
i created a function exactly like in RTM developer edition and it worked
fine.
i don't think edition should matter - but release might.
fniles wrote:
> I just install SQL 2005 Standard Edition, and try the GetDate function
> again, but it still gives me the same error.
> Here is my function:
> CREATE function dbo.udftemp()
> returns @.myTable TABLE(datex datetime)
> AS BEGIN
> INSERT INTO @.myTable(datex)
> select getdate()
> return
> end
> The error I got was: "Invalid use of 'getdate' within a function."
> Do I need SQL 2005 Enterprise Edition for the GetDate() to work ?
> Thanks.
>
> "Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
> news:uPy03j0BGHA.2320@.TK2MSFTNGP11.phx.gbl...
>
>
>

GetDate()

I would like to exec master..xp_fixeddrives which will give you the list of
physical drives sqlserver sees. Output is Drives, FreeSpace from
xp_fixeddrives.
I would like to write this information to a table with Drive,
FreeSpace,SampleTime.
The SampleTime will be the getdate() function.
Please help me with this issue.
ThanksCREATE TABLE #DriveData
(
Drive CHAR(2),
FreeSpace BIGINT,
SampleTime DATETIME NOT NULL DEFAULT GETDATE()
);
INSERT #DriveData
(
Drive,
FreeSpace
)
EXEC master..xp_fixeddrives;
SELECT Drive, FreeSpace, SampleTime
FROM #DriveData;
DROP TABLE #DriveData;
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:23A81366-D8DD-4833-B25C-96DF0561090D@.microsoft.com...
>I would like to exec master..xp_fixeddrives which will give you the list of
> physical drives sqlserver sees. Output is Drives, FreeSpace from
> xp_fixeddrives.
> I would like to write this information to a table with Drive,
> FreeSpace,SampleTime.
> The SampleTime will be the getdate() function.
> Please help me with this issue.
> Thanks
>
>|||CREATE TABLE #diskspace(
drivename VARCHAR(50),
MBFree INT)
INSERT INTO #diskspace(drivename,MBFree) exec master..xp_fixeddrives
SELECT * FROM #diskspace|||CREATE TABLE #diskspace(
drivename VARCHAR(50),
MBFree INT)
INSERT INTO #diskspace(drivename,MBFree) exec master..xp_fixeddrives
SELECT * FROM #diskspace|||Note the addition of the date field to the table.
CREATE TABLE #diskspace(
drivename VARCHAR(50),
MBFree INT,
sampledate DATETIME default getdate()
)sql

GetBytes issue

We recently change our application from Sun JDBC-ODBC
bridge driver to MS SQLServer JDBC driver and one of our
functionality is failing and after debuging dound that
getByte mentod is not retrving the data correctly rather
it change the data. The code is working fine with JdbcOdbc
driver, here is the sample code.
Any help is appreciated.
Thanks
Syed
stmt = objUtil.getConnection().createStatement();
result = stmt.executeQuery(strSQL);
byte[] gbArrKS = null;
while (result.next()){
gbArrKS= result.getBytes("BinrydataCol");
}
Hi. You'll get your best help if you describe the definition of the table
column, and maybe also show what the data really is.
thanks,
Joe Weinstein at BEA
Syed Hussain wrote:

> We recently change our application from Sun JDBC-ODBC
> bridge driver to MS SQLServer JDBC driver and one of our
> functionality is failing and after debuging dound that
> getByte mentod is not retrving the data correctly rather
> it change the data. The code is working fine with JdbcOdbc
> driver, here is the sample code.
> Any help is appreciated.
> Thanks
> --
> Syed
> stmt = objUtil.getConnection().createStatement();
> result = stmt.executeQuery(strSQL);
> byte[] gbArrKS = null;
> while (result.next()){
> gbArrKS= result.getBytes("BinrydataCol");
> }
>
sql

Get the owner of a table in SQLServer

Is there any way to get the owner of a table or a database object in SQL
Server? I want to check whether a given user id is the owner of a particular
table or not.
Please help me with an example if possible.
VenkatVenkat
SELECT ROUTINE_NAME,ROUTINE_SCHEMA
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMsShipped')=0
"Venkat" <tammana@.inooga.com> wrote in message
news:eDZqMy%23mFHA.3304@.tk2msftngp13.phx.gbl...
> Is there any way to get the owner of a table or a database object in SQL
> Server? I want to check whether a given user id is the owner of a
> particular table or not.
> Please help me with an example if possible.
> --
> Venkat
>|||VENKAT,
Execute the below system stored procedure
sp_help <object_name>
Execute the sp_help with out parameter to get the object owners for all
objects.
Thanks
Hari
SQL Server Mvp
"Venkat" <tammana@.inooga.com> wrote in message
news:eDZqMy%23mFHA.3304@.tk2msftngp13.phx.gbl...
> Is there any way to get the owner of a table or a database object in SQL
> Server? I want to check whether a given user id is the owner of a
> particular table or not.
> Please help me with an example if possible.
> --
> Venkat
>

Get the error in dos cmd line

Hi everyone,

I have a cmd file that executes sql statements:

something like this:

Code Snippet

OSQL -S %sqlServer% -E -b -n -i DATABASE.sql"
IF ERRORLEVEL 1 GOTO error
echo -

...

:error
@.ECHO An error occured in [%errorLevel%]
echo [%errorLevel%] > %1

GOTO EXIT

if there is an error, I print the errorlevel in a txt file wich is a parameter in the command (%1)

What I want is: instead of sending the %errorlevel% to the txt, I want to send the error description but I don't know the variable that keeps the error description.

Any help please

Thanx in advance

I really need to know if there is some variable that gives me the sql error because when I execute the cmd, if there's an error

the error is printed int the cmd window.

The errorlevel var just give me the number "1" but I would like to get something like

"An error in database ocurred due to database already exists" or something like that.

Please I'll apreciate any ideias you may have
|||

Instead of overwriting the error log file, I would just append the error to the file instead.

Code Snippet

ECHO OFF

OSQL -S %sqlServer% -E -b -n -i test.sql > %1
IF ERRORLEVEL 1 GOTO error
ECHO "" > %1
EXIT

:error
@.ECHO An error occured in [%errorLevel%]
echo [%errorLevel%] >> %1

This way you either have a blank file, or you have a file with an error message and errorLevel code.

|||Thanx for the answer ShawnNWF, that will do the trick for sure

Get the error in dos cmd line

Hi everyone,

I have a cmd file that executes sql statements:

something like this:

Code Snippet

OSQL -S %sqlServer% -E -b -n -i DATABASE.sql"
IF ERRORLEVEL 1 GOTO error
echo -

...

:error
@.ECHO An error occured in [%errorLevel%]
echo [%errorLevel%] > %1

GOTO EXIT

if there is an error, I print the errorlevel in a txt file wich is a parameter in the command (%1)

What I want is: instead of sending the %errorlevel% to the txt, I want to send the error description but I don't know the variable that keeps the error description.

Any help please

Thanx in advance

I really need to know if there is some variable that gives me the sql error because when I execute the cmd, if there's an error

the error is printed int the cmd window.

The errorlevel var just give me the number "1" but I would like to get something like

"An error in database ocurred due to database already exists" or something like that.

Please I'll apreciate any ideias you may have
|||

Instead of overwriting the error log file, I would just append the error to the file instead.

Code Snippet

ECHO OFF

OSQL -S %sqlServer% -E -b -n -i test.sql > %1
IF ERRORLEVEL 1 GOTO error
ECHO "" > %1
EXIT

:error
@.ECHO An error occured in [%errorLevel%]
echo [%errorLevel%] >> %1

This way you either have a blank file, or you have a file with an error message and errorLevel code.

|||Thanx for the answer ShawnNWF, that will do the trick for sure

Get the default snapshot folder path

How can i get the unc path to the default snapshot folder using TSQL? I am
using Transactional replication on SQLServer 2005. I have used
sp_helppublication but that only tells me whether my publication uses the
default snapshot folder or not. My publication does use the default snapshot
folder so the alt_snapshot_folder is NULL as expected.
Thanks,
Ian.
use [distribution]
select value from ::fn_listextendedproperty('SnapshotFolder', 'user',
'dbo', 'table', 'UIProperties', null, null)
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dinnebier" <dinnebier@.community.nospam> wrote in message
news:B09AC18E-B2E5-4FE7-BAC3-E5A020840E40@.microsoft.com...
> How can i get the unc path to the default snapshot folder using TSQL? I am
> using Transactional replication on SQLServer 2005. I have used
> sp_helppublication but that only tells me whether my publication uses the
> default snapshot folder or not. My publication does use the default
> snapshot
> folder so the alt_snapshot_folder is NULL as expected.
> Thanks,
> Ian.
|||Thanks for your rapid response Hilary. That's great. I would have never
thought of using fn_listextendedproperty. However I have realised that that
would only work if the publisher is also the distributor. Having done some
more research on this I have found the following TQSL produces the result I
require:
DECLARE @.result nvarchar(255)
exec sp_helpdistributor @.directory = @.result OUTPUT
PRINT @.result
This works regardless of whether I run this at the publisher or the
subscriber.
In fact both solutions are fine for my requirements.
Thanks again,
Ian.
"Hilary Cotter" wrote:

> use [distribution]
> select value from ::fn_listextendedproperty('SnapshotFolder', 'user',
> 'dbo', 'table', 'UIProperties', null, null)
>
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "dinnebier" <dinnebier@.community.nospam> wrote in message
> news:B09AC18E-B2E5-4FE7-BAC3-E5A020840E40@.microsoft.com...
>
>
|||Hello,
Thank you for posting here.
I am glad to hear that the resolution for your requirements has been found.
Thank you for investing time in this issue and for sharing the solution.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Get the default snapshot folder path
| thread-index: AcetnLeE/hNy56AyQvapXKihY41Auw==
| X-WBNR-Posting-Host: 207.46.193.207
| From: =?Utf-8?B?ZGlubmViaWVy?= <dinnebier@.community.nospam>
| Subject: Get the default snapshot folder path
| Date: Wed, 13 Jun 2007 02:25:00 -0700
| Lines: 8
| Message-ID: <B09AC18E-B2E5-4FE7-BAC3-E5A020840E40@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
| Newsgroups: microsoft.public.sqlserver.replication
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.replication:3509
| NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| How can i get the unc path to the default snapshot folder using TSQL? I
am
| using Transactional replication on SQLServer 2005. I have used
| sp_helppublication but that only tells me whether my publication uses the
| default snapshot folder or not. My publication does use the default
snapshot
| folder so the alt_snapshot_folder is NULL as expected.
|
| Thanks,
| Ian.
|

Get servers current date format?

sql server 2k...
I found the Set DateFormat method but I can't seem to find a get method
where it will return to me what sqlserver is set as...(mdy,dmy etc..) does
such a method/property exist?
thanks
Doug
Doug Swanson
Senior Applications Developer
Synchrono, Inc
651.228.1772
dswanson@.synchrono.comYou can use DBCC USEROPTIONS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Doug Swanson" <dcswanson@._remove_this_surfbest.net> wrote in message
news:ujHMya0FEHA.1272@.TK2MSFTNGP12.phx.gbl...
> sql server 2k...
> I found the Set DateFormat method but I can't seem to find a get method
> where it will return to me what sqlserver is set as...(mdy,dmy etc..) doe
s
> such a method/property exist?
> thanks
> Doug
> --
> Doug Swanson
> Senior Applications Developer
> Synchrono, Inc
> 651.228.1772
> dswanson@.synchrono.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/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)

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
--