Term
| Name the 5 types of constraints. |
|
Definition
- PRIMARY KEY
- UNIQUE
- FOREIGN KEY
- CHECK
- DEFAULTS
|
|
|
Term
| What are the size limitations of a primary key and unique constraints? |
|
Definition
| Cannot contain more than 16 columns or 900 bytes of data |
|
|
Term
| What is the syntax of an ALTER TABLE for row data compression |
|
Definition
ALTER TABLE
REBUILD WITH (DATA_COMPRESSION = ROW); |
|
|
Term
| What is the syntax for creating an identity column? |
|
Definition
| <column_name> IDENTITY(<seed>,<increment> ) NOT NULL |
|
|
Term
| Name the 6 date and time data types. |
|
Definition
date,
datetime,
datetime2,
datetimeoffset,
smalldatetime,
time |
|
|
Term
| Name the approximate numeric types. |
|
Definition
| float(24), float(53), real |
|
|
Term
| Name the 7 exact numeric data types |
|
Definition
tinyint,
smallint,
int,
bigint,
decimal(precision, scale),
numeric(precision, scale),
money |
|
|
Term
| What are the 5 transaction isolation levels? |
|
Definition
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SNAPSHOT
- SERIALIZABLE
|
|
|
Term
| What is the DBCC LOG statement and its syntax? |
|
Definition
A nondocumented feature that returns information about the information contained in the current transaction log.
DBCC LOG (<databasename>, <output_identifier>).
<output_identifier> is between 0-4 |
|
|
Term
| Name the 7 SQL Server locking modes. |
|
Definition
Bulk Update (BU),
Exclusive (X),
Intent (IS, IX, SIX),
Key-range
Schema (Sch-M, Sch-S),
Shared (S),
Update (U),
|
|
|
Term
| The index created for a primary key is a non-clustered index by default. True or False. |
|
Definition
| False. If nothing else is specified, the index created for a PK is a clustered index. |
|
|
Term
| What are the 3 rules to implement a foreign key constraint? |
|
Definition
-
The columns being referenced must have exactly the same data type as the local columns.
-
The columns being referenced must have a unique index created on them. Typically a pk or unique constraint.
-
Because the fk must reference a unique index, the fk columns have the same size limitations as that of a pk and unique constraints.
|
|
|
Term
| Name the different join operators. |
|
Definition
INNER JOIN
LEFT OUTERR JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
CROSS JOIN |
|
|
Term
How many sub-total rows are produced by the following clause:
GROUP BY ROLLUP( production.productcategory.productcategoryid
,product.productsubcategoryid )
|
|
Definition
|
|
Term
| What are the left input and right input of the APPLY operator? |
|
Definition
| Left input are the results from a query to a table or view. Right input are the results of a table-valued function. |
|
|
Term
| With respect to formatting a date, would you use a CAST or CONVERT? |
|
Definition
| CONVERT (e.g. CONVERT(varchar(30), GETDATE(),1) |
|
|
Term
Is there anything wrong with the following syntax?
DELETE TOP(5)
FROM table_a
|
|
Definition
| No, the TOP can be used with a SELECT, UPDATE, INSERT and DELETE. |
|
|
Term
| What are the 2 tables that the OUTPUT clause give you the ability to access? |
|
Definition
|
|
Term
| Name one functionality that a ROLLBACK will not reset. |
|
Definition
| seed values for identity columns. |
|
|
Term
What information will the following statement provide?
SELECT @@trancount |
|
Definition
| It provides the number of open transactions in the current session. |
|
|
Term
| What are the four actions to choose from when a foreign key detects a referential integrity violation? |
|
Definition
NO ACTION
SET NULL
SET DEFAULT
CASCADE |
|
|
Term
| What is the issue related to NULL values and check constraints? |
|
Definition
| Check constraints reject values that evaluate to False rather than accepting values that evaluate to True. If you have a check constraint like Price > 10.0, you can still insert a NULL (unless you have a NOT NULL constraint or add a IS NOT NULL to the check constraint) because a NULL comparison returns a NULL - it is neither True nor False. |
|
|
Term
SELECT is_not_trusted FROM sys.foreign_keys WHERE name = 'FKOrdersCustomers';
is_not_trusted contains a 1 meaning that the constraint is not to be trusted. How would you change it? |
|
Definition
ALTER TABLE tablename
WITH CHECK
CHECK CONSTRAINT FKOrdersCustomers; |
|
|
Term
Expalin how the ON DELETE CASCADE in the following example will work.
CREATE TABLE test.customers
( customerid INT PRIMARY KEY,
customercityid INT NOT NULL
REFERENCES test.cities(cityid)
ON DELETE CASCADE) |
|
Definition
| When a row is deleted from test.cities, any rows on test.customers with corresponding cityid (customercityid) will also be deleted. |
|
|
Term
| Which is more precise, CURRENT_TIMESTAMP or SYSDATETIME? |
|
Definition
|
|
Term
Which two of the following functions produce the same results?
a. SYSDATETIME
b. CURRENT_TIMESTAMP
c. GETDATE
d. GETUTCDATE
e. SYSDATETIMEOFFSET |
|
Definition
| b & c (CURRENT_TIMESTAMP & GETDATE) |
|
|
Term
| True or False: The INTO keyword is optional in all INSERT statements. |
|
Definition
|
|
Term
What is the result of the following statement?
SELECT CHARINDEX ( 'TEST',
'Das ist ein Test'
COLLATE Latin1_General_CS_AS) |
|
Definition
|
|
Term
| What is the main difference between CHARINDEX and PATINDEX? |
|
Definition
| PatIndex can use wildcard characters (%, _, [], [^]). |
|
|
Term
select len(N'hello'), datalength(N'hello')
What does each function return? |
|
Definition
|
|
