Practice Questions: Building Tables

Here are some practice questions you might see on the MCDBA exams.

Michael D. Reilly

September 30, 1999

2 Min Read
ITPro Today logo in a gray background | ITPro Today

WebSQL subscribers who don't want to wait until next month's issue can find the answers to these questions at the link to this article at http://www.sqlmag.com.

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? (Select one answer.)

  1. SQL Server will not create a table of more than 8060 bytes, so the table creation fails.

  2. SQL Server creates the table as input and warns you not to insert more than 8060 bytes.

  3. SQL Server creates the table but adjusts the length of the last column(s) to fit the 8060 bytes.

  4. SQL Server creates the table and moves the columns that would cause the row to exceed 8060 bytes to the next page.

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? (Select one answer.)

  1. SELECT MAX (orderid) from ORDERS

  2. You have to insert the order, then find the orderid value within one transaction so that nobody else adds an order and changes the orderid number before you are finished using it:

    BEGIN TRANINSERT Orders...... SELECT MAX (orderid) from OrdersINSERT [Order Details]...COMMIT TRAN
  3. SELECT @@identity

  4. Use an Identity column in the Order Details table. Use an explicit transaction to make sure that the two Identity columns stay synchronized:

    BEGIN TRANINSERT Orders...... INSERT [Order Details]COMMIT TRAN

Q3: When setting up merge replication, SQL Server will (select all correct answers)

  1. always add a column to provide a unique identifier to be used in the merge.

  2. add a column with the Identity property if it doesn't find one.

  3. add a column with the RowGUID property if it doesn't find one.

  4. do nothing—you have to make sure all the identifier columns are in place before setting up merge replication.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like