Dropping Indexes

I'm doing some bulk inserting into various tables, and I'm dropping all the
indexes first, and rebuilding them after the data load. One of the tables
has several nonclustered indexes + a clustered index. My question is
simple: does the order I drop the indexes make a difference in the speed
with which they are dropped? My thought is that the clustered index should
be dropped last. Any suggestions?
ThanksFrom the Books Online topic "Dropping Indexes"
Dropping a clustered index can take time because in addition to dropping the
clustered index, all nonclustered indexes on the table must be rebuilt to
replace the clustered index keys with row pointers to the heap. When you
drop all indexes on a table, drop the nonclustered indexes first and the
clustered index last. That way, no indexes have to be rebuilt.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Mike C#" <xyz@.xyz.com> wrote in message
news:OvpqShbhGHA.1276@.TK2MSFTNGP03.phx.gbl...
> I'm doing some bulk inserting into various tables, and I'm dropping all
> the indexes first, and rebuilding them after the data load. One of the
> tables has several nonclustered indexes + a clustered index. My question
> is simple: does the order I drop the indexes make a difference in the
> speed with which they are dropped? My thought is that the clustered index
> should be dropped last. Any suggestions?
> Thanks
>|||That's what I figured, just wanted to make sure. Thanks Gail.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:Odo3RpbhGHA.4864@.TK2MSFTNGP03.phx.gbl...
> From the Books Online topic "Dropping Indexes"
> Dropping a clustered index can take time because in addition to dropping
> the clustered index, all nonclustered indexes on the table must be rebuilt
> to replace the clustered index keys with row pointers to the heap. When
> you drop all indexes on a table, drop the nonclustered indexes first and
> the clustered index last. That way, no indexes have to be rebuilt.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:OvpqShbhGHA.1276@.TK2MSFTNGP03.phx.gbl...
>sql

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