Correct Answer : Microsoft
Explanation : SQL Server is defined as a relational database management system (RDBMS) developed by Microsoft. T-SQL means Transact-SQL, a propriety Language by Microsoft. Microsoft and Sybase released version 1.0 in 1989. Various Editions of SQL Server are Enterprise, Standard, Web, Developer, and Express.
Correct Answer : 1989
Explanation : The history of Microsoft SQL Server begins with the first Microsoft SQL Server database product - SQL Server v1. 0, a 16-bit Relational Database for the OS/2 operating system, released in 1989.
Correct Answer : All of the above
Explanation : First version of SQL Server was designed for the OS/2 platform and was developed jointly by Microsoft and Sybase along with Ashton-Tate.
Correct Answer : 9
Explanation : Support for XML is integrated into all the components from SQL Server 2005 RTM 9.
Correct Answer : Denali
Explanation : Codenames for SQL Server 2005, 2008, 2008 R2, 2012, 2014 are Yukon, Katmai, Kilimanjaro, Denali and Hekaton respectively.
Correct Answer : Dynamic Management Views
Explanation : SQL Server 2005 introduced DMVs (Dynamic Management Views), which are specialized views and functions that return server state information. FILESTREAM, Powerpivot and in-memory capability were introduced in 2008,2008 R2 and 2014 respectively.
Correct Answer : 2012
Explanation : LocalDB was introduced in SQL Server 2012 CTP3. It’s basically a new version of SQL Express, with same functionality, dedicated to developers.
Correct Answer : SQL Server Express
Explanation : SQL Server Express Edition is a scaled down, free edition of SQL Server, which includes the core database engine.
Correct Answer : SQL Server 7.0
Explanation : SQL Server 7.0 provides a graphical user interface to interactively build a query and view the result for data source types.
Correct Answer : SQL Server 2014
Explanation : SQL Server 2014 provides a new in-memory capability for tables that can fit entirely in memory (also known as Hekaton).
Correct Answer : Front end tool
Explanation : Management Studio is a front-end client tool used to manage SQL Server and develop databases.
Correct Answer : Object Explorer
Explanation : Object Explorer is used for administering and developing SQL Server database objects.
Correct Answer : T-SQL scripts
Explanation : Template Explorer is used to create and manage T-SQL code templates.
Correct Answer : Ctrl + \
Explanation : Error List (Ctrl+\, Ctrl+E) lists multiple errors.
Correct Answer : Reset
Explanation : To reset Management Studio to its default configuration (Object Explorer ➪ Tabbed Documents ➪Property Window) use the Window ➪ Reset Window Layout menu command
Correct Answer : Registered Servers
Explanation : Using Registered Servers, connection information can be maintained for connections to the Database Engine, Analysis Services, Reporting Services, SQL Server Mobile Edition Databases, and Integration Services.
Correct Answer : Table designer
Explanation : Table Designer is used to create and modify tables (rows and columns).
Correct Answer : Query pane
Explanation : Query Designer does not provide Query pane for querying database schema.
Correct Answer : image
Explanation : In SQL Server, based on their storage characteristics, some data types are designated as large value data types and large object data types.
Correct Answer : 6
Explanation : SQL Server offers six categories of data types for your use:-exact numeric, Unicode character strings, approximate numeric, Binary strings, Date and time and Character strings.
Explanation : Exact numeric data types store numeric values where you wish to specify the precision of the variable. They may include integer or decimal numbers.
Correct Answer : sql_variant
Explanation : sql_variant is data type that stores values of various SQL Server-supported data types.
Correct Answer : Add TIMESTAMP column to the table
Explanation : The correct answer is Add a DATETIME column to the table and write a trigger that sets its value.
Correct Answer : boolean
Explanation : SQL Server doesn’t have a Boolean data type, at least not by that name. To store True/False, Yes/No, and On/Off values, use the bit data type. It accepts only three values: 0, 1, and NULL.
Correct Answer : Smallmoney
Explanation : Monetary data types are data types that represent monetary or currency values such as smallmoney and money.
Correct Answer : timestamp
Explanation : uniqueidentifier, real and smallmoney data types have storage size of 16,4 and 4 bytes respectively.
Correct Answer : GETDATE
Explanation : GETDATE function is used to obtain the current system date and time. Although GETDATE doesn’t have any input parameters, you still need to include the parentheses in your code because that’s how SQL Server typically identifies functions.
Correct Answer : CEIL
Explanation : SQL Server has CEILING function to get the smallest integer greater than the specified expression.
Correct Answer : Symmetric decryption with Automatic key handling
Explanation : DecryptByKeyAutoCert function decrypts by using a symmetric key that is automatically decrypted with a certificate.
Correct Answer : nondeterministic
Explanation : Text and image functions are nondeterministic. This means they do not always return the same results every time they are called, even with the same set of input values.
Correct Answer : TRY_CASE
Explanation : Conversion functions support data type casting and converting.
Correct Answer : DENSE_RANK
Explanation : DENSE_RANK function makes the rank of a row is one plus the number of distinct ranks that come before the row in question.
Correct Answer : 4
Explanation : SQL Server provides many built-in functions and also lets you create user-defined functions:- Rowset, Aggregate, Ranking and Scalar Function.
Correct Answer : Outer join
Explanation : OUTER JOIN is the only join which shows the unmatched rows.
Explanation : Outer join returns the row having matching as well as non matching values.
Correct Answer : Combines the output from multiple queries and must include the same number of columns
Explanation : A single UNION can combine only 2 sql query at a time.
Correct Answer : Uses the result of an inner query to determine the processing of an outer query
Explanation : A ‘correlated subquery’ is a term used for specific types of queries in SQL in computer databases. It is a subquery (a query nested inside another query) that uses values from the outer query in its WHERE clause.
Correct Answer : Cartesian join
Explanation : Cartesian Join is simply the joining of one or more table which returns the product of all the rows in these tables.
Correct Answer : Natural join
Explanation : A NATURAL JOIN is an inner join where the RDBMS automatically selects the join columns based on common columns names. Some RDBMS vendors, like Oracle but not SQL Server, implement a NATURAL JOIN operator.
Correct Answer : two
Explanation : In SQL Server we have two types of views-System Defined Views and User Defined View.
Correct Answer : System Defined Views
Explanation : Dynamic Management Views were introduced in SQL Server 2005. These Views give the administrator information of the database about the current state of the SQL Server machine.
Correct Answer : CREATE VIEW AS SELECT
Explanation : SQL CREATE VIEW Syntax:CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition.
Correct Answer : DROP VIEW
Explanation : DROP VIEW removes one or more views from the current database.
Correct Answer : sys.dm_exec_connections
Explanation : The INFORMATION_SCHEMA views allow you to retrieve metadata about the objects within a database. These views can be found in the master database under Views / System Views and be called from any database in your SQL Server instance.
Correct Answer : None of the above
Explanation : Server-scoped Dynamic Management View is stored only in the Master database.
Correct Answer : With
Explanation : The with clause provides a way of defining a temporary relation whose definition is available only to the query in which the with clause occurs.
Correct Answer : Lateral
Explanation :
Correct Answer : Distinct
Explanation : Distinct keyword is used to select only unique items from the relation.
Correct Answer : Count(attribute)
Explanation : * is used to select all values including null.
Correct Answer : Not exists
Explanation : Exists is used to check for the existence of tuples.
Correct Answer : Get-ChildItem
Explanation : Get-ChildItem is used to get the contents of a folder or registry key.
Correct Answer : ConvertFrom-CSV
Explanation : ConvertFrom-CSV convert object properties (in CSV format) into CSV objects.
Correct Answer : Get-Process | Get-Member
Explanation : The Get-Process cmdlet gets the processes on a local or remote computer.
Correct Answer : Set-ExecutionPolicy Unrestricted
Explanation : Unrestricted removes all restrictions from the execution policy.
Correct Answer : 2008
Explanation : SQL Server has had only a partial implementation up to now, but it has come up in SQL 2012.
Correct Answer : Aggregation
Explanation : Most important benefit of window functions is that we can access the detail of the rows from an aggregation.
Correct Answer : RANK
Explanation : Since SQL Server 2005 we have had support for some window functions, they are: ROW_NUMBER, RANK, DENSE_RANK and NTILE.
Correct Answer : LEAD
Explanation : There are 8 analytic window functions in SQL Server 2012.
SELECT ID, name FROM 1_Order WHERE instructor=1;
Correct Answer : _Order
Explaination : Table name should not start with numerical value as per naming convention in T-SQL.
Correct Answer : drop database database_name
Explanation : This will delete the database with its structure.
Correct Answer : Like Predicate
Explanation : Like matches the pattern with the query.
Correct Answer : Backup
Explanation : Backup is required to protect the data.
Correct Answer : Inner
Explanation : Inner query joins only the rows that are matching.
Correct Answer : ALTER database database_name
Explanation : ALTER Statement will alter the database structure and its related functionalities.
Correct Answer : NULL
Explanation : NOT NULL specifies that the column does not accept NULL values. For more information.
Correct Answer : Foreign key
Explanation : FOREIGN KEY constraints identify and enforce the relationships between tables.
Correct Answer : FOREIGN KEY constraints identify and enforce the relationships between tables
Explanation : Foreign key is to enforce referential integrity.
Correct Answer : NO ACTION
Explanation : The ON UPDATE clause defines the actions that are taken if you try to update a candidate key value to which existing foreign keys point.
Explanation : Constraints are Primary key, Foreign Key, Unique Key, Not Null, Check, Default.
Correct Answer : Primary key constraint
Explanation : Each table is having only one primary key constraint and it contains only unique values.
DECLARE @parent HierarchyId = HierarchyId::GetRoot() INSERT INTO H (Node,ID,Name) VALUES (@parent.GetDescendant(NULL,NULL),2,'Johnny')
Correct Answer : Takes 2 arguments
Explaination : GetDescendant method takes 2 arguments indicating the left and right nodes on the child level respectively.
DECLARE @parent HierarchyId = (SELECT Node FROM H WHERE Name = 'Thuru') DECLARE @parentNodeLevel INT = (SELECT NodeLevel FROM h WHERE name = 'Thuru') SELECT Node.ToString() AS NodeText, * FROM H WHERE Node.IsDescendantOf(@parent) = 'TRUE' AND Node != @parent AND NodeLevel = @parentNodeLevel + 1
Correct Answer : IsDescendantOf
Explaination : In the above query,immediate children is returned using the NodeLevel column (@parentNodeLevel + 1). And also notice that I have opted out the parent node because IsDescendantOf function includes the parent node as well in the result.
Correct Answer : DELETE FROM H WHERE Name = ‘Steve’
Explanation : Deleting a node does not automatically delete the child nodes, this would result in orphaned children.
DECLARE @newParent HierarchyId = (SELECT Node FROM H WHERE name = 'Johnny') UPDATE H SET Node = Node.__________(Node.GetAncestor(1),@newParent) WHERE Name = 'S1'
Correct Answer : GetReparentedValue
Explaination : The GetReparentedValue function is used to move the nodes to different locations.
Explanation : XML is a good choice if you want a platform-independent model in order to ensure the portability of the data by using structural and semantic markup.
Correct Answer : Small object storage
Explanation : XML uses large object storage which uses varchar(max) and varbinary(max).
Explanation : Different XML choices may also have different performance characteristics.
Correct Answer : Combination of relational and XML data type columns
Explanation : Hybrid model may yield better performance in that you have more control over the indexes created on the relational columns and locking characteristics.
Correct Answer : Granularity
Explanation : When the granularity is large, locking large XML instances for updates causes throughput to decline in a multi user scenario.
Correct Answer : SQL Server 2005
Explanation : XML data type allows variables and columns to be created to specifically hold xml data, whether it be an entire XML document or just some XML content.
Correct Answer : Stored Procedure
Explanation : You need to create a stored procedure to process incoming XML from a partner before SQL Server 2005.
Correct Answer : query()
Explanation : xml.query() method takes an XQuery statement and returns an instance of the XML data type.
Correct Answer : 5
Explanation : SQL Server provides five xquery methods to query xml file or xml data.
Correct Answer : QName
Explanation : A QName is made up of a namespace prefix and a local name. In this implementation, the variable names in XQuery are QNames and they cannot have prefixes.
Explanation : The W3C specification allows type errors to be raised statically or dynamically, and defines static, dynamic, and type errors.
Correct Answer : Typed
Explanation : Some of the XQuery operators, such as arithmetic and comparison operators, depend on atomization.
Correct Answer : Delete nodes
Explanation : To change or remove a portion of the content in an XML variable or column, the modify() method can be used.
Correct Answer : To determine if the XML data contains a certain node
Explanation : exist() method is used to determine whether a query returns a nonempty result or not.
Correct Answer : full-text
Explanation : Before you can run full-text queries on a table, the database administrator must create a full-text index on the table.
Correct Answer : optimal
Explanation : Full-text queries perform linguistic searches against text data in full-text indexes.
Explanation : Full Text Index can be used to search words, phrases and multiple forms of word or phrase.
Correct Answer : exact
Explanation : In full-text search, a word (or token) is a string whose boundaries are identified by appropriate word breakers, following the linguistic rules of the specified language.
Correct Answer : READ_COMMITTED_SNAPSHOT
Explanation : ALTER DATABASE is used for instant file initialization when adding or growing data files.
Correct Answer : Service Broker options
Explanation : CREATE DATABASE is used mainly for creating new database.
Correct Answer : CONTROL
Explanation : To execute DROP DATABASE, at a minimum, a user must have CONTROL permission on the database.
Correct Answer : sys.databases
Explanation : sys.databases catalog view is used for displaying the present status of the system.
Correct Answer : T-SQL
Explanation : Transact-SQL (T-SQL) is Microsoft’s and Sybase’s proprietary extension to SQL.
Explanation : Transact-SQL is central to using SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.
Correct Answer : CREATE USERS
Explanation : SQL Server Service Broker provides native support for messaging and queuing applications in the SQL Server Database Engine.
Correct Answer : Returns the status for the last transmission for one side of a conversation
Explanation : GET_TRANSMISSION_STATUS returns a string describing the status of the last transmission attempt for the specified conversation.
Explanation : SQL Server provides many security statements for a secure database.
Correct Answer : Deletes the master key of the current database
Explanation : CLOSE MASTER KEY statement closes the master key of the current database.
Correct Answer : Control-of-flow language
Explanation : Transact-SQL provides special words called control-of-flow language that control the flow of execution of Transact-SQL statements.
Correct Answer : GOTO
Explanation : The Transact-SQL statement or statements that follow GOTO are skipped and processing continues at the label.
Correct Answer : BREAK and CONTINUE
Explanation : The BREAK statement exits the innermost WHILE loop and the CONTINUE statement restarts a WHILE loop.
Correct Answer : when an IF statement controls the execution of only one Transact-SQL statement
Explanation : Use the BEGIN and END statements anywhere a control-of-flow statement must execute a block of two or more Transact-SQL statements.
Explanation : A BEGIN and END statement block must contain at least one Transact-SQL statement.
WAITFOR DELAY '00:00:02'; SELECT BusinessEntityID FROM AdventureWorks2008R2.HumanResources.Employee;
Correct Answer : 2
Explaination : The DELAY keyword followed by a time_to_pass before completing the WAITFOR statement. The time to wait before completing the WAITFOR statement can be up to 24 hours.
Explanation : Variable feature was introduced in SQL Server 2008.
Correct Answer : @@IDENTITY
Explanation : @@identity retains the last value inserted into an IDENTITY column, even if the statement that inserted it fails to commit.
Correct Answer : @@IDLE
Explanation : Return type of @@IDLE is int.
Explanation : The five steps are Declare Cursor,Open,fetch,CLose and Deallocate.
Explanation : SQL Server supports four types of cursor.
Correct Answer : WHILE
Explanation : Cursor alternatives are WHILE loop, subqueries, Temporary tables and Table variables.
Explanation : A dynamic cursor allows you to see the data updation, deletion and insertion in the data source while the cursor is open.
Correct Answer : FORWARD_ONLY STATIC
Explanation : A FORWARD_ONLY STATIC Cursor is populated at the time of creation and cached the data to the cursor lifetime. It is not sensitive to any changes to the data source.
Correct Answer : Read-only
Explanation : A static cursor populates the result set at the time of cursor creation and query result is cached for the lifetime of the cursor.
Correct Answer : TRY…CATCH
Explanation : SQL Server 2005 introduced TRY…CATCH statement which helps us to handle the errors effectively in the back end.
Correct Answer : INSERT
Explanation : DML statements can be checked for handling errors.
Correct Answer : Error handling
Explanation : TRY… CATCH implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C#.
Correct Answer : THROW
Explanation : Generates an error message and initiates error processing for the session.
Correct Answer : ERROR_STATE()
Explanation : ERROR_STATE() returns the state number of the error.
Correct Answer : Severity
Explanation : ERROR_SEVERITY() returns the severity level of the error.
Explanation : Output parameters, which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).
Explanation : sp_help provides details on any database object such as tables,views and so on.
Correct Answer : User defined
Explanation : User defined procedure can be created in all system databases except the Resource database or in a user-defined database.
Explanation : We can nest stored procedures and managed code references in Sql Server up to 32 levels only.
Correct Answer : @@NESTLEVEL
Explanation : When @@NESTLEVEL is executed within a Transact-SQL string, the value returned is 1 + the current nesting level.
Explanation : SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value.
Correct Answer : Insert
Explanation : We can’t use function to Insert, Update, Delete records in the database table(s).
Correct Answer : They allow modular programming
Correct Answer : CREATE FUNCTION
Explanation : If a CREATE FUNCTION statement produces side effects against resources that do not exist when the CREATE FUNCTION statement is issued.
Explanation : We return any data type value from function.
Correct Answer : Stored
Explanation : Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements.
Explanation : In Sql Server we can create four types of triggers Data Definition Language (DDL) triggers, Data Manipulation Language (DML) triggers, CLR triggers and Logon triggers.
Correct Answer : Table and Views
Explanation : AFTER trigger fires after SQL Server completes the execution of the action successfully that fired it.
Explanation : In SQL Server we can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations.
Correct Answer : AFTER
Explanation : AFTER triggers do not work for views.
Correct Answer : Select
Explanation : Valid trigger actions are INSERT, UPDATE and DELETE, or a combination of several, separated by commas.
Correct Answer : ALTER TABLE statement
Explanation : You can also use the ALL keyword instead of a trigger name to enable/disable all of the triggers on a table in question.
Explanation : SQL Server offer three ways of running a dynamically built SQL statement.
Correct Answer : Stored procedure
Explanation : Dynamic SQL is a term used to mean SQL code that is generated programmatically (in part or fully) by your program before it is executed.
CREATE PROCEDURE GetArticle @ArticleID INT AS SELECT ArticleTitle, ArticleBody FROM Articles WHERE ArticleID = @ArticleID GO -
Correct Answer : input
Explaination : The output of an SP could also be returned in an output parameter.
Correct Answer : EXECUTE(@SQLStatement)
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = ' + CAST(@EmpID AS NVARCHAR(10)) EXECUTE(@SQLQuery)
Correct Answer : sp_executesql
Explanation : sp_executesql executes a Transact-SQL statement or batch that can be reused many times or one that has been built dynamically. Run time-compiled Transact-SQL statements can expose applications to malicious attacks.
Correct Answer : malicious
Explanation : SQL injection is a code injection technique, used to attack data-driven applications.
Correct Answer : xp_regwrite
Explanation : xp_regwrite writes an arbitrary value into the Registry (undocumented extended procedure).
Correct Answer : sp_addextendedproc ‘xp_cmdshell’,’xp_log70.dll’
Explanation : Security best practices for SQL Server recommends disabling xp_cmdshell in SQL Server 2000 (in SQL Server 2005 it is disabled by default). However, if we have sysadmin rights (natively or by bruteforcing the sysadmin password, see below), we can often bypass this limitation.
Correct Answer : For integer inputs : convert(int,@@version)
Explanation : Quick detection attacks should throw conversion errors.
Correct Answer : Blind SQL Injection
Explanation : Blind SQL Injection is just like sleep, wait for specified time.
Correct Answer : Full
Explanation : You can minimize logging for a set of bulk operations by switching the database to the bulk-logged recovery model temporarily for bulk operations.
Explanation : SQL Server bulk-import operations do not support importing data from comma-separated value (CSV) files.
Correct Answer : bcp utility
Explanation : bcp is a command line utility for bulk import and export.
Correct Answer : INSERT … SELECT
Explanation : INSERT … SELECT is a Transact-SQL statement that uses the OPENROWSET bulk rowset provider to bulk import data into a SQL Server table by specifying the OPENROWSET(BULK…) function to select data in an INSERT statement.
Explanation : BATCHSIZE =batch_size specifies the number of rows in a batch. Each batch is copied to the server as one transaction.
Correct Answer : Comma
Explanation : Field Terminator is used to delimit columns in the source file. There are some cases where I have seen “|” also. “,” can be part of Name. So in that case the bulk insert may fail.
Correct Answer : 2005
Explanation : Service Broker in Microsoft SQL Server 2005 is a new technology that provides messaging and queuing functions between instances.
Correct Answer : 3
Explanation : The three components of the Service broker are as follows: conversation components, service definition components and networking and security components.
Correct Answer : Conversation
Explanation : Networking and security components defines the infrastructure used for exchanging messages between instances.
Correct Answer : Unauthorized
Explanation : Service broker is essential component for security of database.
Explanation : SQL Server Service Broker (SSBS), introduced with SQL Server 2005 allows you to write queuing/message based applications within the database itself.
Correct Answer : Messages can be sent to multiple target services (multicast)
Explanation : Queues have a new column, message_enqueue_time, that shows how long a message has been in the queue.
Correct Answer : sys.dm_broker_activated_tasks
Explanation : sys.dm_broker_activated_tasks returns a row for each stored procedure activated by Service Broker.
Correct Answer : ssbdiagnose
Explanation : The ssbdiagnose utility reports issues in Service Broker conversations or the configuration of Service Broker services.
Correct Answer : Replication
Explanation : Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks.
Explanation : Microsoft SQL Server provides three types of replication for use in distributed applications.
Explanation : Replication allows us to maintain the same database multiple copies at different locations.
Explanation : In SQL Server 2005, replication had to be stopped in order to perform some actions like adding nodes, making schema changes, etc. But in 2008, these can be done online.
Correct Answer : Publisher
Explanation : Publisher is a source database where replication starts.
Correct Answer : push and pull
Explanation : Push subscriptions are created at the Publisher server and pull subscriptions initiates the replication instead of the publisher.
Correct Answer : Import and Export
Explanation : With SSIS wizards, we can create a structure on how the data flow should happen and make a package and deploy it based on our needs to execute in any environment.
Correct Answer : Plan for capacity by understanding resource utilization
Explanation : SQL Server Integration Services is designed to process large amounts of data row by row in memory with high speed. Because of this, it is important to understand resource utilization, i.e., the CPU, memory, I/O, and network utilization of your packages.
Correct Answer : Application contention
Explanation : In Application contention, SQL Server is taking on more processor resources, making them unavailable to SSIS.
Correct Answer : Process Bytes
Explanation : Process byte is the amount of memory currently in use by Integration Services. This memory cannot be shared with other processes.
Correct Answer : Not making use of parallelism
Explanation : The design limitation of SSIS package is not making use of parallelism.
Correct Answer : Network Transfers
Explanation : If Network Transfers/sec is approaching 40,000 IOPs, then get another NIC card and use teaming between the NIC cards.
Correct Answer : unlink
Explanation : If you have linked the Access tables to one SQL Server instance, and then you want to change the links to another instance, you must relink the tables.
Correct Answer : SSMA
Explanation : SSMA stands for Microsoft SQL Server Migration Assistant for Access.
Correct Answer : Slow performance with linked tables
Correct Answer : Highlighting of “well known†LOB schemas
Explanation : This change was done so that they can be ignored in conversion.
Correct Answer : Access Metadata Explorer
Explanation : SSMA contains two metadata explorers to browse and perform actions on Access and SQL Server or Azure SQL DB databases.
Correct Answer : Tcp
Explanation : You must restart the SQL Server service after making changes to network protocols.
Correct Answer : recovery
Explanation : A recovery model is a database property that controls how transactions are logged.
Correct Answer : Simple
Explanation : Simple recovery model is the simplest of all and maintains only a minimum amount of information in the SQL Server transaction log file.
Explanation : Operations that require transaction log backups are not supported by the simple recovery model.
Explanation : Six backups if full recovery model are Complete backup, Differential backup, File and/or Filegroup backup, Partial backup, Copy-Only backup, Transaction log backup.
Correct Answer : Bulk-logged
Explanation : Full Recovery and Bulk-Logged Recovery models provide the greatest protection for data.
Correct Answer : ALTER DATABASE
Explanation : You can use ALTER DATABASE command with the SET RECOVERY option to change the recovery model of a database.
Correct Answer : protecting
Explanation : SQL Server offers many options for creating backups.
Correct Answer : 8
Explanation : DatabaseBackup is the SQL Server Maintenance Solution’s stored procedure for backing up databases.
Explanation : Secure and automated backup of your company’s databases with no downtime; easy to deploy and administer.
Correct Answer : Online
Explanation : Offline Data Cannot Be Backed Up.
Correct Answer : table backup
Explanation : In order to select a proper SQL Server backup type, a SQL Server administrator needs to understand the difference between the major backup.
Correct Answer : database backup
Explanation : Differential database backups contain only changes made to the database since its most recent full database backup.
Explanation : Log backup includes backup of transaction logs that includes all log records that were not backed up in a previous log backup.
Correct Answer : partial
Explanation : Partial backups consist of data in the primary filegroup, every read/write filegroup and any optionally-specified read-only files.
Correct Answer : Agent
Explanation : The SQL Server Agent is a service that lets you configure scheduled tasks and system alerts.
Explanation : SQL Server Agent consist of jobs,alerts and operators.
Correct Answer : msdb
Explanation : msdb is a system database that is created when you first install SQL Server.
Explanation : Open up Microsoft SQL Server Configuration Manager and locate the SQL Server Agent service. If the status of that service is “RUNNING”, you do not need to do anything.
Explanation : An operator defines contact information for an individual responsible for the maintenance of one or more instances of SQL Server.
Correct Answer : members of the sysadmin
Explanation : Members of the SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole fixed database roles in msdb, and members of the sysadmin fixed server role have access to SQL Server Agent.
Correct Answer : High Availability
Explanation : High availability of the SQL Server instance is protected by the presence of redundant nodes in the FCI.
Explanation : You can even run an FCI-hosted database inside an AlwaysOn availability group.
Correct Answer : One file share resource, if the FILESTREAM feature is installed
Explanation : One file share resource is included only if the FILESTREAM feature is installed.
Correct Answer : SQL Server Binaries
Explanation : However, during startup, SQL Server binaries are not started automatically, but managed by WSFC.
Explanation : To update or remove a SQL Server failover cluster, you must be a local administrator with permission to login as a service on all nodes of the failover cluster.
Correct Answer : Node Majority
Explanation : In Node majority, each node that is available and in communication can vote. The cluster functions only with a majority of the votes.
Correct Answer : Cluster Shared Volumes (CSV)
Explanation : A SQL 2014 deployment with Cluster Shared Volumes provides several advantages over a deployment on “traditional” cluster storage.
Correct Answer : Backward compatibility
Explanation : SQL Server Authentication is provided for backward compatibility. For example, if you create a single Windows 2000 group and add all necessary users to that group you will need to grant the Windows 2000 group login rights to SQL Server and access to any necessary databases.
Correct Answer : Windows 98
Explanation : Windows Authentication Mode is also unavailable on Microsoft Windows Millennium Edition.
Correct Answer : Domain
Explanation : Managing access from the domain level can simplify account administration.
Explanation : When using SQL Server Authentication, logins are created in SQL Server that are not based on Windows user accounts.
Correct Answer : SQL Server Authentication cannot use Kerberos security protocol
Explanation : Windows Authentication uses Kerberos security protocol.
Correct Answer : NetValidatePasswordPolicy
Explanation : NetValidatePasswordPolicy API is only available in Windows Server 2003 and later versions.
Correct Answer : UserName = SUSER_SNAME()
Explanation : USER_NAME or CURRENT_USER Return the database user name.
Correct Answer : vwVisibleLabels
Explanation : The purpose of vwVisibleLabels view is to enforce the row security by joining it with the base table.
Correct Answer : usp_GetCurrentUserLabel
Explanation : usp_GetSecLabelDetails generates the security label identifier and encryption objects for the specified label.
Explanation : The Label Security Toolkit provides tools and techniques for using Microsoft® SQL Server (versions 2005 through 2012) to implement row-level security (RLS) and cell-level security (CLS) based on security labels.
Explanation : All of the specified functions will return user name.
Correct Answer : EXECUTE
Explanation : Users can only access data through the stored procedures provided.
EncryptByKey ( key_GUID , { 'cleartext' | @cleartext } [, { add_authenticator | @add_authenticator } , { authenticator | @authenticator } ] )
Correct Answer : @add_authenticator
Explaination : add_authenticator indicates whether an authenticator will be encrypted together with the cleartext.
Correct Answer : Page
Explanation : Performing the encryption at the page level enables the encryption process to be completely transparent to the client applications.
Correct Answer : sys.certificates
Explanation : To find out what is signed by the certificate, you can use the sys.certificates.
Correct Answer : Column-level Encryption
Explanation : To use cell-level encryption, the schema must be changed to varbinary, then reconverted to the desired data type.
Correct Answer : DECRYPTBYKEYAUTOASYMKEY
Explanation : DECRYPTBYKEYAUTOASYMKEY, which decrypts data by using a symmetric key that’s automatically decrypted with an asymmetric key.
Correct Answer : ApexSQL
Explanation : ApexSQL Audit is a tool for auditing SQL Server data changes specifically for Microsoft SQL Server.
Correct Answer : Object Name
Explanation : Object Name is the name of the object to audit.It does not apply to audit groups.
Correct Answer : Continue
Explanation : Select Continue option when continuing the operation of the Database Engine is more important than maintaining a complete audit. This is the default selection.
Correct Answer : auditpol /set /subcategory:â€application generated†/success:enable /failure:enable
Explanation : auditpol is command is new to Windows Server 2008 and Vista and is required for querying or configuring audit policy at the subcategory level.
Explanation : Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services.
Correct Answer : SQL:BatchCompleted
Explanation : The event class contains all of the data that can be reported by an event.
Correct Answer : TRACE_FILE_ROLLOVER
Explanation : As more rollover trace files are created, the integer value appended to the file name increases sequentially.
Correct Answer : N’%SQLDIR%\MSSQL\DATA\blackbox.trc
Explanation : The default file_count is 2 but can be overridden by the user using filecount option.
Correct Answer : SQL:BatchStarting
Explanation : SQL:BatchStarting occurs when a Transact-SQL batch has started.
Correct Answer : sys.dm_os_wait_stats
Explanation : sys.dm_os_wait_stats returns information about all the waits encountered by threads that executed.
Correct Answer : DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR);
Explanation : DBCC SQLPERFcommand resets all counters to 0.
Correct Answer : Resource
Explanation : Examples of resource waits are locks, latches, network and disk I/O waits. Lock and latch waits are waits on synchronization objects.
Correct Answer : ASYNC_NETWORK_IO
Explanation : AUDIT_XE_SESSION_MGR occurs when there is a wait on a lock that is used to synchronize the starting and stopping of audit related Extended Events sessions.
Correct Answer : When the Service Broker lazy flusher flushes the in-memory transmission objects to a work table
Explanation : BROKER_SHUTDOWN tries to shut down the task.
Correct Answer : HADR_DBR_SUBSCRIBER_FILTER_LIST
Explanation : The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the list of event subscribers that correspond to availability databases.
Correct Answer : LCK_M_U_ABORT_BLOCKERS
Explanation : LCK_M_U_ABORT_BLOCKERS occurs when a task is waiting to acquire an Update lock with Abort Blockers.
Correct Answer : tempdb
Explanation : It is required to create or rebuild an index when tempdb is on a set of disks different from that of the user database.
Correct Answer : An xml data type can only be a key column only in an XML index
Explanation : QL Server 2012 SP1 introduces a new type of XML index known as a Selective XML Index. This new index can improve querying performance over data stored as XML in SQL Server, allow for much faster indexing of large XML data workloads.
Correct Answer : same
Explanation : A nonpartitioned clustered index and the base table always reside in the same filegroup.
Correct Answer : CLUSTERED
Explanation : Clustered indexes sort and store the data rows in the table based on their key values.
Correct Answer : ALLOW_PAGE_LOCKS
Explanation : ALTER INDEX statement requires ALTER permission on the table or view.
Correct Answer : RID
Explanation : RID stands for Row identifier and is used to lock a single row within a table.
Correct Answer : Shared locks
Explanation : Shared (S) locks on a resource are released as soon as the data has been read.
Correct Answer : SERIALIZABLE
Explanation : When SERIALIZABLE isolation level is set – a range lock is placed on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete.
Correct Answer : Bulk update
Explanation : Bulk update (BU) locks are used when bulk copying data into a table and either the TABLOCK hint is specified.
Correct Answer : READUNCOMMITTED
Explanation : Do not issue shared locks and do not honor exclusive locks.
Correct Answer : SIX
Explanation : SIX stands for Shared with intent exclusive and can modify some (but not all) resources lower in the hierarchy by placing IX locks on those individual resources.
Correct Answer : RangeX_X
Explanation : RangeX-X locks are exclusive resource locks acquired on Index key values from a specific range when the transaction modifies a key value from the Index.
Correct Answer : Use clustered indexes on high-usage tables
Explanation : Clustered index should be created on frequently used tables.
Correct Answer : Block
Explanation : Lock is done by database when any connection access the same piece of data concurrently and another is demanding for it.
Explanation : An XML deadlock graph has two main sections: Processes section and Resources section.
Correct Answer : Resources
Explanation : Resources section lists all the resources that were involved in the deadlock, which locks each process acquired and which locks they requested.
Correct Answer : Reader-Writer
Explanation : When you look at the resources involved, you’ll see that the signature of this form of deadlock is locked that are all either a shared (S) lock granted and an exclusive (X) lock requested or an X lock granted and an S lock requested.
Correct Answer : Optimistic Locking in SQL Server using the ROWVERSION
Explanation : Implementing optimistic locking utilizing the row version data type is an effective, low overhead way to prevent lost updates while still maintaining application concurrency.
Correct Answer : 15000
Explanation : In versions earlier than SQL Server 2012, the number of partitions was limited to 1,000 by default
Correct Answer : Partition function
Explanation : Partition function defines the number of partitions that the table will have and how the boundaries of the partitions are defined.
Correct Answer : 16 GB
Explanation : Systems with 16 GB of RAM that run many memory-intensive processes may run out of memory on operations that run on a large number of partitions.
Correct Answer : DBCC
Explanation : The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.
Correct Answer : Both compile-time and run-time
Explanation : Execution plans now provide an optional Partitioned attribute that indicates that an operator, such as a seek, scan, insert, update, merge, or delete, is performed on a partitioned table.
Correct Answer : parallel
Explanation : As part of the execution strategy, the query processor determines the table partitions required for the query and the proportion of threads to allocate to each partition.
Explanation : SQL Server Resource Governor is a feature than you can use to manage SQL Server workload and system resource consumption.
Correct Answer : ALTER RESOURCE GOVERNOR
Explanation : You can enable the Resource Governor by using either SQL Server Management Studio or Transact-SQL.
Correct Answer : CONTROL SERVER
Explanation : The classifier function is run for new connections so that their workloads can be assigned to workload groups.
Correct Answer : ALTER RESOURCE GOVERNOR RECONFIGURE;
Explanation : The Resource Governor is turned off by default.
Correct Answer : CAP_CPU_PERCENT
Explanation : Workloads associated with the pool can use CPU capacity above the value of MAX_CPU_PERCENT if it is available, but not above the value of CAP_CPU_PERCENT.
Correct Answer : AFFINITY
Explanation : By using the AFFINITY option the Sales and Marketing workloads can be scheduled on different CPUs.
Correct Answer : min(X,Y)
Explanation : The minimum of the configured MAX value of a pool and the sum of the MIN values of other pools is subtracted from 100 percent.
Explanation : Business intelligence (BI) is a broad category of application programs and technologies for gathering, storing, analyzing, and providing access to data from various data sources.
Explanation : SQL Server Business Intelligence delivers a comprehensive platform empowering organizations to build and deploy secure, scalable and manageable BI apps.
Correct Answer : Balanced Scorecard
Explanation : The balanced scorecard (BSC) is a strategy performance management tool – a semi-standard structured report, supported by design methods and automation.
Correct Answer : Data staging
Explanation : In computing, Extract, Transform and Load (ETL) refers to a process in database usage and especially in data warehousing
Correct Answer : EIS
Explanation : EIS stands for Enterprise Information System.
Correct Answer : OBIEE
Explanation : Oracle Business Intelligence Enterprise Edition Plus, also termed OBI EE Plus, is Oracle Corporation’s set of business intelligence tools.
Correct Answer : server
Explanation : Members of this role have access to all Analysis Services databases and objects on an instance of Analysis Services.
Correct Answer : Database
Explanation : An Analysis Services database role defines user access to objects and data in an Analysis Services database.
Correct Answer : Assemblies
Explanation : Assemblies let you extend the business functionality of MDX and DMX.
Correct Answer : ImpersonateAnonymous
Explanation : The default setting, ImpersonateCurrentUser, runs an assembly under the current user’s network login account.
Correct Answer : PermissionSet
Explanation : You can use stored procedures to call external routines from Microsoft SQL Server 2005 Analysis Services(SSAS).
Correct Answer : CALCULATE
Explanation : In Microsoft SQL Server Analysis Services, a Multidimensional Expressions (MDX) script is made up of one or more MDX expressions or statements that populate a cube with calculations.
Correct Answer : IgnoreNone
Explanation : Error handling is done through the ScriptErrorHandlingMode enumerator.
Correct Answer : CREATE CELL CALCULATION
Explanation : CREATE CELL CALCULATION statement creates a calculated cell formula for a specified set of tuples within a cube.
Correct Answer : CREATE ACTION
Explanation : Creates an action that can be associated with a cube, dimension, hierarchy, or subordinate object.
Correct Answer : REFRESH CUBECube_Name
Explanation : REFRESH CUBECube_Name refreshes the client’s cache for a cube.
Correct Answer : Power View
Explanation : Power view gives interactive data exploration and visual presentation experience designed to let you create and interact with reports based on Analysis Services tabular models.
Explanation : Reporting Services Configuration Manager is used to configure a Reporting Services installation.
Correct Answer : Rsconfig Utility
Explanation : You can also use it to specify a user account to use for unattended report processing.
Correct Answer : Report Server Web service URL
Explanation : Use Report Server Web service URL tool to browse content in the report catalog in a generic item navigation page.
Correct Answer : Data connections
Explanation : A data connection includes a name and connection properties that are dependent on the connection type.
Correct Answer : Data alert
Explanation : A data alert definition includes which data to use from existing report data feeds, the conditions to be met, a schedule.
Correct Answer : Linked report
Explanation : Linked reports include security, parameters, location, subscriptions, and schedules.
Correct Answer : 711 Mb
Explanation : SQL Server 2008 R2 can run on both 32-bit and 64-bit systems.
Correct Answer : Client components
Explanation : Client components does not include SSIS tools.
Correct Answer : Support for 32-bit and 64-bit processors
Explanation : SQL Server 2008 R2 Datacenter provides the complete feature set available in SQL Server 2008.
Correct Answer : In memory database
Explanation : In-memory database capability is provided by 2012.
Correct Answer : Minimum:512 MB
Explanation : Recommended RAM is 2.048 GB or more for SQL Server 2008.
Correct Answer : 1.4 GHz
Explanation : Recommended speed is 2.0 GHz or faster.
Correct Answer : Windows Server 2003 R2 SP2 64-bit Itanium Datacenter
Explanation : SQL Server 2008 R2 Enterprise is designed for large, highly scalable enterprise implementations.
Correct Answer : Shared Memory
Explanation : All the x86 and x64 editions of SQL Server, except the SQL Server Express versions.
Correct Answer : Northwind
Correct Answer : Tempdb
Explanation : Tempdb is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there.
Correct Answer : Master
Explanation : Master database includes information about the file locations of the user databases, as well as logon accounts, server configuration settings, and a number of other items such as linked servers and startup stored procedures.
Correct Answer : Msdb
Explanation : The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.
Correct Answer : Model
Explanation : When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database.
Explanation : The master database records all of the system level information for a SQL Server system.
