MCTS 70 – 433 working with query fundamentals

Working with Query fundamentals

Querying data

SELECT DISTINCT: selects distinct values that exist in a specific table


FROM Production.Product

The WHERE clause can be used to furhter refine the results.

SET ANSI_NULLS [ON|OFF] – defines whether non-conforming ISO operators will return NULL values i.e. IS or = and IS NOT or <>; more detailed article here

Parsing order of operation: NOT, AND then OR, you can control this order by using parentheses.

NOT: When this keyword is used then the query optimser cannot use indexes.

AND: Generally results in a smaller result set which improves performance.

OR: Will only use indexes if columns referenced by the OR clause are indexed.

LIKE clause wildcards: NOTE: leading wildcard symbols will stop the query optimser using indexes.

% – the percent sign matches any number of characters in a string.

_ – the underscore will match exactly one character.

[] – the square brackets will match specific or a range of values defined in the square brackets e.g. [abcd], [a,b,c,d] and [a-d] are all the same.

^ – the caret can be used inside the square brackets to denote NOT whatever is defined in the square brackets.


THe BETWEEN keyword will return inclusive data i.e. BETWEEN 1 AND 5 would return all values 1, 1.1 > 4.9, 5 whereas BETWEEN X AND Z would return all strings that start with X, Y and a single Z.

ALIASES: You can define aliases for tables, etc. if you’re using a large number of objects and your T-SQL becomes messy e.g.

PP.FirstName As 'Forename' 
Person.Person As PP
PP.FirstName + ' ' + PP.LastName As 'Name' 
Person.Person As PP

ORDER BY: by default the ORDER BY clause is ascending, if you ORDER BY a aliases be sure to surround the aliases with square brackets.

Joining related tables


Joins are three fundamental parts: the tables, the join type and the join condition.

e.g. FROM tableone T1 JOIN tabletwo T2 ON =

The tables are: tableone and tabletwo, the join type is: JOIN (by default an INNER JOIN) and the jion condition is: =

NOTE: joins can be specified within the FROM or WHERE clause; it is best practice to only specify joins within the FROM clause.

inner join


An outer join will return all records from one or both tables that have common data. A LEFT OUTER JOIN or RIGHT OUTER JOIN defines which side of the JOIN operator the outer table is; the outer table is the one which will return all records which are common to the table being joined. A FULL OUTER JOIN will return all records from all tables being joined.

If there are foreign key constraints enforcing relational integrity then a FULL OUTER JOIN will return the same results as a LEFT OUTER JOIN; if the table with the foreign key is to the left of the JOIN keyword.

outer join


Cross joins join every record from one table to every row in another table; the result set can become large e.g.

SELECT p.BusinessEntityID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
ORDER BY p.BusinessEntityID;

will return 170 rows. 17 X 10.

A few things to note about joins:

Four or fives tables per join operation

The first result set of the first join is used on subsequent joins thus result set can differ depending on the table order

self-joins are created when you define a join on the same table but reference the table using different aliases

Implementing aggregate functions

NULL values are ignored for all aggregate functions.

GROUP BY; if you use the group by clause then all other expression except aggregate functions must be defined.

WITH ROLLUP; with rollup is used with the GROUP BY clause to provide subtotal information for multiple columns.

WITH CUBE; provides summary information for multiple columns e.g. average price of one product on a particular order and from within each subcategory.

GROUPING; this function will define whether the data is summary or detail information. A 1 denotes a summary and 0 denotes detail.

GROUPING SETS; grouping sets have been added to SQL server 2008 to provide an easier way of grouping queries rather than using UNION and GROUP BY operations.

HAVING; this function allows you to filter aggregate functions whereas the WHERE clause doesn’t.

Combining datasets

UNION; the union operator allows you to combine the rsult sets of multiple select statements into one result set. The syntax is: UNION or UNION ALL; for the union operation to succeed both queries must return the same numberof columns and each datatype in the corresponding column must be compatible or converted using the CONVERT function. The first select statement must define the column names as aliases or the column names will  be blank.

