Question 2) Microsoft SQL Server 2005

Posted on
Like what you see? Share it.Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone

SubObjective: Import and Export Data from a File

Single Answer Multiple Choice

You are the database administrator for Globaltek Corp., a consulting firm. The company has two branch offices that run reports on production data. Each branch office has a single SQL 7.0 Server database server. The branch offices use the BULK INSERT statement to import the first 2500 rows from the production table of a legacy database system into a table in a database on the SQL 7.0 Server. The import contains invalid data. The database administrator runs a T-SQL statement to fix the invalid data before the production reports are run. During the import process, triggers are fired and the data is sorted by the Product_Name column in alphabetical order.

You would now like to import the production data from the branch offices into a database on an instance of SQL Server 2005 at the central office. You will use the same BULK INSERT statement that was used at the branch offices on the SQL Server 2005 instance at the central office. The production reports will be run from the central office.
What can you not control by using the different arguments available in the BULK INSERT statement?

A. The firing of triggers
B. The importation of invalid data from the legacy system
C. The first 2500 rows imported into the database
D. The data to be sorted by the Product_Name column in alphabetical order

Answer:
B. The importation of invalid data from the legacy system

Tutorial:
The option stating that you cannot control the importation of invalid data from the legacy system is correct. When you perform a bulk insert by using the BULKINSERT statement, you can specify arguments to control the behavior of the import process and the activities that will or will not be performed by the import process. The previous version of SQL Server allowed you to import invalid data. This data could be fixed later with T-SQL statements. However, in SQL Server 2005, there are stricter data checks and data validation of the data that is read from the import file. These new data checks and data validations that have been implemented in SQL Server 2005 can cause existing import scripts to fail. Therefore, you cannot control whether invalid data, that will be fixed later, can be imported with a BULK INSERT statement.

You can control the firing of triggers in an import process performed by using the BULKINSERT statement. The use of the FIRE_TRIGGERS argument in the BULK INSERT statement specifies that the triggers should be fired. Therefore, not including the argument will not fire the triggers.

You can control the import of first 2500 rows into the database while performing an import by using the BULKINSERT statement. The use of the FIRSTROW and LASTROW arguments can determine the number of rows that should be imported into the database. The FIRSTROW argument specifies the number of the first row to be loaded into the database. The LASTROW argument specifies the number of the last row to be loaded.

You can sort the data by the Product_Name column in alphabetical order while performing an import by using the BULKINSERT statement. The use of the ORDER argument specifies whether the rows in the data file should be sorted and the order in which the rows should be sorted.

Reference:
MSDN, Search “Enterprise Servers and Development,” “SQL Server,” “SQL Server 2005 Documentation,” “SQL Server 2005 Books Online,” “SQL Server Language Reference,” “Transact-SQL Reference,” “BULK INSERT (Transact-SQL).”

These questions are derived from the Self Test Software Practice Test for Microsoft exam 70-431-TS: Microsoft SQL Server 2005 Implementation and Maintenance

Like what you see? Share it.Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone
cmadmin

ABOUT THE AUTHOR

Posted in Archive|

Comment: