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'


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)