Deleteme_Ipad_Sort-List4

Front 1

SQL NOT NULL Constraint Example

Back 1

CREATE TABLE Persons

(
P_Id int NOT NULL,
Last Name varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Front 2

SQL UNIQUE Constraint

Back 2

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Front 3

SQL UNIQUE Constraint on CREATE TABLE

Back 3

CREATE TABLE Persons

(
P_Id int NOT NULL,
Last Name varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

Front 4

SQL UNIQUE Constraint on ALTER TABLE

Back 4

ALTER TABLE Persons

ADD UNIQUE (P_Id)

Front 5

to DROP a UNIQUE Constraint

Back 5

To drop a UNIQUE constraint, use the following SQL:

MySQL:

ALTER TABLE Persons
DROP INDEX uc_PersonID

Front 6

SQL PRIMARY KEY Constraint

Back 6

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.

Front 7

SQL PRIMARY KEY Constraint on CREATE TABLE

Back 7

CREATE TABLE Persons

(
P_Id int NOT NULL PRIMARY KEY,
Last Name varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Front 8

for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax

Back 8

CREATE TABLE Persons

(
P_Id int NOT NULL,
Last Name varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

Front 9

SQL PRIMARY KEY Constraint on ALTER TABLE

Back 9

ALTER TABLE Persons

ADD PRIMARY KEY (P_Id)

Front 10

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax

Back 10

ALTER TABLE Persons

ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

Front 11

To DROP a PRIMARY KEY Constraint

Back 11

ALTER TABLE Persons

DROP CONSTRAINT pk_PersonID

Front 12

SQL FOREIGN KEY Constraint

Back 12

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Front 13

SQL FOREIGN KEY Constraint on CREATE TABLE

Back 13

CREATE TABLE Orders

(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

Front 14

Allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax

Back 14

CREATE TABLE Orders

(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

Front 15

SQL FOREIGN KEY Constraint on ALTER TABLE

Back 15

To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following SQL:

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

Front 16

To DROP a FOREIGN KEY Constraint

Back 16

ALTER TABLE Orders

DROP CONSTRAINT fk_PerOrders

Front 17

SQL CHECK Constraint

Back 17

The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

Front 18

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint specifies that the column "P_Id" must only include integers greater than 0.

Back 18

CREATE TABLE Persons

(
P_Id int NOT NULL CHECK (P_Id>0),
Last Name varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Front 19

Allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax

Back 19

CREATE TABLE Persons

(
P_Id int NOT NULL,
Last Name varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

Front 20

SQL CHECK Constraint on ALTER TABLE

Back 20

To create a CHECK constraint on the "P_Id" column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD CHECK (P_Id>0)

Front 21

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax

Back 21

ALTER TABLE Persons

ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

Front 22

To DROP a CHECK Constraint

Back 22

ALTER TABLE Persons

DROP CONSTRAINT chk_Person

Front 23

SQL DEFAULT Constraint

Back 23

The DEFAULT constraint is used to insert a default value into a column.

The default value will be added to all new records, if no other value is specified.

Front 24

SQL DEFAULT Constraint on CREATE TABLE

Back 24

The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:

CREATE TABLE Persons
(
P_Id int NOT NULL,
Last Name varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

Front 25

The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE()

Back 25

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)

Front 26

SQL DEFAULT Constraint on ALTER TABLE

Back 26

To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

Front 27

To DROP a DEFAULT Constraint

Back 27

ALTER TABLE Persons

ALTER COLUMN City DROP DEFAULT

Front 28

SQL CREATE INDEX Statement

Back 28

The CREATE INDEX statement is used to create indexes in tables.

Indexes allow the database application to find data fast; without reading the whole table.

Front 29

SQL CREATE INDEX Syntax

Back 29

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column_name)

Front 30

SQL CREATE UNIQUE INDEX Syntax

Back 30

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

Front 31

CREATE INDEX Example

Back 31

The SQL statement below creates an index named "PIndex" on the "Last Name" column in the "Persons" table:

CREATE INDEX PIndex
ON Persons (Last Name)

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

CREATE INDEX PIndex
ON Persons (Last Name, FirstName)

Front 32

create an index on a combination of columns

Back 32

CREATE INDEX PIndex

ON Persons (Last Name, FirstName)

Front 33

DROP INDEX Syntax for MS SQL Server

Back 33

DROP INDEX table_name.index_name

Front 34

The DROP TABLE Statement

Back 34

The DROP TABLE statement is used to delete a table.

DROP TABLE table_name

Front 35

The DROP DATABASE Statement

Back 35

The DROP DATABASE statement is used to delete a database.

DROP DATABASE database_name

Front 36

The TRUNCATE TABLE Statement

Back 36

What if we only want to delete the data inside the table, and not the table itself?

Then, use the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name

Front 37

SQL ALTER TABLE Statement

Back 37

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Front 38

SQL ALTER TABLE Syntax

Back 38

ALTER TABLE table_name

ADD column_name datatype

Front 39

To delete a column in a table

Back 39

ALTER TABLE table_name

DROP COLUMN column_name

Front 40

use ALTER TABLE to

change the data type of a column in a table, use the following syntax:

Back 40

ALTER TABLE table_name

ALTER COLUMN column_name datatype

Front 41

SQL ALTER TABLE Example



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

Back 41

Now we want to add a column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons
ADD DateOfBirth date

Front 42

Change Data Type Example

Back 42

Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year

Front 43

DROP COLUMN Example

Back 43

Next, we want to delete the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons
DROP COLUMN DateOfBirth

Front 44

SQL AUTO INCREMENT Field

Back 44

Auto-increment allows a unique number to be generated when a new record is inserted into a table.

Front 45

AUTO INCREMENT a Field

Back 45

CREATE TABLE Persons

(
P_Id int NOT NULL AUTO_INCREMENT,
Last Name varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

Front 46

Let the AUTO_INCREMENT sequence start with another value

Back 46

ALTER TABLE Persons AUTO_INCREMENT=100

Front 47

The following SQL statement defines the "P_Id" column to be an auto-increment primary key field in the "Persons" table:

Back 47

CREATE TABLE Persons

(
P_Id int PRIMARY KEY IDENTITY,
Last Name varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Front 48

SQL statement above would insert a new record into the "Persons" table. The "P_Id" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "Last Name" column would be set to "Monsen".

Back 48

INSERT INTO Persons (FirstName,LastName)

VALUES ('Lars','Monsen')

Front 49

SQL statement defines the "P_Id" column to be an auto-increment primary key field in the "Persons" table

Back 49

CREATE TABLE Persons

(
P_Id PRIMARY KEY AUTOINCREMENT,
Last Name varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Front 50

SQL CREATE VIEW Statement

Back 50

In SQL, a view is a virtual table based on the result-set of an SQL statement.

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 one single table.

Front 51

SQL CREATE VIEW Syntax

Back 51

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

Front 52

SQL CREATE VIEW Examples

Back 52

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

We can query the view above as follows:

SELECT * FROM [Current Product List]

Front 53

SQL Updating a View

Back 53

You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax

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

Front 54

SQL Dropping a View

Back 54

You can delete a view with the DROP VIEW command.

SQL DROP VIEW Syntax
DROP VIEW view_name

Front 55

MySQL Date Functions

Back 55

Function Description

NOW() Returns the current date and time

CURDATE() Returns the current date

CURTIME() Returns the current time

DATE() Extracts the date part of a date or date/time expression

EXTRACT() Returns a single part of a date/time

DATE_ADD() Adds a specified time interval to a date

DATE_SUB() Subtracts a specified time interval from a date

DATEDIFF() Returns the number of days between two dates

DATE_FORMAT() Displays date/time data in different formats

Front 56

NOW()

Back 56

Returns the current date and time

Front 57

CURDATE()

Back 57

Returns the current date

Front 58

CURTIME()

Back 58

Returns the current time

Front 59

DATE()

Back 59

Extracts the date part of a date or date/time expression

Front 60

EXTRACT()

Back 60

Returns a single part of a date/time

Front 61

DATE_ADD()

Back 61

Adds a specified time interval to a date

Front 62

DATE_SUB()

Back 62

Subtracts a specified time interval from a date

Front 63

DATEDIFF()

Back 63

Returns the number of days between two dates

Front 64

DATE_FORMAT()

Back 64

Displays date/time data in different formats

Front 65

GETDATE()

Back 65

Returns the current date and time

Front 66

DATEPART()

Back 66

Returns a single part of a date/time

Front 67

DATEADD()

Back 67

Adds or subtracts a specified time interval from a date

Front 68

DATEDIFF()

Back 68

Returns the time between two dates

Front 69

CONVERT()

Back 69

Displays date/time data in different formats

Front 70

SQL Server Date Functionsv

Back 70

Function Description

GETDATE() Returns the current date and time

DATEPART() Returns a single part of a date/time

DATEADD() Adds or subtracts a specified time interval from a date

DATEDIFF() Returns the time between two dates

CONVERT() Displays date/time data in different formats

Front 71

MySQL comes with the following data types for storing a date or a date/time value in the database

Back 71

DATE - format YYYY-MM-DD

DATETIME - format: YYYY-MM-DD HH:MM:SS

TIMESTAMP - format: YYYY-MM-DD HH:MM:SS

YEAR - format YYYY or YY

Front 72

SQL Server comes with the following data types for storing a date or a date/time value in the database

Back 72

DATE - format YYYY-MM-DD

DATETIME - format: YYYY-MM-DD HH:MM:SS

SMALLDATETIME - format: YYYY-MM-DD HH:MM:SS

TIMESTAMP - format: a unique number

Front 73

OrderId ProductName OrderDate

1 Geitost 2008-11-11
2 Camembert Pierrot 2008-11-09
3 Mozzarella di Giovanni 2008-11-11
4 Mascarpone Fabioli 2008-10-29

Now we want to select the records with an OrderDate of "2008-11-11" from the table above.

We use the following SELECT statement:

Back 73

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

The result-set will look like this:

OrderId ProductName OrderDate
1 Geitost 2008-11-11
3 Mozzarella di Giovanni 2008-11-11

Front 74

SQL NULL Values

Back 74

If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.

NULL values are treated differently from other values.

NULL is used as a placeholder for unknown or inapplicable values.

Front 75

SQL IS NULL

How do we select only the records with NULL values in the "Address" column?

Back 75

We will have to use the IS NULL operator:

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

The result-set will look like this:

Last Name FirstName Address
Hansen Ola
Pettersen Kari

Front 76

SQL IS NOT NULL

How do we select only the records with no NULL values in the "Address" column?

Back 76

We will have to use the IS NOT NULL operator:

SELECT LastName,FirstName,Address FROM Persons

WHERE Address IS NOT NULL

Front 77

ISNULL() function

Back 77

MySQL does have an ISNULL() function. However, it works a little bit different from Microsoft's ISNULL() function.

Front 78

SQL Server / MS Access ISNULL()

Back 78

ISNULL() returns a zero if the value is NULL:

Front 79

NVL(), IFNULL(), and COALESCE() use

Back 79

NVL(), IFNULL(), and COALESCE() functions can also be used to achieve the same result.

Front 80

Microsoft Access Data Types

Back 80

Text, Memo, Byte, Integer, Long, Single, Double, Currency, autoNumber, Date/Time, Yes/No, Ole Object, Hyperlink, Lookup Wizard

Front 81

CHAR(size)

MySQL Data Types

Back 81

Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters

Front 82

VARCHAR(size)

MySQL Data Types

Back 82

Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type

Front 83

TINYTEXT

MySQL Data Types

Back 83

Holds a string with a maximum length of 255 characters

Front 84

TEXT

MySQL Data Types

Back 84

Holds a string with a maximum length of 65,535 characters

Front 85

BLOB

MySQL Data Types

Back 85

For BLOBs (Binary Large Objects). Holds up to 65,535 bytes of data

Front 86

MEDIUMTEXT

MySQL Data Types

Back 86

For BLOBs (Binary Large Objects). Holds up to 16,777,215 bytes of data

Front 87

MEDIUMBLOB

MySQL Data Types

Back 87

Holds a string with a maximum length of 4,294,967,295 characters

Front 88

LONGBLOB

MySQL Data Types

Back 88

For BLOBs (Binary Large Objects). Holds up to 4,294,967,295 bytes of data

Front 89

ENUM(x,y,z,etc.)

MySQL Data Types

Back 89

Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.

Note: The values are sorted in the order you enter them.

You enter the possible values in this format: ENUM('X','Y','Z')

Front 90

SET

MySQL Data Types

Back 90

Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice

Front 91

TINYINT(size)

MySQL Data Types

Back 91

-128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis

Front 92

SMALLINT(size)

MySQL Data Types

Back 92

-32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis

Front 93

MEDIUMINT(size)

MySQL Data Types

Back 93

-8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis

Front 94

INT(size)

MySQL Data Types

Back 94

-2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis

Front 95

BIGINT(size)

MySQL Data Types

Back 95

-9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis

Front 96

FLOAT(size,d)

MySQL Data Types

Back 96

A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

Front 97

DOUBLE(size,d)

MySQL Data Types

Back 97

A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

Front 98

DECIMAL(size,d)

MySQL Data Types

Back 98

A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

Front 99

DATE()

MySQL Data Types

Back 99

A date. Format: YYYY-MM-DD

Note: The supported range is from '1000-01-01' to '9999-12-31'

Front 100

DATETIME()

MySQL Data Types

Back 100

*A date and time combination. Format: YYYY-MM-DD HH:MM:SS

Note: The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

Front 101

TIMESTAMP()

MySQL Data Types

Back 101

*A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD HH:MM:SS

Note: The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC

Front 102

TIME()

MySQL Data Types

Back 102

A time. Format: HH:MM:SS

Note: The supported range is from '-838:59:59' to '838:59:59'

Front 103

YEAR()

MySQL Data Types

Back 103

A year in two-digit or four-digit format.

Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069

Front 104

char(n)

SQL Server Data Types

Back 104

Fixed-length character string. Maximum 8,000 characters

Front 105

char(n)

SQL Server Data Types

Back 105

Variable-length character string. Maximum 8,000 characters

Front 106

varchar(max)

SQL Server Data Types

Back 106

Variable-length character string. Maximum 1,073,741,824 characters

Front 107

text

SQL Server Data Types

Back 107

Variable-length character string. Maximum 2GB of text data

Front 108

nchar(n)

SQL Server Data Types

Back 108

Fixed-length Unicode data. Maximum 4,000 characters

Front 109

nvarchar(n)

SQL Server Data Types

Back 109

Variable-length Unicode data. Maximum 4,000 characters

Front 110

nvarchar(max)

SQL Server Data Types

Back 110

Variable-length Unicode data. Maximum 536,870,912 characters

Front 111

ntext

SQL Server Data Types

Back 111

Variable-length Unicode data. Maximum 2GB of text data

Front 112

bit

SQL Server Data Types

Back 112

Allows 0, 1, or NULL

Front 113

binary(n)

SQL Server Data Types

Back 113

Fixed-length binary data. Maximum 8,000 bytes

Front 114

varbinary(n)

SQL Server Data Types

Back 114

Variable-length binary data. Maximum 8,000 bytes

Front 115

varbinary(max)

SQL Server Data Types

Back 115

Variable-length binary data. Maximum 2GB

Front 116

image

SQL Server Data Types

Back 116

Variable-length binary data. Maximum 2GB

Front 117

tinyint

SQL Server Data Types

Back 117

Allows whole numbers from 0 to 255

Front 118

smallint

SQL Server Data Types

Back 118

Allows whole numbers between -32,768 and 32,767

Front 119

int

SQL Server Data Types

Back 119

Allows whole numbers between -2,147,483,648 and 2,147,483,647

Front 120

bigint

SQL Server Data Types

Back 120

Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807

Front 121

decimal(p,s)

SQL Server Data Types

Back 121

Fixed precision and scale numbers.

Allows numbers from -10^38 +1 to 10^38 –1.

The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0

Front 122

numeric(p,s)

SQL Server Data Types

Back 122

Fixed precision and scale numbers.

Allows numbers from -10^38 +1 to 10^38 –1.

The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0

Front 123

smallmoney

SQL Server Data Types

Back 123

Monetary data from -214,748.3648 to 214,748.3647

Front 124

money

SQL Server Data Types

Back 124

Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807

Front 125

float(n)

SQL Server Data Types

Back 125

Floating precision number data from -1.79E + 308 to 1.79E + 308.

The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.

Front 126

real

SQL Server Data Types

Back 126

Floating precision number data from -3.40E + 38 to 3.40E + 38

Front 127

datetime

SQL Server Data Types

Back 127

From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds

Front 128

datetime2

SQL Server Data Types

Back 128

From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds

Front 129

smalldatetime

SQL Server Data Types

Back 129

From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute

Front 130

date

SQL Server Data Types

Back 130

Store a date only. From January 1, 0001 to December 31, 9999

Front 131

time

SQL Server Data Types

Back 131

Store a time only to an accuracy of 100 nanoseconds

Front 132

datetimeoffset

SQL Server Data Types

Back 132

The same as datetime2 with the addition of a time zone offset

Front 133

timestamp

SQL Server Data Types

Back 133

Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable

Front 134

sql_variant

SQL Server Data Types

Back 134

Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp

Front 135

uniqueidentifier

SQL Server Data Types

Back 135

Stores a globally unique identifier (GUID)

Front 136

xml

SQL Server Data Types

Back 136

Stores XML formatted data. Maximum 2GB

Front 137

cursor

SQL Server Data Types

Back 137

Stores a reference to a cursor used for database operations

Front 138

table

SQL Server Data Types

Back 138

Stores a result-set for later processing

Front 139

SQL Aggregate Functions

Back 139

SQL aggregate functions return a single value, calculated from values in a column.

Front 140

SQL Scalar functions

Back 140

SQL scalar functions return a single value, based on the input value.

Front 141

AVG()

COUNT()

FIRST()

LAST()

MAX()

MIN()

SUM()

Back 141

* AVG() - Returns the average value

* COUNT() - Returns the number of rows

* FIRST() - Returns the first value

* LAST() - Returns the last value

* MAX() - Returns the largest value

* MIN() - Returns the smallest value

* SUM() - Returns the sum

Front 142

UCASE()

LCASE()

MID()

LEN()

ROUND()

NOW()

FORMAT()

Back 142

* UCASE() - Converts a field to upper case

* LCASE() - Converts a field to lower case

* MID() - Extract characters from a text field

* LEN() - Returns the length of a text field

* ROUND() - Rounds a numeric field to the number of decimals specified

* NOW() - Returns the current system date and time

* FORMAT() - Formats how a field is to be displayed

Front 143

AVG() function returns the average value of a numeric column.

SQL AVG() Syntax

Back 143

SELECT AVG(column_name) FROM table_name

Front 144

SQL AVG() Example

Back 144

O_Id OrderDate OrderPrice Customer

1 2008/11/12 1000 Hansen

2 2008/10/23 1600 Nilsen

3 2008/09/02 700 Hansen

4 2008/09/03 300 Hansen

5 2008/08/30 2000 Jensen

6 2008/10/04 100 Nilsen



SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

Front 145

find the customers that have an OrderPrice value higher than the average OrderPrice value

Back 145

SELECT Customer FROM Orders

WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

Front 146

SQL COUNT(column_name) Syntax

Back 146

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:



SELECT COUNT(column_name) FROM table_name

Front 147

SQL COUNT(*) Syntax

Back 147

The COUNT(*) function returns the number of records in a table:



SELECT COUNT(*) FROM table_name

Front 148

SQL COUNT(DISTINCT column_name) Syntax

Back 148

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name



Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.

Front 149

SQL COUNT(column_name) Example

Back 149

O_Id OrderDate OrderPrice Customer

1 2008/11/12 1000 Hansen

2 2008/10/23 1600 Nilsen

3 2008/09/02 700 Hansen

4 2008/09/03 300 Hansen

5 2008/08/30 2000 Jensen

6 2008/10/04 100 Nilsen



Now we want to count the number of orders from "Customer Nilsen".



We use the following SQL statement:

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders

WHERE Customer='Nilsen'



The result of the SQL statement above will be 2, because the customer Nilsen has made 2 orders in total:

CustomerNilsen

2

Front 150

SQL COUNT(*) Example

Back 150

we omit the WHERE clause, like this:

SELECT COUNT(*) AS NumberOfOrders FROM Orders

Front 151

SQL COUNT(DISTINCT column_name) Example

Back 151

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

Front 152

SQL FIRST() Function

Back 152

SQL FIRST() Syntax

SELECT FIRST(column_name) FROM table_name

Front 153

SQL LAST() Function

Back 153

SQL LAST() Syntax

SELECT LAST(column_name) FROM table_name

Front 154

SQL LAST() Example



O_Id OrderDate OrderPrice Customer

1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen

Back 154

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders


Note Tip: Workaround if LAST() function is not supported:

SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1


The result-set will look like this:

LastOrderPrice

100

Front 155

SQL MAX() Function

Back 155

The MAX() function returns the largest value of the selected column.

SQL MAX() Syntax

SELECT MAX(column_name) FROM table_name

Front 156

SQL MIN() Function

Back 156

The MIN() function returns the smallest value of the selected column.

SQL MIN() Syntax

SELECT MIN(column_name) FROM table_name

Front 157

SQL SUM() Function

Back 157

The SUM() function returns the total sum of a numeric column.

SQL SUM() Syntax

SELECT SUM(column_name) FROM table_name

Front 158

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

Back 158

OrderTotal

5700

Front 159

The GROUP BY Statement

Back 159

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Front 160

SQL GROUP BY Syntax

Back 160

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

Front 161

GROUP BY More Than One Column

Back 161

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate

Front 162

SQL HAVING Clause

Back 162

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Front 163

SQL HAVING Syntax

Back 163

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

Front 164

SQL HAVING Example



O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen

Back 164

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000



The result-set will look like this:

Customer SUM(OrderPrice)
Nilsen 1700

Front 165

Now we want to find if the customers "Hansen" or "Jensen" have a total order of more than 1500.

We add an ordinary WHERE clause to the SQL statement:

Back 165

SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Hansen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500


The result-set will look like this:
Customer SUM(OrderPrice)
Hansen 2000
Jensen 2000

Front 166

SQL UCASE() Function

Back 166

The UCASE() function converts the value of a field to uppercase.

SQL UCASE() Syntax

SELECT UCASE(column_name) FROM table_name

Syntax for SQL Server

SELECT UPPER(column_name) FROM table_name

Front 167

SQL LCASE() Function

Back 167

The LCASE() function converts the value of a field to lowercase.

SQL LCASE() Syntax

SELECT LCASE(column_name) FROM table_name

Syntax for SQL Server

SELECT LOWER(column_name) FROM table_name

Front 168

The MID() Function

Back 168

The MID() function is used to extract characters from a text field.

SQL MID() Syntax

SELECT MID(column_name,start[,length]) FROM table_name



Parameter Description

column_name Required. The field to extract characters from

start Required. Specifies the starting position (starts at 1)

length Optional. The number of characters to return. If omitted, the MID() function returns the rest of the text

Front 169

SQL MID() Example



P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Now we want to extract the first four characters of the "City" column above.

We use the following SELECT statement:

Back 169

SELECT MID(City,1,4) as SmallCity FROM Persons


The result-set will look like this:

SmallCity

Sand

Sand

Stav

Front 170

SQL LEN() Function

Back 170

The LEN() Function

The LEN() function returns the length of the value in a text field.

SQL LEN() Syntax

SELECT LEN(column_name) FROM table_name

Front 171

SQL ROUND() Function

Back 171

The ROUND() Function

The ROUND() function is used to round a numeric field to the number of decimals specified.

SQL ROUND() Syntax

SELECT ROUND(column_name,decimals) FROM table_name

Parameter Description

column_name Required. The field to round.

decimals Required. Specifies the number of decimals to be returned

Front 172

SQL NOW() Function

Back 172

The NOW() Function

The NOW() function returns the current system date and time.

SQL NOW() Syntax

SELECT NOW() FROM table_name

Front 173

SQL FORMAT() Function

Back 173

The FORMAT() function is used to format how a field is to be displayed.

SQL FORMAT() Syntax

SELECT FORMAT(column_name,format) FROM table_name

Parameter Description

column_name Required. The field to be formatted.

format Required. Specifies the format.

Front 174

SQL FORMAT() Example

We have the following "Products" table:

Prod_Id ProductName Unit UnitPrice
1 Jarlsberg 1000 g 10.45
2 Mascarpone 1000 g 32.56
3 Gorgonzola 1000 g 15.67

Now we want to display the products and prices per today's date (with today's date displayed in the following format "YYYY-MM-DD").

We use the following SELECT statement:

Back 174

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products

The result-set will look like this:

ProductName UnitPrice PerDate
Jarlsberg 10.45 2008-10-07
Mascarpone 32.56 2008-10-07
Gorgonzola 15.67 2008-10-07

Front 175

sp_who

Back 175

shows everybody that is running a request on the SQL server

Front 176

use of DMV

Back 176

SELECT * FROM sys.dm_exec_sessions

SELECT * FROM sys.dm_exec_requests

Front 177

what is DMV

Back 177

dynamic management views

Front 178

DBCC

Back 178

Transact-SQL programming language provides DBCC statements that act as Database Console Commands for Microsoft SQL Server 2008. These statements check the physical and logical consistency of a database. Many DBCC statements can fix detected problems

Front 179

sys.dm_clr_loaded_assemblies

Back 179

Assemblies in available in SQL Server

Front 180

sys.dm_db_file_space_usage

Back 180

Database file usage to determine if databases are getting low on space and need immediate attention

Front 181

sys.dm_exec_cached_plans

Back 181

Cached query plans available to SQL Server

Front 182

sys.dm_exec_sessions

Back 182

Sessions in SQL Server

Front 183

sys.dm_exec_connections

Back 183

Connections to SQL Server

Front 184

sys.dm_db_index_usage_stats

Back 184

Seeks, scans, lookups per index

Front 185

sys.dm_io_virtual_file_stats

Back 185

IO statistics for databases and log files

Front 186

sys.dm_broker_connections

Back 186

Service Broker connections to the network

Front 187

sys.dm_os_memory_objects

Back 187

SQL Server memory usage

Front 188

sys.dm_tran_active_transactions

Back 188

Transaction state for an instance of SQL Server

Front 189

list of all db query

Back 189

SELECT DISTINCT DB_NAME(database_id)

FROM sys.master_files

Front 190

Can 32 bit version of SQL 2008 be installed on 64 bit windows

Back 190

Yes with WOW

Front 191

What version for .Net is required by SQL 2008

Back 191

.Net Framework 3.5 and Windows Installer 4.5

Front 192

SQL Server requires

Back 192

.Net framework 3.5

Microsoft Data Access Components 2.8 SP1

Share Memory

Named Pipe

TCP/IP networking support

IE 6 SP1 and up

Front 193

You can install all editions of SQL Server 2008 on either

Back 193

Windows 2003 Standard

Windows 2008 Server Standard

Front 194

Describe the Service Broker ?

Back 194

intro in SQL 2005 message queuing system. Based on user-defined messages and processing actions, it provides asynchronous data processing capabilities

Front 195

SSIS

ETL

MSMS

WMI

Back 195

SQL Server Integration Service

Extract, Transform, Load

Microsoft Message Queue

Windows Management Instrumentation

Front 196

Describe the SSIS?

Back 196

feature all the capabilities you can find in ETL + to build apps. that can manage db and system resources, respond to DB and System event, while interacting with user

Front 197

SSIS additional functionality

Back 197

ftp and folder manipulation

import/export files in DB

interact with Web service

respond to WMI event

Front 198

What containers allow SSIS

Back 198

to execute entire tasks and workflows within a loop with a variety of inputs

Front 199

Specialized tasks included in SSIS part1

(4)

Back 199

copy SQL Server objects

manage DB backups

re-indexing

extendibility with VSTA

Front 200

What does SSRS stand for?

Back 200

SQL Server Reporting Service

platform for designing reports and distributing data

Front 201

What are the SSRS components?

Back 201

report server

report designer

Front 202

What does SSAS stand for? What does it do?

Back 202

SQL Server Analysis Service

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

Front 203

What are the SSAS components?

Back 203

Online Analytical Processing

Data Mining

Front 204

What does OLAP stand for?

Back 204

Online Analytical Processing

Front 205

What does BIDS stand for?

Back 205

Business Intelligence Development Studio

Front 206

Data Mining engine use

Back 206

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

Front 207

What is an enumeration request?

Back 207

Applications 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 208

What are the possible profile configurations and what do that do?

Back 208

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 209

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

Back 209

Messages will accumulate in a queue within the MSDB database

Front 210

What is the query to enable Database Mail feature

Back 210

EXEC sp_configure 'Database Mail XPs', 1
GO

RECONFIGURE WITH OVERRIDE
GO

Front 211

What service database does mail utilize?

Back 211

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

Front 212

What happens if SQL Server Agent is not running

Back 212

Messages accumulate in queue within the Msdb database.

Front 213

How many mail accounts can a mail profile contain?

Back 213

One and more

Front 214

What are notification Services?

Back 214

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

Front 215

What is Activity Monitor?

Back 215

It is a feature of SSMS that displays query activity on the server.

Front 216

What is the first thing you can do on the path to achieving optimal performance disk I/O performance?

Back 216

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

Front 217

Types of files are used by SQL Server?

Back 217

Data and transaction logs.

Front 218

What are data files for?

Back 218

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

Front 219

What are transaction log files for?

Back 219

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

Front 220

Three thing about the .mdf extension?

Back 220

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 221

What is the .ndf extension?

Back 221

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

Front 222

What is the .ldf extension?

Back 222

Used for the transaction logs.

Front 223

Describe the check pointing process.

Back 223

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 224

What is the DEFAULT property for a filegroup?

Back 224

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

Front 225

Why would you change the default filegroup?

Back 225

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 226

What is the command for changing the default filegroup?

Back 226

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

Front 227

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

Back 227

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

Front 228

Give several reasons to use secondary filegroup vs. primary filegroup.

Back 228

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 229

What determines the state of the DB?

Back 229

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 230

How does SQL Server write to the transaction log?

Back 230

Sequentially

Front 231

When does SQL Server read from the log?

Back 231

Only during restart recoveries.

Front 232

What does FILESTREAM allows you to do?

Back 232

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

Front 233

What does the tempdb database do?

Back 233

temporary objects

workstables used in grouping/storing

worktables to support cursors

version store supporting snapshot isolation level

overflow for table variables

Front 234

What would you ever do multiple tempdb databases?

Back 234

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 235

Generally, what is the Recover model in SQL Server?

Back 235

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 236

Describe the Full Recovery Mode.

Back 236

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 237

What is the best practice recovery model?

Back 237

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

Front 238

What is the Bulk-logged recovery model?

Back 238

It 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 239

What are the five operations that are performed in minimally logged manner with the DB set in bulk-logged recovery?

Back 239

BCP

BULK INSERT

SELECT..INTO

CREATE INDEX

ALTER INDEX .. REBUILD

Front 240

Describe the simple recovery model.

Back 240

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 241

What is the Cmd for setting DB recovery model

Back 241

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

Front 242

How do pages get damaged in SQL Server?

Back 242

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 243

What command allows you to discover and log damaged pages?

Back 243

PAGE_VERIFY CHECKSUM

Front 244

Does SQL Server by default checks for damaged pages?

Back 244

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

Front 245

What is the default thing to happen in a SQL Server DB if a damaged page is found?

Back 245

The default config 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 246

When is error 824 returned?

Back 246

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 247

What do the AUTO OPTIONS do in SQL Server?

Back 247

The AUTO database options are those that enable certain actions to occur automatically.

Front 248

List five AUTO OPTIONS.

Back 248

AUTO_CLOSE

AUTO_SHRINK

AUTO_CREATE_STATISTICS

AUTO_UPDATE_STATISTICS

AUTO_UPDATE_STATISTICS_ASYNCH

Front 249

Best practices for AUTO_SHRINK

Back 249

Disabled and manually shrink files only when needed.

Front 250

Access to a DB is controlled with this options

Back 250

ONLINE

OFFLINE

EMERGENCY

Front 251

DB is in ONLINE STATE

Back 251

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

Front 252

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

Back 252

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 253

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

Back 253

SQL caches the query plan. When a query is executed, SQL Server parses and compile 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 254

What are the options that PAGE_VERIFY can be set to?

Back 254

TORN_PAGE_DETECTION

CHECKSUM

Front 255

What is PAGE_VERIFY TORN_PAGE_DETECTION?

Back 255

Exist only for backward compatibility should not be used

Front 256

What is PAGE_VERIFY CHECKSUM?

Back 256

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 257

What is the DBCC CHECKDB command?

Back 257

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

Front 258

Schemas explanation?

Back 258

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

Front 259

Syntax to create schema?

Back 259

CREATE SCHEMA <schema name> AUTHORIZATION <owner name>

Front 260

What are the numeric data types? 9 of them.

Back 260

TINYINT

SMALLINT

INT

BIGINT

DECIMAL(P,S)

NUMERIC(P,S)

SMALLMONEY

MONEY

REAL

FLOAT

Front 261

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

Back 261

COLLATE works only of a character-based column.

Front 262

32 bit minimal requirements

64 bit maximum requirements

Back 262

p3 1gh 512mb

Itanium, Opteron, Athlon, Xeon 1.6ghz 512mb

Front 263

OS supported for all editions of SQL Servers are

Back 263

Windows Server 2008 Standard and up

Windows Server 2003 Standard SP2 or up

Front 264

SQL Developer, Evaluation and Express are supported

Back 264

Windows XP Pro Sp2 and up

Vista Home Basic or up

Front 265

SQL Express

Back 265

Xp Home and up
Xp Home Reduced Media Edition
Xp Table Edition SP2 or up
Xp Media Center 2002 SP2 or up
Xp Pro Reduced Media Edition
Xp Pro Embedded Edition Sp2
Xp Pro Embedded Edition

Front 266

Is SQL server 2008 is not supported on

Back 266

Windows Server 2008 Server Core, because .Net Framework is not supported on Server Core. SQL Server 2008 relies on .Net framework capabilities to support FILESTREAM, SPATIAL, and DATE data types, along whit server additional features.

Front 267

What version of .Net is required by SQL 2008

Back 267

.Net Framework 3.5 and Windows Installer 4.5

Front 268

SQL Server requires

(7)

Back 268

.Net framework 3.5

Microsoft Data Access Components 2.8 SP1

Share Memory

Named Pipe

TCP/IP networking support

IE 6 SP1 and up

Front 269

Service Broker

(descr.)

Back 269

Intro in SQL 2005 message queuing system. Based on user-defined messages and processing actions, it provides asynchronous data processing capabilities.

Front 270

SSIS

ETL

MSMS

WMI

Back 270

SQL Server Integration Service

Extract, Transform, Load

Microsoft Message Queue

Windows Management Instrumentation

Front 271

SSIS

(descr.)

Back 271

Feature all the capabilities you can find in ETL + to build apps. That can manage db and system resources, respond to DB and System event, while interacting with user.

Front 272

SSIS additional func.

(4)

Back 272

ftp and folder manipulation

import/export files in DB

interact with Web service

respond to WMI event

Front 273

DDL triggers

Back 273

Allow you to trap and respond to login events. You can scope DDL triggers at instance or a DB level.

Front 274

Generic syntax for creating a DDL trigger

Back 274

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 > [ ; ] }

<ddl_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

Front 275

DDL not applicable

Back 275

When a query (like ALTER TABLE) changes the file structure in windows cannot be rolled back.

Front 276

DDL triggers most important feature

Back 276

DDL triggers is ability to roll back an action. The Policy-Based Management Framework creates DDL triggers for all policies that you configure to prevent an out-of-compliance situation.

Front 277

EVENTDATA()

Back 277

Within the execution context of the DDL trigger, you have access to a special function, that provide information about the DDL action.

Returns an XML document with a structure depending upon the event.

Front 278

General syntax for creating a server audit

Back 278

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 / UNLIMITED } ]
[ , RESERVE_DISK_SPACE = { ON / OFF } ]

}


<audit_options>::=

{
[ QUEUE_DELAY = integer ]
[ , ON_FAILURE = { CONTINUE / SHUTDOWN } ]
[ , AUDIT_GUID = uniqueidentifier ]
}

Front 279

SQL Server 2008 auditing capabilities

Back 279

Combines all the auditing capabilities into an audit specification.

Audit specification begin with a server-level audit object that define the logging location for the audit trial.

Audits can be created on server/database.

Front 280

QUEUE_DELAY option of CREATE SERVER AUDIT

Back 280

When = 0 audit records are sent to the audit log synchronously

Front 281

ON_FAILURE option of CREATE SERVER AUDIT

Back 281

Control how the instance behaves if audit records cannot be written.

CONTINUE(default) - instance continues running and processing transactions

SHUTDOWN - instance shuts down

Front 282

CREATE SERVER AUDIT

Back 282

creates an instance of an audit object

Front 283

Possible way to encrypt

Back 283

hash

passphrase

symmetric key

asymmetric key

certificates

Front 284

What encryption SQL uses to handle password encryption

Back 284

MD5

Front 285

List of (5) hash algorithms available in SQL

Back 285

SHA

SHA1

MD2

MD4

MD5(recommended)

Front 286

TDE

Back 286

Transparent data encryption

Provides real time encryption and decryption service to ensure that data within the files and backups is encrypted.'

Front 287

The process of implementing TDE.

Back 287

1) Create a DB master key in the master DB

2) Create a certificate in the master database

3) Create a DB encryption key in the target DB using the certificate in the master DB

4) Alter the DB and enable encryption

Front 288

EKM

Back 288

Extensible Key Management

Feature in SQL Server 2008 allowing it to integrate with enterprise key management.

Keys can be maintained in a central location within an enterprise and exported for use within SQL Server.

Front 289

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

Back 289

Objects

Counters

Counter instances

Front 290

What types of data can System monitor capture?

Back 290

System Monitor captures numeric data for performance counters that are defined for hardware or software components

Front 291

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

Back 291

S: Processor Queue Length

Network Interface: Output Queue Length

Physical Disk:Avg.Disk Queue Length

Front 292

System Monitor use

Back 292

To capture numeric statistics about hardware and software components.

Front 293

Counters three-level hierarchy

Back 293

Counter object

counter

counter instance

Front 294

A counter must have at least on .......

Back 294

Counter

Front 295

Counter can have.......

Back 295

zero and more instances

Front 296

Blank profiler trace template

Back 296

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

Front 297

SP_Counts profiler trace template

Back 297

Captures each stored procedure executed so that you can determine how many of each procedure is being executed.

Front 298

Standard profiler trace template

Back 298

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

Front 299

TSQL profiler trace template

Back 299

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

Front 300

TSQL_Duration profiler trace template

Back 300

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

Front 301

TSQL_Grouped profiler trace templates

Back 301

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 302

TSQL_Locks profiler trace templates

Back 302

Captures blocking and deadlock information such as blocked processes, deadlock chains, deadlock graph, 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 303

TSQL_Replay profiler trace templates

Back 303

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 304

TSQL_SPs profiler trace templates

Back 304

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 305

Tuning profiler trace templates

Back 305

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

Front 306

Broker SQL trace Event Groups

Back 306

13 events for Service Broker message, queues, and conversations

Front 307

CLR SQL trace Event Groups

Back 307

1 event for the loading of a Common Language Runtime assembly

Front 308

Cursors

Back 308

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

Front 309

Database SQL trace Event Groups

Back 309

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

Front 310

Deprecation SQL trace Event Groups

Back 310

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

Front 311

Errors and Warnings SQL trace Event Groups

Back 311

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

Front 312

Full Text SQL trace Event Groups

Back 312

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

Front 313

Full Text SQL trace Event Groups

Back 313

3 events to track the progress of a fill text index crawl

Front 314

Locks SQL trace Event Groups

Back 314

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

Front 315

OLEDB SQL trace Event Groups

Back 315

5 events for distributed queries and remote stored procedure calls

Front 316

Objects SQL trace Event Groups

Back 316

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

Front 317

Performance SQL trace Event Groups

Back 317

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 318

Progress Report SQL trace Event Groups

Back 318

1 event for online index creation progress

Front 319

Query Notification SQL trace Event Groups

Back 319

4 events to tack the parameters, subscription, and templates for query notifications

Front 320

Scans SQL trace Event Groups

Back 320

2 events to track when a table or index is scanned

Front 321

Security Audit SQL trace Event Groups

Back 321

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

Front 322

Server SQL trace Event Groups

Back 322

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

Front 323

Sessions SQL trace Event Groups

Back 323

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

Front 324

Stored procedures SQL trace Event Groups

Back 324

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

Front 325

Transactions SQL trace Event Groups

Back 325

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

Front 326

TSQL SQL trace Event Groups

Back 326

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 327

User Configurable SQL trace Event Groups

Back 327

10 events that you can configure with SQL Trace

Front 328

model db use

Back 328

model d=database is used as the template for all databases created on an instance of SQL Server.

Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.

Front 329

msdb use

Back 329

Used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.

Front 330

SQL Server Profiler purpose

Back 330

Application that allows you to define graphically settings that are translated into stored procedure calls to create and manage traces.

Front 331

What is a trace?

Back 331

Collection of stored procedures to create trace on a instance.

Traces can be stopped, started, ended and paused.

Front 332

Trace behavior

Back 332

1) After trace is started SQL Server Trace API returns only events matching the trace definition all other are discarded.

2) When trace is stopped, all event collection terminates, and if the race is started again all previous data is cleared.

3) To temporary stop data collection for a trace you can pause it.

Front 333

sp_trace_create

(SQL Trace Event Group)

Back 333

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 334

sp_trace_generateevent

(SQL Trace Event Group)

Back 334

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

Front 335

sp_trace_setevent

(SQL Trace Event Group)

Back 335

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 336

sp_trace_setfilter

(SQL Trace Event Group)

Back 336

A stored procedure that adds a filter to a trace.

Equivalent to the Edit Filter dialog box in Profiler.

Front 337

sp_trace_setstatus

(SQL Trace Event Group)

Back 337

A stored procedure that starts, stops, and closes a trace.

A status of 0 stops a trace.

1 starts
2 closes and remove the trace from the instance

Front 338

fn_trace_geteventinfo

(SQL Trace Event Group)

Back 338

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

Front 339

fn_trace_getfilterinfo

(SQL Trace Event Group)

Back 339

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

Front 340

fn_trace_getfilterinfo

(SQL Trace Event Group)

Back 340

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

Front 341

fn_trace_getinfo

(SQL Trace Event Group)

Back 341

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

Front 342

fn_trace_gettab

Back 342

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

Front 343

retrieve the contents of the current error log as a result set by executing the system extended stored procedure

