Friday, March 30, 2012
Replacing column value on insert
I'm running a database which contains on table, where the customer is
allowed to import data into.
This table also has a column, which the customer should set to his name, so
that you know who imported the record.
The problem is, that the customer is not restricted in setting this columns
value, so he also could set it to 'foo'!
Now I want to ensure, that the value is always set to the logged on user,
when importing data.
I know, that using a view and an INSTEAD OF trigger would do the job.
But as the table has abount 250 columns, I try to find an easier way.
Is it possible to write a trigger, which executes just before the insert,
and is able to replace a columns value?
Thanks in advance!
MaxHi there,
why not setting up a UPDATE / INSERT trigger then modifying the users
name, like this one below which takes the SUSER_SNAME as a input, make
sure you modify that, to eventually reflect your enviroment:
CREATE TRIGGER SomeTrigger ON SOmeTable
FOR INSERT
AS
BEGIN
UPDATE SomeTable SET ModifiedColumn = SUSER_SNAME
FROM SomeTable S
INNER JOIN INSERTED I
ON S.PKColumn = I.PKColumn
END
HTH, Jens Suessmeyer,
http://www.sqlserver2005.de
--|||Markus,
You can use the system function SYSTEM_USER to get the current username.
You can create an AFTER Trigger for inserts and update the inserted rows
Username with the SYSTEM_USER function.
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Markus Emayr" <essmayr/at/racon-linz.at> wrote in message
news:OqyIvYMTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> Hello SQLServer-specialists!
> I'm running a database which contains on table, where the customer is
> allowed to import data into.
> This table also has a column, which the customer should set to his name,
> so that you know who imported the record.
> The problem is, that the customer is not restricted in setting this
> columns value, so he also could set it to 'foo'!
> Now I want to ensure, that the value is always set to the logged on user,
> when importing data.
> I know, that using a view and an INSTEAD OF trigger would do the job.
> But as the table has abount 250 columns, I try to find an easier way.
> Is it possible to write a trigger, which executes just before the insert,
> and is able to replace a columns value?
> Thanks in advance!
> Max
>|||Hello!
Thanks for your answers!
I now tried the following
CREATE TRIGGER ModificationTrigger ON TheTableToInsertTo
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO TheTableToInsertTo
SELECT col1, col2, suser_sname()
FROM inserted
END
First I thought, that inserting into the same table, as the trigger should
instead of an insert, could make problems, but it works perfectly!!!
Greetings,
Max
"Markus Emayr" <essmayr/at/racon-linz.at> schrieb im Newsbeitrag
news:OqyIvYMTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> Hello SQLServer-specialists!
> I'm running a database which contains on table, where the customer is
> allowed to import data into.
> This table also has a column, which the customer should set to his name,
> so that you know who imported the record.
> The problem is, that the customer is not restricted in setting this
> columns value, so he also could set it to 'foo'!
> Now I want to ensure, that the value is always set to the logged on user,
> when importing data.
> I know, that using a view and an INSTEAD OF trigger would do the job.
> But as the table has abount 250 columns, I try to find an easier way.
> Is it possible to write a trigger, which executes just before the insert,
> and is able to replace a columns value?
> Thanks in advance!
> Max
>
Wednesday, March 21, 2012
Replace column value with MAX() query in dataflow before insert
Hi,
We have a dataflow task that imports data from excel to a sql2005 database table. One of the columns is never filled in in the excel source. For updates we can use the lookup transformation to fill in that column, but for new values we need to calculate a new value for it (it's a PK) with MAX(column) +1 and replace the null value in the dataflow with this new value.
Just to be clear:
column id (int)
column name (string)
Based on the 'name' column, we can look up existing ids, and update these in the table. Ids that don't exist yet need to be filled in with the maximum value of the column + 1 (we can't use identity columns) and inserted in the table
Which transformation do I use to replace the value of the id column with this new id?
thanks,
Stephane
Hi Stephane,
You would first need to get the maximum value of the id column into another column (called maxid, for the purposes of this example). You could do this with a script component, or by splitting the flow and using an Aggregate transform,and merging it back. Then, you can then use a derived column transform to replace the value of the column by setting a replace expression such as the following:
ISNULL(id) ? maxid + 1 : id
Mark
|||Hi Mark,
Thanks for your reply.
Your solution would work if all the data was in the dataflow, which unfortunately it isn't for me (we iterate over excel files with one row per file). We now do a select max() ... at the start of the control flow and store it in a variable. When we detect a new row, we increment the value and use a derived column transform like you suggested as well to put the value in the column.
Thanks,
Stephane
sqlTuesday, March 20, 2012
Replace
update tbl set ...
else
insert tbl ...
update tbl set ...
select @.error = @.@.error, @.rowcount = @.@.rowcount
if @.@.error = 0 and @.rowcount = 0
insert tbl ...
I include the @.@.error in the second method to show that if you check one then you will lose the other so have to save them both in one statement.
Monday, March 12, 2012
repeating an insert statement
passed from a user interface)...any hints on how to code the sql'
Thanks!!!Put it in a stored proc with a WHILE Loop.
CREATE PROC YourProc
@.Loop INT
AS
WHILE @.Loop > 0
BEGIN
INSERT INTO Table VALUES (x)
SET @.Loop = @.Loop - 1
END
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>I want a insert statement to repeat X number of times (X will be a variable
> passed from a user interface)...any hints on how to code the sql'
> Thanks!!!|||And I might add that if X happens to be a large number, there may be a
need--for better performance--to control the number of INSERTs you want to
commit in a single transaction. By default, each INSERT commits as a single
transaction, which may not be most efficient if you are doing many
single-INSERT commits in a row.
Linchi
"Gerry M" wrote:
> I want a insert statement to repeat X number of times (X will be a variable
> passed from a user interface)...any hints on how to code the sql'
> Thanks!!!|||"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>I want a insert statement to repeat X number of times (X will be a variable
> passed from a user interface)...any hints on how to code the sql'
> Thanks!!!
What data do you want to insert? Perhaps you want all the integers from 1 to
X, in which case you can use a numbers table to help you:
INSERT INTO SomeTable (z)
SELECT num
FROM numbers
WHERE num BETWEEN 1 AND @.x -- your parameter ;
--
David Portas|||If it's SQL2005, you can do without a numbers auxiliary table. To insert the
same integer X times:
with tmp as (
select 1 as a, 1 as b
union all
select a, b+1 from tmp where b < 100 -- or @.x
)
insert junk(a)
select a from tmp;
To insert integers from 1 to @.x:
with tmp as (
select 1 as a
union all
select a + 1 from tmp where a < 100 -- or @.x
)
insert junk(a)
select a from tmp;
Linchi
"David Portas" wrote:
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
> >I want a insert statement to repeat X number of times (X will be a variable
> > passed from a user interface)...any hints on how to code the sql'
> >
> > Thanks!!!
> What data do you want to insert? Perhaps you want all the integers from 1 to
> X, in which case you can use a numbers table to help you:
>
> INSERT INTO SomeTable (z)
> SELECT num
> FROM numbers
> WHERE num BETWEEN 1 AND @.x -- your parameter ;
> --
> David Portas
>
>|||Hi Gerry!
You could try
INSERT INTO MyTable ( MyValue ) VALUES ( 1 )
GO 10;
This will loop through all statements in the batch 10 times. That's assuming
that you want identical copies of the same row to be inserted of course. And
it doesn't work with variables unfortunately, as they get re-declared and
re-assigned in every loop.
Regards,
Jan
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>I want a insert statement to repeat X number of times (X will be a variable
> passed from a user interface)...any hints on how to code the sql'
> Thanks!!!|||> INSERT INTO MyTable ( MyValue ) VALUES ( 1 )
> GO 10;
The "GO n" method will work with SQL Server tools like SSMS or SQLCMD but
not from application code. GO is a batch terminator recognized only by the
SQL Server tools and is not actually sent to the server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Van der Eecken" <jkerner@.mweb.co.za> wrote in message
news:OBAl5ZaGIHA.4956@.TK2MSFTNGP06.phx.gbl...
> Hi Gerry!
> You could try
> INSERT INTO MyTable ( MyValue ) VALUES ( 1 )
> GO 10;
> This will loop through all statements in the batch 10 times. That's
> assuming that you want identical copies of the same row to be inserted of
> course. And it doesn't work with variables unfortunately, as they get
> re-declared and re-assigned in every loop.
> Regards,
> Jan
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>>I want a insert statement to repeat X number of times (X will be a
>>variable
>> passed from a user interface)...any hints on how to code the sql'
>> Thanks!!!
>|||Andrew, how do I pass the variable to the stored proc? Do I use a select
statement to select it?...I want it all to fit into a single job...this is
what I have so far...but it only inserts a single record...
(In this example freeintfield_01 has a value of 100)
Select freeintfield_01
from absences
where type=206 and status=1 and freedatefield_05 is null
create proc sngen
@.freeintfield_01 int
as
while @.freeintfield_01>0
begin
insert into sngenerator (itemcode)
select itemcode
from absences
where type=206 and status=1 and freedatefield_05 is null and
freeintfield_01=@.freeintfield_01
set @.freeintfield_01=@.freeintfield_01-1
end
"Andrew J. Kelly" wrote:
> Put it in a stored proc with a WHILE Loop.
> CREATE PROC YourProc
> @.Loop INT
> AS
> WHILE @.Loop > 0
> BEGIN
> INSERT INTO Table VALUES (x)
> SET @.Loop = @.Loop - 1
> END
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
> >I want a insert statement to repeat X number of times (X will be a variable
> > passed from a user interface)...any hints on how to code the sql'
> >
> > Thanks!!!
>|||Are you talking about a SQL Agent job? If so then like this:
DECLARE @.freeintfield_01 int
SET @.freeintfield_01 = (SELECT freeintfield_01
from absences
where type=206 and status=1 and freedatefield_05 is null )
EXEC dbo.sngen @.freeintfield_01
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:819E8C07-8667-4865-BF86-D0AF005D3FC6@.microsoft.com...
> Andrew, how do I pass the variable to the stored proc? Do I use a select
> statement to select it?...I want it all to fit into a single job...this
> is
> what I have so far...but it only inserts a single record...
> (In this example freeintfield_01 has a value of 100)
> Select freeintfield_01
> from absences
> where type=206 and status=1 and freedatefield_05 is null
> create proc sngen
> @.freeintfield_01 int
> as
> while @.freeintfield_01>0
> begin
> insert into sngenerator (itemcode)
> select itemcode
> from absences
> where type=206 and status=1 and freedatefield_05 is null and
> freeintfield_01=@.freeintfield_01
> set @.freeintfield_01=@.freeintfield_01-1
> end
>
> "Andrew J. Kelly" wrote:
>> Put it in a stored proc with a WHILE Loop.
>> CREATE PROC YourProc
>> @.Loop INT
>> AS
>> WHILE @.Loop > 0
>> BEGIN
>> INSERT INTO Table VALUES (x)
>> SET @.Loop = @.Loop - 1
>> END
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>> >I want a insert statement to repeat X number of times (X will be a
>> >variable
>> > passed from a user interface)...any hints on how to code the sql'
>> >
>> > Thanks!!!
>>|||Yes, the loop works fine but it does not insert the data (itemcode)100 times,
just once....?
"Andrew J. Kelly" wrote:
> Are you talking about a SQL Agent job? If so then like this:
> DECLARE @.freeintfield_01 int
> SET @.freeintfield_01 = (SELECT freeintfield_01
> from absences
> where type=206 and status=1 and freedatefield_05 is null )
> EXEC dbo.sngen @.freeintfield_01
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:819E8C07-8667-4865-BF86-D0AF005D3FC6@.microsoft.com...
> > Andrew, how do I pass the variable to the stored proc? Do I use a select
> > statement to select it?...I want it all to fit into a single job...this
> > is
> > what I have so far...but it only inserts a single record...
> > (In this example freeintfield_01 has a value of 100)
> >
> > Select freeintfield_01
> > from absences
> > where type=206 and status=1 and freedatefield_05 is null
> >
> > create proc sngen
> > @.freeintfield_01 int
> > as
> > while @.freeintfield_01>0
> >
> > begin
> >
> > insert into sngenerator (itemcode)
> > select itemcode
> >
> > from absences
> > where type=206 and status=1 and freedatefield_05 is null and
> > freeintfield_01=@.freeintfield_01
> >
> > set @.freeintfield_01=@.freeintfield_01-1
> > end
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Put it in a stored proc with a WHILE Loop.
> >>
> >> CREATE PROC YourProc
> >> @.Loop INT
> >>
> >> AS
> >>
> >> WHILE @.Loop > 0
> >> BEGIN
> >>
> >> INSERT INTO Table VALUES (x)
> >>
> >> SET @.Loop = @.Loop - 1
> >> END
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
> >> >I want a insert statement to repeat X number of times (X will be a
> >> >variable
> >> > passed from a user interface)...any hints on how to code the sql'
> >> >
> >> > Thanks!!!
> >>
> >>
>|||If the loop is executing 100 times and the data is not being inserted you
must have an issue with the select statement.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:F8F7A55F-0E1B-4351-B157-AAB90FA04F59@.microsoft.com...
> Yes, the loop works fine but it does not insert the data (itemcode)100
> times,
> just once....?
> "Andrew J. Kelly" wrote:
>> Are you talking about a SQL Agent job? If so then like this:
>> DECLARE @.freeintfield_01 int
>> SET @.freeintfield_01 = (SELECT freeintfield_01
>> from absences
>> where type=206 and status=1 and freedatefield_05 is null )
>> EXEC dbo.sngen @.freeintfield_01
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:819E8C07-8667-4865-BF86-D0AF005D3FC6@.microsoft.com...
>> > Andrew, how do I pass the variable to the stored proc? Do I use a
>> > select
>> > statement to select it?...I want it all to fit into a single
>> > job...this
>> > is
>> > what I have so far...but it only inserts a single record...
>> > (In this example freeintfield_01 has a value of 100)
>> >
>> > Select freeintfield_01
>> > from absences
>> > where type=206 and status=1 and freedatefield_05 is null
>> >
>> > create proc sngen
>> > @.freeintfield_01 int
>> > as
>> > while @.freeintfield_01>0
>> >
>> > begin
>> >
>> > insert into sngenerator (itemcode)
>> > select itemcode
>> >
>> > from absences
>> > where type=206 and status=1 and freedatefield_05 is null and
>> > freeintfield_01=@.freeintfield_01
>> >
>> > set @.freeintfield_01=@.freeintfield_01-1
>> > end
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Put it in a stored proc with a WHILE Loop.
>> >>
>> >> CREATE PROC YourProc
>> >> @.Loop INT
>> >>
>> >> AS
>> >>
>> >> WHILE @.Loop > 0
>> >> BEGIN
>> >>
>> >> INSERT INTO Table VALUES (x)
>> >>
>> >> SET @.Loop = @.Loop - 1
>> >> END
>> >>
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >>
>> >>
>> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> >> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>> >> >I want a insert statement to repeat X number of times (X will be a
>> >> >variable
>> >> > passed from a user interface)...any hints on how to code the sql'
>> >> >
>> >> > Thanks!!!
>> >>
>> >>
>>|||Yes, I found it...thanks for your help!
Gerry
"Andrew J. Kelly" wrote:
> If the loop is executing 100 times and the data is not being inserted you
> must have an issue with the select statement.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:F8F7A55F-0E1B-4351-B157-AAB90FA04F59@.microsoft.com...
> > Yes, the loop works fine but it does not insert the data (itemcode)100
> > times,
> > just once....?
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Are you talking about a SQL Agent job? If so then like this:
> >>
> >> DECLARE @.freeintfield_01 int
> >>
> >> SET @.freeintfield_01 = (SELECT freeintfield_01
> >> from absences
> >> where type=206 and status=1 and freedatefield_05 is null )
> >>
> >> EXEC dbo.sngen @.freeintfield_01
> >>
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> news:819E8C07-8667-4865-BF86-D0AF005D3FC6@.microsoft.com...
> >> > Andrew, how do I pass the variable to the stored proc? Do I use a
> >> > select
> >> > statement to select it?...I want it all to fit into a single
> >> > job...this
> >> > is
> >> > what I have so far...but it only inserts a single record...
> >> > (In this example freeintfield_01 has a value of 100)
> >> >
> >> > Select freeintfield_01
> >> > from absences
> >> > where type=206 and status=1 and freedatefield_05 is null
> >> >
> >> > create proc sngen
> >> > @.freeintfield_01 int
> >> > as
> >> > while @.freeintfield_01>0
> >> >
> >> > begin
> >> >
> >> > insert into sngenerator (itemcode)
> >> > select itemcode
> >> >
> >> > from absences
> >> > where type=206 and status=1 and freedatefield_05 is null and
> >> > freeintfield_01=@.freeintfield_01
> >> >
> >> > set @.freeintfield_01=@.freeintfield_01-1
> >> > end
> >> >
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> Put it in a stored proc with a WHILE Loop.
> >> >>
> >> >> CREATE PROC YourProc
> >> >> @.Loop INT
> >> >>
> >> >> AS
> >> >>
> >> >> WHILE @.Loop > 0
> >> >> BEGIN
> >> >>
> >> >> INSERT INTO Table VALUES (x)
> >> >>
> >> >> SET @.Loop = @.Loop - 1
> >> >> END
> >> >>
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >> Solid Quality Mentors
> >> >>
> >> >>
> >> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> >> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
> >> >> >I want a insert statement to repeat X number of times (X will be a
> >> >> >variable
> >> >> > passed from a user interface)...any hints on how to code the sql'
> >> >> >
> >> >> > Thanks!!!
> >> >>
> >> >>
> >>
> >>
>
Saturday, February 25, 2012
reorg PK clustered index in VLDB
600 GB od data.
We had only data insert no update in this duration. PKs
are all clustered indexes and in chronological order.
Some of tables are as large as 100 GB.
I wanted to check if the PKs are in good figure and ran
DBCC showcontig against many of large tables and 80 % had
bad rate for scan density , such as 50 %, 30 %.
I may need to reorganise PK.
I BOL it says comparing the values of Extent Switches and
Extents Scanned is a way to know how much fragmented. But
it says this method does not work if the index spans
multiple files. I presume all VLDB exploit multiple files
for one table in order to gain physical disk I/O.
My question: how can I check fragmentation rate of my
large tables which span multiple files (up to 4 to 6
files)?
What is the best way to reorganise clustered index which
are PK ? I have to drop all FK in order to reorganise PK,
don't I !
I hope to hear your idea!!!When you say you presume your db spans multiple files, does the database use
more than one file other than the MDF? DBCC DBreindex on the clustered key
should reindex your data tables and automatically reindex your other
nonclustered indexes.
Some links:
http://www.microsoft.com/technet/co...ql/sql0326.mspx
http://www.microsoft.com/technet/co...ql/sql1014.mspx
http://www.sqlservercentral.com/scr...butions/721.asp
Ray Higdon MCSE, MCDBA, CCNA
--
"didi" <anonymous@.discussions.microsoft.com> wrote in message
news:140c01c40b37$dd2c95d0$3501280a@.phx.gbl...
> My data warehouse is now 4 years old and the size is about
> 600 GB od data.
> We had only data insert no update in this duration. PKs
> are all clustered indexes and in chronological order.
> Some of tables are as large as 100 GB.
> I wanted to check if the PKs are in good figure and ran
> DBCC showcontig against many of large tables and 80 % had
> bad rate for scan density , such as 50 %, 30 %.
> I may need to reorganise PK.
> I BOL it says comparing the values of Extent Switches and
> Extents Scanned is a way to know how much fragmented. But
> it says this method does not work if the index spans
> multiple files. I presume all VLDB exploit multiple files
> for one table in order to gain physical disk I/O.
> My question: how can I check fragmentation rate of my
> large tables which span multiple files (up to 4 to 6
> files)?
> What is the best way to reorganise clustered index which
> are PK ? I have to drop all FK in order to reorganise PK,
> don't I !
> I hope to hear your idea!!!|||MDF file is used only for system table in all of my
databases. (especially when dealing with VLDB).
The database is over 600GB, and each table could be nearly
100GB,
Would DBreindex a good solution ?
This will copy the whole table into different location
without asking !
>--Original Message--
>When you say you presume your db spans multiple files,
does the database use
>more than one file other than the MDF? DBCC DBreindex on
the clustered key
>should reindex your data tables and automatically reindex
your other
>nonclustered indexes.
>Some links:
>http://www.microsoft.com/technet/co...chats/trans/sql
/sql0326.mspx
>http://www.microsoft.com/technet/co...chats/trans/sql
/sql1014.mspx
>http://www.sqlservercentral.com/scr...tributions/721.
asp
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"didi" <anonymous@.discussions.microsoft.com> wrote in
message
>news:140c01c40b37$dd2c95d0$3501280a@.phx.gbl...
about
had
and
But
files
PK,
>
>.
>|||Did those links help?
Ray Higdon MCSE, MCDBA, CCNA
--
"didi" <anonymous@.discussions.microsoft.com> wrote in message
news:148601c40b4b$a0c711b0$3a01280a@.phx.gbl...
> MDF file is used only for system table in all of my
> databases. (especially when dealing with VLDB).
> The database is over 600GB, and each table could be nearly
> 100GB,
> Would DBreindex a good solution ?
> This will copy the whole table into different location
> without asking !
>
> does the database use
> the clustered key
> your other
> /sql0326.mspx
> /sql1014.mspx
> asp
> message
> about
> had
> and
> But
> files
> PK,|||Links were very good! Thank you very much!
Especially Index Defrag Best Practices.
So, according to the article I should use fragmentation
level by logical scan fragmentation.
Still I am not very sure about using DBCC INDEXDEFRAG.
Because when a table is 100GB, and do this operation, how
large the log should be allocated ? 200 GB, 300 GB ?
Usually for copying data it takes about 2.5 times of data
size consumed in log before the data is inserted into.
Would DBCC INDEXDEFRAG be a best way in VLDB environment ?
>--Original Message--
>Did those links help?
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"didi" <anonymous@.discussions.microsoft.com> wrote in
message
>news:148601c40b4b$a0c711b0$3a01280a@.phx.gbl...
nearly
on
reindex
>http://www.microsoft.com/technet/co...chats/trans/sql
>http://www.microsoft.com/technet/co...chats/trans/sql
>http://www.sqlservercentral.com/scr...tributions/721.
PKs
ran
which
>
>.
>|||Depends on the needed uptime of your DB, you can write scripts to defrag in
chunks. Here is an example of using dbreindex (you can alter to use index
defrag) and backing up the log when needed, think I got this from MVP Andrew
Kelly but not 100% sure:
-- Reindexing the tables --
SET NOCOUNT ON
DECLARE @.TableName VARCHAR(100), @.Counter INT
SET @.Counter = 1
DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
OPEN curTables
FETCH NEXT FROM curTables INTO @.TableName
SET @.TableName = RTRIM(@.TableName)
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT 'Reindexing ' + @.TableName
DBCC DBREINDEX (@.TableName)
SET @.Counter = @.Counter + 1
-- Backup the Log every so often so as not to fill the log
IF @.Counter % 10 = 0
BEGIN
BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT , NOUNLOAD
,
NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
NOFORMAT
END
FETCH NEXT FROM curTables INTO @.TableName
END
CLOSE curTables
DEALLOCATE curTables
Ray Higdon MCSE, MCDBA, CCNA
--
"didi" <anonymous@.discussions.microsoft.com> wrote in message
news:159401c40c21$2fc43b60$3a01280a@.phx.gbl...
> Links were very good! Thank you very much!
> Especially Index Defrag Best Practices.
> So, according to the article I should use fragmentation
> level by logical scan fragmentation.
> Still I am not very sure about using DBCC INDEXDEFRAG.
> Because when a table is 100GB, and do this operation, how
> large the log should be allocated ? 200 GB, 300 GB ?
> Usually for copying data it takes about 2.5 times of data
> size consumed in log before the data is inserted into.
> Would DBCC INDEXDEFRAG be a best way in VLDB environment ?
>
> message
> nearly
> on
> reindex
> PKs
> ran
> which