difference between FOR INSERT and AFTER INSERT triggers

I've been reading the docs and playing around, but I'm still not
getting the difference. For instance,

create table a(i int check(i>0))
create table a_src(i int)
go
create unique index ai on a(i) with IGNORE_DUP_KEY
go
insert into a_src values(1)
insert into a_src values(1)
insert into a_src values(2)
--insert into a_src values(-1)
go
create trigger a4ins on a
for insert
as
select * from inserted
go
create trigger afterins on a
after insert
as
select * from inserted
go
insert into a select * from a_src
go
drop table a
drop table a_src

I'm getting

i
----
1
2

(2 row(s) affected)

Server: Msg 3604, Level 16, State 1, Procedure a4ins, Line 4
Duplicate key was ignored.
i
----
1
2

(2 row(s) affected)

even the inserted quasi tables are identical.
If I uncomment insert into a_src values(-1), I'm getting

Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint
'CK__a__i__58FC18A6'. The conflict occurred in database 'ABC_1COMPEE',
table 'a', column 'i'.
The statement has been terminated.

without any output from either trigger.
So,
in which situations will FOR INSERT be useful while AFTER INSERT won't
do?
in which situations will AFTER INSERT be useful while FOR INSERT won't
do?Ford Desperado (ford_desperado@.yahoo.com) writes:
> I've been reading the docs and playing around, but I'm still not
> getting the difference.

That is because there isn't any!

