Deleteme_Ipad_Sort-List3

Front 1

What is a principal?

Back 1

The means by which you authenticate and are identified within an instance or database.

Front 2

What are the 5 types of logins in SQL Server 2008?

Back 2

Standard SQL Server login

Windows login

Windows group

Certificate

Asymmetric key

Front 3

How does SQL Server map a windows user or group to a Standard SQL Server login?

Back 3

SQL stores the name of the login or group along with the corresponding security identifier (SID); when a user logs in to the instance using windows credentials, SQL makes a call to the windows security application programming interface to validate the account, retrieve the SID and then compare the SID to those stored in the master database.

Front 4

What do SQL Server logins mapped to certificates or asymmetric keys lack?

Back 4

A means to authenticate. Logins mapped to certificates and asymmetric keys are used internally as a security container.

Front 5

What is the syntax to create a login?

Back 5

CREATE LOGIN loginName { WITH <option_list1> / FROM <sources> }

<option_list1> ::=
PASSWORD = { 'password' / hashed_password HASHED } [ MUST_CHANGE ]
[ , <option_list2> [ ,... ] ]

<option_list2> ::=

SID = sid

/ DEFAULT_DATABASE = database
/ DEFAULT_LANGUAGE = language
/ CHECK_EXPIRATION = { ON / OFF}
/ CHECK_POLICY = { ON / OFF}
/ CREDENTIAL = credential_name

<sources> ::=
WINDOWS [ WITH <windows_options> [ ,... ] ]
/ CERTIFICATE certname
/ ASYMMETRIC KEY asym_key_name

<windows_options> ::=
DEFAULT_DATABASE = database
/ DEFAULT_LANGUAGE = language

Front 6

What is the CHECK_POLICY option in creating logins?

Back 6

It is the default option; when enabled, SQL server enforces the windows password policy settings when you create a SQL Server login.

Front 7

What is the CHECK_EXPIRATION option in creating logins?

Back 7

It is used to prevent brute force attacks against a login. When enabled, each time the login is used to authenticate to an instance, SQL Server checks whether the password has expired and change the password if necessary.

Front 8

Why is the sa account a target for brute force attacks.

Back 8

Unlike other accounts, the sa account cannot be locked out due to failed login attempts. You can rename the SA account as an alternative.

Front 9

How can you prevent access by user/users without revoking permissions?

Back 9

You can disable the account:

ALTER LOGIN <loginname> DISABLE

Front 10

What are the instance level roles that ship with SQL; can you change permissions on them?

Back 10

-- bulkadmin - Administer BCP and Bulk Insert operations

-- dbcreator - Create databases

-- diskadmin - Manage disk resources

-- processadmin - Manage connections and start or pause an instance

-- securityadmin - Create, alter, and drop logins, but cannot change passwords

-- serveradmin - Perform the same actions as diskadmin and processadmin, plus manage endpoints, change instance settings, and shut down the instance

-- setupadmin - Manage linked servers

-- sysadmin - Perform any action within the instance. Members cannot be prevented from accessing any object or performing any action.

No, you cannot change permissions on them.

Front 11

How do you grant access to a database?

Back 11

By adding a login to the database as a user by executing the CREATE USER command.

Front 12

What is the syntax for the CREATE USER command?

Back 12

CREATE USER user_name

[ { { FOR / FROM }
{ LOGIN login_name
/ CERTIFICATE cert_name
/ ASYMMETRIC KEY asym_key_name}
/ WITHOUT LOGIN ]
[ WITH DEFAULT_SCHEMA = schema_name ]

Front 13

What is a loginless user?

Back 13

A user in the database that is not associated to a login. Loginless users were added to replace application roles. Users still authenticate to the instance using their own credentials. The user‚ login needs access to the database. After SQL Server changes the user‚ context to the database, the user impersonates the loginless user to gain necessary permissions. Because the user is authenticating to the instance using his or her own credentials, SQL Server can still audit activity to an individual login even though the login is impersonating a loginless user.

Front 14

What are the fixed roles at the database level?

Back 14

-- db_accessadmin - Add or remove users in the database

-- db_backupoperator - Back up the database but cannot restore a database or view any information in the database

-- db_datareader - Issue SELECT against all tables, views, and functions within the database

-- db_datawriter - Issue INSERT, UPDATE, DELETE, and MERGE against all tables within the database. Members of this role must also be members of the db_datareader role.

-- db_ddladmin - Execute data definition language (DDL) statements

-- db_denydatareader - Prevent SELECT against all tables, views, and functions within the database

-- db_denydatawriter - Prevent INSERT, UPDATE, DELETE, and MERGE against all tables within the database

-- db_owner - Owner of the database that has full control over the database and all objects contained within the database

-- db_securityadmin - Manage the membership of roles and associated permissions, but cannot manage membership for the db_owner role

--public - Default group in every database that all users belong to

Front 15

Which logins cannot be used to authenticate to an instance?

Back 15

You cannot use logins that are mapped to a certificate or asymmetric key to authenticate to an instance.

Front 16

What are the only objects directly owned by a database user?

Back 16

schemas

Front 17

If you need to allow a user to view metadata in a database, what command do you execute?

Back 17

GRANT VIEW DEFINITION TO <user>

Front 18

What command can you execute to allow a login to view metadata on any object?

Back 18

GRANT VIEW ANY DEFINITION TO <user>

Front 19

In order for a login to see execution statistics, such as sys.dm_exec_requests, what command do you need to execute?

Back 19

GRANT VIEW SERVER STATE TO <user>

Front 20

How do you accomplish impersonation?

Back 20

{ EXEC / EXECUTE ] AS <context_specification>
<context_specification>::=
{ LOGIN / USER } = 'name'
[ WITH { NO REVERT / COOKIE INTO @varbinary_variable } ]
/ CALLER

Front 21

What type of key is the Service Master Key?

Back 21

It is a symmetric key generated from the local machine key and encrypted using the SQL Server service account by the Windows Data Protection API.

Front 22

What statement is used to create a Database Master Key?

Back 22

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong password'>

Front 23

How is the database master key encrypted?

Back 23

Using Triple DES and the user-supplied password. A copy is also encrypted using the service master key such that automatic decryption can be accomplished within the instance.

Front 24

What is the database master key used to protect?

Back 24

Any certificates, symmetric keys or asymmetric keys that are stored in the db.

Front 25

When you make a request to decrypt data, what happens?

Back 25

The service master key is used to decrypt the database master key, that is used to decrypt a certificate, symmetric key or assymmetric key, and in turn is used to decrypt the data.

Front 26

To restore and be able to decrypt data successfully, you must be able to backup the db master key and then regenerate the db master key on another instance. What commands do you use to perform this?

Back 26

OPEN MASTER KEY

BACKUP MASTER KEY

RESTORE MASTER KEY

CLOSE MASTER KEY

Front 27

What standard are certificates based on?

Back 27

X.509

Front 28

What is the syntax for creating a self-signed certificate in SQL Server?

Back 28

CREATE CERTIFICATE certificate_name [ AUTHORIZATION user_name ]

{ FROM <existing_keys> / <generate_new_keys> }
[ ACTIVE FOR BEGIN_DIALOG = { ON / OFF } ]

<existing_keys> ::=
ASSEMBLY assembly_name / {
[ EXECUTABLE ] FILE = 'path_to_file'
[ WITH PRIVATE KEY ( <private_key_options> ) ] }

<generate_new_keys> ::=
[ ENCRYPTION BY PASSWORD = 'password']
WITH SUBJECT = 'certificate_subject_name'
[ , <date_options> [ ,...n ] ]

<private_key_options> ::=
FILE = 'path_to_private_key'
[ , DECRYPTION BY PASSWORD = 'password' ]
[ , ENCRYPTION BY PASSWORD = 'password' ]

<date_options> ::=
START_DATE = 'mm/dd/yyyy' / EXPIRY_DATE = 'mm/dd/yyyy'

Front 29

What is the purpose of a signature?

Back 29

It allows you to elevate a user's permission but to provide a restriction such that the elevation occurs only when the user is executing a specific piece of code.

Front 30

What can you add a signature to?

Back 30

stored procedures

functions

triggers

assemblies

Front 31

What is the process to sign code digitally?

Back 31

1. Create a database master key.

2. Create a certificate in the database.

3. Create a user mapped to the certificate.

4. Assign permissions on an object or objects to the user.

5. Execute ADD SIGNATURE on a module by the certificate.

Front 32

How are principals, securables, and permissions related?

Back 32

You can GRANT, REVOKE, or DENY permissions ON a securable TO a principal.

Front 33

What is an ownership chain, and how can you have a broken ownership chain?

Back 33

An ownership chain applies to objects that reference other objects within a database. The owner of the schema that contains the object is considered the owner of the object. SQL Server checks permissions for the first object that you access, as well as each time the owner changes within the calling stack. The chain of object owners within a calling stack is called an ownership chain. You have a broken ownership chain when the object owner changes within a calling stack and you have not been granted sufficient permissions to continue accessing objects within the call stack.

Front 34

What principals can you not impersonate?

Back 34

Principals that have been mapped to a certificate or asymmetric key.

Front 35

What are DDL triggers?

Back 35

They allow you to trap and respond to login events.

Front 36

What is the syntax for creating a DDL trigger?

Back 36

CREATE TRIGGER trigger_name

ON { ALL SERVER / DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR / AFTER } { event_type / event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] /
EXTERNAL NAME < method specifier > [ ; ] }

Trigger on a LOGON event (Logon Trigger)

CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR / AFTER } LOGON
AS { sql_statement [ ; ] [ ,...n ] /
EXTERNAL NAME < method specifier > [ ; ] }

Front 37

Can you prevent an ALTER DATABASE with a DDL trigger?

Back 37

No, ALTER DATABASE executes outside the scope of a transaction and cannot be rolled back with a DDL trigger. A DDL trigger can only audit it.

Front 38

How are DDL triggers related to the Policy-based management framework?

Back 38

The Policy-based management framework creates DDL triggers for all policies that you configure to prevent an out of compliance situation.

Front 39

What is the syntax for creating a server audit object?

Back 39

CREATE SERVER AUDIT audit_name

TO { [ FILE (<file_options> [, ...n]) ] /
APPLICATION_LOG / SECURITY_LOG }
[ WITH ( <audit_options> [, ...n] ) ] }[ ; ]

<file_options>::=
{FILEPATH = 'os_file_path'
[, MAXSIZE = { max_size { MB / GB / TB } / UNLIMITED } ]
[, MAX_ROLLOVER_FILES = integer ]
[, RESERVE_DISK_SPACE = { ON / OFF } ] }

<audit_options>::=
{ [ QUEUE_DELAY = integer ]
[, ON_FAILURE = { CONTINUE / SHUTDOWN } ]
[, AUDIT_GUID = uniqueidentifier ]}

Front 40

What does QUEUE_DELAY control for audit objects?

Back 40

Whether messages are sent synchronously or asynchronously; when set to 0, audit records are sent to the audit log synchronously with the transaction.

Front 41

What does the On_FAILURE action control in an audit object?

Back 41

It controls how the instance behaves if audit records cannot be written. The default option is CONTINUE, which allows the instance to continue running and processing transactions. If you specify a value of SHUTDOWN, if the audit record cannot be written to the log within the specified QUEUE_DELAY interval, the instance is shut down.

Front 42

What is C2 auditing?

Back 42

A U.S. Department of Defense audit specification.

Front 43

What can columns be encrypted using?

Back 43

hash

passphrase

symmetric key

asymmetric key

certificate

Symmetric keys provide best balance between performance and securing data. Asymmetric keys and certificates provide strongest encryption.

Front 44

Can an encrypted column be indexed? Searched?

Back 44

No. No.

Front 45

What is an example of a one-way encryption algorithm (can only encrypt, not decrypt)?

Back 45

Hash

Front 46

How does SQL Server handle passwords?

Back 46

SQL Server uses an MD5 hash to handle passwords. When a password is specified for an object, SQL Server applies an MD5 hash and stores the hash value. When you specify a password to access an object, the password is hashed using the same MD5 hash, the hashed password is transmitted in a secure channel, and the hash value transmitted is compared to the hash value stored. Even an administrator who is running a trace cannot access the password.

Front 47

What are the 5 hash algorithms allowed by SQL Server?

Back 47

SHA

SHA1

MD2

MD4

MD5

Front 48

Why is it not a good idea to use a hash algorithm to encrypt birth dates, salaries or credit card numbers?

Back 48

Hash algorithms are vulnerable to brute force attacks. If the range of values that you are seeking to encrypt is small, an attacker can easily generate all the possible hashes for the range of possible values. After generating these hashes, the attacker needs to compare the hash values to find a match and thus reverse-engineer your data.

Front 49

How can you increase the complexity of a hash?

Back 49

By salting - adding a string of one more characters to the value before hashing.

Front 50

What keys utilize a single key for both encryption and decryption?

Back 50

symmetric

Front 51

Which key is generated by a key server and cannot be backed up or moved from one system to another?

Back 51

asymmetric

Front 52

What does Transparent Data Encryption (TDE) provide?

Back 52

TDE is used to encrypt data at rest. The contents of the data and transaction log, along with any backups, are encrypted by the engine. TDE works by using an encryption key stored within the database boot record. The TDE key is encrypted by using a certificate within the master database.

Front 53

What is the process of implementing TDE on a database?

Back 53

1. Create a database master key in the master database.

2. Create a certificate in the master database.

3. Create a database encryption key in the target database using the certificate in the master database.

4. Alter the database and enable encryption.

Front 54

Why should you backup the certificate used for TDE and store the backup in a safe place?

Back 54

After you encrypt it, you cannot access your data without the certificate.

Front 55

What are the items that you can capture data for with System Monitor?

Back 55

Objects, counters and counter instances.

Front 56

What types of data can system monitor capture?

Back 56

Numeric data for performance counters that are defined for hardware or software components.

Front 57

What are the three counters that, by themselves, indicate a system problem?

Back 57

System:Processor Queue Length, Network Interface:Output Queue Length, and Physical Disk:Avg. Disk Queue Length.

Front 58

What is the purpose of the Blank Profiler Trace Template?

Back 58

An empty trace; allows you to create an entire trace from scratch.

Front 59

What is the purpose of the SP_Counts Profiler Trace Template?

Back 59

Captures each stored procedure executed so that you can determine

How many of each procedure is being executed.

Front 60

What is the purpose of the Standard Profiler Trace Template?

Back 60

The most common template to start with; captures stored procedure and ad hoc SQL being executed along with performance statistics for each procedure and batch. Every login and logout is also captured.

Front 61

What is the purpose of the TSQL Profiler Trace Template?

Back 61

Captures a list of all the stored procedures and ad hoc SQL batches that are executed, but does not include any performance statistics.

Front 62

What is the purpose of the TSQL_Duration Profiler Trace Template?

Back 62

Captures the duration of every stored procedure and ad hoc SQL batch that is executed.

Front 63

What is the purpose of the TSQL_Grouped Profiler Trace Template?

Back 63

Captures every login and logout along with the stored procedures and ad hoc SQL batches that are executed. Includes information to identify the application and user executing the request, but does not include any performance data.

Front 64

What is the purpose of the TSQL_Locks Profiler Trace Template?

Back 64

Captures blocking and deadlock information such as blocked processes, deadlock chains, deadlock graphs, lock escalation, and lock timeouts. This template also captures every stored procedure, each command within a stored procedure, and every ad hoc SQL request.

Front 65

What is the purpose of the TSQL_Replay Profiler Trace Template?

Back 65

Captures the stored procedures and ad hoc SQL batches executed against the instance in a format that allows you to replay the trace against a test system. This template is commonly used to perform load and regression tests.

Front 66

What is the purpose of the TSQL_SPs Profiler Trace Template?

Back 66

Captures performance data for all ad hoc SQL batches, stored procedures, and each statement inside a stored procedure. Every login and logout is also captured.

Front 67

What is the purpose of the Tuning Profiler Trace Template?

Back 67

Captures basic performance information for ad hoc SQL batches, stored procedures, and each statement inside a stored procedure.

Front 68

What is the cause of enabling file rollover for a trace file?

Back 68

After the file reaches the upper size limit specified, the file closes and a new file is opened.

Front 69

Why should you never log trace events to the same instance for which you are capturing events?

Back 69

You can possibly overwhelm the server; the best solution is to log trace events to a file and then later import the file into the server for analysis.

Front 70

What is the purpose of the Broker SQL Trace Event Group?

Back 70

13 events for Service Broker messages, queues, and conversations.

Front 71

What is the purpose of the CLR SQL Trace Event Group?

Back 71

1 event for the loading of a Common Language Runtime (CLR) assembly.

Front 72

What is the purpose of the Cursors SQL Trace Event Group?

Back 72

7 events for the creation, access, and disposal of cursors.

Front 73

What is the purpose of the Database SQL Trace Event Group?

Back 73

6 events for data/log file grow/shrink as well as Database Mirroring state changes.

Front 74

What is the purpose of the Database SQL Trace Event Group?

Back 74

2 events to notify when a deprecated feature is used within the instance.

Front 75

What is the purpose of the Errors and Warnings SQL Trace Event Group?

Back 75

16 events for errors, warnings, and information messages being logged. Events to detect suspect pages, blocked processes, and missing column statistics.

Front 76

What is the purpose of the Full Text SQL Trace Event Group?

Back 76

3 events to track the progress of a full text index crawl.

Front 77

What is the purpose of the Locks SQL Trace Event Group?

Back 77

9 events for lock acquisition, escalation, release, and deadlocks.

Front 78

What is the purpose of the OLEDB SQL Trace Event Group?

Back 78

5 events for distributed queries and remote stored procedure calls.

Front 79

What is the purpose of the Objects SQL Trace Event Group?

Back 79

3 events that track when an object is created, altered, or dropped.

Front 80

What is the purpose of the Performance SQL Trace Event Group?

Back 80

14 events that allow you to capture show plans, use of plan guides, and parallelism. This event group also allows you to capture full text queries.

Front 81

What is the purpose of the Progress Report SQL Trace Event Group?

Back 81

1 event for online index creation progress.

Front 82

What is the purpose of the Query Notifications SQL Trace Event Group?

Back 82

4 events to track the parameters, subscriptions, and templates for query notifications.

Front 83

What is the purpose of the Scans SQL Trace Event Group?

Back 83

2 events to track when a table or index is scanned.

Front 84

What is the purpose of the Security Audit SQL Trace Event Group?

Back 84

44 events to track the use of permissions, impersonation, changes to security objects, management actions are taken on objects, start/stop of an instance, and backup/restore of a database.

Front 85

What is the purpose of the Server SQL Trace Event Group?

Back 85

3 events for mounting a tape, change to the server memory, and closing a trace file.

Front 86

What is the purpose of the Sessions SQL Trace Event Group?

Back 86

3 events for existing connections when the trace starts as well as tracking the execution of logon triggers and resource governor classifier functions.

Front 87

What is the purpose of the Stored Procedures SQL Trace Event Group?

Back 87

12 events for the execution of a stored procedure, cache usage, recompilation, and statements within a stored procedure.

Front 88

What is the purpose of the Transactions SQL Trace Event Group?

Back 88

13 events for the begin, save, commit, and rollback of transactions.

Front 89

What is the purpose of the TSQL SQL Trace Event Group?

Back 89

9 events for the execution of ad hoc T-SQL or XQuery calls. Events for an entire SQL batch as well as each statement within a batch.

Front 90

What is the purpose of the User Configurable SQL Trace Event Group?

Back 90

10 events that you can configure with SQL Trace.

Front 91

What event groups are commonly captured to baseline and troubleshoot query performance?

Back 91

Stored Procedures, Performance, and TSQL event groups.

Front 92

What event group is used to define auditing quickly across a variety of security events?

Back 92

The Security Audit event group.

Front 93

What event group is commonly used to troubleshoot concurrency issues?

Back 93

The Locks event group.

Front 94

What events return a significant amount of data and should be used with caution on a very busy instance?

Back 94

Performance / Showplan *

Stored Procedures / SP:StmtCompleted

Stored Procedures / SP:StmtStarting

TSQL / StmtCompleted

TSQL / StmtStarting

Front 95

What is the purpose of the sp_trace_create trace module?

Back 95

A stored procedure that creates a new trace object. Equivalent to the definition on the General tab of the New Trace dialog in Profiler.

Front 96

What is the purpose of the sp_trace_generateevent trace module?

Back 96

A stored procedure that allows you to define your own trace event.

Front 97

What is the purpose of the sp_trace_setevent trace module?

Back 97

A stored procedure that adds a data column for an event to be captured by a trace. You need to call sp_trace_setevent once for each data column being captured for an event. Equivalent to the event and data column selection grid in the Events Selection tab of the New Trace dialog in Profiler.

Front 98

What is the purpose of the sp_trace_setfilter trace module?

Back 98

A stored procedure that adds a filter to a trace. Equivalent to the Edit Filter dialog box in Profiler.

Front 99

What is the purpose of the sp_trace_setstatus trace module?

Back 99

A stored procedure that starts, stops, and closes a trace. A status of 0 stops a trace. A status of 1 starts a trace. A status of 2 closes a trace and removes the trace definition from the instance.

Front 100

What is the purpose of the sp_trace_geteventinfo trace module?

Back 100

A function that returns the events and data columns being captured by a trace.

Front 101

What is the purpose of the sp_trace_getfilterinfo trace module?

Back 101

A function that returns the filters applied to a specified trace.

Front 102

What is the purpose of the sp_trace_getinfo trace module?

Back 102

A function that returns status and property information about all traces defined in the instance.

Front 103

What is the purpose of the sp_trace_gettable trace module?

Back 103

A function that reads one or more trace files and returns the contents as a result set. Commonly used to import trace files into a table.

Front 104

What is required to correlate a counter log trace with a trace file?

Back 104

You need to have captured the StartTime data column in the trace.

Front 105

Which events are commonly used to establish a performance baseline?

Back 105

The RPC:Completed and SQL:BatchCompleted events.

Front 106

What are the four places that error and information messages related to your server running SQL Server can be found?

Back 106

Windows Event Log

SQL Server error log

SQL Server Agent log (sqlagent.out)

Database mail log

Front 107

The current SQL Server error log is called errorlog (no extension) and located where?

Back 107

MSSQL10.<instance>\MSSQL\LOG

Front 108

What stored procedure can you run to extract the contents of the current error log?

Back 108

sys.xp_readerrorlog

Front 109

When the transaction log fills up and any write action attempted rolls back, what is the error logged to the SQL Server error log and Windows Application Event log?

Back 109

9002

Front 110

If a transaction log fills up, what 5 actions can you perform?

Back 110

Back up the transaction log.

Add disk space to the volume that the transaction log file is on.

Move the log to a volume with more space.

Increase the size of the transaction log file.

Add another log file on a disk volume that has space.

Front 111

A transaction log backup might not free up enough space within the log to be reused if any of the following occurs: ?

Back 111

The database is participating in replication and the distribution database is not available.

You have an open transaction.

Database Mirroring is paused.

The mirror database is behind the principal.

A log scan is running.

SQL Server cannot increase the size of a file quickly enough.

Front 112

If a db runs out of disk space, what errors are logged? What action can you take?

Back 112

1101 and 1105. You could add a new file to the appropriate filegroup on a disk volume that has space.

Front 113

Before adding files to a filegroup to resolve errors 1101 and 1105, what should you do?

Back 113

Check to see if the data files have auto-grow disabled. If the disk still has space, you could increase the size of the data files and resolve the issue.

Front 114

If tempdb runs out of space, what error codes are generated?

Back 114

3958, 3959, 3966 and 3967

Front 115

What does a service with a startup mode of Disabled appear as in the Start Mode column in the SQL Server Configuration Manager main window?

Back 115

Other

Front 116

The start mode for all SQL Server services in a clustered installation should be set to what?

Back 116

Manual - they are controlled by the cluster service.

Front 117

What permissions do SQL Server service accounts need?

Back 117

Read and Write access to the folder(s) where the data/log files for system databases are stored.

Read and Write permissions on SQL Server registry keys.

Log On As A Service authority.

Sysadmin authority inside the SQL Server instance.

Front 118

What sequence of events appear in the Windows Application Event viewer log indicate that SQL Server service account does not have sufficient permissions to access the data or log file(s) for either the master or tempdb databases?

Back 118

Service startup

Device activation or file not found error

Service shutdown

Front 119

Errors in what three databases can prevent an instance from starting?

Back 119

Errors in the master, tempdb, and mssqlsystemresource databases can prevent an instance from starting. Errors in all other databases just make the problem database inaccessible until you can fix the problem.

Front 120

When changing the startup parameters for the SQL Server service, what happens if a semicolon, which separates parameters, is followed by a space.

Back 120

It interprets everything after the space as part of the previous parameter. Do not introduce spaces following semicolons.

Front 121

What is the first indication you normally have for memory or processor issues?

Back 121

A stack dump is generated.

Front 122

What are 3 of SQL Server's 7 locking modes?

Back 122

Shared

Exclusive

Update

Front 123

What are the three lock types?

Back 123

Row

Page

Table

Front 124

What type of lock is acquired for reads?

Back 124

A shared lock; you can have multiple shared locks on the same resource.

Front 125

What type of lock is acquired for a resource being modified?

Back 125

An exclusive lock; it is held until the modification is complete.

Front 126

What is an update lock?

Back 126

A hybrid of the shared and exclusive locks. An update lock starts out by acquiring a shared locks on resources until it finds the piece of data that needs to be modified, the shared lock is then changed to an exclusive lock while the data is changed.

Front 127

What determines what type of lock to acquire?

Back 127

The Lock Manager; uses an aggressive resource threshold, commonly called 2% rule, designed to minimize the number of locks needed--as each lock consumes memory. If determined that more than 2% of the rows on a page will need to be accessed, a page lock will be acquired. Likewise, if more than 2% of the pages in a table will be needed, a table lock is acquired.

Front 128

What is lock escalation?

Back 128

Because distribution statistics are not always accurate or don't exist, Lock manager uses lock escalation to allow a lock to be promoted to another lock type. A row lock can be escalated to a table lock and a page lock can be escalated to a table lock. Row locks are NEVER escalated to page locks.

Front 129

What are the five transaction isolation levels?

Back 129

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

READ SERIALIZED

SNAPSHOT

Front 130

What is the READ UNCOMMITTED transaction isolation level?

Back 130

Data can be read that has not been committed. Although an exclusive lock still blocks another exclusive lock, any read operations ignore an exclusive lock.

Front 131

What is the READ COMMITTED transaction isolation level?

Back 131

This is the default isolation level for SQL Server. An exclusive lock blocks both shared as well as exclusive locks. A shared lock blocks an exclusive lock. Shared locks are released as soon as the data has been read.

Front 132

What is the REPEATABLE READ transaction isolation level?

Back 132

Exclusive locks block both shared and exclusive locks. Shared locks block exclusive locks. Shared locks are held for the duration of the transaction.

Front 133

What is the READ SERIALIZED transaction isolation level?

Back 133

All the restrictions as the REPEATABLE READ isolation level. In addition, you cannot insert a new row within the keyset range currently locked by the transaction. Locks are held for the duration of the transaction.

Front 134

What is the SNAPSHOT transaction isolation level?

Back 134

Uses the row versioning feature to keep shared and exclusive locks from blocking each other while maintaining data consistency. A read operation retrieves data from the version of the row prior to the start of a data modification operation.

Front 135

How can you determine whether a process is blocked?

Back 135

Use the sys.dm_exec_requests view. A process that is blocked will show a nonzero number in the blocking_session_id column. sp_who2 system stored procedure displays the SPID that is blocking a given process.

Front 136

If you determine that a process is causing contention within your database, what command do you execute?

Back 136

KILL <spid> where SPID is the system process ID of the blocking session.

Front 137

What happens when a process is killed?

Back 137

Any open transaction is rolled back.

A message is returned to the client.

An entry is placed in the SQL Server error log.

An entry is placed in the Windows Application Event Log.

Front 138

What sequence described a how a deadlock occurs?

Back 138

1. SPID1 acquires an exclusive lock on Row A.

2. SPID2 acquires an exclusive lock on Row C.

3. SPID1 attempts to acquire a shared lock on Row C and is blocked by the exclusive lock.

4. SPID2 attempts to acquire a shared lock on Row A and is also blocked by the exclusive lock.

Because both processes have to wait on the other process to release an exclusive lock before they can complete, the Lock Manager has an impossible situation. Lock Manager will kill one of the processes automatically (the one that has the least amount of accumulated time).

Front 139

What error is returned when a deadlock is detected?

Back 139

1205

Front 140

How can you troubleshoot a deadlock?

Back 140

Use SQL Server Profiler to capture a deadlock trace, which allows you to inspect the cause of the gridlock graphically.

Front 141

What recommendations can DTA make?

Back 141

Adding indexes

Dropping indexes

Partitioning tables

Storage aligning tables

Front 142

What are the only events that DTA is concerned with?

Back 142

RPC:Starting

RPC:Completed

SQL:Batch Starting

SQL:Batch Completed

Front 143

What are the four steps that comprise DTA analysis?

Back 143

1. Generate a workload for analysis.

2. Start DTA and connect to a server running SQL Server that contains a database to analyze the workload against.

3. Select the workload to use.

4. Specify tuning options.

Front 144

Why would you want to consider running DTA against a test system rather than a production system?

Back 144

The creation and destruction of statistics by DTA can place a very heavy load on the database being analyzed.

Front 145

What are the valid input sources for DTA to analyze?

Back 145

DTA can analyze queries and stored procedures that are stored in either a file or a table. The most common tuning source for DTA is a trace output file.

Front 146

What three components does Resource Governor work with?

Back 146

Resource Pools

Workload groups

Classification functions

Front 147

What resources can be managed by Resource Governor?

Back 147

CPU and memory

Front 148

What is a workload group?

Back 148

A label that you associate to a connection when it is created so that Resource Governor can assign the connection to the appropriate resource pool.

Front 149

What is a classifier function?

Back 149

A function that you create in the master database; only one classifier function can be active for Resource Governor at a time. It returns the name of the workload group that the session should be classified into. Executes after authentication but before connection handle returned to user's applications; performance issues in classification function could cause connection timeout.

Front 150

When is the default resource pool used?

Back 150

If a classifier function is not associated to Resource Governor, or the classifier function does not exist, returns NULL, or returns a nonexistent workload group, the user session is associated to the default resource pool.

Front 151

What steps do you take to implement Resource Governor?

Back 151

1. Enable Resource Governor.

2. Create one or more resource pools.

3. Create one or more workload groups.

4. Associate each workload group to a resource pool.

5. Create and test a classifier function.

6. Associate the classifier function to Resource Governor.

Front 152

What views can you use to return information about the Resource Governor configuration?

Back 152

SELECT * FROM sys.resource_governor_resource_pools

SELECT * FROM sys.resource_governor_workload_groups

SELECT * FROM sys.resource_governor_configuration

GO

When Resource Governor is active, the group_id column of sys.dm_exec_sessions is the ID of the workload group to which the session is assigned.

Front 153

Where are all DMVs stored?

Back 153

The sys schema.

Front 154

What is the general purpose of DMVs with a prefix of: dm_db_* ?

Back 154

General database space and index utilization.

Front 155

What is the general purpose of DMVs with a prefix of: dm_exec_* ?

Back 155

Statistics for queries that are executing as well as queries that have completed and still have plans in the query cache.

Front 156

What is the general purpose of DMVs with a prefix of: dm_io_* ?

Back 156

Disk subsystem statistics

Front 157

What is the general purpose of DMVs with a prefix of: dm_os_* ?

Back 157

Statistics related to the use of hardware resources.

Front 158

What does the sys.dm_db_index_usage_stats view contain?

Back 158

The number of times (and the last time) each index was used to satisfy a seek, scan or lookup as well as the number of times and the last time an update was performed on each index.

Front 159

What 4 optional parameters does sys.dm_db_index_operational_stats take?

Back 159

database_id

object_id

index_id

partition_id

Front 160

What is db_index_operational_stats used for?

Back 160

The function returns locking, latching and access statistics for each index--helps in determining contention issues.

Front 161

What are the five optional parameters that sys.dm_db_index_physical_stats takes?

Back 161

database_id

object_id

index_id

partition_id

mode

Front 162

What is sys.dm_db_index_physical_stats takes used for?

Back 162

It returns the size and fragmentation statistics for each index and should be the primary source for determining when an index needs to be defragmented.

Front 163

What is the sys.dm_db_missing_index_* views used for?

Back 163

Every time an index miss (when the Optimizer determines that an index would be beneficial but doesn't exist) occurs, SQL Server logs the details.

Front 164

What is sys.dm_exec_connections used for?

Back 164

Contains one row for each connection to the instance. Contains time connection was made along with properties of the connection and encryption settings. Can also tell you the total number of reads and writes for the connection as well as the last time a read or write was executed.

Front 165

What is sys.dm_exec_sessions used for?

Back 165

Contains a row for each currently authenticated session. In addition to login info, also track the current state of each possible query option and the current execution status. Also returns the accumulated reads, writes, CPU and query execution duration for the session.

Front 166

What is sys.dm_exec_requests used for?

Back 166

Contains one row for each currently executing request in the instance. you can use the blocking_session_id column to diagnose contention issues. Also contains the start time, elapsed time, estimated completion time, reads writes and CPU for the request. In addition, you can retrieve the database and command being executed, along with handles for the SQL statement and query plan associated with the request.

Front 167

What is sys.dm_exec_query_stats used for?

Back 167

Contains detailed stats on the performance and resources consumed for every query in the query cache. Lists the last time the query was executed and how many times the query was executed, along with the min and max execution time, logical/physical reads/writes/CPU, and a handle to the query plan generated by the Optimizer.

Front 168

What is a handle?

Back 168

It is the unique value by which SQL Server identifies the stored query plan and text stored in the query cache.

Front 169

What is sys.dm_exec_sql_text used for?

Back 169

It returns the text of the SQL statement associated to the handle that was passed in.

Front 170

What is sys.dm_exec_query_plan used for?

Back 170

It accepts a plan handle and returns the corresponding XML showplan.

Front 171

What statement would you use to return the query and XML showplan for every query currently executing?

Back 171

SELECT * FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_query_plan(plan_handle) CROSS APPLY sys.dm_exec_sql_text(sql_handle)

Front 172

What does the sys.dm_io_virtual_file_stats function return?

Back 172

Statistics about the reads and writes for every database file. Provides aggregate number as well as the bytes written to each file since the instance was started. You can also retrieve IOStalls. You use info from this function to determine whether disk contention is contributing to performance issues. You can also determine if you have disk I/O balanced across database files or if you have a disk hot spot.

Front 173

What is an IOStall?

Back 173

When SQL has to wait for the disk subsystem to become available to satisfy either a read or write operation; the time for IOStalls, measured in ms, is logged for each database file.

Front 174

What does the sys.dm_io_pending_requests return?

Back 174

Contains a row for each request that is waiting for the disk subsystem to complete an I/O request. If you have a request that appear frequently or stays for a long time, you probably have a disk bottleneck.

Front 175

Data collection for the Performance Data Warehouse is configured using which collector types?

Back 175

T-SQL Query

SQL Trace

Performance Counter

Query Activity

Front 176

Because the Data Collector dynamically generates the table based on the results of the query defines, what must you ensure?

Back 176

The result set does not contain columns named snapshot_time, snapshot_id, or database_name, because these are reserved for the Data Collector.

All columns in the result set must have a name.

Columns with an image, text, ntext, or XML data type cannot be included.

Only a single result set is returned.

Front 177

What features is the Performance Data Warehouse based on?

Back 177

The Performance Data Warehouse is built upon the Data Collector infrastructure. Data collection is based on SSIS packages and SQL Server Agent jobs.

Front 178

What are the two types of clusters?

Back 178

Standard windows cluster and majority node set cluster.

Front 179

Where is the quorum database stored in each type of cluster?

Back 179

In Standard windows cluster, it is on the shared array. In a majority node set cluster, it is stored locally on each node in the cluster.

Front 180

What permissions does the cluster administrator account need?

Back 180

It needs to be a member of the local administrators group on the machine.

Front 181

What drives can you use in clustering? What if you are building a cluster that contains more than two nodes, has Windows Datacenter or has the 64-bit version of windows?

Back 181

SCSI/iSCSI and Fibre. If any of those conditions are present, you are limited to only Fibre drives.

Front 182

Can you use dynamic disks in clusters?

Back 182

No, only basic disks and mount points.

Front 183

What are some examples of cluster resources?

Back 183

Cluster resources can be hardware, software, or networking. Some examples are IP addresses, network names, disk mounts, and Windows services.

Front 184

How many network connections does a node need for clustering? Why?

Back 184

Each node needs at least two network connections: One connection is used for public communications to applications on the network, and the other is used for private internal communications within the cluster.

Front 185

How does the health check within a Windows cluster work?

Back 185

The basic health check that is performed is called a LooksAlive test. This test consists of each node pinging the others.

Front 186

What is the main difference between a standard cluster and a majority node set cluster?

Back 186

A standard cluster uses a shared quorum database. A majority node set cluster maintains a separate quorum database on each node that is synchronized across all nodes. The majority of nodes (more than 50 percent) must be online for a majority node set cluster to function.

Front 187

Which types of Windows accounts and groups can you use with a SQL Server cluster instance?

Back 187

Domain users and domain groups must be used with SQL Server failover cluster instances. The SID for accounts and groups used must be resolvable across all nodes in the cluster. The SID for a local account or group cannot be resolved across machines.

Front 188

With how many clustered instances can a single drive letter be used?

Back 188

Although a clustered instance can address multiple drive letters, you can configure a given drive letter for only a single instance. This configuration prevents the possibility of having SQL Server running on one node while a different node has ownership of the disk resources required by the clustered instance.

Front 189

What are the two health checks performed in a cluster, and which operations are executed?

Back 189

The LooksAlive check executes every 5 seconds by default and issues a ping from all nodes to the IP address of the SQL Server clustered instance. The IsAlive check executes every 60 seconds by default, connects to the SQL Server clustered instance, issues SELECT @@SERVERNAME, and must receive a valid result set.

Front 190

What are the three database mirroring roles? Which are required and which are optional?

Back 190

principal - req

mirror - req

witness - optional

Front 191

What port is the database mirroring endpoint defined for?

Back 191

5022

Front 192

What are the three operating modes that you can configure for database mirroring?

Back 192

high availability

high performance

high safety

Front 193

What servers does high availability mode in database mirroring require?

Back 193

A principal server, a mirror server and a witness server.

Front 194

In High Availability mode, how is failure detected?

Back 194

High Availability mode uses a ping between each instance participating in the Database mirroring session.

Front 195

How are transactions handled in databases operating in high availability mirroring mode?

Back 195

SQL Server first writes all transactions into memory buffers within the SQL Server memory space. The system writes out these memory buffers to the transaction log. When SQL Server writes a transaction to the transaction log, the system triggers Database Mirroring to begin transferring the transaction log rows for a given transaction to the mirror. When the application issues a commit for the transaction, the transaction is first committed on the mirror database. An acknowledgment of the commit is sent back to the principal, which then enables the commit to be issued on the principal. At that point, the acknowledgment is sent back to the application, enabling it to continue processing. This process guarantees that all transactions are committed and hardened to the transaction log on both the principal and mirror databases before the commit is returned to the application.

Front 196

What is a "split-brain" problem?

Back 196

If the mirror were allowed to determine that it should serve the database by itself, it could introduce a situation whereby the database would be accessible to transactions on more than one server.

Front 197

What are the general steps for automatic failure detection and failover in the High Availability operating mode?

Back 197

1. The principal and mirror continuously ping each other.

2. The witness periodically pings both the principal and the mirror.

3. The principal fails.

4. The mirror detects the failure and makes a request to the witness to promote itself to the principal database.

5. The witness cannot ping the principal but can ping the mirror, so the witness agrees with the role reversal, and SQL Server promotes the mirror to the principal.

6. The principal server comes back online from the failure and detects that the mirror has been promoted to the principal.

7. SQL Server demotes the original principal to a mirror, and transactions begin flowing to this database to resynchronize it with the new principal.

Front 198

What is the High Performance operating mode?

Back 198

High Performance operating mode uses a principal and a mirror database, but it does not need a witness server. This operating mode provides a warm standby configuration that does not support automatic failure detection or automatic failover. High Performance operating mode does not automatically fail over because transactions are sent to the mirror asynchronously. Transactions are committed to the principal database and acknowledged to the application. A separate process constantly sends those transactions to the mirror, which introduces latency into the process. This latency prevents a Database Mirroring session from automatically failing over because the process cannot guarantee that the mirror has received all transactions when a failure occurs. Because the transfer is asynchronous, High Performance operating mode does not affect application performance, and you can have greater geographic separation between the principal and mirror. However, due to the data transfer being asynchronous, you can lose transactions in the event of a failure of the principal or when you force a failover of the mirroring session.

Front 199

What is high safety operating mode?

Back 199

High Safety operating mode transfers transactions synchronously, but it does not have a witness server. The synchronous transfer guarantees that all transactions committed at the principal are first committed at the mirror, and it requires the same performance considerations as the High Availability operating mode. However, the lack of a witness prevents automatic failover to the mirror if the principal fails. If the principal fails in High Safety operating mode, you must promote the mirror manually to serve the database.

Front 200

One benefit of database mirroring is that it does not have caching issues, why is this?

Back 200

In addition to sending transactions to the mirror, database mirroring performs periodic metadata transfers. The purpose of these metadata transfers is to cause the mirror to read pages into the data cache. This process maintains the cache on the mirror in a "semi-hot" state.

Front 201

What is transparent client redirection in terms of database mirroring?

Back 201

The new version of Microsoft Data Access Components (MDAC) that ships with Microsoft Visual Studio 2008 contains a Database Mirroring related feature within the connection object called Transparent Client Redirect. When a client makes a connection to a principal, the connection object caches the principal as well as the mirror. This caching is transparent to the application, and developers do not need to write any code to implement this functionality.

Front 202

Which database mirroring mode is not recommended for normal operations?

Back 202

High Safety - its synchronous transfers have a high-performance impact without the benefit of automatic failover.

Front 203

What is the global communications thread in database mirroring?

Back 203

When you initiate a Database Mirroring session, Service Broker is enlisted to manage a global communications thread. The global communications thread is used to detect when a participant in the Database Mirroring session is not available so that failover can occur when configured in High Availability operating mode and status messages can be passed between the session members.

Front 204

How many threads are created per database in database mirroring and what is their purpose?

Back 204

One thread per database participating in Database Mirroring sessions is created on the instance. The purpose of the database threads is to exchange messages between the principal and the mirror, such as transactions and acknowledgments. On the mirror, one thread per database is opened to manage the process of writing log records and maintaining the query and data caches.

Front 205

What is the purpose of the additional thread that a witness has in database mirroring?

Back 205

It is used to manage all the messages between the witness and participating principal/mirror sessions. The primary messages that are sent on the witness thread are state changes of the principal/mirror and failover requests.

Front 206

What are the four general steps to prepare for Database mirroring?

Back 206

1. Ensure that databases are set to use the Full recovery model.

2. Back up the primary database.

3. Restore the database to the instance hosting the mirror database by using the NORECOVERY option.

4. Copy all necessary system objects to the instance hosting the mirror database.

Front 207

Why must you copy system objects when you configure Database mirroring?

Back 207

It enables applications to function after a failover. The most common objects to transfer are logins, linked server, SQL Server Integration Services (SSIS) packages, SQL Server Agent jobs, custom error messages.

Front 208

What can you use to transfer objects to the instance hosting the mirror database?

Back 208

You can use SSIS, which includes the Transfer Logins task for transferring logins from one instance of SQL Server to another while keeping any passwords encrypted. SSIS also provides tasks for transferring SQL Server Agent jobs, error messages, and other types of objects.

Front 209

If you do not remember the endpoint addresses when configuring database mirroring, what are two ways you can retrieve them?

Back 209

-- You can query sys.database_mirroring_endpoints on each instance to get the endpoint address

-- You can start the Configure Database Mirroring Security Wizard by first right-clicking on the principal database and selecting Tasks, Mirroring. Then, in the Database Properties dialog box, click Configure Security and walk through each step. Because you have created the endpoints already, the wizard automatically retrieves information about them. When the wizard finalizes, it automatically enters the endpoint addresses into the appropriate fields for configuring Database Mirroring.

Front 210

If your security access is defined using SQL Server logins, what additional action might you need to perform after failover to a mirrored database?

Back 210

When you create a SQL Server login, a dummy security identifier (SID) is generated. This SID is then used to link the user in a database to the login for a server. If you do not re-create the SQL Server logins in the same order that you created them on the principal, a mismatch between the login and the user in the database occurs that can grant elevated permissions to a login. In this case, you need to execute ALTER LOGIN to remap the logins. However, because the mirror is inaccessible, you can perform this step only after a failover has occurred.

Front 211

How do you fail back from a forced failover?

Back 211

If the partners were synchronized at the time of the failover, you can apply transaction log backups to roll the failed partner forward in time, and then Database Mirroring finishes the resynchronization process. If the partners were not synchronized at the time of the failover, you need to remove mirroring and reinitialize.

Front 212

Which server objects are your primary concern in the event of a failover?

Back 212

The instance containing the mirror database must also have all the logins present that are required for applications and users to connect to the database. If the applications use linked servers, they also need to exist to prevent application failures. Other server objects such as SSIS packages and jobs are not as critical during the failover and can generally be addressed shortly after a failover after all applications are online.

Front 213

When does a forced failover occur?

Back 213

When the principal fails while the mirroring session is in an unsynchronized state, causing transactions that were committed on the principal to become lost. This is only possible for the High Performance operating mode.

Front 214

What command do you execute to cause a mirrored session to fail over in High Performance Mode?

Back 214

ALTER DATABASE <database name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

Front 215

What command to you execute to remove a mirroring session?

Back 215

ALTER DATABASE <database name> SET PARTNER OFF;

Front 216

What are the two modes that a secondary database participating in log shipping can be in?

Back 216

Standby Mode or No Recovery Mode. When the secondary database is in Standby Mode, users can connect to and issue SELECT statements against the database. When the secondary database is in No Recovery Mode, users cannot connect to the database. In either mode, you can restore transaction logs to the secondary database. You cannot restore transaction logs when users are connected to the database, so you should not use Standby Mode for high-availability architectures.

Front 217

What are the three components of log shipping?

Back 217

The primary database is accessible and accepts transactions. The secondary database has a continuous chain of transaction logs applied to keep it up to date with the primary. The monitor instance sends alerts when the log shipping session is out of sync.

Front 218

What happens to log shipping if the recovery model is changed to Simple?

Back 218

Since log shipping depends on transaction log backup, this change would cause log shipping to cease functioning.

Front 219

What affect would issuing a BACKUP LOG....WITH TRUNCATE_ONLY have on log shipping?

Back 219

Since log shipping depends on transaction log backup, this change would cause log shipping to cease functioning.

Front 220

What is the basic process for initializing log shipping?

Back 220

1. Create a share on both the primary and secondary because the backups need to be accessed across servers.

2. Create jobs to back up transaction logs, copy logs to the secondary, and restore the logs.

3. Restore a full backup to the secondary database.

4. Restore all subsequent transaction logs.

5. Start up jobs to automate restoration of the logs.

6. Copy any instance-level objects upon which the secondary database depends to service applications.

Front 221

The acronym ETL stands for?

Back 221

Extract, Transform, Load

Front 222

The acronym BI stands for?

Back 222

Business Intelligence

Front 223

What information does the Master database contain?

Back 223

The meta data for the SQL server.

Front 224

What information does the MSDB database contain?

Back 224

Information about SQL jobs and other automated functions.

Front 225

What is the Model database used for?

Back 225

It is the basic template for all user created databases.

Front 226

List the database objects that fall under the category of a "securable".

Back 226

Databases, Tables, Indexes, Triggers, Views, Etc.

Front 227

What is the difference between the "grant" and "with grant" permissions?

Back 227

"Grant" allows access to an object or function and "with grant" allows the user access to an object or function and the ability to grant others access to the object or function.

Front 228

What is the "Direct Admin Connection" and how do you access it?

Back 228

It is the last line of connectivity to a server having issues and is access able via the SQLCMD prompt.

Front 229

What is the tempdb database used for?

Back 229

The tempdb database stores temporary tables and information.

Front 230

When does the tempdb database dump its contents?

Back 230

Upon server restart and when instructed to do so by the server or administrator.

Front 231

What is the major difference between schemas in SQL 2000 and SQL 2005?

Back 231

Schemas in SQL 2000 where associated exclusively with a particular database user account where schemas in SQL 2005 are not.

Front 232

List the different database logging recovery models.

Back 232

Simple, Bulk-Logged & Full

Front 233

How does the "Full" recovery model allow for?

Back 233

Logs all transactions occurring within a particular database.

Front 234

How does the "Bulk-Logged" recovery model allow for?

Back 234

Logs all transactions occurring within a particular database except those occurring as part of a BULK INSERT.

Front 235

What types of operations are not logged via the "Bulk-Logged" recovery model?

Back 235

BCP, BULK INSERT, WRITETEXT, UPDATETEXT, SELECT INTO, CREATE; ALTER; DBCC DBREINDEX index commands.

Front 236

What happens to transactions in the transaction log when the "Simple" recovery model is in place?

Back 236

Transactions are deleted from the transaction log upon being committed into the database.

Front 237

How many bytes are in a Page?

Back 237

Eight Thousand One Hundred Ninety-Two (8,192)

Front 238

How many pages are in an Extent?

Back 238

Eight (8)

Front 239

How many bytes are in an Extent?

Back 239

Sixty-Four Thousand (64K) aka 65,536 Bytes

Front 240

What is the acronym for the management studio?

Back 240

SSMS

Front 241

How much space is required for the BIT datatype?

Back 241

One (1) bit is required per value reserved in one (1) Byte increments for every 8 values.

Front 242

What is the name of the naming convention that capitalizes the first character of each individual word?

Back 242

Camel Case

Front 243

What are the names of the different data types in SQL server?

Back 243

1) Exact Numerics

