Online Test Knowledge of SQL Server 2008 Skills

Online Test Knowledge of SQL Server 2008 Skills


What is the default timeout for login to sqlcmd while you try to connect to a server?

  1. 5 seconds
  2. 8 seconds
  3. 10 seconds
  4. 15 seconds
  5. 1 minute
Answer: Answer is b
 0 Comments

Analyze the following permissions:

1. ALTER permission on the database.
2. CONTROL permission in the database.
3. ALTER ANY SCHEMA permission and CREATE XML SCHEMA COLLECTION permission in the database
Which of the above permissions is/are required to create an XML SCHEMA COLLECTION?
  1. 1 and 2
  2. 2 and 3
  3. 1 and 3
  4. 1 or 2 or 3
  5. 1 and 2 and 3
Answer: Answer is e
Further Reading:
 0 Comments

All MDX, DMX and XML/A errors are displayed in the Error List window of the SQL Server 2008. Management Studio?

  1. True
  2. False
Answer: Answer is a
Further Reading:

What is the name of the default instance installed by SQL Server 2008 Express in your computer?

  1. Sqlinst
  2. Sqlcmd
  3. Sqlexpress
  4. sqldef
Answer: Answer is c
Further Reading:

Which of the following options is NOT supported by the ON DELETE clause of a FOREIGN KEY constraint of SQL Server 2008?

  1. CASCADE
  2. DELETE
  3. SET NULL
  4. SET DEFAULT
Answer: Answer is b
Further Reading:

Which of the following options is used to change the value of the password variable and exit using sqlcmd?

  1. –p
  2. –P
  3. –z
  4. -Z
Answer: Answer is d
Further Reading:
Useful resources, sqlcmd Utility

In which of the following statements can TOP clause be used in SQL Server 2008?

  1. Only SELECT
  2. Only SELECT and INSERT
  3. Only SELECT, INSERT, UPDATE, DELETE
  4. Only SELECT, INSERT, UPDATE, MERGE, DELETE
Answer: Answer is d
Further Reading:
Useful resources, TOP

A local temporary stored procedure name can contain a maximum of __________ characters

  1. 58
  2. 116
  3. 128
  4. 512
Answer: Answer is b
Further Reading:
The complete name for a stored procedure or a global temporary stored procedure, including ##, cannot exceed 128 characters. The complete name for a local temporary stored procedure, including #, cannot exceed 116 characters. Useful resources, CREATE PROCEDURE

Which of the following is a valid code to remove the key used to encrypt a database using Transparent Data Encryption algorithm?

  1. ALTER DATABASE DROP ENCRYPTION KEY
  2. DROP DATABASE ENCRYPTION KEY
  3. ALTER DATABASE REMOVE ENCRYPTION KEY
  4. REMOVE DATABASE ENCRYPTION KEY
Answer: Answer is b
Further Reading:

Which of the following commands is used to start the Resource Governor in SQL Server 2008?

  1. ALTER Resource Governor Enable
  2. ALTER Resource Governor Reconfigure
  3. ALTER Resource Governor Set
  4. None of the above
Answer: Answer is b
Further Reading:
Useful resources, ALTER RESOURCE GOVERNOR


Which of the following is a valid code to change the index created on a column of a table to page compression?

  1. ALTER INDEX Index_Product ON Table1 WITH ( DATA_COMPRESSION = PAGE ) GO
  2. ALTER INDEX Index_Product ON Table1 SET ( DATA_COMPRESSION = PAGE ) GO
  3. ALTER INDEX Index_Product ON Table1 REBUILD WITH ( DATA_COMPRESSION = PAGE ) GO
  4. ALTER INDEX Index_Product ON Table1 REBUILD SET ( DATA_COMPRESSION = PAGE ) GO
Answer: Answer is c
Further Reading:
 0 Comments

Analyze the following code which uses the TOP clause of SQL Server 2008:

USE Person 
GO 
DECLARE @p AS int 
SELECT @p=10 
SELECT TOP(@p) 
FROM Employee 
GO
What Will be the output of the above code?
  1. No output is displayed.
  2. A list of first 10 employees is displayed from the Person database.
  3. An incorrect syntax error is displayed.
  4. None of the above.
Answer: Answer is c
 0 Comments

Which of the following features of SQL Server 2008 evaluates servers for compliance with a set of predefined conditions and prevents making undesirable changes to servers?

  1. Data Compression
  2. Resource Governor
  3. Policy-Based Management
  4. Auditing
