Create Statistics Question

I have a 2005 database with Auto Create Statistics set to true and Auto
Update Statistics set to true. Am I correct that it should not be necessary
for me to manually execute any 'Create Statistics' statements to create
statistics to help in query plan generation? My assumption is that with
these two statements set to true, SQL 2005 will create whatever statistics
it needs, and I don't need to create any.
Thanks, Amos.There may be some exceptions but in general that is true.
--
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:ucZp4neJGHA.740@.TK2MSFTNGP12.phx.gbl...
>I have a 2005 database with Auto Create Statistics set to true and Auto
>Update Statistics set to true. Am I correct that it should not be necessary
>for me to manually execute any 'Create Statistics' statements to create
>statistics to help in query plan generation? My assumption is that with
>these two statements set to true, SQL 2005 will create whatever statistics
>it needs, and I don't need to create any.
> Thanks, Amos.
>

Create or update with updategram

Without knowing whether a row already exists in a table, is it possible to
construct an updategram that would create the row it does not exist or
update it if it does?No.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Xerox" <info@.thinkscape.com> wrote in message
news:O1aI5TWIFHA.580@.TK2MSFTNGP15.phx.gbl...
> Without knowing whether a row already exists in a table, is it possible to
> construct an updategram that would create the row it does not exist or
> update it if it does?
>

Create or update with updategram

Without knowing whether a row already exists in a table, is it possible to
construct an updategram that would create the row it does not exist or
update it if it does?
No.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Xerox" <info@.thinkscape.com> wrote in message
news:O1aI5TWIFHA.580@.TK2MSFTNGP15.phx.gbl...
> Without knowing whether a row already exists in a table, is it possible to
> construct an updategram that would create the row it does not exist or
> update it if it does?
>

Create One Trigger For Both Update and Delete

hi,
CAn i have one trigger for both Update and Delete
Delete Trigger
-------
create Trigger [tr_delete_user_log]
on [dbo].[user_log] for delete
as
begin
insert into z_user_log select * from deleted
end

Trigger Update
-------
CREATE Trigger [tr_update_user_log]
on [dbo].[user_log] for update
as
begin
insert into z_user_log select * from deleted
end

Can i have one trigger instead of these Triggers ..On 30 Apr 2007 06:02:57 -0700, satish wrote:

Quote:

Originally Posted by

>hi,
>CAn i have one trigger for both Update and Delete

(snip)

Quote:

Originally Posted by

>Can i have one trigger instead of these Triggers ..

Hi satish,

Yes.

CREATE Trigger [tr_update_delete_user_log]
on [dbo].[user_log] for update, delete
as
begin
insert into z_user_log select * from deleted
end

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||satish (satishkumar.gourabathina@.gmail.com) writes:

Quote:

Originally Posted by

CAn i have one trigger for both Update and Delete
Delete Trigger
-------
create Trigger [tr_delete_user_log]
on [dbo].[user_log] for delete
as
begin
insert into z_user_log select * from deleted
end

As Hugo said, you can. Permit me to point that your example exhibits
two cases of bad practice:

o INSERT without a values list. If someone adds a column to user_log,
the INSERT statement will fail.
o SELECT *. While convenient for ad hoc queries, it's bad in production
code. In this example - if someone adds or removes a column - or
just changes the column order, the INSERT statement will fail. SELECT *
also make it more difficult to find where different columns are
actually used.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 30 Apr 2007 21:31:28 +0000 (UTC), Erland Sommarskog wrote:

Quote:

Originally Posted by

Permit me to point that your example exhibits
>two cases of bad practice:

(snip)

Hi Erland,

Thanks for stepping in. I new feel so bad for not mentioning that
myself. Please remind me not to reply when tired in the future :-)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Create one stored procedure for INSERT/UPDATE/SELECT

