Deleteme_Ipad_Sort-List1

Front 1

Specialized tasks included in SSIS part1

(4)

Back 1

Copy SQL Server objects

Manage DB backups

Re-indexing

Extendibility with VSTA

Front 2

Precedence constraints in SSIS

Back 2

Allows to configure operational workflows based on a component success, failure or other execution

Front 3

Package configurations in SSIS

Back 3

Enables developers to expose internal properties of a package such that the properties can be modified

Front 4

Specialized tasks included in SSIS part1

(4)

Back 4

Workflow tasks

Fuzzy lookups

Fuzzy grouping

Combinif data flows

Remap characters

Pivot/unpivot data sets

Front 5

Fuzzy search/lookup

(simple)

Back 5

A type of search that will find matches even when users misspell words or enter in only partial words for the search.

Front 6

Fuzzy Lookup

(advanced)

Back 6

Enables you to match input records with clean, standardized records in a reference table. The matching process is resilient to errors that are present in the input records. Fuzzy Lookup returns the closest match and indicates the quality of the match.

Front 7

Fuzzy Grouping

Back 7

Enables you to identify groups of records in a table where each record in the group potentially corresponds to the same real-world entity. The grouping is resilient to commonly observed errors in real data, because records in each group may not be identical to each other but are very similar to each other. For example, Fuzzy Grouping is useful for grouping together all records in a Customers reference table that describe a single real customer.

Front 8

SSRS

Back 8

SQL Server Reporting Service

Platform for designing reports and distributing data

Front 9

SSRS components

(2)

Back 9

Report server

Report designer

Front 10

SSAS

Back 10

SQL Server Analysis Service

Create to fill the gap between the data needs of business and the ability if IT to provide data.

Front 11

SSAS components

(2)

Back 11

Online Analytical Processing

Data Mining

Front 12

OLAP

Back 12

Online Analytical Processing

Front 13

OLAP engine use

Back 13

Allows to deploy, query, and manage cubes that have been designed in Business Intelligence Development Studio.

Front 14

BIDS

Back 14

Business Intelligence Development Studio

Front 15

Data Mining engine use

Back 15

Allows users to find patterns and make predictions. It could be used examine data trends over time.

Front 16

SQL Workgroup is for

Back 16

Small departmental projects with a limited set of features.

Front 17

Express is for

Back 17

Freely distro of SQL for embedded apps., basic storage needs.

Front 18

Compact

Back 18

Designed as an embedded DB.

Front 19

Standard CPU, DB size, RAM

Back 19

CPU, DB size, RAM

4, Un, Un

Front 20

Workgroup CPU, DB size, RAM

Back 20

CPU, DB size, RAM

4, Un, Un

Front 21

Express CPU, DB size, RAM

Back 21

CPU, DB size, RAM

1, 4, 1

Front 22

Compact CPU, DB size, RAM

Back 22

CPU, DB size, RAM

1, 4, 1

Front 23

SQL Server Management Studion(DB engine support)

Back 23

Standard:yes
Workgroup:yes
Express:sep.down.
Compact:no

Front 24

Full Text Search(DB engine support)

Back 24

Standard:yes
Workgroup:yes
Express:adv.serv.only
Compact:no

Front 25

Partioning(DB engine support)

Back 25

Standard:no
Workgroup:no
Express:no
Compact:no

Front 26

Parallel Operations(DB engine support)

Back 26

Standard:no
Workgroup:no
Express:no
Compact:no

Front 27

Multiple Instances(SSIS support)

Back 27

Standard:no
Workgroup:no
Express:no
Compact:no

Front 28

DB snapshots(SSIS support)

Back 28

Standard:no
Workgroup:no
Express:no
Compact:no

Front 29

Scalable Shared DB(SSIS support)

Back 29

Standard:no
Workgroup:no
Express:no
Compact:no

Front 30

Indexed Views(SSIS support)

Back 30

Standard:no
Workgroup:no
Express:no
Compact:no

Front 31

Log Compression(SSIS support)

Back 31

Standard:no
Workgroup:no
Express:no
Compact:no

Front 32

Clustering(SSIS support)

Back 32

Standard:2 nodes
Workgroup:no
Express:no
Compact:no

Front 33

DB Mirroring(SSIS support)

Back 33

Standard:single-thread
Workgroup:no
Express:no
Compact:no

Front 34

Online Operations(SSIS support)

Back 34

Standard:no
Workgroup:no
Express:no
Compact:no

Front 35

Resource Governor(SSIS support)

Back 35

Standard:no
Workgroup:no
Express:no
Compact:no

Front 36

Backup Compression(SSIS support)

Back 36

Standard:no
Workgroup:no
Express:no
Compact:no

Front 37

Hot Swap Memory/CPU(SSIS support)

Back 37

Standard:no
Workgroup:no
Express:no
Compact:no

Front 38

Data Encryption(SSIS support)

Back 38

Standard:Limited
Workgroup:Limited
Express:Limited
Compact:Pass.based only

Front 39

Change Data Capture(SSIS support)

Back 39

Standard:no
Workgroup:no
Express:no
Compact:no

Front 40

Policy Based Management(SSIS support)

Back 40

Standard:yes
Workgroup:no
Express:no
Compact:no

Front 41

Performance data collection(SSIS support)

Back 41

Standard:Yes
Workgroup:no
Express:no
Compact:no

Front 42

CLR(SSIS support)

Back 42

Standard:Yes

Front 43

XML support(SSIS support)

Back 43

Standard:Native
Workgroup: Native
Express:Native
Compact:Stored as text

Front 44

Spatial data(SSIS support)

Back 44

Standard: Yes

Front 45

Stored procedure, triggers and views(SSIS support)

Back 45

Standard:Yes
Workgroup:Yes
Express:Yes
Compact:no

Front 46

Merge Replication(SSIS support)

Back 46

Standard:Yes
Workgroup:Yes
Express:Subscriber only
Compact:Subscribe only

Front 47

Transactional Replication(SSIS support)

Back 47

Standard:Yes
Workgroup:Subscriber only
Express:Subscriber only
Compact:No

Front 48

import/Export Wizard(SSIS support)

Back 48

Standard:Yes
Workgroup:Yes
Express:N/A
Compact:N/A

Front 49

Package Designer(SSIS support)

Back 49

Standard:Yes
Workgroup:Yes
Express:N/A
Compact:N/A

Front 50

Data Mining(SSIS support)

Back 50

Standard:No
Workgroup:No
Express:N/A
Compact:N/A

Front 51

Fuzzy grouping/lookup(SSIS support)

Back 51

Standard:No
Workgroup:No
Express:N/A
Compact:N/A

Front 52

Term extraction/lookup(SSIS support)

Back 52

Standard:No
Workgroup:No
Express:N/A
Compact:N/A

Front 53

OLAP processing(SSIS support)

Back 53

Standard:No
Workgroup:No
Express:N/A
Compact:N/A

Front 54

What services do you need dedicated accounts for

Back 54

Database Engine
SQL Server Engine

Front 55

Possible authentication services in SQL

Back 55

Windows Only
Windows and SQL Server

Front 56

What is the maximum amount of SQL instances on single machine

Back 56

50 = 1 default + 49 named
supported only in SQL enterprise

Front 57

Enumeration request

Back 57

Application can broadcast a special command request across a network to locate any servers running SQL server. By disabling enumeration responses by setting the Hide Instances to Yes you will prevent discovery of you SQL server preventing a possible attack

Front 58

Which command must be execute against the SQL Server instance before FILESTREAM data can be stored

Back 58

Sp_configure

EXEC sp_configure 'filestream_access_level', 2;

Front 59

Profiles and Database Mail

Back 59

Profiles form the core element within Database Mail. Given profile can have multiple email accounts

Front 60

Possible profile configurations

(2)

Back 60

Public - can be accessed by any user with the ability to send mail.

Private - cab be accessed only by those users who have been granted access to the mail profile explicitly.

Front 61

What happens to Database Mail if SQL Server Agent is not running

Back 61

Messages will accumulate in a queue within the MSDB database.

Front 62

Query to enable Database Mail feature

Back 62

EXEC sp_configure 'Database Mail XPs', 1
GO

RECONFIGURE WITH OVERRIDE
GO

Front 63

What service database mail utilizes

Back 63

SQL Server Agent is used to send messages as a background process.

Front 64

What happens if SQL Serve Agent is not running

Back 64

Messages accumulate in queue within the Msdb database.

Front 65

How many mail accounts can a mail profile contain

Back 65

One and more

Front 66

What are notification Services

Back 66

A feature that was available in SQL 2005 that is not longer available in 2008

Front 67

What is Activity Monitor

Back 67

Is a feature of SSMS that displays query activity on the server

Front 68

Achieving optimal performance disk I/O performance

Back 68

Separate the transaction logs from data files so that you can isolate disk I/O

Front 69

What happens to data prior to being written to the data files of a DB

Back 69

Every transaction is written to a transaction log

Front 70

Types of files used by SQL Server(2)

Back 70

Data and transaction logs

Front 71

What are data files for?

Back 71

Data files are responsible for the long-term storage of all the data within a DB

Front 72

What are transaction log files for?

Back 72

Responsible for storing all the transactions that are executed against a DB

Front 73

Explanation of a filegroup

Back 73

Instead of defining the storage of objects directly to a data file, SQL Server provides and abstraction layer for more flexibility called a filegroup.

Filegroups are a logical structure defined within a DB, that map a DB and the objects contained within a DB to the data files on disk.

Front 74

Can a filegroup contain more than one file?

Back 74

Yes

Front 75

What is ON clause

Back 75

Allows you to specify when you create an object the file group where SQL Server will store the object

Front 76

What can you specify for a file when is added to a DB

Back 76

initial size

max size

auto growth in percents or MB

Front 77

What algorithm SQL Server uses when writing to a file

Back 77

Proportional fill algorithm. This algorithm is designed to ensure that all files within a filegroup reach the maximum defined capacity at the same time.

