PartOne

Question 2

^{<span><span>What does SELECT INTO do?</span></span>}

Answer 2

Run the query. Create the target table. Insert the result of the query into the target table. All data type will be copied except constraints, triggers and indexes.

Question 3

^{Which is not allowed to specify the target column in an insert stmt?}

• default constraint
• not null
• identity

Answer 3

Identity

Question 4

What is CROSS JOIN?

Answer 4

It performs cartesian product of the 2 input table.m rows in T1. n rows in T2. Cross join will produce m x n rows.

Question 5

Left join / Right join

Answer 5

ON and WHERE works differently.The preserved side will return all rows regardless.The ON side just matches rows from the unpreserved side to the preserved side

Question 6

INLINE table function

Answer 6

The function returns a table.Different from VIEW, it accepts parameters.

_{^{<span><strong>CREATE FUNCTION</strong> dbo.authors<br></br> ( @au_lname VARCHAR(40) )<br></br> <strong>RETURNS TABLE</strong><br></br> <strong>RETURN</strong><br></br> <strong>SELECT</strong> au_id, au_fname, au_lname, city, state<br></br> <strong>FROM </strong>authors<br></br> <strong>WHERE </strong>au_lname = @au_lname</span>}}

Question 7

What is CROSS APPLY?

Answer 7

Similar to inner join.Does not return left side if table expression returns a empty set.

Question 8

When a primary key or unique constraint is defined, SQL Server create ______ automatically.

Answer 8

Unique index

Question 9

What’s the precedence?EXCEPT UNION INTERSECT

Answer 9

INTERSECT precedes UNION & EXCEPTUNION & EXCEPT are equal

Question 10

Window functions

Answer 10

OVER ([PARTITION BY clause][ORDER BY clause][ROW or RANGE clause] )

Question 11

Derived table

Answer 11

SELECT FROM(Select from where…) as d1 WHERE…

Question 12

Describe INDEXED VIEW

Answer 12

An unique clustered index is created on the view and the actual result of the view query are stored on disk

Question 13

VIEW WITH SCHEMABINDING

Answer 13

Guarantees the underlying table structures cannot be altered w/o dropping the view.

Question 14

VIEW WITH ENCRYPTION

Answer 14

Make it difficult to discover the SELECT text of the view

Question 15

VIEW WITH VIEW_METADATA

Answer 15

Return metadata instead of the base table

Question 16

VIEW WITH CHECK OPTION

Answer 16

Prevent any updates thru the view that would cause some rows to get values no longer satisfying a where clause of the view

Question 17

What are the VIEW restrictions?

Answer 17

• No order by
• Does not accept parameters
• Cannot create tables, permanent or temp
• Can only reference permanent table. No temp tables.

Question 18

What is SYNONYM

Answer 18

Synonyms are names stored in a database that can be used as substitutes for other object names

Question 19

How to move table from one schema to another

Answer 19

ALTER SCHEMA TRANSFER

Question 20

ALTER TABLE cannot…

Answer 20

1. Change column name
2. Add or remove identity

Question 21

^{DENSE_RANK ( ) OVER ( [] < order_by_clause > )}

Answer 21

^{If two or more rows tie for a rank in the same partition, each tied rows receives the same rank. No gaps and always have consecutive ranks.}

Question 22

^{The WHERE clause returns cases where the predicate evaluates to _ and _ the rest. No false or NULL.}

Answer 22

• ^TRUE
• ^Discards

Question 23

SARG - search argument

Answer 23

Try not to include a field inside a function in the WHERE clause.

Bad - WHERE Year(myDate) = 2008.

Good - WHERE myDate >= ‘01-01-2008’ AND myDate < ‘01-01-2009’ instead.

Question 24

How to show tiebreakers with TOP

Answer 24

SELECT TOP(n) WITH TIES

Question 25

OFFSET-FETCH

Answer 25

OFFSET - skip m rows FETCH NEXT n ROWS ONLY

Question 26

T-SQL logical order

Answer 26

FROM,

WHERE,

GROUP BY,

HAVING,

SELECT,

ORDER BY

Question 27

OPENDATASOURCE

Answer 27

Provides ad hoc connection info as part of a 4-part object name, without using a linked server name.

Question 28

T-SQL logical order

Answer 28

FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY

Question 29

OFFSET-FETCH

Answer 29

• OFFSET - skip m rows
• FETCH NEXT n ROWS ONLY

Question 30

Window functions

Answer 30

OVER ( [PARTITION BY clause] [ORDER BY clause] [ROW or RANGE clause] )

Question 31

How to move table from one schema to another?

Answer 31

ALTER SCHEMA TRANSFER

Question 32

What is SYNONYM

Answer 32

Synonyms are names stored in a database that can be used as substitutes for other object names

Question 33

TRUNCATE resets _____ while DELETE don’t.

Answer 33

IDENTITY to the initial seed

Question 34

What is distributed transaction

Answer 34

Transaction that span more than one server, by using a linked server.

Question 35

_{__ Sales.Myorders AS TGT}

_{<span>__Sales.Orders AS SRC ON TGT.orderid = SRC.orderid</span>}

_{__( TGT.Custid <> SRC.Custid OR TGT.Empid <> SRC.Empid OR TGT.orderdate <> SRC.orderDate )}

_{__SET TGT.Custid = SRC.Custid, TGT.Empid = SRC.Empid, TGT.orderdate = SRC.orderdate}

_{__(SRC.orderid, SRC.Custid, SRC.empid, SRC.orderdate)}

_{__ THEN DELETE;}

Answer 35

1. _{MERGE INTO}
2. _USING
3. _{WHEN MATCHED AND}
4. _{THEN UPDATE}
5. _{WHEN NOT MATCHED THEN INSERT VALUES}
6. _{WHEN NOT MATCHED BY SOURCE}

Question 36

How to get next value of a Sequence object

Answer 36

NEXT VALUE FOR

Question 37

What is the three-valued logic?

Answer 37

• True
• False
• Unknown

Question 38

col1 LIKE ‘!_%’ ESCAPE ‘!’

Answer 38

Look for value starting with an underscore. ‘!’ is the designated escape character.

Question 39

RANK

Answer 39

The RANK function does not always return consecutive integers.