2) Approximate Numerics

3) Date and Time

4) Character Strings

5) Unicode Character Strings

6) Binary Strings

7) Other (not the official name)

Front 244

What is the name of the sample database included with SQL 2005?

Back 244

AdventureWorks

Front 245

What is the name of the sample database included with SQL 2000?

Back 245

NorthWind

Front 246

What data types are contained within the Exact Numerics group?

Back 246

BIGINT, INT, SMALLINT, TINYINT, BIT, DECIMAL, NUMERIC, MONEY & SMALLMONEY

Front 247

How many bytes are required for the BIGINT datatype?

Back 247

8 Bytes

Front 248

What is the numeric range for the BIGINT data type?

Back 248

Negative to positive nine (9) followed by eighteen (18) zeros (roughly)

Front 249

What is the numeric range for the INT data type?

Back 249

Just over negative to positive two (2) billion

Front 250

How many bytes are required for the INT data type?

Back 250

Four (4) Bytes

Front 251

How many bytes are required for the SMALLINT data type?

Back 251

Two (2) Bytes

Front 252

How many bytes are required for the TINYINT data type?

Back 252

One (1) Byte

Front 253

What is the numeric range for the SMALLINT data type?

Back 253

-32,768 to 32,767

Front 254

What is the numeric range for the TINYINT data type?

Back 254

0 - 255

Front 255

What is the maximum precision of the DECIMAL data type?

Back 255

thirty-eight (38) digits to the left or right of the decimal point.

Front 256

How many bytes are required for the DECIMAL data type?

Back 256

Up to seventeen (17) bytes

Front 257

Which datatypes not going to be supported in future releases of SQL Server?

Back 257

Numeric

Front 258

What are the new data types as of SQL 2005?

Back 258

XML, VARCHAR(MAX), NVARCHAR(MAX)

Front 259

What are the three levels of Principals?

Back 259

Windows, SQL Server, Database

Front 260

What are the Principal objects at the database level?

Back 260

1) Database User

2) Database Role

3) Database Group

4) Application Role

Front 261

What is a Principal?

Back 261

Anything or anybody that can be given permission to access something.

Front 262

What are the two types of Principals?

Back 262

1) Individual aka Single Identities

2) Collection aka Fixed Server Roles

Front 263

What are the Principal objects at the SQL Server level?

Back 263

1) SQL Server Login

2) SQL Server Role

Front 264

What are the Principal objects at the Windows level?

Back 264

1) Local User Account

2) Domain User Account

3) Group

Front 265

What are Securables?

Back 265

Objects that are accessed

Front 266

What are the different scopes of Securables?

Back 266

1) Server

2) Database

3) Schema

Front 267

What are the Server Securables?

Back 267

1) Logins

2) HTTP Endpoints

3) Certificates

4) Events

5) Databases

Front 268

What are the Database Securables?

Back 268

1) Users

2) Roles

3) Application Roles

4) Assemblies

5) DDL Events

6) Schemas

7) Services

8) Message Types

Front 269

What is the TLA CTE?

Back 269

Common Table Expressions

Front 270

What are the benefits for the "Snapshot Isolation Level" in SQL 2005?

Back 270

1) Better isolates of transactions

2) No locks placed when reading data

3) No dirty reads

4) Uses tempdb to store row versions

5) Can significantly improve concurrency

Front 271

What is the SQLCLR?

Back 271

SQL Common Language Runtime

Front 272

What does the SQLCLR allow for?

Back 272

The SQLCLR allows you to run .NET code within Transact-SQL. Improves processor intensive tasks. Allows for regular expressions.

Front 273

What is the TLA SMO?

Back 273

SQL Management Objects

Front 274

What is the TLA RMO?

Back 274

Replication Management Objects

Front 275

What does the acronym BIDS stand for?

Back 275

Business Intelligence Development Studio

Front 276

What is the Business Intelligence Development Studio used for?

Back 276

It is a development environment for:

1) Analysis Services

2) Integration Services

3) Reporting Services

Front 277

What component replaces DTS in SQL 2005?

Back 277

SSIS (SQL Server Integration Services)

Front 278

What does the acronym SSIS stand for?

Back 278

SQL Server Integration Services

Front 279

What features are included with SQL Server Integration Services?

Back 279

1) Graphical tools and wizards

2) Tasks for performing workflow functions

3) Data sources and destinations for extracting and loading data

4) Transformations for cleaning, aggregating, merging, and copying data

5) Integration Services packages APIs

6) APIs for Integration Services object model

Front 280

What is an aggregate query?

Back 280

TBD

Front 281

What are SQL Reporting Services?

Back 281

Server-based reporting platform containing:

1) Tools for creating, managing and viewing reports

2) Report server to host and process reports

3) API to create application or custom tools

Front 282

What type of data can be processed by Reporting Services?

Back 282

Relational and Multidimensional data

Front 283

What data sources are supported by Reporting Services?

Back 283

1) SQL Server

2) Analysis Server

3) Oracle

4) >NET Framework data providers (OLEDB & ODBC giving access to MySQL, etc.)

Front 284

What does the acronym OLAP stand for?

Back 284

Online Analytical Processing

Front 285

What does the acronym OLTP stand for?

Back 285

Online Transaction Processing

Front 286

What does the TLA UDM stand for?

Back 286

Unified Dimensional Model

Front 287

What are two types of multidimensional data schemas?

Back 287

1) Star

2) Snowflake

Front 288

What is a Fact Table?

Back 288

TBD

Front 289

SQL Server can run under different user accounts. What are they?

Back 289

1) A "Domain User Account" (generally the best option when accessing other servers/network resources)

2) The "Local System Account" (never use)

3) The "Local Service Account" (okay to use, no access to network resources)

4) The "Network Service Account" (same as local service account with network access support)

Front 290

Does SQL Server 2000 support multiple instances on a single server?

Back 290

No (not totally proven yet)

Front 291

What is the benefit of using multiple instances?

Back 291

Allows you to continue using older versions of SQL as the default instance while installing new SQL 2005 named instances on the system.

Front 292

How many instances of SQL 2005 can exist on a single server?

Back 292

Enterprise Version = 50

All Other Versions = 16

Front 293

What version/edition can you upgrade SQL Server 2000 Enterprise Edition to?

Back 293

SQL Server 2005 Enterprise Edition ONLY

Front 294

What is likely to break when upgrading SQL 2000 to SQL 2005?

Back 294

TBD

Front 295

What is the purpose of the Full-Text Search Service?

Back 295

TBD

Front 296

What is the purpose of the SQL Browser Service?

Back 296

TBD

Front 297

What is the name of the application that give you an overview and allow you to make GENERAL/SERVICE configuration changes on the SQL Server installation(s)?

Back 297

SQL Server Configuration Manager

Front 298

What is the default communications port number for SQL Server?

Back 298

1433

Front 299

What is the name of the application that give you an overview and allow you to make FEATURE configuration changes on the SQL Server installation(s)?

Back 299

SQL Server 2005 Surface Area Configuration

Front 300

What does the TLA AWE stand for?

Back 300

Address Windowing Extensions

Front 301

What does the AWE setting do?

Back 301

Allows SQL Server to control its physical memory space directly on 32-bit Windows 2000/2003 server systems ONLY.

Front 302

How much physical memory does the AWE setting allow SQL Server to access?

Back 302

Sixty-Four (64) Gigabytes

Front 303

What does the acronym TLA stand for?

Back 303

Three Letter Acronym

Front 304

True or False. The AWE setting will relinquish physical memory to other applications installed on the server as needed?

Back 304

TRUE

Front 305

As it relates to the SQL Server "Maximum Server Memory (in MB)" setting. What is the default value (2147483647) equate to?

Back 305

2048 Terabytes

Front 306

What do the "Configured Values" and "Running Values" radio buttons within various SQL Server configuration dialogs allow you to determine?

Back 306

The allow you to see what settings are currently configured versus what settings are currently running. Helps to determine if a restart of SQL Server is required to implement various configuration changes.

Front 307

How does the "Maximum Worker Threads" setting affect SQL Server?

Back 307

It helps limit the resource utilization. Each connection to the server requires a new thread.

Front 308

How does the "Boost SQL Server Priority" setting affect SQL Server?

Back 308

Instructs the Windows Server operating system to give the SQL Server threads a higher priority

Front 309

How does the "Use Windows fibers (lightweight pooling)" setting affect SQL Server?

Back 309

TBD

Front 310

How does the "Enable Server Proxy Account" setting affect SQL Server?

Back 310

This setting overrides the default user account (typically the SQL Server service account) under which xp_cmdshell commands are executed.

Front 311

How does the "Cross database ownership chaining" setting affect SQL Server?

Back 311

Very dangerous setting. Used when database ownership must be maintained across multiple SQL Server instances.

Front 312

How does the "Use query governor to prevent long-running queries" setting affect SQL Server?

Back 312

Sets the maximum number of seconds that a query is allowed to run. If the query does not finish in the allotted time all transactions associated with the query are rolled-back and the query is shutdown.

Front 313

What are the various options that can be set under the "Default Connection Options" setting?

Back 313

1) Implicit Transactions

2) Cursor Close On Commit

3) ANSI Warnings

4) ANSI Padding

5) ANSI NULLS

6) Arithmetic Abort

7) Arithmetic Ignore

8) Quoted Identifier

9) No Count

10) ANSI NULL Default On

11) ANSI NULL Default Off

12) CONCAT NULL Yields NULL

13) Numeric Round Abort

14) XACT Abort

Front 314

How does the "Require Distributed transactions for server-to-server communication" setting affect SQL Server?

