Sql Server 2000

Front 1

What is the main administrative interface SQL Server?

Back 1

Enterprise Manager

Front 2

What GUI Interface provides a method to write and test code in written Transact-SQL?

Back 2

Query Analyzer

Front 3

What GUI interface provides a method of importing and exporting data?

Back 3

Data Transformation Services

Front 4

What GUI interface provides a method to Stop, Start, and Pause the srvices associated with SQL Server?

Back 4

Service Manager

Front 5

What GUI Interface allows the database administrator to caputre data about CPU Usage, data related to client connections, and provides a graphic tool to use to track usage statistics?

Back 5

SQL Servere Profiler

Front 6

What licensing mode should be used in a server environment where the database is connected to by tens of thousands of users over the web?

Back 6

Per Processor

Front 7

What is the term used to describe the act of Installing SQK Server on a server that isn't the one you are physically sitting in front of?

Back 7

Unattended installation

Front 8

What is the preferred authentication method for SQL Server?

Back 8

Windows Authentication

Front 9

If a user on your network needed only to run Crystal Reports which fired stored procedures against the database but did not need to use Enterprise Manager or the other Client Tools only, or Server & Client tools?

Back 9

Connectivity only

Front 10

What are SQL Server's 2 default network protocols?

Back 10

Named Pipes and TCP/IP

Front 11

Is the National City bank website's database, in which you can transfer money between different accounts on the web and have the results immediately visible, an example of
A.Decision-support
B. Online Transaction Processing, or
C: Online Analyutical Processing Database?

Back 11

C. OLTP

Front 12

What is the name of the command prompt tool that can be used to execute SQL code?

Back 12

OSQL

Front 13

What Edition of SQL Server 2000 would your business most likely run if you worked for a medium-sized business?

Back 13

Standard

Front 14

Name all four of the System Databases that are installed in SQL Server 2000

Back 14

MSDB, Master, Model and TempDB

Front 15

What Service governs the distribution of tasks in a clustered server environment?

Back 15

MSDTC

Front 16

You want to disallow new user connections but do not want to boot currently-connected client machines off the SQL Server. What service should you manipulate and what should you do to it? Pause or stop it?

Back 16

Pause the SQL Servere Service

Front 17

If you're using Per Processing licensing, is it legal under the licensing agreement to configure SQL Server to use only one processor on a machine that has more than one?

Back 17

No, it's not.

Front 18

Your transaction log has recorded the execution of an import of 270,000 records as one log record. What Recovery Model are you using?

Back 18

Bulk-logged

Front 19

Your server is connected to by thousands of users and you are using per Processor licensing. In the Network Configuration Setting that dictates how many concurrent connections SQL Server should accept, what value should you enter if you want the number of connections to be unlimited?

Back 19

A - O

Front 20

If you want every database you create from now on to have a default primary data file size of 76 MB, what should you do to configure the server so that happens?

Back 20

Change the default primary data file size in the Model database.

Front 21

What colums does SELECT * FROM [table} actually select from the table?

Back 21

All of them

Front 22

What will happen if you run the code CREATE DATABASE MYDATABASE if the database named MYDATABASE exists?

Back 22

It will fail

Front 23

What will happen if you attempt to use the DROP DATABASE SALES if a user on your network is connected to it?

Back 23

The drop will fail

Front 24

If you do not spsecify whether a database's transaction log file should grow in megabytes or as a percentage of its original size, which will SQL Server use by default?

Back 24

Megabytes

Front 25

What is the name of the system table that oontains the names of every object in the instance, the one I use to write code that drops a table in order to recreate it if it already exists?

Back 25

sysobjects

Front 26

What is the database term for a value that is blank or nonexistent?

Back 26

NULL

Front 27

I want a user in the database to select only certain fields from a table. What database object should I create to make this as easy for me as possible?

Back 27

A view

Front 28

A query needs to search my claims table for a certain last name. The records in the table are physically ordered on the disk according to the service date of the claim and that does not relate at all to the names of the clients. What type of database object could I create to allow the query to run faster?

Back 28

A nonclustered index

Front 29

Can two databases share a primary data file?

Back 29

NO

Front 30

What are the Microsoft-written pieces of code called that come with SQL Server 2000 and allows you to administer tasks and gather statistics without having to write code that queries the system tables directly?

