Answers to October Practice Questions: Building Tables

Q1: The maximum row length in SQL Server 7.0 is 8060 bytes. The sum of all of your columns, allowing for the maximum length of the variable-length columns, is 8200 bytes. What happens when you try to create this table?

The correct answer is (b) SQL Server creates the table as input and warns you not to insert more than 8060 bytes. Answer (a) isn't an option, (c) would let SQL Server change your design, which wouldn't be good, and (d) is wrong because rows never span pages.

Q2: You used the Identity property on the orderid column to assign an order number to each incoming order. You need to use that order number in an Order Details table. How would you find out what order number was assigned?

The correct answer is (c) SELECT @@identity. Option (a), SELECT MAX (orderid) from ORDERS, would run but might give you the orderid of an order someone else placed after you placed your order. Answer (b) works only if you lock the tables; otherwise nothing would stop another transaction from inserting a row into Orders and changing the MAX (orderid) before you read it. (d) is a creative solution, but again, another order could get the next orderid after yours, and then jump ahead to grab the orderid in Order Details that should have been yours. Unless you lock the tables, as with (b), the BEGIN...COMMIT just lets you roll back the transaction.

Q3: When setting up merge replication, SQL Server will:

The correct answer is (c) Add a column with the RowGUID property if it doesn't find one. Setting up merge replication causes SQL Server to look for a globally unique identifier, and if it doesn't find one, it adds one.