SQL
Can we set up multiple secondary databases in Log Shipping configuration?
False True
Which o f the following returns a list o f the database data files and log files of the original database of a given backup file?
RESTORE VERIFYONLY RESTORE HEADERONLY RESTORE FILELISTONLY All of the mentioned
Which SQL keyword is used to sort the result-set?
ORDER BY SORT ORDER SORT BY
This cmd utility is used for remote server management but encrypts all communications.
SSH telnet ping 20. What moves you into the root directory on the default drive?] ]CD
Which one of the following is not one of the four pillars of the Database System Hardware Performance.
CPU/Processors Disk/Storage Keyboard Memory/RAM Network 1
Which of the DDL statements creates a session object that contains the metadata?
CREATE EVENT SESSION CREATE SESSION CREATE EVENT SESSION METADATA None of the mentioned
which is true about NON-CLUSTERED INDEX?
Data Page AND Index Page ARE INDEPENDENT WE CAN HAVE UPTO 999 NON-CLUSTERED INDEXES PER TABLE LOGICALLY ORDERS THE DATA UNIQUE KEY CONSTRAINT CREATES NON-CLUSTERED INDEX BY DEFAULT All of the Above 1
Which is true about CLUSTERED INDEX?
Data Page + Index Page together ONE CLUSTERED INDEX PER TABLE PHYSICALLY ORDERS THE DATA PRIMARY KEY CONSTRAINTS CREATE CLUSTERED INDEX BY DEFAULT All of the above 1
Can we set up multiple mirror databases in Mirroring configuration?
False True
Which type of backup below is not applicable for a database in simple recovery mode?
Filegroup backup File level backup Transaction log backup All of the above
Which of the these storage devices can hold the most data
Flash drive RAM ROM Hard drive DVD
What is the advantage of deleting a large number of records in small chunks?
For faster delete operation To manage lag space issues To reduce blocking All of the above 1
Which type of replication creates a global unique identifier column in replicated tables in both replication and subscription databases/tables?
Transactional Snapshot Merge Peer-to-peer
What are the two system databases that are going to be created when we install and configure SQL Server Reporting Services?
reportserver and reportservertempdb 1
SSMS, SQL Server Management Studio is not needed to access or connect to the engine as long as we have SSCM, SQL Server Configuration Manager.
True False
Can a foreign key contain null values?
True False 2
How do you create duplicate SQL Server backup files of a database into multiple locations?
Use Duplicate to Disk='Path
How many types of cursor are present in SQL Server?
3 4 5 6 1
allows you to copy files using command prompt.
COPY COPY PASTE CP PASTE
9. WHICH OF THE FOLLOWING FREE EDITION HAS ALL THE FEATURES OF ENTERPRISE EDITION BUT NOT LICENSED TO BE USED IN A PRODUCTION ENVIRONMENT?
EVALUATION EDITION EXPRESS EDITION DEVELOPER EDITION NONE
Where will you find the SQL Server Installation related logs named as ConfigurationFile.ini?
%programfiles%
The hierarchy of folders in Windows 10
a. File B. Folder Structure C. Subfolder 1
How do we MAINTENANCE AN INDEX TO AVOID FRAGMENTATION OF INDEXES?
(Hint: How do we REORGANIZE or REBUILD an Index?]) Using SCRIPT GUI/SSMS Using MAINTENANCE PLAN All of the Above 15_Join_N_View_QUIZ_Objective_Protected
How many types of replication are provided by SQLServer?
2 3 4 5
The size of a data page is?
8 MB = 8192 KB 8 GB= 8192 MB 8 TB= 8192 GB 8 KB = 8192 Bytes
What is the Size of the Row Offset in a page?
8192 Bytes 96 Bytes 36 Bytes 64 Bytes
What is a view?
A view is a special stored procedure executed when certain event occurs A view is a virtual table which results of executing a pre- compiled query A view is a database diagram None of the Mentioned
Which one of the following query will not disable the indexes on the Employee table under HumanResources schema?
ALTER INDEX IX Employee_OrganizationLevel_OrganizationNade Employee DISABLE: ALTER INDEX IX_Emptoyee_OrganizationLevel_OrganizationNode ON HumanResources DISABLE: CREATE INDEX IX Employee OrganizationLevel_OrganizationNode ON HumanResources, Employee DISABLE: ALTER INDEX ALL ON HumanResources.émployee DISABLE;
Which of the following codes Enables the audit 'HIPAA_Audit'?
ALTER SERVER AUDIT HIPAA_Audit WITH (STATE ==1); ALTER SERVER AUDIT HIPAA_Audit WITH (STATE = OFF); ALTER SERVER AUDIT HIPAA_Audit WITH (STATE = ON); None of the mentioned 1
What is the best way to address a hard drive space issue in a log file of a production critical database having a full recovery model and running with a daily full backup only?
Adding extra expensive hard drive Make the database offline and bring the database online Take transaction log backup & Shrink log file Changing the recovery model to simple and back to full
Which of the following makes data available for replication?
Article Publisher Distributor Subscription
6. WHERE DO MOST ENTERPRISE COMPANIES KEEP OR SAFEGUARD THEIR PRODUCTION SERVERS?
BRANCH OFFICE DATA CENTER HEAD OFFICE LOCAL SERVER
Which one of the following jobs is not managed by Database Maintenance Plan?
Backup Jobs (Full, Differential and Transaction log backups} Index Maintenance Jobs (Reorganize Index, Rebuild Index and Update Stats) Database Maintenance Job (D8CC Checkdb or DatabaseIntegrity Check} Cleanup Jobs (Cleanup files from disk and cleanup history from msdb) None
Which of the following features prefers SQL Server authentication mode over windows authentication?
Backward compatibility Forward compatibility Security All of the mentioned 1
Why do you mainly prefer using Backup/Restore instead of Detach/Attach to have a copy of a production database in a non-production environment?
Because backup & restore is easier to manage Because backup & restore takes less time Because backup & restore requires no downtime All of the above
Which of the following code creates a server audit called "HIPPA_Audit" with a binary file as the target and no options?
CREATE SERVER AUDIT HIPAA_Audit TO SUBJECT { FILEPATH ="
What backup option do you use in order to take a backup without breaking the backup sequence of a given database?
Copy_Only Backup Transaction log backup Full Backup None
What is an Orphan user in SQL Server?
Correct answers: An orphan user is a user without a login in the master database. It occurs either when the login does not exist in the master or when the database is moved from the master where the login exists
What is the difference between REVOKE and deny in SQL Server?
Correct answers: REVOKE removes access that has been GRANTed, DENY explicitly rejects, taking precedence over GRANT.
What is a restore command that helps to redirect datafile and log file locations of a given backup file to a different datafile and log file path?
Correct answers: = Move
Which of the following is a class of constraint in SQL Server?
DEFAULT PRIMARY KEY AND FOREIGN KEY UNIQUE CHECK ALL
Which of the following files of an active database are offline files?
Data files Log files Backup files All the above 1
Which of the following transfer methods makes the database unavailable to users during the transfer?
Detach and attach method SQL Management Object (SMO) method Backup and Restore method None of the mentioned
What is the use of having multiple File Groups?
Disk I/O performance and management Easy management and archiving of the data Benefits of doing filegroup level backups and restores All 08_2_DB_Design_Datatype_DML_DDL_Objective_Clean
What is the name of the system database that is going to be created when we set up replication in SQL Server?
Distribution 1
A hidden & a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys. objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database
Distribution database Resource database Tempdb database Master database
a command which allows the user to clear the complete contents of the screen and leave only a prompt.
ENTER CLEAR SCREEN CLEAR CLs
Which one of the following below is true about replication?
Encrypted objects can't be replicated Primary key is required to replicate a table in transactional replication GUID column will be created automatically when we configure merge replication Subscriber servers can have a different version than the counterpart Publisher server
What is the maximum number of databases that can be created in SQL Server instances?
FYI. As per my experience, I haven't seen a SQL Server Instance hosting more than 60 databases and the majority of the Instances I was exposed so far have less than 20 databases. * 32767 06_2_DB_Design_N_TSQL_Objective
What would be the recovery model of a newly created database in SQL Server for any given Instance?
Full Bulk Logged Simple It depends on the recovery mode of model database on the Instance
8. WHAT IS/ARE THE MAIN SPECIFICATION(S) THAT WE NEED TO CHECK BEFORE INSTALLATION?
HARDWARE OF THE COMPUTER SOFTWARE OF THE COMPUTER Microsoft .Net Framework version All
Which of the following scenario prefers failover cluster instance over standalone instance in SQL Server?
High Safety High Availability High Performance None of the mentioned
7. WHICH OF THE FOLLOWING TASK IS NOT DBA'S PRIMARILY RESPONSIBILITY?
INSTALLATION AND CONFIGURATION OF SQL SERVER DATABASE BACKUP & RECOVERY BUSINESS DATA ANALYSIS SQL SERVER PATCHING
When is it advisable to take a regular backup of MODEL database?
If you have certain objects or configurations in model database that you want to have in all new databases If the model database is in full recovery model If the model database is in simple recovery model None of the above
Which of the following is a disadvantage of replication?
Improved performance To move data closer to the user Easy maintenance None of the mentioned
How does a non-clustered index point to the data?
It never points to anything It points to a data row It is used for pointing data rows containing key values None of the mentioned
What are the Disadvantages of Database Mirroring?
It only works at database level and not at server level. It only propagates changes at database level, no server level objects, such as logins and fixed server role membership, can be propagated. May not be suitable for application using multiple databases and for application that doesn't have a logic to accept multiple connection strings You can configure only one mirror database for a given principal database Potential data lost is possible in asynchronous operation mode. RTO will vary and depend on several factors, such as propagation interval time and bandwidth speed.
19, What is the feature that is introduced in SQL Server 2016 and SQL Server automatically creates the secondary replicas for every database in the group?
Join Only Backup and Restore Automatic Seeding All of the Above:
allows you to list all the files in a directory.
LS DIR LIST DIRECTORY
3. Which one of the following below that the DBA needs to verify before Installing SQL Server Instance?
Local Admin Privilege Username and password of the service account The minimum hardware and the software requirement The collation of the Instance All
allows you to create your own directories in DOS
MD or MKDIR MKD Makedir Createdir
is a system database that can impact the performance of the Instance and is highly recommended to have a separated disk drive and multiple data files based on the number of cores of the server.
Master database Model database MSDB database Tempdb database
The speed at which the CPU processes data to convert is measured mostly in __
Megahertz Gigahertz Nanoseconds A and B 1
Which of the following is DBA's most frequent day to day activity?
Migration Upgrade Database refresh Deployment and disaster recovery
Which of the following are uses of backups except?
Migration and upgrade Data refresh Disaster recovery Database configuration 1
Which of the following pairs of technology is supported in SQL Server?
Mirroring and Clustering Mirroring and Log Shipping Mirroring and Replication All of the mentioned
is considered as the heart (and brains) of the computer.
Motherboard ROM CPU RAM
TRACE_PRODUCE_BLACKBOX file can be saved in the following location?
N'%SQLDIRS
Which of the following elements is installed locally on each node of the FCI?
Network Name SQL Server Binaries Storage WSFC Resource Group
If a transaction has obtained a ...lock, it can read but cannot write on the item Shared Exclusive Read only Write only Which one of the below DBCC commands can help you capture deadlock information in SQL Server error logs?
Note that it requires membership in the sysadmin fixed server role to make this change DBCC TRACEON (1222,-1) DBCC TRACEOFF (1222,-1} DBCC TRACESTATUS All of the above 19 Transactions_Quiz_Subjective
How many tables may be included with a join?
One Two Three All of the Mentioned
How many types of views are available in SQL Server?
One Two Three Four
Which part of a page section helps to store a table and the data resides in the table?
Page Header Data Area Row Offset Extent
Choose the three advantages of using Linked servers from the list below?
Provides the ability to access data from outside of SQL Server Typically, linked servers are used to handle distributed queries. The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise. The ability to provide bi-directional access between servers with a single linked server setup
20. How do you refer to the SQL Server Instance name in a SQL Server Cluster setup?
SOL Server Network Name SQL Server Virtual Name SQL Server Cluster instance Name All
Which SQL statement is used to return only different values?
SELECT DIFFERENT SELECT UNIQUE SELECT DISTINCT SELECT ALL
Write the script you use to change a database into single user mode before you perform a database restore Correct answers: ALTER DATABASE [databasename
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
3. Which of the following SQL Server versions can be supported on both Windows and Linux OS?
SQL SERVER 2012 and 2014 SQL SERVER 2016 and 2017 SQL SERVER 2017 and above
Which of the following SQL Server versions can be supported on both Windows and Linux OS?
SQL SERVER 2012 and 2014 SQL SERVER 2016 and 2017 SQL SERVER 2017 and above
Which of the following authentication modes is more secure?
SQL Server Authentication Windows Authentication Mixed Mode All of the mentioned
What are the available Database Mirroring states?
SYNCHRONIZING SYNCHRONIZED SUSPENDED PENDING FAILOVER DISCONNECTED All 25_Cluster_Quiz_Objective Please discuss among team members and answer the following questions,
Which of the user defined functions will be preferred for adding two numbers?
Scalar Inline Table Valued Max() Min() 1
Does the Mirror instance need to be licensed?
TRUE FALSE
What is NOT NULL Constraint in SQL Server?
The NOT NULL Constraint is used to avoid NULL values but accepted duplicate values into a column. We can apply the NOT NULL constraint on any data type column such as integer, character, money, etc. A table can contain any number of NOT NULL constraints All of the Above
What is the purpose of the index in SQL Server?
To enhance query performance To provide an index to a record To perform quick search or fast retrieval of data All
5. Why do you need a service account when you install SQL Server Instance?
To manage resources external to the server where SQL Server Instance has been installed Local Service account is limited to the resources inside the server SQL Server jobs can access locations outside of the SQL Server host machine All
Which of the following is false about PRIMARY KEY?
UNIQUELY IDENTIFIES EACH ROWS IN A TABLE ONLY ONE PER TABLE NO NULL VALUE ACCEPTS ONLY ONE NULL 1
Which of the following is not a DDL command?
UPDATE TRUNCATE ALTER None of the Mentioned 1
Which of the following are TCL commands?
UPDATE and TRUNCATE SELECT and INSERT GRANT and REVOKE COMMIT and ROLLBACK 1
Which of the following constraints does not enforce uniqueness?
Unique Primary key Foreign key None of the mentioned 1
Which of the following statements is true?
Views could be looked as an additional layer on the table which enables us to protect intricate or sensitive data based upon our needs Views are virtual tables that are compiled at run time Creating views can improve query response time All of the Mentioned
6. Where do you find the setup file of a licensed sql server software before the installation?
Will be available in the NAS location if the setup file has already been downloaded before You can download it from microsoft if the company has MSDN subscription Ask the lead DBA or DBA manager or another DBA or System Admin if there is a particular location where setup files are stored All
Reasons for consideration of the stored procedure can be?
With which tables will you have to work?] Does it make sense to create a VIEW first?] How often will this procedure actually be used?] Do you want to view data in the database (SELECT), insert new records (INSERT INTO), or do I want to change an existing record (UPDATE)?] All of the mentioned 18_Triggers_Quiz_Objective
Active cluster in case of any failover?
Yes No
Is SQL Server a Load balancing solution or not?
Yes No
How do you refresh an active window using a keyboard shortcut?
]F1 F2 F3 F4 F5 Quiz 03_Dos Commands Objective Quiz
WHAT IS/ARE THE MAIN SPECIFICATION(S) THAT WE NEED TO CHECK BEFORE INSTALLATION?
]HARDWARE OF THE COMPUTER SOFTWARE OF THE COMPUTER Microsoft .Net Framework version All
WHICH OF THE FOLLOWING TASK IS NOT DBA'S PRIMARILY RESPONSIBILITY?
]INSTALLATION AND CONFIGURATION OF SQL SERVER DATABASE BACKUP & RECOVERY BUSINESS DATA ANALYSIS SQL SERVER PATCHING
When do you choose a scalar function over inline table valued function?
You want a single value as the result One or more row's worth of data is expected You want multiple values as result All of the mentioned 1
Which one of the following is the right keyboard key combination to lock and unlock your computer?
]Press Ctrl + Alt + Del and then select the "Lock" option or Windows Log Key + L Press Ctrl + Alt + Del and then select the "Switch user" option Press Ctrl + Alt + Del and then select the "Sign out" option Press Ctrl + Alt + Del and then select the "Change a password" option Press Ctrl + Alt + Del and then select the "Task Manager" option 20. Which one of the following is not a web browser software?] ]Internet Explorer Google Chrome Mozilla Firefox Microsoft Edge None 2
Difference between INSERT INTO and SELECT INTO. Choose all that applies SELECT INTO is most commonly used with intermediate data sets, like #temp tables, or to copy out an entire table like for a backup. INSERT INTO is used when you insert into an existing table with a known structure. INSERT INTO is used when the table exists. SELECT INTO works when the table does not exist. In other words INSERT INTO is used to insert data in an existing table which may or may not have data, SELECT INTO creates a new table according to received dat
a. INSERT INTO creates a new Table where one did not exist before and SELECT INTO is used to insert records to an existing table INSERT INTO with no table hints is normally logged where as SELECT INTO is minimally logged assuming proper trace flags are set
Primary key creates ... index by default that physically ordered the dat
a. XML-INDEX NON-CLUSTERED CLUSTERED FILTERED INDEX 1
4. ... Edition of SQL SERVER HAVE BOTH LICENSING OPTIONS?
ie. Per Core License vs. Per CAL License STANDARD ENTERPRISE DEVELOPER EXPRESS
The CPU is also known as:
a. The Brain b. The Processor c. The Central Processing Unit d. All of the above. 1
The system that enables you to interact with your computer and that uses images you click to activate the item is a
a. Windows 10 B. Graphical User Interface (GUI) . C. Desktop
One Terabyte (TB) is equal to. {Note: " represents "The power of'')
a. 2^10 gigabytes b. 2^20 megabytes c. 2^30 kilo bytes d. 2^40 bytes e. All
The area of the desktop that contains program buttons for all open programs
a. Desktop App B. Dashboard C. Taskbar 1
Write the steps to recover a production database if the database crashed on Wednesday at 11:55
a.M, with a database having a full backup every Sunday at 6:00 P.M., differential backup everyday at 6:00 P.M. except Sunday and hourly transaction log backup everyday Correct answers: Apply the last Sunday full backup with norecovery Apply the Tuesday night differential backup with norecovery Apply each transaction log backup after the Tuesday night differential backup until 11 ]a.M. on Wednesday sequentially with norecovery Apply restore database database_name with recovery to make the database available 22_Upgrade_N_Migration_Objective_01
INCLUDE THIS SERVER INTO CENTRAL MONITORING What is Normalization?
and what are the benefits of Normalization?] ]Normalization is the process of organizing data in a database. Its benefits include creating tables and establishing relationships between those tables according to rules designed both to protect the data (higher database security) and to make the database more flexible by eliminating redundancy and inconsistent dependency
What is the main purpose of having a test or lower environment (Development Server ,Test Server and QA server)?
choose the main one. To have a copy of each databases To test changes before its moved to the production For customers use None of the Above
... Edition of SQL SERVER HAVE BOTH LICENSING OPTIONS?
ie. Per Core License vs. Per CAL License ]STANDARD ENTERPRISE DEVELOPER EXPRESS
The script that used to bring a database named "Finprod?
from restoring to online state is Restore Database [Finprod] With Norecovery Restore Database [Finprod] With recovery Restore Database {Finprod] With Restore None of the above.
Data types in SQL Server are organized into how many categories based on Microsoft documentation?
https://docs.microsoft.com/en-us/sql/t-sql/data- types/data-types-transact-sql?]view=sql-server-ver15 6 8 9 7
What are the three options that are available to Configure Read-Only Routing for an Availability Group on the secondary replicas or the available READABLE SECONDARY SETTINGS?
ie. Readable Secondary. ...or... or... Choose all that applies No Yes or All Read-intent only ReadWrite intent
To get the most detailed network configuration, use this command.
ipconfig /all ipconfig ipconfig /renew ipconfig /most 1
Which of the following tables stores history detail for log shipping jobs?
log_shipping_monitor_alert logshipping_monitor_primary None of the mentioned log _shipping_monitor_history_detail
What is the use of Constraints in SQL Server?
ls used to limit the type of data that can go into a table To ensure the accuracy and reliability of the data in the table For data integrity and correctness All of the the above
ls the command that is used to find out different IP statistics such as current connections and any active ports?
netstat nslookup ping cmd 1
You want to find out the IP address of a hostname, like google.com, you should use
nslookup Ping tracert /ping cmd 1
This ipconfig switch requests new IP information from DHCP server
renew release and then use renew return rerun 1
This cmd utility is used for remote server management.
telnet netstat ping SSH 1
What are the advantages of storing the SSIS packages in a File System rather than MSDB database?
(File System Deployment) Bonus The packages are saved on a physical location on the hard disk drive or any shared folder on the network. We can easily manage the packages if we want to bring about any changes in it. Helpful not to bloat MSOB if you have thousands of packages Packages are available when on file system and we can easily load the packages into the designer.
WHICH COLUMN IS THE BEST CANDIDATE FOR AN INDEX from the syntax below?
(Please consider the SELECTIVITY OF AN INDEX). select I-id, |-name, I-city, I-salary from Instructors where I-city = 'Houston' order by I-id ORDER BY
Which of the following is not a typical trigger action?
* Insert Select Delete All of the mentioned
Which role below is available as a server level role and database level role?
* Sys Admin (sa) db_owner Public Option 4 20. Which one of the following is SQL Server Built-in Schema?] DBO Guest SYS INFORMATION SCHEMA All 2
Which of the following code is used to enable the sa login?
* ALTER LOGIN sa ENABLE ;GO ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;GO ALTER LOGIN sa DISABLE ;GO ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;GO ALTER LOGIN sa ENABLE ;GO ALTER LOGIN Sysadmin WITH PASSWORD = *<enterStrongPasswordHere>" ;GO None of the mentioned
Why is it not advised to add additional Transaction log files in a database?
* It is because
How many types of files can be available in SQL Server database and what is the recommended file name extension of each type of files?
* Three. MDF, NDF and LDF for Primary Data Files, Secondary Data Files and Transaction Log Files respectively 1
Write down the list of privileges the nine Fixed Server roles below can do?
- bulkadmin - dbcreator - diskadmin - processadmin - public - securityadmin - serveradmin - setupadmin - sysadmin Correct answers: bulkadmin: can execute BULK INSERT dbcreator:can alter, create, drop, of restore databases diskadmin: can manage disk files, processadmin: can end running processes in the SQL server instance. public: default role for server principals who do not have specific securable object permissions. Only assign public permissions to objects that can be made available to all users. You cannot revoke public permission from any server role. securityadmin:can administer logins, can reset SQL server login passwords, and grant, deny or revoke server-level permissions or database-level permissions serveradmin:can alter server configuration and shut it down setupadmin can add/remove linked servers and run Transact-SQL sysadmin can perform all server activities.
Write down the list o f privileges the ten Fixed Database roles below can do?
-db_datareader -db_datawriter -db_executor (Not a fixed database level role) -db_ddladmin -db_owner -db_denydatareader -db_denydatawriter -db_securityadmin -db_accessadmin -db_backupaperator public Correct answers: db_datareader: These users have permissions to read all data within the database. db_datawriter: These users have permissions to insert, update, or delete from the database db_executor: (Not a fixed database level role) - a blanket permission type of role db_ddladmin: These users have the ability to create, alter, and drop objects from the database db_owner: In addition to having unrestricted access to all objects in the database, these users can execute maintenance and configuration activities on the database. Most importantly, they can also drop the database. Therefore, carefully consider which users should be assigned this role db_denydatareader: These users cannot modify any data within the database db_denydatawriter: are not allowed to add, modify or delete user table data db_securityadmin:These users control role membership. db_accessadmin: can add/remove database access for Windows groups and logins, as well as SQL Server logins db_backupoperator: can perform database backups public: default role for server principals who do not have specific securable object permissions
For quick reference help with MS-DOS commands in any of the versions of MS-DOS, you can use after a command.
/h /help /?] /
How many major types of indexes are there in sql server?
1 2 3 More than 3
20. What is the maximum number of secondary replicas we can configure in a SQL Server 2016 AlwaysOn Availability Group?
10 12 8 4
What is default session timeout period the replicas waits for a ping Response?
15 min 10 sec 10min No time limit
How many types of user defined function are provided by SQL Server?
2 3 4 5
How many types of DML triggers are present in SQL Server?
3 4 5 2
How many modes does Database Mirroring operate in?
4 2 3 5
How many versions down can we change the compatibility level of a given database in a given SQL Server Instance by default?
4 version down 3 version down 5 version down 2 version down
How many versions up can we upgrade SQL Server Instance by default?
4 version up 3 version up 5 version up 2 version up
What is the default time interval in Log Shipping?
45 min 60 min 20 min 15 min
Write the steps to display the database diagram of a given database in SQL Server
> Open SSMS > In Object Explorer, Hit Database folder > Expand the Selected Database > Right-click the Database Diagrams folder > Choose New Database Diagram on the shortcut menu. > Select the required tables in the Tables list and click Add when the Add Table dialog box appears. > The tables will be displayed graphically in the new database diagram.
Write the steps to configure maximum server memory of the SQL SERVER?
> Open SSMS > Right-click on the desired SQL Server instance and click Properties. > In the properties dialog box, click the Memory tab. > Under the Maximum (MB) slider option, move the slider to the desired maximum value. > Click OK to save your changes
Which of the following is a valid global variable?
@@CPU_BUSY @@CPU_IO @@CPU @@I_BUSY
Which of the following global variables has return type 'numeric'?
@@ERROR @@MAX_CONNECTIONS. @@CPU_BUSY @@IDENTITY
Which one of the following files are acceptable files to be an input for Database Engine Tuning Advisor (DTA) or the correct type of workload file in DTA?
A Transact-SQL script file, trace file, or trace table that contains a representative workload for the databases you want to tune Beginning with SQL Server 2072 (1
What is a Unique Constraint in SQL Server?
A UNIQUE constraint is used to avoid duplicate values but accepted null values in a column. It enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints. A table can contain multiple number of UNIQUE constraints The UNIQUE constraint will accept 1 NULL value only. All of the Above
In which of the following modes can the secondary database be set to be in read-only status?
All of the mentioned Restore mode Read only Standby
The following are the three types of Transaction Modes in SQL Server. Which Transaction Mode below will have less level of transaction control and less number of blocking at the same time?
Auto-commit Transaction Explicit Transaction implicit Transaction None of the above
What are the minimum requirements of a database to be part of the Always ON Availability Group?
Availability groups must be created with user databases. Systems databases can't be used. Databases must be read-write. Read-only databases aren't supported, Databases must be multi user databases. All of the Above
Which of the following is not an aggregate function?
Avg Sum Max Min Count aggregate
Which one of the following are true about online index operations?
Bonus Disabled, XML, Columnstore Indexes are not included in online index operations Indexes on local temp tables cannot be created, rebuilt, or dropped online Online index operations require more disk space requirements than offline index 'Operations Online index operations will be slower than equivalent offline index operations regardless of the concurrent update activity level. Large-scale index operations, performed offline or online, can generate large data loads that can cause the transaction log to quickly fill
20. What are the advantages of storing the SSIS packages in an MSDB database rather than a File System?
Bonus SSIS packages will be stored in the MSDB database, in the sysssispackages table (we can find this table in the MSDB database under system tables — msdb.dbo.sysssispackages). We can save the packages in different folders if we want, these folders being virtual folders. Here the SSIS package will not be stored as a physical fite rather it will be on the MSDB database. We can filter the packages using SQL Server queries using the sysssispackages table. Packages can be easily accessed by multiple individuals when saved on SQL Server. It allows to have folders hierarchy and keep track of versions and displays all packages with versions numbers and hierarchy, It is more secure as compared to File System, as it gets the same benefits as Database security and msdb database roles 28_Replication_Quiz_Objective
Which one of the following Index Operations Requires Additional Disk Space?
Bonus: Take time to read and understand each options and mark all the options below ALTER INDEX REBUILD Both CREATE INDEX and CREATE INDEX WITH DROP_EXISTING ALTER TABLE ADD CONSTRAINT {PRIMARY KEY or UNIQUE) ALTER TABLE DROP CONSTRAINT {PRIMARY KEY or UNIQUE) when the constraint is based on a clustered index DROP INDEX MOVE TO (Applies only to clustered indexes)
Which one of the following Index Operations That Require No Additional Disk Space?
Bonus: Take time to read and understand each options and mark all the options below ALTER INDEX REORGANIZE: however, log space is required, DROP INDEX when you are dropping a nonclustered index, DROP INDEX when you are dropping a clustered index offline without specifying the MOVE TO clause and nonclustered indexes do not exist CREATE TABLE (PRIMARY KEY or UNIQUE constraints)
How do you mitigate the performance impacts of an over normalized database?
By Denormalization. It is a technique used on a previously-normalized database to increase the performance. This process involves minimizing the need for joins, reducing the number of tables, and queries to be retrieved can be simpler improve the read statement) at the expense of increasing the redundancy of data
Which of the following can be used for reducing recovery time?
By taking backup on a faster device Taking multiple backups — one in same location, another at different location Using Differential Backup All 4 21_Backup_N_Restore_Quiz_Subjective
is a command used to switch directories in MS-bos.
CD MD RD All of the Above
displays or changes file attributes like reading, archive, system and hidden.
CHANGE ATTRIB FILE MODIFY 1
is a command used to remove FILES from the computer.
CLEAR DEL RD CLS
allows you to delete empty directories.
CLS RD DEL DELETE
11. Which one of the following hardware components is associated with the Licensing of SQL Server Software?
CPU or Processors RAM or Memory Disk or Storage None 2. Who is responsible to verify the server name and the disk size and layout of a database server when commissioning a new server?] Developer DBA System Admin Security Admin
What are SQL Server AlwaysOn Availability Groups?
Choose all that applies AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring An availability group supports a sel of read-write primary databases and one to eight sets of corresponding secondary databases. AlwaysOn Availability Groups minimizes the availability of a set of user databases for an enterprise Secondary databases can be made available for read-only access and/or some backup operations
Which of the following operations is required to run SQL Server Setup on the node?
Choose all that applies Add Node Remove Node Update Node None of the Above
Which one of the below SQL Server Services are not cluster aware services?
Choose all that applies Database Engine service SQL Server integration Service SQL Server Analysis Service SQL Server Reporting Service SQL Server Agent Service
x) Instance using Backup and Restore methodology?
Choose all that applies By upgrading the database to the interim SQL Server 2008 R2 first and then upgrading the database to SQL Server 2016 By upgrading the database to the interim SQL Server 2012 first and then upgrading the database to SQL Server 2016, You can directly upgrade the database from SQL Server 2005 to SQL Server 2016 if Service Pack 4 is applied on SQL Server 2005 source Instance All are possible
What are the Replication agents which involves in transactional replication?
Choose all that applies Log Reader Agent Snapshot Agent Distribution Agent Merge Agent
4, Which of the following are some of the counters available to monitor SQL Server performance related issues?
Choose all that applies Processor related performance counters Memory related performance counters Physical Disk related performance counters Network Interface related performance counters
What are the basics tests done by the validation tests in Windows Cluster?
Choose all that applies Cluster Configuration tests: Validate important cluster configuration settings. System Configuration tests: Validate that the system software and configuration settings are compatible across servers. Network tests: Validate that networks are set up correctly for clustering. Storage tests: Validate that the storage on which the failover cluster depends is behaving correctly and supports the required functions of the cluster.
What is the advantage o f In-Place Upgrade?
Choose all that applies Generally fast overall process Requires no additional hardware and thus less cost Instance remains offline during the upgrade process Complex rollback strategy Connection string remains the same after the upgrade
What is a Monitor server?
Choose all that applies Monitor Server tracks when the transaction log on the primary database was last backed up. Monitor Server tracks Information about any backup failure alerts. Monitor Server tracks when the secondary servers last copied and restored the backup files. Monitor Server supports automatic failover from secondary to primary server
How do you transfer logins of the Primary database from the Primary Server to the Secondary Server?
Choose all that applies Use SQL Server Agent Job to automate the process using the SSIS transfer logins task to accomplish this goal Create a SQL login on the other server manually with the same SID as login on the primary server Create the logins with the same name and SID on the secondary server using something like sp_help_revlogin
Which of the following is the benefit of SQL Server Profiler?
Choose all that applies Capturing the series of Transact-SQL statements that lead to a problem Finding and diagnosing slow-running queries Capturing and solving stow running stored procedure performance. Monitoring the performance of SQL Server to tune workloads
Which of the following is an extended event task?
Choose all that applies Create an Extended Events session Alter an Extended Events session Find out what events are available in the registered packages None of the above
What are the failover types supported by under asynchronous-commit mode?
Choose all that applies Planned Manual Failover Forced Failover Automatic Failover All of the Above
What are the Benefits of Log Shipping?
Choose all that applies The users will experience some downtime. How long depends on how well you implemented log shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over Log shipping doesn't require expensive hardware or software. It is recommended but not required to have the standby server in similar capacity to your production server Log shipping failover is not automatic. The DBA must still manually failover the server, which means the DBA must be present when the failover occurs Once log shipping has been implemented, it is relatively easy to maintain.
What is the user of Witness server?
Choose all that applies The witness is an optional instance of SQL Server that enables the mirror server in a high-safety mode session to recognize whether to initiate an automatic failover Supporting automatic failover is the only rote of the witness. Only Synchronous mode takes advantage of Witness server Must have the same edition and version with Principal and Mirror Instances
What are all the jobs created after configuring the Log Shipping?
Choose all that applies Alert Jobs Restore Job Copy Job Backup Job
What are the Benefits of Database Mirroring?
Choose all that applies Asynchronous mode takes advantage of more bandwidth than synchronous method Has automatic Instance level failover mechanism a Does not require special hardware (such as shared storage, heart-beat connection) and cluster ware, thus potentially has lower infrastructure cost Has built-in network encryption support (AES algorithm) to send transactions from the Principal to the Mirror Instances securely
How do you transfer logins of the Principal database from the Principal Server to the Mirror server?
Choose all that applies Create a sq] login on the other server manually with the same SID as login on the principal server Use SQL Server Agent Job to automate the process using the SSIS transfer logins task or PowerShell script to accomplish this goal Create the logins with the same name and SID on the secondary server using something like sp_help.reviogin
What is the use of a copy job in Log shipping and on which instance it's created?
Choose all that applies Created in Monitor server for every log shipping configuration, Copy the backup files from backup folder into the copy folder On the backup folder we have to grant read permission to the secondary server service account and read-write permissions on the copy folder. Created in Primary server for every log shipping configuration,
In which recovery model Log Shipping cannot be configured?
Choose all that applies Full recovery model. Bulk Logged recovery model Simple recovery model All of the Above
What is the disadvantage o f In-Place Upgrade?
Choose all that applies Generally fast overall process (Requires no additional hardware Instance remains offline during part of upgrade Complex rollback strategy
Which ones of the below Quorum Configurations are the recommended configuration choice for clusters that have an even number of nodes?
Choose all that applies Node and Disk Majority No Majority: Disk Only Node and File Majority Node and Cloud Witness Node Majority
10, What are the Failover types supported by Synchronous-commit mode?
Choose all that applies Planned manual failover Automatic failover Forced failover All of the Above
Which one o f the below reasons is not good enough to migrate or upgrade a database to a target instance o f the same or higher version of the source instance?
Choose all that applies consolidation Application level upgrade without involving a database upgrade Retirement of SQL Server version or when Microsoft extended support is discontinued To separate resource intensive databases and balance workload of a given instance
What are the different ways in upgrading SQL Server Instances t o a higher version?
Choose all that applies. In-Place Upgrade. Windows Patching Side-by-Side Upgrade Script Migration or Deployment
What are the advantages of Side-by-Side migration/upgrade?
Choose all that applies. More granular control over the upgrade process Original database left unchanged; allows for testing of new database Easier back-out plan of the migration didn't go well as planned Requires the commissioning of new server Less down time if proper backup and restore strategy has been implement
In which recovery model can Mirroring be configured?
Choose all that applies. Full recovery model. Bulk Logged recovery model Simple recovery model All of the Above
...which one of the following below is the pros/advantages of the PUSH Subscription model?
Choose one. Everything can be managed from one location. It is much easier to apply changes to distribution agents, monitor and stop/start agents in bulk Slower than Pull when going over WAN If there are multiple agents going through one Distributor you may need to consider a separate box just for remote Distributor (that means additional! costs). It offloads work load from the distributor server
Which three statements describe differences between Storage Area Network (SAN) and Network Attached Storage (NAS) solutions?
Choose three. SAN is generally more expensive but provides higher performance and mostly used for storing database files NAS requires additional hardware on a host: a host bus adapter for connectivity NAS uses TCP/IP for communication between hosts and the NAS server SAN uses proprietary protocols for communication between hosts and the SAN febric Write your names as per the instruction on the Team Rule on the top of your Viber page. 23_Log_shipping_objective
Which one of the following below is the pros/advantages of the PULL Subscription model?
Choose two. Faster when going over WAN. In this case the agent is "downloading" the data instead of sending it. You may need to consider this if data is going over to the other side of the world. The load is moved from Distributor to Subscriber. The agent will be using subscriber resources. Could be important for you if you have multiple agents coming from one Distributor. If Subscriber is really busy and under pressure already it won't be good to get more load by adding a Distribution agent. However one distribution agent won't make a big difference. That could be a different story if we have multiple agents running on that given subscriber. In some cases it could be a security issue. Especially when we have subscribers located in some 3rd party/remote location etc. In that case you will need to open port 1433 for subscribers to your distributor. Distribution agent need access to Distributor.Review: 29_Performance_Part_01_Objective
Which one is true about clustered index?
Clustered index is not associated with table Clustered index is built by default on primary key columns Clustered index can not be built on unique key columns None of the mentioned
Why do we need a group account in SQL Server and who creates the group account in the Active Directory Domain (AD)?
Correct answers: It saves time. It facilitates for the DBA to grant the same kind of privileges for the members of the group. If a new developer gets hired, he/she will be added by the DBA to have the same permission. If an employee quits, the DBA will have to do nothing. Once the Windows account is removed from the active directory, the permission will be removed too. A member of the Domain Administrators group (System Security Admin will create new group accounts in the AD domain. 21_Backup_N_Restore_Objective_02
What are the three types of Transactions available in SQL Server?
Correct answers: Autocommit, Implicit, Explicit
What recovery model would you suggest t o manage the log file of a critical production database having an SSIS package that imports close to one million records every morning?
Correct answers: Bulklog
What is the restore command that helps you to find out the version of a backup file or the version of the host instance of the source database?
Correct answers: Headeronly
What is the reason SQL Server dynamically determines and performs lock escalation?
Correct answers: Lock Escalation is an optimization technique used by SQL Server. The Server dynamically causes Lock Escalation to improve performance or when reducing excessive system lack memory to a more reasonable level is necessary 20_Security_Quiz_Objective (22Q2)
What is the advantage of "nolock" locking hint in SQL Server?
Correct answers: Nolock allows SQL to read data from tables by ignoring any locks and therefore not get blocked by other processes. 1
What is the mandatory restore command that you need to use if you have further restores or backup files that you need to apply before recovering the database for use?
Correct answers: Norecovery
What is the recovery model of the majority of non-production databases in SQL Server?
Correct answers: Simple
What is the advantage of the "readpast" locking hint in SQL Server?
Correct answers: Skips locked rows
What is the restore command that helps to verify the validity of a given backup file?
Correct answers: Verifyonly
Which system database doesn't support differential backup at all?
Correct answers: master
If you want to have a simple recovery model for any newly created databases in a given instance by default, which system database recovery model has to be changed into simple recovery?
Correct answers: model
What is the system database that stores any backup and restore metadata information of SQL Server databases?
Correct answers: msdb
What is the optional backup type that helps to save backup time and backup space and makes the recovery procedure easily manageable?
Correct answers:Differential
What is the best practice to Configure TempDB for Microsoft SQL Server
Create a separate disk volume for tempdb database files Create multiple data files of tempdb database based on the number of cores of the server. Example: Create 8 data files for a server having 8 cores TempDB files need to be sized evenly to avoid page contention. Limit the maximum size of tempdb files leaving 10% free space on the disk volume so that the disk is not going to run out of space due to tempdb database growth All
Which one of the following keyboard combinations helps you copy and paste a selected text, script, file or folder from one location to another location.
Ctri + C then Ctrl+V Ctrl + C then Ctrl + P 1
Which of the following columns in a table cannot be updated without setting cascade update?
DATE type columns in the table Columns which allows Null values in the table A primary key column which also serves as a foreign key reference in another table All of the above 14_Index_Objective_Quiz_Protected
Which of the following system databases holds temporary data?
DBA Master Tempdb Msdb 1
DBCC Command that displays information about the oldest active transaction and the oldest distributed and non-distributed replicated transactions, if any, within the specified database?
DBCC CHECKDB DBCC FREEPROCCACHEQ DBCC OPENTRAN: DBCC TRACEOFF
Which of the following query rebuilds the IX_Employee_OrganizationLevel_OrganizationNode index on the Employee table?
DBCC DBREINDEX ("Employee',UX_Emplayee_OrganizationLevel_OrganizationNode); DBCC REINDEX ("Employee', [X_Employee_OrganizationLevel_OrganizationNode); DBCC DBINDEX ("Employee", IX Employee_OrganizationLevel_OrganizationNode}; All
Which one of the below commands rebuilds all indexes on the Employee table in AdventureWorks by using a fill factor value of 80?
DBCC DBREINDEX {'HumanResources. Employee', ' ', 80}; DBCC DBREINDEX (HumanResources.Employee', ' ', 20); DBCC DBREINDEX (HumanResources.Employee', 'All', 80); DBCC DBREINDEX (HumanResources. Employee', 'All', 20);
Which of the following is used to determine the fragmentation of tables and indexes?
DBCC SHOWCONTIG DBCC CHECKDB BCC CHECKCATALOG None
Which of the following keywords is not associated with initialization and fetching of a value of variable?
DECLARE SET SELECT None of the above
Which of the following statements is true?
DELETE does not free the space containing the table and TRUNCATE free the space containing the table Both DELETE and TRUNCATE free the space containing the table Both DELETE and TRUNCATE does not free the space containing the table DELETE free the space containing the table and TRUNCATE does not free the space containing the table 1
One of the below SQL Server Services is not an Instance feature of SQL Server.
Database Engine Services Analysis Services Reporting Services Integration Services
Collation is applicable only for the following two services of SQL Server Services
Database Engine Services & Integration Services Database Engine Services & Reporting Services Database Engine Services & Analysis Services Analysis Services & Integration Services
What is the available tool we have in SQL Server that we can leverage before Instance upgrade?
Database Engine Tuning Advisor SQL Server Profiler SQL Server Configuration Manager SQL Server Upgrade Advisor
What is a Database Diagram and what is the advantage of it?
Database diagrams graphically show the structure of the database and relations between database objects. Database Diagrams illustrate some or all of the tables, columns, keys, and relationships in it. It is used to visualize a database. Using database diagrams you can create and modify tables, columns, relationships, and keys, Additionally, you can modify indexes and constraints.
Which of the following transfer methods makes the source database unavailable to users during the copy process of the database to another Instance?
Detach and attach method SQL Management Object (SMO) method only Backup and Restore method None of the mentioned
Which of the following transfer methods makes the source database unavailable to users during the transfer?
Detach and attach method SQL Management Object method (SMO} Restore method None of the mentioned
Why do we mainly need to have a good or restorable backup at any given point of time?
Disaster recovery For migration and upgrade Database refresh-the most frequent task for a DBA All of the Above
What do you use to display the relationship diagram of a given database in SQL Server?
Erwin Visio Database Diagram Toad for oracle Entity Relationship Diagram/ER Diagram
What Editions of SQL Server can support SQL Server clustering setup in a Production Environment?
Express Edition Enterprise Edition Standard Edition Developer Edition
Which recovery model does not support point in time recovery or Transaction Log backup?
Full Recovery Model Bulk Recovery Model Simple Recovery Model None
Types of DB backups that are not supported on Secondary Replicas?
Full backups Copy-only full backups Differential backups Files, or filegroups.
In which of the following recovery models of a database a DBA cannot perform a transaction log backup?
Full recovery model Bulk logged recovery model Simple recovery model All the above
Create a SQL account named, User_Insrt_N_Slct under AdventureWorks2016 database with the db_Insert_Select database level role you created above. Correct answers: USE [master
GO CREATE LOGIN [User.InsrtN_Sket] WITH PASSWORD=N'Admin123", DEFAULT_DATABASE= [AdventureWorks2016}, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE {AdventureWorks2016] GO CREATE USER: [User_Insrt_N_Slet] FOR LOGIN [User_Insrt_N_Slet] GO USE [AdventureWorks2016] GO ALTER ROLE (db_Insert Select} ADD MEMBER [User_Insrt_N_Sict] GO USE [AdventureWorks2016] GO ALTER ROLE [dlb_owner] ADD MEMBER [User_Insrt_N_slet] GO
Which one of the following is not a DCL (Data Control Language) command?
Grant Privilege Revoke Deny 07_2_DB_Design_Quiz_Objective
What is true about indexes?
Indexes enhance the performance even if the table is updated frequently It makes it harder for SQL Server engine to work on index which have large keys It doesn't make it harder for SQL Server engine to work on index which have large keys None of the above
What type of join is needed when you wish to return rows that do have matching values?
Inner Join Self Join Outer join All
The following SQL is which type of join: SELECT CUSTOMER_T. CUSTOMER_ID, ORDER_T. CUSTOMER_ID, NAME, ORDER_ID FROM CUSTOMER_T,ORDER_T?
Inner Join Self Join Outer join Cross Join or Cartesian Join
What type of join is needed when you wish to include rows that do not have matching values?
Inner Join Self Join Outer join Cross Join or Cartesian Join
Which is not a type of join in T-SQL?
Inner-join Natural join Outer join Cross Join or Cartesian Join
The instructions that tell the computer what to do and how to do it.
Malware Hardware Firmware Software
What is the start mode of a functioning SQL Server Instance Services in SQL Server Cluster?
Manual Automatic Disabled
Do indexes take space on the disk?
No, it is stored in memory and are used when required Yes, Indexes are stored on disk Indexes are stored in tempdb Indexes don't take space at all
Which ones of the below Quorum Configurations is the recommended configuration choice for clusters that have an odd number of nodes?
Node and File Majority Node Majority No Majority: Disk Only Node and Disk Majority Node and cloud Witness
What is over normalization and explain how over normalization causes performance issues?
Over normalization can be referred to as normalization past the third Normal Form, Over normalization implies increasing the number of joins required in a query between related tables. No data redundancy, Joins are necessary. So, Data retrieval/ reading becomes slower.
Which of the following is not the best practice related to SSIS?
Plan for capacity by understanding resource utilization SSIS is an in-memory pipeline, so ensure that all transformations occur in memory For the SQL job that calls the SSIS packages, make multiple steps, each doing small tasks, rather than a single step doing all the tasks Keep it simple and avoid using components unnecessarily.
How many types of constraints are present in SQL Server?
Please exclude properties. 4 5 6 7 1
What is the major difference between primary data files and secondary data files?
Please research. While Primary Data File, - contains startup information for the database - points to the other files in the database - every database has one primary data file - file name extension is .mdf Secondary Data File on the other hand, - is optional user-defined data files. - can be spread across multiple disks by putting each file on a different disk drive. -file name extension is .ndf 1
Which of the following is the instance of SQL Server that your production database resides in log Shipping configuration?
Primary database Secondary server Primary server Secondary database
We have a system file group and user file groups (optional) in SQL Server. What is the name of the system file group in any given database?
Primary file group 1
Which one of the below constraints can be enforced one per table?
Primary key constraint Not Null constraint Foreign Key constraint Check constraint 1
filename' T-SQL Command None 20. How do you split or stripe a backup file of a database into an equal smaller number of files?
Provide multiple backup files in your backup T-SQL statement Use Stripe to Disk='Path
20. What type of testing are available at the application end to verify the functionality of the application after a successful database and/or application upgrade?
QA testing Smoke testing Regression testing Manual and automatic testing All of the above
5. WHICH ONE IS THE FOLLOWING IS THE MAIN OBJECT OF A DATABASE?
RDBMS VIEW TABLE TRIGGER
Which o f the following returns the version of the Instance the backup file has come from?
RESTORE VERIFYONLY RESTORE HEADERONLY RESTORE FILELISTONLY All Of The mentioned
To decide on a backup strategy for your organization, which of the following should you consider?
RPO (Recovery Point Objective) RTO (Recovery Time Objective) Both RPO & RTO % None of the mentioned
RAM Stands for
Random Average Memory Random Access Memory Random Accessory Memory Really Awesome Memory
Which one of the following is a large object data type or a large value data type?
Read about the LOB data types varchar(max) and nvarchar(max) image, varbinary(max) text and ntext xml data types all of the above
Which restore option will help you replace an existing database with a new database having the same database name?
Replace Init Noinit Norecovery
Which backup option will help you to overwrite an existing backup file with the new backup file if the backups are using the same backup name?
Replace Init noinit Recovery
A DBA wants to restore a backup file to a different server with different disk layouts. What restore options can the DBA choose relevant to Relocating the files?
Replace Stopat Move All the above 1
The following database(s) will be created after Installing and Configuring Reporting Services.
ReportServer ReportServerTempDB ReportServer & ReportServerTempDB None 1
What restore command can be used to check the validity of a backup?
Restore Verifyonly Restore Headeronly Restore Filelistonly All 1
What restore command can be used to check the source data location of both the data and log file of a backup?
Restore Verifyonly Restore Headeronly Restore Filelistonly All 1
What restore command can be used to check the version of a backup?
Restore Verifyonly Restore Headeronly Restore Filelistonly All 1
Which one of the below recovery procedures works if a production database with full, differential and transaction log backups crashed in the middle of the day?
Restore last full backup, latest differential backup and all the subsequent log backups after the latest differential backup sequentially Restore last full backup and all the subsequent log backups after the last full backup sequentially Restore last full backup, all the subsequent differential backups sequentially and all the subsequent log backups after the latest differential backup sequentially All of the above 1
Which of the following recovery states during the restore a database can be chosen to make the database read only?
Restore with Recovery Restore With Norecovery Restore With StandBy All the above 1
A Table is primarily made up of?
Rows and Indexes Pages and Columns Rows and Columns Indexes and Column
Which of the following is the biggest disadvantage of SQL Server Authentication made?
SQL Server Authentication cannot use Kerberos security protocol Allows SQL Server to support Web-based applications where users create their own identities Allows SQL Server to support older applications and applications provided by third parties that require SQL Server Authentication None of the mentioned.
Which one of the following is not true in SQL Server Multi-Subnet Clustering (SQL Server)?
SQL Server multi-subnet failover cluster is a configuration where each failover cluster node is connected to a different subnet or different set of subnets. These subnets can be in the same location or in geographically dispersed sites and Clustering across geographically dispersed sites is sometimes referred to as stretch clusters. A multi-subnet failover cluster, there is no shared storage that all the nodes can access, data should be replicated between the data storage on the multiple subnets, with data replication, there is more than one copy of the data available. A multi-subnet failover cluster provides a disaster recovery solution in addition to high availability. None of the above 26_AlwaysOn_Quiz_Objective (22Q2)
01_Intro to SQL Server_Objective 2. Which one of the following editions have all the features of Microsoft SQL Server?
STANDARD EDITION EXPRESS EDITION ENTERPRISE EDITION
Select * from employee where salary > 10000 and dept_id=101: Which of the following fields are displayed as an output?
Salary, dept_id Employee Salary All the field of the employee table
What is the use of the SCHEMABINDING option in a VIEW?
Schemabinding binds your views to the dependent base table and avoids having orphan views in a database These types of view are defined by users on a specified schema These are used to show database self describing information None 16_Variables_N_Cursors__Objective
What is the most and main responsibility of SQL server DBA?
Security Server configuration Backup and Recovery Writing queries
Which of the following transaction isolation levels are available in SQL standard?
Serializable & Repeatable Read Read Committed & Read Uncommitted Read Committed Snapshot & Snapshot Isolation All of the mentioned
Which of the following are Ticketing or Incident applications that are used to address issues?
Servicenow Footprints Remedy All
Full database backup is supported in which of the following recovery models?
Simple Full Bulk logged All the above 1
Under what recovery model a DBA can perform a transaction log backup?
Simple and Full Full and Bulk logged Bulk logged and Simple All
What are the available recovery models in a database?
Simple recovery model Full recovery model Bulk logged recovery model All of the above .
In which of the following recovery model transaction logs backup is not applicable?
Simple recovery model Full recovery model Bulk logged recovery model None of the Above
Launch a tool to check for conditions that prevent a successful SQL Server Installation?
System Configuration Checker Download Upgrade Advisor Hardware and Software Requirement None
Which of the following does not return a table variable?
System-defined function Scalar Function Inline Table-Valued Function Multi-Statement Table-Valued Function
T-SQL COMMAND that helps to modify a database or a database object?
TCL COMMANDS (Transaction Control Language) DDL COMMANDS (Data Definition Language) DML COMMANDS (Data Manipulation Language) DCL COMMANDS (Data Control Language)
10. MICROSOFT SQL SERVER SUPPORTS ONLY OLTP DATABASES?
TRUE FALSE
Does Log Shipping support automatic failover?
TRUE FALSE
Does the secondary instance need to be licensed?
TRUE FALSE
Insert into employee (1002,'Joy',2000): In the given query which of the keywords below has to be used in the blank space above?
Table Values Relation Fred 11_Constraints_Objective_Protected
What does the ALTER TABLE clause do?
The ALTER TABLE clause is used to insert data into database table The ALTER TABLE clause modifies a table definition by altering, adding, or deleting table columns and/or constraints The ALTER TABLE deletes data from database table The ALTER TABLE clause is used to delete a database table
What is CHECK Constraint in SQL Server?
The CHECK constraint is used to enforce domain integrity In simple words, we can say that Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of possible values Check constraints allow us to define an expression for a TABLE that must not evaluate to FALSE for a data modification statement to succeed. All of the Above
Why is SQL Server Consolidation Better Than Having a Scattered Environment?
There is less administration work in SQL Server, Windows, and hardware It becomes easier to monitor the whole infrastructure. Significant savings in Windows and SQL Server licensing and hardware costs. |Helps to gain savings in electricity and rack space. All Of the above
Which of the following is the most visible benefit of a user-defined function?
They allow modular programming They allow faster execution They can reduce network traffic. All of the mentioned 1
Does Mirroring support automatic failover?
True FALSE
Can we use other databases in SQL Server cluster Instance for reporting purposes as we can do in Log shipping?
True False
Can we use a secondary replica for reporting purposes?
True Fale
Can a publication support push and pull at one time?
True False
Can we take Transaction log backups on the secondary replicas?
True False
Does AG (Availability Groups) use a Windows cluster on the infrastructure side?
True False
Does replication encrypt data?
True False
SQL Server Agent is completely dependent on its respective SQL Server Engine.
True False
How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?
UPDATE Persons SET LastName='Hansen' INTO LastName='Nilsen' MODIFY Persons SET LastName='Nilsen' WHERE LastName='Hansen' MODIFY Persons SET LastName="Hansen' INTO LastName='Nilsen' UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'
Why using SELECT * in an SQL Query is a Bad Practice?
Unnecessary IO or retrieving unnecessary data that will just be ignored. Increased Network Traffic - SELECT * returns more data than required to the client, which in turn will use more network bandwidth. This increase in network bandwidth also means that data will take a longer time to reach the client application, which could be SSMS or your Java application server More Memory - due to this increase in data, your application may require more memory just to hold unnecessary data that will not be used All 09_2_DML_N_DDL_QUIZ_Objective
What types of database maintenance below should be executed regularly?
Update SQL Server's usage Validate the database objects and system catalogs are free of corruption Determine and maintain the fragmentation for tables and indexes All of the mentioned
How can you add a missing feature to an existing installation of SQL Server Instance?
Use ]New SQL Server stand-alone installation or add features to an existing installation New SQL Server failover cluster installation Add node to a SQL Server failover cluster Upgrade from a previous version of SQL Server
As a senior DBA, how do you find the backup location of any given database on any server?
Use the default backup location of the given Instance from Instance Properties window Query the system tables of msdb database Communicate with the other DBA in the company Search for *.bak files on the backup drives of the server
Which of the following procedures are created by the user for their own actions?
User Defined Stored Procedure Extended Procedure CLR Stored Procedure All of the mentioned 1
Which of the following stored procedures is already defined in a new empty shell Database in SQL Server?
User Defined Stored Procedure Extended Procedure CLR Stored Procedure System Defined Procedure 1
Where would be the location of a newly created table if you don't specify the filegroup of the table upon creation?
User Filegroup System Filegroup Primary Filegroup Default Filegroup
Which of the following conditions in the where clause will return the login identification name of the user?
UserName = SUSER_NAME() UserName = SUSER_SNAME() UserName = CURRENT_USER() UserName = USER() 1
How do you manage weekly, daily or hourly routine backups in SQL Server?
Using T-SQL Scripts in SQL Server Agent Jobs Using PowerShell Scripts in SQL Server Agent Jobs Using SQL Server Native Maintenance Plan Using Ola Hallengren Maintenance Plan All 21_Backup_N_Restore_Objective_01
Which one of the below options is not the preferred way of copying a database?
Using the Copy Database Wizard Restoring a database backup Using the Generate Scripts Wizard to publish databases Using Detach and Attach
How do you monitor in Mirroring?
Using.... Database Mirroring Monitor SQL Server Log Profiler Performance Monitor DMVs and System Stored Procedures All of the above
What are the benefits of using AG?
What are the advantages of AG when compared to mirroring & Clustering AG does not need shared storage, unlike FCI. So the customer needs to invest in expensive SAN to achieve this requirement. Also, the shared disk is the single point of failure. AG uses a Listener to route traffic to the Primary Server and handles the failover Process. For the application, this failover is transparent. Unlike in Database Mirroring, where the "Failover Partner" name needs to be mentioned in the connection string. Else, the failover process is manual. AG can have 8 secondary replicas and they can be read-only. in database mirroring, you can only have 1 Primary and 1 secondary where the secondary cannot be accessed. The only around it was to take a Database snapshot which is an Enterprise Only feature All of the above
Can we configure Availability Groups without configuring Availability Group Listeners?
Yes No
4. Where do you find the service account that you need to use when you make a new installation of SQL Server?
You need to request a service account created by the system admins Log into similar installed servers and find out the service account used Ask the lead DBA or DBA manager or another DBA in the team All
What is the default port of a SQL Server default Instance?
]1522 1434 1433 . 1722
What is the port number used by SQL Server Browser Service?
]1722 1433 1434 1522
Which one of the following is false about bit and byte?
]A bit is a single binary digit that can represent 0 or 1 A bit is considered to be the smallest unit of data measurement. A byte is 8 binary digits working together to represent a number or a character or a special symbol and 1 Byte equals to 8 Bits. Bits are used to measure internet speed (data rate) whereas Bytes are used to measure data storage. When data is transmitted over a network medium, it is typically written in bits/s, kilobits/s(kbps), megabits/s (Mbps), or gigabits/s (Gbps). None of the above Quiz 2_Windows 10 Quizzes_Objective
WHERE DO MOST ENTERPRISE COMPANIES KEEP OR SAFEGUARD THEIR PRODUCTION SERVERS?
]BRANCH OFFICE DATA CENTER HEAD OFFICE LOCAL SERVER
Which one of the following hardware components is associated with the Licensing of SQL Server Software?
]CPU or Processors RAM or Memory Disk or Storage None 02_SQLServer Pre-Installation_Objective
20. Which one of the services below is used to access NAMED SQL Server Instances?
]Database Engine Service SQL Server Reporting Services SQL Server Analysis Services SQL Server Integration Services. SQL Server Browser Services SQL Server Agent Services
Which one of the services below is used to manage OLAP databases?
]Database Engine Service SQL Server Reporting Services SQL Server Analysis Services . SQL Server Integration Services SQL Server Browser Services SQL Server Agent Services
Which one of the services below is considered as the best friend of a DBA and helps to automate routine tasks such as database backups and maintenance plans?
]Database Engine Service SQL Server Reporting Services SQL Server Integration Services SQL Server Analysis Services SQL Server Browser Services SQL Server Agent Services
Who is responsible to verify the server name and the disk size and layout of a database server when commissioning a new server?
]Developer DBA System Admin Security Admin
WHICH OF THE FOLLOWING FREE EDITION HAS ALL THE FEATURES OF ENTERPRISE EDITION BUT NOT LICENSED TO BE USED IN A PRODUCTION ENVIRONMENT?
]EVALUATION EDITION EXPRESS EDITION DEVELOPER EDITION NONE
Which one of the following is an alternative name for a USB drive?
]Flash drive/USB Flash drive Pen drive/USB Pen drive Memory stick Thumb drive/ USB thumb drive All 1
What is the main function of the motherboard?
]It acts as the brains of a computer It allows a computer to display video It ties together all the functions of a computer and manages and directs the flow of data between each of the components It processes the instructions given to a computer 1
Which one of the following below that the DBA needs to verify before Installing SQL Server Instance?
]Local Admin Privilege Username and password of the service account The minimum hardware and the software requirement The collation of the Instance All
WHICH ONE IS THE FOLLOWING IS THE MAIN OBJECT OF A DATABASE?
]RDBMS VIEW TABLE TRIGGER
Which one of the following is not a Business Intelligent (Bl) Service?
]SQL Server integration Services (SSIS) SQL Server Engine Services SQL Server Analysis Services (SSAS) SQL Server Reporting Services (SSRS)
What is the default collation of SQL Server Engine?
]SQL_Latin1_General_CP1_IC_AS SQL_Latini_General_CP1_CI_SA SQL_Latin1_General_CP1_CI_AS SQL_Latin1_General_CP1_Cl_AB
Which one of the following editions have all the features of Microsoft SQL Server?
]STANDARD EDITION EXPRESS EDITION ENTERPRISE EDITION
Which SQL Server Network Protocols enables SQL Server Instance to be available over the Internet or WAN (Wide Area Network)?
]TCP/IP Named Pipes Shared Memory All
MICROSOFT SQL SERVER SUPPORTS ONLY OLTP DATABASES?
]TRUE FALSE
Why does Microsoft recommend having a separate disk volume for data file, log file and backup file of a database?
]To improve performance To improve recoverability of the database in case of disaster Both
Why do you need a service account when you install SQL Server Instance?
]To manage resources external to the server where SQL Server Instance has been installed Local Service account is limited to the resources inside the server SQL Server jobs can access locations outside of the SQL Server host machine All
Where do you find the setup file of a licensed sql server software before the installation?
]Will be available in the NAS location if the setup file has already been downloaded before You can download it from microsoft if the company has MSDN subscription Ask the lead DBA or DBA manager or another DBA or System Admin if there is a particular location where setup files are stored All
What are the two types of Authentication Modes in SQL Server?
]Windows Authentication & Mixed Mode Authentication Mixed Mode (SQL Server authentication and Windows authentication)
Where do you find the service account that you need to use when you make a new installation of SQL Server?
]You need to request a service account created by the system admins Log into similar installed servers and find out the service account used Ask the lead DBA or DBA manager or another DBA in the team All
Which of the following is a single-tasking Operating System?
]a. MS DOS b. MS Windows 95/98, c. UNIX d. Utility Software
The bar across the top of the window that displays the program name is the?
]a. Menu bar B. App bar C. Title bar 1
What is a Microsoft technology that enables employees in an organization to access information across organizational and geographic boundaries?
]a. SharePoint B. Microsoft Edge C. Cortana
What has the ability to drag windows to the edges or corners of your screen and have them snap into place?
]a. Virtual desktop B. Snap Assist C. The Windows Journal
What is the Dos command that helps you to display the name of the server you are currently working on?
]hostname whoami servername compname 01_Intro to SQL Server_Objective
You are the new IT admin, and you need to find the network configuration. What should you use?
]ipconfig cmd netchange newnet 1
Which of the following system databases requires constant cleanup to delete jobs related metadata information and maintain the size of the database?
]master msdb model resource tempdb
If you wanted to ping a host but also follow the path at which it pings, what would you use?
]tracert ping tracert /ping ipconfig /most 1
The script that used to bring a database named "Finprod" from restoring to online state is?
__ Restore Database [Finprod] With Norecovery Restore Database [Finprod] With Recovery Restore Database [Finprod] With Restore Restore the last backup of the database with Recovery option
A folder within another folder
a. File B. Sub folder C. Platform 1
When you save a file, it is permanently saved on the ....
a. Hard drive b. Cloud storage c. RAM d. USB Flash drive, Pen drive, Memory stick, USB thumb drive e. All except C
Turning off your computer in a manner that closes all open programs and files, close your network connections, stops the hard disk, and discontinues the use of electrical power is called:
a. Hibernate B. Sleep C. Shutdown D. Restart
An underlying computer system on which application programs can run
a. Live Tiles B. Operating System C. Platform 1
A list of installed programs on the left and a customization group of app tiles on the right
a. Live Tiles B. Windows tO C. Start Menu
A sequence of folders (directories) that leads to a specific file or folder
a. Microsoft Edge B. Path C. Platform
A free file storage and file sharing service provided by Microsoft when you sign up for a Microsoft account
a. OneDrive B. GoogleDrive C. iCloud
An area of Windows 10 where you can manipulate some of the Windows 10 basic system settings such as managing users or uninstalling software
a. Path B. Control Panel C. Dashboard 1
An operating system developed by Microsoft Corporation
a. Platform
A program included with Windows 10 that enables you to capture an image of all or part or a computer screen
a. Snipping Tool B. Cortana C. Microsoft Edge
An Operating System like Microsoft Windows or Linux is categorized as:
a. System Software b. Application Software c. Programming Software d. Malware
The browser program that comes with Windows 10
a. Windows 10 B. Operating System C. Microsoft Edge 1
Which of the following options can be used to launch the Windows Monitoring Tool, Perfmon?
choose all that applies Type "start perfmon" at the command prompt Go to Start | Programs | Administrative Tools | Performance Monitor Go to Start | Run | Perfmon None of the above
What is the critical responsibility of a DBA in a company?
choose the main one Secure the Databases Make sure to have a valid backup in case of Disaster Recovery Installation of SQL SERVER Upgrade and Migration
Which of the data types has a storage size of 8 bytes?
datetime bigint money uniqueidentifier All except uniqueidentifier
Which of the following system database doesn't need to have a database backup?
master model msdb tempdb
Which one of the following two system databases are mostly considered while performing side by side Instance level migration or upgrade?
master msdb model tempdb 22_Upgrade_N_Migration_Objective_02
The only backup type that we take using "NORECOVERY?
option is Differential Backup Full backup Transaction Backup Tail log Backup
This command sends a request out and expects a response, indicating that both hosts are communicating.
ping tracert ipconfig /renew nslookup 1
records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings
resource master msdb model model
read-only database that contains all the system objects that are included with SQL Server such as system objects.
resource msdb model tempdb master Quiz 5 1_DB_Design_Form_Subjective Write your full names in Upper Case separated by semicolons.. Correct Answers List out all the post installation configuration steps before using a newly installed instance in a production environment. ]
The physical components of a computer are the ; the digital programs running on the hardware are the
software; hardware hardware; software flashware; software access codes; systemware 1
Which of the following stored procedures is used to add a primary database on the primary server?
sp_add_tog_shipping_primary sp.add_log_shipping sp_add_log shippingprimarydatabase sp_add_primary_database
Which of the following stored procedures is used to show the database object details or properties?
sp_helpSysObject 'Object' sp_help 'object' sp_helpUserObject 'Object' All 1
Which of the following determines the type or resource to which the lock applies?
sp_lock sp_who Sp_monitor sp_spaceused
Which of the following stored procedure reports snapshot Information about the current SQL Server users activities?
sp_lock sp_who Sp_monitor sp_user
Which of the following stored procedures will stop the profiler trace if you provide the trace status value as zero?
sp_trace_create sp_trace_setfilter sp_trace_setstatus sp_stop_status
Which of the following stored procedure provide replication support for AlwaysOn?
sp_validate_replica_publishers sp_validate_hosts_as_publishers sp_validate_replica_hosts_as_publishers . all of the mentioned
How do you identify blocking transactions in SQL Server?
sp_who2 System Stored Procedure and Activity Monitor Using DMVs such as sys.dm_exec_requests & sys.dm_os_waiting_tasks SQL Server Profiler and SSMS Reports All of the above
Which of the following catalog views is used for SQL Server Extended Events?
sys.server_sessions sys.server_event_sess sys.server_event_session_actions all of the mentioned