.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Does dropping a table automatically drop its contraints and indexes?

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :Sql Server
 
When I have a table that has foreign key constraints and/or index and generate a delete script with Management Studio, the script explicitly drops all constraints/indexes before dropping the table.  Is this necessary?  Or does dropping a table automatically "cascade delete" all that table's constraints, indexes, etc...?


View Complete Post


More Related Resource Links

How to setup db diagram to automatically insert username into table?

  
 I have a table setup in the database for users who are basically going to create a classified listing.  How can I setup a diagram that would easily create a table relationship with the Users table, so that it automatically inserts the users username when they submit a new ad?  If so, how?  I've tried adding a UserName row to my table and creating a relationship with the Users table "UserName" row, but it wasn't accepting the relationship when I tried to save the diagram...apparently because the UserName row in the Users table isn't a primary key?Also, one this is setup, what's the best way to provide the user a way to see a list of their ads and have the ability to edit them?

Excel Drop Table question

  
Can anyone tell me if the drop table syntax should delete only the data in a worksheet or clear the formatting as well. If not is there a way to clear all the formatting. Thanks

Performance Drop when switching from a #temp table to a @temp table variable

  

I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?

Thanks,
Jim


Performance Drop when switching from a #temp table to a @temp table variable

  

I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?

Thanks,
Jim


Modify a table name without drop it and recreate it using T SQL

  

Hi

How to modify a table name without drop it and recreate it using T SQL. Suppose the SSMS is not installed or reachable.

Thank you

 


The complexity resides in the simplicity

how can i drag and drop fields of table in teh desing surface

  

I am followint the insructions of this walkthrough of VS2003

http://msdn.microsoft.com/es-es/library/1as0t7ff(en-us,VS.71).aspx

adn in a part say this


  •  8   Find the authors node and expand it to show the fields in the authors table.
  •  9   Using CTRL+Click, select the au_id, au_lname, au_fname, and city fields.
  •  10  Drag these fields from Server Explorer onto the design surface
  • Now i am using VS2008 

    When i try to drag and drop i can not made it is maybe because of the differences between 2003 and 2008 versions

    how can do it in VS2008?

    Luis 


    Drop tables where table name is a parameter

      

    I'm trying to drop some tables and I created a curser to do so.

    The table name is a parameter.

    I get an error message when I run this code in the curser.

    Is there any way to run this code in a different way.

    I'm trying to drop databases as well where database name is a paramerter, can I do so?

     

     

    Declare

     

    @DatabaseName varchar(30)

    Declare

     

    @FullDatabaseName

    Difference between truncate and drop table on database fragmentation

      

    I have a group of developers that are creating stored procedures, in them they are dropping a table and re-creating the table and then continuing with the processing.  My question is if there is any difference between the effect of dropping a table and truncating the table on database fragmentatiom.

     

    Thanks

    Pam 


    cascading drop down lists from single table

      

    my one table consists of fields (id, flying from, flying to). thus i have one drop down for flying from and another drop down for flying to. i'm hoping to cascade them so 2nd drop down values are dependant on the values from 1st drop down. is it possible to do this using a single table? or must i use two tables and link the id's from both? and do i write anyting in the SelectedIndexChange event? thanks...

    the sqldatasource code looks like this:

    Flying From:

            <asp:DropDownList ID="ddlFlyingFrom" runat="server" DataSourceID="SqlDataSource1"
                DataTextField="FlyingFrom" DataValueField="Id" AutoPostBack="True" OnSelectedIndexChanged="ddlFlyingFrom_SelectedIndexChanged">
            </asp:DropDownList>

            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"
                SelectCommand="SELECT [Id], [FlyingFrom] FROM [Flights]"></asp:SqlDataSource>
          &nb

    will create / drop a lot of indexes affect sql performance?

      
    if so, how badly it affects the performance? disk, memory or cpu?

    Getting the values of drop down repeating table in info path

      
    HI how can i get all the values of a drop down repeating table, using XPathNavigator please reply me!

    SSMS/sqlwb.exe hangs when expanding indexes on table in object explorer when table has 10 or more in

      
    Greetings, I have had an anoying problem in SSMS for some time, it seems to hang in the object explorer when I try to expand indexes on a table that has 10 or more indexes. It works fine when we have fewer indexes but when there are 10 or 12, it opens up and shows the indexes and you can work with them (script them, drop, etc.) but the "stop" button in object explorer shows red and is unresponsive as in you can't click and stop it. Eventually I will shut down SSMS and reopen to clear it, but in task manager there is still the sqlwb.exe running and its using 50% or more of the CPU and I have to end that process. I am running SQL 2005 management studio 9.00.4035.00. Any ideas on a fix out there would be appreciated.

    Updating multiple Indexes on a table - Performance issue

      

    Hi

    I have a performance issue when trying to update a table with multiple indexes. The table itself has about 280 million rows. The selection of the records is fast, about 160 ms, as it has a suitable non-clustered index. However the update itself takes over 10 minutes.

    When I look at the excution plan, it shows the update to the clustered index as well 5 other non clustered index which are being affected by the statement. Unfortunately it doesn't show me how those indexes are being accessed. I suspect that the update statement is performing a full index scan against each of the non-clustered indexes in order to do the update.

    So my question is this, if i add the key columns of the other non-clustered indexes as included columns on the index used to select the records for update will sqlserver use them to access the additional non-clustered indexes?

    any advice greatly appreciated

    Conan


    How to automatically refresh pivot table in excel web access?

      

    Hi,

    I have created UDF which takes parameters and returns data from the SQL back end. On the data returned to excel, i have created a pivot table and pivot chart. When i pass in the parameter from excel web acess, the data in the pivot table and chart dosent gets refreshed until i click on "Refresh All Connection" in excel web access webpart. Is it possible to refresh the pivot table automatically wheh i pass in the parameters from excel web access webpart?


    mallikarjun

    Drag and drop field from db into HTML table cell possible?

      

    Hi

    I'm used to Dreamweaver where this is possible. I find it hard to believe that I would need to use a 1 column gridView control.

    Is there a way to drag one field from the db Explorer window onto my page and NOT get a grid view?

    Or alternatively, I used to be able to put something like this in ASP classic (years ago!) <%= Eval("User") %>

    Any help would be SO appreciated.

    Thanks



    Column contraints forign key on another table

      

    say I have a table A

    TableA

    int IdA, int IdB

    and Table B

    int IdB, int SpecialCode.

    Can I put a constraint on Table A so, you can ONLY insert IdA,IdB, if the row in TableB.IdB has a SpecialCode=1?


    dan

    How do you Drop Table in C# code behind?

      

    I have a stored procedure that builds a table.  I need to delete the table before running it or I get an object already exists error.  How do you code Drop Table in C# code behind.  I have am calling the SP with execute non-query statement.

    Thank you 


    Categories: 
    ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
    Sql Server  SharePoint  Silverlight  Others  All   

    Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend