"Open table" doesn't show all records (sql server 2005).

Using sql managment studio in Sql Server 2005, I right-click on a (large)
table and select "open table," and then press the "move last" (go to last
record) button. After a minute or two, it claims all records (455,903 of
455,903) have been accessed (what it shows on the status bar, also the
"pause" button becomes greyed out), but in fact it has stopped at record
446,739, and browsing doesn't show the missing last records.
Running a query in Management Studio on that same table shows that all the
records really are there. Opening the same table from Access as a linked
table also shows all the records are there (Access only takes a few seconds
to execute a move to last record command on the table and go to record
455,903).
Is this a bug? It's hard to see how it's by design, at least the false
indication that I'm at the last (455,903th) record seems to be a bug.
Thanks.Okay, I see that what "open table" has done is actually put the last set of
records somewhere in the middle of the table, in an order different from
SELECT * (and access). Setting a clustered index solved that problem (but
not the speed issue...).|||On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Using sql managment studio in Sql Server 2005, I right-click on a (large)
> table and select "open table," and then press the "move last" (go to last
> record) button. After a minute or two, it claims all records (455,903 of
> 455,903) have been accessed (what it shows on the status bar, also the
> "pause" button becomes greyed out), but in fact it has stopped at record
> 446,739, and browsing doesn't show the missing last records.
>
What exactly do you mean by saying "it has stopped at record 446,739"?
There is no concept of a first or last record because tables are
unordered sets by definition. There is no such thing as a record
number.
Returning 400K rows for display in a single query is a rather absurd
thing to do. It's certainly the case that the Open Table feature
leaves a lot to be desired in many respects. On the other hand it
isn't so surprising that it struggles with such an unreasonable
request. Why would you really want to do that?
I suggest you write queries to extract the data that's of interest to
you. Otherwise you might as well be using a spreadsheet instead of a
database.
--
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 4 Mar, 10:33, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Okay, I see that what "open table" has done is actually put the last set of
> records somewhere in the middle of the table, in an order different from
> SELECT * (and access). Setting a clustered index solved that problem (but
> not the speed issue...).
No. A table is unordered by definition. This has nothing to do with
clustered indexes and unless you specify ORDER BY in a query the
resulting order is undefined - i.e. the ordering could even be
different each time you run the query. You simply cannot use a SQL
database as if it were a spreadsheet.
--
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
--|||Thanks. I meant the indexed primary key field, which is actually a record
count field in my table. However, as I said in the revised post, the
records were there after all (the upsizing wizard had not created a
clustered index as I expected).
Of course, I follow the standard practice of using queries to limit the size
of result sets. But it's by no means absurd to occasionally need to browse
an entire table, even a large one--as you can guess from the numerous
complaints when MS briefly eliminated "open table" from studio during the
beta. At any rate, given how fast the linked table feature to that same sql
server 2005 table works from Access, I would think some speed improvement
can be made here.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173004572.639515.95050@.64g2000cwx.googlegroups.com...
> On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
>> Using sql managment studio in Sql Server 2005, I right-click on a (large)
>> table and select "open table," and then press the "move last" (go to last
>> record) button. After a minute or two, it claims all records (455,903 of
>> 455,903) have been accessed (what it shows on the status bar, also the
>> "pause" button becomes greyed out), but in fact it has stopped at record
>> 446,739, and browsing doesn't show the missing last records.
> What exactly do you mean by saying "it has stopped at record 446,739"?
> There is no concept of a first or last record because tables are
> unordered sets by definition. There is no such thing as a record
> number.
> Returning 400K rows for display in a single query is a rather absurd
> thing to do. It's certainly the case that the Open Table feature
> leaves a lot to be desired in many respects. On the other hand it
> isn't so surprising that it struggles with such an unreasonable
> request. Why would you really want to do that?
> I suggest you write queries to extract the data that's of interest to
> you. Otherwise you might as well be using a spreadsheet instead of a
> database.
> --
> 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
> --
>|||Of course it's undefined by definition and one can't rely on the order
without an order by etc. However, some dbs do present the clustered order
on a simple table open (or equivalent) operation, and I checked for a
difference only after the post. I may have but didn't mean to imply that a
clustered index would lead to rows being in any given order on retrieval
(absent an order by), only that it in this case it did in fact cause a
difference between the select all and the open table operation.|||On 4 Mar, 10:50, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Thanks. I meant the indexed primary key field, which is actually a record
> count field in my table. However, as I said in the revised post, the
> records were there after all (the upsizing wizard had not created a
> clustered index as I expected).
> Of course, I follow the standard practice of using queries to limit the size
> of result sets. But it's by no means absurd to occasionally need to browse
> an entire table, even a large one--as you can guess from the numerous
> complaints when MS briefly eliminated "open table" from studio during the
> beta. At any rate, given how fast the linked table feature to that same sql
> server 2005 table works from Access, I would think some speed improvement
> can be made here.
>
The key point is that "Open Table" is mainly a shortcut for "SELECT *
FROM tbl", which is not a smart thing to do in many environments.
Now you might have expected that MS would provide a more sophisticated
page-and-requery style of interface. Unfortunately, designing a single
generic UI to do that for every possible case isn't really feasible.
There are too many potential variables around indexing, requery,
locking and sorting strategies prefererred for different situations.
Even if it were achievable, some people who liked the "traditional"
open table feature would complain that a requery interface was not
what they wanted. So instead the lobby who favoured an Access legacy
UI got the one-size-fits-all solution they asked for. Unfortunately
that really means one-size-fits... very little.
For most practical purposes the solution for browsing 1000 rows will
likely be different to the solution required for browsing 1 billion
rows. That's why the only real answer is to build it yourself. The
compasison to Access ADP is irrelevant. It would be insane even to
attempt that in an enterprise-scale environment with thousands of
concurrent users.
HTH.
--
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
--|||Yes, one might well have expected an interface much better, and not much
worse, than a good but cheaper product. What you said makes a lot of sense,
yet I'm not persuaded it's necessarily decisive. First, a minor point, this
is not being compared with an access adp, but an access mdb linked to sql
server via the native client. It may or not be insane in an enterprise
environment with thousands of concurrent users for only *one* user, the
developer, to browse the entire table with a linked mdb. However, it
doesn't seem insane to allow it *as an option* ("open table via ODBC" or
"open table in workgroup mode"?), nor impossible to implement, since already
used in the Access linked table scheme (you could also have an option to
"open table after table lock" or "try to acquire table lock on open
table"--there are several different possibilities here). Instead of "build
it myself," I prefer to have MS build it, and I'll "check the option box
myself." And why does it have to be one size fits all, when practically
everything else in sql server changes as environment and parameters change.
It would be an extremely useful feature for those of us who need it. Not
everyone is running sql server on the high end; increasingly people are
migrating in from the low end, and this is a very useful feature that I know
works well with sql server data in the small workgroup environment, but it's
only available as an option in Access. Of course, I can always open Access
and link to the same data, to avoid the minutes of delay, but then I need to
have Access installed on the machine and I need to set up a database there
and link.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173008179.376755.126190@.s48g2000cws.googlegroups.com...
> The key point is that "Open Table" is mainly a shortcut for "SELECT *
> FROM tbl", which is not a smart thing to do in many environments.
> Now you might have expected that MS would provide a more sophisticated
> page-and-requery style of interface. Unfortunately, designing a single
> generic UI to do that for every possible case isn't really feasible.
> There are too many potential variables around indexing, requery,
> locking and sorting strategies prefererred for different situations.
> Even if it were achievable, some people who liked the "traditional"
> open table feature would complain that a requery interface was not
> what they wanted. So instead the lobby who favoured an Access legacy
> UI got the one-size-fits-all solution they asked for. Unfortunately
> that really means one-size-fits... very little.
> For most practical purposes the solution for browsing 1000 rows will
> likely be different to the solution required for browsing 1 billion
> rows. That's why the only real answer is to build it yourself. The
> compasison to Access ADP is irrelevant. It would be insane even to
> attempt that in an enterprise-scale environment with thousands of
> concurrent users.
> HTH.
> --
> David Portas, SQL Server MVP
>