Back 314

TBD

Front 315

How does the "Recovery Interval" setting affect SQL Server?

Back 315

TBD

Front 316

What does the acronym RDBMS stand for?

Back 316

Relational Database Management System

Front 317

Who created the relational database design principals?

Back 317

Dr. E.F. Codd at IBM (aka Ted Codd)

Front 318

What is "Set Theory"?

Back 318

TBD

Front 319

What is "Predicate Logic"?

Back 319

TBD

Front 320

How many levels are contained within the "Normal Forms"?

Back 320

Three (3) basic normal forms followed by four (4) additional advanced forms.

Front 321

What is the term "Relation" mean in set theory?

Back 321

It refers to a table itself

Front 322

What is the term "Attribute" mean in set theory?

Back 322

It refers to the table columns

Front 323

What is the term "Tulpe" (ta-ple) mean in set theory?

Back 323

It refers to the rows in a table

Front 324

What is the term "Candidate Key" mean in set theory?

Back 324

Means of uniquely identifying a tuple in a relation based on one or more of its attributes.

Front 325

What is the term "Primary Key" mean in set theory?

Back 325

It is the attribute or combination of attributes by which each row is determined as unique.

Front 326

What is the "Surrogate Key" mean in set theory?

Back 326

It is "new" unique value (i.e. autonumber, GUID, etc) when none of the candidate keys are suitable to use as a primary key.

Front 327

What is the "First Normal Form"?

Back 327

"The Key,..."

First normal form requires that each attribute in a relation contain only one piece of information that you care about, and each piece of information that you care about should be held in only one attribute.

Front 328

What is the "Second Normal Form"?

Back 328

"...the whole key,..."

The database should be in first normal form, and every attribute should be functionally dependent on the entire primary key.

(Each relation should model just one entity or event)

Front 329

What is the "Third Normal Form"?

Back 329

"...and nothing but the key, so help me Codd."

The database should be in second normal form, and no attribute should be functionally dependent on an attribute that isn't in the primary key.

(Example: Don't store calculated values. Store the raw data, and use queries to perform calculations as needed unless the calculated values are automatically maintained, which is really a view.)

Front 330

What is the minimum number of table to represent a many-to-many entity relationship?

Back 330

Three (3)

(with the middle, resolver, or junction table storing the "pairings")

Front 331

What are the two types of classes as it relates to relational data?

Back 331

1) Base

2) Derived

Front 332

What is a cascading update?

Back 332

When a change is made to a primary key it will change all the cascade over to any related foreign keys.

Front 333

What are the two new options created for foreign key cascading updates/deletes in SQL 2005?

Back 333

1) Set NULL

2) Set Default

Front 334

Relational Database Engines are to assist in the enforcement of data integrity. What are the three types of "Integrity"?

Back 334

1) Entity Integrity

2) Domain Integrity

3) Referential Integrity



(which are all forms of domain integrity)

Front 335

What does the DML query/update command SELECT do?

Back 335

Extracts data from a database table.

Front 336

What does the DML query/update command UPDATE do?

Back 336

Updates data in a database table.

Front 337

What does the DML query/update command DELETE do?

Back 337

Deletes data from a database table.

Front 338

What does the DML query/update command INSERT INTO do?

Back 338

Inserts new data into a database table.

Front 339

What does DML stand for?

Back 339

Data Manipulation Language

Front 340

What does the DDL query/update command CREATE TABLE do?

Back 340

Creates a new database table.

Front 341

What does the DDL query/update command ALTER TABLE do?

Back 341

Alters (changes) a database table.

Front 342

What does the DDL query/update command DROP TABLE do?

Back 342

Deletes a database table.

Front 343

What does the DDL query/update command CREATE INDEX do?

Back 343

Creates and index (search key).

Front 344

What does the DDL query/update command DROP INDEX do?

Back 344

Deletes an index.

Front 345

What does DDL stand for?

Back 345

Data Definition Language

Front 346

What does SQL stand for?

Back 346

Structured Query Language

Front 347

What is the syntax for using a select statement?

Back 347

SELECT column_name(s)

FROM table_name

Front 348

How do you select multiple columns using the SELECT statement?

Back 348

Separate the column names with commas. Ex:



SELECT column_name1, column_name2

FROM table_name

Front 349

What is the syntax used to select all columns from a table?

Back 349

SELECT * FROM table_name

Front 350

A semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server. Is it necessary to use a semicolon?

Back 350

Not always. It depends on the database program you are using.

Front 351

The result from a SQL query is stored in a ______-___. Most database software systems use programming functions, like: Move-To-First-Record, Get-Record-Content, Move-To-Next-Record, etc. to navigate this with.

Back 351

result-set

Front 352

The DISTINCT keyword is used to return only distinct (different) values. What is the syntax of a SELECT statement with this keyword added to it?

Back 352

SELECT DISTINCT column_name(s)

FROM table_name

Front 353

What is the syntax for using a WHERE clause in a SELECT statement?

Back 353

SELECT column_name FROM table

WHERE column_name <operator> value

Front 354

True or False: SQL uses single quotes around text values (most database systems will also accept double quotes). Numeric values however should also be enclosed in quotes.

Back 354

False, numeric values should not be enclosed in quotes.

Front 355

The LIKE condition is used to specify a search for a pattern in a column. What is the syntax of using LIKE in a SELECT statement?

Back 355

SELECT column_name FROM table

WHERE column_name LIKE pattern_type

Front 356

A "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern in a LIKE condition. What would the syntax be for a statement that will return all people with first names that start with an 'O' in the table Persons? Assume the table has the following columns: FirstName, Last Name and Address

Back 356

SELECT * FROM Persons

WHERE FirstName LIKE 'O%'

Front 357

A "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern in a LIKE condition. What would the syntax be for a statement that will return all people with first names that contain the letters 'la' in them from the table Persons? Assume the table has the following columns: FirstName, Last Name and Address

Back 357

SELECT * FROM Persons

WHERE FirstName LIKE '%la%'

Front 358

The INSERT INTO statement is used to insert new rows into a table. What is the syntax for using this command?

Back 358

INSERT INTO table_name

VALUES (value1, value2,....)

Front 359

Using INSERT INTO, it is possible to specify which column to insert new data into. What is the syntax for this?

Back 359

INSERT INTO table_name (column_name1, column_name2,...)

VALUES (value1, value2,....)

Front 360

The UPDATE statement is used to modify the data in a table. What is the syntax for this?

Back 360

UPDATE table_name

SET column_name = new_value

WHERE column_name = some_value

Front 361

The DELETE statement is used to delete rows in a table. What is the syntax for this?

Back 361

DELETE FROM table_name

WHERE column_name = some_value

Front 362

The ORDER BY clause is used to sort the rows. What is the syntax for this?

Back 362

SELECT column_name(s) FROM table_name

ORDER BY column_name(s)

Front 363

What keyword would you use if you want to sort values in descending order?

Back 363

DESC

Front 364

What keyword would you use if you want to sort values in ascending order?

Back 364

ASC

Front 365

AND and OR join two or more conditions in a WHERE clause.



The AND operator displays a row if ___ conditions listed are true.

Back 365

all

Front 366

AND and OR join two or more conditions in a WHERE clause.



The OR operator displays a row if ___ of the conditions listed are true.

Back 366

any

Front 367

AND (and OR - see hint) join two or more conditions in a WHERE clause.



What is the syntax of these statements?

Back 367

SELECT column_name FROM table_name
WHERE columname<operator>'value'
AND columname<operator>'value'

Front 368

The IN operator may be used if you know the exact value you want to return for at least one of the columns. What is the syntax for this expression?

Back 368

SELECT column_name FROM table_name
WHERE column_name IN (value1,value2,..)

Front 369

The BETWEEN ... AND operator selects a range of data between two values. These values can be numbers, text, or dates. What is the syntax for this expression?

Back 369

SELECT column_name FROM table_name
WHERE column_name
BETWEEN value1 AND value2

Front 370

To display the persons outside the range used in a BETWEEN ... AND statement, use the ___ operator.

Back 370

NOT

Front 371

With SQL, aliases can be used for column names and table names. What is the syntax for Column Name Alias?

Back 371

SELECT column_name AS column_alias FROM table_name

Front 372

With SQL, aliases can be used for column names and table names. What is the syntax for Table Name Alias?

Back 372

SELECT column_name FROM table_name AS table_alias

Front 373

Tables in a database can be related to each other with keys. A primary key is a column with a ______ _____ for each row.

Back 373

unique value

Front 374

We can select data from two tables by referring to two tables. If we had one table named Employees which had columns named Employee_ID(primary key) and Name and another table called Orders that had columns named Prod_ID(primary key), Product, and Employee_ID how would we find out who has ordered what product?

Back 374

SELECT Employees.Name, Orders.Product
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID

Front 375

We can select data from two tables by referring to two tables. If we had one table named Employees which had columns named Employee_ID(primary key) and Name and another table called Orders that had columns named Prod_ID(primary key), Product, and Employee_ID how would we find out who ordered a printer?

Back 375

SELECT Employees.Name
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID
AND Orders.Product='Printer'

Front 376

We can select data from two tables with the INNER JOIN keyword. The INNER JOIN returns all rows from both tables where there is a match. If there are rows in table_name1 that do not have matches in table_name2, those rows will not be listed. What is the syntax for this?

Back 376

SELECT table_name1.column_name, table_name2.column_name
FROM table_name1
INNER JOIN table_name2
ON table_name1.primary_key1 = table_name2.primary_key2

Front 377

We can select data from two tables with the LEFT JOIN keyword. The LEFT JOIN returns all the rows from table_name1, even if there are no matches in table_name2. If there are rows in table_name1 that do not have matches in table_name2, those rows also will be listed. What is the syntax for LEFT JOIN?

Back 377

SELECT table_name1.column_name, table_name2.column_name
FROM table_name1
LEFT JOIN table_name2
ON table_name1.primary_key1 = table_name2.primary_key2

Front 378

We can select data from two tables with the RIGHT JOIN keyword. The RIGHT JOIN returns all the rows from table_name2, even if there are no matches in table_name1. If there had been any rows in table_name2 that did not have matches in table_name1, those rows also would have been listed. What is the syntax for RIGHT JOIN?

Back 378

SELECT table_name1.column_name, table_name2.column_name
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.primary_key1 = table_name2.primary_key2

Front 379

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the ____ data type.

Back 379

same

Front 380

The UNION command is used to select related information from two tables, much like the JOIN command. With UNION, only distinct values are selected. What is the syntax for UNION?

Back 380

SQL Statement 1

UNION

SQL Statement 2

Front 381

The _____ ___ command is equal to the UNION command, except that it selects all values.

Back 381

UNION ALL

Front 382

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. What is the syntax for UNION ALL?

Back 382

SQL Statement 1

UNION ALL

SQL Statement 2

Front 383

What is the syntax used to create a new database?

Back 383

CREATE DATABASE database_name

Front 384

What is the syntax for creating a new table in a database?

Back 384

CREATE TABLE table_name

(
column_name1 data_type,
column_name2 data_type,
.......
)

Front 385

In the example below, what does the numbers in parentheses indicate?



CREATE TABLE Person

(

Last Name varchar(30),

FirstName varchar,

Address varchar,

Age int(3)

)

Back 385

The maximum length for the column.

Front 386

The data type for creating a table with specifies what type of data the column can hold. What are the four integer values?

Back 386

integer(size)

int(size)

smallint(size)

tinyint(size)

Front 387

The data type for creating a table with specifies what type of data the column can hold. What are the two most common data types for decimal numbers?

Back 387

decimal(size,d)

numeric(size,d)

Front 388

The data type for creating a table with specifies what type of data the column can hold. What is the type used for a fixed length string (can contain letters, numbers, and special characters).

Back 388

char(size)

Front 389

The data type for creating a table with specifies what type of data the column can hold. What is the data type for a variable length string (can contain letters, numbers, and special characters).

Back 389

varchar(size)

Front 390

The data type for creating a table with specifies what type of data the column can hold. What is the data type used to hold a date?

Back 390

date(yyyymmdd)

Front 391

You can create an _____ in an existing table to locate rows more quickly and efficiently. It is possible to create one on one or more columns of a table, and each is given a name. The users cannot see them, they are just used to speed up queries.

Back 391

index

Front 392

A unique index means that two rows cannot have the same index value. What is the syntax for a unique index?

Back 392

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

Front 393

A simple index, one without the UNIQUE keyword, allows duplicate values. What is the syntax for a simple index?

Back 393

CREATE INDEX index_name

ON table_name (column_name)

Front 394

You can delete an existing index in a table with the ____ _____ statement.

Back 394

DROP INDEX

Front 395

You can delete an existing index in a table with the DROP INDEX statement.



What is the syntax for Microsoft SQLJet (and Microsoft Access)?

Back 395

DROP INDEX index_name ON table_name

Front 396

You can delete an existing index in a table with the DROP INDEX statement.



What is the syntax for MS SQL Server?

Back 396

DROP INDEX table_name.index_name

Front 397

You can delete an existing index in a table with the DROP INDEX statement.



What is the syntax for IBM DB2 and Oracle?

Back 397

DROP INDEX index_name

Front 398

You can delete an existing index in a table with the DROP INDEX statement.



What is the syntax for MySQL?

Back 398

ALTER TABLE table_name DROP INDEX index_name

Front 399

To delete a table (the table structure, attributes, and indexes will also be deleted) you would use what syntax?

Back 399

DROP TABLE table_name

Front 400

To delete a database what syntax would you use?

Back 400

DROP DATABASE database_name

Front 401

If you only want to get rid of the data inside a table, and not the table itself, use the TRUNCATE TABLE command. What is the syntax for this command?

Back 401

TRUNCATE TABLE table_name

Front 402

The _____ _____ statement is used to add or drop columns in an existing table.

Back 402

ALTER TABLE

Front 403

The ALTER TABLE statement is used to add or drop columns in an existing table. What is the ADD command syntax?

Back 403

ALTER TABLE table_name
ADD column_name datatype

Front 404

The ALTER TABLE statement is used to add or drop columns in an existing table. What is the DROP COLUMN command syntax?

Back 404

ALTER TABLE table_name
DROP COLUMN column_name

Front 405

What is the syntax for built-in SQL functions?

Back 405

SELECT function(column) FROM table

Front 406

What are two basic types of functions in SQL?

Back 406

Aggregate Functions and Scalar functions

Front 407

GROUP BY... was added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called, and without the GROUP BY function it was impossible to find the sum for each individual group of column values. What is the syntax for this command?

Back 407

SELECT column_name1,SUM(column_name2) FROM table GROUP BY column_name1

Front 408

HAVING... was added to SQL because the WHERE keyword could not be used against aggregate functions (like SUM), and without HAVING... it would be impossible to test for result conditions.

The syntax for the HAVING function is?

Back 408

SELECT column,SUM(column) FROM table
GROUP BY column
HAVING SUM(column) condition value

Front 409

The SELECT INTO statement is most often used to create backup copies of tables or for archiving records. What is the syntax for this statement

Back 409

SELECT column_name(s) INTO newtable [IN externaldatabase]
FROM source

Front 410

What is a view in SQL?

Back 410

A VIEW is a virtual table based on the result-set of a SELECT statement.

Front 411

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from a single table. What is the syntax for a VIEW statement?



Note: The database design and structure will NOT be affected by the functions, where, or join statements in a view.

Back 411

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Front 412

Aggregate functions: What does AVG(column_name) do?

Back 412

Returns the average value of a column.

Front 413

Aggregate functions: What does COUNT(column_name) do?

Back 413

Returns the number of rows (without a NULL value) of a column.

Front 414

Aggregate functions: What does COUNT(*) do?

Back 414

Returns the number of selected rows.

Front 415

Aggregate functions: What does FIRST(column_name) do?

Back 415

Returns the value of the first record in a specified field.

Front 416

Aggregate functions: What does LAST(column_name) do?

Back 416

Returns the value of the last record in a specified field.

Front 417

Aggregate functions: What does MAX(column_name) do?

Back 417

Returns the highest value of a column.

Front 418

Aggregate functions: What does MIN(column_name) do?

Back 418

Returns the lowest value of a column.

Front 419

Aggregate functions: What does SUM(column_name) do?

Back 419

Returns the total sum of a column.

Front 420

Aggregate functions: What does FIRST(column_name) do?

(not supported in SQLServer2K)

Back 420

Returns the value of the first record in a specified field.

Front 421

Aggregate functions: What does LAST(column_name) do?

(not supported in SQLServer2K)

Back 421

Returns the value of the last record in a specified field.

Front 422

Scalar functions: What does UCASE(c) do?

Back 422

Converts a field to upper case.

Front 423

Scalar functions: What does LCASE(c) do?

Back 423

Converts a field to lower case.

Front 424

Scalar functions: What does MID(c,start[,end]) do?

Back 424

Extract characters from a text field.

Front 425

Scalar functions: What does LEN(c) do?

Back 425

Returns the length of a text field.

Front 426

Scalar functions: What does INSTR(c,char) do?

Back 426

Returns the numeric position of a named character within a text field.

Front 427

Scalar functions: What does LEFT(c,number_of_char) do?

Back 427

Return the left part of a text field requested.

Front 428

Scalar functions: What does RIGHT(c,number_of_char) do?

Back 428

Return the right part of a text field requested.

Front 429

Scalar functions: What does ROUND(c,decimals) do?

Back 429

Rounds a numeric field to the number of decimals specified.

Front 430

Scalar functions: What does MOD(x,y) do?

Back 430

Returns the remainder of a division operation.

Front 431

Scalar functions: What does NOW() do?

Back 431

Returns the current system date.

Front 432

Scalar functions: What does FORMAT(c,format) do?

Back 432

Changes the way a field is displayed.

Front 433

Scalar functions: What does DATEDIFF(d,date1,date2) do?

Back 433

Used to perform date calculations.

Front 434

RDBMS

Back 434

Relational Database Management System

Front 435

DML

DDL

Back 435

Data Manipulation Language (DML)

Data Definition Language (DDL)

Front 436

SQL SELECT Syntax

Back 436

SELECT column_name(s)
FROM table_name

Front 437

SELECT * FROM table_name

Back 437

SQL SELECT Syntax

Front 438

P_Id Last Name FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

SELECT LastName,FirstName FROM Persons (exmp)

Back 438

Last Name FirstName

Hansen Ola
Svendson Tove
Pettersen Kari

Front 439

P_Id Last Name FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

SELECT * FROM Persons

Back 439

P_Id Last Name FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Front 440

SQL WHERE Syntax

Back 440

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

Front 441

P_Id Last Name FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

SELECT * FROM Persons

WHERE City='Sandnes'

Back 441

P_Id Last Name FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes

Front 442

Operators Allowed in the WHERE Clause

Back 442

"= Equal
'<> Not equal
'> Greater than
'< Less than
'>= Greater than or equal
'<= Less than or equal
'BETWEEN Between an inclusive range
'LIKE Search for a pattern
'IN If you know the exact value you want to return for at least one of the columns"

Front 443

OR Operator Example

Back 443

SELECT * FROM Persons
WHERE FirstName='Tove'
OR FirstName='Ola

Front 444

Combining AND & OR

Back 444

SELECT * FROM Persons WHERE
Last Name='Svendson'
AND (FirstName='Tove' OR FirstName='Ola')

Front 445

The ORDER BY Keyword

Back 445

The ORDER BY keyword is used to sort the result-set by a specified column.

Front 446

SQL ORDER BY Syntax

Back 446

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC / DESC

Front 447

SQL ORDER BY Syntax with SELECT statement

Back 447

SELECT * FROM Persons
ORDER BY Last Name

Front 448

ORDER BY DESC Example

Back 448

Now we want to select all the persons from the table above, however, we want to sort the persons descending by their last name.

SELECT * FROM Persons
ORDER BY Last Name DESC

Front 449

SQL INSERT INTO Syntax v1

Back 449

INSERT INTO table_name
VALUES (value1, value2, value3,...)