Front 78

.mdf extension(3)

Back 78

1) First data file created within a DB

2) Associated with the primary filegroup

3) Primary data file which contains all the system objects necessary to a database

Front 79

.ndf extension

Back 79

Used for all other data files underneath a DB regardless of the filegroup to which the file is associated.

Front 80

.ldf extension

Back 80

Used for the transaction logs.

Front 81

Describe the check pointing process

Back 81

All data manipulation within SQL occurs in memory within a set of buffers. Data is first written to a memory buffer, then written to the transaction log, and finally persisted to a data file via a background process called check pointing.

Front 82

Does data file design and I/O speed affects performance

Back 82

Yes, faster they are the better the performance is.

Front 83

How SQL Server communicates with the files underneath a DB

Back 83

SQL Server creates a separate thread for each file underneath a Database.

Front 84

How many files and filegroups should you have when creating a DB?

Back 84

You should have a file with an .mdf extension within a filegroup named PRIMARY, a file with an .ndf extension in a filegroup with any name that you chose, and the transaction log with and ldf. extension

Front 85

DEFAULT property for a filegroup

Back 85

Used to define the filegroup where SQL Server places objects if you do not specify the ON clause during object creation.

Front 86

Why would you change the default filegroup?

Back 86

After you create a DB you should mark the second filegroup as the default filegroup. By changing the default filegroup, you ensure that any object you create are not accidentally placed on the primary filegroup

Front 87

Command for changing the default filegroup

Back 87

ALTER DATABASE <db name. MODIFY FILEGROUP <fg name> DEFAULT

Front 88

Main reason not to place any of your objects on the primary filegroup is?

Back 88

To provide as much isolation in the I/O as possible

Front 89

Reasons to use secondary filegroup vs. primary filegroup

Back 89

1) To provide as much isolation in the I/O as possible.
2) The data in the system object doesn't change as often as data in your object.
3) By minimizing the write activity to the primary data file you reduce the possibility of introducing corruption due to hardware failures.
4) Increase the availability of the DB by minimizing the changes made to the primary filegroup.

Front 90

What determines the state of the DB

Back 90

The state of the primary filegroup determines the state of the database. You can increase the availability of the database by minimizing the changes made to the primary filegroup.

Front 91

Transaction Logs - process explanation

Back 91

When SQL Server acknowledges that a transaction has been committed, SQL Server must ensure that the change is hardened to persistent storage. Although all writes occur through memory buffers, persistence is guaranteed by requiring that all changes are written to the transaction log prior to a commit being issued. In addition, the writes to the transaction log must occur directly to disk.

Front 92

How SQL Server writes to the transaction log

Back 92

Sequentially

Front 93

When SQL Server reads from the log

Back 93

Only during restart recoveries.

Front 94

Why would you isolate the transaction log to a dedicated disk

Back 94

SQL Server randomly reads and writes to the data files underneath a DB, by isolating the transaction log to a dedicated set of disks you ensure that the disk heads performance is optimal.

Front 95

What FILESTREAM allows you

Back 95

To associate files with a database. The files are stored in a folder on the OS, but are linked directly into a Database where the files can be backed up, restored, full-text-indexed, and combined with other structured data.

Front 96

tempdb database usage

Back 96

Temporary objects

Works tables used in grouping/storing

Work tables to support cursors

Version store supporting snapshot isolation level

Overflow for table variables

Front 97

Why should you move tempdb?

Back 97

Due to potential heavy traffic tempdb should be moved to a set of disks separated from DBs and backup files.

Front 98

Multiple tempdb

Back 98

Common practice for tempdb is to create one file per processor. The one file per processor is with respect to what SQL Server would consider a processor and not the physical processor, which could have multiple cores as well as hyper-threading.

Front 99

DB Recovery options

Back 99

Determine the behavior of the transaction log and how damaged pages are handled.

Front 100

Recover model(explanation)

Back 100

Every DB within a SQL Server instance has a property setting called the recovery model. Define the type of backups you can perform against a DB.

Front 101

Recover models available in SQL 2008 are:

(3)

Back 101

Full

Bulk-logged

Simple

Front 102

Full Recovery Mode

Back 102

All changes made using both DML and DDL are logged to the transaction log. It is possible to recover a DB in the Full recovery model to a give point in time so that data loss can be minimized.

Front 103

Best practice recovery model

Back 103

Every production DB that accepts transactions should be set to the Full recovery model.

Front 104

Bulk-logged recovery model

Back 104

Allows certain operations to be executed with minimal logged. When a minimally logged operation is performed, SQL Server does not log every row changed but only the extents, reducing over head and improving performance.

Front 105

Operations that are performed in minimally logged manner with the DB set in bulk-logged recovery mode are:

(5)

Back 105

BCP

BULK INSERT

SELECT..INTO

CREATE INDEX

ALTER INDEX .. REBUILD

Front 106

Bulk-logged recovery model note for recovery procedure

Back 106

Because bulk-logged recovery model doesn't log every change to the transaction log, a DB cannot be recovered to a point in time within the interval that a minimally logged transactions executed.

Front 107

Simple recovery model

Back 107

A DB in Simple recovery model logs operations to the transaction log exactly as the Full recovery model does. However, each time the DB checkpoint process executes, the committed portion of the transaction log is discarded.

Front 108

Cmd for setting DB recovery model

Back 108

ALTER DATABASE database_name SET RECOVERY { FULL / BULK_LOGGED / SIMPLE }

Front 109

How pages get damaged

Back 109

It is possible to damage data pages during a write to a disk if you have a power failure or failures in disk subsystem components during the write operation. Because the damage happens to a page on disk the only time that you see a result of the damage is when SQL Server attempts to read the page off disk.

Front 110

What command allows you to discover and log damaged pages

Back 110

PAGE_VERIFY CHECKSUM

Front 111

Do SQL by default checks for damaged pages

Back 111

No, you need to turn PAGE_VERIFY CHECKSUM to have SQL check for damaged pages.

Front 112

What can happen to an SQL Server a DB if a damaged pages is found

Back 112

The default Configuration of SQL Server does not check for damaged pages and could cause the DB to go off-line if a damaged page is encountered.

Front 113

When is error 824 returned?

Back 113

What error is returned to the calling application and logged to SQL Server error log and Windows Application Event Log and the ID of the damaged page is logged to the suspect_pages table in the msdb DB.

Front 114

SQL Server 2008 introduces FILESTREAM why?

Back 114

Although the volume of data within organization has been exploding, leading the way in this data explosion is unstructured data. To tackle the problem of storing, managing, and combining the large volumes of unstructured DB with the structured data in you DB.

Front 115

What FILESTREAM allows you to do?

Back 115

This feature allows you to associate files with a DB. The files are stored in a folder on the operating system, but are linked directly into a DB where the files can be backed up, restored, gull-text-indexed, and combined with other structured data.

Front 116

What AUTO OPTIONS do

Back 116

Database that enables certain actions to occur automatically

Front 117

List AUTO OPTIONS

(5)

Back 117

AUTO_CLOSE

AUTO_SHRINK

AUTO_CREATE_STATISTICS

AUTO_UPDATE_STATISTICS

AUTO_UPDATE_STATISTICS_ASYNCH

Front 118

If AUTO_CLOSE is enabled

Back 118

When the last connection to a DB is closed, SQL Server shuts down the DB and releases all resources related to the DB. When a new connection is made to the DB, SQL starts up the DB and allocates resources.

Front 119

What is the default state for AUTO_CLOSE

Back 119

Disable. Never set it to a DB that is frequently accessed as it is going to cause severe degradation in performance.

Front 120

AUTO_SHRINK explanation

Back 120

SQL Server periodically checks the space utilization of data and transaction log files. If the space checking algorithm finds a data file that has more than 25% free space, the file automatically shrinks to reclaim disk space.

Front 121

Best practices for AUTO_SHRINK

Back 121

Disabled and manually shrink files only when needed.

Front 122

AUTO_CREATE_STATISTICS

Back 122

SQL Server automatically create statistics that are missing during the optimization phase of query processing. Although this creates some overhead, the benefits worth it.

Front 123

AUTO_UPDATES_STATISTICS

Back 123

Updates out-of-date statistics during query optimization.

Front 124

AUTO_UPDATE_STATISTICS_ASYNC

Back 124

Controls whether statistics are updated during query optimization or if query optimization continues while the statistics are updated asynchronously.

Front 125

CHANGE_TRACKING

Back 125

SQL Server 2008. Lightweight mechanism that associates a version with each row in a table that has been enabled for change tracking, Each time the row is changed, the version number is incremented. Applications need only to compare the row version to determinate if a change has occurred.

Front 126

CHANGE_RETENTION

Back 126

After change tracking has been enabled for the DB, you can choose which tables within a DB that change tracking info should be captured. CHANGE_RETENTION options allows you to specify how long the tracking info should be kept.

Front 127

AUTO_CLEANUP option

Back 127

Used by CHANGE_RETENTION option to automatically clean up tracking info.

Front 128

Access to a DB is controlled with this options

Back 128

ONLINE

OFFLINE

EMERGENCY

Front 129

DB is in ONLINE STATE

Back 129

You can perform all operations that that would otherwise be possible.

Front 130

DB is in OFFLINE state

Back 130

The DB is inaccessible.

Front 131

DB is in EMERGENCY state

Back 131

State can be accessed only by a member of the db_owner role, and the only command allowed to be executed is SELECT.

Front 132

DB READ_ONLY mode

Back 132

The DB cannot be written to also SQL Server removes any transaction log file that is specified for the DB.

Front 133

DB mode is set to READ_WRITE mode

Back 133

Causes SQL Server to recreate the transaction log file that has been deleted when the DB was in READ_ONLY.

Front 134

How can you fix a damaged page in SQL 2005?

Back 134

Only way to fix a damaged page was to execute a page restore.

Front 135

What happens to a damaged data page is SQL Serve is part of mirroring session?

Back 135

SQL Server2008 automatically replaces the page with a copy of the page from the mirror. An entry is logged and can be reviewed with the sys.dm_db_mirroring_auto_page repair view

Front 136

sys.dm_db_mirroring_auto_page repair view

Back 136

Allows you to review all logs generated from automatically recovering damaged pages

Front 137

User access options to access DB

Back 137

SINGLE_USER

RESTRICTED_USER

MULTI_USER

Front 138

SINGLE_USER mode

Back 138

Only a single user is allowed to access the DB.

Front 139

RESTRICTED_USER mode

Back 139

Only allow access to members of the db_owner, dbcreator, and sysadmin roles.

Front 140

What happens to a DB when multiple user try to access it but the DB is in mode either SINGLE_USER or RESTRICTED_USER

Back 140

The ALTER DATABASE cmd is locked until all the non-allowed users disconnect.

Front 141

ROLLBACK action

Back 141

Instead of waiting for all users to complete transactions, along with disconnect from the DB you can specify a ROLLBACK action to terminate the connections by force.

Front 142

What happens to values when a DB call is parameterized

Back 142

The values are passed as variables. Applications gain a significant benefit when DB calls are parameterized.

Front 143

What SQL Serve does with every query's plan when the query is executed

Back 143

SQL caches the query plan. When a query is executed, SQL Server parses and compiles the query. Then the query is compared to the query cache. If match is found, SQL Server retrieves the plan that has already been generated and executes the query.

Front 144

PARAMETERIZATION FORCED database option

Back 144

Forces SQL Server to parameterize every query for a given DB.

Front 145

How do you restrict DB access to member of the db_owner role and terminate all active transactions and connection at the same time?

Back 145

You would execute the following command: ALTER DATABASE <DB name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

Front 146

What size are the data pages in SQL

Back 146

8 Kb divided in 1+ blocks of 512 bytes

Front 147

PAGE_VERIFY can be set to:

Back 147

TORN_PAGE_DETECTION

CHECKSUM

Front 148

PAGE_VERIFY TORN_PAGE_DETECTION

Back 148

Exists only for backward compatibility should not be used.

Front 149

PAGE_VERIFY CHECKSUM

Back 149

SQL Server calculates a checksum for the page prior to the write. Each time a page is read off disk, a checksum is recalculated and compared to the checksum write to the page. If the checksum do not match, the page has been corrupted.

Front 150

Best practice for PAGE VERIFY CHECKSUM

Back 150

Should be enabled on every production DB.

Front 151

DBCC CHECKDB

Back 151

You can force SQL Server to read every page from the disk and check the integrity by executing the DBCC CHECKDB cmd

Front 152

DBCC CHECKDB syntax

Back 152

DBCC CHECKDB

[( 'database_name' / database_ID / 0

[ , NOINDEX / { REPAIR_ALLOW_DATA_LOSS / REPAIR_FAST / REPAIR_REBUILD } ]

)]

[ WITH { [ ALL_ERRORMSGS ] [ , [ NO_INFOMSGS ] ] [ , [ TABLOCK ] ]

[ , [ ESTIMATEONLY ] ] [ , [ PHYSICAL_ONLY ] ] } ]

Front 153

What SQL Server does in responses to DBCC CHECKDB

(6)

Back 153

1) Checks page allocation within the db.

2) Checks the structural integrity of all tables and indexed views.

3) Calculates a checksum for every data and index page to compare against the stored checksum.

4) Validates the contents of every indexed view.

5) Checks the DB catalog.

6) Validates Service Broker data within the DB.

Front 154

What cmds. does DBCC CHECKDB to achieve its tasks

(3)

Back 154

1) DBCC CHECKALLOC, to check the page allocation of the DB.

2) DBCC CHECKCATALOG, to check the DB catalog.

3) DBCC CHECKTABLE, for each table and view in the DB to check the structural integrity.

Front 155

schemas explanation

Back 155

In addition to being a security structure, a schema provides a convenient mechanism to group objects together within a DB.

Front 156

syntax to create schema

Back 156

CREATE SCHEMA <schema name> AUTHORIZATION <owner name>

Front 157

Numeric data type - 9

Back 157

TINYINT
SMALLINT
INT
BIGINT
DECIMAL(P,S)
NUMERIC(P,S)
SMALLMONEY
MONEY
REAL
FLOAT

Front 158

Decimal data type precision and scale explanation

Back 158

Decimal data type have two parameters - precision and scale. Precision indicates the total number of digits that can be stored both to the left and to the right of the decimal. Scale indicates the maximum number of digits to the rights of the decimal point.

Front 159

Decimal and Numeric Data Type Storage cost list - 4

Back 159

1 to 9 - 5 bytes
10 to 19 - 9 bytes
20 to 28 - 13 bytes
29 to 38 - 17 bytes

Front 160

Character data type

Back 160

CHAR(n) - 1 byte per char max 8000 non-unicode
VARCHAR(n) - 1 byte per char max 8000 non-unicode
NCHAR(n) - 2 bytes per char max 4000 unicode
NVARCHAR(n) - 2 bytes per char max 4000 unicode

Front 161

Date and Time data types

Back 161

SMALLDATETIME 01/01/1900 to 06/06/2079 accuracy 1 min 4 bytes
DATETIME 01/01/1753 to 12/31/9999 0.0033 8 bytes
DATETIME2 01/01/0001 to 12/31/9999 100 nanons 6-8 bytes
DATETIMEOFFSET 01/01/0001 to 12/31/9999 100 nanos 8-10 bytes
DATE 01/01/0001 to 12/31/9999 1 day 3 bytes
TIME 00:00:00 to 23:59:59 100 nanos 3-5 byte

Front 162

Binary Data

Back 162

BIT Null,0,1 1Bit
BINARY Fixed-length binary data Up to 8000 bytes
VARBINARY Variable-length binary data Up to 8000 bytes

Front 163

XML Data Type

Back 163

Limited to maximum of 2 GB
Has a maximum of 128 levels within a document

Front 164

Schema collection

Back 164

XML schemas are stored within SQL Server in a structure called a schema collection. When a schema is applied to an XML column, the only documents allowed to be stored within the XML column must first validate to the associated XML schema collection

Front 165

spatial data types

Back 165

SQL Server 2008 supports two data types to store spatial data:
GEOMETRY and GEOGRAPHY

Front 166

Using what are the two data types supported by SQL 2008 and in what version of SQL they were introduced

Back 166

Both spatial data types are implemented using CLR and were introduced in SQL Server 2005

Front 167

CLR

Back 167

Common language runtime

Front 168

GEOMETRY column can contain

(7)

Back 168

1) Point
2) LineString
3) Polygon
4) GeometryCollcection
5) MultiPolygon
6) MultiLineString
7) MultiPoint

Front 169

HIERARCHYID data type

Back 169

Used to organize hierarchical data, such as organization charts, bills of materials, and flow charts. Stores position within a tree hierarchy.

Front 170

Column properties list

Back 170

1) NULLABILITY
2) COLLATE
3) IDENTITY
4) ROWGUIDCOL
5) FILESTREAM
6) NOT FOR REPLICATION
7) SPARSE

Front 171

Nullability

Back 171

You can specify whether a column allows null by specifying NULL or NOT NULL for the column properties. If you don't specify a nullability option, SQL Server uses the default option when creating a table.

Front 172

Command to set the default nullability option

Back 172

ANSI_NULL_DEFAULT - the default option is not guaranteed to be the same for each DB you can modify it with the command.

Front 173

COLLATE

Back 173

You can set the COLLATE property of a DB to override the instance collation sequence.

Front 174

What option would you specify on a column to control the collation behavior

Back 174

COLLATE works only of a character-based column

Front 175

IDENTITY

Back 175

Identities are used to provide a value for a column automatically when data is inserted.

1) A column with identity property cannot be updated. 2)Float and real cannot be used as indent.

3)only one identity column per table

Front 176

IDENTITY restrictions

Back 176

1) A column with identity property cannot be updated.
2) Float and real cannot be used as indent.
3) Only one identity column per table

Front 177

How can you insert an explicit value in an indent column?

Back 177

SET IDENTITY_INSERT <table name>

Front 178

How can you change the next value generated in IDENTITY column

Back 178

By using the DBCC CHECKIDENT command

Front 179

DBCC CHECKIDENT

Back 179

How can you change the next value generated in IDENTITY column

Front 180

ROWGUIDCOL

Back 180

Used mainly by merge replication to designate a column that is used to identify rows uniquely across a DB. Ensures that only a single column of this type exist and that the column has a UNIQUEIDENTIFIER value.

Front 181

FILESTREAM

Back 181

BLOBs stored in FILESTREAM are controlled and maintained by SQL Server, but the file resides on the OS.

Front 182

BLOBs

Back 182

Binary large object

Front 183

Benefits of using FILESTREAM and BLOBs

Back 183

1)By storing the data on the file system outside of the DB we are not limited to 2GB.
2)When you back up all files are backed up at the same time

Front 184

To what kind of columns can you apply the FILESTREAM

Back 184

Columns with a VARBINARY(MAX) data type. The column within the table maintain a 16-byte identifier for the file.

Front 185

In what state is a FILEGROUP designated for FILESTREAM

Back 185

1) Storage is off-line
2) Inaccessible within a DB snapshot
3) DB Mirroring cannot be implemented against a DB containing data stored with FILESTREAM

Front 186

NOT FOR REPLICATION

Back 186

Used for column defined with IDENTITY property. When you def. an identity, we specify:

1) Value
2) Seed
3) Increment

If you explicitly insert a value in the column SQL reseeds the column. If the table is participating in replication, you don't want to reseed the identity column each time data is synch. By applying NOT FOR REPLICATION, SQL doesn't reseed the identity column when the replication engine is applying changes.

Front 187

SPARSE requirement

Back 187

To designate a column as SPARSE the column must allow NULLs.

Front 188

SPARSE

Back 188

When a NULL is stored in a column designated as SPARSE no storage space is consumed. However, non-NULL values require 4 bytes extra for storage

Front 189

You cannot apply SPARSE to

Back 189

1) Columns with ROWGUIDCOL
2) Columns with IDENTITY
3) TEXT, NTEXT, IMAGE, TIMESTAMP, GEOMETRY, GEOGRAPHY
4) User defined data types
5) VARBINARY(MAX) with FILESTREAM
6) Computed column
7) Column within an ALTER TABLE statement

Front 190

Limitation for using SPARSE

Back 190

If the max size of row is over 4009 bytes ALTER TABLE cannot be issued. During ALTER TABLE each row is recomputed by writing a second copy of the row on the same data page. When row is copied if it exceeds 4009 this will cause the data page to exceed 8018 bytes

Front 191

Computed Columns

Back 191

Allows to add to a table columns that, instead of being populated with data, are calculated based on other columns in a row.

Front 192

PERSISTED property for Computed Columns

Back 192

When you create a computed column, only the definition of the calculation is stored. When you use the computed column within any DML statement, the value is calculated at runtime. Using PERSISTED causes SQL to store the result of the calculation in the row and update the value anytime data participating in the calculation is changed

Front 193

Row and Page Compression

Back 193

SQL Server 2008 allows to compress rows and pages for tables that do not have a SPARSE columns, as well as for indexes and indexed views

Front 194

Heap

Back 194

Table without a clustered index

Front 195

What happens when apply compression to a heap?

Back 195

Compresses only the pages that currently exist in the table

Front 196

When data added to the heap is compressed?

Back 196

Only if BULK INSERT or INSERT INTO...WITH(TABLOCK) statements are used

Front 197

What you need to execute to compress newly added pages?

Back 197

ALTER TABLE...REBUILD statement with the PAGE compression option

Front 198

Does compression setting for a table pass to any nonclustered indexes or indexed views created against a table

Back 198

Does not, you need to specify for each nonclustered index or indexed view

Front 199

What data types store data outside of the table?

Back 199

1) VARCHAR(MAX)
2) NVARCHAR(MAX)
3) VARBINARY(MAX)

Front 200

Is data stored outside of the row in a table compressed?

Back 200

No

Front 201

Columns in standard SQL Server table

Back 201

1024

Front 202

Using column set definition in conjunction with the new sparse column capabilities how many columns can you have in a DB

Back 202

Up to 30000

Front 203

Wide tables

Back 203

Tables that exceed 1024 columns by using a column set definition, but the data stored in any row cannot exceed 8019 unless we have:

1) VARCHAR(MAX)
2) NVARCHAR(MAX)
3) VARBINARY(MAX)

Front 204

Temporary tables are stored in ______ and can be either _____ or ______

Back 204

Temporary tables are stored in TEMPDB and can be either LOCAL or GLOBAL.

Front 205

Local temporary table

Back 205

Designated by name starting with # and visible only within the connection that created it.

Front 206

Global temporary table

Back 206

Name begins with ## and is visible across all connections.

Front 207

Table variables

Back 207

Created to pass sets of data within stored procedures and functions. Stored in the memory it is visible only within the connection that declared it.

Front 208

With what cmd can table variables be populated

Back 208

1) Insert
2) Update
3) Delete
4) Merge
5) Participate in JOIN

Front 209

primary keys

Back 209

1 primary key constraint per table. Defines the column(s) that uniquely identify every row in table. You specify all columns within the primary key as NOT NULL.

Front 210

Clustered primary key

Back 210

The default SQL Server behavior, causes SQL Server to store the table in sorted order according to the primary key.

Front 211

Default option for primary key is

Back 211

Clustered. When a clustered primary key is created on a table that is compressed, the compression option is applied to the primary key when the table is rebuilt.

Front 212

Foreign key

Back 212

Implemented to provide referential integrity between tables within DBs. If you have a multicolumn primary key, all the columns from the parent primary key must exist in the child table to define a foreign key.

Front 213

CASCADE option for a foreign key

Back 213

You can configure a foreign key such that modification of the parent are cascaded to the child table. Cascading is a bad idea.

Front 214

Unique constraints

Back 214

Define a column or columns for which the values must be unique within the table. Duplicate entries are not allowed.

Front 215

Dif. between unique constrains and primary keys

Back 215

Unique constraint allows NULL.

Primary key doesn't allow NULL.

Front 216

Default constrains

Back 216

Specify a value that is written to the column of the application does not supply a value. Can define for NULL or NOT NULL columns.

Front 217

Default constrains apply only for rows added with

Back 217

INSERT

BCP

BULK INSERT

Front 218

Check constrains

Back 218

Limit the range of values within a column. Can be column level and table level.

Front 219

Column level check constrains

Back 219

Are not allowed to reference any other column in the table.

Front 220

Table-level check constrains

Back 220

Can reference any column within a table, but they are not allowed to reference columns in other tables.

Front 221

Add primary key cmd

Back 221

ALTER TABLE <tb name>

ADD CONSTRAINT pk_customer PRIMARY KEY CLUSTERED (Customer ID)

GO

Front 222

Add foreign key cmd

Back 222

ALTER TABLE <tb name>

ADD CONSTRAINT fk_order FOREIGN KEY CLUSTERED

(Customer ID)

REFERENCE test.Customer (Customer ID)

GO

Front 223

Default constraint cmd

Back 223

ALTER TABLE <tb name>

ADD CONSTRAINT df_customer DEFAULT (GETDATE()) FOR CreationDate

GO

Front 224

Check constraint

Back 224

ALTER TABLE <tb name>

ADD CONSTRAINT ck_customer CHECK (Subtotal > 0)

GO

Front 225

What structure SQL uses to build and maintain indexes?

Back 225

Balanced tree aka b-tree

B-tree is always symmetrical

Front 226

How big a data page?

Back 226

8192 bytes, but only 8060 bytes of actual user data

Front 227

What are the three type of pages within an index?

Back 227

Index can contain

Root pages, Intermediate pages, and Leaf pages

Front 228

How you define an index

Back 228

By using one or more columns in the table, called the index key.

Front 229

What is the maximum amount of columns that can participate in index?

Back 229

16 column

Front 230

What is the maximum size of the index key?

Back 230

900 bytes

Front 231

clustering key

Back 231

Columns defined for the clustered index.

Front 232

What clustered key forces SQL to do?

Back 232

It causes SQL Server to arrange the data in the table according to the clustering key. Because you can arrange the table on only one way you can have only one clustered index per table.

Front 233

heap

Back 233

A table without an index. SQL Server allocates pages and stores data as data is written to the table.

Front 234

page chain of index

Back 234

Show how the page is sorted logical.

Front 235

nonclustered index

Back 235

Built on the data that is stored, with the leaf level of the indexes containing a pointer to the location of the row in tables data page.

Front 236

forwarding pointer

Back 236

When SQL Server must move the row by subsequent modifications (page split, row no longer fitting in page), nonclustered indexes don't get updated with the new location of the row instead a forwarding pointer to the new location is created.

Front 237

FILESTREAM_ON clause

Back 237

Used when clustered indexes are created on a table containing FILESTREAM data.

Front 238

What happens if a different filegroup is specified in FILESTREAM_ON clause than where the FILESTREAM data is currently located

Back 238

All FILESTREAM data will be moved to the newly specified filegroup during the creation of the clustered index.

Front 239

How SQL Server physically implements a primary key

Back 239

As unique clustered index.

Front 240

default option for a primary key

Back 240

Clustered

Front 241

How SQL Serve physically implements unique constraint

Back 241

As unique index.

Front 242

nonclustered index

Back 242

Do not impose a sort order on the table

There can be up to 1000
No more than 900 bytes
No more than 16 columns

Front 243

covering index

Back 243

When an index is constructed in such way that can completely satisfy queries by reading only the index.

Front 244

INCLUDE clause when creating a new index

Back 244

Indexes can be created using the INCLUDE clause. Included columns become part of the index at only the leaf level.

Front 245

What determines if an index should be used to satisfy a query

Back 245

The query optimizer based on the distribution statistics.

Front 246

histogram

Back 246

When an index is created a structure is generate called histogram that stores info about the relative distribution of data values within the column.

Front 247

selectivity of an index

Back 247

The degree to which values in the column allow to locate small sets of data.

Front 248

query optimizer uses the most ____ index to satisfy a query because a highly selective index allows the query processor to skip large amounts of data

Back 248

Selectivity

Front 249

filtered index

Back 249

An index with a WHERE clause . Only the index keys matching the WHERE clause are added to the index. This allows to build indexes that focus on highly selective portions of the table while allowing SQL to use other methods for less selective ranges.

Front 250

restrictions for filtered indexes(3)

Back 250

1) Must be nonclustered
2) Cannot be created on computed columns
3) Columns cannot undergo implicit or explicit data type conversion

Front 251

FILLFACTOR option (for when creating an index)

Back 251

To control the rate at which page splits occur we specify the fill factor for the index. FILLFACTOR specifies the percentage of free space that should be left on the leaf level of an index during creation or rebuild.

Front 252

FILLFACTOR applies only to the ____ level of the index

Back 252

Leaf

Front 253

PAD_INDEX option (for when creating an index)

Back 253

Used when there is a high amount of leaf level pages. Will cause the FILLFACTOR to be applied to the intermediate-level pages of the root page off an index.

Front 254

SORT_IN_TEMPDB option (for when creating an index)

Back 254

By default the worktables are created in the same DB as the index. They can be forced to be created in the TEMPDB.

Front 255

IGNORE_DUP_KEY option

Back 255

Lets have an unique index and do a multi-row insert if any of the rows in the insert is duplicating an existing in table index all rows will be drop. With IGNORE_DUP_KEY only the duplicate row will be doped from the insert all others will be accepted.

Front 256

WITH ONLINE = OFF option

Back 256

With this option SQL Server will lock the entire table while building the index.

Front 257

WITH ONLINE = ON option

Back 257

SQL Server allows changes to the table during the creating of the index by using the version store within the tempdb.

Front 258

Online index creation/rebuild

Online restore

are available only in

Back 258

SQL Server 2008 enterprise

Front 259

XML index

Back 259

To improve performance of queries against XML data.

Front 260

There are two types of XML indexes

Back 260

Primary
Secondary

Front 261

Primary XML index

Back 261

Build against all the nodes within XML column. Tied to the table by maintaining a link to the corresponding row in the clustered index. That is why a clustered index required before you can create a primary XML index.

Front 262

Secondary XML index

Back 262

After you create a primary XML index you can have a secondary. This secondary index is built against:

PATH
VALUE
PROPERTY

Front 263

Spatial index

Back 263

Created against a spatial column and is either geometry or geography.

Front 264

Tessellation

Back 264

Spatial data is defined using 2d coordinate system. Indexes are built in B-trees, linear structure. So to index spatial data SQL transforms 2d data into linear chain. This transformation is called tessellation.

Front 265

Types of XML indexes

Back 265

PATH
VALUE
PROPERTY

Front 266

Index fragmentation

Back 266

When an index is built, all the values from the index key are written in sorted order onto the pages. Removal of values creates "holes" on the index page. If data in column that the index is based on changes that causes the index to be moved which leaves another "hole". When index page fills up and require a page split additional "holes" might be created.

Front 267

ALTER INDEX cmd use

Back 267

Statement to remove the fragmentation.

Front 268

REBUILD option for ALTER INDEX

Back 268

Rebuilds all levels of the index and leave all pages filled according to the FILLFACTOR of an index.

Front 269

REORGANIZE option for ALTER INDEX

Back 269

Removes fragmentation only at leaf level. Intermediate-level pages and the root page are not defragmented during reorganize. Always online option.

Front 270

What type of lock is applied on a table during index rebuild?

Back 270

The rebuild of an index effectively re-creates the entire B-tree structure, so unless the ONLINE options is specified, shared table lock is acquired preventing any changes until the rebuild operation completes.

Front 271

Disabling index(cmd)

Back 271

ALTER INDEX { index name! ALL } on <object/

DISABLE [ ; ]

Front 272

If a clustered index is disabled, what happens to the table?

Back 272

The entire table becomes inaccessible.

Front 273

Enable index(cmd)

Back 273

ALTER INDEX { index name! ALL } on <object/

REBUILD [ ; ]

Front 274

What happens when an index is disabled?

Back 274

An index that is disabled is no longer used by the optimizer. In addition, as data changes in the table, any disabled index is not maintained.

Front 275

Spatial index

Back 275

Constructed by mapping the spatial objects to a four-level uniform grid and applying tessellation rules

Front 276

Fix defragmented indexes

Back 276

REBUILD or REORGANIZE option of ALTER INDEX statement.

Front 277

Full text catalogs

Back 277

The first step in building a full text index is to create a storage structure. Unlike relational indexes, full text indexes have a unique structure that is maintained within a separate storage format called a full text catalog. Each full text catalog contains one or more full text indexes.

Front 278

Is SQL Server 2008 where is the content of a full text catalog stored?

Back 278

Stored within the DB.

Front 279

Is pre 2008 SQL, where is the content of a full text catalog stored?

Back 279

You associated the full text catalog with a filegroup only for backup process, with the entire contents of the catalog maintained in a directory structure on the OS.

Front 280

Generic syntax for creating a full text catalog.

Back 280

CREATE FULLTEXT CATALOG catalog_name

[ON FILEGROUP filegroup ]
[IN PATH 'rootpath']
[WITH <catalog_option>]
[AS DEFAULT]
[AUTHORIZATION owner_name ]

<catalog_option>::=

ACCENT_SENSITIVITY = {ON/OFF}

Front 281

CREATE FULLTEXT CATALOG catalog_name

[ON FILEGROUP filegroup ]
[IN PATH 'rootpath']
[WITH <catalog_option>]
[AS DEFAULT]
[AUTHORIZATION owner_name ]

<catalog_option>::=

ACCENT_SENSITIVITY = {ON/OFF}

What is the FILEGROUP option for?

Back 281

specifies the filegroup that you want to use to store any full text indexes created with the full text catalog

Front 282

CREATE FULLTEXT CATALOG catalog_name

[ON FILEGROUP filegroup ]
[IN PATH 'rootpath']
[WITH <catalog_option>]
[AS DEFAULT]
[AUTHORIZATION owner_name ]

<catalog_option>::=

ACCENT_SENSITIVITY = {ON/OFF}

What is the IN PATH option for?

Back 282

its a depreciated clause should not be used any more, because full text indexes are now stored in the DB

Front 283

Full text index

Back 283

after you create the full text catalog, you can create the full text indexes that are the basis for searching unstructured data

Front 284

On what columns can you create full text indexes

Back 284

CHAR
VARCHAR
XML
VARBINARY

Front 285

full text index on a CHAR/VARCHAR column

Back 285

The full text engine parses the data directly and builds an appropriate index.

Front 286

full text index on a XML column

Back 286

Loads a special processor that can understand and parse an XML document so that we are indexing the XML document but no the XML tags.

Front 287

most common use of VARBINARY(MAX) columns

Back 287

To store documents using the new FILESTREAM capabilities in SQL.

Front 288

word breakers

Back 288

Assemblies that locate breaks between words to build a list of words to be indexes.

Front 289

stemmers

Back 289

Stemmers allows queries to locate information even across multiple verb tenses.

Front 290

stop word

Back 290

For example a, the, an are stop words for English.

Front 291

Full text index limitation

Back 291

You can create full text indexes on multiple columns; however, you can create only a single full text index on a table or indexed view.

Front 292

CHANGE_TRACKING option

Back 292

This full text index option determines how SQL Server maintains the index when the underlying data changes.

Front 293

What commands SQL provide to query full text data

Back 293

CONTAINS
FREETEXT
CONTAINSTABLE
FREETEXTTABLE

Front 294

What CONTAINS, FREETEXT return

Back 294

Return a True/False value used to restrict a result set.

Front 295

what CONTAINSTABLE, FREETEXTTABLE return

Back 295

Return a result set that can be used to extend query functionality.

Front 296

FREETEXT example

Back 296

These queries are the most basic form of full text search:

SELECT ProdDiscID, Description
FROM Prod.ProductDescription
WHERE FREETEXT(Description, N'bike')
GO

Front 297

What happens to a query when we use non-Unicode string

Back 297

All search terms used with full text are Unicode string. If a non-Unicode string is passed, the query still works, but is much less efficient because the optimize cannot use parameter sniffing to evaluate distribution statistics on the full text index. Make certain that all terms you pass in for full text search are always typed as Unicode for maximum performance.

Front 298

FREETEXTTABLE example

Back 298

Returns a result set with additional info that ranks the results in accordance to how close the match was to the original search term.

SELECT a.ProductDescriptionID, a.Description, b.*
FROM Production.ProductDescritpion a

INNER JOIN FREETEXTABLE(Production.ProductDescription, Description, N'bike') b ON a.ProductDescriptionID = b.[Key]

ORDER BY b.[Rank]

GO

Front 299

CONTAINS explanation

Back 299

For queries that require greater flexibility, you would use the CONTAINS predicate, which allows you to:

1) Search word forms
2) Search for word proximity
3) Provide relative weighting to terms

Front 300

CONTAINS example

Back 300

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N'bike')

Front 301

What options can you use if you want to search for word variants

Back 301

FORMSOF - consider word stems
INFLECTIONAL -
THESAURUS - produces synonyms for the search term

Front 302

where are the thesaurus files

Back 302

A thesaurus file exist for each supported language. All thesaurus files are XML files stored in the FDATA directory underneath SQL installation path.

Front 303

NEAR keyword

Back 303

Because full text indexes are built against unstructured data, the index stores the proximity of one word to another in addition to indexing the words found within the data. Proximity searching is accomplished by using the NEAR keyword.

Front 304

Which cmd perform fuzzy searching by default?

Back 304

FREETEXT and FREETEXTTABLE predicates perform wildcard searches by default.

Front 305

Which cmd perform proximity and synonym searches?

Back 305

CONTAINS and CONTAINSTABLE are used for proximity, thesaurus and inflectional searches.

Front 306

For what cmd SQL Server uses the language-specific thesaurus automatically

Back 306

FREETEXT and FREETEXTTABLE queries.

Front 307

What cmd key you need to specify to turn on thesaurus for CONTAINS and CONTAINSTABLE queries

Back 307

FORMSOF THESAURUS

Front 308

stop list aka noise word files

Back 308

Used to exclude words that you do not want to include in a full text index.

Front 309

In SQL Server 2005 and prev. where is the noise word files stored

Back 309

FDATA Directory

Front 310

In SQL Server 2009 where is the noise word files stored

Back 310

Within a DB on the server.

Front 311

Option for populating full text indexes

Back 311

1) FULL
2) INCREMENTAL
3) UPDATE

Front 312

FULL option for populating full text index exp.

Back 312

Reprocesses every row from the underlying data to rebuild the full text index completely.

Front 313

INCREMENTAL option for populating full text index exp.

Back 313

Processes only the rows that have changed since the last population; requires a timestamp column on the table.

Front 314

UPDATE option for populating full text index exp.

Back 314

Processes any changes since the last time the index was updated; requires that the CHANGE_TRACKING options is enabled for the index and set to MANUAL.

Front 315

How you initiate a full text index

Back 315

ALTER FULLTEXT INDEX statement

Front 316

How you manage thesaurus files

Back 316

By editing the language-specific file that is contained within the FDATA directory for the instance.

Front 317

What is the cmd for creating a stop list

Back 317

CREATE FULLTEXT STOPLIST

Front 318

What is the cmd for changing a stop list

Back 318

ALTER FULLTEXT STOPLIST

Front 319

What cmd is used to associate a stop list with a full text index

Back 319

ALTER FULLTEXT INDEX

Front 320

In what version was table partitioning introduced

Back 320

2005

Front 321

What is table partitioning used for?

Back 321

Means to split large tables across multiple structures.

Allows tables, indexes and indexed views to be created on multiple filegroups while also allowing the DBA to specify which portion of the object will be stored on a specific filegroup

Front 322

Partition function

Back 322

Defines the set of boundary point for which data will be partitioned.

Front 323

Example for partition function

Back 323

CREATE PARTITION FUNCTION
mypartfunction (int)
AS RANGE LEFT
FOR VALUES (10,20,30,40,50,60)

Front 324

What data type cannot be used for partition function

Back 324

1) text
2) ntext
3) image
4) varbinary(max)
5) timestamp
6) xml
7) vacrchar(max)

Cannot use also
Transact-SQL user define data types
CLR data types

Front 325

What is the AS clause used for in CREATE PARTITION FUNCTION

Back 325

Allows you to specify whether the partition function you are creating is RANGE LEFT or RANGE RIGHT. The LEFT and RIGHT parameters define which partition will include a boundary point.

Front 326

What is the FOR VALUES clause used for in CREATE PARTITION FUNCTION

Back 326

Used to specify the boundary points for the partition function.

Front 327

Maximum amount of partitions for an object? And maximum amount of boundary points?

Back 327

partitions 1000

points 999

Front 328

CREATE PARTITION FUNCTION

mypartfunction (int)

AS RANGE LEFT

FOR VALUES (10,20,30,40,50,60)



representation

Back 328

Partition--min val--ax val

1 --neg inf--10
2 --11 --20
3 --21 --30
4 --31 --40
5 --41 --50
6 --51 --60
7 --61 --pos inf.

Front 329

CREATE PARTITION FUNCTION

mypartfunction (int)

AS RANGE RIGHT

FOR VALUES (10,20,30,40,50,60)



representation

Back 329

Partition--min val--ax val

1 --neg inf--9
2 --10 --19
3 --20 --29
4 --30 --39
5 --40 --49
6 --50 --59
7 --60 --pos inf.

Front 330

Can you partition an existing object after it has been populated with data?

Back 330

Yes

Front 331

What you do to partition an existing table

Back 331

Drop the clustered index and re-create the clustered index on the partition scheme

Front 332

Partition an existing index or indexed view

Back 332

Drop the index and re-create the index on a partition scheme

Front 333

partition schema

Back 333

Defines the storage structures and collection of filegroups that you want to use with a given partition function

Front 334

Can partition schema encompass more than one filegroup

Back 334

Yes

Front 335

create partition cmd

Back 335

CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name / [ PRIMARY ] } [ ,...n ] )
[ ; ]

Front 336

CREATE PARTITION SCHEMA requirements

Back 336

1) Any filegroup specified in CREATE PARTITION SCHEMA must already exist in the DB

Front 337

What if you specify ALL keyword in CREATE PARTITION SCHEMA

Back 337

You can specify maximum of one filegroup

Front 338

Requirements for partition schemes and filegroup

Back 338

A partition scheme must be defined in such a way as to contain a file group for each partition that is created by the partition function mapped to the partition scheme

Front 339

example partitioned table

Back 339

CREATE TABLE Employee (
EmpID int NOT NULL,
FirstN varhar(50) NOT NULL,
LastN varchar(50) NOT NULL,)
ON myparscheme(EmpId)

Front 340

What is the final step in creating a partitioned table or index?

Back 340

To create a the table or index on a partitioned scheme through a partitioning column

Front 341

req. partition key

Back 341

The partitioning key that is specified must match the data type, length, and precision of the partition function.

Front 342

What if a partitioning key is a computed column

Back 342

The computed column must be PERSISTED

Front 343

creating a partitioned index

(example)

Back 343

CREATE NONCLUSTERED INDEX idx_employeefirstname
ON dbo.Employee(FirstName) ON mypartscheme(EmployeeID)
GO

Front 344

creating a partitioned index

(expl)

Back 344

Similar to creating a partitioned table, you partition an index by specifying a partition scheme in the ON clause.

Front 345

What if an index has the optional INCLUDE clause?

Back 345

When you create an index on a partitioned table, SQL Server automatically includes the partitioning key in the definition of each index, thereby allowing you to partition an index the same way as the table is partitioned.

Front 346

Which clause of the CREATE TABLE or CREATE INDEX statements is used to partition the object?

Back 346

The ON clause is used so specify the storage structure, filegroup, or partition scheme, for the table or index.

Front 347

SPLIT operator

Back 347

Introduces a new boundary point into a partition function.

Front 348

MERGE operator

Back 348

Eliminates a boundary point from a partition function.

Front 349

SPLIT / MERGE syntax

Back 349

ALTER PARTITION FUNCTION partition_function_name()
{
SPLIT RANGE ( boundary_value )
/ MERGE RANGE ( boundary_value )
} [ ; ]

Front 350

Altering a partition scheme

Back 350

Add filegroups to an existing partition scheme to create more storage space for a partitioned table.

Front 351

ALTER PARTITION SCHEME syntax

Back 351

ALTER PARTITION SCHEME partition_scheme_name
NEXT USED [ filegroup_name ] [ ; ]

Front 352

What aligned means when talking about tables, indexes and partition functions?

Back 352

If a table and all its indexes are partitioned using the same partition function they are said to be aligned.

Front 353

SWITCH

Back 353

Allows you to exchange partitions between tables in a perfectly scalable manner with no locking, blocking or deadlocking.

Front 354

SWITCH requirements

Back 354

1) The data and index for the source and target tables must be aligned.
2) Source and target tables must have the same structure.
3) Data cannot be moved from one filegroup to another.
4) Two partition with data cannot be exchanged.
5) The target partition must be empty.
6) The source or target table cannot participate in replication.
7) The source or target tables cannot have fill text indexes or a FILESTREAM data type defined.

Front 355

Can you move data from one filegroup to another or exchange two partitions with data using the SWITCH operations

Back 355

No

Front 356

BCP queryout argument

Back 356

If you want to export only a subset if a table or the result set of a query, you can replace the name of the table with query delimited by double quotes and then specify the queryout parameter. Allow only to export data.

Front 357

BCP's -n switch

Back 357

Specifies that the data in the file is in the native format of SQL Server.

Front 358

BCP's -c switch

Back 358

Specifies that the data is in character format.

Front 359

BCP's -S switch

Back 359

Specifies the instance name to connect to.

Front 360

BCP's -T switch

-U and -P

Back 360

Designates a trusted connection.

Specify Username and Password.

Front 361

What is the draw back of BCP?

Back 361

Its a command line utility

Front 362

dif. between BCP and BULK INSERT

Back 362

1) BULK INSERT cannot export

2) Its T-SQL cmd and does not need to specify the instance name or login credentials.

Front 363

facets

Back 363

Core object upon which your standard are built. Define type of object or option to be checked.

74 by default implemented as .Net assemblies

Front 364

how to get a list of available facets

Back 364

dbo.syspolicy_management_facets

Front 365

DMF

SMO

Back 365

Eeclarative management framework

Server management objects

Front 366

conditions

Back 366

Within the Policy Based Management framework, conditions are the equivalent of a WHERE clause that define the criteria needing to be checked.

Front 367

policy target

Back 367

You can target a policy at the server level or user DB level like system DB and user DB.

Front 368

policies

Back 368

Policies are created for a single condition and set to either enforce or check compliance.

Front 369

Policies execution modes

Back 369

1) On Demand - evaluates the policy when directly executed by a user
2) On Change, prevent - create DDL triggers to prevent a change that violates the policy
3) On change, log only - checks the policy automatically when a change is made using the event notification infrastructure
4) On schedule - creates a SQL Server Agent job to check the policy on a defined schedule

Front 370

policy categories

Back 370

Can be used to group one or more policies into a single compliant unit

Front 371

subscriptions

Back 371

To check and enforce policies you create a subscription to one or more policies

Front 372

2 types of subscriptions

Back 372

Instance and database.

Member of sysadmin role can subscribe an instance to a policy category

Front 373

What are the five objects that are used within Policy Based Management?

Back 373

The objects that are used with Policy Based management are:
1) Facets
2) Conditions
3) Policies
4) Policy targets
5) Policy category

Front 374

What are the allowed execution modes for a policy>

Back 374

On demand
On schedule
On change
Log only
On change, prevent

Front 375

Which object has a property that allows you to mandate checking for all DB on an instance

Back 375

Policy categories allow you to mandate checking of all DB within an instance

Front 376

How many facets can be checked within a single condition

Back 376

1

Front 377

How many conditions can be checked within a single policy

Back 377

1

Front 378

LSN

DCM

DMK

Back 378

Log sequence number

Differential change map

DB master key

Front 379

differential change map

Back 379

SQL Server tracks each extent that has been changed following a fill backup using a special page in the header of a DB called DCM.

Front 380

What are the four type of backup in SQL?

Back 380

1) Full

2) Differential

3) Transaction log

4) File/filegroup

Front 381

How can you detect and log corrupt page?

Back 381

ALTER DATABASE <dbname> SET PAGE_VERIFY CHECKSUM

Front 382

Under what account are backups executed

Back 382

SQL Server Service Account

Front 383

Member of sysadmin server role can backup ....?

Back 383

Backup any DB in an instance

Front 384

Member of db_owner database role can backup ...?

Back 384

Can back up their DB

Front 385

Member of db_backupoperator server role can backup ...?

Back 385

Allow the user to back up a DB while preventing any other access to the DB

Front 386

full backup

Back 386

Captures all pages within a DB that contain data. Empty pages are not included

Front 387

Steps performed by SQL to ensure concurrent access and backup consistency

Back 387

1) Locks the DB, blocking all transactions
2) Places a mark in the transaction log
3) Releases the DB lock
4) Extract all pages in the data files and writes them to the backup service
5) Lock the DB, blocking all transactions
6) Places a mark in the transaction log
7) Releases the DB lock
8) Extract the portion of the log between the marks and appends it to the backup

Front 388

generic syntax to back up a DB

Back 388

BACKUP DATABASE { database_name / @database_name_var }

TO <backup_device> [ ,...n ]
[ <MIRROR TO clause> ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL / <general_WITH_options> [ ,...n ] } ]

[;]

Front 389

When SQL Server strips backup across multiple devices what is the requirement for the devices

Back 389

All devices are required

Front 390

Example stripped backup

Back 390

BACKUP DATABASE AdvWorks

TO DISK = 'AdvWorks_1.bak', DISK = 'AdvWorsk_2.bak'

GO

Front 391

How many copies can you create using the MIRROR TO clause

Back 391

up to 4

Front 392

MIRROR TO requirements

Back 392

1) All backup devices must be of the same media type.
2) Each mirror must have the same number of backup devices FORMAT must be specified in the backup cmd.
3) WITH

Front 393

INIT/NOINIT options

Back 393

Options of a BACKUP cmd control whether an existing backup file is overwritten or appended to. NOINIT appends.

Front 394

LSN

Back 394

Log Sequence Number.
Integer value that starts at 1 and increments. to infinity. Its never reused.

Front 395

DCM

Back 395

Differential change map
A special page used to track differential backups.
Full backup zeros the page.
As changes are made corresponding bits are set to 1. Using the info form the page SQL knows what needs to be backed up

Front 396

COPY_ONLY option

Back 396

Allows to create a backup, but does not affect the database state or set of backups in production. Full backup with COPY_ONLY doest reset the DCM. Transaction log backup with COPY_ONLY does not remove transactions for the transaction log

Front 397

Filegroup backups

Back 397

If you need to reduce the footprint of a backup, you can rely on file or filegroup backup instead.

Front 398

Partial backups

Back 398

Filegroup can be marked as read-only. Because the purpose of a backup is to capture the changes there is no need to backup a readonly filegroup. Performed by specifying the READ_WRITE_FILEGROUPS option

Front 399

Page corruption

Back 399

By executing
ALTER DATABASE <dbname> SET PAGE_VERIFY_CHECKSUM
SQL can detect and quarantine corrupted pages

Front 400

service master keys

Back 400

Each instance have a service master key. Created automatically the first time that an instance is started. Regenerated each time SQL service account or service account password is changed.

Front 401

DMK

Back 401

Database Master Key

Is the root of the of the encryption hierarchy in a DB. To ensure access to certificates, asymmetric keys and symmetric key within the DB you need to backup the DMK

Front 402

what are the 4 types of backup?

Back 402

Full
Differential
Transaction log
File/filegroup backup

Front 403

DB snapshots

Back 403

Introduced in SQL Server 2005 providing a method to create read-only copies of the data in particular point in time.

Front 404

DB snapshots and FILESTREAM feature

Back 404

DB snapshot is not compatible with FILESTREAM. If you create a DB against a DB with FILESTREAM data, the FILESTREAM filegroup is disabled and not accessible

Front 405

How you create a DB snapshot

Back 405

CREATE DATABASE command with the AS SNAPSHOT OF clause

Front 406

MSMQ

WMI

Back 406

MSMQ - Microsoft Message Queue
WMI - Windows Management Instrumentation

Front 407

Microsoft Office Integration(SSRS support)

Back 407

Standard:Yes
Workgroup:Yes
Express:Advanced Services only
Compact:N/A

Front 408

Report Builder(SSRS support)

Back 408

Standard:Yes
Workgroup:Yes
Express:Advanced Services only
Compact:N/A

Front 409

Scale-out Reporting(SSRS support)

Back 409

Standard:No
Workgroup:No
Express:Advanced Services only
Compact:N/A

Front 410

Data-driven subscription(SSRS support)

Back 410

Standard:No
Workgroup:No
Express:No
Compact:N/A

Front 411

Linked measures/dimensions(SSAS OLAP support)

Back 411

Standard:No
Workgroup:No
Express:N/A
Compact:N/A

Front 412

Perspectives(SSAS OLAP support)

Back 412

Standard:No
Workgroup:No
Express:N/A
Compact:N/A

Front 413

Partitioned Cubes(SSAS OLAP support)

Back 413

Standard:No
Workgroup:No
Express:N/A
Compact:N/A

Front 414

Times series(SSAS Data mining support)

Back 414

Standard:No
Workgroup:No
Express:N/A
Compact:N/A

Front 415

Parallel processing and reduction(SSAS Data mining support)

Back 415

Standard:No
Workgroup:No
Express:N/A
Compact:N/A

Front 416

Advanced mining algorithms(SSAS Data mining support)

Back 416

Standard:No
Workgroup:No
Express:N/A
Compact:N/A

Front 417

How the Core SQL Server components run as

Back 417

How the Core SQL Server components run as services.

Front 418

collation sequence

Back 418

Control how SQL Server treats character data for storage, retrieval, sorting, and comparison operation. SQL allows you to specify a collation sequence to support any language currently used around the world.

Front 419

at what levels can collation sequence be specified

Back 419

Instance, db, table, column.

Front 420

Max amount of SQL Server instances on a single server

Back 420

Total of 50 - 1 default and 49 named.

Front 421

SQL Server Instances

Back 421

Define the container for all operations you perform within SQL Server. Each instance contains its own set of DB, security credentials, config settings, Windows services and other SQL Server objects.

Front 422

Why we should not use Windows Service Control applet to change service accounts and passwords

Back 422

The Windows Service Control applet also has entries for SQL Server services and allows you to change service accounts and passwords. You should never change service accounts or service account passwords using the Windows Service Control applet. SQL Server Configuration Manger needs to be used, because it includes the code to regenerate the service master key that is critical to the operations of SQ: Server services.

Front 423

SQL Server Surface Area Configuration Managers

Back 423

A utility that existed in SQL Server 2006 and was removed in SQL Server 2008 in favor of making configuration changes directly using the sp_configure system stored procedure

Front 424

What are the two basic components of Database Mail?

Back 424

Database Mail uses mail profiles which can contain one or more mail accounts

Front 425

What are the two types of mail profiles that can be created?

Back 425

Mail profiles can be configured as either public or private

Front 426

cmd that enables the Database Mail feature?

Back 426

EXEC sp_configure 'Database Mail XPs', 1
GO

RECONFIGURE WITH OVERRIDE
GO

Front 427

Activity Monitor

Back 427

Feature of SSMS that displays query activity on the server

Front 428

Notification Service

Back 428

Is a feature that was available with SQL Server 2005 that is no longer available in SQL Server 2008

Front 429

ON clause

Back 429

All objects that contain data, tables, indexes, and indexed views have an ON clause that you can use to specify the filegroup where SQL Server stores the object. As data is written to the objects, SQL Server uses the filegroup definition to determine on which file it should store the data

Front 430

Can filegroups contain more than one file?

Back 430

Yes

Front 431

Check Pointing

Back 431

All data manipulation within SQL occurs in memory within a set of buffer. If you are adding new data to a database, the new data is first written to a memory buffer, then written to the transaction log, and finally persisted to a data file via a background process called check pointing.

Front 432

When corrupt page is encountered ...

Back 432

When a corrupt page is encountered, the page is logged to the suspects_pages table in the msdb. If the DB is participating in Database Mirroring session, SQL Server automatically retrieves a copy of the page from the mirror, replaces the page on the principal, and logs an entry it the sys.dm_db_mirroring_auto_page_repair view.

Front 433

suspect_pages table in msdb

Back 433

Log of all corrupt pages.

Front 434

sys.dm_db_mirroring_auto_page_repair view

Back 434

Holds a logs of every time a copy of the page is brought from the mirror to the principal when the server is part of db mirroring.

Front 435

Simple syntax to create a schema

Back 435

CREATE SCHEMA <schema name> AUTHORIZATION <owner name>

Front 436

Each index created causes performance degradation for the following (5) cmd

Back 436

INSERT
UPDATE
DELETE
BULK INSERT
BCP

Front 437

Create non-clustered index example on Person.Address table

Back 437

CREATE NONCLUSTERED INDEX idc_city ON Person.Address(City) INCLUDE (AddressLine1)

Front 438

Example create a filtered index on Person.Address table

Back 438

CREATE NONCLUSTERED INDEX idx_ciy2 ON Person.Address(City)
INCLUDE (AddressLine1, AddressLine2)
WHERE AddressLine2 IS NOT NULL

Front 439

Create a spatial index on Person.Address table

Back 439

CREATE SPATIAL INDEX sdix_spatiallocation
ON Person.Address(SpatialLocation)
USING GEOGRAPHY_GRID
WITH (GRIDS = (MEDIUM, LOW, HIGH), CELLS_PER_OBJECT = 64);

Front 440

Can you create full text indexes on multiple columns?
What about full text index on a table or indexed view?

Back 440

You can create full text indexes on multiple columns.

You can create only a single full text index on a table or indexed view.

Front 441

CHANGE TRACKING option on CREATE FULL TEXT INDEX

Back 441

CHANGE_TRACKING option for a full text index determines how SQL Server maintains the index when the underlying data changes.

Front 442

Full text index and full text catalog connection

Back 442

Before creating a full text index, you must create a full text catalog that is mapped to a filegroup to contain one or more full text indexes.

Front 443

In what partition are null values stored when we use a partitioning function.

Back 443

Null values are always stored in the leftmost partition.

Until you explicitly specify null as a boundary point and use the RANGE RIGHT syntax then it will be the rightmost partition.

Front 444

What is the requirement for filegroup specified in the CREATE PARTITION SCHEME statement.

Back 444

The file group must already exist in the database.

Front 445

part.func mapped to part.schema

----------------------------------

CREATE PARTITION FUNCTION

