Couple of questions for the experienced people out there

Hi, I have some unrelated questions borne from my studying of sql server
internals. They are probably stupid but I am new to sql server.
1. On an insert, select, update, or delete of table rows, if the rows are
not already in the buffer cache
does sql server take the effected rows and put them there? For instance,
if I start up the server and
then immediately issue a delete statement, do the deleted rows go into the
buffer cache? I understand
that the transaction will get written to the transaction log first. I'm
trying to understand when and
exactly what ends up in the buffer cache. A good layman's explanation
would make me a happy man.
2. I have 2 tables with a many to many relationship. I know a join table
is used to accommodate this
scenario. What is the best practice for indexing?
ie
create table student (studentid int... blah, blah)
create table course(courseid int... blah, blah)
create table studentcourse (studentid, courseid)
in the studentcourse table, would the best practice be to add primary key
studentcourseid and indexes
on the other 2 columns? Or no primary key and just the 2 indexes on the 2
columns?
3. Is it a good idea to make an index on a foreign key? If so, why?
Example
create table parent (parentid int primary key....)
create table child (childid int primary key, parentid int) <-- parentid is
defined as a foreign key to parentHope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BKWdnTLoyrthiHDZnZ2dnUVZ_qCdnZ2d@.co
mcast.com...
> Hi, I have some unrelated questions borne from my studying of sql server
> internals. They are probably stupid but I am new to sql server.
> 1. On an insert, select, update, or delete of table rows, if the rows
> are
> not already in the buffer cache
> does sql server take the effected rows and put them there? For instance,
> if I start up the server and
> then immediately issue a delete statement, do the deleted rows go into the
> buffer cache? I understand
> that the transaction will get written to the transaction log first. I'm
> trying to understand when and
> exactly what ends up in the buffer cache. A good layman's explanation
> would make me a happy man.
All data access is done in buffer cache. In your delete example, the
affected pages are first be read into memory, where the delete occurs. The
pages remain in memory until the cache is reused. It might be some time
before the pages are written to disk so pages might get changed many times
before being written. However, as you mentioned, a record of the changes is
written to the log.

> 2. I have 2 tables with a many to many relationship. I know a join
> table
> is used to accommodate this
> scenario. What is the best practice for indexing?
> ie
> create table student (studentid int... blah, blah)
> create table course(courseid int... blah, blah)
> create table studentcourse (studentid, courseid)
> in the studentcourse table, would the best practice be to add primary key
> studentcourseid and indexes
> on the other 2 columns? Or no primary key and just the 2 indexes on the 2
> columns?
All tables in a relational database should generally have a primary key.
The primary key of studentcourse is a composite key of studentid and
courseid. It is likely that joins will occur from both the student table
(select a student's courses) and the course table (select a course's
students) so you will probably want a composite index on courseid and
studentid too.

> 3. Is it a good idea to make an index on a foreign key? If so, why?
>
An index on a foreign key column is especially handy when rows are deleted
from the parent. Without one, SQL Server will need to scan the child table
to ensure the relationship isn't violated Of course, the index can also be
useful for queries involving the foreign key column.

> Example
> create table parent (parentid int primary key....)
> create table child (childid int primary key, parentid int) <-- parentid
> is
> defined as a foreign key to parent
>
>
>