Back 343

sys.xp_readerrorlog

Front 344

Where are errors and info messages related to SQL Server Agent found SQL Server Agent Log file named....

Back 344

sqlagent.out

Front 345

Where is the database mail log contained in the

Back 345

dbo.sysmail_log table in msdb database

Front 346

What happens when you run out of space in the transaction log?
(4)

Back 346

All write activity to the DB stops.

You can still read from the db.

Write operations roll back with error 9002 send back to the application.

Error is written to Windows Application Log.

Front 347

error 1101 or 1105

Back 347

Database has run out of disk space.

You cant insert new data.

Front 348

MARS

Back 348

Multiple active result sets

Front 349

1101, 1105, 9002

3958, 3959, 3966 and 3967

Back 349

Space issues for the tempdb are encountered.

Front 350

9002 error

Back 350

When you run out of transaction log space.

Front 351

1101 and 1105 error

Back 351

When the data files are out of space.

Front 352

SQL Startup sequence

Back 352

1) Master db is brought online

2) Tempdb is recreated

3) Write occurs to the folder that store the data and log files

Front 353

???

Back 353

Boots SQL server in single user mode

Allows to change location of tempdb

Front 354

???

Back 354

List the location and the filename for the master table.

Front 355

???

Back 355

Lists the location and file name of the master transaction log file.

Front 356

???

Back 356

Lists the name and location of the Sql error log.

Front 357

???

Back 357

Adding a trace flags.

Front 358

How to rebuild a master database in SQL Server 2008

Back 358

Using the new Installation Center Event through it still uses setup.

Front 359

use for distribution database

Back 359

If you have configured replication this is one of our system tables.

Front 360

mssqlsystemresource

Back 360

Intro in 2005

contains stored procedures

functions

DMV is by default hidden

Front 361

Kill all connections to a DB.

Back 361

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'navigator1'

DECLARE @SQL varchar(max)
SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'

FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

--SELECT @SQL
EXEC(@SQL)

Front 362

Convert from decimal to hex.

Back 362

convert(varbinary(16),<column>)

Front 363

Utilities to view the state and configuration of SQL Server service.

Back 363

Windows Service Console

SQL Server Configuration Manager

Front 364

SQL Server starts up

Back 364

1) Master DB is brought online.

2) Tempdb is re-created.

3) Creation of tempdb causes a write to occur to the folder that stores the data and log files for tempdb.

Front 365

Sql server single user mode

Back 365

Use the -m code

Front 366

Device activation error

Back 366

SQL Server could not access a data or log file for a database.

Front 367

What device activation errors for tempdb and master do?

Back 367

Prevent the instance from starting.

Front 368

SQL Server service account

Back 368

Dedicated account per service

3 choices

local user account
domain user account
built in user account

Front 369

When to use local user account for SQL?

Back 369

If SQL is not part of a Domain.

Front 370

When to use domain user account for SQL?

Back 370

99% of the time

If service will interact with network services, access resources like file shares or multiple linked server connections to other SQL Servers.

Front 371

When to use built in system accounts?

Back 371

bad choices

Front 372

master db purpose and use

Back 372

Main system database.

DB of all DBs

1 of for each service

Front 373

model db purpose and use

Back 373

Used as a template for creating user DB

Changes here are reflected to all new user DB

Front 374

msdb purpose and use

Back 374

Stores info related to automation

SQL Server agents and jobs schedule

Front 375

???

Back 375

Lists the location and file name for the master data file.

Front 376

???

Back 376

Lists the location and file name of the master transaction log file.

Front 377

???

Back 377

Lists the name and location of the Sql Server error log.

Front 378

???

Back 378

Adding trace flag.

Front 379

How you fix a corrupt master db

Back 379

By running SQL Server installation.

Front 380

distribution DB(sys db)

Back 380

If you have configured replication you also see a database named distribution.

Front 381

mssqlsystemresource DB(sys db)

Back 381

Introduced in SQL 2005

Contains most of the store procedure, function, DMV and other code that ships with SQL.

DB is usually hidden

Front 382

device activation error indicates what???

Back 382

That SQL Server either cannot find or cannot write to a data or log file.