Answer: Answer is c
Further Reading:
Policy-Based Management enables the efficient management of multiple SQL Server instances from a single location. It is easy to create policies that control security, database options, object naming conventions, and other settings at a highly granular level. Such policies can evaluate servers for compliance with a set of predefined conditions and help prevent undesirable. Useful resources, Why SQL Server May Be More Suitable For You
 0 Comments

What is the default value of the SORT_IN_TEMPDB clause of the relational_index_option specified at the time of creation of an index on a table in SQL Server 2008?

  1. ON
  2. OFF
  3. TRUE
  4. FALSE
Answer: Answer is b
Further Reading:
Useful resources, Create Index (Transact-SQL)

Which of the following scripting options are available while creating a script using the shortcut menu from the Object Explorer in SQL Server 2008 Management Studio?

  1. DELETE To
  2. MERGE To
  3. CREATE INDEX To
  4. INSERT To
  5. UPDATE To
Answer: Answer is a, d, e

Which of the following statements are true about FILESTREAM storage in SQL Server 2008?

  1. Transact-SQL can be used to SELECT, INSERT, UPDATE, DELETE FILESTREAM data.
  2. The size of the stored data is limited to 2 GB.
  3. You cannot take backup of a database without the FILESTREAM data.
  4. FILESTREAM storage is best used when the BLOB file sizes average 1MB or higher.
Answer: Answer is a, d
Further Reading:
The FILESTREAM data can be accessed and manipulated in two ways – either with the standard Transact-SQL programming model, or via the Win32 streaming APIs. Both mechanisms are fully transaction-aware and support most DML operations including insert, update, delete, and select.BLOBs smaller than 256 kilobytes (KB) are better stored inside a database, and BLOBs larger than 1 megabyte (MB) are best stored outside the database. For those sized between 256 KB and 1 MB, the more efficient storage solution depends on the read vs. write ratio of the data, and the rate of “overwrite”. Storing BLOB data solely within the database (e.g., using the varbinary (max) data type) is limited to 2 gigabytes (GB) per BLOB. Useful resources, FILESTREAM Storage in SQL Server 2008

Which of the following encryption algorithms are used by the Transparent Data Encryption security mechanism of SQL Server 2008?

  1. Tiny Encryption Algorithm
  2. Advanced Encryption Standard(AES)
  3. Blowfish
  4. Triple Data Encryption Standard(3DES)
Answer: Answer is b, d
Further Reading:

Which of the following queries is used to rename a database in SQL Server 2008?

  1. sp_renamedb oldname,newname
  2. sp_renamedb newname,oldname
  3. sp_dbrename oldname,newname
  4. sp_rename oldname,newname
Answer: Answer is a
Further Reading:

Which of the following statements are true?

  1. IGNORE_DUP_KEY cannot be set to ON for XML indexes.
  2. IGNORE_DUP_KEY can be set to ON for spatial indexes.
  3. IGNORE_DUP_KEY can be set to ON for filtered indexes.
  4. IGNORE_DUP_KEY can be set to ON for XML indexes.
  5. IGNORE_DUP_KEY cannot be set to ON for indexes created on a view.
Answer: Answer is a, e
Further Reading:
IGNORE_DUP_KEY cannot be set to ON for indexes created on a view, non-unique indexes, XML indexes, spatial indexes, and filtered indexes. Useful resources, Create Index

The login timeout when you try to connect to a server must be a number between __________.

  1. 0 and 66020
  2. 1 and 65530
  3. 0 and 65534
  4. 1 and 65535
Answer: Answer is c
Further Reading:
The login time-out must be a number between 0 and 65534. Useful resources, ssbdiagnose Utility

Which of the following is a valid code to delete a clustered index with a PRIMARY KEY constraint in SQL Server 2008?

  1. ALTER TABLE Production.ProductCost DROP CONSTRAINT PK_Product WITH (ONLINE = ON) GO
  2. ALTER TABLE Production.ProductCostHistory DROP CONSTRAINT PK_Product DROP INDEX Index_Product WITH (ONLINE = ON) GO
  3. DROP INDEX Index_Product ON Production.ProductCostGO
Answer: Answer is c
Further Reading:
Useful resources, Dropping Indexes
 0 Comments

Which of the following spatial data types are used in SQL Server 2008?

  1. Geography
  2. Longitude
  3. Altitude
  4. Geometry
  5. Latitude