Back 30

System Stored Procedures

Front 31

What types of index is implemented as the physical ordering of the records on the hard drive?

Back 31

Clustered Index

Front 32

What is it called when a group of servers process transactions as a single unit, where the task that need to be done are sent to the server that is currently the least busy?

Back 32

Clustered Servers

Front 33

What structures on the hard drive (not what types of files) does SQL Server use to allocate space?

Back 33

Extents and Pages

Front 34

Name the three recovery models

Back 34

Full, Simple and bulk-logged

Front 35

There are 7 editions of SQL Server 2000. Name 4 of them

Back 35

Enterprise, Standard, Personal, CE, Developer, Desktop Engine, and Enterprise evaluation

Front 36

What Server Role allows a user to manage server?
A. Serveradmin
B. Security admin,
C. Processadmin

Back 36

B. Security admin

Front 37

What comes first, adding a user or an instance to a database?

Back 37

Instance

Front 38

What server role allows a user to execute bulk insert statements?
A. DBcreator
B. Diskadmin
C. Bulkadmin?

Back 38

C. Bulkadmin

Front 39

What database role allows a user to deny other users the ability to update tables?
A. DDladmin
B. Denydatareader
C. Denydatawriter

Back 39

C. DB-denydatawriter

Front 40

What does it mean if a user has both a red X and a green check in their SELECT permission on a table?

Back 40

The user can select from some columns in that table, but not all of them.

Front 41

How many Recovery Models are there?

Back 41

Three

Front 42

How many of the recovery models can always be used to reover a database to the point of failure?

Back 42

One - Full Recovery Model

Front 43

is it possible to change recovery models on an existing database?

Back 43

Yes

Front 44

Which recovery model requires the most writing to the transaction log?

Back 44

Full

Front 45

Which recovery model logs the running of a DTS package as one record?

Back 45

Bulk-logged

Front 46

Which GUI interace is used to write code?

Back 46

Query Analyzer

Front 47

Which GUI interface is used to guide a user through the process of creating a DTS package?

Back 47

Import/Export Data/ DTS Wizard

Front 48

Which GUI interface is used to look up answers when you don't know how to do something?

Back 48

BOL

Front 49

– Which GUI interface is used to configure the network protocols that the server uses to communicate?

Back 49

Server Network Utility

Front 50

Which GUI interface is used to track performance statistics like RAM and processor usage?

Back 50

Profiler

Front 51

Are queries written in QA case-sensitive?

Back 51

NO

Front 52

– What licensing mode is best to use for one server with one processor with 10 users

Back 52

Server + CAL with 10 client Access Licenses

Front 53

How (specifically) would you go about changing a user’s default database?

Back 53

Either use sp_defaultdb or EM, in the users properties under security for the instance

Front 54

Explain the difference between clustered and nonclusterted indexes

Back 54

Clustered indexes physically sort the data by the indexed column. Nonclustered indexes are separate entities that work like indexes in a book, pointing toward the row in the table where a record with a certain value can be found.

Front 55

– Explain how differential change maps work

Back 55

Pages are marked as being changed since the last full databasebackup and only changed pages are backed up in differential backups

Front 56

What datatype would you use to contain whole numbers whose values are always between -6 and positive 87?

Back 56

Smallint

Front 57

What datatype would you use for numeric data where the number of digits after the decimal point is not always the same?

Back 57

Float

Front 58

What is the primary consideration when trying to choose between char and varchar for a field?

Back 58

Whether the data in the column is always the same length or not.

Front 59

– Name three datatypes for which you do not have to specify a data length

Back 59

Anything but char, varchar, & real/numeric

Front 60

Approximately what money value is the cutoff point when deciding whether to use the smallmoney or money datatype

Back 60

$214,000

Front 61

What system database is responsible for maintaining data about all other databases

Back 61

Master

Front 62

What system database is responsible for keeping scheduled jobs?

Back 62

MSDB

Front 63

Other than the SQL Server service, what service must be running for an automated job to succeed?

Back 63

SQL Server Agent Service

Front 64

What service must be running for clustered servers to receive and process incoming transacations as if they were one server?

Back 64

MSDTC

Front 65

You want all new databases you create to have a primary data file growth increment of 30 MB, which is not SQL Server 2000’s default. How do you make this change?

Back 65

