SQL Timestamp field for Access use

On Tue, 7 Apr 2009 00:53:42 -0400, "Sylvain Lafontaine"
wrote:

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

"Brendan Reynolds" wrote in
:


"David W. Fenton" wrote in message
36.89...

Have I made a mistake with the clustering on Lastname/Firstname?

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Tom Wickerath" aos168b at comcast dot net wrote in
:

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

David W. Fenton wrote:
"Brendan Reynolds" wrote in
:


"David W. Fenton" wrote in message
.136.89...


Have I made a mistake with the clustering on Lastname/Firstname?

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?


See:

Using Clustered Indexes

http://msdn.microsoft.com/en-us/libr...1(SQL.80).aspx

It might not answer the question, but it seems like useful information :-).

James A. Fortune

James A. Fortune wrote:
David W. Fenton wrote:

"Brendan Reynolds" wrote in
:

"David W. Fenton" wrote in message
36.89...



Have I made a mistake with the clustering on Lastname/Firstname?


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?


See:

Using Clustered Indexes

http://msdn.microsoft.com/en-us/libr...1(SQL.80).aspx

It might not answer the question, but it seems like useful information :-).

James A. Fortune



BTW, If you think the link is somewhat dated, poke around the navigation
tree for a similar page on an analogous path for SQL Server 2005 and SQL
Server 2008.

James A. Fortune


MOLLYDOOKER - A left-handed person. Whence, "mollydook" (adj.) -
left-handed. -- Dictionary of Australian Slang, Second Edition, Sidney
J. Baker, 1943 (Price: Three shillings & sixpence)

James A. Fortune wrote:

BTW, If you think the link is somewhat dated, poke around the navigation
tree for a similar page on an analogous path for SQL Server 2005 and SQL
Server 2008.

Hmm... I suppose that wasn't helpful enough.

For SQL Server 2008 see:

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

For SQL Server 2005 see:

http://msdn.microsoft.com/en-us/libr...9(SQL.90).aspx

James A. Fortune


PLONK - Cheap Aust. wine, often laced with methylated spirits. --
Dictionary of Australian Slang, Second Edition, Sidney J. Baker, 1943
(Price: Three shillings & sixpence)

"David W. Fenton" wrote in message
36.98...
"Brendan Reynolds" wrote in
:

"David W. Fenton" wrote in message
36.89...

Have I made a mistake with the clustering on Lastname/Firstname?

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?

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.

--
Brendan Reynolds

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.

"Brendan Reynolds" wrote in message
...
"David W. Fenton" wrote in message
36.98...
"Brendan Reynolds" wrote in
:

"David W. Fenton" wrote in message
36.89...

Have I made a mistake with the clustering on Lastname/Firstname?

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?

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.

--
Brendan Reynolds

"Brendan Reynolds" wrote in
:

"David W. Fenton" wrote in message
36.98...
"Brendan Reynolds" wrote in
:

"David W. Fenton" wrote in
message
36.89...

Have I made a mistake with the clustering on
Lastname/Firstname?

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?

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Paul Shapiro" wrote in
:

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?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/