Answer: Answer is a, d
Further Reading:
Useful resources, spatial data types

Which of the following permissions are required to create a view in SQL Server 2008?

  1. CREATE VIEW permission in database
  2. ALTER permission on schema
  3. CONTROL permission on object
Answer: Answer is a, b
Further Reading:
Create a view requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created.Useful resources, CREATE VIEW

Which of the following statements are true regarding row and page compression in SQL Server 2008?

  1. When a table or index is created, data compression is set to NONE, unless otherwise specified.
  2. Nonclustered indexes inherit the compression property of the table
  3. New pages allocated in a heap as part of DML operations will not use PAGE compression until the heap is rebuilt.
  4. When you are compressing indexes, leaf-level pages can be compressed with only page compression.
Answer: Answer is a, c
Further Reading:
Useful resources,Data Compression
 0 Comments

Which of the following features is used by SQL Server Audit to create and manage audits in SQL Server 2008?

  1. System stored procedures
  2. Triggers
  3. SQL DDL syntax
  4. None of the above
Answer: Answer is b
 0 Comments

Which of the following security features have been introduced in SQL Server 2008?

  1. Extensible Key Management
  2. SOAP Security
  3. Hashing
  4. Transparent Data Encryption
Answer: Answer is a, d
Further Reading:
Useful resources, Security

Multiple CHECK constraints can be applied to a single column and a single CHECK constraint can be applied to multiple columns in SQL Server 2008.

  1. True
  2. False
Answer: Answer is a
Further Reading:
Useful resources,CHECK Constraints
 0 Comments

Which of the following is a valid syntax of the ALTER SERVER AUDIT statement used to enable a server audit?

  1. ALTER SERVER AUDIT <audit_name> WITH (ENABLE=TRUE)
  2. ALTER SERVER AUDIT <audit_name> SET (ENABLE=TRUE)
  3. ALTER SERVER AUDIT <audit_name> WITH (STATE=ON)
  4. ALTER SERVER AUDIT <audit_name> SET (STATE=ON)
Answer: Answer is c
Further Reading:
Useful resources, alter server audit

Which of the following default passwords is used by sqlcmd if -P option is used at the end of the command prompt without a password?

  1. 0000
  2. NULL
  3. 1234
  4. None of the above
Answer: Answer is b
Further Reading:
If the -P option is used at the end of the command prompt without a password sqlcmd uses the default password (NULL). Useful resources, sqlcmd utility

Which data type of SQL Server 2008 converts itself into the type of data that is inserted into it?

  1. Real
  2. Rowversion
  3. type_var
  4. sql_variant
  5. monetary
Answer: Answer is d
Further Reading:
Useful resources, sql_variant



Which of the following rules must be followed for combining the result sets of two queries by using UNION?

  1. The number of columns must be same in all queries.
  2. The order of columns must be same in all queries.
  3. The data types of the columns of the queries must be compatible.
  4. There should be at least one common column in the tables which are part of the UNION operation.
Answer: Answer is a, b, c
Further Reading:
Useful resources, union
 0 Comments

Windows Security log can be specified as a target for Audit in SQL Server 2008.

  1. True
  2. False
Answer: Answer is a
Further Reading:
Windows Security log can be specified as a target for Audit (starting with the Windows Server® 2003 operating system).Useful resources,Auditing in SQL Server 2008
 0 Comments

What is the default precision of the date data type in SQL Server 2008?

  1. 4 digits
  2. 6 digits
  3. 8 digits
  4. 10 digits
Answer: Answer is d
Further Reading:
Useful resources, date data type
 0 Comments

What range is supported by the TIME data type in SQL Server 2008?

  1. 00:00:00.0000000 through 23:59:59.9999999
  2. 00:00:00.0000000 through 24:00:00.0000000
  3. 00:00:00.0000000 through 23:59:00.0000000
  4. 00:00:00.0000000 through 23:59:59.0000000
Answer: Answer is a
Further Reading:
Useful resources, time

Which of the following evaluation modes of Policy-Based Management CANNOT be automated in SQL Server 2008?

  1. On demand
  2. On change: prevent
  3. On change: log only
  4. On schedule
Answer: Answer is a
Further Reading:
Useful resources,Policy-Based Management
 0 Comments

Which of the following Audit objects primarily describes the destination where the audit data of SQL Server 2008 gets stored?

  1. Server Audit object
  2. Server Audit Specification object
  3. Database Audit Specification object
