You are responsible for managing a SQL Server 2005 database. This database is updated with data that is exported from another site. On a monthly basis, data is loaded into the database from a flat file by using the bcp utility. This operation should be minimally logged. However, users report that they receive error messages indicating that the transaction log is full. You investigate and discover that the errors occur when the data is being imported. You need to prevent this problem from occurring. What should you do?

TestKing.com uses a SQL Server 2005 database. Customer data from your trading partners is imported into the Customers table every night. You need to ensure that the customer record is updated if it already exists in the Customers table. If the record does not exist, the data needs to be inserted into the Customers table. What should you do?

You are planning to import customer data from the Customer table and the Country table in your SQL Server 2000 database into a SQL Server 2005 database. You want to ensure that during the import process, each value in the CountryCode column of the Customer table has a corresponding record in the CountryCode column in the Country table of the SQL Server 2005 database. You define a foreign key between these two tables. Thus, referential integrity will guarantee that the import routine fails if there are any records where a CountryCode value does not exist in the Country table but does exist in the Customer table. You need to ensure that the import process does not fail if records are encountered that do not exist in the Country table. What should you do?

You work with a SQL Server 2005 database that provides banking information for customers. You want customers to see banking reports that combine data that is retrieved from the database with real-time investment information that comes from a Web service provided by a third party. The investment information must be current when the reports are executed. You need to create the appropriate objects that support the reports. What should you do?

TestKing.com has a SQL Server 2005 computer. You have been assigned the task of retrieving information about a user who is currently logged in. You need to create a function that returns scalar information about the activity time for a particular user. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

Application developers in TestKing.com create an assembly that contains a CLR function.
This CLR function reads data from a spreadsheet, performs some calculations, and returns the data to a SQL Server 2005 computer. You need to register the assembly with SQL Server 2005 by using the CREATE ASSEMBLY statement and the least privileged security permission set. Which permission set should you use?

TestKing.com has two SQL Server 2005 computers named SQL1 and SQL2. A user in TestKing.com named Eric writes many ad hoc queries against the company databases.
Eric has access to the Customer database on SQL1. He does not have access to the Sales database on SQL2. You need to ensure that Eric can write queries that join information from both servers. What should you do first?

TestKing.com has multiple servers in a distributed environment. You work with two SQL Server 2005 computers named SQL1 and SQL2. Each server uses SQL Server Authentication and they use different logins. You need to write a distributed query that joins the data on SQL1 with the data on SQL2. What should you do?

TestKing.com uses SQL Server 2005. You are implementing a series of views that are used in ad hoc queries. The views are used to enforce TestKing.coms security policy of abstracting data. Some of these views perform slowly. You create indexes on those views to increase performance, while still maintaining the company’s security policy. One of the views returns the current date as one of the columns. The view returns the current date by using the GETDATE() function. This view does not allow you to create an index. You need to create an index on the view. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

You are responsible for maintaining a SQL Server 2005 database. Business analysts in the company routinely use a view named v_CustomerSales to join the Customers and Sales tables in the database. They use the view to aggregate total sales by customer by month.
You need to increase the performance of the view. What should you do?