Saturday, November 14, 2009

SQL Server Database Administrator Interview Questions and Answers Series 4

What are the advantages of using Stored Procedures?
1. Stored procedure can reduced network traffic and latency, boosting application performance.
2. Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
3. Stored procedures help promote code reuse.
4. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
5. Stored procedures provide better security to your data.

Which stored procedure will you be running to add a linked server?
sp_addlinkedserver
sp_addlinkedsrvlogin 

What are the OS services that the SQL Server installation adds?
MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac coordinator) 

Can you explain the role of each service?
SQL SERVER - is for running the databases SQL AGENT - is for automation such as Jobs, DB Maintenance, Backups DTC - Is for linking and connecting to other SQL Servers 

What are the authentication modes in SQL Server?
Windows mode and mixed mode (SQL & Windows). 

Where do you think the users names and passwords will be stored in sql server?
They get stored in master db in the sysxlogins table. 

What should we do to copy the tables, schema and views from one SQL Server to another?
We have to write some DTS packages for it. 

How to hide/show system tables and other system objects in SQL Server Enterprise Manager?
Open Enterprise Manager. 
Right click on your server name and choose 'Edit SQL Server Registration properties...' from the popup menu. 
This presents the 'Registered SQL Server Properties' dialog box. In the bottom half of this dialog box, there is a check box against 'Show system databases and system objects'. 
Check it to show system databases/tables or uncheck it to hide system databases/tables. 

How do I rename a database?
First, you must make sure no one is in the database you want to rename.
Second, you must switch the database to single user mode.
Now you can run the system stored procedure sp_renamedb.
Last, if you want the database to be fully functional you must take it our of single user mode.


Here is the code you can run in query analyzer after ensuring no one is in the database you plan to rename:
 

EXEC sp_dboption 'DatabaseName', 'single user', 'True'
EXEC sp_renamedb 'DatabaseName', 'OLD_DatabaseName' 
EXEC sp_dboption 'OLD_databaseName', 'single user', 'False'

How do I start SQL Server in single user mode?
From a command prompt (Start | Run | cmd) change to the directory where SQL Server is installed, and type the following:
sqlservr.exe -c –m 

Please check these interview questions as well. 
[SQL Server Database Administrator Interview Questions and Answers - Part #12] 
Tags: sql server interview questions. sql server 2008 interview questions. sql server 2008 interview questions and answers . sql server interview questions and answers for experienced. sql server interview questions and answers for freshers. sql server interview questions and answers. sql server dba interview questions and answers. sql server dba interview questions. sql server database administration interview questions . sql server 2008 database administration interview questions. sql server 2012 interview question and answers. sql server 2012 FAQ. sql server 2012 dba interview. sql dba 2012 interview question and answers. sql server 2008 r2 interview question and answers. sql 2012. sql 2008 r2. sql 200 r2 interview question and answers. sql server question and answers. sql server developer interview question and answers. sql developer interview question and answers.  sql server interview questions. sql server 2008 interview questions. sql server 2008 interview questions and answers. sql server interview questions and answers for experienced. sql server interview questions and answers for freshers. sql server interview questions and answers. sql server dba interview questions and answers. sql server dba interview questions. sql server database administration interview questions. sql server 2012 database administration interview questions. sql server interview questions for entry level. sql server interview questions for experienced.

0 comments:

Post a Comment