You have a user named John. He has SELECT access to the Sales schema. You need to eliminate
John’s SELECT access rights from the Sales.SalesOrder table without affecting his other permissions.
Which TransactSQL statement should you use?

You need to create a column that allows you to create a unique constraint.
Which two column definitions should you choose? (Each correct answer presents a complete
solution.
Choose two.)

You manage a SQL Server 2008 database that is located at your company’s corporate headquarters.
The database contains a table named dbo.Sales. You need to create different views of the dbo.Sales
table that will be used by each region to insert, update, and delete rows. Each regional office must
only be able to insert, update, and delete rows for their respective region.
Which view should you create for Region1?

You administer a SQL Server 2008 database that contains a table name dbo.Sales, which contains the
following table definition:
CREATE TABLE [dbo].[Sales](
[SalesID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[SalesPersonID] [int] NULL,
[CommentDate] [date] NULL);
This table contains millions of orders. You run the following query to determine when sales persons
comment in the dbo.Sales table:
SELECT SalesID,CustomerID,SalesPersonID,CommentDate
FROM dbo.Sales
WHERE CommentDate IS NOT NULL
AND SalesPersonID IS NOT NULL;
You discover that this query runs slow. After examining the data, you find only 1% of rows have
comment
dates and the SalesPersonID is null on 10% of the rows. You need to create an index to optimize the
query. The index must conserve disk space while optimizing your query.
Which index should you create?

Your database is 5GB and contains a table named SalesHistory. Sales information is frequently
inserted and updated.
You discover that excessive page splitting is occurring.
You need to reduce the occurrence of page splitting in the SalesHistory table.
Which code segment should you use?.

You have a table named dbo.Customers. The table was created by using the following TransactSQL
statement:
CREATE TABLE dbo.Customers
(
CustomerID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
AccountNumber nvarchar(25) NOT NULL,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
AddressLine1 nvarchar(255) NOT NULL,
AddressLine2 nvarchar(255) NOT NULL,
City nvarchar(50) NOT NULL,
StateProvince nvarchar(50) NOT NULL,
Country nvarchar(50) NOT NULL,
PostalCode nvarchar(50) NOT NULL,
CreateDate datetime NOT NULL DEFAULT(GETDATE()),
ModifiedDate datetime NOT NULL DEFAULT(GETDATE())
)
You create a stored procedure that includes the AccountNumber, Country, and StateProvince
columns

from the dbo.Customers table. The stored procedure accepts a parameter to filter the output on the
AccountNumber column.
You need to optimize the performance of the stored procedure. You must not change the existing
structure of the table.
Which TransactSQL
statement should you use?

You have a table named Customer.
You need to ensure that customer data in the table meets the following requirements:
credit limit must be zero unless customer identification has been verified.
credit limit must be less than 10,000.
Which constraint should you use?

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_AccountDat
a. The AccountsReceivable table is defined in
the following TransactSQL
statement:
CREATE TABLE AccountsReceivable (
column_a INT NOT NULL,

column_b VARCHAR(20) NULL)
ON [PRIMARY];
You need to move the AccountsReceivable table from the PRIMARY file group to FG_AccountData.
Which TransactSQL statement should you use?

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 Invoice table is related
to the InvoiceLineItem table. You remove all constraints from the Invoice table during a data load to
increase load speed. You notice that while the constraints were removed, a row with InvoiceId = 10
was removed from the database. You need to reinsert
the row into the Invoice table with the same InvoiceId value.
Which TransactSQL statement should you use?

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 table have a
corresponding record in the Product table.
Which method should you use?