Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
33 Cards in this Set
- Front
- Back
|
third normal form
|
eliminate dependencies between non-key columns in a table
|
|
|
denormalization
|
breaking from the normal forms to help performance
|
|
|
3 things about optimistic concurrency
|
1. may result in loss of concurrency because multiple users can edit data at the same time
2. results in higher concurency than other locking systems. 3. requires timestamps |
|
|
object_id function
|
gets the database id of a table or object
|
|
|
what is the openrowset function?
|
returns data from a remote data source other than a linked server-- includes a connect string
|
|
|
what is a filegroup? What is the primary file group?
|
a collection of one or more data files. the primary file group contains the primary data file. the primary file group is the default filegroup.
info written to a file group is spread between the files in the group proportionally to the free space in the files. |
|
|
@@IDENTITY
|
returns the last identity value used
|
|
|
what is the open query function?
|
allows passthrough queries to be executed on a linked server. Good for picky database formats with abnormal syntax.
format: openquery(server, 'query') |
|
|
ALTER TABLE--
1. WITH NOCHECK option 2. WITH CHECK option |
disables or enables constraint and foreign key checks when altering a table.
|
|
|
@@ERROR
|
after every statement @@ERROR is set to indicate success or failure. 0 indicates success
|
|
|
@@FETCH_STATUS
3 values |
0 - fetch successful
-1 - fetch failed(bof or eof) -2 - fetch record deleted |
|
|
@@TRANCOUNT
|
This system function allows you to keep track of nested transactions. BEGIN TRANSACTION increments and COMMIT TRANSACTION decrements. ROLLBACK TRANSACTION sets @@TRANSCOUNT TO 0
|
|
|
stored prcedures in the replication process
|
the keyword FOR REPLICATION must be used in the create proc statement-- the actions of the stored procedure will then be replicated rather than the changed data
|
|
|
inserted and deleted tables
|
these tables can be accessed by triggers. the inserted table holds copies of all the rows that were inserted into the base table. The delete table holds the rows that were delete from the base table.
|
|
|
primary data file
|
contains all of the system tables and other system objects. usually a .mdf extension. usually contains all the user data too.
|
|
|
what is the command to rebuild an index? 3 things about this command
|
DBCC DBREINDEX
1. all indexes on a table can be rebuilt at once 2. an index can be rebuilt with a new fillfactor 3. changes are atomic and are roll back if there is a problem |
|
|
5 things about TRUNCATE TABLE
|
1. Doesnt hit the transaction log so it cant be rolled back.
2. doesnt activate delete trigger 3. cant be used when the table is referenced in a foreign key constraint 4. removes index statistics 5. if there is an identity column it get reseeded. |
|
|
3 update statement restrictions
|
1. new value is subject to null option, rules and constraints.
2. views can only update on table at a time 3. char and varchar get padded according to the ANSI padding option set when the db was created |
|
|
3 raiserror options
|
1. with log -- error gets written to the nt log-- necessary for severity level 19+
2. with no wait -- error sent immediately to client 3. with seterror -- @@ERROR gets set to msg_id or 5000 |
|
|
what is the purpose of the raiserror statement?
1. severity 2. state |
returns a custom error to the user
1. severity = 1 to 25. 1-10 info only, 11-16 generated by user and can be corrected, 17-19 hardware and software errors, 20-25 fatal problems. only admin can call 19+ 2. state-- source of error |
|
|
RAISERROR: format strings
|
special strings including the error string to mark the point at which arguments should be inserted.
|
|
|
how do you set up linked server login maping for distributed queries
|
on the remote server create a standard login and assign permissions to it. on the sending server map a login using sp_addlinkedsrvlogin
|
|
|
what is an ownership chain and how can it be broken? how is it avoided?
|
an object owner can assign permissions to others - the other must ask permission before granting permission to yet other users. If they do so without asking the chain is broken.
Avoid by making neccessary people members of the fixed server role sysadmin or members of the fixed database role db_owner/db_dlladmin and have them specify dbo as owner when creating objects |
|
|
2 methods for sending security info in distributed queries
|
1. security account deligation
2. linked server login mapping |
|
|
how does security acocunt deligation work in distributed queries?
|
1. if in a different domain ther emust be a NT trust relationship.
2. add nt account to sending server 3. add same account to remote server 4. create user account for remote db and assign permissions |
|
|
what is NT disk duplexing
|
Same as disk mirroring but uses 2 hd controller cards
|
|
|
what is a NT cluster
|
sharing hard drive arrays between servers
|
|
|
2 levels of DTS package security
|
1. DTS owner -- user can modify package and run it
2. DTS operator - can run package |
|
|
how do you rebuild the master database
|
run rebuildm.exe
this also rebuilds msdb, model and distribution databases |
|
|
what is raid5
|
spriping with parity-- writes in stripe sets and parity checksums are written across all disks in the set. good for fault tolerance and speed-- can loose one hd.
|
|
|
what is raid1
|
disk mirroring. good fault tolerance but its slow
|
|
|
what is raid0
|
a stripe set-- writes data across multiple hard disks. if one disk fails you loose all data-- good speed, bad fault tolerance
|
|
|
3 upgrading issues
|
1. upgrade wil fial if @@servername returns null
2. stored procedures that modify sys tables will not be converted 3. replication-- upgrade distribution server first. some features may not be available until all servers are upgraded |
