Advance Features Of Sqlserver 2005 For Aptech (Accp 20007)
Settings
This is theory test for advance sql server 2005. It is good for you and I.
- 1.This refer to parts of XML documents. These parts need not include the root element.
- A.
XML Views
- B.
XML fragments
- C.
Native XML storage
- 2.The XML data type can be used to define:- 3 choices.
- A.
Rowa
- B.
Columns
- C.
Parameters
- D.
Values
- E.
Variables
- 3.Until an xml data type variables. parameter, or column is not associated with an xml schema it is called:
- A.
Typed XML data
- B.
Untyped XML data
- 4.Which statement of code is type xml data?
- A.
CREATE TABLE student( ID varchar(20) not null, Address xml (CONTENT studentAddress) null, Class xml )
- B.
CREATE TABLE student( ID varchar(20) not null, Address xml null, Class xml not null )
- 5.The XML data type enable storage of xml data in their native form, xml
- A.
True
- B.
False
- 6.XML data type variables or columns can only store complete XML documents.
- A.
True
- B.
False
- 7.Native XML storage makes it easy to map XML data into relational databases
- A.
True
- B.
False
- 8.XML data can provide a highly optimized querying environment by indexing.
- A.
True
- B.
False
- 9.XML views work best when data in database needs to be converted to xml
- A.
True
- B.
False
- 10.XML data storage in relational databases makes data management impossible.
- A.
True
- B.
False
- 11.XML document can be validated against their schemas sincean xml schema defines an xml document's basic structure.
- A.
True
- B.
False
- 12.ALTER XML SCHEMA COLLECTION statement permits developers to modify existing schema elements and attributes.
- A.
True
- B.
False
- 13.This method is used to shred an xml document into relational data.
- A.
Query();
- B.
Value();
- C.
Nodes();
- D.
Modify();
- 14.This method is used to retrieve fragments of an xml document.
- A.
Value()
- B.
Data()
- C.
Query()
- D.
Nodes()
- 15.Insert, append or delete XML document nodes.
- A.
Modify
- B.
Nodes
- C.
Query
- 16.Recreate xml fragments as specified by query.
- A.
Modify
- B.
Exist
- C.
Query
- 17.Transpose XML values to relational database
- A.
Value
- B.
Exist
- C.
Nodes
- D.
Node
- 18.You want to create a table store long distance train travel details. The table stores the journey, passenger and reservation details in the form of untyped XML data. Which one of the following codes will help you achieve this ?
- A.
CREATE TABLE LongDistanceDetails(TicketNumber varchar NOT NULL, JourneyDetails varchar NOT NULL, PassengerDetails varchar NOT NULL, ReservationDetails varchar NOT NULL)
- B.
CREATE TABLE LongDistanceDetails(TicketNumber varchar NOT NULL, JourneyDetails XML, PassengerDetails XML (CONTENT Passenger) NOT NULL, ReservationDetails varchar NOT NULL)
- C.
CRATE TABLE LongDistanceDetails(TicketNumber varchar NOT NULL, JourneyDetails XML, PassengerDetails XML, ReservationDetails XML)
- D.
CREATE TABLE LongDistanceDetails(TicketNumber varchar NOT NULL, JourneyDetails XML (CONTENT Journey) NOT NULL, PassengerDetails XML (CONTENT Passenger) NOT NULL, ReservationDetails XML (CONTENT Reservation) NOT NULL)
- 19.Which of these codes snippets about XML data type methods are true and which of these statements are false ?
- A.
SELECT StudentID FROM Students WHERE @student.exist(‘(/Age)[.=”15”]’)=1
- B.
SELECT @student.value( ‘(/Student/Age)[1]’, ‘int(2)’)
- C.
SELECT StudentID FROM Students WHERE @student.exist(‘(/Age)[.=”15”]’)=0
- D.
SET @student.modify( ‘insert Aaroninto Student[1]’)
- E.
SELECT StudentID FROM Students WHERE @student.exist(‘(/Age’=”15”)
- 20.Software entity that implements a service specification.
- A.
Service Consumer
- B.
Service Locator
- C.
Service Broker
- D.
Services
- E.
Service Provider
- 21.Service provider that can pass on service requests to one or more additional service providers.
- A.
Service Consumer
- B.
Service Locator
- C.
Service Broker
- D.
Services
- E.
Service Provider
- 22.Service provider that acts as a registry and allows for lookup of service provider interfaces and service location
- A.
Service Consumer
- B.
Service Locator
- C.
Service Broker
- D.
Services
- E.
Service Provider
- 23.Function in which consumers communicate through contract
- A.
Service Consumer
- B.
Service Locator
- C.
Service Broker
- D.
Services
- E.
Service Provider
- 24.SOA is an architecture that encourages tightly coupled communication between software services.
- A.
True
- B.
Fasle
- 25.Service-Oriented architecture is essentially a collection of services that communicate with each other
- A.
True
- B.
False
- 26.Service consumer can be a client application, a server application such as a Web server, or any other kind of application.
- A.
True
- B.
False
- 27.Service is a function that directly depends on the state of other service.
- A.
True
- B.
False
- 28.A group sharing similar dialog conversations.
- A.
Endpoints
- B.
Service Broker Security
- C.
Conversation Groups
- D.
Messages
- E.
Dialogs
- 29.A conversation that takes place between two Service Brokers.
- A.
Endpoints
- B.
Service Broker Security
- C.
Conversation Groups
- D.
Messages
- E.
Dialogs
- 30.An entity that is exchanged between Service Brokers
- A.
Dialogs
- B.
Endpoints
- C.
Conversation Groups
- D.
Service Broker Security
- E.
Messages
- 31.Supports communication between two services and connection between two databases along with authentication
- A.
Endpoints
- B.
Dialogs
- C.
Service Broker Security
- D.
Conversation Groups
- E.
Message
- 32.Responsible for the communication in Service Broker
- A.
Conversation Groups
- B.
Endpoints
- C.
Service Broker Security
- D.
Dialogs
- E.
Messages
- 33.The messages are read one after another from the same queue by multiple programs
- A.
True
- B.
False
- 34.Service Broker handles ordering of messages, conversation identification, and unique delivery automatically.
- A.
True
- B.
False
- 35.Service Broker’s integrated design is beneficial for application performance and administration
- A.
True
- B.
Fasle
- 36.Defines the type of message data
- A.
Services
- B.
Message Types
- C.
Service Program
- D.
Queues
- E.
Contracts
- 37.Agreement that specifies who sends the message and what message types are sent.
- A.
Message Types
- B.
Contracts
- C.
Queues
- D.
Services
- E.
Service Type
- 38.Holds messages as long as they do not expire
- A.
Message Type
- B.
Service Program
- C.
Services
- D.
Queues
- E.
Contracts
- 39.Receives the messages from a queue and send the messages to services.
- A.
Message Type
- B.
Service Program
- C.
Queues
- D.
Services
- E.
Contracts
- 40.Handles and processes all the messages when they arrive at the server.
- A.
Message Type
- B.
Service Program
- C.
Services
- D.
Queues
- E.
Contracts
- 41.All Service Broker applications use conversations for their reliable communication
- A.
True
- B.
Fasle
- 42.All Service Broker applications communicate using dialogs for reliable communication
- A.
True
- B.
False
- 43.Service Broker delivers messages in an Exactly-Once-In-Order fashion
- A.
True
- B.
False
- 44.The first message dialog sent by the Service Broker has the message_sequence_number of one
- A.
True
- B.
False
- 45.When conversations are going on between various SQL Server instances, Service Broker use the routes to direct the messages.
- A.
True
- B.
False
- 46.Any type of message body is acceptable.
- A.
NONE
- B.
Well Formed xml
- C.
Well Formed Xml Schema
- D.
Empty
- 47.Only an empty message body is acceptable
- A.
NONE
- B.
Well Formed XML
- C.
Well Form XML Schema
- D.
EMPTY
- 48.Only a message body with valid XML is acceptable.
- A.
NONE
- B.
Well Formed XML
- C.
Well Formed XML Schema
- D.
EMPTY
- 49.Arrange the following steps in sequence to create the service in Service Broker 1. Create a contract 2. Create an application that uses functions 3. Create a service 4. Create the message type 5. Create a queue.
- A.
1-3-2-4-5
- B.
4-1-2-5-3
- C.
4-1-5-3-2
- D.
2-3-4-1-5
- 50.Which of the following is the correct code to send the message from service consumer to service provider
- A.
DECLARE @dialog_handle1 FROM SERVICE [//Adventure-Service.com/Provider] TO SERVICE ‘//Adventure-Service.com/Consumer’ DECLARE @msgString NVARCHAR(MAX) SET @msgString = N’Welcome’; SEND ON CONVERSATION @dialog_handle1 MESSAGE TYPE [//Adventure-Service.com/Adventure/AdventureSales] (@msgString)
- B.
DECLARE @dialog_handle1 BEGIN DIALOG CONVERSATION @dialog_handle1 FROM SERVICE [//Adventure-Service.com/Provider] TO SERVICE ‘//Adventure-Service.com/Consumer’ ON CONTRACT [//Adventure-Service.com/Adventure/Contract] DECLARE @msgString NVARCHAR(MAX) SET @msgString = N’Welcome’; SEND ON CONVERSATION @dialog_handle1
- C.
DECLARE @dialog_handle1 BEGIN DIALOG CONVERSATION @dialog_handle1 FROM SERVICE [//Adventure-Service.com/Provider] TO SERVICE ‘//Adventure-Service.com/Consumer’ ON CONTRACT [//Adventure-Service.com/Adventure/Contract] DECLARE @msgString NVARCHAR(MAX) SET @msgString = N’Welcome’; SEND ON CONVERSATION @dialog_handle1 MESSAGE TYPE [//Adventure-Service.com/Adventure/AdventureSales] (@msgString)
- D.
DECLARE @dialog_handle1 BEGIN DIALOG CONVERSATION @dialog_handle1 ON CONTRACT [//Adventure-Service.com/Adventure/Contract] DECLARE @msgString NVARCHAR(MAX) SET @msgString = N’Welcome’; SEND ON CONVERSATION @dialog_handle1 MESSAGE TYPE [//Adventure-Service.com/Adventure/AdventureSales]
- 51.Web Services are services available on the Web
- A.
True
- B.
False
- 52.Web Services are platform independent, but not language independent
- A.
True
- B.
False
- 53.Web Services fail to encourage reusability
- A.
True
- B.
False
- 54.The existing components and libraries can be easily exposed as Web Services
- A.
True
- B.
False
- 55.SOAP is a protocol for the exchange of information in decentralized and distributed environments
- A.
True
- B.
False
- 56.Exchanging SOAP messages leads to time consuming way for communicating with Web Services.
- A.
True
- B.
False
- 57.SOAP encourages distributed computing
- A.
True
- B.
False
- 58.SOAP defines application semantics such as a programming model or implementation-specific details
- A.
True
- B.
False
- 59.SOAP message consists of an XML document with a header and a body
- A.
True
- B.
Fasle
- 60.IIS needs to be installed to listen for the requests in SQL Server 2005
- A.
True
- B.
Fasle
- 61.WSDL document helps to create a proxy class to call a Web Service
- A.
True
- B.
Fasle
- 62.Web Service methods provide access to database objects
- A.
True
- B.
False
- 63.Document that helps in creating a proxy class, understand methods and parameters passing.
- A.
Http.sys
- B.
WSDL Document Generation
- C.
Access to database objects.
- D.
Native HTTP requirements.
- E.
Receiving requests.
- 64.Requires installation of Windows 2003 to support native HTTP
- A.
Receiving requests.
- B.
Access to database objects.
- C.
WSDL Document Generation
- D.
Http.sys
- E.
Native HTTP requirements
- 65.Receives the HTTP request from the client
- A.
Native HTTP requirements
- B.
Http.sys
- C.
WSDL Document Generation
- D.
Receiving requests
- E.
Access to database objects.
- 66.The HTTP request is forwarded to SQL Server.
- A.
Access to database objects.
- B.
Receiving requests
- C.
Http.sys
- D.
Native HTTP requirements
- E.
WSDL Document Generation
- 67.Which one of the following code snippets is a valid code snippet for creating HTTP endpoints?
- A.
CREATE ENDPOINT sqlEndpoint STATE = STARTED AS HTTP( PATH = ‘/sql’, AUTHENTICATION = (INTEGRATED), PORTS = (CLEAR) )
- B.
CREATE sqlEndpoint STATE = STARTED AS HTTP( PATH = ‘/sql’, AUTHENTICATION = (INTEGRATED), PORTS = (CLEAR) )
- C.
GRANT ENDPOINT sqlEndpoint STATE = STARTED AS HTTP( PATH = ‘/sql’, AUTHENTICATION = (INTEGRATED), PORTS = (CLEAR) )
- D.
CREATE POINT sqlEndpoint STATE = STARTED AS HTTP( PATH = ‘/sql’, AUTHENTICATION = (INTEGRATED), PORTS = (CLEAR) )
- 68.CREATE LOGIN statement is used to add a user to the SQL Server 2005 security system.
- A.
True
- B.
False
- 69.GRANT ACCESS statement is used to grant permission to a user for stored procedures
- A.
True
- B.
False
- 70.GRANT CONNECT ON ENDPOINT statement grants permission to the user to connect to the HTTP endpoint.
- A.
True
- B.
False
- 71.Endpoints can be altered by any of the members.
- A.
True
- B.
False
- 72.ALTER ENDPOINT statement permits adding, removing and altering a WEBMETHOD from the endpoints
- A.
True
- B.
False
- 73.Which statement is used to grant permission for stored procedure or user-define function?
- A.
GRANT ACCESS TO
- B.
GRANT EXECUTE TO
- C.
GRANT EXECUTE ON TO
- D.
GRANT ACCESS ON TO
- 74.Entity interested in being notified when an event occurs.
- A.
Notification
- B.
Subscription
- C.
Subscriber
- D.
Event
- 75.Channel of communication.
- A.
Subscriber
- B.
Subscription
- C.
Event
- D.
Notification
- 76.Action that occurred affecting the specified data.
- A.
Notification
- B.
Event
- C.
Subscriber
- D.
Subscription
- 77.An act by which subscriber describes when and what to be notified on.
- A.
Subscription
- B.
Subscriber
- C.
Notification
- D.
Event
- 78.Which of the following tasks in Notification Services are correct and which are incorrect?- 3 choices
- A.
Events format notifications.
- B.
Distributor sends notification to subscribers
- C.
Event data stored in application database.
- D.
Generator runs every five seconds.
- E.
Subscriber data stored in database
- 79.Which of the following tasks in Notification Services are correct and which are incorrect?-2 choices
- A.
Subscription data stored in database.
- B.
Generator collects event data.
- C.
Generator matches subscriptions and events.
- 80.Which of the following actions for creating and configuring a Notification Services application are correct and which are incorrect?- 3 choices
- A.
Create a stored procedure.
- B.
Create custom event providers.
- C.
Create program definition file.
- D.
Create subscription management application.
- E.
Create instance configuration file.
- 81.Which of the following actions for creating and configuring a Notification Services application are correct and which are incorrect?
- A.
Deploy notification services application.
- B.
Deploy the target device.
- C.
Unregister the target device.
- 82.You want to define a SQL Server event provider named SQLStockWatch in the application definition file. Which one of the following codes will help you to achieve this?
- A.
P0DT00H00M60S PT4M EventsQuery SELECT StockID, StockPrice, StockName FROM Stock EventClassName StockEvents
- B.
SQLStockWatch SQLProvider SERVER304 P0DT00H00M60S PT4M EventClassName StockEvents
- C.
SQLStockWatch SQLProvider SERVER304 P0DT00H00M60S PT4M EventsQuery SELECT StockID, StockPrice, StockName FROM Stock EventClassName StockEvents
- D.
SQLStockWatch P0DT00H00M60S PT4M EventsQuery SELECT StockID, StockPrice, StockName FROM Stock
- 83.Specifies a device that can receive notifications.
- A.
NSInstance
- B.
SubscriberDevice
- C.
SubscriberLocale
- D.
Subscription
- E.
Subscriber
- 84.Specifies a Notification Services instance.
- A.
SubscriberDevice
- B.
NSInstance
- C.
SubscriberLocale
- D.
Subscription
- E.
Subscriber
- 85.Enables to manipulate subscriber information in Notification Services instance
- A.
SubscriberDevice
- B.
NSInstance
- C.
SubscriberLocale
- D.
Subscription
- E.
Subscriber
- 86.Subscriptions define what notifications the subscriber wishes to retrieve
- A.
True
- B.
False
- 87.Subscriber can be a person identified by subscriber identification
- A.
True
- B.
False
- 88.Subscription management interface can be an ASP.NET Web application
- A.
True
- B.
False
- 89.Subscription management interface does not require subscriptions, and subscriber devices to create notification services
- A.
True
- B.
False
- 90.Subscriber device is not the endpoint for notification delivery.
- A.
True
- B.
False
- 91.You want to configure an e-mail delivery channel for the instance of Notification Services. Which one of the following codes will help you to achieve this?
- A.
FineChannel C:\File.xml
- B.
EmailChannel SMTP
- C.
EmailChannel http://aptech-education.com
- D.
File or EmailChannel C:\File1.xml
- 92.Notification Services delivers notifications to the delivery endpoint by the protocol specified by the delivery channel
- A.
True
- B.
False
- 93.The generator writes notification to internal notification table.
- A.
True
- B.
False
- 94.The generator does the notification formatting and distribution in Notification Services.
- A.
True
- B.
False
- 95.Delivery Channels of Notification are:- 3 choices
- A.
POP3 channel
- B.
SMTP channel
- C.
Email channel
- D.
HTTP channel
- E.
File chnnel
- 96.A class consisting of properties and methods is created for defining a UDT
- A.
True
- B.
False
- 97.A CLR UDE can have only a single element.
- A.
True
- B.
False
- 98.Complex UDT enhances the performance of the system.
- A.
True
- B.
False
- 99.A UDT is used to define a column in a table.
- A.
True
- B.
False
- 100.You want to register the Email assembly in the AdventureWorks database. The assembly cannot access the external resources and it should present in D:\Project\EMail\Email\bin\Debug directory. Which one of the following codes will help you to achieve this?
- A.
CREATE ASSEMBLY Email FROM ‘EMail\EMail\bin\Debug\Email.dll’ ITH PERMISSION_SET = SAFE
- B.
USE AdventureWorks CREATE ASSEMBLY Email FROM ‘D:\Project\EMail\EMail\bin\Debug\EMail.dll’ WITH PERMISSION_SET = SAFE
- C.
CREATE ASSEMBLY EMail FROM ‘D:\Project\EMail\EMail\bin\Debug\EMail.dll’
- D.
USE AdventureWorks CREATE ASSEMBLY EMail FROM ‘D:\Project\EMail\EMail\bin\Debug\EMail.dll’ WITH PERMISSION_SET = UNSAFE
- 101.Satisfied by MAX and MIN function
- A.
MaxByteSize
- B.
IsNullIfEmpty
- C.
Format
- D.
IsInvariantToNulls
- E.
IsInvariantToDuplicates
- 102.Specified in bytes the maximum size allowed for storing the state of the aggregate.
- A.
IsInvariantToDuplicates
- B.
IsInvariantToNulls
- C.
Format
- D.
IsNullIfEmpty
- E.
MaxByteSize
- 103.Specifies the serialization format.
- A.
MaxByteSize
- B.
IsNullIfEmpty
- C.
Format
- D.
IsInvariantToNull
- E.
IsInvariantToDuplicates
- 104.Indicates if no value is returned then aggregate returns null.
- A.
IsInvariantToDuplicates
- B.
IsInvariantToNull
- C.
IsNullIfEmpty
- D.
MaxByteSize
- E.
Format
- 105.Satisfied by COUNT function.
- A.
Format
- B.
IsInvariantToNull
- C.
IsInvariantToDuplicates
- D.
IsNullIfEmpty
- E.
MaxByteSize
- 106.The SqlUserDefinedAggregate attribute is used before using a CLR aggregate function.
- A.
True
- B.
False
- 107.The Init () method is used to initialize the aggregate computation.
- A.
True
- B.
False
- 108.The Merge () method is used to merge an aggregate object with another object.
- A.
True
- B.
False
- 109.The Accumulate () method is used to process the aggregate value into discrete value.
- A.
True
- B.
False
- 110.The Terminate () method terminates the aggregation process
- A.
True
- B.
Fasle
- 111.You want to create a scalar-valued function named CurrencyConverter, which converts US dollar to Chinese Yuan. Which one of following codes will help you to achieve this?
- A.
Public partial class UserDefinedFunctions { [SqlUserFunction] public static SqlDecimal CurrencyConverter() { return new SqlDecimal(((Int16)value * 7.10); } };
- B.
Public partial class UserDefinedFunctions { [SqlUserFunction] (DataAccess=DataAccessKind.None)] public static SqlDecimal CurrencyConverter() { return new SqlDecimal(((Int16)value * 7.10); } };
- C.
Public partial class UserDefinedFunctions { public static SqlDecimal CurrencyConverter(SqlInt16 value) { Return new SqlDecimal(((Int16)value * 7.10); } };
- D.
Public partial class UserDefinedFunctions { [SqlFunction (DataAccess=DataAccessKind.None)] public static SqlDecimal CurrencyConverter(SqlInt16 value) { Return new SqlDecimal(((Int16)value * 7.10); } };
- 112.CLR is a runtime environment that manages the execution of .NET code
- A.
True
- B.
Fasle
- 113.CLR is also known as Virtual Runtime System.
- A.
True
- B.
False
- 114.CRL compiles the code into Microsoft Intermediate Language.
- A.
True
- B.
False
- 115.CLR forms the core of Microsoft .NET programming initiative.
- A.
True
- B.
False
- 116.CLR executes non-managed code.
- A.
True
- B.
False
- 117.Database engine hosting the CLR environment execute managed codes.
- A.
Streamlined Model
- B.
UDT & Aggregate Functions
- C.
Improved Safety & Security
- D.
Enhanced Programming Model
- E.
Improved Performance
- 118.Microsoft .NET Framework compilation and execution model improves the performance over T-SQL.
- A.
Improved Performance
- B.
Enhanced Programming Model
- C.
Improved Safety & Security
- D.
UDT & Aggregate Functions
- E.
Streamlined Model
- 119.Developers develop and debug database objects and scripts from within the .NET development environment.
- A.
Improved Performance
- B.
Enhanced Programming Model
- C.
Improved Safety & Security
- D.
UDT & Aggregate Functions
- E.
Streamlined Model
- 120.Microsoft .NET Framework languages provide different programming constructs and capabilities.
- A.
Streamlined Model
- B.
UDT & Aggregate Functions
- C.
Enhanced Programming Model
- D.
Improved Safety & Security
- E.
Improved Performance
- 121.Two new database objects that expands the storage and querying capabilities of SQL Server
- A.
Improved Performance
- B.
Enhanced Programming Model
- C.
UDT & Aggregate Functions
- D.
Improved Safety & Security
- E.
Streamlined Model
- 122.Executes the command received as an argument and returns the result set.
- A.
Send ExecuteAndSend
- B.
SendResultsEnd
- C.
SendResultsStart
- D.
ExecuteAndSend
- E.
SendResultsRow
- 123.Returns a string message or a single row result set to the client.
- A.
SendResultsRow
- B.
ExecuteAndSend
- C.
SendResultsStart
- D.
SendResultsEnd
- E.
Send
- 124.Sets the SqlPipe object to its original state.
- A.
Send
- B.
SendResultsEnd
- C.
ExecuteAndSend
- D.
SendResultsRow
- E.
SendResultsStart
- 125.Sends a single row of data
- A.
SendResultsStart
- B.
SendResultsRow
- C.
ExecuteAndSend
- D.
SendResultsEnd
- E.
Send
- 126.Marks the beginning of the result set from a stored procedure.
- A.
Send
- B.
SendResultsEnd
- C.
ExecuteAndSend
- D.
SendResultsRow
- E.
SendResultsStart
Back to top