Dropping tempe tables

My query creates a new temp table every time a report is run. I need to check
if the table exists before I drop it and perform a select into. Since SQL
Server appends an ID (session ID?) to the temp table name, I am unable to
drop the table prior to running the query. Is there a better way to handle
this? Any help would be appreciated.Try this. make sure the temp table does not exist, and you can do it this way.
Drop your new table at the end if your query.
CREATE PROCEDURE [dbo].[Test] AS
Select top 5 * into #temp from Orders
Select * from #temp
Drop Table #temp
GO
"DrM" wrote:
> My query creates a new temp table every time a report is run. I need to check
> if the table exists before I drop it and perform a select into. Since SQL
> Server appends an ID (session ID?) to the temp table name, I am unable to
> drop the table prior to running the query. Is there a better way to handle
> this? Any help would be appreciated.

Dropping article

Hi,
I have setup merge replication to run between two SQL 2k (SP3) across the internet. But while selecting the articles via EM I forgot to select "Identity Resolver" for a particular table. And this is creating havoc in that table.
I have to solve this by dropping the article. Or is there any other way?
But if I try to drop the article it says that there is an active subscription for this publication. Would this mean that I will have to drop my entire publication and create the entire thing again?
Can someone help me with this?
Thank you.
Regards,
Karthik
Karthik,
it is not mandatory to reinitialize - you can drop the subscription to a particular article using sp_dropsubscription then drop the article using sp_droparticle, then sp_addarticle to readd then sp_refreshsubscriptions.
Incidentally, what is the error message you see?
Regards,
Paul Ibison
|||Hi Paul,
Thank you for the quick response.
I see a primary key violation for a column that has IDENTITY property set and marked as "YES NOT FOR REPLICATION".
So according to you I do the following -
1. Run sp_dropsubscription at the publisher.
2. Run sp_droparticle
3. Run sp_addarticle
4. Run sp_refreshsubscriptions
I am not all that experienced in Replication. So I still have a few queries on what you have said. I would like to be really clear before I do something as this is the production database.
If I drop a subscription from the publisher, will it not mean that I would need to initialize via the snapshots again?
And what does sp_refreshsubscriptions do actually? I did look at BOL, but didn't understand too much.
Regards,
Karthik.
|||Karthik,
apologies - I just reread your post and noticed that you are using merge replication. In this case my route won't work - it only works for snapshot and transactional. What you can do though is to script out your publication (inc subscriptions),delete the
publication (and prevent users from editing data on the subscriber), make necessary changes to the table, then recreate the publication. For each of the other articles you set @.sync_type to 'none' to establish that the table already exists on the subscrib
er. For the table that you want the identity value, you'll need to have @.sync_type set to 'automatic'.
Alternatively you can have several publications to separate the articles if this is a common issue.
As another point, I'd mention that you can be sure to avoid these PK violations if you set the increment of the identity column in such a way as there can never be an overlap eg publisher uses even numbers and subscriber odd ones. There is an article by M
ichael Hotek on this (http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Paul,
I did generate a script of both the publisher and subscribers. But nowhere could I see a @.sync_type. I searched the entire text file.
May be I forgot to specify that I have a pull subscription.
Do let me know if my posting part of the script file would help.
Thank you once again.
Regards,
Karthik
|||Karthik,
it's a parameter off sp_addmergepullsubscription or sp_addmergesubscription.
However, unfortunately it is not valid on a per article basis - hadn't
appreciated this subtle difference between transactional and merge, so you'd
have to set @.sync_type set to 'automatic' for the complete subscription. In
your case you might be able to make the required table modification to the
table on the publisher and subscriber then run this. As it involves identity
column changes whose ranges are saved to system tables I suspect this is not
viable and would advise another route:
(a) drop the publication! Add 2 new publications - one with the old tables
apart from the problemmatic one, one with the problemmatic table. For the
first one have @.sync_type set to 'none' (subscriber has schema) and have
@.sync_type set to 'automatic' for the second.
(b) if this is not feasible (due to PK-FK constraints between articles),
then go down the initial route of a complete recreation of the publication.
Sorry it's not easier advice.
Regards,
Paul Ibison
|||Hi Paul,
What you seemed to have mentioned seems quite complicated. I would like to stall it as much as possible. Anyways, may be I am trying to go about this the wrong way as I didn't specify why I want to drop the article.
Is there any way I can specify idenity ranges for an article after it has been published? What I need to do basically is, specify that my table "FTPFile" has a range of 1-1000 from my publisher with a threshold of 80%.
Is there any way of doing this?
Thank you once again!
Regards,
Karthik.
|||Karthik,
the only sp wich might be relevant is:
sp_adjustpublisheridentityrange
From BOL: For a publication which has the auto identity range enabled, the
Distribution Agent or Merge Agent is responsible for automatically adjusting
the identity range in a publication based on its threshold value. However,
if for some reason the Distribution Agent or Merge Agent has not been run
for a period of time, and identity range resource have been consumed heavily
to the point of threshold, users can call sp_adjustpublisheridentityrange to
allocate a new range of values for a Publisher.
The identity ranges are held in a system table and the infrastructure is
also implemented using check constraints, but I would not advise you to
change these manually.
My recommendation would be to set it up again and ensure there can't be any
problems by following Michael Hotek's example: eg publisher uses even
numbers and subscriber odd ones. There is an article on this
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Paul,
Thank you for all the help.
I was able to fix the problem as suggested in the link.
Thank you once again.
Regards,
Karthik

Dropping a set of tables

Hi
I am using SQL server 7 database and ASP as front end. I run an application where a text file is loaded into database. After this is done procedures are run to create a set of tables that have snapshots of the data in the text file.. each time i load a new text file i want to create the snapshots.. i hve written a stored procedure to create tables and insert values into the tables.. however how do i delete the tables i created the previous time.. the number of snapshots and their names will depend on the size of the text file.. how do i refer to all the snapshots created and drop them all before creating new ones?
plese guide
regdsIn your stored procedure, you should drop the temporary tables you created when you do not need them. If I'm not wrong, SQL server 2000 will drop the temporary tebles for you. But I suggest that you should drop them in the code if you do not need them.

Dropdownlist with stored procedure problem

Hi Everyone,

I am trying to load the data into the dropdownlist using stored procedure. But when I run the code, the dropdownlist is empty. The code is shown below. Please help! Thanks.

public DataSet getProvince()
{
DataSet ds = new DataSet();
SqlParameter myParam;

string conString;
SqlConnection myConnection;

conString = ConfigurationManager.AppSettings["connectionString"];
myConnection = new SqlConnection(conString);

SqlCommand cmd = new SqlCommand("stored_procedure_GetProvinces", myConnection);

SqlDataAdapter adpt = new SqlDataAdapter(cmd);

try
{

cmd.CommandType = CommandType.StoredProcedure;
myParam = cmd.Parameters.Add("@.province_key", SqlDbType.Int);
myParam.Direction = ParameterDirection.Output;

myParam = cmd.Parameters.Add("@.province_name", SqlDbType.NVarChar, 200);
myParam.Direction = ParameterDirection.Output;

adpt.Fill(ds, "Provinces");

myConnection.Close();

}
catch (SqlException ex)
{
Response.Write("Error: " + ex.Message);
}
return ds;
}

-----------

create procedure stored_procedure_GetProvinces( @.province_key int output, @.province_name nvarchar (200) output)AsSelect @.province_key=province_key, @.province_name=province_name From provinceGO

--------

<asp:DropDownList id="Dropdownlist_Province" DataValueField="province_key" DataTextField="province_name" DataSource='<%# getProvince() %>' Runat="server" >
</asp:DropDownList>

----------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[province]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[province]
GO

CREATE TABLE province (
province_key int IDENTITY PRIMARY KEY,
province_name nvarchar (200) NULL
)
GO

INSERT INTO province(province_name) VALUES
('- Select -');
GO
INSERT INTO province (province_name) VALUES
('Ontario');
GO
INSERT INTO province (province_name) VALUES
('Alberta');
GO
INSERT INTO province (province_name) VALUES
('British Columbia');
GO
INSERT INTO province (province_name) VALUES
('Manitoba');
GO
INSERT INTO province(province_name) VALUES
('New Brunswick');
GO
INSERT INTO province (province_name) VALUES
('Newfoundland');
GO
INSERT INTO province (province_name) VALUES
('Northwest Territories');
GO
INSERT INTO province (province_name) VALUES
('Nova Scotia');
GO
INSERT INTO province (province_name) VALUES
('Nunavut');
GO
INSERT INTO province (province_name) VALUES
('Prince Edward Island');
GO
INSERT INTO province (province_name) VALUES
('Quebec');
GO
INSERT INTO province (province_name) VALUES
('Saskatchewan');
GO
INSERT INTO province (province_name) VALUES
('Yukon Territory');
GO

What is the value of your parameter?|||

what you need is:

Select province_key, province_nameFrom province

also, you need to modify your .NET code and remove the parameter part. The way you are doing, your proc will return just one set of values from the table, since there is no WHERE condition and your variables can only have one value at a time, SQL server will just pick a row at random and assign the values to the variables. Assuming you want to see ALL the values since you are binding it to a drop down, you just need the SELECT statement with the columns (without any variables). Also, look into using an ExecuteReader which is faster than filling a dataadapter and a dataset.

|||

bullpit:

What is the value of your parameter?

Sorry, I don't know. Thanks.

|||

ndinakar:

what you need is:

Select province_key, province_nameFrom province

also, you need to modify your .NET code and remove the parameter part. The way you are doing, your proc will return just one set of values from the table, since there is no WHERE condition and your variables can only have one value at a time, SQL server will just pick a row at random and assign the values to the variables. Assuming you want to see ALL the values since you are binding it to a drop down, you just need the SELECT statement with the columns (without any variables). Also, look into using an ExecuteReader which is faster than filling a dataadapter and a dataset.

After I modify my code and my stored procedure. I still get the empty dropdownlist. Please help. Thanks.

|||did you change the proc to the way I suggested? Also please post your new .NET code..|||

ndinakar:

did you change the proc to the way I suggested? Also please post your new .NET code..

My updated code is shown below. Thanks!

create procedure

stored_procedure_GetProvinces

(

@.province_key

intoutput,

@.province_name nvarchar (200)

output

)

As

Select

province_key, province_nameFromprovince

GO

--------

publicDataSet getProvince()

{

DataSet ds =newDataSet();SqlParameter myParam;string conString;SqlConnection myConnection;

conString =

ConfigurationManager.AppSettings["connectionString"];

myConnection =

newSqlConnection(conString);SqlCommand cmd =newSqlCommand("stored_procedure_GetProvinces", myConnection);SqlDataAdapter adpt =newSqlDataAdapter(cmd);try

{

cmd.CommandType =

CommandType.StoredProcedure;

adpt.Fill(ds,

"Provinces");

myConnection.Close();

}

catch (SqlException ex)

{

Response.Write(

"Error: " + ex.Message);

}

return ds;

}

--------- the end -------

|||

ndinakar:

what you need is:

Select province_key, province_nameFrom province

also, you need to modify your .NET code and remove the parameter part. The way you are doing, your proc will return just one set of values from the table, since there is no WHERE condition and your variables can only have one value at a time, SQL server will just pick a row at random and assign the values to the variables. Assuming you want to see ALL the values since you are binding it to a drop down, you just need the SELECT statement with the columns (without any variables). Also, look into using an ExecuteReader which is faster than filling a dataadapter and a dataset.

I have just tried using ExecuteReader. And I still get empty dropdownlist. Please help. Thanks.

SqlCommand objCmd;

SqlConnection objConn;

SqlDataReader dataReader;

String strSql;

objConn =newSqlConnection(ConfigurationManager.AppSettings.Get("ConnectionString"));

strSql ="EXECUTE stored_procedure_GetProvinces";

objCmd =newSqlCommand(strSql, objConn);

try

{

objConn.Open();

dataReader = objCmd.ExecuteReader(

CommandBehavior.CloseConnection);

Dropdownlist_Provinces.DataSource = dataReader;

Dropdownlist_Provinces.DataTextField =

"province_name";

Dropdownlist_Provinces.DataValueField =

"province_name";

Dropdownlist_Provinces.DataBind();

}

catch

{

}

finally

{

objConn.Close();

objConn.Dispose();

}

|||

Your stored proc would be like this:

create procedure dbo.stored_procedure_GetProvincesAsSelect province_key, province_nameFrom province GO

Then your .NET code could be someting like this: You might have to convert it to C#.

Protected objConAs New SqlConnection(ConfigurationSettings.AppSettings("conn"))pubic sub....Dim myCommandAs SqlCommandmyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="stored_procedure_GetProvinces"myCommand.CommandType = CommandType.StoredProcedureIf objCon.State = 0Then objCon.Open()dpunits.DataSource = SqlCmd1.ExecuteReaderdpunits.DataTextField ="province_name" dpunits.DataValueField ="province_key"dpunits.DataBind()objCon.Close()end sub

|||

ndinakar:

Your stored proc would be like this:

create procedure dbo.stored_procedure_GetProvincesAsSelect province_key, province_nameFrom province GO

Then your .NET code could be someting like this: You might have to convert it to C#.

Protected objConAs New SqlConnection(ConfigurationSettings.AppSettings("conn"))pubic sub....Dim myCommandAs SqlCommandmyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="stored_procedure_GetProvinces"myCommand.CommandType = CommandType.StoredProcedureIf objCon.State = 0Then objCon.Open()dpunits.DataSource = SqlCmd1.ExecuteReaderdpunits.DataTextField ="province_name" dpunits.DataValueField ="province_key"dpunits.DataBind()objCon.Close()end sub

What is SqlCmd1 in SqlCmd1.ExecuteReader ? Thanks.

|||

MayLam:

ndinakar:

Your stored proc would be like this:

create procedure dbo.stored_procedure_GetProvincesAsSelect province_key, province_nameFrom province GO

Then your .NET code could be someting like this: You might have to convert it to C#.

Protected objConAs New SqlConnection(ConfigurationSettings.AppSettings("conn"))pubic sub....Dim myCommandAs SqlCommandmyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="stored_procedure_GetProvinces"myCommand.CommandType = CommandType.StoredProcedureIf objCon.State = 0Then objCon.Open()dpunits.DataSource = SqlCmd1.ExecuteReaderdpunits.DataTextField ="province_name" dpunits.DataValueField ="province_key"dpunits.DataBind()objCon.Close()end sub

What is SqlCmd1 in SqlCmd1.ExecuteReader ? Thanks.

I replace SqlCmd1 with myCommand. And it is working now. Thank you very much for your help! Bye!

May

DROPCLEANBUFFERS doesn't seem to clear cache

I have a query that I'm trying to benchmark. The first time I run it,
it takes 40 seconds to run. The next time I run it, it returns in 0
seconds.
I execute the following:
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Then I run the query again and it takes 40 seconds, as expected.
Re-running the query causes it to go back down to 0 seconds, as
expected. However, from this point forward, even if I run the commands
above, the query stays at 0 seconds! I can't seem to clear the cache
anymore.
Is something being cached in spite of dropping the data and procedure
caches?Hello,
It should work. Can you set the Statistics I/O and Statistics time and see
if there is any change in values after and before clearing the cache.
Thanks
Hari
SQL Server MVP
"element533@.gmail.com" wrote:

> I have a query that I'm trying to benchmark. The first time I run it,
> it takes 40 seconds to run. The next time I run it, it returns in 0
> seconds.
> I execute the following:
> CHECKPOINT
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> Then I run the query again and it takes 40 seconds, as expected.
> Re-running the query causes it to go back down to 0 seconds, as
> expected. However, from this point forward, even if I run the commands
> above, the query stays at 0 seconds! I can't seem to clear the cache
> anymore.
> Is something being cached in spite of dropping the data and procedure
> caches?
>|||You might compare the query plans to make sure they are the same. It might
be that a large scan takes 40 seconds without cached data but runs
sub-second once data are cached. A plan change (e.g. index seek) could
cause the query to run sub-second with or without cached data.
Hope this helps.
Dan Guzman
SQL Server MVP
<element533@.gmail.com> wrote in message
news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...
>I have a query that I'm trying to benchmark. The first time I run it,
> it takes 40 seconds to run. The next time I run it, it returns in 0
> seconds.
> I execute the following:
> CHECKPOINT
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> Then I run the query again and it takes 40 seconds, as expected.
> Re-running the query causes it to go back down to 0 seconds, as
> expected. However, from this point forward, even if I run the commands
> above, the query stays at 0 seconds! I can't seem to clear the cache
> anymore.
> Is something being cached in spite of dropping the data and procedure
> caches?
>|||But why would the same SELECT statement result in two different query
plans on two different runs?
Dan Guzman wrote:[vbcol=seagreen]
> You might compare the query plans to make sure they are the same. It migh
t
> be that a large scan takes 40 seconds without cached data but runs
> sub-second once data are cached. A plan change (e.g. index seek) could
> cause the query to run sub-second with or without cached data.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <element533@.gmail.com> wrote in message
> news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...|||> But why would the same SELECT statement result in two different query
> plans on two different runs?
You might get different plans due to differences in statistics.
Hope this helps.
Dan Guzman
SQL Server MVP
<element533@.gmail.com> wrote in message
news:1158182579.563636.167870@.i42g2000cwa.googlegroups.com...
> But why would the same SELECT statement result in two different query
> plans on two different runs?
> Dan Guzman wrote:
>|||On 12 Sep 2006 11:48:15 -0700, element533@.gmail.com wrote:
>I have a query that I'm trying to benchmark. The first time I run it,
>it takes 40 seconds to run. The next time I run it, it returns in 0
>seconds.
>I execute the following:
>CHECKPOINT
>DBCC DROPCLEANBUFFERS
>DBCC FREEPROCCACHE
>Then I run the query again and it takes 40 seconds, as expected.
>Re-running the query causes it to go back down to 0 seconds, as
>expected. However, from this point forward, even if I run the commands
>above, the query stays at 0 seconds! I can't seem to clear the cache
>anymore.
Pretty weird.
You haven't left any transactions open, have you?
You might turn on a few perfmon stats to see if you can get more info.
J.

DROPCLEANBUFFERS doesn't seem to clear cache

I have a query that I'm trying to benchmark. The first time I run it,
it takes 40 seconds to run. The next time I run it, it returns in 0
seconds.
I execute the following:
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Then I run the query again and it takes 40 seconds, as expected.
Re-running the query causes it to go back down to 0 seconds, as
expected. However, from this point forward, even if I run the commands
above, the query stays at 0 seconds! I can't seem to clear the cache
anymore.
Is something being cached in spite of dropping the data and procedure
caches?Hello,
It should work. Can you set the Statistics I/O and Statistics time and see
if there is any change in values after and before clearing the cache.
Thanks
Hari
SQL Server MVP
"element533@.gmail.com" wrote:
> I have a query that I'm trying to benchmark. The first time I run it,
> it takes 40 seconds to run. The next time I run it, it returns in 0
> seconds.
> I execute the following:
> CHECKPOINT
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> Then I run the query again and it takes 40 seconds, as expected.
> Re-running the query causes it to go back down to 0 seconds, as
> expected. However, from this point forward, even if I run the commands
> above, the query stays at 0 seconds! I can't seem to clear the cache
> anymore.
> Is something being cached in spite of dropping the data and procedure
> caches?
>|||You might compare the query plans to make sure they are the same. It might
be that a large scan takes 40 seconds without cached data but runs
sub-second once data are cached. A plan change (e.g. index seek) could
cause the query to run sub-second with or without cached data.
Hope this helps.
Dan Guzman
SQL Server MVP
<element533@.gmail.com> wrote in message
news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...
>I have a query that I'm trying to benchmark. The first time I run it,
> it takes 40 seconds to run. The next time I run it, it returns in 0
> seconds.
> I execute the following:
> CHECKPOINT
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> Then I run the query again and it takes 40 seconds, as expected.
> Re-running the query causes it to go back down to 0 seconds, as
> expected. However, from this point forward, even if I run the commands
> above, the query stays at 0 seconds! I can't seem to clear the cache
> anymore.
> Is something being cached in spite of dropping the data and procedure
> caches?
>|||But why would the same SELECT statement result in two different query
plans on two different runs?
Dan Guzman wrote:
> You might compare the query plans to make sure they are the same. It might
> be that a large scan takes 40 seconds without cached data but runs
> sub-second once data are cached. A plan change (e.g. index seek) could
> cause the query to run sub-second with or without cached data.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <element533@.gmail.com> wrote in message
> news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...
> >I have a query that I'm trying to benchmark. The first time I run it,
> > it takes 40 seconds to run. The next time I run it, it returns in 0
> > seconds.
> >
> > I execute the following:
> >
> > CHECKPOINT
> > DBCC DROPCLEANBUFFERS
> > DBCC FREEPROCCACHE
> >
> > Then I run the query again and it takes 40 seconds, as expected.
> > Re-running the query causes it to go back down to 0 seconds, as
> > expected. However, from this point forward, even if I run the commands
> > above, the query stays at 0 seconds! I can't seem to clear the cache
> > anymore.
> >
> > Is something being cached in spite of dropping the data and procedure
> > caches?
> >|||> But why would the same SELECT statement result in two different query
> plans on two different runs?
You might get different plans due to differences in statistics.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<element533@.gmail.com> wrote in message
news:1158182579.563636.167870@.i42g2000cwa.googlegroups.com...
> But why would the same SELECT statement result in two different query
> plans on two different runs?
> Dan Guzman wrote:
>> You might compare the query plans to make sure they are the same. It
>> might
>> be that a large scan takes 40 seconds without cached data but runs
>> sub-second once data are cached. A plan change (e.g. index seek) could
>> cause the query to run sub-second with or without cached data.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> <element533@.gmail.com> wrote in message
>> news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...
>> >I have a query that I'm trying to benchmark. The first time I run it,
>> > it takes 40 seconds to run. The next time I run it, it returns in 0
>> > seconds.
>> >
>> > I execute the following:
>> >
>> > CHECKPOINT
>> > DBCC DROPCLEANBUFFERS
>> > DBCC FREEPROCCACHE
>> >
>> > Then I run the query again and it takes 40 seconds, as expected.
>> > Re-running the query causes it to go back down to 0 seconds, as
>> > expected. However, from this point forward, even if I run the commands
>> > above, the query stays at 0 seconds! I can't seem to clear the cache
>> > anymore.
>> >
>> > Is something being cached in spite of dropping the data and procedure
>> > caches?
>> >
>|||On 12 Sep 2006 11:48:15 -0700, element533@.gmail.com wrote:
>I have a query that I'm trying to benchmark. The first time I run it,
>it takes 40 seconds to run. The next time I run it, it returns in 0
>seconds.
>I execute the following:
>CHECKPOINT
>DBCC DROPCLEANBUFFERS
>DBCC FREEPROCCACHE
>Then I run the query again and it takes 40 seconds, as expected.
>Re-running the query causes it to go back down to 0 seconds, as
>expected. However, from this point forward, even if I run the commands
>above, the query stays at 0 seconds! I can't seem to clear the cache
>anymore.
Pretty weird.
You haven't left any transactions open, have you?
You might turn on a few perfmon stats to see if you can get more info.
J.

Drop the table or Truncate?

I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.
They are effectively the same, but dropping the table in the right order is
effectively quicker.
Anyway, you could really gain some performance, if you could look into the
possibility of incremental updates, instead of getting rid of the whole lot
and repopulating.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:4092B4F1.6060700@.deltanet.com...
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.
|||Narayana Vyas Kondreddi wrote:

> They are effectively the same, but dropping the table in the right order is
> effectively quicker.
I thought so, also. But someone had mentioned that tables are meant to
be permanent, so it would be better to truncate. The problem is just
because they are meant to be permanent, doesn't mean there is a problem
doing it.
Someone else said it would be better to do all the table drops and
creates - then populate them. I'm not sure why that would be better.
Why would dropping them in the correct order be quicker - I assume you
are talking about referential integrity. In my case, I am deleting
everything each time at night.

> Anyway, you could really gain some performance, if you could look into the
> possibility of incremental updates, instead of getting rid of the whole lot
> and repopulating.
I agree. But in our case the tables are already created on a foreign
system and we have no control over the schema, so it would be difficult
to do incremental updates.
Thanks,
Tom.

> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:4092B4F1.6060700@.deltanet.com...
> I am setting up about 60 or so DTS packages to run every night. These
> packages will be recreating all the tables from a foreign database.
> At the moment, I have them dropping the tables, creating the tables,
> copying all the data to the new table and creating the keys.
> Would it be better to just drop the keys, truncate the tables, copy the
> data and recreate the keys? Or does it really matter? I am, in
> essence, doing the same thing.
> Just trying to see if I am going about the task in the best way.
> Thanks,
> Tom.
>
>
|||Thomas
As Vyas said that dropping the table in the right order is
effectively quicker.
I would a little bit re-phrase his by saying that truncating the table in
the right order is effectively quicker.
As I understood you don't have to drop the table so you have to know
relationship between them and to define the order to truncate tables.( First
truncate a referenced(child) table and then a referncing(father) )
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40936865.4010204@.deltanet.com...[vbcol=seagreen]
> Narayana Vyas Kondreddi wrote:
is[vbcol=seagreen]
>
> I thought so, also. But someone had mentioned that tables are meant to
> be permanent, so it would be better to truncate. The problem is just
> because they are meant to be permanent, doesn't mean there is a problem
> doing it.
> Someone else said it would be better to do all the table drops and
> creates - then populate them. I'm not sure why that would be better.
> Why would dropping them in the correct order be quicker - I assume you
> are talking about referential integrity. In my case, I am deleting
> everything each time at night.
the[vbcol=seagreen]
lot
>
> I agree. But in our case the tables are already created on a foreign
> system and we have no control over the schema, so it would be difficult
> to do incremental updates.
> Thanks,
> Tom.
>
|||Uri Dimant wrote:

> Thomas
> As Vyas said that dropping the table in the right order is
> effectively quicker.
> I would a little bit re-phrase his by saying that truncating the table in
> the right order is effectively quicker.
> As I understood you don't have to drop the table so you have to know
> relationship between them and to define the order to truncate tables.( First
> truncate a referenced(child) table and then a referncing(father) )
I am running this using DTS and to do a straight copy, the program does
a "Create" with optional "Drop". I was thinking of doing a
"Select/Into". So I would do a "Drop Table" and then do a "Select/Into"
from the foreign database.
Is there a reason why I should use the Create and Insert that DTS does
vs the Select/Into way. I was thinking of doing this so I wouldn't have
to make 60 packages (one for each file copied), since each create would
be different. I would just need to pass the file name in a Global
Variable and let DTS do the Select/Into. This would allow me to create
only one package.
Of course, the other problem would be creating the indexes after the
Select/Into. If I need to have a package for each table just to handle
the Indexes, I might as well let DTS do it's normal Create.
Thanks,
Tom.

>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:40936865.4010204@.deltanet.com...
> is
> the
> lot
>
>
|||One thing to consider;
Truncating the table does not get logged in the transaction log. So if
there is a lot of data in the tables, deleting the tables can cause the log
to grow.
Rand
This posting is provided "as is" with no warranties and confers no rights.
sql

Drop the table or Truncate?

I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.They are effectively the same, but dropping the table in the right order is
effectively quicker.
Anyway, you could really gain some performance, if you could look into the
possibility of incremental updates, instead of getting rid of the whole lot
and repopulating.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:4092B4F1.6060700@.deltanet.com...
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.|||Narayana Vyas Kondreddi wrote:

> They are effectively the same, but dropping the table in the right order i
s
> effectively quicker.
I thought so, also. But someone had mentioned that tables are meant to
be permanent, so it would be better to truncate. The problem is just
because they are meant to be permanent, doesn't mean there is a problem
doing it.
Someone else said it would be better to do all the table drops and
creates - then populate them. I'm not sure why that would be better.
Why would dropping them in the correct order be quicker - I assume you
are talking about referential integrity. In my case, I am deleting
everything each time at night.

> Anyway, you could really gain some performance, if you could look into the
> possibility of incremental updates, instead of getting rid of the whole lo
t
> and repopulating.
I agree. But in our case the tables are already created on a foreign
system and we have no control over the schema, so it would be difficult
to do incremental updates.
Thanks,
Tom.

> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:4092B4F1.6060700@.deltanet.com...
> I am setting up about 60 or so DTS packages to run every night. These
> packages will be recreating all the tables from a foreign database.
> At the moment, I have them dropping the tables, creating the tables,
> copying all the data to the new table and creating the keys.
> Would it be better to just drop the keys, truncate the tables, copy the
> data and recreate the keys? Or does it really matter? I am, in
> essence, doing the same thing.
> Just trying to see if I am going about the task in the best way.
> Thanks,
> Tom.
>
>|||Thomas
As Vyas said that dropping the table in the right order is
effectively quicker.
I would a little bit re-phrase his by saying that truncating the table in
the right order is effectively quicker.
As I understood you don't have to drop the table so you have to know
relationship between them and to define the order to truncate tables.( First
truncate a referenced(child) table and then a referncing(father) )
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40936865.4010204@.deltanet.com...
> Narayana Vyas Kondreddi wrote:
>
is[vbcol=seagreen]
>
> I thought so, also. But someone had mentioned that tables are meant to
> be permanent, so it would be better to truncate. The problem is just
> because they are meant to be permanent, doesn't mean there is a problem
> doing it.
> Someone else said it would be better to do all the table drops and
> creates - then populate them. I'm not sure why that would be better.
> Why would dropping them in the correct order be quicker - I assume you
> are talking about referential integrity. In my case, I am deleting
> everything each time at night.
>
the[vbcol=seagreen]
lot[vbcol=seagreen]
>
> I agree. But in our case the tables are already created on a foreign
> system and we have no control over the schema, so it would be difficult
> to do incremental updates.
> Thanks,
> Tom.
>
>|||Uri Dimant wrote:

> Thomas
> As Vyas said that dropping the table in the right order is
> effectively quicker.
> I would a little bit re-phrase his by saying that truncating the table in
> the right order is effectively quicker.
> As I understood you don't have to drop the table so you have to know
> relationship between them and to define the order to truncate tables.( Fir
st
> truncate a referenced(child) table and then a referncing(father) )
I am running this using DTS and to do a straight copy, the program does
a "Create" with optional "Drop". I was thinking of doing a
"Select/Into". So I would do a "Drop Table" and then do a "Select/Into"
from the foreign database.
Is there a reason why I should use the Create and Insert that DTS does
vs the Select/Into way. I was thinking of doing this so I wouldn't have
to make 60 packages (one for each file copied), since each create would
be different. I would just need to pass the file name in a Global
Variable and let DTS do the Select/Into. This would allow me to create
only one package.
Of course, the other problem would be creating the indexes after the
Select/Into. If I need to have a package for each table just to handle
the Indexes, I might as well let DTS do it's normal Create.
Thanks,
Tom.

>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:40936865.4010204@.deltanet.com...
>
> is
>
> the
>
> lot
>
>|||One thing to consider;
Truncating the table does not get logged in the transaction log. So if
there is a lot of data in the tables, deleting the tables can cause the log
to grow.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Drop the table or Truncate?

I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.They are effectively the same, but dropping the table in the right order is
effectively quicker.
Anyway, you could really gain some performance, if you could look into the
possibility of incremental updates, instead of getting rid of the whole lot
and repopulating.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:4092B4F1.6060700@.deltanet.com...
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.|||Narayana Vyas Kondreddi wrote:
> They are effectively the same, but dropping the table in the right order is
> effectively quicker.
I thought so, also. But someone had mentioned that tables are meant to
be permanent, so it would be better to truncate. The problem is just
because they are meant to be permanent, doesn't mean there is a problem
doing it.
Someone else said it would be better to do all the table drops and
creates - then populate them. I'm not sure why that would be better.
Why would dropping them in the correct order be quicker - I assume you
are talking about referential integrity. In my case, I am deleting
everything each time at night.
> Anyway, you could really gain some performance, if you could look into the
> possibility of incremental updates, instead of getting rid of the whole lot
> and repopulating.
I agree. But in our case the tables are already created on a foreign
system and we have no control over the schema, so it would be difficult
to do incremental updates.
Thanks,
Tom.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:4092B4F1.6060700@.deltanet.com...
> I am setting up about 60 or so DTS packages to run every night. These
> packages will be recreating all the tables from a foreign database.
> At the moment, I have them dropping the tables, creating the tables,
> copying all the data to the new table and creating the keys.
> Would it be better to just drop the keys, truncate the tables, copy the
> data and recreate the keys? Or does it really matter? I am, in
> essence, doing the same thing.
> Just trying to see if I am going about the task in the best way.
> Thanks,
> Tom.
>
>|||Thomas
As Vyas said that dropping the table in the right order is
effectively quicker.
I would a little bit re-phrase his by saying that truncating the table in
the right order is effectively quicker.
As I understood you don't have to drop the table so you have to know
relationship between them and to define the order to truncate tables.( First
truncate a referenced(child) table and then a referncing(father) )
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40936865.4010204@.deltanet.com...
> Narayana Vyas Kondreddi wrote:
> > They are effectively the same, but dropping the table in the right order
is
> > effectively quicker.
>
> I thought so, also. But someone had mentioned that tables are meant to
> be permanent, so it would be better to truncate. The problem is just
> because they are meant to be permanent, doesn't mean there is a problem
> doing it.
> Someone else said it would be better to do all the table drops and
> creates - then populate them. I'm not sure why that would be better.
> Why would dropping them in the correct order be quicker - I assume you
> are talking about referential integrity. In my case, I am deleting
> everything each time at night.
> >
> > Anyway, you could really gain some performance, if you could look into
the
> > possibility of incremental updates, instead of getting rid of the whole
lot
> > and repopulating.
>
> I agree. But in our case the tables are already created on a foreign
> system and we have no control over the schema, so it would be difficult
> to do incremental updates.
> Thanks,
> Tom.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> > Is .NET important for a database professional?
> > http://vyaskn.tripod.com/poll.htm
> >
> >
> >
> >
> > "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> > news:4092B4F1.6060700@.deltanet.com...
> > I am setting up about 60 or so DTS packages to run every night. These
> > packages will be recreating all the tables from a foreign database.
> >
> > At the moment, I have them dropping the tables, creating the tables,
> > copying all the data to the new table and creating the keys.
> >
> > Would it be better to just drop the keys, truncate the tables, copy the
> > data and recreate the keys? Or does it really matter? I am, in
> > essence, doing the same thing.
> >
> > Just trying to see if I am going about the task in the best way.
> >
> > Thanks,
> >
> > Tom.
> >
> >
> >
> >
>|||Uri Dimant wrote:
> Thomas
> As Vyas said that dropping the table in the right order is
> effectively quicker.
> I would a little bit re-phrase his by saying that truncating the table in
> the right order is effectively quicker.
> As I understood you don't have to drop the table so you have to know
> relationship between them and to define the order to truncate tables.( First
> truncate a referenced(child) table and then a referncing(father) )
I am running this using DTS and to do a straight copy, the program does
a "Create" with optional "Drop". I was thinking of doing a
"Select/Into". So I would do a "Drop Table" and then do a "Select/Into"
from the foreign database.
Is there a reason why I should use the Create and Insert that DTS does
vs the Select/Into way. I was thinking of doing this so I wouldn't have
to make 60 packages (one for each file copied), since each create would
be different. I would just need to pass the file name in a Global
Variable and let DTS do the Select/Into. This would allow me to create
only one package.
Of course, the other problem would be creating the indexes after the
Select/Into. If I need to have a package for each table just to handle
the Indexes, I might as well let DTS do it's normal Create.
Thanks,
Tom.
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:40936865.4010204@.deltanet.com...
>>Narayana Vyas Kondreddi wrote:
>>
>>They are effectively the same, but dropping the table in the right order
> is
>>effectively quicker.
>>
>>I thought so, also. But someone had mentioned that tables are meant to
>>be permanent, so it would be better to truncate. The problem is just
>>because they are meant to be permanent, doesn't mean there is a problem
>>doing it.
>>Someone else said it would be better to do all the table drops and
>>creates - then populate them. I'm not sure why that would be better.
>>Why would dropping them in the correct order be quicker - I assume you
>>are talking about referential integrity. In my case, I am deleting
>>everything each time at night.
>>
>>Anyway, you could really gain some performance, if you could look into
> the
>>possibility of incremental updates, instead of getting rid of the whole
> lot
>>and repopulating.
>>
>>I agree. But in our case the tables are already created on a foreign
>>system and we have no control over the schema, so it would be difficult
>>to do incremental updates.
>>Thanks,
>>Tom.
>>
>>--
>>HTH,
>>Vyas, MVP (SQL Server)
>>http://vyaskn.tripod.com/
>>Is .NET important for a database professional?
>>http://vyaskn.tripod.com/poll.htm
>>
>>
>>"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
>>news:4092B4F1.6060700@.deltanet.com...
>>I am setting up about 60 or so DTS packages to run every night. These
>>packages will be recreating all the tables from a foreign database.
>>At the moment, I have them dropping the tables, creating the tables,
>>copying all the data to the new table and creating the keys.
>>Would it be better to just drop the keys, truncate the tables, copy the
>>data and recreate the keys? Or does it really matter? I am, in
>>essence, doing the same thing.
>>Just trying to see if I am going about the task in the best way.
>>Thanks,
>>Tom.
>>
>>
>|||One thing to consider;
Truncating the table does not get logged in the transaction log. So if
there is a lot of data in the tables, deleting the tables can cause the log
to grow.
Rand
This posting is provided "as is" with no warranties and confers no rights.

DROP TABLE failed

An overnight run failed, and I now regularly get the message:
Could not find row in sysindexes for database ID nn, object ID nnnnnnnnnn,
index ID -1. Run DBCC CHECKTABLE on sysindexes.
It appears that a DROP TABLE failed - the table does not show up in
Enterprise Manager, but a row for the table appears in sysobjects. DBCC
CHECKTABLE did not help.
How can I safely get out of this? Presumably it is not safe to delete the
row from sysobjects?
In future, would it help to put the DROP TABLE instruction within a
transaction?
Thanks.
Peter Hyssett
Yeah, that can happen, here is another post which clearly identifies the
problem and give a hint how to solve it:
http://groups.google.de/group/micros...5b5409616cfed4
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Peter Hyssett" <PeterHyssett@.discussions.microsoft.com> schrieb im
Newsbeitrag news:732EDDE9-15C5-42D4-9A81-F4A80E951320@.microsoft.com...
> An overnight run failed, and I now regularly get the message:
> Could not find row in sysindexes for database ID nn, object ID nnnnnnnnnn,
> index ID -1. Run DBCC CHECKTABLE on sysindexes.
> It appears that a DROP TABLE failed - the table does not show up in
> Enterprise Manager, but a row for the table appears in sysobjects. DBCC
> CHECKTABLE did not help.
> How can I safely get out of this? Presumably it is not safe to delete the
> row from sysobjects?
> In future, would it help to put the DROP TABLE instruction within a
> transaction?
> Thanks.
> --
> Peter Hyssett
sql

DROP TABLE failed

An overnight run failed, and I now regularly get the message:
Could not find row in sysindexes for database ID nn, object ID nnnnnnnnnn,
index ID -1. Run DBCC CHECKTABLE on sysindexes.
It appears that a DROP TABLE failed - the table does not show up in
Enterprise Manager, but a row for the table appears in sysobjects. DBCC
CHECKTABLE did not help.
How can I safely get out of this? Presumably it is not safe to delete the
row from sysobjects?
In future, would it help to put the DROP TABLE instruction within a
transaction?
Thanks.
--
Peter HyssettYeah, that can happen, here is another post which clearly identifies the
problem and give a hint how to solve it:
e97a7097d5e766ff/db5b5409616cfed4? q=%22Drop+Table%22+%22exists+in+sysobjec
ts%22&rn
um=3&hl=de#db5b5409616cfed4" target="_blank">http://groups.google.de/group/micro...b5b5409616cfed4
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Peter Hyssett" <PeterHyssett@.discussions.microsoft.com> schrieb im
Newsbeitrag news:732EDDE9-15C5-42D4-9A81-F4A80E951320@.microsoft.com...
> An overnight run failed, and I now regularly get the message:
> Could not find row in sysindexes for database ID nn, object ID nnnnnnnnnn,
> index ID -1. Run DBCC CHECKTABLE on sysindexes.
> It appears that a DROP TABLE failed - the table does not show up in
> Enterprise Manager, but a row for the table appears in sysobjects. DBCC
> CHECKTABLE did not help.
> How can I safely get out of this? Presumably it is not safe to delete the
> row from sysobjects?
> In future, would it help to put the DROP TABLE instruction within a
> transaction?
> Thanks.
> --
> Peter Hyssett

DROP TABLE failed

An overnight run failed, and I now regularly get the message:
Could not find row in sysindexes for database ID nn, object ID nnnnnnnnnn,
index ID -1. Run DBCC CHECKTABLE on sysindexes.
It appears that a DROP TABLE failed - the table does not show up in
Enterprise Manager, but a row for the table appears in sysobjects. DBCC
CHECKTABLE did not help.
How can I safely get out of this? Presumably it is not safe to delete the
row from sysobjects?
In future, would it help to put the DROP TABLE instruction within a
transaction?
Thanks.
--
Peter HyssettYeah, that can happen, here is another post which clearly identifies the
problem and give a hint how to solve it:
http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/e97a7097d5e766ff/db5b5409616cfed4?q=%22Drop+Table%22+%22exists+in+sysobjects%22&rnum=3&hl=de#db5b5409616cfed4
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Peter Hyssett" <PeterHyssett@.discussions.microsoft.com> schrieb im
Newsbeitrag news:732EDDE9-15C5-42D4-9A81-F4A80E951320@.microsoft.com...
> An overnight run failed, and I now regularly get the message:
> Could not find row in sysindexes for database ID nn, object ID nnnnnnnnnn,
> index ID -1. Run DBCC CHECKTABLE on sysindexes.
> It appears that a DROP TABLE failed - the table does not show up in
> Enterprise Manager, but a row for the table appears in sysobjects. DBCC
> CHECKTABLE did not help.
> How can I safely get out of this? Presumably it is not safe to delete the
> row from sysobjects?
> In future, would it help to put the DROP TABLE instruction within a
> transaction?
> Thanks.
> --
> Peter Hyssett

drop out of stored procedure when found some data

Hi
I have a stored procedure which takes a while to run and returns some
data. I want to make
another version that just says is there some data to look at (boolean,
yes no). This should
run as quickly as possible so as soon as it finds theres any data to
look at it stops. Is
there any general way of doing this?
This has to work in oracle and sqlserver 2000.
Can this be done in SQL. I'm afraid I know very little about SQL so I
just don't know if its even possible?
thanks
fshard wrote:
> Hi
> I have a stored procedure which takes a while to run and returns some
> data. I want to make
> another version that just says is there some data to look at (boolean,
> yes no). This should
> run as quickly as possible so as soon as it finds theres any data to
> look at it stops. Is
> there any general way of doing this?
> This has to work in oracle and sqlserver 2000.
> Can this be done in SQL. I'm afraid I know very little about SQL so I
> just don't know if its even possible?
> thanks
> f
Use EXISTS:
IF EXISTS (SELECT ... /* something */)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> IF EXISTS (SELECT ... /* something */)
> --
> David Portas, SQL Server MVP
thanks for that, nice simple answer.

drop out of stored procedure when found some data

Hi
I have a stored procedure which takes a while to run and returns some
data. I want to make
another version that just says is there some data to look at (boolean,
yes no). This should
run as quickly as possible so as soon as it finds theres any data to
look at it stops. Is
there any general way of doing this?
This has to work in oracle and sqlserver 2000.
Can this be done in SQL. I'm afraid I know very little about SQL so I
just don't know if its even possible?
thanks
fshard wrote:
> Hi
> I have a stored procedure which takes a while to run and returns some
> data. I want to make
> another version that just says is there some data to look at (boolean,
> yes no). This should
> run as quickly as possible so as soon as it finds theres any data to
> look at it stops. Is
> there any general way of doing this?
> This has to work in oracle and sqlserver 2000.
> Can this be done in SQL. I'm afraid I know very little about SQL so I
> just don't know if its even possible?
> thanks
> f
Use EXISTS:
IF EXISTS (SELECT ... /* something */)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> IF EXISTS (SELECT ... /* something */)
> --
> David Portas, SQL Server MVP
thanks for that, nice simple answer.

Drop and create procedure

I have to run a Big Sproc for make a lot of updates and insert. because trigger it take to many time.
I can drop the trigger before the procedure and recreate it after, but I wondered whether there existed of other solution?

Can I deactive the trigger? I'm affraid too got two copie of code for the trigger that why I dont really like the Drop-Create solution...

ThanksWhat is the trigger for?

There's no way I know of to disable the trigger...but what's the big deal with

DROP TRIGGER

EXEC Sproc

CREATE TRIGGER

The only thing is, whatever the trigger is for, it's there for a reason, and wouldn't dropping cause you any data integrity issues?|||Actually, it can be done in SQL 2000. I have never tried it, but there is ALTER TABLE syntax for enabling/disabling a trigger.

As Brett pointed out, though, you want to be sure that not only your process but any other process accessing the table will not be adversely affected by the sudden disabling of the trigger.|||No Sheet!

Still, once I've put a trigger in place, I've never had a need (or want) to remove it.

You might want to consider some alternatives

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 int)
GO

CREATE TRIGGER myTrigger99 ON myTable99
FOR INSERT
AS
BEGIN
UPDATE t SET t.Col2 = t.Col2 * 2
FROM myTable99 t JOIN inserted i ON t.Col1 = i.Col1
END
GO

INSERT INTO myTable99(Col2) SELECT 1

SELECT * FROM myTable99
GO

ALTER TABLE myTable99
DISABLE TRIGGER myTrigger99
GO

INSERT INTO myTable99(Col2) SELECT 1

SELECT * FROM myTable99
GO

ALTER TABLE myTable99
ENABLE TRIGGER myTrigger99
GO
INSERT INTO myTable99(Col2) SELECT 2

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TRIGGER myTrigger99
DROP TABLE myTable99
GO

DROP and CREATE INDEXES

I'm trying to reduce fragmentation of indexes on tables. When I run DBCC
SHOWCONTIG on the table both befor and after dropping and recreating the
indexes, including PK constraints, the results are the same? Any insight? I
thought this would reorganize the data to be more efficient? What am I doing
wrong?
DROP INDEX [PD7333].[F98741].[F98741_1]
ALTER TABLE [PD7333].[F98741] DROP CONSTRAINT [F98741_PK]
CREATE NONCLUSTERED INDEX [F98741_1] ON [PD7333].[F98741] ([ESEVSPEC],
[ESEVSEQ])
ALTER TABLE [PD7333].[F98741] ADD CONSTRAINT [F98741_PK] PRIMARY KEY
NONCLUSTERED ([ESEVSK], [ESEVSEQ])
DBCC SHOWCONTIG scanning 'F98741' table...
Table: 'F98741' (277576027); index ID: 0, database ID: 14
TABLE level scan performed.
- Pages Scanned........................: 30049
- Extents Scanned.......................: 3786
- Extent Switches.......................: 3785
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.23% [3757:3786]
- Extent Scan Fragmentation ...............: 97.94%
- Avg. Bytes Free per Page................: 221.6
- Avg. Page Density (full)................: 97.26%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
That is because your table is a HEAP which means there is no clustered
index. You can not reduce fragmentation on a HEAP by reindexing. Choose a
proper column and create a clustered index and your problem will go away.
By the way you should use DBCC DBREINDEX instead of dropping and creating.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:9E90DF53-9D44-40CF-A611-BEF8C29BE3DD@.microsoft.com...
> I'm trying to reduce fragmentation of indexes on tables. When I run DBCC
> SHOWCONTIG on the table both befor and after dropping and recreating the
> indexes, including PK constraints, the results are the same? Any insight?
> I
> thought this would reorganize the data to be more efficient? What am I
> doing
> wrong?
> DROP INDEX [PD7333].[F98741].[F98741_1]
> ALTER TABLE [PD7333].[F98741] DROP CONSTRAINT [F98741_PK]
> CREATE NONCLUSTERED INDEX [F98741_1] ON [PD7333].[F98741] ([ESEVSPEC],
> [ESEVSEQ])
> ALTER TABLE [PD7333].[F98741] ADD CONSTRAINT [F98741_PK] PRIMARY KEY
> NONCLUSTERED ([ESEVSK], [ESEVSEQ])
>
> DBCC SHOWCONTIG scanning 'F98741' table...
> Table: 'F98741' (277576027); index ID: 0, database ID: 14
> TABLE level scan performed.
> - Pages Scanned........................: 30049
> - Extents Scanned.......................: 3786
> - Extent Switches.......................: 3785
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.23% [3757:3786]
> - Extent Scan Fragmentation ...............: 97.94%
> - Avg. Bytes Free per Page................: 221.6
> - Avg. Page Density (full)................: 97.26%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
|||Thanks. I had started with DBREINDEX but when I saw it made no change, I
resorted to the DROP/CREATE. Unforuntately this is a 3rd party system and I
don't know if I can add and/or change these indexes to clustered...I'll have
to find out.
"Andrew J. Kelly" wrote:

> That is because your table is a HEAP which means there is no clustered
> index. You can not reduce fragmentation on a HEAP by reindexing. Choose a
> proper column and create a clustered index and your problem will go away.
> By the way you should use DBCC DBREINDEX instead of dropping and creating.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:9E90DF53-9D44-40CF-A611-BEF8C29BE3DD@.microsoft.com...
>
>
|||Well you need to tell them to get their act together<g>. You can create a
clustered index and then drop it to defrag the table but each table really
should have a clustered index on it.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:E8335A7A-73EA-427B-9DB3-1BBE473488D0@.microsoft.com...[vbcol=seagreen]
> Thanks. I had started with DBREINDEX but when I saw it made no change, I
> resorted to the DROP/CREATE. Unforuntately this is a 3rd party system and
> I
> don't know if I can add and/or change these indexes to clustered...I'll
> have
> to find out.
> "Andrew J. Kelly" wrote:

DROP and CREATE INDEXES

I'm trying to reduce fragmentation of indexes on tables. When I run DBCC
SHOWCONTIG on the table both befor and after dropping and recreating the
indexes, including PK constraints, the results are the same? Any insight?
I
thought this would reorganize the data to be more efficient? What am I doin
g
wrong?
DROP INDEX [PD7333].[F98741].[F98741_1]
ALTER TABLE [PD7333].[F98741] DROP CONSTRAINT [F98741_PK]
CREATE NONCLUSTERED INDEX [F98741_1] ON [PD7333].[F98741] ([
ESEVSPEC],
[ESEVSEQ])
ALTER TABLE [PD7333].[F98741] ADD CONSTRAINT [F98741_PK] PRIMARY
KEY
NONCLUSTERED ([ESEVSK], [ESEVSEQ])
DBCC SHOWCONTIG scanning 'F98741' table...
Table: 'F98741' (277576027); index ID: 0, database ID: 14
TABLE level scan performed.
- Pages Scanned........................: 30049
- Extents Scanned.......................: 3786
- Extent Switches.......................: 3785
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.23% [3757:3786]
- Extent Scan Fragmentation ...............: 97.94%
- Avg. Bytes Free per Page................: 221.6
- Avg. Page Density (full)................: 97.26%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.That is because your table is a HEAP which means there is no clustered
index. You can not reduce fragmentation on a HEAP by reindexing. Choose a
proper column and create a clustered index and your problem will go away.
By the way you should use DBCC DBREINDEX instead of dropping and creating.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:9E90DF53-9D44-40CF-A611-BEF8C29BE3DD@.microsoft.com...
> I'm trying to reduce fragmentation of indexes on tables. When I run DBCC
> SHOWCONTIG on the table both befor and after dropping and recreating the
> indexes, including PK constraints, the results are the same? Any insight?
> I
> thought this would reorganize the data to be more efficient? What am I
> doing
> wrong?
> DROP INDEX [PD7333].[F98741].[F98741_1]
> ALTER TABLE [PD7333].[F98741] DROP CONSTRAINT [F98741_PK]
> CREATE NONCLUSTERED INDEX [F98741_1] ON [PD7333].[F98741] (
1;ESEVSPEC],
> [ESEVSEQ])
> ALTER TABLE [PD7333].[F98741] ADD CONSTRAINT [F98741_PK] PRIMA
RY KEY
> NONCLUSTERED ([ESEVSK], [ESEVSEQ])
>
> DBCC SHOWCONTIG scanning 'F98741' table...
> Table: 'F98741' (277576027); index ID: 0, database ID: 14
> TABLE level scan performed.
> - Pages Scanned........................: 30049
> - Extents Scanned.......................: 3786
> - Extent Switches.......................: 3785
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.23% [3757:3786
]
> - Extent Scan Fragmentation ...............: 97.94%
> - Avg. Bytes Free per Page................: 221.6
> - Avg. Page Density (full)................: 97.26%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.|||Thanks. I had started with DBREINDEX but when I saw it made no change, I
resorted to the DROP/CREATE. Unforuntately this is a 3rd party system and I
don't know if I can add and/or change these indexes to clustered...I'll have
to find out.
"Andrew J. Kelly" wrote:

