Post reply

What's the reason?

  • June 23, 2016 at 4:54 am

    #327391

    Hi Friends,

    I had a question during my interview, as saying that What could be the reason when I am trying to drop all these tables , views, functions, SP's ...that few are dropped and few are not?

    Tables : Table1, Table 2 , Table 3

    View : View1, View2, View3

    SP : SP1, SP2 , SP3

    Function : Fn1, Fn2, Fn3

    Just curious to know.

    Any suggestions would be appreciated. Thanks in advance.

    Thanks,
    Charmer

  • June 23, 2016 at 5:05 am

    #1886477

    Can you explain little more, the question is unclear.

  • June 23, 2016 at 5:18 am

    #1886480

    Foreign keys between tables and SCHEMABINDING option in view definitions are two reasons why tables and views might not be able to be dropped. Alternatively, perhaps you don't have permissions on all objects. What answer did you give, as a matter of interest?

    John

  • June 23, 2016 at 7:13 am

    #1886520

    I told him in the first place that we don't have permission to drop.

    The guy had told me clearly that we have permission to drop too.

    Then the answer I gave was the foreign key issue for why few tables got dropped and few not...

    but for functions , views and SP..I told no idea..

    And he replied, could that be the only reason for few tables got dropped and few not?

    I told that's all I knew.

    Thanks,
    Charmer

  • June 23, 2016 at 7:27 am

    #1886526

    Few tables couldn't be dropped because of SCHEMABINDING in dependent views and functions etc.

  • June 23, 2016 at 7:48 am

    #1886533

    Charmer (6/23/2016)

    I told him in the first place that we don't have permission to drop.

    The guy had told me clearly that we have permission to drop too.

    Then the answer I gave was the foreign key issue for why few tables got dropped and few not...

    but for functions , views and SP..I told no idea..

    And he replied, could that be the only reason for few tables got dropped and few not?

    I told that's all I knew.

    Sounds like a poor question that was very contrived to see if you understand schemabinding. The worst interview questions are the ones where you have a scenario and have to explain it deeper and deeper trying to drive the person to answer a specific word like that.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • June 23, 2016 at 7:54 am

    #1886536

    If the question was as vague as your initial post I would have asked what error messages were we getting when attempting to drop the tables, views, stored procedures, and/or functions. Those error messages would provide the information needed to know why some dropped and some didn't and what was needed to be done to fix the issue.

  • June 23, 2016 at 8:37 am

    #1886556

    Charmer (6/23/2016)

    Hi Friends,

    I had a question during my interview, as saying that What could be the reason when I am trying to drop all these tables , views, functions, SP's ...that few are dropped and few are not?

    Tables : Table1, Table 2 , Table 3

    View : View1, View2, View3

    SP : SP1, SP2 , SP3

    Function : Fn1, Fn2, Fn3

    Just curious to know.

    Any suggestions would be appreciated. Thanks in advance.

    It could be because of depencies between tables or between tables and schema bound stored procedures and views. For this reason, it's important to drop (and create) objects in proper sequence, which requires familiarity with object dependencies, however, in general you want to drop in the order of: stored procedures, views, functions, and then tables. Although it's more common for a view to reference a function, it's entirely possible for a function to reference a view as well, so there is no general sequence that is guaranteed to work.

    It could also be due to lack of permissions, for example if you're not a member of database owner role in this database.

    In both cases above an error will result, informing you of the issue.

    Of course, this situation where some objects are dropped and other are not will also occur while you're still in the process of dropping objects and havn't finished yet. We don't know what the interviewer had in mind specifically, but they're probably thinking about object dependencies, since they provided you with a sequence that generally wouldn't work.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • June 23, 2016 at 11:38 pm

    #1886654

    Like Sean said, May be the guy was expecting the word 'SCHEMABINDING' from me.

    Yes Lynn, I agree with you. But unfortunately I did not ask him what's the error message? My bad.

    Thanks,
    Charmer

  • June 24, 2016 at 8:12 am

    #1886754

    If the interviewer knows what he's talking about, and the whole point is to spark a conversation about how the candidate approaches DDL deployment, then it's a good question. If the interviewer is looking for a specific answer, then it's a bad question.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • June 24, 2016 at 4:30 pm

    #1886864

    Charmer (6/23/2016)

    Hi Friends,

    I had a question during my interview, as saying that What could be the reason when I am trying to drop all these tables , views, functions, SP's ...that few are dropped and few are not?

    Tables : Table1, Table 2 , Table 3

    View : View1, View2, View3

    SP : SP1, SP2 , SP3

    Function : Fn1, Fn2, Fn3

    Just curious to know.

    Any suggestions would be appreciated. Thanks in advance.

    I would think that most of the time would be because of dependencies even if WITH SCHEMABINDING isn't included in the problem. Another reason could be that the object is currently in use and will not drop until usage completes. Depending on several things, the system may just wait in a temporary "blocked" mode for the object to go "out of use" and then drop it.

    The same holds true for certain settings. For example, if xp_CmdShell is being used, setting it to "Disabled" will wait until current usage completes.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 27, 2016 at 2:15 pm

    #1887136

    Or the object(s) could be in a schema that is not the default schema, since the q is written as:

    DROP TABLE table1

    rather than

    DROP TABLE schema_name.table1

    Almost certainly not the answer the asker is looking for, but technically valid all the same.

    There are a lot of possible reasons objects wouldn't drop.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply