SQL Server 2000 Interview Questions & Answers

Question 1. What Is The Definition For Sql Server 2000?

Answer :

Microsoft SQL Server is a relational model database server produced by Microsoft. Its primary query languages are T-SQL and ANSI SQL.

Question 2. What Is The Maximum Row Of A Size?

Answer :

8060 bytes.

Question 3. How Many Databases Instances Are There In Sql Server 2000?

Answer :

32,767.

Question 4. How Many Instances Per Computer Are There In Sql Server 2000?

Answer :

16.

Question 5. What Are The Default System Databases In Sql Server 2000?

Answer :

The default system databases in sql server 2000 are:

• Master
• Model
• Tempdb
• Msdb.

Question 6. How Many Database Files Are There In Sql Server 2000?what Are They?

Answer :

There are 3 database files in sql server 2000.
They are: 

• Primary file
• Secondary file
• Log file.

Question 7. What Are The Recovery Models In Sql Server 2000?

Answer :

The recovery models in sql server 2000 are:

• Simple recovery model
• Full recovery model
• Bulk-Logged recovery model.

Question 8. What Is The Command To Change The Recovery Model?

Answer :

Alter database [Database name] set recovery full
Go.

Question 9. What Is The Sql Server 2000 Version Number?

Answer :

8.0.

Question 10. Does Sql Server 2000 Clustering Support Load Balancing?

Answer :

SQL Server 2000 clustering does not provide load balancing; it provides failover support. To achieve load balancing, you need software that balances the load between clusters, not between servers within a cluster.

Question 11. Does Sql Server 2000 Full-text Search Support Clustering?

Answer :

Yes.

Question 12. Can I Run Multiple Instances Of Sql Server 2000 At The Same Time On One Computer?

Answer :

Yes.

Question 13. What Is A Collation?

Answer :

A collation specifies the bit patterns that represent each character and the rules by which characters are stored and compared.

Question 14. What Are The Different Types Of Replication Are There In Sql Server 2000?

Answer :

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.
Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.
Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected.

Question 15. What Command Do We Use To Rename A Database?

Answer :

sp_renamedb ‘oldname’, ‘newname’.

Question 16. What Is Logshipping And Its Purpose?

Answer :

In logshipping the transactional log file from one server is automatically updated in backup database on the other server and in the case when one server fails the other server will have the same DB and we can use this as the DDR (disaster recovery) plan.

Question 17. What Is 'write-ahead Log' In Sql Server 2000 ?

Answer :

Once data is stores in memory user can make changes to that a log file is generated. This log file is generated in every five minutes of transaction is done. After this sql server writes changes to database with the help of transaction log files. This is called Write-ahead log.

Question 18. What Is Extent? Types Of Extents?

Answer :

Extent is a collection of 8 sequential pages.
There are 2 types of extents.

• Uniform extent :- Uniform means when extent is own by a single object means all collection of 8 pages hold by a single extend is called uniform.
• Mixed extent :- Mixed means when more than one object is comes in extents is known as mixed extents.

Question 19. What Is Default Port Number For Sql Server 2000?

Answer :

1433.

Question 20. What Is Sql Server 2000 Work Load Governor?

Answer :

Workload governor limits the performance of SQL SERVER Desktop engine (MSDE). Workload governor sits between the client and the database engine and counts the number of connections per database instance. If Workload governor finds that the number of connections exceeds eight connections, it starts stalling the connections and slowing down the database engine.

Question 21. What Is A Transactions?

Answer :

A transaction is a logical unit of work in which, all the steps must be performed or none.

Question 22. What Is Lock Escalation?

Answer :

Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks).Every lock is a memory structure too many locks would mean, more memory being occupied by locks.

Question 23. What Is A Raid And What Are Different Types Of Raid Configurations?

Answer :

RAID stands for redundant array of inexpensive disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance.

Question 24. How To Determine The Service Pack Currently Installed On Sql Server?

Answer :

To determine the service pack that installed on your sql server ,connect to your server ,execute the command:

Select @@version
Go.

Question 25. What Is Acid Properties?

Answer :

ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.

• Atomicity is an all-or-none proposition.
• Consistency guarantees that a transaction never leaves your database in a half-finished state.
• Isolation keeps transactions separated from each other until they’re finished.
• Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

Question 26. Is The Log File Is A Part Of File Group?

Answer :

No, Log file is a never part of file group.

Question 27. What Is A File Group?

Answer :

File group is a group of files logically grouped together.

There are two types of filegroups:

• Primary :- The primary file group contains the primary data file and any other files not specifically assigned to another file group. All pages for the system tables are allocated in the primary file group.
• User-defined :- User-defined file groups are any file groups specified using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.

Question 28. What Is Check-point?

Answer :

Check point writes all dirty pages for the current database to disk.

Question 29. What Is Dirty Page?

Answer :

Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to the disk.

Question 30. What Is Awe?

Answer :

Address Windowing Extensions API is commonly known as AWE. AWE is used by SQL Server when it has to support very large amounts of physical memory. AWE feature is only available in SQL Server Enterprise, Standard, and Developer editions with of SQL Server 32 bit version.

Question 31. Are There Any Preferred Steps That Need To Be Taken Care Of Before Starting The Installation Of Sql Server 2000?

Answer :

Yes, For a successful installation, follow these steps:

1. Log on to the computer using a windows user account which is part of the local administrator’s user group.
2. Close all applications and stop the services that use ODBC.
3. Close event viewer and registry editor.

Question 32. What Are The Different Editions Available In Sql Server 2000?

Answer :

SQL Server 2000 is available in seven different editions. They are:

• Enterprise Edition
• Standard Edition
• Personal Edition
• Developer Edition
• Microsoft Windows CE edition
• Enterprise Evaluation Edition
• Desktop Engine.

Question 33. How Do I Find Information About The Install Locations For The Various Instances Running On A Computer?

Answer :

You can find out the install paths from the registry key corresponding to each instance. The following registry key contains the required information:

HKEY_LOCAL_MACHINE /Software /Microsoft SQL Server /{Instance Name} /MSSQLServer /Setup /SQLpath.

Question 34. How Do I Determine How Many Instances Of Sql Server Are Installed On A Computer?

Answer :

The names of all SQL Server instances on a computer can be found from the Installed Instances value which is located under the following registry key:

HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server.

Question 35. How Do I Install Only The Client Tools Of Sql Server 2000?

Answer :

There are two ways to install the Client tools:

• Use the Installation Definition screen to select the components to include in this installation of SQL Server 2000. If you select Client Tools Only or Connectivity Only, setup proceeds and no additional choices are required, unless you select components when installing client tools.
• Use the Custom Setup option, and in the Select Components screen, you can choose to install individual subcomponents of Client Management Tools.

Question 36. How Do I Perform An Unattended Install Of Sql Server 2000?

Answer :

If you are planning to install identical installations of SQL Server 2000 on several computers, or if you need to install SQL Server 2000 on computers that you cannot administer remotely, you can use the unattended install option. The most important component of an unattended install is the setup initialization file. The setup initialization file can be obtained in any of the following ways:

• The SQL Server 2000 CD contains several sample .iss files (Sqlins.iss, Sqlcli.iss, Sqlcst.iss) for the different types of installations.
• Each time that you perform an interactive SQL Server 2000 setup, it records all of the selections you make and saves them in the Setup.iss file found in the system root folder.
• You can use the Record Unattended .ISS file option in the Advanced Options screen to create a completely customized Setup.iss file.

After you have the .iss file, you can perform the unattended install by starting the Setupsql.exe program with the appropriate parameters.

Question 37. When I Run The Sql Server 2000 Setup, It Just Hangs. What Do I Do?

Answer :

In this situation, minimize the Setup window (and other windows if necessary) to see whether there are any message boxes relating to File Copy or File Sharing. You must respond to any such messages before Setup can proceed.