> That is because your table is a HEAP which means there is no clustered
> index. You can not reduce fragmentation on a HEAP by reindexing. Choose a
> proper column and create a clustered index and your problem will go away.
> By the way you should use DBCC DBREINDEX instead of dropping and creating.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:9E90DF53-9D44-40CF-A611-BEF8C29BE3DD@.microsoft.com...
>
>|||Well you need to tell them to get their act together<g>. You can create a
clustered index and then drop it to defrag the table but each table really
should have a clustered index on it.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:E8335A7A-73EA-427B-9DB3-1BBE473488D0@.microsoft.com...[vbcol=seagreen]
> Thanks. I had started with DBREINDEX but when I saw it made no change, I
> resorted to the DROP/CREATE. Unforuntately this is a 3rd party system and
> I
> don't know if I can add and/or change these indexes to clustered...I'll
> have
> to find out.
> "Andrew J. Kelly" wrote:
>

DROP and CREATE INDEXES

I'm trying to reduce fragmentation of indexes on tables. When I run DBCC
SHOWCONTIG on the table both befor and after dropping and recreating the
indexes, including PK constraints, the results are the same? Any insight? I
thought this would reorganize the data to be more efficient? What am I doing
wrong?
DROP INDEX [PD7333].[F98741].[F98741_1]
ALTER TABLE [PD7333].[F98741] DROP CONSTRAINT [F98741_PK]
CREATE NONCLUSTERED INDEX [F98741_1] ON [PD7333].[F98741] ([ESEVSPEC],
[ESEVSEQ])
ALTER TABLE [PD7333].[F98741] ADD CONSTRAINT [F98741_PK] PRIMARY KEY
NONCLUSTERED ([ESEVSK], [ESEVSEQ])
DBCC SHOWCONTIG scanning 'F98741' table...
Table: 'F98741' (277576027); index ID: 0, database ID: 14
TABLE level scan performed.
- Pages Scanned........................: 30049
- Extents Scanned.......................: 3786
- Extent Switches.......................: 3785
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.23% [3757:3786]
- Extent Scan Fragmentation ...............: 97.94%
- Avg. Bytes Free per Page................: 221.6
- Avg. Page Density (full)................: 97.26%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.That is because your table is a HEAP which means there is no clustered
index. You can not reduce fragmentation on a HEAP by reindexing. Choose a
proper column and create a clustered index and your problem will go away.
By the way you should use DBCC DBREINDEX instead of dropping and creating.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:9E90DF53-9D44-40CF-A611-BEF8C29BE3DD@.microsoft.com...
> I'm trying to reduce fragmentation of indexes on tables. When I run DBCC
> SHOWCONTIG on the table both befor and after dropping and recreating the
> indexes, including PK constraints, the results are the same? Any insight?
> I
> thought this would reorganize the data to be more efficient? What am I
> doing
> wrong?
> DROP INDEX [PD7333].[F98741].[F98741_1]
> ALTER TABLE [PD7333].[F98741] DROP CONSTRAINT [F98741_PK]
> CREATE NONCLUSTERED INDEX [F98741_1] ON [PD7333].[F98741] ([ESEVSPEC],
> [ESEVSEQ])
> ALTER TABLE [PD7333].[F98741] ADD CONSTRAINT [F98741_PK] PRIMARY KEY
> NONCLUSTERED ([ESEVSK], [ESEVSEQ])
>
> DBCC SHOWCONTIG scanning 'F98741' table...
> Table: 'F98741' (277576027); index ID: 0, database ID: 14
> TABLE level scan performed.
> - Pages Scanned........................: 30049
> - Extents Scanned.......................: 3786
> - Extent Switches.......................: 3785
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.23% [3757:3786]
> - Extent Scan Fragmentation ...............: 97.94%
> - Avg. Bytes Free per Page................: 221.6
> - Avg. Page Density (full)................: 97.26%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.|||Thanks. I had started with DBREINDEX but when I saw it made no change, I
resorted to the DROP/CREATE. Unforuntately this is a 3rd party system and I
don't know if I can add and/or change these indexes to clustered...I'll have
to find out.
"Andrew J. Kelly" wrote:
> That is because your table is a HEAP which means there is no clustered
> index. You can not reduce fragmentation on a HEAP by reindexing. Choose a
> proper column and create a clustered index and your problem will go away.
> By the way you should use DBCC DBREINDEX instead of dropping and creating.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:9E90DF53-9D44-40CF-A611-BEF8C29BE3DD@.microsoft.com...
> > I'm trying to reduce fragmentation of indexes on tables. When I run DBCC
> > SHOWCONTIG on the table both befor and after dropping and recreating the
> > indexes, including PK constraints, the results are the same? Any insight?
> > I
> > thought this would reorganize the data to be more efficient? What am I
> > doing
> > wrong?
> >
> > DROP INDEX [PD7333].[F98741].[F98741_1]
> >
> > ALTER TABLE [PD7333].[F98741] DROP CONSTRAINT [F98741_PK]
> >
> > CREATE NONCLUSTERED INDEX [F98741_1] ON [PD7333].[F98741] ([ESEVSPEC],
> > [ESEVSEQ])
> >
> > ALTER TABLE [PD7333].[F98741] ADD CONSTRAINT [F98741_PK] PRIMARY KEY
> > NONCLUSTERED ([ESEVSK], [ESEVSEQ])
> >
> >
> >
> > DBCC SHOWCONTIG scanning 'F98741' table...
> > Table: 'F98741' (277576027); index ID: 0, database ID: 14
> > TABLE level scan performed.
> > - Pages Scanned........................: 30049
> > - Extents Scanned.......................: 3786
> > - Extent Switches.......................: 3785
> > - Avg. Pages per Extent..................: 7.9
> > - Scan Density [Best Count:Actual Count]......: 99.23% [3757:3786]
> > - Extent Scan Fragmentation ...............: 97.94%
> > - Avg. Bytes Free per Page................: 221.6
> > - Avg. Page Density (full)................: 97.26%
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
>
>|||Well you need to tell them to get their act together<g>. You can create a
clustered index and then drop it to defrag the table but each table really
should have a clustered index on it.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:E8335A7A-73EA-427B-9DB3-1BBE473488D0@.microsoft.com...
> Thanks. I had started with DBREINDEX but when I saw it made no change, I
> resorted to the DROP/CREATE. Unforuntately this is a 3rd party system and
> I
> don't know if I can add and/or change these indexes to clustered...I'll
> have
> to find out.
> "Andrew J. Kelly" wrote:
>> That is because your table is a HEAP which means there is no clustered
>> index. You can not reduce fragmentation on a HEAP by reindexing. Choose
>> a
>> proper column and create a clustered index and your problem will go away.
>> By the way you should use DBCC DBREINDEX instead of dropping and
>> creating.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:9E90DF53-9D44-40CF-A611-BEF8C29BE3DD@.microsoft.com...
>> > I'm trying to reduce fragmentation of indexes on tables. When I run
>> > DBCC
>> > SHOWCONTIG on the table both befor and after dropping and recreating
>> > the
>> > indexes, including PK constraints, the results are the same? Any
>> > insight?
>> > I
>> > thought this would reorganize the data to be more efficient? What am I
>> > doing
>> > wrong?
>> >
>> > DROP INDEX [PD7333].[F98741].[F98741_1]
>> >
>> > ALTER TABLE [PD7333].[F98741] DROP CONSTRAINT [F98741_PK]
>> >
>> > CREATE NONCLUSTERED INDEX [F98741_1] ON [PD7333].[F98741] ([ESEVSPEC],
>> > [ESEVSEQ])
>> >
>> > ALTER TABLE [PD7333].[F98741] ADD CONSTRAINT [F98741_PK] PRIMARY KEY
>> > NONCLUSTERED ([ESEVSK], [ESEVSEQ])
>> >
>> >
>> >
>> > DBCC SHOWCONTIG scanning 'F98741' table...
>> > Table: 'F98741' (277576027); index ID: 0, database ID: 14
>> > TABLE level scan performed.
>> > - Pages Scanned........................: 30049
>> > - Extents Scanned.......................: 3786
>> > - Extent Switches.......................: 3785
>> > - Avg. Pages per Extent..................: 7.9
>> > - Scan Density [Best Count:Actual Count]......: 99.23% [3757:3786]
>> > - Extent Scan Fragmentation ...............: 97.94%
>> > - Avg. Bytes Free per Page................: 221.6
>> > - Avg. Page Density (full)................: 97.26%
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>>

