SQL Timestamp field for Access use

[Armen Stein]

Nope, as far as I can remember, Access will still pull out the timestamp
value and use for controling the updating even if it's not part of the
Select statement.

That's correct. The timestamp/rowversion field merely needs to exist
in the table. It does not have to be referenced by Access at all.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[David W. Fenton]

You could create a clustered, unique index including the three
fields, Lastname, Firstname, and PK, and see if it makes a
difference?

Why would that make a difference?

 

[David W. Fenton]

A long time ago I remember asking at a PNWADG (Pacific NW Access
Developer's Group) meeting if we needed to include the timestamp
field in queries in order for Access to make use of it. I was told
"no" by a member of the Access Development Team, who was present
at the meeting.

That's interesting.

And quite annoying.

 

[Brendan Reynolds]

Why would that make a difference?

I thought that was the question - would having a clustered unique index make
a difference? Did I misunderstand?

What I'm suggesting (if that was the question) is that adding the PK field
will allow you to make your existing clustered index on the lastname and
firstname fields unique, while still sorting by lastname and firstname. You
can then test to see whether your queries run any faster with the clustered
unique index than without it.

 

[Paul Shapiro]

All clustered indexes are unique. They have to be, since the row data is
stored in the index's leaf node. If a clustered index is not unique by
itself, SQL Server automatically adds a "uniquifier". The potential
advantage to including the PK in the lastname, firstname clustered index
would be for a query like a lookup list (Select id, lastname +', ' +
firstname ...), where all fields could now come from the index entry without
needing to read the leaf node data. If the PK is an id, there's not even an
index size penalty since SQL Server would be adding a unique integer anyway.

The clustered index should be a big improvement if the table is large
enough, and if you are retrieving a subset of the data in that order. So a
query like the lookup list above gets a modest benefit because the sorting
no longer needs to be done after retrieval. A search query on last name
(Select... Where lastname like 'Smi%') gets a big improvement because SQL
Server does not have to lookup the row data once it finds the index values
that qualify for the condition- the data is right there in the index leaf
node, substantially reducing the number of pages SQL Server needs to read.

 

[David W. Fenton]

I thought that was the question - would having a clustered unique
index make a difference? Did I misunderstand?

I already *have* a clustered index. You proposed adding a field to
it, and my question is:

Why would adding a field to the clustered index make a difference?
Particularly, why would adding a column with unique values make a
difference, since it would change the results of the clustering in
terms of ordering of storage?

What I'm suggesting (if that was the question) is that adding the
PK field will allow you to make your existing clustered index on
the lastname and firstname fields unique, while still sorting by
lastname and firstname.

But why do I need them to be unique? I already have a unique index,
i.e., the PK.

You
can then test to see whether your queries run any faster with the
clustered unique index than without it.

Ah. You're suggesting a test. I can't really do that, as I don't
have a testbed for this, only the live app.

 

[David W. Fenton]

All clustered indexes are unique. They have to be, since the row
data is stored in the index's leaf node. If a clustered index is
not unique by itself, SQL Server automatically adds a
"uniquifier". The potential advantage to including the PK in the
lastname, firstname clustered index would be for a query like a
lookup list (Select id, lastname +', ' + firstname ...), where all
fields could now come from the index entry without needing to read
the leaf node data. If the PK is an id, there's not even an index
size penalty since SQL Server would be adding a unique integer
anyway.

The real-world scenario under contemplation here is a an existing
clustered index on 2 fields that together are not unique vs. a
3-column clustered index that is unique. There is no scenario where
my app would ever retrieve just those 3 columns, as there isn't
enough information there to be useful -- consider that if one were
crazy enough to populate a combo box from a 350K-record table, with
just those three fields, you'd be unable to distinguish the
duplicate names.

The clustered index should be a big improvement if the table is
large enough, and if you are retrieving a subset of the data in
that order.

But only if the subset of data is just the three fields in the
clustered index, right?

So a
query like the lookup list above gets a modest benefit because the
sorting no longer needs to be done after retrieval. A search query
on last name (Select... Where lastname like 'Smi%') gets a big
improvement because SQL Server does not have to lookup the row
data once it finds the index values that qualify for the
condition- the data is right there in the index leaf node,
substantially reducing the number of pages SQL Server needs to
read.

Given that I always ask for more fields in all my SELECT statements,
all of which have a WHERE clause (either on one or both of the name
fields or on the PK alone), is there going to be any benefit to
retrieving those 3 fields from the clustered index when there are
other fields that have to be retrieved from the actual data pages?

If not, in regard to sorting, is there going to be a benefit to
adding the PK field? That is, is eliminating the overhead of the
"uniquifier" going to speed up the sort?

 

[Brendan Reynolds]

I already *have* a clustered index. You proposed adding a field to
it, and my question is:

Why would adding a field to the clustered index make a difference?
Particularly, why would adding a column with unique values make a
difference, since it would change the results of the clustering in
terms of ordering of storage?


But why do I need them to be unique? I already have a unique index,
i.e., the PK.


Ah. You're suggesting a test. I can't really do that, as I don't
have a testbed for this, only the live app.

You wrote ...

<quote>
Is this a problem? There is no clustered unique index on the table
at all, so based on the quote above, am I slowing things down?
</quote>

So I suggested adding the PK field to make the clustered index a clustered,
unique index. However, Paul has now explained something of which I was not
aware, that SQL Server automatically makes the clustered index unique by
adding a "uniquifier". So it would appear that the question is not
clustered index versus clustered unique index, but clustered, implicitly
unique index versus clustered, explicitly unique index. Would there be a
performance difference between the two, and if so, would it be significant?
Short of testing, I don't know the answer. Perhaps someone else does. I
would be surprised if there was any significant difference, but then it
wouldn't be the first time I've been surprised.

 

[David W. Fenton]

So it would appear that the question is not
clustered index versus clustered unique index, but clustered,
implicitly unique index versus clustered, explicitly unique index.
Would there be a performance difference between the two, and if
so, would it be significant? Short of testing, I don't know the
answer. Perhaps someone else does. I would be surprised if there
was any significant difference, but then it wouldn't be the first
time I've been surprised.

The article that started this discussion (which I highly recommend
to anyone using SQL Server with Access):

http://msdn.microsoft.com/en-us/library/bb188204.aspx

....discusses the implicitly-created unique index. I read about it
there, and assumed I didn't need to worry about it. I might see if I
can test it over the weekend to compare whether or not it speeds
things up or not to add the PK to the end of the clustered index.