Change the primary file growth increment to 30 MB in the model database

Front 66

You have a long SQL script with many queries. Name two ways of getting part of your code to execute without executing all of it.

Back 66

Comment out what you don’t want to execute, or highlight what you do want to execute

Front 67

Your resulthistory table contains 6,200 records. You execute the SQL code DELETE resulthistory. How many transaction log records are created?

Back 67

6,200

Front 68

How many students in this room would have their first name appear in a select list that contained the following code? WHERE FNAME LIKE ‘_A%’

Back 68

Three, Maryellen, Dave, and Damon. First letter can be anything, second letter must be A, number of letters after the A does not matter

Front 69

– What T-SQL keyword is used to show unique values?

Back 69

DISTINCT

Front 70

What SQL keyword is used to change an existing view?

Back 70

Alter

Front 71

What is the file extension of a transaction log

Back 71

.ldf

Front 72

What is an installation of SQL server called?

Back 72

Instance

Front 73

Is it possible to have 2 unnamed instances on one server at the same time?

Back 73

NO

Front 74

What is the preferred authentication method?

Back 74

Windows Authentication

Front 75

What is the primary difference between installations of client tools only and connectivity only?

Back 75

Client tools only installs the GUI interfaces and connectivity only doesn’t

Front 76

Is it possible to have a database’s primary data file and transaction log file on different partitions

Back 76

Yes

Front 77

Is it possible to give a user permissions to execute a stored procedure without their having permission to select from the table that the procedure is based on?

Back 77

YES

Front 78

What will happen if you use DTS to import a NULL value into a column whose “nullability” setting is unchecked?

Back 78

The entire import will fail

Front 79

What is the earliest date that can successfully be stored in a smalldatetime data type?

Back 79

January 1, 1900

Front 80

What does MSDTC stand for?

Back 80

Microsoft Distributed Transaction Coordinator

Front 81

When do you use the HAVING clause?

Back 81

If you want to specify records only appear in the result list if they have some certain aggregate value? and it is used in combination with our GROUP BY clause

Front 82

When do you use the WHERE clause?

Back 82

When you want to specify a certain value in a column

Front 83

What is a stored procedure?

Back 83

A query tht you have given a name and created as an object in the SQL Server software, that you can then assign permissions to just as you would the ability to select data from a table or a view.

Front 84

What is the file extension for the Secondary Data file?

Back 84

.ndf

Front 85

What is the file extension for the Primary Data fie?

Back 85

.MDF

Front 86

How is pausing the SQL Seerver Service different from stopping it?

Back 86

Pausing the service allows current connections to continue to exist and disallows incoming connections; while stopping the service terminates everything immediately except for stored procedures and queries that are currently running.

Front 87

What are the command prompt tools in SQL?

Back 87

OSQL
SCM-Service Manager
BCP-like DTS-brings data in and takes data out-bulk process is faster but syntax is hard
DTSRUN-runs existing DTS packaging(Handy when DTS is not available for some reason)

Front 88

What does the Master Database do?

Back 88

Stores data about all the databases in the instance

Front 89

What is the Model Database used for?

Back 89

Serves as a template to create new databases

Front 90

What does the MSDB do?

Back 90

Stores data about all the databases in the instance

Front 91

What does the TempDB hold?

Back 91

Temporary tables being used in queries

Front 92

What can you not do with any of the command-line utilities?

Back 92

Schedule backups

Front 93

What datatype should be used for a field that will contain whole numbers ranging between 0 and 400?

Back 93

Tinyint

Front 94

What does Small Int mean?

Back 94

whole numbers between -32,000 and + 32,000

Front 95

What does Int mean?

Back 95

-2.1 billion to + 2.1 billion

Front 96

Char

Back 96

Can contain letters or numbers (use if all the data is the same

Front 97

VarChar

Back 97

Can contain letters or numbers but you have to specify the maximum length

Front 98

DataTime

Back 98

1753-9999 Holds values as little as 3 milliseconds. Can tell you within 3 milliseconds when I withdrew or overdrew my bank account

Front 99

SmallDateTime

Back 99

Date has to be between 1-1-1900 and 12-31-2079

Front 100

Small Money

Back 100

Values between -$214,000 and +$214,000

Front 101

Money datatype

Back 101

Can handle values between -$922,000,000,000 and +$922,000,000,000