70-433 Exam Questions

You have a table named dbo.Customers. The table was created by using the following Transact-SQL statement: CREATE TABLE dbo.Customers ( CustomerID int IDENTITY(1,1) PRIMARY KEY CLU...

You work as a network database administrator at ABC.com. ABC.com has a database server named ABC-DB01. You need to configure a CLR assembly on ABC-DB01 that access an external data...

You have a table named AccountsReceivable. The table has no indexes. There are 75,000 rows in the table. You have a partition function named FG_AccountData. The AccountsReceivable...

You have a SQL Server 2008 database named Contoso with a table named Invoice. The primary key of the table is InvoiceId, and it is populated by using the identity property. The Inv...

You are developing a new database. The database contains two tables named SalesOrderDetail and Product. You need to ensure that all products referenced in the SalesOrderDetail tabl...

You are creating a table that stores the GPS location of customers. You need to ensure that the table allows you to identify customers within a specified sales boundary and to calc...

You plan to add a new column named SmallKey to the Sales.Product table that will be used in a unique constraint. You are required to ensure that the following information is applie...

You have multiple tables that represent properties of the same kind of entities. The property values are comprised of text, geometry, varchar(max), and user-defined types specified...

You are the administrator of a SQL Server 2008 instance. There is a database named DB1 in the instance. On DB1, quite few logged operations are performed. Now according to the requ...

You are the administrator of a SQL Server 2008 cluster. According to the company requirement, the failover response of the cluster has to be tested. The company assigns this task t...

You are the administrator of a SQL Server 2008 instance. You have to perform the backup of the default trace according to the requirement of your failure recovery plan. The default...

You are the administrator of a SQL Server 2008 instance with a database named DB1. The latest differential backup is performed at 15:30, the full back up was performed at 13:30, an...

You work as a network database administrator at ABC.com. ABC.com has a database server named ABC-DB01 which hosts the Inventory database. ABC.com recently created a table named Ite...

You work as a network database administrator at ABC.com. ABC.com has a database server named ABC-DB01 that hosts the Inventory database. ABC.com has a Sales division which makes ch...

You work as a network database administrator at ABC.com. ABC.com has a database server named ABC-DB01 that hosts the Retail database. The Retail database has a table named Customer...

You work as a network database administrator at ABC.com. ABC.com has a poorly designed database with similar data stored in numerous tables. The tables use columns with different d...

You are creating a function that references a table. You need to prevent the table from being dropped. Which option should you use when you create the function?

You are developing a database using Microsoft SQL Server 2008. The database contains the tables shown in the exhibit. You are required to prevent parts from being deleted if they b...

You have a third-party application that inserts data directly into a table. You add two new columns to the table. These columns cannot accept NULL values and cannot use default con...

Your database contains two tables named Order and OrderDetails that store order information. They relate to each other using the OrderID column in each table. Your business require...

You need to ensure that tables are not dropped from your database. What should you do?

You are responsible for a SQL Server database. You require the tables to be added or altered only on the first day of the month. You need to ensure that if the tables are attempted...

You have a single CLR assembly in your database. The assembly only references blessed assemblies from the Microsoft .NET Framework and does not access external resources. You need...

Your company stores vendor and price information in a database. All items in the database have a list price. You need to increase the list price for all products of only the vendor...

You have two tables named Customer and SalesOrder. You need to identify all customers that have not yet made any purchases and those that have only made orders with an OrderTotal l...

You have two tables named Customer and SalesOrder. In the Customer table you have 1000 customers, of which 900 customers have orders in the SalesOrder table. You execute the follow...

You have the following rows in the Customer Table: You write the following query to return all customers that do not have NULL or 'Dormant' for their status: SELECT * FROM Customer...

You have a table named Employee. You document your company's organizational hierarchy by inserting the EmployeeID of each employee's manager in the ReportsTo column. You need to wr...

You need to determine the result of executing this code segment. DECLARE @RangeStart INT = 0; DECLARE @RangeEnd INT = 10000; DECLARE @RangeStep INT = 1; WITH NumberRange(ItemValue)...

You need to implement a common table expression (CTE). Which code segment should you use?

You are tasked to analyze blocking behavior of the following query: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE WITH Customers AS ( SELECT * FROM Customer ), SalesTotal AS ( SELEC...

You create and populate a table named SiteNavigation by using the following statements: CREATE TABLE SiteNavigation ( SiteNavigationId INT PRIMARY KEY, Linktext VARCHAR(10), LinkUr...

You have two views named Sales.SalesSummaryOverall and Sales.CustomerAndSalesSummary. They are defined as follows: CREATE VIEW Sales.SalesSummaryOverall AS SELECT CustomerId, SUM(S...

You need to write a query that allows you to rank total sales for each salesperson into four groups, where the top 25 percent of results are in group 1, the next 25 percent are in...

You need to write a query that uses a ranking function that returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each parti...

You have a table named ProductCounts that contains 1000 products as well as the number of units that have been sold for each product. You need to write a query that displays the to...

You need to identify, within a given clause, if the month of February will contain 29 days for a specified year. Which object should you use?

You have a database server that has four quad-core processors. This database server executes complex queries that are used to generate reports. You need to force a query to use onl...

You notice that for a particular set of parameter values the following query sometimes executes quickly and other times executes slowly. You also notice that 90 percent of the rows...

You have been tasked to write a query to select one million rows. You need to optimize the query to return the first 50 rows as quickly as possible. What query hint should you use?

You have the following query: SELECT EmployeeID, ManagerID, LoginID FROM dbo.Employees WHERE ManagerID = 1500 ORDER BY ManagerID; You have been tasked to force the query to use the...

You are working with a SQL Server 2008 instance that is configured to use the Latin1_General_CS_AS collation. You create a database by using the following statements. CREATE DATABA...

You have a table named Person that contains a nvarchar column named Surname. The Person table currently has a clustered index on PersonID. The Surname column contains Russian and J...

You have an application that is used by international clients. All clients connect by using Windows Authentication. You need to ensure that system and user-defined error messages a...

Your server collation is SQL_Latin1_General_CP1_CI_AS. You have a database named Contoso that has a collation setting of SQL_Scandinavian_Cp850_CI_AS. You create and populate a tem...

You have a SQL Server 2008 database. You have not installed a MAPI client. You need to send e-mail from a stored procedure. Which system stored procedure should you use?

You are using Database Mail to deliver email notification and are notified that an employee has not been receiving emails. You need to determine if any email notifications sent by...

You have a computed column that is implemented with a user-defined function. The user-defined function returns a formatted account number. The column must be indexed to provide ade...

You have a table named Books that has columns named BookTitle and Description. There is a full-text index on these columns. You need to return rows from the table in which the word...

You need to configure Full-Text Search to ignore specific words. Which Full-Text Search component should you use?