oDesk Test answers for Knowledge of SQL Server 2008 Skills Test 2015

99 Answered Test Questions:

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

Answers:

• SELECT Column1 + Column2 FROM Table GROUP BY Column1, Column2

• SELECT Column1, Column2 FROM Table GROUP BY Column1 + Column2

• SELECT Column1 + constant + Column2 FROM Table GROUP BY Column1 + Column2

• SELECT Column1 + Column2 FROM Table GROUP BY Column1 + Column2

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

Answers:

• It checks the allocation of all the objects in the specified database.

• It checks the structural integrity of all the objects in the specified database.

• It checks for consistency in and between system tables in the specified database.

• It checks the consistency of disk space allocation structures of a specified database.

3. Which of the following compression features have been introduced in SQL Server 2008 that were NOT supported by SQL Server 2005?

Answers:

• Row-level compression

• Page-level compression

• Data file level compression

• Backup level compression

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

Answers:

• The number of columns must be same in all queries.

• The order of columns must be same in all queries.

• The data types of the columns of the queries must be compatible.

• There should be at least one common column in the tables which are part of the UNION operation.

5. 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?

Answers:

• DELETE To

• MERGE To

• CREATE INDEX To

• INSERT To

• UPDATE To

6. Which of the following options are available for the DATA_COMPRESSION clause specified for a table in SQL Server 2008?

Answers:

• Page

• Table

• Row

• Column

7. Which of the following methods can be specified for the lock escalation of a table in SQL Server 2008?

Answers:

• ENABLE

• AUTO

• DISABLE

• TABLE

• DEFAULT

8. Which of the following data types are supported by SQL Server 2008?

Answers:

• date

• datetime

• datetime1

• datetime2

• datetimeoffset

9. Which of the following statements are true?

Answers:

• IGNORE_DUP_KEY cannot be set to ON for XML indexes.

• IGNORE_DUP_KEY can be set to ON for spatial indexes.

• IGNORE_DUP_KEY can be set to ON for filtered indexes.

• IGNORE_DUP_KEY can be set to ON for XML indexes.

• IGNORE_DUP_KEY cannot be set to ON for indexes created on a view.

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

Answers:

• When a table or index is created, data compression is set to NONE, unless otherwise specified.

• Nonclustered indexes inherit the compression property of the table.

• New pages allocated in a heap as part of DML operations will not use PAGE compression until the heap is rebuilt.

• When you are compressing indexes, leaf-level pages can be compressed with only page compression.

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

Answers:

• Extensible Key Management

• SOAP Security

• Hashing

• Transparent Data Encryption

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

Answers:

• Tiny Encryption Algorithm

• Advanced Encryption Standard(AES)

• Blowfish

• Triple Data Encryption Standard(3DES)

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

Answers:

• Integer

• Binary

• Decimal

• Hexadecimal

14. Which of the following are valid DATETIME functions in SQL Server 2008?

Answers:

• SYSDATE()

• SYSTIME()

• SYSDATETIME()

• SYSUTCDATETIME()

15. Which of the following counters are used to monitor compression of the whole instance of SQL Server?

Answers:

• Page compression attempts/sec

• Row compression/sec

• Pages compressed/min

• Pages compressed/sec

• Rows compressed/min

16. Which of the following statements are true about the FILESTREAM argument of SQL Server 2008?

Answers:

• If a table contains FILESTREAM data and the table is partitioned, the FILESTREAM_ON clause must be included.

• The FILESTREAM column can be partitioned even if a table is not partitioned.

• There may not be a FILESTREAM filegroup if a table is not partitioned and the FILESTREAM_ON clause is not specified.

• The filegroup in the FILESTREAM_ON <filegroup>> clause must have one file defined for the filegroup.

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

Answers:

• Geography

• Longitude

• Altitude

• Geometry

• Latitude

18. Which function/functions are performed by the following statement?

DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)

Answers:

• It checks the current iDentity fielD value of the specifieD table.

• It sets the identity field value to the new reseed value.

• It sets the identity field value to 1.

• All of the above.

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

Answers:

• Transact-SQL can be used to SELECT, INSERT, UPDATE, DELETE FILESTREAM data.

• The size of the stored data is limited to 2 GB.

• You cannot take backup of a database without the FILESTREAM data.

• FILESTREAM storage is best used when the BLOB file sizes average 1MB or higher.

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

Answers:

• CREATE VIEW permission in database

• ALTER permission on schema

• CONTROL permission on object

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

Answers:

• text

• int

• varchar

• bigint

22. Columns marked with which of the following constraints/properties allow null values to be inserted in them?

Answers:

• PRIMARY KEY

• UNIQUE

• IDENTITY

• FOREIGN KEY

23. Analyze the following code used to update a Common Table Expression:

USE demodb
GO
DECLARE @a TABLE (ID int, Value int);
DECLARE @b TABLE (ID int, Value int);
INSERT @a VALUES (1, 10), (2, 20);
INSERT @b VALUES (1, 100),(2, 200);
WITH cte AS (SELECT * FROM @a)
UPDATE cte
SET Value = b.Value
FROM cte AS a
INNER JOIN @b AS b ON b.ID = a.ID
SELECT * FROM @a
GO

What Will be the output of the above code?

Answers:

• ID Value ----- ----- 1 100 2 200

• ID Value ----- ----- 1 10 2 100

• ID Value ----- ----- 1 10 2 200

• ID Value ----- ----- 1 100 2 100

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

Answers:

• One-to-one

• One-to-many

• Many-to-one

• Many-to-many

25. 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?

Answers:

• 50

• 75

• 100

• 150

• 200

26. An identifier denoting a global temporary object in SQL Server 2008 starts with a ______ sign.

Answers:

• At sign(@)

• Double number(##)

• Underscore(_)

• None of the above

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

Answers:

• System stored procedures

• Triggers

• SQL DDL syntax

• None of the above

28. What is returned by NULLIF if the two expressions that it compares are NOT equal?

Answers:

• It returns the first expression.

• It returns the second expression.

• It returns a null value.

• It returns an error.

29. User defined data type columns can be designated with ROWGUIDCOL.

Answers:

• True

• False

30. Which of the following commands is used to save the output to a text file after you run a Transact-SQL script using sqlcmd?

Answers:

• sqlcmd -S instanceName -o C:\new.txt

• sqlcmd -S instanceName -i C:\myScript.sql -o C:\new.txt

• sqlcmd -S myServer\instanceName -o C:\new.txt

• sqlcmd -S myServer\instanceName -i C:\myScript.sql -o C:\new.txt

31. Analyze the following code snippet:

DECLARE @myTime TIME(4) = '02:02:02.234567 +01:01'
SELECT @myTime AS '@myTime'

What is the output of the above code?

Answers:

• 02:02:02.2345

• 02:02:02

• 02:02:02.2346

• An error is displayed

32. Which of the following is used by SQL Server 2008 Management Studio to execute scripts in regular mode in the Query Editor?

Answers:

• sqlcmd

• Microsoft .NET Framework SqlClient

• OLE DB provider

• None of the above

33. 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?

Answers:

• ON

• OFF

• TRUE

• FALSE

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

Answers:

• Multiple UNIQUE constraints can be defined on a table.

• When using the UNIQUE constraint, only one null value is allowed per column.

• UNIQUE constraint cannot be referenced by a FOREIGN KEY constraint

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

Answers:

• -p

• -P

• -z

• -Z

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

Answers:

• 1 to 116

• 1 to 127

• 1 to 128

• 1 to 512

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

Answers:

• On demand

• On change: prevent

• On change: log only

• On schedule

38. Which of the following statements is true if you create a global temporary table in SQL Server 2008 and specify a FOREIGN KEY with the CREATE TABLE command?

Answers:

• The table is created successfully with the FOREIGN KEY constraint.

• The table is not created and no message is returned.

• The table is created without the FOREIGN KEY constraint and a warning message is returned.

• The table is not created and an error message is displayed.

39. 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?

Answers:

• Data Compression

• Resource Governor

• Policy-Based Management

• Auditing

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

Answers:

• n (n= number of expressions in the element list passed to CUBE)

• n+1 (n= number of expressions in the element list passed to CUBE)

• 2^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)

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

