Glossary of SQL Server 7 - Misc 2

Start Studying! Add Cards ↓

third normal form
eliminate dependencies between non-key columns in a table
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.
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')
1. WITH NOCHECK option
2. WITH CHECK option
disables or enables constraint and foreign key checks when altering a table.
after every statement @@ERROR is set to indicate success or failure. 0 indicates success
3 values
0 - fetch successful
-1 - fetch failed(bof or eof)
-2 - fetch record deleted
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

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

Add Cards

You must Login or Register to add cards