The five questions
1. A schema review finds: CREATE TABLE contact (contact_id INT PRIMARY KEY, phone INT) . Loading the UK numbers 01632 960111, +44 1632 960111 and 020 7946 0102 either fails outright or stores a value that no longer matches the source. Which data type decision is correct?
- A. A variable-length character type, because leading zeros and '+' are part of the recorded value
- B. BIGINT, because the failures come from the 32-bit range of INT rather than from the format
- C. DECIMAL(15,0), because it holds the full digit count without a fractional part
- D. INT with the punctuation reapplied by a display mask in the application
Correct answer: A.
A phone number is an identifier made of characters, not a quantity. Leading zeros carry meaning, '+' and spaces are part of the recorded form, and no query will ever add or average two numbers. A variable-length character column stores exactly what was supplied and lets a CHECK constraint police the format.
Why the others are wrong. B: Widening the integer still rejects every non-digit character and still discards the leading zero, so the stored value continues to differ from the source. C: A numeric type of any width cannot hold '+' or a space, and it reduces 01632 to 1632, which is the defect being reported. D: A mask can reinsert punctuation but cannot recover a leading zero the column never stored, and it moves an integrity rule out of the database.
2. A capacity plan is being prepared for a new transactional database. Which two pieces of information most directly determine the storage to provision? (Choose two.)
- A. The number of rows expected per day and the forecast growth rate
- B. The index fill factor the DBA intends to set
- C. The retention period before rows are archived or deleted
- D. The server collation chosen at installation
- E. The number of stored procedures the application will deploy
Correct answer: A.
Volume comes from arrival rate multiplied by how long rows are kept. Growth rate tells you how the first figure moves over the life of the system, and retention tells you when rows stop accumulating. Everything else refines the estimate rather than establishing it.
Why the others are wrong. B: Fill factor changes how densely pages are packed and adjusts an estimate at the margin; it does not establish how much data arrives. D: Collation governs comparison and sorting behaviour and has no effect on how many rows arrive or how long they are kept. E: Procedures are code objects of negligible size, so their count says nothing about data volume.
3. An accounts schema declares line_amount FLOAT. Month-end reconciliation reports 148,302.9999999998 against a control figure of 148,303.00, and the size of the discrepancy moves as rows are added. Which change addresses the cause?
- A. Keep FLOAT and wrap every report total in ROUND(total, 2)
- B. Change the column to DOUBLE PRECISION, which carries more significant bits
- C. Redeclare the column as DECIMAL(12,2) so amounts are stored exactly in base ten
- D. Store the value as VARCHAR(20) so the typed characters are preserved exactly
Correct answer: C.
Binary floating point cannot represent most two-place decimal amounts exactly, so each stored value carries a tiny error and a large SUM accumulates them. A fixed-point decimal type stores the amount exactly in base ten, so the total is exact regardless of row count.
Why the others are wrong. A: Rounding one total hides one symptom while the stored values stay inexact, so per-row comparisons, equality tests and every other report still disagree. B: More bits push the error further down the expansion but the type is still binary floating point, so a value such as 0.10 remains unrepresentable. D: Text preserves the characters but forfeits arithmetic, ordering and range checks, and every SUM then needs a cast that reintroduces the same type choice.
4. A new order-capture system is specified as: 40,000 orders per day | average row 1.4 KB | seven-year retention | index overhead estimated at 45 per cent of table size . Ignoring compression, roughly how much space should be provisioned for the orders table and its indexes?
- A. About 198 GB
- B. About 136 GB
- C. About 283 GB
- D. About 28 GB
Correct answer: A.
40,000 x 365 x 7 gives 102,200,000 rows. At 1.4 KB each that is 143,080,000 KB, which is about 136 GB. Adding the stated 45 per cent index allowance gives 136 x 1.45, close to 198 GB.
Why the others are wrong. B: That is the table on its own; the 45 per cent index allowance the specification states has not been added. C: That figure comes from ten years of retention, and the requirement gives seven. D: That is a single year of orders with the index allowance applied, so the retention requirement has been ignored.
5. A SQL Server instance shows: CPU 88 per cent sustained | signal waits 34 per cent of total wait time | top wait SOS_SCHEDULER_YIELD | data file read latency 4 ms | page life expectancy 9,400 s. What does this metric set MOST strongly indicate?
- A. Processor pressure, with workers queuing for a scheduler rather than for I/O or memory
- B. Storage saturation, since sustained high CPU is normally a symptom of slow disk
- C. Memory pressure, since the buffer pool cannot hold the working set
- D. Blocking, since sessions waiting on locks drive processor time upward
Correct answer: A.
Signal wait is the interval between a resource becoming available and the worker actually getting on a processor, so 34 per cent of wait time spent queuing for a scheduler is the classic marker of genuine CPU pressure. SOS_SCHEDULER_YIELD reinforces it, while 4 ms reads and a page life expectancy of 9,400 s rule out disk and memory.
Why the others are wrong. B: Read service time is 4 ms and no I/O wait appears at the top of the list, so the storage path is comfortable at this load. C: A page life expectancy of 9,400 s means pages survive in cache for over two hours, which is the opposite of a buffer pool under strain. D: A session waiting on a lock consumes no processor time and would show as an LCK_M wait, not as scheduler yields.
6. A user reports that the payroll application fails. The instance error log records, one second apart: 'Login succeeded for user CORP\jhalliday' | 'The SELECT permission was denied on the object Salary, database Payroll'. Which statement BEST describes what happened?
- A. Authentication succeeded and authorisation failed, so the account is missing an object permission
- B. Authentication failed, so the account has to be re-created in the directory
- C. The account is locked out in the directory and the lockout surfaces as a permission error
- D. The password has expired and the application cannot present the replacement
Correct answer: A.
The first line shows the instance accepted the identity presented, so authentication completed. The second is a permission check on a named object failing afterwards. Authentication answers who you are; authorisation answers what you may do, and only the second one failed.
Why the others are wrong. B: The instance recorded the login as successful, so the identity was established before anything was denied. C: A lockout stops the login itself, so no session would exist to reach an object permission check. D: An expired password prevents the connection from being established at all and never produces an object-level denial.
7. A table is defined with country_code CHAR(2) and delivery_note CHAR(500) . delivery_note averages 40 characters and the table holds 6 million rows. What is the MOST significant consequence of the delivery_note definition?
- A. Values longer than 500 characters are silently truncated rather than rejected
- B. Every row stores 500 characters, so fewer rows fit per page and scans read more I/O
- C. The column cannot be indexed, because fixed-length types above 255 characters are not indexable
- D. Comparisons against the column become case sensitive because of the padding
Correct answer: B.
CHAR is fixed length, so every value is padded to the declared width. At 500 characters against a 40-character average, roughly 92 per cent of the column's storage is padding, which reduces rows per page and multiplies the I/O of any scan. country_code is a sound use of CHAR because the value really is always two characters.
Why the others are wrong. A: An over-length value raises an error on a standard configuration; the waste here comes from the values that do fit, not from the ones that do not. C: No such limit exists; the column is indexable, and index entries would simply inherit the same padding waste. D: Case sensitivity is a property of the collation and is unrelated to whether the type is fixed or variable length.
8. A telemetry platform is specified as: 2,500 devices | one reading per device every 30 seconds | 220 bytes per row | 60-day retention | take 1 GB as 1,024 MB . Roughly how much raw table space is needed?
- A. About 95 GB
- B. About 44 GB
- C. About 89 GB
- D. About 1.5 GB
Correct answer: C.
Each device writes 2,880 rows a day, so 2,500 devices produce 7,200,000 rows a day and 432,000,000 rows over 60 days. At 220 bytes that is 95,040,000,000 bytes, and dividing by 1,024 three times gives about 88.5 GB.
Why the others are wrong. A: That is the same byte total converted using 1,000 at each step rather than the 1,024 the specification states. B: That is 30 days of readings, and the retention requirement is 60 days. D: That is a single day of readings, so the retention period has been left out of the calculation entirely.
