Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/101

Click to flip

101 Cards in this Set

  • Front
  • Back
What is the main administrative interface SQL Server?
Enterprise Manager
What GUI Interface provides a method to write and test code in written Transact-SQL?
Query Analyzer
What GUI interface provides a method of importing and exporting data?
Data Transformation Services
What GUI interface provides a method to Stop, Start, and Pause the srvices associated with SQL Server?
Service Manager
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?
SQL Servere Profiler
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?
Per Processor
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?
Unattended installation
What is the preferred authentication method for SQL Server?
Windows Authentication
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?
Connectivity only
What are SQL Server's 2 default network protocols?
Named Pipes and TCP/IP
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?
C. OLTP
What is the name of the command prompt tool that can be used to execute SQL code?
OSQL
What Edition of SQL Server 2000 would your business most likely run if you worked for a medium-sized business?
Standard
Name all four of the System Databases that are installed in SQL Server 2000
MSDB, Master, Model and TempDB
What Service governs the distribution of tasks in a clustered server environment?
MSDTC
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?
Pause the SQL Servere Service
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?
No, it's not.
Your transaction log has recorded the execution of an import of 270,000 records as one log record. What Recovery Model are you using?
Bulk-logged
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?
A - O
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?
Change the default primary data file size in the Model database.
What colums does SELECT * FROM [table} actually select from the table?
All of them
What will happen if you run the code CREATE DATABASE MYDATABASE if the database named MYDATABASE exists?
It will fail
What will happen if you attempt to use the DROP DATABASE SALES if a user on your network is connected to it?
The drop will fail
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?
Megabytes
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?
sysobjects
What is the database term for a value that is blank or nonexistent?
NULL
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?
A view
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?
A nonclustered index
Can two databases share a primary data file?
NO
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?
System Stored Procedures
What types of index is implemented as the physical ordering of the records on the hard drive?
Clustered Index
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?
Clustered Servers
What structures on the hard drive (not what types of files) does SQL Server use to allocate space?
Extents and Pages
Name the three recovery models
Full, Simple and bulk-logged
There are 7 editions of SQL Server 2000. Name 4 of them
Enterprise, Standard, Personal, CE, Developer, Desktop Engine, and Enterprise evaluation
What Server Role allows a user to manage server?
A. Serveradmin
B. Security admin,
C. Processadmin
B. Security admin
What comes first, adding a user or an instance to a database?
Instance
What server role allows a user to execute bulk insert statements?
A. DBcreator
B. Diskadmin
C. Bulkadmin?
C. Bulkadmin
What database role allows a user to deny other users the ability to update tables?
A. DDladmin
B. Denydatareader
C. Denydatawriter
C. DB-denydatawriter
What does it mean if a user has both a red X and a green check in their SELECT permission on a table?
The user can select from some columns in that table, but not all of them.
How many Recovery Models are there?
Three
How many of the recovery models can always be used to reover a database to the point of failure?
One - Full Recovery Model
is it possible to change recovery models on an existing database?
Yes
Which recovery model requires the most writing to the transaction log?
Full
Which recovery model logs the running of a DTS package as one record?
Bulk-logged
Which GUI interace is used to write code?
Query Analyzer
Which GUI interface is used to guide a user through the process of creating a DTS package?
Import/Export Data/ DTS Wizard
Which GUI interface is used to look up answers when you don't know how to do something?
BOL
– Which GUI interface is used to configure the network protocols that the server uses to communicate?
Server Network Utility
Which GUI interface is used to track performance statistics like RAM and processor usage?
Profiler
Are queries written in QA case-sensitive?
NO
– What licensing mode is best to use for one server with one processor with 10 users
Server + CAL with 10 client Access Licenses
How (specifically) would you go about changing a user’s default database?
Either use sp_defaultdb or EM, in the users properties under security for the instance
Explain the difference between clustered and nonclusterted indexes
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.
– Explain how differential change maps work
Pages are marked as being changed since the last full databasebackup and only changed pages are backed up in differential backups
What datatype would you use to contain whole numbers whose values are always between -6 and positive 87?
Smallint
What datatype would you use for numeric data where the number of digits after the decimal point is not always the same?
Float
What is the primary consideration when trying to choose between char and varchar for a field?
Whether the data in the column is always the same length or not.
– Name three datatypes for which you do not have to specify a data length
Anything but char, varchar, & real/numeric
Approximately what money value is the cutoff point when deciding whether to use the smallmoney or money datatype
$214,000
What system database is responsible for maintaining data about all other databases
Master
What system database is responsible for keeping scheduled jobs?
MSDB
Other than the SQL Server service, what service must be running for an automated job to succeed?
SQL Server Agent Service
What service must be running for clustered servers to receive and process incoming transacations as if they were one server?
MSDTC
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?
Change the primary file growth increment to 30 MB in the model database
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.
Comment out what you don’t want to execute, or highlight what you do want to execute
Your resulthistory table contains 6,200 records. You execute the SQL code DELETE resulthistory. How many transaction log records are created?
6,200
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%’
Three, Maryellen, Dave, and Damon. First letter can be anything, second letter must be A, number of letters after the A does not matter
– What T-SQL keyword is used to show unique values?
DISTINCT
What SQL keyword is used to change an existing view?
Alter
What is the file extension of a transaction log
.ldf
What is an installation of SQL server called?
Instance
Is it possible to have 2 unnamed instances on one server at the same time?
NO
What is the preferred authentication method?
Windows Authentication
What is the primary difference between installations of client tools only and connectivity only?
Client tools only installs the GUI interfaces and connectivity only doesn’t
Is it possible to have a database’s primary data file and transaction log file on different partitions
Yes
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?
YES
What will happen if you use DTS to import a NULL value into a column whose “nullability” setting is unchecked?
The entire import will fail
What is the earliest date that can successfully be stored in a smalldatetime data type?
January 1, 1900
What does MSDTC stand for?
Microsoft Distributed Transaction Coordinator
When do you use the HAVING clause?
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
When do you use the WHERE clause?
When you want to specify a certain value in a column
What is a stored procedure?
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.
What is the file extension for the Secondary Data file?
.ndf
What is the file extension for the Primary Data fie?
.MDF
How is pausing the SQL Seerver Service different from stopping it?
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.
What are the command prompt tools in SQL?
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)
What does the Master Database do?
Stores data about all the databases in the instance
What is the Model Database used for?
Serves as a template to create new databases
What does the MSDB do?
Stores data about all the databases in the instance
What does the TempDB hold?
Temporary tables being used in queries
What can you not do with any of the command-line utilities?
Schedule backups
What datatype should be used for a field that will contain whole numbers ranging between 0 and 400?
Tinyint
What does Small Int mean?
whole numbers between -32,000 and + 32,000
What does Int mean?
-2.1 billion to + 2.1 billion
Char
Can contain letters or numbers (use if all the data is the same
VarChar
Can contain letters or numbers but you have to specify the maximum length
DataTime
1753-9999 Holds values as little as 3 milliseconds. Can tell you within 3 milliseconds when I withdrew or overdrew my bank account
SmallDateTime
Date has to be between 1-1-1900 and 12-31-2079
Small Money
Values between -$214,000 and +$214,000
Money datatype
Can handle values between -$922,000,000,000 and +$922,000,000,000