Answer: Answer is a
Further Reading:
Useful resources,Auditing in SQL Server 2008

Which of the following is the default authentication mode for sqlcmd in SQL Server 2008?

  1. Windows Authentication
  2. SQL Server Authentication
  3. IIS Authentication
  4. Digest Authentication
Answer: Answer is a
Further Reading:
Windows Authentication is the default authentication mode for sqlcmd. To use SQL Server Authentication, you must specify a user name and password by using the -U and -P options. Useful resources,Start the sqlcmd Utility, sqlcmd utility

Suppose e1 and e2 denote two decimal expressions with precisions p1 and p2 and scales s1 and s2, respectively. What is the result precision of the following operation on the two expressions?

Operation: e1 / e2
  1. p1 - s1 + s2 + max(6, s1 + p2 + 1)
  2. max(s1, s2) + max(p1 - s1, p2 - s2) + 1
  3. p1 + p2 + 1
Answer: Answer is a
Further Reading:

PRIMARY KEY constraint defaults to __________ index

  1. Clustered
  2. Nonclustered
  3. None of the above
Answer: Answer is a
Further Reading:

Which of the following formats are supported by the language_term argument while altering a full-text stoplist in SQL Server 2008?

  1. Integer
  2. Binary
  3. Decimal
  4. Hexadecimal
Answer: Answer is a, d
Further Reading:
language_term can be specified as a string, integer, or hexadecimal value corresponding to the locale identifier (LCID) of the language Useful resources, alter fulltext stoplist



Which of the following data types can be specified as SPARSE?

  1. Image
  2. Text
  3. Nchar
  4. geography
Answer: Answer is c
Further Reading:
Useful resources,sparse

What should a computed column be marked as so that SQL Server Database Engine can physically store values in a table?

  1. ON
  2. WRITE
  3. PERSISTED
Answer: Answer is c
Further Reading:
The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Useful resources, computed columns

The Server Audit object and the Server Audit Specification object of SQL Server 2008 share a __________ relationship.

  1. One-to-one
  2. One-to-many
  3. Many-to-one
  4. Many-to-many
Answer: Answer is a
Further Reading:
Useful resources, Auditing in SQL Server 2008

Which of the following statements is NOT true about the UNIQUE constraint of SQL Server 2008?

  1. Multiple UNIQUE constraints can be defined on a table.
  2. When using the UNIQUE constraint, only one null value is allowed per column.
  3. UNIQUE constraint cannot be referenced by a FOREIGN KEY constraint
Answer: Answer is c
Further Reading:
Useful resources,Unique Constraints
 0 Comments

For which of the following data types can a COLLATE clause be applied?

  1. Text
  2. Int
  3. Varchar
  4. bigint
Answer: Answer is a, c
Further Reading:
Useful resources, collate

What is the maximum value for the sum of the values of MIN_CPU_PERCENT and MIN_MEMORY_PERCENT for all the resource pools of SQL Server?

  1. 50
  2. 75
  3. 100
  4. 150
  5. 200
Answer: Answer is c
Further Reading:
Useful resources,resource pool

What is the default maximum precision of decimal data type in SQL Server 2008?

  1. 16
  2. 18
  3. 38
  4. 48
Answer: Answer is c

Which of the following statements using GROUP BY clause are allowed in SQL Server 2008?

  1. SELECT Column1 + Column2 FROM Table GROUP BY Column1, Column2
  2. SELECT Column1, Column2 FROM Table GROUP BY Column1 + Column2
  3. SELECT Column1 + constant + Column2 FROM Table GROUP BY Column1 + Column2
  4. SELECT Column1 + Column2 FROM Table GROUP BY Column1 + Column2
Answer: Answer is a, d
Further Reading:
Useful resources,GROUP BY

What happens to the audit records if a failure to write the Audit event does NOT trigger the SQL Server instance to shut down?

  1. The server blocks any new activity to be performed to the SQL Server instance.
  2. Audit events are buffered in memory until they can be flushed to the target.
  3. The audit is automatically disabled
  4. An error is displayed.
Answer: Answer is b
Further Reading:
Useful resources, Auditing in SQL Server 2008

What is the default precision of the TIME data type in SQL Server 2008?

  1. 5 digits
  2. 6 digits
  3. 7 digits
  4. 10 digits
Answer: Answer is c
Further Reading:
Useful resources, time


Which of the following values specifies that each instance of the XML data type in column_name can contain multiple top-level elements?

  1. DEFAULT
  2. CONTENT
  3. DOCUMENT
  4. FILESTREAM
Answer: Answer is b
Further Reading:
Useful resources, columns
 0 Comments

Analyze the following code using wild card characters of SQL Server 2008:

SELECT Name FROM person.employees WHERE Name LIKE '[_]n' GO
Which of the following values will be returned by the above code?
  1. In
  2. Sin
  3. _n
  4. All of the above
Answer: Answer is a
Further Reading:
Useful resources, LIKE

Which of the following stored procedures is used to delete data from the management data warehouse of a Data Collector?

  1. core.sp_delete_data
  2. core.sp_purge_data
  3. core.sp_remove_data
  4. None of the above
Answer: Answer is b
Further Reading:
Useful resources, Managing Data Collection
 0 Comments

Which function/functions are performed by the following statement?

DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)
  1. It checks the current iDentity fielD value of the specifieD table.
  2. It sets the identity field value to the new reseed value.
  3. It sets the identity field value to 1.
  4. All of the above.
Answer: Answer is a, b
Further Reading:
Useful resources, DBCC CHECKIDENT
 0 Comments

How many groupings are generated when CUBE is used with the GROUP BY clause of SQL Server 2008?

  1. n (n= number of expressions in the element list passed to CUBE)
  2. n+1 (n= number of expressions in the element list passed to CUBE)
  3. 2^n (n= number of expressions in the element list passed to CUBE)
  4. 2^n+1 (n= number of expressions in the element list passed to CUBE)
Answer: Answer is c
Further Reading:
Useful resources, GROUP BY

The regular identifiers of SQL Server 2008 may contain a range of _____________ characters

  1. 1 to 116
  2. 1 to 127
  3. 1 to 128
  4. 1 to 512
Answer: Answer is c
Further Reading:
Useful resources, Identifiers

If the characters 'D', 'C', 'B', 'A' are placed in a queue (in that order) and then removed one at a time, in what order will they be removed?

  1. ABCD
  2. ABDC
  3. DCAB
  4. DCBA
Answer: Answer is a

Which of these are standard operations of Stack Data Structure?

  1. Push, delete
  2. Insert, pop
  3. Put, extract
  4. Push, pop
Answer: Answer is d
Further Reading:
Useful resources, Stack

How many real links are required for a sparse matrix having 10 rows, 10 columns and 15 non-zero elements? (Pick the nearest answer)

  1. 15
  2. 20
  3. 50
  4. 100
Answer: Answer is c

Which graph traversal algorithm uses a queue to keep track of the vertices which need to be processed?

  1. Breadth-first search
  2. Depth-first search
Answer: Answer is a


Which of the following statements are true?

  1. With DDL, you can create and remove tables, schemas, domains, indexes and views.
  2. SELECT, INSERT and UPDATE are DCL commands.
  3. GRANT and REVOKE are DML commands.
  4. COMMIT and ROLLBACK are DCL commands.
Answer: Answer is a
Further Reading:
 0 Comments

What is the function of DBCC CHECKDB command in SQL Server 2008?

  1. It checks the allocation of all the objects in the specified database.
  2. It checks the structural integrity of all the objects in the specified database.
  3. It checks for consistency in and between system tables in the specified database.
  4. It checks the consistency of disk space allocation structures of a specified database.
Answer: Answer is a, b, d
Further Reading:
DBCC CHECKALLOC - Checks the consistency of disk space allocation structures for a specified database.
DBCC CHECKTABLE- Checks the integrity of all the pages and structures that make up the table or indexed view.
DBCC CHECKCATALOG-Checks for catalog consistency within the specified database. The database must be online.
Useful resources,DBCC CHECKDB, DBCC CHECKDB
Share this article :
Labels:

+ comments + 1 comments

Reply
jefrin
April 1, 2019 at 3:47 PM

Hey, would you mind if I share your blog with my twitter group? There’s a lot of folks that I think would enjoy your content. Please let me know. Thank you.
RPA Course Training in Chennai
AWS Training in Chennai | Best AWS Training in Chennai
Data Science Training in Chennai | Best Data Science Training in Chennai
No.1 Python Training in Chennai | Best Python Training in Chennai

Post a Comment

 
Copyright © 2014. Mominul's Diary - All Rights Reserved
Developed by Mominul Islam