mypartfunction (int)
AS RANGE LEFT
FOR VALUES (10,20,30,40,50,60);
GO

CREATE PARTITION SCHEME mypartscheme AS PARTITION mypartfunction TO (Fg1, Fg2, Fg3, Fg4, Fg5, Fg6, Fg7);
GO

Back 445

FG1 neg.inf - 10
FG2 11 - 20
FG3 21 - 30
FG4 31 - 40
FG5 41 - 50
FG6 51 - 60
FG7 61 - pos.imv

Front 446

Partition an index example

Back 446

CREATE NONCLUSTERED INDEX ind_employeefirstname
ON dbo.Employee(FirstName) ON mypartscheme(EmployeeID);
GO

Front 447

If you are exporting data using BCP, the account that BCP is running under needs ________ permissions on the table or view (1)

Back 447

SELECT

Front 448

If you are importing data, the account that BCP is running under needs ____________ permissions (3)

Back 448

ALTER TABLE
INSERT
SELECT

Front 449

When to use SQL Sever Import and Export Wizard.

Back 449

When you want to import/export data directly between source and destination as well as apply transformations and error-handling routines, you can use the capabilities of SSIS to build packages.

Front 450

Backups execute under the security context of what account

Back 450

SQL Server service account

Front 451

Member of the sys admin role can backup

Member of the db_owner can backup

db_backupoperator

Back 451

sys admin can backup any db in an instance

db_owner role can backup only their db

db_backupoperator is allowed to back up any db but has no other access

Front 452

SQL server backup types

Back 452

Full
Differential
Transaction log
Filegroup

Front 453

To execute a transaction log backup, the database must be in what recovery model

Back 453

Full or Bulk-logged
also

Full backup must have been executed and the transaction log must not have been truncated since the last full backup.

Front 454

What is a dirty page?

Back 454

All data changes occur within buffers in memory. When a change is made, the corresponding buffer is modified and a record is added to the transaction log. A modified page in the buffer pool is referred to as a dirty page.

Every dirty page tracks the LSN in the transaction log that corresponds to the change that modified the page in the buffer pool.

When SQL Server executes a checkpoint, all dirty pages in the buffer pool are written out to the data files.

Front 455

Database snapshots

Back 455

Feature introduced in 2005

Method to quickly create read-only copies of the database.

Available only in 2008 enterprise

Front 456

Database snapshots And filestreams

Back 456

The two feature are incompatible.

If you create a database snapshot against a db with filestream data the filestream filegroup is disabled and not accessible.

Front 457

Cmd for creating a db snapshot

Back 457

CREATE DATABASE dnname ON
(NAME = logical_filename,
FILENAME = 'os_filename')
AS SNAPSHOT OF source_db_name

Front 458

Restrictions for db snapshot

Back 458

1) Can't back, restore or detach db snapshot
2) db snapshot must exist on the same instance as the data source
3) Full text indexes are not supported
4) FILESTREAM is not supported, and any FILESTREAM data is inaccessible through a db snapshot
5) Can create a db snapshot against a sys db
6) You can't drop, restore, or detach db that has a db snapshot created against it
7) Can't reference filegroups that are off-line, defunct, or restoring

Front 459

Sparse file

Back 459

When a db snapshot is created SQL server uses sparse file.

Sparse file is an entrain the fat table that consumes no space. As data is added to the file, the file automatically grows.

Front 460

Copy-on-write technology

Back 460

An SQL server mechanism that manages db snapshots when changes occur in source db.

The first time a modification to a data page occurs SQL copies the before image to the db snapshot.

Front 461

Db snapshot max size

Back 461

Because the db snapshot is at the point in time the size is the amount of data that existed in the source db at the time of creation of the snapshot

Front 462

Filestream and DB snapshots notes

Back 462

The two are not compatible, cannot use the DB snapshot for a RESTORE DATABASE operation

Front 463

Each time a job executes, SQL Server logs information into the

Back 463

dbo.sysobjhistory table in the msdb

Front 464

endpoints

Back 464

Control the capability to connect to an instance of SQL Server as well as to dictate the communications methods that are acceptable.

Front 465

endpoints parts

Back 465

transport - TCP or HTTP

payload - defines the basic category of traffic

Front 466

endpoints payloads

Back 466

SOAP
TSQL
SERVICE_BROKER
DATABASE_MIRRORING

Front 467

List valid combinations of endpoint transport and endpoint payload

Back 467

TCP - TSQL
TCP - SERVICE_BROKER
TCP - DATABASE_MIRRORING
HTTP - SOAP

Front 468

endpoint states

Back 468

defines the access security

STARTED - the endpoint is actively listening for connections and will reply to an application

STOPPED - The endpoint is actively listening but returns a connection error to an application

DISABLED - The endpoint does not listen and does not respond to any connection that is attempted

Front 469

possible layers of security for an endpoint

Back 469

1) Endpoint state

2) Permissions to connect to the endpoint - app must have credentials created in Sql server that have CONNECT permission assign

Front 470

Database Mirroring and Service Broker endpoints

Back 470

Provide options to specify the authentication method and the encryption settings.

Front 471

What is the default encryption algorithm between endpoints used by SQL Server?

Back 471

RC4, recommended AES

Front 472

Database mirroring specific arguments

Back 472

PARTNER - participate as principals or as mirror

WITNESS - can participate only as witness

ALL - can function in any role

Front 473

Service Broker specific arguments

Back 473

Authentication mode - NTLM, Kerberos, NEGOTIATE

Encryption - RC4, AES

Message forwarding

MESSAGE_FORWARDING [ENABLED / DISABLED]

Enables messages destined for a different broker instance to be forwarded

When ENABLED you can specify MESSAGE_FORWARDING_SIZE

Front 474

principals

Back 474

Are the means by which we authenticate and are identified within an instance or db

2 major categories:

Logins/users

Groups at instance and db level

Front 475

logins within SQL Server 2008 can be 5 different types

Back 475

Standard SQL Server logins

Windows login

Windows group

Certificate

Asymmetric keys

Front 476

Standard SQL Server login

Back 476

Created by DBA config with username and password.

Stored in msdb and assigned a local security identifier within SQL Server.

Front 477

SQL Server logins mapped to certificates or asymmetric keys

Back 477

possible.

Does not provide a means to authenticate to the instance. Logins mapped to certificates and asymmetric keys are used internally as a security container.

Front 478

CREATE a LOGIN cmd

Back 478

CREATE LOGIN loginName {Options}

Password
Sid
Windows

Front 479

CREATE LOGIN and CHECK_POLICY option

Back 479

The default and recommended setting

SQL Server 2008 will enforce Windows password policy

Front 480

CREATE LOGIN and CHECK_EXPIRATION option

Back 480

Used to prevent brute force attacks

Front 481

ALTER LOGIN <loginname> DISABLE

Back 481

To prevent access you can revoke permissions from a login, after that you will need to reestablish permissions. By disabling the login you can prevent access while keeping the permissions.

Front 482

Roles in SQL Server

Back 482

same functionality as groups in Windows

Front 483

bulkadmin

dbcreator

diskadmin

processadmin

Back 483

bulkadmin - administer BCP and BULK INSERT operations

dbcreator - create DBs

diskadmin - manage disk resources

processadmin - manage connections and start or pause an instance

Front 484

securityadmin

serveradmin

setupadmin

sysadmin

Back 484

securityadmin - create, alter, and drop logins cant change passwords

serveradmin - disk management process management, manage endpoint, change instance settings and shot down the instance

setupadmin - mange linked servers

sysadmin - perform any action within the instance

Front 485

SQL Security works on the Principle of

Back 485

No access by default

You need to be explicitly granted permission to an action or DB

Front 486

CREATE USER syntax

Back 486

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 487

loginless user

Back 487

It is possible to create a user in the database that is not associated to a login.

Front 488

application roles

Back 488

Prior to SQL Server 2005 if you wanted to allow users to access a DB only when a specific application was being used you used an application role. Application role is created with a password and assigned permissions. Users will specify the pass for the application role to gain access to the DB under the application role's security context.

Front 489

loginless users and application roles

Back 489

loginless users are designed to replace application roles.

Users still authenticate to the instance using their credentials.

The users login needs access to the DB. After SQL Server changes the user's context to the DB the user impersonates the loginless user to gain necessary permission. Because the user is authenticating to the instance using his credential SQL can still audit the user activity.

Front 490

db_accessadmin

db_backupoperator

db_datareader

db_datawriter

Back 490

db_accessadmin - add/rem users

db_backupoperator - backup db no restore or view info

db_datareader - issue select against all tables, views, func within a DB

db_datawriter - issue INSERT, UPDATE, DELETE, MERGE against all tables within the DB. Member of this role must be members of the db_datareader role

Front 491

db_ddladmin

db_denydatareader

db_denydatawriter

db_owner

Back 491

db_ddladmin - execute DDL statements

db_denydatareader - prevent SELECT against all tables, views, func within a DB

db_denydatawriter - prevent INSERT, UPDATE, DELETE, MERGE

db_owner - owner of the DB that has full control over the DB and all objects contained within the DB

Front 492

db_securityadmin

public

Back 492

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

public - default group in every DB that all users belong to

Front 493

accounts that are considered administrative accounts

Back 493

members of sysadmin role fixed server role

members of db_owner fixed database role

the sa account

Front 494

securables

Back 494

are the objects on which you grant permissions

Every object is a securable

Securables can be nested

Front 495

schemas

Back 495

Created in a DB require an owner

Provide the containers that own all object within a DB and in turn a schema is owned by a database users.So you can drop an user without affecting the object.

Front 496

Permissions

Back 496

Proved the authority for principals to perform actions within an instance or DB.

You add permission with GRANT

You remove permission with DENY

DENY overrides GRANT

Front 497

REVOKE statement

Back 497

Remove permission entries for the object referenced.

Front 498

Metadata Security

Back 498

SQL Server secures all the metadata within the system such that you can view only the objects within an instance or DB on which you have permission