Answers:

• Server Audit object

• Server Audit Specification object

• Database Audit Specification object

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

Answers:

• True

• False

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

Answers:

• ALTER TABLE Production.ProductCost DROP CONSTRAINT PK_Product WITH (ONLINE = ON) GO

• ALTER TABLE Production.ProductCostHistory DROP CONSTRAINT PK_Product DROP INDEX Index_Product WITH (ONLINE = ON) GO

• DROP INDEX Index_Product ON Production.ProductCost GO

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

Answers:

• 5 seconds

• 8 seconds

• 10 seconds

• 15 seconds

• 1 minute

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

Answers:

• 4 digits

• 6 digits

• 8 digits

• 10 digits

46. What amount of storage is required by a date variable in SQL Server 2008?

Answers:

• 3 bytes

• 5 bytes

• 6 bytes

• 8 bytes

47. PRIMARY KEY constraint defaults to __________ index.

Answers:

• Clustered

• Nonclustered

• None of the above

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

Answers:

• ALTER INDEX Index_Product ON Table1 WITH ( DATA_COMPRESSION = PAGE ) GO

• ALTER INDEX Index_Product ON Table1 SET ( DATA_COMPRESSION = PAGE ) GO

• ALTER INDEX Index_Product ON Table1 REBUILD WITH ( DATA_COMPRESSION = PAGE ) GO

• ALTER INDEX Index_Product ON Table1 REBUILD SET ( DATA_COMPRESSION = PAGE ) GO

49. What is the return type of the value returned by @@DATEFIRST in SQL Server 2008?

Answers:

• datetime

• date

• int

• tinyint

50. Which of the following features of SQL Server 2008 enables you to limit the memory used by incoming application requests?

Answers:

• Hot Add CPU

• Dynamic management views

• Resource Governor

• Backup Compression

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

Answers:

• real

• rowversion

• type_var

• sql_variant

• monetary

52. Analyze the following command:

-o "C:\Folder\<file_name>"

Which of the following statements is true if a file with the file name specified in the above command already exists?

Answers:

• An error is generated.

• The file is overwritten.

• None of the above.

53. 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.

Answers:

• True

• False

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

Answers:

• 58

• 116

• 128

• 512

55. 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?

Answers:

• in

• sin

• _n

• All of the above

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

Answers:

• Windows Authentication

• SQL Server Authentication

• IIS Authentication

• Digest Authentication

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

Answers:

• True

• False

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

Answers:

• core.sp_delete_data

• core.sp_purge_data

• core.sp_remove_data

• None of the above

59. What is the maximum number of processors that can be used in parallel plan execution using the MAXDOP option in SQL Server 2008?

Answers:

• 32 processors

• 50 processors

• 64 processors

• 75 processors

60. Which of the following arguments of the fn_get_audit_file() function of SQL Server 2008 specifies the directory indicating the location and audit file set to be read?

Answers:

• Initial_file_name

• File_pattern

• Audit_file_offset

• None of the above

61. Analyze the following code snippet:

DECLARE @myDate DATETIME2
SELECT @myDate = '2009/10/08 12:35:29.2348 +12:15'
SELECT @myDate AS '@myDate'

What is the output of the above code?

Answers:

• 2009-10-08 12:35:29

• 2009/10/08 12:35:29.2348000

• 2009-10-08 12:35:29.2348000

• 2009/10/08 12:35:29

62. What exactly is Full-Text Catalog in SQL Server 2008?

Answers:

• The Full-Text Catalog is an index for searching specific keywords.

• The Full-Text Catalog is a file which contains full-text indexes.

• The Full-Text Catalog is an inbuilt function for validating text data.

• The Full-Text Catalog is a system trigger for checking text validation.

63. Which of the following clauses of SQL Server 2008 avoids enforcing a constraint when repeated inserts are performed?

Answers:

• IDENTITY

• SPARSE

• NOT FOR REPLICATION

• COLLATE

64. Which of the following roles is required to connect to a Central Management Server?

Answers:

• ServerGroupReaderRole

• ServerGroupAdministratorRole

• PolicyAdministratorRole

• db_ssisoperator

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

Answers:

• 00:00:00.0000000 through 23:59:59.9999999

• 00:00:00.0000000 through 24:00:00.0000000

• 00:00:00.0000000 through 23:59:00.0000000

• 00:00:00.0000000 through 23:59:59.0000000

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

Answers:

• ALTER SERVER AUDIT <audit_name> WITH (ENABLE=TRUE)

• ALTER SERVER AUDIT <audit_name> SET (ENABLE=TRUE)

• ALTER SERVER AUDIT <audit_name> WITH (STATE=ON)

• ALTER SERVER AUDIT <audit_name> SET (STATE=ON)

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

Answers:

• sqlinst

• sqlcmd

• sqlexpress

• sqldef

68. 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?

Answers:

• 1 and 2

• 2 and 3

• 1 and 3

• 1 or 2 or 3

• 1 and 2 and 3

69. Which of the following statements is true regarding indexes in SQL Server 2008?

Answers:

• Modifications made in a table will be faster if the number of indexes is more.

• Modifications made in a table will be slower if the number of indexes is more.

• The number of indexes does not affect the modification process of a table.

70. How many audit action groups are available for a server in SQL Server 2008 to perform different actions on audit data?

Answers:

• 25

• 30

• 35

• 40

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

Answers:

• image

• text

• nchar

• geography

72. Analyze the following code snippet for creating a stored procedure in SQL Server 2008:

CREATE PROCEDURE Person.GetEmployees
@LastName nvarchar(50),
@FirstName nvarchar(50)
AS
SET NOCOUNT ON
SELECT FirstName, LastName, JobTitle, Department
FROM Person.EmployeeDepartment
WHERE FirstName = @FirstName AND LastName = @LastName
GO

Which of the following is a valid code to execute the GetEmployees stored procedure?

Answers:

• EXECUTE Person.GetEmployees N'John', N'Miller'

• EXEC Person.GetEmployees @LastName = N'Miller', @FirstName = N'John'

• EXECUTE Person.GetEmployees @FirstName = N'John', @LastName = N'Miller'

• All of the above

73. Starting from the lowest, arrange the following operators in the order in which they produce results in an SQL WHERE query.

1. >, >=, <, <=
2. <>
3. =
4. LIKE

Answers:

• 1,4,3,2

• 4,1,2,3

• 3,2,1,4

• 2,4,1,3

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

Answers:

• 16

• 18

• 38

• 48

75. What is the maximum number of columns that can be combined into a single composite index key?

Answers:

• 8

• 16

• 32

• 64

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

Answers:

• DEFAULT

• CONTENT

• DOCUMENT

• FILESTREAM

77. Which of the following is the correct order of steps to be followed while using the Transparent Data Encryption security feature of SQL Server 2008?

Answers:

• 1. Create a database encryption key. 2. Create or obtain a certificate protected by the encryption key. 3. Create a master key and protect it by the certificate. 4. Set the database to use encryption.

• 1. Create a database encryption key. 2. Create a master key. 3. Create or obtain a certificate protected by the master key. 4. Set the database to use encryption.

• 1. Create a master key. 2. Create or obtain a certificate protected by the master key. 3. Create a database encryption key and protect it by the certificate. 4. Set the database to use encryption.

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

Answers:

• ALTER Resource Governor Enable

• ALTER Resource Governor Reconfigure

• ALTER Resource Governor Set

• None of the above

79. Which of the following commands is used to connect to a named instance of SQL Server using the sqlcmd utility of SQL Server?

Answers:

• sqlcmd -C myServer\instanceName

• sqlcmd -S instanceName

• sqlcmd -C instanceName

• sqlcmd -S myServer\instanceName

80. Which of the following commands is used to run a Transact-SQL script file by using sqlcmd?

Answers:

• sqlcmd -S myServer\instanceName -i C:\newScript.sql

• sqlcmd -S instanceName -o C:\newScript.sql

• sqlcmd -S myServer\instanceName -r C:\newScript.sql

• sqlcmd -S instanceName -i C:\newScript.sql

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

Answers:

• 5 digits

• 6 digits

• 7 digits

• 10 digits

82. Which of the following permissions is required to drop a stored procedure in SQL Server 2008?

Answers:

• SELECT permission on the schema

• ALTER permission on the schema

• CONTROL permission on the procedure

• Both a and b

• Either b or c

• Both b and c

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

Answers:

• 0 and 66020

• 1 and 65530

• 0 and 65534

• 1 and 65535

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

Answers:

• Only SELECT

• Only SELECT and INSERT

• Only SELECT, INSERT, UPDATE, DELETE

• Only SELECT, INSERT, UPDATE, MERGE, DELETE

85. 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?

Answers:

• 0000

• NULL

• 1234

• None of the above

86. Which of the following namespaces is used to manage the audit configuration programmatically in SQL Server 2008?

Answers:

• Microsoft.SqlServer.Management.Smo

• Microsoft.SqlServer.Server

• Microsoft.sqlserver.management.common

• Microsoft.sqlserver.connectioninfo

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

Answers:

• The server blocks any new activity to be performed to the SQL Server instance.

• Audit events are buffered in memory until they can be flushed to the target.

• The audit is automatically disabled.

• An error is displayed.

88. Which of the following code snippets causes only partition number 1 to be rebuilt while changing the compression of a partitioned table?

Answers:

• ALTER TABLE Table1 REBUILD WITH (DATA_COMPRESSION = PAGE)

• ALTER TABLE Table1 REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = NONE) GO

• ALTER TABLE Table1 REBUILD PARTITION ALL WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) )

• All of the above

89. Analyze the following data types and schemas:

1.The SQL Server system data type.
2.The default schema of the current user in the current database.
3.The dbo schema in the current database.

In which order does the SQL Server Database Engine refer to type_name when a type_schema_name is NOT specified while creating a table in SQL Server 2008?

Answers:

• 3,2,1

• 2,3,1

• 3,1,2

• 1,2,3

• Any order

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

Answers:

• CASCADE

• DELETE

• SET NULL

• SET DEFAULT

91. Suppose you create a stored procedure and save it in the database with "sp_" prefix. Which of the following statements is true regarding the execution of the stored procedure?

Answers:

• The prefix will speed up the execution of the stored procedure.

• The prefix will slow down the execution of the stored procedure.

• The performance of the stored procedure is not affected.

92. Each table in SQL Server 2008 can have up to ______ nonclustered indexes.

Answers:

• 777

• 888

• 999

• 1000

93. 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?

Answers:

• No output is displayed.

• A list of first 10 employees is displayed from the Person database.

• An incorrect syntax error is displayed.

• None of the above.

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

Answers:

• ALTER DATABASE DROP ENCRYPTION KEY

• DROP DATABASE ENCRYPTION KEY

• ALTER DATABASE REMOVE ENCRYPTION KEY

• REMOVE DATABASE ENCRYPTION KEY

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

Answers:

• ON

• WRITE

• PERSISTED

96. The output of a Transact SQL query using the GROUPING function of SQL Server 2008 is of __________ return type.

Answers:

• varchar

• boolean

• int

• tinyint

97. What is the data type of the parameter "Audit_file_offset" of function "fn_get_audit_file()" in SQL Server 2008?

Answers:

• varchar

• nvarchar

• int

• bigint

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

Answers:

• sp_renamedb oldname,newname

• sp_renamedb newname,oldname

• sp_dbrename oldname,newname

• sp_rename oldname,newname

99. Which of the following is the correct precedence order of the data types in SQL Server 2008?

1.xml
2.text
3.bigint
4.image
5.float
6.datetime

Answers:

• 1,2,3,4,5,6

• 6,5,4,3,2,1

• 1,6,5,3,2,4

• 1,4,2,3,6,5

SHARE

Blogger

• 
• 
• 
• 
•