Database Reference
In-Depth Information
Drawbacks
While there are advantages to the default result set, there are drawbacks as well. Using the default result set requires
some special conditions for maximum performance:
•
It doesn't support all properties and methods:
Properties such as
AbsolutePosition
,
Bookmark
,
and
RecordCount
, as well as methods such as
Clone
,
MoveLast
,
MovePrevious
, and
Resync
, are
not supported.
•
Locks may be held on the underlying resource:
SQL Server sends as many rows of the result set
to the client as the client-network buffers can cache. If the size of the result set is large, then
the client-network buffers may not be able to receive all the rows. SQL Server then holds a lock
on the next page of the underlying tables, which has not been sent to the client.
To demonstrate these concepts, consider the following test table:
USE AdventureWorks2012;
GO
IF (SELECT OBJECT_ID('dbo.Test1')
) IS NOT NULL
DROP TABLE dbo.Test1;
GO
CREATE TABLE dbo.Test1 (C1 INT, C2 CHAR(996));
CREATE CLUSTERED INDEX Test1Index ON dbo.Test1 (C1);
INSERT INTO dbo.Test1
VALUES (1, '1') ,
(2, '2');
GO
Now consider this PowerShell script, which accesses the rows of the test table using ADO with OLEDB and the
default cursor type for the database API cursor (
ADODB.Recordset
object) as follows:
$AdoConn = New-Object -comobject ADODB.Connection
$AdoRecordset = New-Object -comobject ADODB.Recordset
$AdoConn.Open("Provider= SQLOLEDB; Data Source=DOJO\RANDORI; Initial Catalog=AdventureWorks2012;
Integrated Security=SSPI")
$AdoRecordset.Open("SELECT * FROM dbo.Test1", $AdoConn)
do {
$C1 = $AdoRecordset.Fields.Item("C1").Value
$C2 = $AdoRecordset.Fields.Item("C2").Value
Write-Output "C1 = $C1 and C2 = $C2"
$AdoRecordset.MoveNext()
} until ($AdoRecordset.EOF -eq $True)
$AdoRecordset.Close()
$AdoConn.Close()
