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

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

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'

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 (http://blog.SQLAuthority.com)

About these ads

9 thoughts on “SQL SERVER – 2008 – Interview Questions and Answers – Part 5

  1. 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)

    • 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

  2. 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

  3. Pingback: SQL SERVER - 2008 - Interview Questions and Answers Complete List Download Journey to SQL Authority with Pinal Dave

  4. Pingback: SQL SERVER – Interview Questions & Answers Needs Your Help Journey to SQL Authority with Pinal Dave

  5. 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.

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

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #047 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s