If memory servers, FOR was the original syntax. I suspect that AFTER
has been added to align with ANSI standards. FOR is not very precise,
where as AFTER tells us that the trigger fires after the statement.
That in difference to BEFORE and INSTEAD OF triggers. (Of which SQL
Server has the latter, but not the former.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanks Erlandsql

Diff betw Clustered and Non-Clustered Index and their Application

Hi experts,
I have being reading up on index, in particular the difference between a
cluster index and non-cluster index, and how they should be used. However th
e
more I read the more I am.
Basically if anyone can advise me on this question:
- Which kind of index should I use for PRIMARY KEY column, a clustered or
non-clustered index?
- Similarly which kind of index should I use for FOREIGN KEY column, a
clustered or non-clustered index?
A background of how I become .
My understanding of clustered index is, basically rows with column value
that fit an index value are grouped together. In another words, 1 index
value, multiple rows (1-to-many relationship).
Furthermore these rows are sorted within the group itself.
Therefore when SQL Server queries the DB along a column with clustered
index, it will be able to quickly find and return all the rows.
As for non-clustered index, it is basically an index that points directly to
that row. So, 1 index value, 1 row (1-to-1 relationship "ideally").
However on one article, it recommends that primary keys should be clustered
(which is what SQL Server does by default). But on another article, it
recommends that pimary keys should be non-clustered! And that is when I
become .
Since primary keys are unique, so rows are not grouped under a clustered
index at all. SQL Server cannot find more than one row under that index. So
there is no performance value gain.
However non-clustered index is more suited to set on the primary key column.
Afterall one-index value, one primary key.
So I think it makes more sense to set non-cluster index for primary key,
while reserving the clustered index for foreign keys, especially the child
table's foreign key (back to the parent row). The power of the clustered
index should not be wasted on primary key column.
Is my understanding correct?
Could u please kindly advise. TQ very much in advance.Hi
This is very,very big and important issue.
Start with
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...ing_indexes.asp
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:BABFB6D4-09F7-482B-89F9-DF10DFCF1242@.microsoft.com...
> Hi experts,
> I have being reading up on index, in particular the difference between a
> cluster index and non-cluster index, and how they should be used. However
the
> more I read the more I am.
> Basically if anyone can advise me on this question:
> - Which kind of index should I use for PRIMARY KEY column, a clustered or
> non-clustered index?
> - Similarly which kind of index should I use for FOREIGN KEY column, a
> clustered or non-clustered index?
> A background of how I become .
> My understanding of clustered index is, basically rows with column value
> that fit an index value are grouped together. In another words, 1 index
> value, multiple rows (1-to-many relationship).
> Furthermore these rows are sorted within the group itself.
> Therefore when SQL Server queries the DB along a column with clustered
> index, it will be able to quickly find and return all the rows.
> As for non-clustered index, it is basically an index that points directly
to
> that row. So, 1 index value, 1 row (1-to-1 relationship "ideally").
> However on one article, it recommends that primary keys should be
clustered
> (which is what SQL Server does by default). But on another article, it
> recommends that pimary keys should be non-clustered! And that is when I
> become .
> Since primary keys are unique, so rows are not grouped under a clustered
> index at all. SQL Server cannot find more than one row under that index.
So
> there is no performance value gain.
> However non-clustered index is more suited to set on the primary key
column.
> Afterall one-index value, one primary key.
> So I think it makes more sense to set non-cluster index for primary key,
> while reserving the clustered index for foreign keys, especially the child
> table's foreign key (back to the parent row). The power of the clustered
> index should not be wasted on primary key column.
> Is my understanding correct?
> Could u please kindly advise. TQ very much in advance.

Developing the clever backup strategy

Hi there...
I've been reading around in the BOL to figure out what backup strategy is th
e
"best" available. The database I want to backup, is ~4.5 after a shrink, but
it
of course grows after a while. Same with the log.
The database runs in "full recovery" mode currently, and it is being used du
ring
a normal workday, by around 15 people. What backup setup is the recommended
in
this situation?
I've tried setting it up to backup the database every 4 hours, and the log e
very
30 minutes. This generates a /lot/ of *.trn files, is there anyway to
"consolidate" those after a day? Secondly, when I do a database backup every
24
hours, does that mean that I can then afterward safely truncate the log (I l
ike
keeping things neat ? What do I do with the *.bak and *.trn when they are
over
two days old?
I doubt, therefore I might be.I'd suggest a good, thorough read through of books on line to make sure you
understand things like truncating the logs, full recovery more, simple
recovery mode, etc.
Truncating the logs should probably never be done which is why I suggest thi
s.
burt_king@.yahoo.com
"Kim Noer" wrote:

> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is
the
> "best" available. The database I want to backup, is ~4.5 after a shrink, b
ut it
> of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used
during
> a normal workday, by around 15 people. What backup setup is the recommende
d in
> this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log
every
> 30 minutes. This generates a /lot/ of *.trn files, is there anyway to
> "consolidate" those after a day? Secondly, when I do a database backup eve
ry 24
> hours, does that mean that I can then afterward safely truncate the log (I
like
> keeping things neat ? What do I do with the *.bak and *.trn when they ar
e over
> two days old?
> --
> I doubt, therefore I might be.
>|||I definitely agree. Also, be restrictive using shrink, for more info, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"burt_king" <burt_king@.yahoo.com> wrote in message
news:193B7308-3D54-426E-AF7A-B9983D509983@.microsoft.com...[vbcol=seagreen]
> I'd suggest a good, thorough read through of books on line to make sure yo
u
> understand things like truncating the logs, full recovery more, simple
> recovery mode, etc.
> Truncating the logs should probably never be done which is why I suggest t
his.
>
> --
> burt_king@.yahoo.com
>
> "Kim Noer" wrote:
>|||Usually in a smaller environment like you describe I reccomend a daily full
backup and then differential backups throughout the day. With this scenario
you can put the database in simple recovery mode and not have to worry about
log file growth. I have seen too many environments where they left the
databases in Full Recovery (default) and never back up the logs. A couple
weeks go by and the database is refusing transactions because the disk is
full.
I truncate and shrink the logs every night to make sure it gets done. One
big Insert or something else can throw your log files out of whack. With
this you should make sure your minimum log file is big enough that it does
not have to grow signifigantly during a normal work day.
When you say your DB is 4.5G (I assume G) after the shrink, how big is it
before the shrink? If your database files are shrinking signifigantly
(>20%) every night you need to look at your clustered indexes and fill
factors to make sure there is enough room that the database is not having to
auto grow throughout the day. Also, if you have not done reindexed in a
while, you should look at doing that - see DBCC Reindex (note that this
should be done when the database is not being accessed).
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%231ouK%2327FHA.2816@.tk2msftngp13.phx.gbl...
> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is
> the "best" available. The database I want to backup, is ~4.5 after a
> shrink, but it of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used
> during a normal workday, by around 15 people. What backup setup is the
> recommended in this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log
> every 30 minutes. This generates a /lot/ of *.trn files, is there anyway
> to "consolidate" those after a day? Secondly, when I do a database backup
> every 24 hours, does that mean that I can then afterward safely truncate
> the log (I like keeping things neat ? What do I do with the *.bak and
> *.trn when they are over two days old?
> --
> I doubt, therefore I might be.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:uHbwqP37FHA.1140@.tk2msftngp13.phx.gbl
> I definitely agree. Also, be restrictive using shrink, for more info,
> see http://www.karaszi.com/SQLServer/info_dont_shrink.asp
I've read your excellent guide, and I think I actually more now than before
.
Anyway. Currently the database runs in "full recovery" mode. Then I did a
complete backup of the database, and then I tried a dbcc loginfo('database')
Now, according to your guide, status 2 means the VLF is in use. The result i
s
this -
2 253952 8192 65770 0 64 0
2 253952 262144 65769 0 64 0
2 253952 516096 65768 0 128 0
2 278528 770048 65771 0 64 0
2 262144 1048576 65772 0 128 40875000000042200043
...
And this continues all the way to row 308 (your guide tells me I should set
the
allocated size way above the current) -
2 27000832 2188771328 65773 2 64 65760000004588300008
2 27000832 2215772160 65766 0 64 65760000004588300008
As you might be able to see, row 308 have the status 2. Doesn't this mean th
at
this logfile will never go below 2.06GB unless I truncate it manually? Secon
dly,
what about the VLF's below, since they are unused will this cause SQL Server
to
reuse the VLF, or will it proceed from CreateLSN 65760000004588300008?
I doubt, therefore I might be.|||"Kim Noer" <kn@.nospam.dk> wrote in message
news:OZD%23gFE8FHA.1140@.tk2msftngp13.phx.gbl

> Secondly, what about the VLF's below, since they are
> unused will this cause SQL Server to reuse the VLF, or will it
> proceed from CreateLSN 65760000004588300008?
Nevermind, I decided to use your guide as test, that is, create a table, the
n
insert a lot of records, which showed me that SQL Server reuses unused VLF,
even
when they are "before" used VLF's.
I doubt, therefore I might be.

Developing the clever backup strategy

Hi there...
I've been reading around in the BOL to figure out what backup strategy is the
"best" available. The database I want to backup, is ~4.5 after a shrink, but it
of course grows after a while. Same with the log.
The database runs in "full recovery" mode currently, and it is being used during
a normal workday, by around 15 people. What backup setup is the recommended in
this situation?
I've tried setting it up to backup the database every 4 hours, and the log every
30 minutes. This generates a /lot/ of *.trn files, is there anyway to
"consolidate" those after a day? Secondly, when I do a database backup every 24
hours, does that mean that I can then afterward safely truncate the log (I like
keeping things neat ? What do I do with the *.bak and *.trn when they are over
two days old?
I doubt, therefore I might be.
I'd suggest a good, thorough read through of books on line to make sure you
understand things like truncating the logs, full recovery more, simple
recovery mode, etc.
Truncating the logs should probably never be done which is why I suggest this.
burt_king@.yahoo.com
"Kim Noer" wrote:

> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is the
> "best" available. The database I want to backup, is ~4.5 after a shrink, but it
> of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used during
> a normal workday, by around 15 people. What backup setup is the recommended in
> this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log every
> 30 minutes. This generates a /lot/ of *.trn files, is there anyway to
> "consolidate" those after a day? Secondly, when I do a database backup every 24
> hours, does that mean that I can then afterward safely truncate the log (I like
> keeping things neat ? What do I do with the *.bak and *.trn when they are over
> two days old?
> --
> I doubt, therefore I might be.
>
|||I definitely agree. Also, be restrictive using shrink, for more info, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"burt_king" <burt_king@.yahoo.com> wrote in message
news:193B7308-3D54-426E-AF7A-B9983D509983@.microsoft.com...[vbcol=seagreen]
> I'd suggest a good, thorough read through of books on line to make sure you
> understand things like truncating the logs, full recovery more, simple
> recovery mode, etc.
> Truncating the logs should probably never be done which is why I suggest this.
>
> --
> burt_king@.yahoo.com
>
> "Kim Noer" wrote:
|||Usually in a smaller environment like you describe I reccomend a daily full
backup and then differential backups throughout the day. With this scenario
you can put the database in simple recovery mode and not have to worry about
log file growth. I have seen too many environments where they left the
databases in Full Recovery (default) and never back up the logs. A couple
weeks go by and the database is refusing transactions because the disk is
full.
I truncate and shrink the logs every night to make sure it gets done. One
big Insert or something else can throw your log files out of whack. With
this you should make sure your minimum log file is big enough that it does
not have to grow signifigantly during a normal work day.
When you say your DB is 4.5G (I assume G) after the shrink, how big is it
before the shrink? If your database files are shrinking signifigantly
(>20%) every night you need to look at your clustered indexes and fill
factors to make sure there is enough room that the database is not having to
auto grow throughout the day. Also, if you have not done reindexed in a
while, you should look at doing that - see DBCC Reindex (note that this
should be done when the database is not being accessed).
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%231ouK%2327FHA.2816@.tk2msftngp13.phx.gbl...
> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is
> the "best" available. The database I want to backup, is ~4.5 after a
> shrink, but it of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used
> during a normal workday, by around 15 people. What backup setup is the
> recommended in this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log
> every 30 minutes. This generates a /lot/ of *.trn files, is there anyway
> to "consolidate" those after a day? Secondly, when I do a database backup
> every 24 hours, does that mean that I can then afterward safely truncate
> the log (I like keeping things neat ? What do I do with the *.bak and
> *.trn when they are over two days old?
> --
> I doubt, therefore I might be.
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:uHbwqP37FHA.1140@.tk2msftngp13.phx.gbl
> I definitely agree. Also, be restrictive using shrink, for more info,
> see http://www.karaszi.com/SQLServer/info_dont_shrink.asp
I've read your excellent guide, and I think I actually more now than before .
Anyway. Currently the database runs in "full recovery" mode. Then I did a
complete backup of the database, and then I tried a dbcc loginfo('database')
Now, according to your guide, status 2 means the VLF is in use. The result is
this -
2 253952 8192 65770 0 64 0
2 253952 262144 65769 0 64 0
2 253952 516096 65768 0 128 0
2 278528 770048 65771 0 64 0
2 262144 1048576 65772 0 128 40875000000042200043
...
And this continues all the way to row 308 (your guide tells me I should set the
allocated size way above the current) -
2 27000832 2188771328 65773 2 64 65760000004588300008
2 27000832 2215772160 65766 0 64 65760000004588300008
As you might be able to see, row 308 have the status 2. Doesn't this mean that
this logfile will never go below 2.06GB unless I truncate it manually? Secondly,
what about the VLF's below, since they are unused will this cause SQL Server to
reuse the VLF, or will it proceed from CreateLSN 65760000004588300008?
I doubt, therefore I might be.
|||"Kim Noer" <kn@.nospam.dk> wrote in message
news:OZD%23gFE8FHA.1140@.tk2msftngp13.phx.gbl

> Secondly, what about the VLF's below, since they are
> unused will this cause SQL Server to reuse the VLF, or will it
> proceed from CreateLSN 65760000004588300008?
Nevermind, I decided to use your guide as test, that is, create a table, then
insert a lot of records, which showed me that SQL Server reuses unused VLF, even
when they are "before" used VLF's.
I doubt, therefore I might be.

Developing the clever backup strategy

Hi there...
I've been reading around in the BOL to figure out what backup strategy is the
"best" available. The database I want to backup, is ~4.5 after a shrink, but it
of course grows after a while. Same with the log.
The database runs in "full recovery" mode currently, and it is being used during
a normal workday, by around 15 people. What backup setup is the recommended in
this situation?
I've tried setting it up to backup the database every 4 hours, and the log every
30 minutes. This generates a /lot/ of *.trn files, is there anyway to
"consolidate" those after a day? Secondly, when I do a database backup every 24
hours, does that mean that I can then afterward safely truncate the log (I like
keeping things neat :)? What do I do with the *.bak and *.trn when they are over
two days old?
--
I doubt, therefore I might be.I'd suggest a good, thorough read through of books on line to make sure you
understand things like truncating the logs, full recovery more, simple
recovery mode, etc.
Truncating the logs should probably never be done which is why I suggest this.
burt_king@.yahoo.com
"Kim Noer" wrote:
> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is the
> "best" available. The database I want to backup, is ~4.5 after a shrink, but it
> of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used during
> a normal workday, by around 15 people. What backup setup is the recommended in
> this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log every
> 30 minutes. This generates a /lot/ of *.trn files, is there anyway to
> "consolidate" those after a day? Secondly, when I do a database backup every 24
> hours, does that mean that I can then afterward safely truncate the log (I like
> keeping things neat :)? What do I do with the *.bak and *.trn when they are over
> two days old?
> --
> I doubt, therefore I might be.
>|||I definitely agree. Also, be restrictive using shrink, for more info, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"burt_king" <burt_king@.yahoo.com> wrote in message
news:193B7308-3D54-426E-AF7A-B9983D509983@.microsoft.com...
> I'd suggest a good, thorough read through of books on line to make sure you
> understand things like truncating the logs, full recovery more, simple
> recovery mode, etc.
> Truncating the logs should probably never be done which is why I suggest this.
>
> --
> burt_king@.yahoo.com
>
> "Kim Noer" wrote:
>> Hi there...
>> I've been reading around in the BOL to figure out what backup strategy is the
>> "best" available. The database I want to backup, is ~4.5 after a shrink, but it
>> of course grows after a while. Same with the log.
>> The database runs in "full recovery" mode currently, and it is being used during
>> a normal workday, by around 15 people. What backup setup is the recommended in
>> this situation?
>>
>> I've tried setting it up to backup the database every 4 hours, and the log every
>> 30 minutes. This generates a /lot/ of *.trn files, is there anyway to
>> "consolidate" those after a day? Secondly, when I do a database backup every 24
>> hours, does that mean that I can then afterward safely truncate the log (I like
>> keeping things neat :)? What do I do with the *.bak and *.trn when they are over
>> two days old?
>> --
>> I doubt, therefore I might be.
>>|||Usually in a smaller environment like you describe I reccomend a daily full
backup and then differential backups throughout the day. With this scenario
you can put the database in simple recovery mode and not have to worry about
log file growth. I have seen too many environments where they left the
databases in Full Recovery (default) and never back up the logs. A couple
weeks go by and the database is refusing transactions because the disk is
full.
I truncate and shrink the logs every night to make sure it gets done. One
big Insert or something else can throw your log files out of whack. With
this you should make sure your minimum log file is big enough that it does
not have to grow signifigantly during a normal work day.
When you say your DB is 4.5G (I assume G) after the shrink, how big is it
before the shrink? If your database files are shrinking signifigantly
(>20%) every night you need to look at your clustered indexes and fill
factors to make sure there is enough room that the database is not having to
auto grow throughout the day. Also, if you have not done reindexed in a
while, you should look at doing that - see DBCC Reindex (note that this
should be done when the database is not being accessed).
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%231ouK%2327FHA.2816@.tk2msftngp13.phx.gbl...
> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is
> the "best" available. The database I want to backup, is ~4.5 after a
> shrink, but it of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used
> during a normal workday, by around 15 people. What backup setup is the
> recommended in this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log
> every 30 minutes. This generates a /lot/ of *.trn files, is there anyway
> to "consolidate" those after a day? Secondly, when I do a database backup
> every 24 hours, does that mean that I can then afterward safely truncate
> the log (I like keeping things neat :)? What do I do with the *.bak and
> *.trn when they are over two days old?
> --
> I doubt, therefore I might be.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:uHbwqP37FHA.1140@.tk2msftngp13.phx.gbl
> I definitely agree. Also, be restrictive using shrink, for more info,
> see http://www.karaszi.com/SQLServer/info_dont_shrink.asp
I've read your excellent guide, and I think I actually more now than before :).
Anyway. Currently the database runs in "full recovery" mode. Then I did a
complete backup of the database, and then I tried a dbcc loginfo('database')
Now, according to your guide, status 2 means the VLF is in use. The result is
this -
2 253952 8192 65770 0 64 0
2 253952 262144 65769 0 64 0
2 253952 516096 65768 0 128 0
2 278528 770048 65771 0 64 0
2 262144 1048576 65772 0 128 40875000000042200043
...
And this continues all the way to row 308 (your guide tells me I should set the
allocated size way above the current) -
2 27000832 2188771328 65773 2 64 65760000004588300008
2 27000832 2215772160 65766 0 64 65760000004588300008
As you might be able to see, row 308 have the status 2. Doesn't this mean that
this logfile will never go below 2.06GB unless I truncate it manually? Secondly,
what about the VLF's below, since they are unused will this cause SQL Server to
reuse the VLF, or will it proceed from CreateLSN 65760000004588300008?
--
I doubt, therefore I might be.|||"Kim Noer" <kn@.nospam.dk> wrote in message
news:OZD%23gFE8FHA.1140@.tk2msftngp13.phx.gbl
> Secondly, what about the VLF's below, since they are
> unused will this cause SQL Server to reuse the VLF, or will it
> proceed from CreateLSN 65760000004588300008?
Nevermind, I decided to use your guide as test, that is, create a table, then
insert a lot of records, which showed me that SQL Server reuses unused VLF, even
when they are "before" used VLF's.
--
I doubt, therefore I might be.