Drive Fragmentation

The drive on my SQL 2K Server is highly fragmented. Do I
need to shut down SQL before I run the defrag or can I
just run it?From an old post...
There are 2 kinds of fragmentation. You have fragmentation at the OS (or
file) level and at the Table level inside the DB file(s). The first kind
(OS) can be fixed with a product like Disk Keeper but you have to stop SQL
Server for it to work. The .MDF will only be fragmented if you didn't have
enough contiguous space when you created it or if it was resized at a later
date and there wasn't a contiguous space. More than likely it is at the
table level. If you have clustered indexes you can use DBCC DBREINDEX or
DBCC INDEXDEFRAG to fix that.
HTH
Ryan Waight, MCDBA, MCSE
"JUSBYS" <anonymous@.discussions.microsoft.com> wrote in message
news:061901c3a545$4c761780$a001280a@.phx.gbl...
> The drive on my SQL 2K Server is highly fragmented. Do I
> need to shut down SQL before I run the defrag or can I
> just run it?
>|||I was told from a defrag too manufacturer that you can defrag it without
shutting down SQL Server. I still don't defrag without shutting down,
though... :-)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"JUSBYS" <anonymous@.discussions.microsoft.com> wrote in message
news:061901c3a545$4c761780$a001280a@.phx.gbl...
> The drive on my SQL 2K Server is highly fragmented. Do I
> need to shut down SQL before I run the defrag or can I
> just run it?
>|||If this fragmentation is of the OS type and you have
enough room, you can try a utility from
www.sysinternals.com called 'contig'. This will defrag
individual database files without needing to shut down the
database. I use it here to defrag my Oracle and SQL
Server databases and I've never had a problem with it.
That's said, every environment is different so test it
EXTENSIVELY and make sure your backup/recovery plan is
bulletproof before turning on a live system!
HTH
>--Original Message--
>The drive on my SQL 2K Server is highly fragmented. Do I
>need to shut down SQL before I run the defrag or can I
>just run it?
>
>.
>

Drive allocation

Any comments, please. I have a new server 5x160GB drives. It needs to run IIS for a web app and SQL Server 2005. One user database. I was thinking of dividing space as follows and wanted to get some thoughts from others.

2 drives = RAID 1 set, 160GB space, OS, IIS, SQL exe, tempDB, user database log file

3 drives = RAID 5 set, 320GB space, SQL data files, user database files

Thanks for looking...

With just two virtual drives, it is a tough call.

I'd probably put the TempDb on the RAID 5 set -leaving only the Transaction Log on the Mirror set.

|||Thanks. I did not know what to do with the tempdb. Also, is there any benefit to try and create multiple partitions on a single logical disk array? So that one RAID 1 set has a partition for [C drive] and a partition for [L drive] to hold the transaction log? Or should it just be a single partition for the C drive, and put everything on that same logical drive partition?|||

The only significant benefit is in 'housekeeping' -easier to backup all files in a partition, for example.

However, in this situation, it seems like a little extra overhead for no additional benefit.

Hopefully, on a operational server, hosting no other applications except SQL Server, there will be very little disk activity required except for the TRANSACTION Log file -so there should not be any fragmentation, and the heads will most often be in the optimal location.

|||Thanks, your input has been helpful.