Chapter 3 - Lesson 1

Question 1

What is a searched argument?

Answer 1

A column used in the where clause without manipulation is caled a searched argument

Question 2

Why is it important to use searched arguments?

Answer 2

Because sql server otherwise will not use indexes to search for that argument and perform instead a table scan, which is slow.

Question 3

Is this a searched argument?
WHERE COALESCE(e.region,' ' ) <> 'wa'

Answer 3

no, Because Coalesce is performed on each row, sql server will not use a possible index on that column.

Question 4

How can i make region in COALESCE(e.region,’ ‘ ) <> ‘wa’ a searched argument and still get the same rows back?

Answer 4

WHERE e.region <> ‘wa’ OR e.region IS NULL

Question 5

Is shipped date a sa?
WHERE COALESCE(shippeddate, '19000101') = COALESCE(@DT, '19000101');

Answer 5

Becasuse we are manipulating it with COALESCE, preventing sql server from using indexes.

Question 6

How can i solve the problem that COALESCE(shippeddate, ‘19000101’) = COALESCE(@DT, ‘19000101’) is not a searched argument?

Answer 6

WHERE shippeddate = @DT OR (shippeddate IS NULL AND @DT IS NULL)

Question 7

What is the precedence when it comes to logical operators AND , OR and NOT and the parentheses(pr. parentesees)?

Answer 7

• • 1. ()
• • 2.NOT
• • 3.AND
• • 4.OR

Question 8

Why will the follwoing predicate influence the performance of a query, suppose u know that lastname is nvarchar?
– WHERE lastname = ‘Davis’;

Answer 8

Now the column lastname is not a searched argument anymore because of the (manupulation) implicit convertion to varchar, so no index will be used.

Question 9

What rule do you have to follow when it comes to wild cards to not influence the performance?

Answer 9

never use the wildcard at the beginning of the charachters, because your column will not be a searched argument anymore

Question 10

Which one is a sa?

cl1 like ‘%ABC%’ is not good, like ‘ABC%’

Answer 10

the second

Question 11

What is the form of a filter predicate that can rely on index ordering called?

Answer 11

A search argument, or SARG, for short.

Question 12

What is the standard way(style) to compare dates?

Answer 12

‘yyyymmdd’

Question 13

Why is the use of between when comparing dates not recommended?

Answer 13

‘20071231’ and ‘20071231 23:59:59.999’ will also.

get records of 1 jan 2008

Question 14

Hoe bepaalt sql server wat de stijl is van een gegeven literal bijvoorbeeld ‘25-08-1983’?

Answer 14

Dit is afhankelijk van je taal instelling!

Question 15

Welke form van date is niet afhankelijk van je taal instelling?

Answer 15

‘yyyymmdd’ oftewel ‘19832508’.

Question 16

Voor welke date datatype is de stijl ‘1983-08-25’ niet taalonafhankelijk?

Answer 16

Datetime and smalldatetime

Question 17

What does the term three-valued logic refer to in T-SQL?

Answer 17

true, false and unknown

Question 18

Which of the following literals are language-dependent for the DATETIME data type?

• -A. ‘2012-02-12’
• -B. ‘02/12/2012’
• -C. ‘12/02/2012’
• -D. ‘20120212’

Answer 18

a,b,c

Question 19

When is a predicate a searched argument, 3 situations?

Answer 19

when the columns are not manipulated

• • patern in LIKE predicate starts with a known prefix like: ‘A%’
• • Patern in LIKE predicate starts doesn’t start with a wild card.

Question 20

Which of the following predicates are search arguments? (Choose all that apply.)

• -A. DAY(orderdate) = 1
• -B. companyname LIKE ‘A%’
• -C. companyname LIKE ‘%A%’
• -D. companyname LIKE ‘%A’
• -E. orderdate > = ‘20120212’ AND orderdate

Answer 20

– B,E