SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 10 of 31

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

Please read the Introductory Post before continue reading interview question and answers.

List of all the Interview Questions and Answers Series blogs

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 mode using sp_dboptions. Use sp_renamedb to rename the database. Use sp_dboptions to bring the 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'

Solarwinds

e.g.

sp_RENAME 'Table_First', 'Table_Last'
GO

To rename Column

The script for renaming any column is as follows:

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 the 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 the following command and check the advanced 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 Difference between Commit and Rollback when Used in Transactions?

The usual structure of the TRANSACTION is as follows:

BEGIN TRANSACTION

Operations

COMMIT TRANSACTION or ROLLBACK TRANSACTION

When Commit is executed, every statement between BEGIN and COMMIT becomes persistent to database. When Rollback is executed, every statement between BEGIN and ROLLBACK are reverted to the state when BEGIN was executed.

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 it 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. Within the Query Analyzer, there is an option called “Show Execution Plan” (in the Query drop-down menu). If this option is turned on, it will display query execution plan in a separate window when the query is ran again.

List of all the Interview Questions and Answers Series blogs

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

Solarwinds
Previous Post
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 9 of 31
Next Post
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 11 of 31

Related Posts

No results found

6 Comments. Leave new

  • VreauUltimulLoc
    July 12, 2011 12:21 pm

    EXEC sp_dboption AdventureWorks, ‘Single User’, False

    should be

    EXEC sp_dboption AdventureWorks_New, ‘Single User’, False

    Reply
  • Hi,

    Please tell me what does it means?

    EXEC sp_dboption AdventureWorks, ‘Single User’, True

    Please explain ‘Single User’ and True

    Reply
    • Only one user can connect to the server – that is only you will have access to it

      Reply
      • what if there are multiple users connected to the server? do they get disconnected when ‘Single User’ is turned to True?

      • Yes, they get disconnected when it is in single user mode

    • When we set the word ”True” after single_user it means only one user can access the database and when it is set to false, the particular database is in multi_user mode i.e everyone can access that database.

      Reply

Leave a Reply

Menu