Unit 1 Vocabulary

Question 1

Model Database

CE

Answer 1

Serves as a template for any other database, a required database. Any future database created has to be at least as large as the model

Question 2

tempdb database

CE

Answer 2

One of the key working areas for SQL Server, storage for temp tables.

Question 3

Transaction Log

CE

Answer 3

Place where initial changes to a database are written in serial order. The log accumulates changes that are deemed as having been committed.

Question 4

Checkpoint

CE

Answer 4

The point in time that all changes in the log are propagated to the actual database files and the log is cleared.

Question 5

Actual database file
Or
Physical database file

CE

Answer 5

After checkpoint is issued changes from the log are propagated here.

Question 5

Index

CE

Answer 5

An object that exists only within the framework of a particular table or view. An index works much like the index does in the back of a book.

Question 6

Cluster Index

CE

Answer 6

An object that exists only within the framework of a table (database object). Triggers are pieces of code that are executed when certain things happen to the table (database object) that they are associated with. The things that can trigger triggers to fire (execute) are inserts, updates, or deletes.

Question 6

Trigger

CE

Answer 6

An object that exists only within the framework of a table (database object). Triggers are pieces of code that are executed when certain things happen to the table (database object) that they are associated with. The things that can trigger triggers to fire (execute) are inserts, updates, or deletes.

Question 7

Secondary files

CE

Answer 7

32,000 files that can be added to the primary filegroup. Secondary files are optional, are user defined, and store user data. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. Additionally, if a database exceeds the maximum size for single Windows file, you can use secondary data files so the database can continue to grow.

The recommended file name extension for secondary data files is .ndf

Question 8

View

CE

Answer 8

Sometimes called a virtual table. It is basically a stored query (a preplanned mapping and representation of the data stored in tables.

Question 9

Stored Procedure

CE

Answer 9

An ordered series of transact- SQL statements bundled into a single logical unit. They allow for variables and parameters as well as selection and looping constructs. They offer several advantages over just sending individual statements to the server (listed on page 11)

Question 10

User defined functions

CE

Answer 10

Similar to stored procedures except that it can return a value and cannot have “side effects”. Can return a value of most SQL SERVER data types.

Question 11

User

CE

Answer 11

Equivalent of logins; object represents an identifier for someone to login into the SQL server.

Question 12

Role

CE

Answer 12

Users belonging to a group that are granted rights to perform certain actions in SQL server. A user can belong to one or more roles.

Question 13

Constraint

CE

Answer 13

Restriction information about what can go into a table.

Question 15

[ ] (square brackets)

CE

Answer 15

Are used for any names that are the same as SQL Server keywords or contain embedded spaces.

Question 15

Query analyzer

CE

Answer 15

A tool that existed in SQL Server 2000. It provided a user interface in which T-SQL statements could be written and executed.

Question 17

Management Studio

CE

Answer 17

A tool introduced with SQL Server 2005 that replaced query analyzer and enterprise manager. It is where you can execute T-SQL statements and perform numerous database management tasks.

Question 19

Query Optimizer

CE

Answer 19

Part of SQL Server that figures out the best way to run a query to balance fast results with minimum impact to other users.