Please read the Introductory Post before continue reading interview question and answers.
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.
EXEC sp_dboption AdventureWorks, 'Single User', True
EXEC sp_renamedb 'AdventureWorks', 'AdventureWorks_New'
EXEC sp_dboption AdventureWorks, 'Single User', False
To Rename Table
We can change the table name using sp_rename as follows:
sp_rename 'oldTableName' 'newTableName'
sp_RENAME 'Table_First', 'Table_Last'
To rename Column
The script for renaming any column is as follows:
sp_rename 'TableName.[OldcolumnName]', 'NewColumnName', 'Column'
sp_RENAME 'Table_First.Name', 'NameChange' , 'COLUMN'
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.
sp_CONFIGURE 'show advanced', 0
You can run the following command and check the advanced global configuration settings.
sp_CONFIGURE 'show advanced', 1
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:
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.
Reference: Pinal Dave (http://blog.SQLAuthority.com)