Hi!
Instead of creating one stored procedure for insert and another one for
update, i did one with both.
Should I create one stored procedure for each? Should i create one stored
procedure for Insert/Update/Select ?
ALTER PROCEDURE dbo.[Inserir Atualizar Conta Bancaria]
(
@.ContaBancariaID As Int,
@.AgenciaID As VarChar(50),
@.BancoID As Int,
@.Numero As Int
)
AS
DECLARE @.Error as int
DECLARE @.Rowcount as int
IF @.ContaBancariaID IS NULL BEGIN
INSERT INTO ContaBancaria (AgenciaID, BancoID, Numero) Values (@.AgenciaID,
@.BancoID, @.Numero)
SELECT @.Error = @.@.ERROR, @.Rowcount = @.@.ROWCOUNT
If @.ERROR <> 0 OR @.ROWCOUNT = 0
GOTO ERROR
END
ELSE BEGIN
UPDATE ContaBancaria SET AgenciaID = @.AgenciaID, BancoID = @.BancoID,
Numero = @.Numero WHERE ContaBancariaID = @.ContaBancariaID
SELECT @.Error = @.@.ERROR, @.Rowcount = @.@.ROWCOUNT
If @.ERROR <> 0 OR @.ROWCOUNT = 0
GOTO ERROR
END
RETURN 0
ERROR:
RAISERROR ('Erro ao realizar a operao', 16,1)
RETURN @.Error
Thanks you all
Bruno N> Should I create one stored procedure for each?
Personally, I prefer two separate stored procedures. The answer depends on
what your criteria for "should" are...
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||One for Insert / Update and another for Select?
Or
One for Insert and another for Update?
Thans,
Bruno N
"Bruno N" <nylren@.hotmail.com> escreveu na mensagem
news:eYd4kN6OFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hi!
> Instead of creating one stored procedure for insert and another one for
> update, i did one with both.
> Should I create one stored procedure for each? Should i create one stored
> procedure for Insert/Update/Select ?
> ALTER PROCEDURE dbo.[Inserir Atualizar Conta Bancaria]
> (
> @.ContaBancariaID As Int,
> @.AgenciaID As VarChar(50),
> @.BancoID As Int,
> @.Numero As Int
> )
> AS
> DECLARE @.Error as int
> DECLARE @.Rowcount as int
>
> IF @.ContaBancariaID IS NULL BEGIN
> INSERT INTO ContaBancaria (AgenciaID, BancoID, Numero) Values
> (@.AgenciaID, @.BancoID, @.Numero)
> SELECT @.Error = @.@.ERROR, @.Rowcount = @.@.ROWCOUNT
> If @.ERROR <> 0 OR @.ROWCOUNT = 0
> GOTO ERROR
> END
> ELSE BEGIN
> UPDATE ContaBancaria SET AgenciaID = @.AgenciaID, BancoID = @.BancoID,
> Numero = @.Numero WHERE ContaBancariaID = @.ContaBancariaID
> SELECT @.Error = @.@.ERROR, @.Rowcount = @.@.ROWCOUNT
> If @.ERROR <> 0 OR @.ROWCOUNT = 0
> GOTO ERROR
> END
> RETURN 0
> ERROR:
> RAISERROR ('Erro ao realizar a operao', 16,1)
> RETURN @.Error
> --
> Thanks you all
> Bruno N
>|||You should definitely be separating SELECT procedures from others, in my
opinion. As for whether to separate INSERT from UPDATE, the answer still
depends on your decision criteria.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Bruno N" <nylren@.hotmail.com> wrote in message
news:uuZFqe6OFHA.164@.TK2MSFTNGP12.phx.gbl...
> One for Insert / Update and another for Select?
> Or
> One for Insert and another for Update?
> Thans,
> Bruno N
>
> "Bruno N" <nylren@.hotmail.com> escreveu na mensagem
> news:eYd4kN6OFHA.1392@.TK2MSFTNGP10.phx.gbl...
stored
>|||To add to what the others have said:
I have separate stored procedures to Insert, Update or Delete contacts.
I have one stored procedure to Insert, Update or Delete attendance.
Why? Because you usually don't work on the same form to Insert a contact and
modify another at the same time.
But you will work on one form to Insert, Update and Delete attendance.
...at least in the company that hired me.
"Bruno N" <nylren@.hotmail.com> wrote in message
news:uuZFqe6OFHA.164@.TK2MSFTNGP12.phx.gbl...
> One for Insert / Update and another for Select?
> Or
> One for Insert and another for Update?
> Thans,
> Bruno N
>
> "Bruno N" <nylren@.hotmail.com> escreveu na mensagem
> news:eYd4kN6OFHA.1392@.TK2MSFTNGP10.phx.gbl...
>