CHARINDEX

What does CHARINDEX do?

Describe 2 examples of its use.

The CHARINDEX string function returns the starting position of the specified expression in a character string.  It accepts three parameters with the third parameter being optional.

CHARINDEX ( expression1, expression2, [ , start_location ] )

The first parameter is the expression that contains the sequence of characters to be found.  The second parameter is the expression searched for the specified sequence.  This is typically a column from a table.  The third parameter, which is optional, is the character position to start searching expression1 in expression2.

The CHARINDEX function searches expression 2 for the existence of expression 1. CHARINDEX returns a 1 or higher if a match is found returns a 0 if nothing is found.

IF CHARINDEX('Slax',@messageAction,0) >0 ...

(Joes2Pros Volume 4, page 135.)

SELECT CHARINDEX('the', 'the quick brown fox jumps over the lazy dog') AS [Location]

Location
----------------
1

SELECT CHARINDEX('the', 'the quick brown fox jumps over the lazy dog', 10) AS [Location]

Location
----------------
32

SELECT CHARINDEX('jumped', 'the quick brown fox jumps over the lazy dog') AS [Location]

Location
----------------
0

DECLARE @FullName  VARCHAR(100)
SET @FullName = 'Mickey Mouse'
SELECT LEFT(@FullName, CHARINDEX(' ', @FullName) - 1) AS [FirstName],
RIGHT(@FullName, CHARINDEX(' ', REVERSE(@FullName)) - 1) AS [LastName]

First Name Last Name
---------- ----------
Mickey Mouse

http://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=4a59645b-a5a1-4099-b6d2-959d8644cbed&tkw=uses-of-the-charindex-function

COUNT

What does COUNT(*) do? 

COUNT

What does COUNT(columnname) do?

CTE - Recursive

1. What is the structure of a recursive CTE query?

2. Explain how to terminate a recursive CTE.

[image]

2. The anchor query is executed exactly once and the result is united with the results from the recursive member, which is executed until it returns an empty set. You have to provide a condition that will ensure the recursive member query does not run indefinitely. The easiest way to ensure termination is to use an increasing identity column in the base_table.

SQL.CTEs.RecursiveQueries.SQLServerCentral.SMarinova.(11.29.13).pdf

Declarative Integrity

What is declarative integrity?

Declarative integrity is a set of rules that are applied to tables and its columns unsing CREATE TABLE or ALTER TABLE statements.

These statements are called CONSTRAINTS.

70-433_Training_Kit_MS_SQL_Server_2008_Database_Development.pdf

EXPRESSIONS

1. When is an expression deterministic?

2. What is an example of deterministic function?

3. When is an expression or function non-deterministic

1. The requirement for determinism and precision means that for a given set of input values, the same output values would always be returned.

2. The ISNULL function, on the other hand, is deterministic because the value to be supplied is contained within the definition when the ISNULL function is called.

3.. For example, the function SYSDATETIME() returns the current date and time whenever it is called. Its output would not be considered deterministic.

Many of the system functions are what is called nondeterministic, meaning that the result may vary and the system does not have a list of predetermined values to supply for the results of the function.

Expressions

What is an "expression"

[image]

70-433_Training_Kit_MS_SQL_Server_2008_Database_Development.pdf

ISNULL / COALESCE

1. What does COALESCE do?

2. What is isntyntax? 

No back of card provided

ISNULL / COALESCE

1. What does ISNULL do?

2. What is its syntax?

3. What does the ISNULL function return?

4. Is the ISNULL function deterministic or non-deterministic?

1. Replaces NULL with the specified replacement value.

2. ISNULL ( check_expression , replacement_value )

Where:

check_expression

Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

3. Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.

4. The ISNULL function is deterministic because because the value to be supplied is contained within the definition when the ISNULL function is called.

Source: http://technet.microsoft.com/en-us/library/ms184325.aspx

ISNULL and COALESCE

1. Explain COALESCE

2. Explan ISNULL

COALESCE

COALESCE ( expression [ ,...n ] )

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

SELECT Name, Class, Color, ProductNumber,

COALESCE(Class, Color, ProductNumber) AS FirstNotNull

FROM Production.Product;

ISNULL

ISNULL ( check_expression , replacement_value )

Replaces NULL with the specified replacement value

check_expression

Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

ISNULL is limited to two arguments. If you need the first non-NULL value from a list of three, you have to nest two ISNULL expressions. Make it a list of 20, and you are knee-deep in the nested parentheses. COALESCE, however, will take any number or arguments and return the first non-NULL value.

The following example finds the average of the weight of all products. It substitutes the value 50 for all NULL entries in the Weight column of the Product table.

USE AdventureWorks2012;

GO

SELECT AVG(ISNULL(Weight, 50))

FROM Production.Product;

GO

Here is the result set.

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

59.79

Index

1. Index density

2. Index fill factor

3. Page Splitting

1. Index Density

Index density refers to what percentage of each index pages (for clustered indexes, leaf pages because they contain both data and index) contains data. If the index density is low, then SQL Server must read more pages from the disk to retrieve the index data. (SQL 2008 Bible p 996)

Index Density is a ratio that shows just how many unique values there are within a given column, or set of columns.

Density = 1 / Number of distinct values for column(s)

If an index consisted of all duplicates, the density would be 1.

SELECT 1.0 / COUNT(DISTINCT MyColumn)
FROM dbo.MyTable

For multiple columns:

SELECT 1.0 / COUNT(*)
FROM (SELECT DISTINCT FirstColumn,
SecondColumn
FROM dbo.MyTable) AS DistinctRows;

High Density = Low Selectivity = Few Unique Values = Many Duplicates

Low Density = High Selectivity = Many Unique Values = Few Duplicates

If an index consisted of all duplicates, the density would be 1.

Density matters because the amount of selectivity of a given index, as determined by its density, is one of the best ways of measuring how effective it will be with your query.

A high density (low selectivity, few unique values) will be of less use to the optimizer because it might not be the most efficient way of getting at your data.

For example, if you have a column that shows up as a bit, a true or false statement such as, has a customer signed up for you mailing list, then for a million rows, you’re only ever going to see one of two values.

That means that using an index or statistics to try to find data within the table based on two values is going to result in scans where more selective data, such as an email address, will result in more efficient data access (e.g. seeks rather than scans).

SQL.Statistics.Indexes.SSCentral.GFritchey.(12.04.13).pdf

The density is the average proportion of duplicates for the index key(s). For example, an index on a 10,000-row table with 2,500 unique values would have a density of 1/2500 = 0.0004. The index density is applied against the number of rows in the table to estimate the average number of rows that would match any given value.

Therefore, any single value compared against the index key on a 10,000-row table with an index density of 0.0004 would be expected to match 10,000 * 0.0004 = 4 rows.

The lower the density value, the more selective the index is; the higher the density, the less selective the index. If an index consisted of all duplicates, the density would be 1.

(SQL.Statistics.Indexes.(12.04.13).pdf)

2a. Fill Factor

Applies only to indexed tables, not heaps. When an index is created or rebuilt, the index fill-factor value determines the percentage of space on each leaf-level index page to be filled with data, reserving the rest as free space for future growth. Index density will slowly alter during inserts, updates, and deletes. FILLFACTOR applies to index pages only.

The Fill Factor indicates the percentage of each 8K data (DATA) page used in the leaf level pages of the indexed table it (SQL Server) should fill with data.

(Source: The sentence is edited from http://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/ and quoted as:
" “Fillfactor” is a setting for indexes in SQL Server. When you create or rebuild an index, you can tell SQL Server what percentage of each 8K data page used in the “leaf” level of the index it should fill up.

In other words, if you set a 90% fillfactor when you rebuild a clustered index, SQL Server will try to leave 10% of each leaf page empty. The empty space will be present on the page as it’s written on disk, and it’ll take up the same amount of space as it sits in memory.")

(Source is edited from http://stackoverflow.com/questions/1113697/fill-factor-of-sql-server-2008
FILLFACTOR applies to index pages only. But the catch here is that if you have a clustered index (and most tables do), your FILLFACTOR will essentially apply to your data as well, since your data lives inside the leaf-nodes of your clustered index.

Fill Factor 100 or 0 Default

In an SQL Server, the smallest unit is a page, which is made of  Page with size 8K. Every page can store one or more rows based on the size of the row. The default value of the Fill Factor is 100, which is same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. There will be no or very little empty space left in the page, when the fill factor is 100.

SQL.Indexes.FillFactor.01.PDave.(12.05.13).pdf

2b. Fill Factor

The ALTER INDEX REBUILD command will completely rebuild the index. Using this command is essentially the equivalent of dropping and creating the index, with the added benefit of enabling users to set the fill factor as the index is recreated.

In contrast, the ALTER INDEX REORGANIZE command repairs fragmentation to the index’s fill factor but does not adjust the target fill factor.

The following code recreates all the indexes on the Frag table and sets the fill factor to 98 percent:
USE tempdb;
ALTER INDEX ALL ON Frag REBUILD WITH (FILLFACTOR = 98);

SQL 2008 Bible p 996

3. Page Splits

In an SQL Server, the smallest unit is a page, which is made of  Page with size 8K. Every page can store one or more rows based on the size of the row. If the page is completely filled and new data is inserted in the table which belongs to completely filled page, the “page split” event happens to accommodate new data.

When new data arrives, SQL Server has to accommodate the new data, and if it belongs to the page which is completely filled, SQL Server splits the page in two pages dividing the data in half. This means a completely filled page is now two half-filled pages.

Once this page split process is over, the new data is inserted at its logical place. This page split process is expensive in terms of the resources. As there is a lock on the page for a brief period as well, more storage space is used to accommodate small amounts of data. The argument usually is that it does not matter as storage is cheaper now a day. Let us talk about Fill Factor in terms of IO in the next paragraph.

SQL.Indexes.FillFactor.PDave.(12.05.13).pdf

Index

1. What does a clustered index impose upon a table?

2. How may clustered indexes can a table have?

Clustered Index:

1. An index that imposes a physical order on the rows within a table.

2. There can only be a single clustered index on a table.

ms-sql-server-2008-database-development-training-kit-exam-70-433.NODRM.pdf

Index

1. What does a nonclustered index contain?

2. If a table has both a clustered index and a non-clustered index, to where does the root level of the nonclustered index point?

3. If a table has only a non-clustered key, to where does the root level of nonclustered index point?

Nonclustered index

1. An index that contains one or more columns in a table that are used to improve query efficiency.

2. If the table has a clustered index, the root level of the nonclustered index points back to the clustering key.

3. Otherwise, the root level points to a row in the table.

Index

1. What is a table that does not have a clustered index?

2. What is a table that is not sorted?

If a table does not have a clustered index defined on it, it is called a heap, or an unsorted table.

ms-sql-server-2008-database-development-training-kit-exam-70-433.NODRM.pdf

Index

1. When using a nonclusted covered index, SQL Server does not need to use ________ between the _______  _____and the table to return query results.

2.Because a clustered index is the actual table, clustered indexes always ______ queries.

The notion of a covered index is that SQL Server doesn’t need to use lookups between the nonclustered index and the table to return the query results.

Because a clustered index is the actual table, clustered indexes always cover queries.

ms-sql-server-2008-database-development-training-kit-exam-70-433.NODRM.pdf page 216

Index

At the leaf-page level, what is the difference between a clustered index and a nonclustered index?

Clustered index leaf-level pages contain the data in the table.

Nonclustered index leaf-level pages contain the key value and a pointer to the data row in the clustered index or heap.

Pluralsight SQL Performance: Indexing Basics

Index

For an index to be covered, it must contain ___________.

Index

What are key-columns?

What are non-key columns in a nonclustered index?

Key vs. Nonkey Columns

Key columns: the columns specified to create a clustered or nonclustered index.

Nonkey columns: columns added to the INCLUDE clause of a nonclustered index.

Index

What could one say are the two types of tables?

You could also say that a table can have one of two forms: It is either a heap (unsorted) or a clusteredbindex (sorted).

ms-sql-server-2008-database-development-training-kit-exam-70-433.NODRM.pdf

Index

What happens when you create a primary key and do not define a clustered or a nonclustered index?

Primary key: a constraint to enforce uniqueness in a table. The primary key columns cannot hold NULL values.

In SQL Server, when you create a primary key on a table, if a clustered index is not defined and a nonclustered index is not specified, a unique clustered index is created to enforce the constraint.

However, there is no guarantee that this is the best choice for a clustered index for that table. Make sure you are carefully considering this in your indexing strategy.

Index

What is Index Depth?

Index Depth is defined as the number of levels from the top node (called the root node) and the bottom nodes (called leaf nodes).

6232 Vol 1 p 5-6

Index

What is a covering index?

What are its benefits?

Covering index: all columns returned in a query are in the index, so no additional reads are required to get the data.

A covering index will reduce the IO operations, and improve performance of queries.

Index

What is a key difference between a clustered index and a non-clustered index?

Context: Physical order of data pages.

General

1. The difference between these index types is that the clustered index is the actual
table; that is, the bottom level of a clustered index contains the actual rows, including all
columns, of the table.

2. A nonclustered index, on the other hand, contains only the columns included in the index’s key, plus a pointer pointing to the actual data row.

ms-sql-server-2008-database-development-training-kit-exam-70-433.NODRM.pdf

The difference between clustered and nonclustered SQL Server indexes is that a clustered index

1. Controls the physical order of the data pages.

2. Data pages of a clustered index will always include all the columns in the table, even if you only create the index on one column.

3. Clustered index leaf-level pages contain the data in the table.

4. Column(s) you specify as key columns affect how the pages are stored in the B-tree index structure.

Nonclustered index

1. Leaf-level pages contain the key value and a pointer to the data row in the clustered index or heap.

2. A nonclustered index does not affect the ordering and storing of the data.

3. Nonclustered index leaf-level pages contain the key value and a pointer to the data row in the clustered index or heap.

http://www.brentozar.com/archive/2012/07/sql-server-index-terms/

Index

When a clustered index is used, what is the B-Tree structure (picture)?

[image]

http://cdn.prod.brentozar.com/wp-content/uploads/2012/07/index-b-tree.png

Index - Clustered

What is a clustered index (using a book as metaphor)

1. Pages in a book represent a clustered index and they are clustered by page number.

2. The clustered index represents the actual physical order of your data.

3. The clustered index is the placement order of a table’s records in memory pages. When you insert new records, then each record will be inserted into the memory page in the order it belongs.

Morelan, Rick A. (2011-02-23). SQL Architecture Basics Joes 2 Pros: Core Architecture concepts (Volume 3) (Kindle Locations 3037-3039).  . Kindle Edition.

A book with page numbers on each page is a clustered index leaf pages. The "table" is physically sorted in by page number and the "data" (words on the page) are stored with page number.

OR

An apartment house has appartment numbers 1XX are on Floor 1; all appartemnt number 2XX are on Flloo2, etc. you know you need to go to the 2nd floor to find appt 213.

Pluralsight SQL Performance: Indexing Basics

Index - Clustered Index

4 questions

Index - Fill Factor

1. What is TSQL to see Fill Factor at server level?

2. What is TSQL to see Fill Factor at table/index level?

1.

SELECT *
FROM sys.configurations
WHERE name ='fill factor (%)'

2.

USE YourDatabaseName;
SELECT OBJECT_NAME(OBJECT_ID) Name, type_desc, fill_factor
FROM sys.indexes

Index - Heap

3 facts about a heap.

1 Extent = 8 Contiguous pages

[image]

One extent contains 8 contiguous pages

A non-clustered index is analogous to a book index. A book index indicates a key term (analogous to an index key or included column) with a page number (a pointer) to page on which the same pointer is located in addition to the actual data.

Pluralsight SQL Performance: Indexing Basics

Index - Page Splits

1. How can you avoid page splits?

1. Rebuild indexes frequently to empty the fillfactor space for more data.
2. Increased use of primary keys and use of clustered index on them.
3. A faster hardware system can also reduce page splits.

Actions to reduce the number of page splits:
1. Increase the fillfactor on your indexes.
2. Rebuild your indexes more often (to update the Fill Factor figure)
3. Add clustered indexes to your monotonically increasing primary keys.
4. Get a faster I/O subsystem.

http://careerride.com/SQL-Server-what-is-page-splits.aspx

5. Use Clustered Indexes on Identify Fields

SQL Architecture Basics Joes 2 Pros (Volume 3) location 3083 of 4817.

Index - Page Splits

1. What is TSQL for seeing page splits?

1.

SELECT cntr_value
FROM MASTER.dbo.sysperfinfo
WHERE counter_name ='Page Splits/sec' AND
OBJECT_NAME LIKE'%Access methods%'

SQL.Indexes.FillFactor.PDave.01.(12.05.13).pdf

Index - Page Splits

1. What is a page split?

2. Are page splits good or bad for performance?

1. Page splits arise when records from one memory page are moved to another page during changes to your table.

Note: A record (row) can't be split across multiple pages. 

 Source: SQL.Indexes.FillFactor.BEllul.(12.05.13) (1).pdf

2. Page splits are bad for performance for the following reasons:

Page splits are performed when there is no space in a data page to accommodate more inserts or updates. In a page spilt, SQL server removes some data from the current data page and moves it to another data page. This is done in order to make space for the new rows. 

Morelan, Rick A. (2011-02-23). SQL Architecture Basics Joes 2 Pros: Core Architecture concepts (Volume 3) (Kindle Location 3046).  . Kindle Edition.

[image]

Pluralsight SQL Performance: Indexing Basics

Index - Primary Key

What does SQL Server do it creates a primary key?

Index Fragmentation

How does it occur?

What are the 2 types?

How do you detect it?

Index Fragmentation - Internal

Index Fragmentation - External

Index Internal vs. External Fragmentation
Index Internal Fragmentation (pages are not full) is similar to what would occur if an existing bookcase was split into two bookcases. Each bookcase would then be only half full.

Index External Fragmentation (pages are not in logical sequence) relates to where the new bookcase would be physically located. It would probably need to be placed at the end of the library, even though it would "logically" need to be in a different order. That means that to read the bookcases in order, you could no longer just walk directly from bookcase to bookcase but would need to follow pointers around the library to follow a chain from one bookcase to another.

Index Selectivity

Index Density

Index Depth

[image]

Density is a measure of the lack of uniqueness of the data in a table. A dense table is one that has a high number of duplicates.

Index Depth is a measure of the number of levels from the root node to the leaf nodes. Users often imagine that SQL Server indexes are quite deep but the reality is quite different to this.

The large number of children that each node in the index can have produces a very flat index structure. Indexes with only 3 or 4 layers are very common.

Indexes - Pages and Extents

1. How many bytes is in a page?

2. What is maximum row length in bytes?

3. How many pages are in 1 MB?

4. What does each page begin with?

4. What is an extent?

1. A page is 8K bytes (8,060 bytes)

2. Row maximum: 8K bytes

3. SQL Server databases have 128 pages / 1 MB

4. Each page begins with 96-page header

5. Extents are a collection of 8 contiguous pages

Source: Pluralsight.com; SQL Server Performance: Indexing Basics; Indexing Fundamentals

The APPLY operator comes in two variants, the CROSS APPLY and the OUTER APPLY.

The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table expression. In other words, the right table expression returns rows for left table expression match only.  Whereas the OUTER APPLY operator returns all the rows from left table expression irrespective of its match with the right table expression.  For those rows for which there are no corresponding matches in right table expression, it contains NULL values in columns of right table expression. So you might now conclude, the CROSS APPLY is semantically equivalent to INNER JOIN (or to be more precise its like a CROSS JOIN with a correlated sub-query) with a implicit join condition of 1=1 whereas OUTER APPLY is semantically equivalent to LEFT OUTER JOIN.

You might be wondering if the same can be achieved with regular JOIN clause then why and when to use APPLY operator? Though the same can be achieved with normal JOIN, the need of APPLY arises if you have table-valued expression on right part and also in some cases use of APPLY operator boost the performance of your query. Let me explain you with help of some examples.

http://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

JOINs - CROSS APPLY

What does it do?

NULL

1. Explain how ON, WHERE, and HAVING treat UNKNOWN (NULL)

2. Explain how the CHECK contraint handles UNKNOWN (NULL)

3. When NULLs treated as equal values

1. All query filters (ON, WHERE, and HAVING) treat UNKNOWN like FALSE. A row for which a filter is UNKNOWN is eliminated from the result set.

2. On the other hand, an UNKNOWN value in a CHECK constraint is actually treated like TRUE.

Suppose you have a CHECK constraint in a table to require that the salary column be greater than zero. A row entered into the table with a NULL salary is accepted because (NULL > 0) is UNKNOWN and treated like TRUE in the CHECK constraint.

3. NULLs are treated as equal:
■ You cannot insert into a table two rows with a NULL in a column that has a
UNIQUE constraint defi ned on it. T-SQL violates the standard on this point.
■ A GROUP BY clause groups all NULLs into one group.
■ An ORDER BY clause sorts all NULLs together.
■ Set operators treat NULLs as equal when comparing rows from the two sets.

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf page 9

NULL value

Indicate 3 things regarding what is unique about NULL value logic?

Dealing with UNKNOWN logical results and NULLs can be very confusing. While NOT TRUE is FALSE, and NOT FALSE is TRUE, the opposite of UNKNOWN (NOT UNKNOWN)is still UNKNOWN.

The mark NULL represents a missing value. When comparing a missing value to another value (even another NULL), the logical result is always UNKNOWN.

The UNKNOWN logical value in SQL typically occurs in a logical expression that involves a NULL (for example, the logical value of each of these three expressions is UNKNOWN:NULL > 42; NULL = NULL; X + NULL > Y).

Normalization - FORMS

1. What is first normal form?

2. What is second normal form?

3. What is third normal form?

• First Normal Form (1NF): Each field in a table must contain different information.
• Second Normal Form (2NF): All attributes that are not dependent upon the primary key in a database table must be eliminated.
• Third Normal Form (3NF): No duplicate information is permitted. So, for example, if two tables both require a common field, this common field information should be separated into a different table.

Normalization - FORMS

1.Describe the 5 normal forms in terms of the relationships they deal with.

1. The First Normal Form (1NF) addresses: 1) the structure of an isolated table; 2) the identification of repeating groups (repeating columns).

Repeating Groups are indicated by rows that repeat columns:

{ PartID, Supplier1ID, Supplier2ID, Supplier3ID }

2. The Second (2NF), Third (3NF), and Boyce-Codd (BCNF) Normal Forms: 1)  address one-to-one and one-to-many relationships; and (2)  functional dependency of key and non-key columns in the same table.

Functional dependency: Given a key, the values of all the other attributes (columns) in row are fixed.

3. The Fourth (4NF) and Fifth (5NF) Normal Forms deal with many-to-many relationships.

SQL.Normalization.PracticalDBDesign.Part2.PJanert.2003.(11.24.13).pdf, p. 3.

Normalization - FORMS - 1NF

1. Describe First Normal Form.

2. Describe how to change a table that violates 1NF into table(s) that conform to 1NF.

1. Table in 1NF definition:

A table is said to be in First Normal Form (1NF), if all entries in it are scalar-valued.

Relational database tables are 1NF by construction since vector-valued entries are forbidden. Vector-valued data (that is, entries which have more than one value in each row) are referred to as repeating groups.

2. Changing a table to meet 1NF

The following relation (table) violates 1NF because the SupplierID forms a repeating group. Repeating groups indicate 1-to-many relationships

{ PartID, Supplier1ID, Supplier2ID, Supplier3ID }

To achieve 1NF

1. Remove the repeating groups in the base table by putting them into new separate table(s), one for for each set of related data comprising the repeating group(s).

2. Connect the new separate table(s) to the original table by putting the PK of the original table into the new separate table(s) as an FK.

Sample solution:

Part Table

PartID (PK)

PartName

Supplier Table

SupplierID (PK)

PartID (FK)

SupplierName

SELECT PartID, PartName, SupplerID, SupplierName

FROM Part p

INNER JOIN Supplier s

ON p.PartID = s.PartID

Normalization - FORMS - 2NF

 1. Explain 2NF.

2. How do you fix a table that does not conform to 2NF?

1. A table is in Second Normal Form (2NF) if every non-key field is a fact about the entire key.

In other words, a table is 2NF if it is 1NF and all non-key attributes are functionally dependent on the entire primary key (that is, the dependency is irreducible).

Clearly, 2NF is only relevant when the key is composite (that is, consisting of several fields).

The following example describes a table which is not 2NF since the WarehouseAddress attributedepends only on WarehouseID but not on PartID:

{ PartID, WarehouseID, Quantity, WarehouseAddress }

2. To achieve 2NF, create separate tables for sets of values that apply to multiple records and relate these tables through foreign keys. The determinants of the initial table become the primary keys of the resulting tables.

Normalization - Functional Dependency

What is Functional Dependency?

1. The Second and Third Normal Forms address dependencies among attributes, specifically between key and non-key fields.

2. In a Functional Dependency, there is a determinate attribute (X) and there is a dependent attribute (Y).

Determinate is analogous to Independent Variable. (X)

Dependent is analogous to Dependent Variable (Y).

3. For each (every) determinate value (X) there

4. Note that functional dependency is a semantic relationship: It is the business logic of the problem domain, represented by the relation, which determines whether a certain X determines Y.

SQL.Normalization.PracticalDBDesign.Part2.PJanert.2003.(11.24.13).pdf

Normalization - Process

What is the normalization process?

Normalization is a process in which an initial DB design is transformed, or decomposed, into a different, but equivalent, design.

The resulting schema is equivalent to the original one in the sense that no information is lost when going from one to the other.

The normalization procedure consists of a sequence of projections -- that is, some attributes are extracted from one table to form a new one. In other words, tables are split up vertically.

The decomposition is lossless, only if you can restore the original (non-conforming normal form) table by joining to its projections (which are additional tables with the originally "non-conforming" columns).

OUTER APPLY

What does it do?

Predicates

1. What are predicates?

2. In which elements are predicates specified?

3. How do you combine predicates?

1. Predicates are logical expressions that evaluate to TRUE, FALSE, or UNKNOWN.

2. T-SQL has language elements in which predicates can be specified—for example, query filters such as WHERE and HAVING, CHECK constraints, and others.

Remember that predicates are logical expressions that evaluate to TRUE, FALSE, or UNKNOWN.

3. You can combine predicates by using logical operators such as AND and OR. You can also involve other types of operators, such as comparison operators, in your expressions.

SQL.microsoft_sql_server_2012_t-sql_fundamentals.Ben-Gan.pdf, p. 50

Predicates

What is a predicate in a SQL statement?

FROM TABLE_NAME WHERE predicate

Simple predicates use one of the operators =, <>, >, >=, <, <=, IN, BETWEEN, LIKE, IS NULL or IS NOT NULL.

Predicates can be enclosed in parentheses if desired. The keywords AND and OR can be used to combine two predicates into a new one. If multiple combinations are applied, parentheses can be used to group combinations to indicate the order of evaluation. Without parentheses, the AND operator has a stronger binding than OR.

Query

A database contains tables named Sales and SalesArchive. SalesArchive contains historical sales
data. You configure Change Tracking on the Sales table. The minimum valid version of the Sales
table is 10. You need to write a query to export only sales data that changed since version 10,
including the primary key of deleted rows.
Which method should you use?
A. FROM SalesRIGHT JOIN CHANGETABLE (CHANGES Sales, 10) AS C ...
B. FROM SalesINNER JOIN CHANGETABLE (CHANGES Sales, 10) AS C ...
C. FROM SalesRIGHT JOIN CHANGETABLE (CHANGES SalesArchive, 10) AS C ...
D. FROM SalesINNER JOIN CHANGETABLE (CHANGES SalesArchive, 10) AS C ...

A. FROM Sales RIGHT JOIN CHANGETABLE (CHANGES Sales, 10) AS C ...

Microsoft.PracticeTest.70-433.v2013-01-17.by.SQLlearner.145q.vce Q11

Query

You administer a Microsoft SQL Server 2008 database that contains tables named Sales.Customer and Sales.SalesOrder.
A diagram of the tables is shown in the exhibit. (Click the Exhibit button.)

You need to execute a query to update the value of the CustomerValue field to HV when a customer has more than 5 orders for a total sales amount of more than 5,000 U.S. dollars. Which Transact-SQL statement should you use?

Practice Test SQLLearner.Q76

UPDATE Sales.Customer
SET CustomerValue = 'HV'
FROM Sales.Customer
WHERE CustomerID IN (SELECT c.CustomerID
                                    FROM Sales.Customer c
                                    INNER JOIN Sales.SalesOrder o ON o.CustomerID=c.CustomerID
                                    GROUP BY c.CustomerID
                                    HAVING COUNT(*) > 5 AND SUM(SalesAmount) > 5000)

UPDATE u
SET CustomerValue = 'HV'
FROM Sales.Customer u
WHERE EXISTS(SELECT c.CustomerID
                         FROMSales.Customer c
                         INNER JOIN Sales.SalesOrder o ON o.CustomerID=c.CustomerID
                         WHERE c.CustomerID=u.CustomerID
                         GROUP BY c.CustomerID
                         HAVING COUNT(*) > 5 AND SUM(SalesAmount) > 5000)

Query

You have two tables named Customer and SalesOrder. In the Customer table you have 1000 customers, of which 900 customers have orders in the SalesOrder table. You execute the following query to list all customers that have had at least one sale.
SELECT * FROM Customer

WHERE Customer.CustomerID IN (SELECT SalesOrder.CustomerID
FROM SalesOrder)

You need to identify the results of the query. 

Which results will the query return?

A. No rows

B. The 900 rows in the Customer table with matching rows in the SalesOrder table

C. The 1000 rows in the Customer table

D. A warning message

B. The 900 rows in the Customer table with matching rows in the SalesOrder table

Query

You need to generate the following XML document.
<ProductExport>
<Product Price="99">Product1</Product>
<Product Price="199">Product2</Product>
<Product Price="299">Product3</Product>
<Product Price="399">Product4</Product></ProductExport>

Which query should you use?

A. SELECT Price, ProductName
FROM Products FOR XML AUTO, ROOT('ProductExport')
B. SELECT Price [@Price],ProductName AS [*]
FROM Products FOR XML PATH('Product'),ROOT('ProductExport')
C. SELECT Price, ProductName
FROM Products AS ProductExport FOR XML PATH('Product')
D. SELECT Price [@Price], ProductName AS [*]
FROM Products AS ProductExport FOR XML AUTO, ELEMENTS

B. SELECT Price [@Price],ProductName AS [*]
FROM Products FOR XML PATH('Product'),ROOT('ProductExport')

Microsoft.PracticeTest.70-433.v2013-01-17.by.SQLlearner.145q.vce Q 132

Query Processing - 01

What is the logical order of query processing?

1. FROM

(1-J1) Cartesian Product

(       ) JOIN table

(1-J2) ON Filter or join condition

(1-J3) Add Outer Rows.

2. WHERE Predicates / Clauses

3. GROUP BY Columns

4. HAVING Condition

5. SELECT

5a. Evaluate expressions

5b. Apply DISTINCT clause

5c Apply TOP option

6. ORDER BY

6a. TOP / OFFSET-FETCH

9. FOR XML

SQL.CheatSheet.2008.MidnightDBA.(11.07.13).pdf

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 02a

Describe the Perform Cartesian Product phase.

1. This is the first of three subphases that are applicable to a join table operator. This subphase performs a Cartesian product (a cross join, or an unrestricted join) between the two tables involved in the join and, as a result, generates virtual table VT1-J1.
2. If the left table contains n rows and the right table contains m rows, VT1-J1 will contain n×m rows. The columns in VT1-J1 are qualified (prefixed) with their source table names (or table aliases, if you specified them in the query).

3. In the subsequent steps (step 1-J2 and on), a reference to a column name that is ambiguous (appears in more than one input table) must be table-qualifi ed (for example, C.customerid).

4. Specifying the table qualifier for column names that appear in only one of the inputs is optional (for example, O.orderid or just orderid).

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 02a

Describe the FROM phase

(1) The FROM phase identifies the query’s source tables and processes table operators.

Each table operator applies a series of subphases.

For example, the phases involved in a join are:

(1-J1) Cartesian Product,

(1-J2) ON Filter,

(1-J3) Add Outer Rows.

■ (1-J1) Cartesian Product This phase performs a Cartesian product (cross join) between the two tables involved in the table operator, generating VT1-J1.

■ (1-J2) ON Filter This phase fi lters the rows from VT1-J1 based on the predicate that appears in the ON clause (<on_predicate>). Only rows for which the predicate evaluates to TRUE are inserted into VT1-J2.

■ (1-J3) Add Outer Rows If OUTER JOIN is specified (as opposed to CROSS JOIN or INNER JOIN), rows from the preserved table or tables for which a match was not found are added to the rows from VT1-J2 as outer rows, generating VT1-J3.

 The FROM phase generates virtual table VT1-J1 or VT-J2 or VT-J3

ebook_Inside_Microsoft_Server_2008_T_SQL_Querying.Ben-Gan.pdf

Query Processing - 02a

Describe the FROM phase.

1. The FROM phase identifies the table or tables that need to be queried, and if table operators are specified, this phase processes those operators from left to right.

2. Each table operator operates on one or two input tables and returns an output table. The result of a table operator is used as the left input to the next table operator—if one exists—and as the input to the next logical query processing phase otherwise.

3. Each table operator has its own set of processing subphases.

For example, the subphases involved in a join are:

(1-J1) Cartesian Product

(1-J2) ON Filter

(1-J3) Add Outer Rows.

Here I will provide a description of the subphases involved in a join; later in the chapter, under “Table Operators,” I’ll describe the other table operators. The FROM phase generates virtual table VT1.

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 02b

Describe the APPLY ON Filter (Join Condition).

1. The ON filter is the first of three possible fi lters (ON, WHERE, and HAVING) that can be specified in a query.

2. The predicate in the ON fi lter is applied to all rows in the virtual table returned by the previous step (VT1-J1).

3. Only rows for which the on_predicate> is TRUE become part of the virtual table returned by this step (VT1-J2).

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 02c

Describe Add Outer Rows phase

1. This step occurs only for an outer join. For an outer join, you mark one or both input tables as preserved by specifying the type of outer join (LEFT, RIGHT, or FULL).

2.Marking a table as preserved means that you want all of its rows returned, even when filtered out by the <on_predicate>.

3. A left outer join marks the left table as preserved, a right outer join marks the right one, and a full outer join  marks both.

4. Step 1-J3 returns the rows from VT1-J2, plus rows from the preserved table(s) for which a match was not found in step 1-J2. These added rows are referred to as outer rows.

5. NULLs are assigned to the attributes (column values) of the nonpreserved table in the outer rows. As a result, virtual table VT1-J3 is generated.

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 02c

Describe the Add Outer Rows subphase.

1. This step occurs only for an outer join. For an outer join, you mark one or both input tables as preserved by specifying the type of outer join (LEFT, RIGHT, or FULL).

2. Marking a table as preserved means that you want all of its rows returned, even when fi ltered out by the <on_predicate>.

3. A left outer join marks the left table as preserved, a right outer join marks the right one, and a full outer join marks both.

4. Step 1-J3 returns the rows from VT1-J2, plus rows from the preserved table(s) for which a match was not found in step 1-J2.

5. These added rows are referred to as outer rows. NULLs are assigned to the attributes (column values) of the nonpreserved table in the outer rows. As a result, virtual table VT1-J3 is generated.

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 03

Describe the WHERE phase.

(2) WHERE This phase fi lters the rows from VT1 based on the predicate that appears in the WHERE clause (<where_predicate>). Only rows for which the predicate evaluates to TRUE are inserted into VT2.

Caution
1. Because the data is not yet grouped, you cannot use aggregates here—for example, you cannot write WHERE orderdate = MAX(orderdate).

2. Also, you cannot refer to column aliases created by the SELECT list because the SELECT list was not processed yet—for example, you cannot write SELECT YEAR(orderdate) AS orderyear . . . WHERE orderyear > 2008.

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 03b

What is the difference between WHERE and ON in query containing an outer join?

A confusing aspect of queries containing an OUTER JOIN clause is whether to specify a logical expression in the ON filter or in the WHERE filter.

The main difference between the two is that ON is applied before adding outer rows (step 1-J3), while WHERE is applied afterwards.

An elimination of a row from the preserved table by the ON filter is not final because step 1-J3 (Add Outer Rows) will add it back; an elimination of a row by the WHERE filter, by contrast, is final. Bearing this in mind should help you make the right choice.

Tip This logical difference between the ON and WHERE clauses exists only when using an outer join. When you use an inner join, it doesn’t matter where you specify your logical expressions because step 1-J3 is skipped. The filters are applied one after the other with no intermediate step between them.

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 04

Describe thed GROUP BY phase

3) GROUP BY This phase arranges the rows from VT2 in groups based on the column list specifi ed in the GROUP BY clause, generating VT3. Ultimately, there will be one result row per group.

The GROUP BY phase associates rows from the table returned by the previous step to groups according to the <group_by_specification>. I will discuss this specification in detail in Chapter 8, “Aggregating and Pivoting Data,” but for now, assume that it specifies a single list of attributes to group by. This list is called the grouping set.

In this phase, the rows from the table returned by the previous step are arranged in groups. Each unique combination of values of the attributes that belong to the grouping set identifies a group. Each base row from the previous step is associated to one and only one group.

Virtual  table VT3 consists of the rows of VT2 arranged in groups (the raw information) along with the group identifiers (the groups information).

Eventually, a query that contains a GROUP BY clause will generate one row per group (unless filtered out). Consequently, when GROUP BY is specified in a query, all subsequent steps (HAVING, SELECT, and so on) can specify only expressions that have a scalar (singular) value per group.

These expressions can include columns or expressions from the GROUP BY list—such as C.customerid in the sample query here—or aggregate functions, such as COUNT(O.orderid).

This phase considers NULLs as equal. That is, all NULLs are grouped into one group, just like a known value.

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 05

Describe the HAVING phase.

(4) HAVING This phase filters the groups from VT3 based on the predicate that
appears in the HAVING clause (<having_predicate>). Only groups for which the
predicate evaluates to TRUE are inserted into VT4.

The HAVING filter is applied to the groups in the table returned by the previous step. Only groups for which the <having_predicate> is TRUE become part of the virtual table returned by this step (VT4).

The HAVING filter is the only filter that applies to the grouped data.

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 06

Describe the SELECT phase

(5) SELECT This phase processes the elements in the SELECT clause, generating VT5.
(5-1) Evaluate Expressions This phase evaluates the expressions in the SELECT list, generating VT5-1.
(5-2) DISTINCT This phase removes duplicate rows from VT5-1, generating VT5-2.
(5-3) TOP This phase fi lters the specifi ed top number or percentage of rows from VT5-2 based on the logical ordering defi ned by the ORDER BY clause, generating the table VT5-3.

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 06a

Describe the DISTINCT phase

If a DISTINCT clause is specifi ed in the query, duplicate rows are removed from the virtual table returned by the previous step, and virtual table VT5-2 is generated.

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 06b

Describe the role of DISTINCT in implementing the relational model.

Note

1. SQL deviates from the relational model by allowing a table to have duplicate rows (when a primary key or unique constraint is not enforced) and a query to return duplicate rows in the result.

2. A relation in the relational model represents a set from set theory, and a set (as opposed to a multiset) has no duplicates.

3. Using the DISTINCT clause you can ensure that a query returns unique rows and in this sense conform to the relational model.

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 06c

Describe the TOP option.

1. The TOP option is a feature specifi c to T-SQL that allows you to specify a number or percentage of rows (rounded up) to return.

2. The specified number of rows is selected based on the query’s ORDER BY clause.Traditionally, and according to the ANSI SQL standard, ORDER BY is supposed to serve a presentation purpose.

3. However, when the TOP option is specified, the ORDER BY clause also serves a logical purpose— answering the question “top according to what order?”
Table VT5-3 is generated.

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 06d

Describe the relationship between TOP and ORDER BY and comment on deterministic and nondeterministic results.

1. The TOP option processing relies on the query’s ORDER BY clause to determine which rows are considered the “first” requested number of rows.

2. If an ORDER BY clause with a unique ORDER BY list is specified in a query, the result is deterministic. That is, only one correct result is possible, containing the first requested number of rows based on the specified order.

3. Similarly, when an ORDER BY clause is specified with a non-unique ORDER BY list but the TOP option is specified WITH TIES, the result is also deterministic. SQL Server inspects the last row that was returned and returns all other rows from the table that have the same sort values as the last row.
4. However, when a non-unique ORDER BY list is specified without the WITH TIES option, or ORDER BY is not specifi ed at all, a TOP query is nondeterministic.

5. That is, the rows returned are the ones that SQL Server happened to access first, and there might be different results that are considered correct.

6. If you want to guarantee determinism, a TOP query must have either a unique ORDER BY list or the WITH TIES option.

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 07a

Describe the ORDER BY phase.

6) ORDER BY This phase sorts the rows from VT5-3 according to the column list
specified in the ORDER BY clause, generating the cursor VC6.

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 07a

Describe the ORDER BY phase.

1. The rows from the previous step are sorted according to the column list specified in the ORDER BY clause, returning the cursor VC6.

2. The ORDER BY clause is the only step where column aliases created in the SELECT phase can be reused.
3. If DISTINCT is specified, the expressions in the ORDER BY clause have access only to the virtual table returned by the previous step (VT5).

4. If DISTINCT is not specified, expressions in the ORDER BY clause can access both the input and the output virtual tables of the SELECT phase.

That is, in the ORDER BY clause you can specify any expression that would have been allowed in the SELECT clause. Namely, you can sort by expressions that you don’t end up returning in the final result set.

5. There is a reason for not allowing access to expressions you’re not returning if DISTINCT is specified. When adding expressions to the SELECT list, DISTINCT can potentially change the number of rows returned. Without DISTINCT, of course, changes in the SELECT list don’t affect the number of rows returned.

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - 07a

What is an important difference between the ORDER BY phase and all other steps in query processing?

Important

1. This step is different than all other steps in the sense that it doesn’t return a valid table; instead, it returns a cursor.

2. Remember that SQL is based on set theory. A set doesn’t have a predetermined order to its rows: It’s a logical collection of members, and the order of the members shouldn’t matter.

3. A query with a presentation ORDER BY clause returns an object with rows organized in a particular order. ANSI calls such an object a cursor.

4. Understanding this step is one of the most fundamental steps to correctly understanding SQL.

Query Processing - 07b

What is the implication of the ORDER BY phase returning a cursor?

1. Because this step doesn’t return a table (it returns a cursor), a query with a presentation ORDER BY clause cannot be used to define a table expression—that is, a view, an inline table-valued function, a derived table, or a common table expression (CTE).

2. Rather, the result must be returned to the client application that can consume cursor records one at a time, in order.

SQL.ebook_Inside_Microsoft_Server_2008_T_SQL_Querying_Ben-Gan.pdf

Query Processing - All-at-Once Operations

1. Explain this concept.

2. Give an example.

SQL supports a concept called all-at-once operations, which means that all expressions that appear in the same logical query processing phase are evaluated logically at the same point in time.

SELECT col1, col2
FROM dbo.T1
WHERE col1 <> 0 AND col2/col1 > 2;

You can see that if SQL Server decides to process the expression
10/col1 > 2 first, this query might fail because of a divide-by-zero error.

SQL.microsoft_sql_server_2012_t-sql_fundamentals.Ben-Gan.pdf, p. 60

Query Processing - All-at-Once Operations

 Explain the CASE statement overcoming a pitfull of All-at-Once Operations.

For example, the order in which the WHEN clauses of a CASE expression are evaluated is guaranteed. So you could revise the query as follows.
SELECT col1, col2
FROM dbo.T1
WHERE
CASE
WHEN col1 = 0 THEN 'no' -- or 'yes' if row should be returned
WHEN col2/col1 > 2 THEN 'yes'
ELSE 'no'
END = 'yes';

Note: Better solution: (Objective: avoid dividing by zero).

SELECT col1, col2
FROM dbo.T1
WHERE (col1 > 0 AND col2 > 2*col1) OR (col1 < 0 AND col2 < 2*col1);

SQL.microsoft_sql_server_2012_t-sql_fundamentals.Ben-Gan.pdf, p. 60

SQL Server - Instances

What are SQL Server instances?

An instance of the Database Engine is a copy of the sqlservr.exe executable that runs as an operating system service. Each instance manages several system databases and one or more user databases. Each computer can run multiple instances of the Database Engine. Applications connect to the instance in order to perform work in a database managed by the instance. http://technet.microsoft.com/en-us/library/hh231298.aspx

Beginning with SQL Server 2000, you can run multiple copies of the software, using what Microsoft calls Instances. Instances share a few files between them, mostly dealing with client tools. This allows you to have two different system administrators (sa accounts) and other server-level security on the same hardware. So if you have different security needs, say running more than one company with different administrators, you can install multiple copies of SQL Server on the same hardware. http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=29

You can run multiple instances of the Database Engine on a computer. One instance can be the default instance. The default instance has no name. If a connection request specifies only the name of the computer, the connection is made to the default instance. A named instance is one where you specify an instance name when installing the instance. A connection request must specify both the computer name and instance name in order to connect to the instance. There is no requirement to install a default instance; all of the instances running on a computer can be named instances. http://technet.microsoft.com/en-us/library/hh231298.aspx

SQL Server - Instances

1. What is a named instance?

2. How do you install a named instance?

3. How do you identtify named instance?

2. You can create a named instance as the first instance you install. Or you can install a default instance first. Then you can put the CD back into the slot and repeat the installation, this time choosing the option ot install a new instance. You must give the this new (second or greater instance) a name.

3. For named Instances: NameOfComputer\NameOfInstance

http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=29

SQL Server - Instances

What is a default instance?

When you install SQL Server on a system, one copy of the software can be (but doesn’t have to be) designated as the Default Instance.

It doesn’t have a special network name; it works by using the name of the computer. So in any client tools or programs, when you want to talk to a Default SQL Server Instance you just enter the name of the computer it runs on, like BUCKTEST for the BUCKTEST Windows Server. The network information for the Default Instance for named pipes connections is \\.\pipe\sql\query.

You can only have one version of SQL Server as the Default Instance; that is, you can’t have two Default Instances. That kind of stands to reason, since only the computer name is used to identify the Instance.

http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=29

Schemabinding - View

What is schemabinding for a view?

Once you create a view with schemabinding, you cannot change the underlying tables in a way that would break the view.  Examples of this would be removing columns or dropping tables that are specified in the view.

When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement (in the view) must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Microsoft SQL Server 2005 Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

SCHEMABINDING cannot be specified if the view contains alias data type columns.

Subquery

What are 4 important facts regarding subqueries in the FROM section.

 Hints:

A subquery in FROM section:

1. Creates _________ table.

2. Useful for breaking _______ queries.

3. Subquery in FROM section must be _________.

4. In FROM section, subquery is also known as ________ table.

 A subquery in FROM section:

1. Creates a dynamic table

2. Useful for breaking down queries

3. Query must be aliased.

4. Also known as derived table. (See Chapter 05, page 157 SQL.TSQLFundamentals2012.Ben-Gan.pdf)

Pluralsight; SQL Server 2012 Querying (70-461) Part 1; Slide title: FROM line

Subquery

What is most important thing about placing Subquery into the SELECT line.

Subquery must return a scalar value.

Subquery

What is value of placing a subquery into a WHERE statement?

Subquery

What predicates are used with subqueries?

NOTE: Definition was lost in conversion from HTML.

Subquery

1. What are the key subquery concepts?

2. What can you "sneak in" to a subquery in the WHERE statement?

Subquery

 What is example of subquery in SELECT section?

[image]

Pluralsight; SQL Server 2012 Querying (70-461) Part 1; Slide title: SELECT line

Subquery - 01

1. Where are the 3 places you can put subqueries?

2. In one of these 3 places, the subquery is also known as a _______ ________ or a table _________.

1. You can put subquery

1. SELECT section

2. FROM section

3. WHERE section

2. In a FROM section, the subquery is also known as a derived table or a table subquery.

 SQL.TSQLFundamentals2012.Ben-Gan.pdf

Subquery - Correlated

What is another name for "correlated" subquery?

Subquery - Correlated

1. What is one really important requirement for a subquery in a SELECT statement?

2. What is example of correclated subquery in a SELECT statement?

1. A correlated  subquery in a SELECT statement must return a scalar (single) value for each row returned by the outer query.

2.

SELECT
SalesOrderNumber,
SubTotal,
OrderDate,

CASE WHEN

(
SELECT SUM(LineTotal)

FROM Sales.SalesOrderDetail d
WHERE d.SalesOrderID = h.SalesOrderID
) = h.SubTotal THEN 'balanced'

ELSE 'not balanced'
END AS LineTotals

FROM
Sales.SalesOrderHeader h;

Subquery - Correlated

What is a correlated subquery?

1. Correlated subquery refers to attributes from the table that appears in the outer query.

2. The subquery is dependent on the outer query and cannot be invoked independently.

3. Logically, the subquery is executed / evaluated separately for each outer row. 4. Logically, the outer query feeds the subquery row by row.

Subquery - Correlated

Where can you put correlated subqueries?

You can put correlated subqueries in

1. SELECT statements

2. WHERE clauses

Subquery - Derived Table

1. What is another name for a subquery in the FROM clause?

2. What does a subquery in a FROM clause return?

1. A derived table.

2. A subquerty in a FROM clause can return a table (row set). It is NOT limited to scalar result set.

www.simple-talk.com/content/print.aspx?article=1297

Subquery - Derived Table

How do you specify the query that defines the derived table?

Hints:

You specify the query that defines the derived table ________  __________, followed by the __________ clause and the derived table _________.

Subquery - Derived Table

Please explain derived table.

SELECT
p.ProductID,
p.Name AS ProductName,
p.ProductSubcategoryID AS SubcategoryID,
ps.Name AS SubcategoryName
FROM
Production.Product p INNER JOIN
(
SELECT ProductSubcategoryID, Name
FROM Production.ProductSubcategory
WHERE Name LIKE '%bikes%'
) AS ps
ON p.ProductSubcategoryID = ps.ProductSubcategoryID;

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

The first thing to notice is that the subquery returns a derived table that includes two columns and multiple rows.

Because the subquery returns a table, I can join that table, which I’ve named ps, to the results from the Product table (p).

As the join demonstrates, you treat a subquery used in the FROM clause just as you would treat any table.

I could have just as easily created a view or temporary table—or even added a regular table to the database—that accesses the same data as that available through the subquery.

I defined the join based on the subcategory ID in the derived table and Product table. I was then able to include columns from both these tables in the SELECT list, as I would any type of join. The following table shows a subset of
the results returned by the outer query.

https://www.simple-talk.com/content/print.aspx?article=1297

Subquery - WHERE NOT IN and NULL logic

What is one fix to explicitly avoid the NOT IN and NULL problems?

All queries that you write should consider all 3 possible truth values of 3-valued logic: (TRUE, FALSE and UNKNOWN).

When you want to check whether a customer ID appears in the set of known values and ignore the NULLs, you should exclude the NULLs explicity or implicitly:

Explcity:

SELECT custid, copanyname

FROM Sales.Customers AS C

WHERE custid NOT IN (SELECT O.custid

FROM Sales.Orders AS O

WHERE O.custid IS NOT NULL)

Microsoft SQL Server 2008 Fundamentals, Ben-Gan, Page 148

Subquery - WHERE NOT IN and NULL logic

What is the method for implicitly avoiding the NULL logic gotcha in subqueries?

Use NOT EXISTS

SELECT custid, companyname

FROM Sales.Customers as C

WHERE NOT EXISTS (SELECT * FROM Sales.Orders as O

WHERE O.custid = C.custid

Unlike IN, EXIST uses 2-valued predicate logic . EXISTS always returns TRUE or FALSE and never returns UNKNOWN. When the subquery stumbles into a NULL in O.custid, the expression evaluates to UNKNOWN and the is filtered out (because it is not TRUE and it is not FALSE)

Microsoft SQL Server 2008 Fundamentals, Ben-Gan, Page 148.

Subquery - WHERE NOT IN and Null logic

1. What is the "gotcha" regarding the following query when there is at least one NULL marker as a custid in the table Sales.Orders?

SELECT custid, companyname

FROM Sales.Customers AS C

WHERE custid NOT IN  (SELECT O.custid

FROM Sales.Orders A O)

1. When you use the NOT IN predicate against a subquery that returns at least one NULL, the outer query always returns an empty set.

Reason: A NULL is UNKNOWN. Negating the UNKNOWN with the NOT (NOT IN) operator still yields UNKNOWN and UNKNOWN in a query filter is filtered out. This means that in a case where it is unknown whether a customer ID appears in a set, it also unknown whether it does not appear in the set.

Microsoft SQL Server fundamentals, Ben-Gan Page 148.

Subquery -- Derived Table

What is scope of existence of Derived Table?

TABLES / VIEWS

Implementing Tables and Views

WITH ENCRYPTION clause

1. Where is it used?

2. What is it used for?

3. What does it prevent?

http://www.ucertify.com/?action=Next&chapter_guid=00QII&item_sequence=23&total_notes=109&func=navigate_flashcard&useraction=

WITH ENCRYPTION clause

TABLES / VIEWS

Implementing Tables and Views

WITH SCHEMABINDING clause

1. What does it safeguard against?

2. What does it prevent?

WITH VIEW_METADATA clause

3. When you want to a view's metadata, what do you do?

4. What does it allow to be returned?

WITH SCHEMABINDING clause

TSQL Language Structure

1. What are the 4 main elements of TSQL?

DML (Data Manipulation Language) - Affect records in a table

SELECT

INSERT

UPDATE

DELETE

DDL (Data Definition Language) - Handle design of database objects

CREATE

ALTER

DROP

DCL (Data Control Language) - Control level of access to securables

GRANT

REVOKE

DENY

TCL (Transaction Control Language) - Allow you to manage transactions

BEGIN TRAN

COMMIT TRAN

ROLLBACK TRAN

Source

Table Expression

What is a table expression?

A table expression is a named query expression that represents a valid relational table.

SQL.TSQLFundamentals2012.Ben-Gan.pdf Chapter 5.

Table Expression

When you query a table expression, what happens to the inner query?

Hint:

The ______ query and the ________ query are ________ into _____ query.

When you query a table expression, the inner query gets unnested. In other words, the outer query and the inner query are merged into one query directly against the underlying objects.

SQL.TSQLFundamentals2012.Ben-Gan.pdf Chap 5

Table Expressions

Are table expressions materialized or are they virtual?

Table expressions are not physically materialized anywhere—they are virtual.

SQL.TSQLFundamentals2012.Ben-Gan.pdf Chap 5.

Table Expressions

What are the 4 types of table expressions that SQL Server supports?

Microsoft SQL Server supports four types of table expressions:

1. derived tables

2. common table expressions (CTEs)

3. views

4. inline table-valued functions (inline TVFs)

SQL.TSQLFundamentals2012.Ben-Gan.pdf Chap 5.

Table Expressions

What are the benefits of using table expressions?

Hints:

1. Are the benefits logical or performance based?

2. Table expression help you ________ code by using a _________ approach.

1. The benefits of using table expressions are typically related to logical aspects of your code and not to performance.

2. Table expressions help you simplify your solutions by using a modular approach

SQL.TSQLFundamentals2012.Ben-Gan.pdf Chap 5

Table Expressions

What is key benefit of table expressions with regard to column aliases?

Hint:

In any clause of the ________ query, you can refer to column aliases assigned in the SELECT clause of the _________ query.

Table expressions give you the ability to refer to column aliases assigned in the SELECT clause in query clauses that are logically processed before the SELECT clause.

In other words:

One of the benefits of using table expressions is that, in any clause of the outer query, you can refer to column aliases that were assigned in the SELECT clause of the inner query.

SQL.TSQLFundamentals2012.Ben-Gan.pdf Chap 5

Tables / Views

What are the 4 types of table expressions?

These are the types of table expressions:

1. Views

2. An inline table-valued functio

3. Derived table

4. Common table expression (CTE)

Tables / Views - Truncate and Delete

What will truncate do?

What is syntax?

Basic Function: TRUNCATE TABLE is a statement that quickly deletes all records in a table by deallocating the data pages used by the table.

Pros: This reduces the resource overhead of logging the deletions, as well as the number of locks acquired;

Pros: The advantage to using TRUNCATE TABLE is that in addition to removing all rows from the table it resets the IDENTITY back to the SEED, and the deallocated pages are returned to the system for use in other areas.

Cons: however, it bypasses the transaction log, and the only record of the truncation in the transaction logs is the page deallocation.

Cons: Records removed by the TRUNCATE TABLE statement cannot be restored.

Cons: You cannot specify a WHERE clause in a TRUNCATE TABLE statement-it is all or nothing.

Cons: TRUNCATE TABLE statements cannot be used for tables involved in replication or log shipping, since both depend on the transaction log to keep remote databases consistent.

Cons: TRUNCATE TABLE cannot used be used when a foreign key references the table to be truncated, since TRUNCATE statements do not fire triggers. This could result in inconsistent data because ON DELETE/UPDATE triggers would not fire.

If a TRUNCATE TABLE statement is issued against a table and there is foreign key referencing the table, you must drop the index and recreate it.

http://www.mssqltips.com/sqlservertip/1080/deleting-data-in-sql-server-with-truncate-vs-delete-commands/

Tables / Views - Truncate and Delete

When do you use DELETE?

What are pros / cons?

What is syntax?

DELETE TABLE statements delete rows one at a time

Pro: logging each row in the transaction log, as well as maintaining log sequence number (LSN) information.

Pro: these transactions can be rolled back if necessary

Pro: You can also specify a WHERE clause to narrow down the rows to be deleted.

Con: DELETE consumes more database resources and locks,

Con: When you delete a large number of rows using a DELETE FROM statement, the table may hang on to the empty pages requiring manual release using DBCC SHRINKDATABASE (db_name).

DELETE equivalent to TRUNCATE:

When large tables require that all records be deleted and TRUNCATE TABLE cannot be used, the following statements can be used to achieve the same result as TRUNCATE TABLE:

• DELETE from "table_name"
• DBCC CHECKIDENT("table_name", RESEED, "reseed_value")

[image]

http://www.mssqltips.com/sqlservertip/1080/deleting-data-in-sql-server-with-truncate-vs-delete-commands/

Triggers #01

INSTEAD OF DELETE

INSTEAD OF INSERT

The primary advantage of INSTEAD OF triggers is that they enable views that would not be updatable to support updates. A view based on multiple base tables must use an INSTEAD OF trigger to support inserts, updates, and deletes that reference data in more than one table. Another advantage of INSTEAD OF triggers is that they enable you to code logic that can reject parts of a batch while letting other parts of a batch to succeed.

INSTEAD OF triggers are a special type of trigger that executes alternate code instead of executing the statement that they were fired from.

It is important to realize that with an INSTEAD OF trigger, only the code in the trigger is executed.
A very common use case for INSTEAD OF triggers is to allow views that are based on multiple base tables to be updatable

http://msdn.microsoft.com/en-us/library/ms175521(v=sql.105).aspx

Triggers #02

INSTEAD OF INSERT

INSTEAD OF DELETE

An INSTEAD OF trigger can take actions such as:

• Ignoring parts of a batch.

• Not processing a part of a batch and logging the problem rows.

• Taking an alternative action when an error condition is encountered.

Views - Partioned Views

1. What does a partitioned view allow?

2. What is the role of CHECK constraint?

3. What is role of UNION ALL?

4. In processing a SELECT on the partitioned view, what does the query optimizer use to determine which member table(s) to search?

1. Partitioned views allow the data in a large table to be split into smaller member tables. The data is partitioned between the member tables based on ranges of data values in one of the columns.

2. The data ranges for each member table are defined in a CHECK constraint specified on the partitioning column.

3. A view that uses UNION ALL to combine selects of all the member tables into a single result set is then defined.

4. When SELECT statements referencing the view specify a search condition on the partition column, the query optimizer uses the CHECK constraint definitions to determine which member table contains the rows.

WHERE or HAVING.

When do you use WHERE clause?

When do you use HAVING clause?

1) Are you filtering literal against column?

Use WHERE clause

2) Are you filtering column against column?

Use WHERE clause

3) Are testing column againt aggregate?

Subquery with WHERE clause

SELECT * FROM Sales.LT.Product

WHERE ListPrice > (

SELECT AVE(ListPrice) FROM SalesLT.Product

     (

4) Are you testing an aggregate against literal?

Use HAVING clause

5) Are you testing aggregate against an aggregate

Use HAVING clause

LearnItFirst, Chapter 6

06_04-DecideBetweenWHEREHAVINGOrSimpleSubquery.png

1. Using declarative data integrity.

     Uses CONSTRAINTS with CREATE TABLE or ALTER TABLE.

2. Using procedural data integrity.

    Uses stored procedures or triggers.

70-433_Training_Kit_MS_SQL_Server_2008_Database_Development.pdf

1. Primary Key

2. Unique

3. Foreign Key

4. Check

5. Default

70-433_Training_Kit_MS_SQL_Server_2008_Database_Development.pdf

[image]

6232BD-ENU-TrainerHandbook-Vol2.pdf p 14-8

XML

1. What is <note id="501">?

2. What role is it playing in this XML expression?

<messages>
   <note id="501">
      <to>Tove</to>
     <from>Jani</from>
     <heading>Reminder</heading>
     <body>Don't forget me this weekend!</body>
  </note>
     <note id="502">
     <to>Jani</to>
     <from>Tove</from>
     <heading>Re: Reminder</heading>
     <body>I will not</body>
   </note>
< /messages>

1. It is an element containing an attibute

2. It is metadata about each "note".

XML

1. What is an XML Stream?

2. How do you get SQL to return an XML Stream?

XML Stream is data in XML format (with tags, elements and attributes).

The FOR XML clause instructs SQL Server to return data as an XML stream rather than a rowset.

Morelan, Rick (2011-07-05). SQL Interoperability Joes 2 Pros: A Guide to Integrating SQL Server with XML, C#, and PowerShell (Sql Exam Prep Series 70-433) (Volume 5) (Kindle Locations 650-651).  . Kindle Edition.

XML

1. What is an XML element?

2. What is an XML attribute?

3. Are there rules about when to use attributes vs. elements?

4. What is the best practice?

5. Why?

6. When is it a best practice to use attributes?

7. In the SQL enviornment, what XML object (attribute or element) typically corresponds to which SQL object?

1a. It is a row in an XML stream. (This is not the same as a row in a SQL table.)

Morelan, Rick (2011-07-05). SQL Interoperability Joes 2 Pros: A Guide to Integrating SQL Server with XML, C#, and PowerShell (Sql Exam Prep Series 70-433) (Volume 5) (Kindle Location 664).  . Kindle Edition.

1b. An XML element is everything from (including) the element's start tag to (including) the element's end tag.

An element can contain:

• other elements
• text
• attributes
• or a mix of all of the above...

2. Attributes provide additional information about an element. Attribute values must always be quoted. Either single or double quotes can be used. For a person's sex, the person element can be written like this:
<person sex="female">

or like this:

<person sex='female'>

or you can use character entities:

<gangster name="George &quot;Shotgun&quot; Ziegler">

3. No.

4. Use Elements instead of Attributes.

5.

Some of the problems with using attributes are:

• attributes cannot contain multiple values (elements can)
• attributes cannot contain tree structures (elements can)
• attributes are not easily expandable (for future changes)

Attributes are difficult to read and maintain. Use elements for data. Use attributes for information that is not relevant to the data.

6. Metadata (data about data) should be stored as attributes, and the data itself should be stored as elements.

Source: 1b thu 6: http://www.w3schools.com/xml/xml_attributes.asp

 7. Column / field

Source: Gary's observation

XML

1. What is the XSINIL option used for?

2. What is the syntax?

XML

1. What is the best FOR XML mode for most solutions?

2. Why is it the best for most solutions?

1. FOR XML PATH mode is the best choice of the different FOR XML modes for most solutions.

2. PATH mode allows for the easy creation of different XML structures by simply interpreting column names specified using an XPath-like expression when generating the XML result.

ms-sql-server-2008-database-development-training-kit-exam-70-433.NODRM.pdf p 272

XML

1. What is the role of tags??

2. What is the name (term) for XML parts?

3. What is the form / syntax for a beginning tag?

4. What is the form / syntax for an ending tag?

5. What is the form / syntax if an element has no nested elements?

6. Can elements be nested?

7. What is the rule of interleaving tags?

8. What is a well-formed XML document?

1. XML uses tags to name parts of an XML document.

2. These parts are called elements.

3. and 4. Every begin tag, such as <Customer>, must have a corresponding end tag, in this case </Customer>.

5. If an element has no nested elements, the notation can be abbreviated to a single tag that denotes the beginning and end of an element, such as <Order … />.

6. Elements can be nested.

7. Tags cannot be interleaved; the end tag of a parent element must be after the end tag of the last nested element.

8. If every begin tag has a corresponding end tag, and if tags are nested properly, the XML document is well-formed.

XML

How do you bulk load XML data?

Example: Loading XML from Files

XML

What are 6 key points regarding FOR XML RAW option?

1. The RAW option can be used with the ROOT or ELEMENTS keywords or both to customize your expected XML stream.
2. The ROOT and ELEMENTS keywords are optional
3. The raw mode likes to store all of its data as attributes.
1. Attributes: data stored inside of a beginning tag.
   
4. FOR XML RAW defaults to making a <row> tag.
5. You can change this to anything you like by using the optional parentheses ( ) after the word RAW.
1. FOR XML RAW – – Results in <row…>
2. FOR XML RAW(‘Emp’) – – Results in <Emp…>
6. FOR XML RAW command by default does not create a root.
1. Root element: also known as the root node; the element which begins and ends a well-formed XML.
2. XML is considered complete (known as well-formed XML) only if it has a root tag which encompasses all other tags.
   
   
   

Morelan, Rick (2011-07-05). SQL Interoperability Joes 2 Pros: A Guide to Integrating SQL Server with XML, C#, and PowerShell (Sql Exam Prep Series 70-433) (Volume 5) (Kindle Locations 654-658).  . Kindle Edition.

XML

What are the 2 forms of XML data?

XML

What are two common reasons why you might want to retrieve data as XML instead of a SQLServer table?

Two common reasons why you might want to retrieve data as XML instead of a SQLServer table could be:

1. Publishing data to a website.
2. Retrieving data to exchange with a trading partner who should not have direct access to your SQL Server.

Morelan, Rick (2011-07-05). SQL Interoperability Joes 2 Pros: A Guide to Integrating SQL Server with XML, C#, and PowerShell (Sql Exam Prep Series 70-433) (Volume 5) (Kindle Locations 652-654).  . Kindle Edition.

XML

What is a SQL Server stream?

XML

What is example syntax for SELECT ... FOR XML PATH?

SELECT
c.CustomerID AS "@Id"
,c.AccountNumber AS "@AccountNumber"
,c.RowGuid AS "comment()"
,CAST('<Test/>' AS XML) AS "node()"
,c.CustomerType AS "AdditionalInfo/@Type"
,c.ModifiedDate AS "AdditionalInfo/text()"
,c.rowguid AS "node()"
FROM Sales.Customer AS c
WHERE c.CustomerID IN (1, 2)
FOR XML PATH('Customer'), ROOT('Customers');
Here is the result:
<Customers>
<Customer Id="1" AccountNumber="AW00000001">
<!--3F5AE95E-B87D-4AED-95B4-C3797AFCB74F-->
<Test />
<AdditionalInfo Type="S">2004-10-13T11:15:07.263</AdditionalInfo>
</Customer>
<Customer Id="2" AccountNumber="AW00000002">
<!--E552F657-A9AF-4A7D-A645-C429D6E02491-->
<Test />
<AdditionalInfo Type="S">2004-10-13T11:15:07.263</AdditionalInfo>
</Customer>
</Customers>
In the XML result, you can see the following:
n The @Id column resulted in the attribute Id in the Customer element.
n The @AccountNumber column resulted in the attribute AccountNumber in the
Customer
element.
n The comment() column resulted in the value of the RowGuid column being returned as
an XML comment.
n The node() column resulted in the XML constant in the query being placed directly into
the XML result without ending up in a subelement.
n The AdditionalInfo/@Type column resulted in the attribute Type in the subelement
AdditionalInfo.
n The AdditionalInfo/text() column resulted in the text of the subelement AdditionalInfo
being set.

ms-sql-server-2008-database-development-training-kit-exam-70-433.NODRM.pdf p 272

XML

What is modification for "FOR XML RAW" that replaces "<row>" tag with other text?

FOR XML RAW defaults to making a <row> tag, you can change this to anything you like by using the optional parentheses ( ) after the word RAW.

FOR XML RAW – – Results in <row…>

FOR XML RAW(‘Emp’) – – Results in <Emp…>

Morelan, Rick (2011-07-05). SQL Interoperability Joes 2 Pros: A Guide to Integrating SQL Server with XML, C#, and PowerShell (Sql Exam Prep Series 70-433) (Volume 5) (Kindle Locations 656-658).  . Kindle Edition.

XML

What is the SELECT statement that provides XML data that shows the root, elements within the root?