UNION ALL will include duplicate by default they’re excluded.

EXCEPT; this operator returns all rows from the table to the left that do not match rows in the table on the right. The same conditions that apply to UNION also apply to EXCEPT.

INTERSECT; this operator returns all rows from the table to the left that match the rows in the table on the right.

APPLY; this operator uses the results of a table or view query and the result of a table-valued function. The APPLY operator has two options; CROSS APPLY or OUTER APPLY. CROSS APPLY returns only rows from the left output that produces data from the table-valued function (this function returns values as a table). OUTER APPLY returns all rwos from the left similar to an OUTER JOIN statement.

Applying built-in scalar functions

Scalar functions can be deterministic or non-deterministic i.e. the result is built into the function or the result is determined by the functions parameters.

Function groups:

  • Datetime functions – examples here
    • The sys[datetime|datetimeoffset|utcdatetime] functions return the datetime2 data type which is precise to 100 nanoseconds; whereas the get[date|utcdate] functions return the datetime data type which is less precise.
    • Other datetime functions such as datepart, datename return numeric and string data types respectively.
    • Dateadd and datediff are used for modification and comparison respectively.
  • System functions – examples here
    • Convert and cast functions are used to convert one data type to another e.g. SELECT CONVERT(varchar(50), getdate(),#), ‘Some description’ where # is the country code.
  • String functions – examples here
    • Left and right return # no. of characters from the left or right of a string.
    • Upper and lower returns uppercase or lowercase for all characters in the string.
    • Substring returns characters from within a string e.g. substring(string,1,3) – this would return the first three characters of the string
    • Replace, replaces one string with another string.
    • Len returns the length of the string.
    • Datalength returns the no. of bytes required to represent the string.
    • PatIndex returns the first occurence of a define pattern within a string.
    • CharIndex returns the string position of a string within a string.

Modifying data by using INSERT, UPDATE and DELETE statements

Introduced to SQL server 2005 was the output clause; the output clause returns information about each row that was affected by a INSERT, UPDATE and DELETE statements.

Introduced to SQL server 2008 is the merge clause; the merge clause enhances the ability to perform INSERT, UPDATE and DELETE statements on a table based on of a query to a joined table.

It is important to note that the table schema will affect what values may or may not be defined, modified or removed in one of the above statements e.g. you cannot insert a value for an identity column without specifying SET IDENTITY_INSERT ON first. The table schema consists of default constraints, identity properties, and NULL settings will affect INSERT statements.

VALUES ('','','')

Some columns may have default values; so specifying DEFAULT as a value will use the column default e.g. GETDATE() would insert the date into the column. NULL is also a valid value if NULL are allowed.

If you’re inserting data into specific columns then you need to specify the columns within brackets before the VALUES keyword and after the INSERT keyword.

UPDATE [Table | Table Joins]
SET [Column] = [Value]
WHERE [Column] = [Test]

WHERE [Column] = [Test]

The TRUNCATE TABLE statement will remove all data from the table but only logs the deallocation of pages; it also resets the IDENTITY SEED.

Enhanced DML functionality with OUTPUT and MERGE

The OUTPUT clause allows you to output specific information into another table or database when a INSERT, UPDATE or DELETE statement is executed.

OUTPUT [inserted|deleted].Column INTO [Table|variable] WHERE [Column] = [Test]

The MERGE clause allows you to perform an INSERT, UPDATE or DELETE on a specific table depending on the query result of another table.

WHEN NOT MATCHED BY TARGET THEN – defines that a row be inserted into the target table when a matched row is not found and any other conditions are not valid; only one of these can exist per MERGE clause.

WHEN NOT MATCHED BY SOURCE THEN – defines that a row be UPDATED or DELETED from the target table; two of these can exist per MERGE clause, one UPDATE and one DELETE.

WHEN MATCHED – defines the action to be taken on the target table where a match exists based on MERGE_CONDITIONS.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.