SQL SERVER – 2008 – Interview Questions and Answers – Part 5

Click here to get free chapters (PDF) in the mailbox

SQL SERVER – 2008 – Interview Questions and Answers Complete List Download

What command do we use to rename a db, a table and a column?

To rename db

sp_renamedb 'oldname' , 'newname'

If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.

E.g.

USE master;

GO

EXEC sp_dboption AdventureWorks, 'Single User', True

GO

EXEC sp_renamedb 'AdventureWorks', 'AdventureWorks_New'

Solarwinds

GO

EXEC sp_dboption AdventureWorks, 'Single User', False

GO

To rename Table

We can change the table name using sp_rename as follows,

sp_rename 'oldTableName' 'newTableName'

E.g.

sp_RENAME 'Table_First', 'Table_Last'
GO

To rename Column

The script for renaming any column :

sp_rename 'TableName.[OldcolumnName]', 'NewColumnName', 'Column'
E.g.
sp_RENAME 'Table_First.Name', 'NameChange' , 'COLUMN'
GO

What are sp_configure commands and set commands?

Use sp_configure to display or change server-level settings. To change database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.

E.g.

sp_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO
sp_CONFIGURE
GO

You can run following command and check advance global configuration settings.

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE
GO

(Read More Here)

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.

Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.

© Copyright 2000-2009 Pinal Dave. All Rights Reserved. SQLAuthority.com

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – 2008 – Interview Questions and Answers – Part 4
Next Post
SQL SERVER – Interview Questions and Answers – Part 6

Related Posts

9 Comments. Leave new

  • Hi,

    In the above example for renaming a database, the last statement must be set to the new database “AdventureWorks_New” otherwise it’ll throw an error saying that “AdventureWorks” does not exist (because it has already been renamed)

    Reply
    • Dont bring your unwanted logic here, he’s just explaining separate examples, show little sense to understand that. Hes not doing a continuation process, jsut saying different examples

      Reply
  • Hi Pinal,

    You have done a great contribution to on SQL Server with you knowledge. I would like to thank you and hope more people contribute their knowledge.

    Thanks,
    parag

    Reply
  • Bharath vengadesan
    March 26, 2009 7:47 pm

    Hi Pinal,

    Your blog is fantastic one.

    And it is very useful.

    Regards,
    Bharath

    Reply
  • Hi Pinal,
    really ur blcs are very usefull..
    one small suggestion, can u give one or more examples for the topics u have discussed here..
    so that we can understand more clearly.

    Reply
  • assortmentofsites
    January 6, 2013 12:12 pm

    @seema – n_dilnaz is right, the database name has been changed, therefore the last statement should use the new database name

    Reply

Leave a Reply

Menu