SQL SERVER INTERVIEW QUESTION & ANSWERS
| 21. |
What are all the databases that are installed with SQL Server? Can we delete them all? |
| |
No. SQL Server includes four system databases that cannot be deleted or your database server's stability will be impacted. These system databases are msdb, Model, TempDB, and Master. The remaining two databases that are installed with SQL Server are the Pubs and Northwind databases. These are sample databases that can be deleted without affecting SQL Server. |
| 22. |
Where should we place our transaction log file? Can we use more than one? |
| |
Transaction log activity is sequential and primarily writes. For this reason, you should isolate your transaction log on a separate physical disk to minimize read/write head movement and eliminate contention over data file activity. Additionally, you can place your transaction log on mirrored disks for fault tolerance. You can use more than one transaction log file if you need additional storage, but transaction logs are filled in order, so you cannot use multiple log files for performance gain. |
| 23. |
Do we need to use multiple data files and different disk systems for my database application? |
| |
Not necessarily. Microsoft states that most databases will receive sufficient performance using a single data file and single transaction log file. Large applications or databases with multiple "hot spots," or areas of high read/write activity, can be optimized using multiple disk systems and data file placement to reduce the amount of work required of single disks.
|
| 24. |
What are Fixed Server Roles? |
| |
- bulkadmin - Members can issue the BULK INSERT statement.
- dbcreator - Members can CREATE, ALTER, and DROP databases; RESTORE databases and database logs; extend data
bases; and rename databases.
- diskadmin - Members can run the sp_add ump device and sp_dropdevice stored procedures.
- processadmin - Members can use the KILL statement to break user connections to SQL Server.
- securityadmin - Members have a number of security assignment privileges. Security administrators can grant, revoke, or deny the CREATE DATABASE permission. Security administrators can add and drop SQL logins; grant, revoke, or deny Windows
logins; and add linked server and remote server logins.
- serveradmin - Members can issue SHUTDOWN and RECONFIGURE statements; run the sp_configure, sp_tableoption,
and sp_fulltext_service stored procedures; and free procedure cache buffers.
- setupadmin - Setup administrators can add, drop, and configure linked servers. They also mark a stored procedure as a startup procedure.
- sysadmin - Members of this role can perform any server action. Once a login is a member of this role, it needs no other role membership or permissions to have full access to SQL Server, its databases, and all objects within those databases.
|
| 25. |
What is dbo? |
| |
Every database has at least one user account in it. The user account is named dbo, which stands for database owner. The dbo user account maps to the login of the user who "owns" the database. Normally, the owner is the user account of
the login who created the database in the first place. |
