The Server Audit object and the Server Audit Specification object of SQL Server 2008 share a __________ relationship.
Answers: • One-to-one
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: • 200
An identifier denoting a global temporary object in SQL Server 2008 starts with a ______ sign.
Answers: • Double number(##)
Which of the following features is used by SQL Server Audit to create and manage audits in SQL Server 2008?
Answers: • Triggers
What is returned by NULLIF if the two expressions that it compares are NOT equal?
Answers: • It returns the first expression.
User defined data type columns can be designated with ROWGUIDCOL.
Answers: • False
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 myServer\instanceName -i C:\myScript.sql -o C:\new.txt
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.2346
Which of the following is used by SQL Server 2008 Management Studio to execute scripts in regular mode in the Query Editor?
Answers: • Microsoft .NET Framework SqlClient
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: • OFF
Which of the following statements is NOT true about the UNIQUE constraint of SQL Server 2008?
Answers: • UNIQUE constraint cannot be referenced by a FOREIGN KEY constraint
Which of the following options is used to change the value of the password variable and exit using sqlcmd?
Answers: • -P
The regular identifiers of SQL Server 2008 may contain a range of _____________ characters.
Answers: • 1 to 128
Which of the following evaluation modes of Policy-Based Management CANNOT be automated in SQL Server 2008?
Answers: • On demand
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 without the FOREIGN KEY constraint and a warning message is returned.
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: • Policy-Based Management
How many groupings are generated when CUBE is used with the GROUP BY clause of SQL Server 2008?
Answers: • 2^n (n= number of expressions in the element list passed to CUBE)
Which of the following Audit objects primarily describes the destination where the audit data of SQL Server 2008 gets stored?
Answers: • Server Audit object
Windows Security log can be specified as a target for Audit in SQL Server 2008.
Answers: • True
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
What is the default timeout for login to sqlcmd while you try to connect to a server?
Answers: • 8 seconds
What is the default precision of the date data type in SQL Server 2008?
Answers: • 10 digits
What amount of storage is required by a date variable in SQL Server 2008?
Answers: • 3 bytes
PRIMARY KEY constraint defaults to __________ index.
Answers: • Clustered
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 REBUILD WITH ( DATA_COMPRESSION = PAGE ) GO
What is the return type of the value returned by @@DATEFIRST in SQL Server 2008?
Answers: • tinyint
Which of the following features of SQL Server 2008 enables you to limit the memory used by incoming application requests?
Answers: • Resource Governor
Which data type of SQL Server 2008 converts itself into the type of data that is inserted into it?
Answers: • sql_variant
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.
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
A local temporary stored procedure name can contain a maximum of __________ characters.
Answers: • 116
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: • _n
Which of the following is the default authentication mode for sqlcmd in SQL Server 2008?
Answers: • Windows Authentication
All MDX, DMX and XML/A errors are displayed in the Error List window of the SQL Server 2008. Management Studio.
Answers: • True
Which of the following stored procedures is used to delete data from the management data warehouse of a Data Collector?
Answers: • core.sp_purge_data
What is the maximum number of processors that can be used in parallel plan execution using the MAXDOP option in SQL Server 2008?
Answers: • 64 processors
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: • File_pattern
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.2348000
What exactly is Full-Text Catalog in SQL Server 2008?
Answers: • The Full-Text Catalog is a file which contains full-text indexes.
Which of the following clauses of SQL Server 2008 avoids enforcing a constraint when repeated inserts are performed?
Answers: • IDENTITY
Which of the following roles is required to connect to a Central Management Server?
Answers: • ServerGroupReaderRole
What range is supported by the TIME data type in SQL Server 2008?
Answers: • 00:00:00.0000000 through 23:59:59.9999999
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 (STATE=ON)
What is the name of the default instance installed by SQL Server 2008 Express in your computer?
Answers: • sqlexpress
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 and 3
Which of the following statements is true regarding indexes in SQL Server 2008?
Answers: • Modifications made in a table will be slower if the number of indexes is more.
How many audit action groups are available for a server in SQL Server 2008 to perform different actions on audit data?
Answers: • 40
Which of the following data types can be specified as SPARSE?
Answers: • nchar
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: • All of the above
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: • 4,1,2,3
What is the default maximum precision of decimal data type in SQL Server 2008?
Answers: • 18
What is the maximum number of columns that can be combined into a single composite index key?
Answers: • 16
Which of the following values specifies that each instance of the XML data type in column_name can contain multiple top-level elements?
Answers: • CONTENT
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 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.
Which of the following commands is used to start the Resource Governor in SQL Server 2008?
Answers: • ALTER Resource Governor Reconfigure
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 -S myServer\instanceName
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
What is the default precision of the TIME data type in SQL Server 2008?
Answers: • 7 digits
Which of the following permissions is required to drop a stored procedure in SQL Server 2008?
Answers: • Either b or c
The login timeout when you try to connect to a server must be a number between __________.
Answers: • 0 and 65534
In which of the following statements can TOP clause be used in SQL Server 2008?
Answers: • Only SELECT, INSERT, UPDATE, MERGE, DELETE
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: • NULL
Which of the following namespaces is used to manage the audit configuration programmatically in SQL Server 2008?
Answers: • Microsoft.SqlServer.Management.Smo
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: • Audit events are buffered in memory until they can be flushed to the target.
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 PARTITION = 1 WITH (DATA_COMPRESSION = NONE) GO
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: • 1,2,3
Which of the following options is NOT supported by the ON DELETE clause of a FOREIGN KEY constraint of SQL Server 2008?
Answers: • DELETE
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 performance of the stored procedure is not affected.
Each table in SQL Server 2008 can have up to ______ nonclustered indexes.
Answers: • 999
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: • An incorrect syntax error is displayed.
Which of the following is a valid code to remove the key used to encrypt a database using Transparent Data Encryption algorithm?
Answers: • DROP 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: • PERSISTED
The output of a Transact SQL query using the GROUPING function of SQL Server 2008 is of __________ return type.
Answers: • tinyint
What is the data type of the parameter "Audit_file_offset" of function "fn_get_audit_file()" in SQL Server 2008?
Answers: • bigint
Which of the following queries is used to rename a database in SQL Server 2008?
Answers: • sp_renamedb oldname,newname
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,6,5,3,2,4
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
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.
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
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.
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
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
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
Which of the following data types are supported by SQL Server 2008?
Answers:
• date
• datetime
• datetime2
Which of the following statements are true?
Answers: • IGNORE_DUP_KEY cannot be set to ON for XML indexes.
• IGNORE_DUP_KEY cannot be set to ON for indexes created on a view.
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.
• New pages allocated in a heap as part of DML operations will not use PAGE compression until the heap is rebuilt.
Which of the following security features have been introduced in SQL Server 2008?
Answers: • Extensible Key Management
• Transparent Data Encryption
Which of the following encryption algorithms are used by the Transparent Data Encryption security mechanism of SQL Server 2008?
Answers: • Advanced Encryption Standard(AES)
• Triple Data Encryption Standard(3DES)
Which of the following formats are supported by the language_term argument while altering a full-text stoplist in SQL Server 2008?
Answers: • Integer
• Hexadecimal
Which of the following are valid DATETIME functions in SQL Server 2008?
Answers: • SYSDATETIME()
• SYSUTCDATETIME()
Which of the following counters are used to monitor compression of the whole instance of SQL Server?
Answers: • Page compression attempts/sec
• Pages compressed/sec
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 filegroup in the FILESTREAM_ON <filegroup>> clause must have one file defined for the filegroup.
Which of the following spatial data types are used in SQL Server 2008?
Answers: • Geography
• Geometry
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.
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.
• FILESTREAM storage is best used when the BLOB file sizes average 1MB or higher.
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
For which of the following data types can a COLLATE clause be applied?
Answers: • text
• varchar
Columns marked with which of the following constraints/properties allow null values to be inserted in them?
Answers: • UNIQUE
• FOREIGN KEY
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 100
Answers: • One-to-one
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: • 200
An identifier denoting a global temporary object in SQL Server 2008 starts with a ______ sign.
Answers: • Double number(##)
Which of the following features is used by SQL Server Audit to create and manage audits in SQL Server 2008?
Answers: • Triggers
What is returned by NULLIF if the two expressions that it compares are NOT equal?
Answers: • It returns the first expression.
User defined data type columns can be designated with ROWGUIDCOL.
Answers: • False
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 myServer\instanceName -i C:\myScript.sql -o C:\new.txt
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.2346
Which of the following is used by SQL Server 2008 Management Studio to execute scripts in regular mode in the Query Editor?
Answers: • Microsoft .NET Framework SqlClient
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: • OFF
Which of the following statements is NOT true about the UNIQUE constraint of SQL Server 2008?
Answers: • UNIQUE constraint cannot be referenced by a FOREIGN KEY constraint
Which of the following options is used to change the value of the password variable and exit using sqlcmd?
Answers: • -P
The regular identifiers of SQL Server 2008 may contain a range of _____________ characters.
Answers: • 1 to 128
Which of the following evaluation modes of Policy-Based Management CANNOT be automated in SQL Server 2008?
Answers: • On demand
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 without the FOREIGN KEY constraint and a warning message is returned.
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: • Policy-Based Management
How many groupings are generated when CUBE is used with the GROUP BY clause of SQL Server 2008?
Answers: • 2^n (n= number of expressions in the element list passed to CUBE)
Which of the following Audit objects primarily describes the destination where the audit data of SQL Server 2008 gets stored?
Answers: • Server Audit object
Windows Security log can be specified as a target for Audit in SQL Server 2008.
Answers: • True
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
What is the default timeout for login to sqlcmd while you try to connect to a server?
Answers: • 8 seconds
What is the default precision of the date data type in SQL Server 2008?
Answers: • 10 digits
What amount of storage is required by a date variable in SQL Server 2008?
Answers: • 3 bytes
PRIMARY KEY constraint defaults to __________ index.
Answers: • Clustered
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 REBUILD WITH ( DATA_COMPRESSION = PAGE ) GO
What is the return type of the value returned by @@DATEFIRST in SQL Server 2008?
Answers: • tinyint
Which of the following features of SQL Server 2008 enables you to limit the memory used by incoming application requests?
Answers: • Resource Governor
Which data type of SQL Server 2008 converts itself into the type of data that is inserted into it?
Answers: • sql_variant
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.
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
A local temporary stored procedure name can contain a maximum of __________ characters.
Answers: • 116
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: • _n
Which of the following is the default authentication mode for sqlcmd in SQL Server 2008?
Answers: • Windows Authentication
All MDX, DMX and XML/A errors are displayed in the Error List window of the SQL Server 2008. Management Studio.
Answers: • True
Which of the following stored procedures is used to delete data from the management data warehouse of a Data Collector?
Answers: • core.sp_purge_data
What is the maximum number of processors that can be used in parallel plan execution using the MAXDOP option in SQL Server 2008?
Answers: • 64 processors
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: • File_pattern
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.2348000
What exactly is Full-Text Catalog in SQL Server 2008?
Answers: • The Full-Text Catalog is a file which contains full-text indexes.
Which of the following clauses of SQL Server 2008 avoids enforcing a constraint when repeated inserts are performed?
Answers: • IDENTITY
Which of the following roles is required to connect to a Central Management Server?
Answers: • ServerGroupReaderRole
What range is supported by the TIME data type in SQL Server 2008?
Answers: • 00:00:00.0000000 through 23:59:59.9999999
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 (STATE=ON)
What is the name of the default instance installed by SQL Server 2008 Express in your computer?
Answers: • sqlexpress
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 and 3
Which of the following statements is true regarding indexes in SQL Server 2008?
Answers: • Modifications made in a table will be slower if the number of indexes is more.
How many audit action groups are available for a server in SQL Server 2008 to perform different actions on audit data?
Answers: • 40
Which of the following data types can be specified as SPARSE?
Answers: • nchar
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: • All of the above
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: • 4,1,2,3
What is the default maximum precision of decimal data type in SQL Server 2008?
Answers: • 18
What is the maximum number of columns that can be combined into a single composite index key?
Answers: • 16
Which of the following values specifies that each instance of the XML data type in column_name can contain multiple top-level elements?
Answers: • CONTENT
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 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.
Which of the following commands is used to start the Resource Governor in SQL Server 2008?
Answers: • ALTER Resource Governor Reconfigure
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 -S myServer\instanceName
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
What is the default precision of the TIME data type in SQL Server 2008?
Answers: • 7 digits
Which of the following permissions is required to drop a stored procedure in SQL Server 2008?
Answers: • Either b or c
The login timeout when you try to connect to a server must be a number between __________.
Answers: • 0 and 65534
In which of the following statements can TOP clause be used in SQL Server 2008?
Answers: • Only SELECT, INSERT, UPDATE, MERGE, DELETE
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: • NULL
Which of the following namespaces is used to manage the audit configuration programmatically in SQL Server 2008?
Answers: • Microsoft.SqlServer.Management.Smo
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: • Audit events are buffered in memory until they can be flushed to the target.
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 PARTITION = 1 WITH (DATA_COMPRESSION = NONE) GO
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: • 1,2,3
Which of the following options is NOT supported by the ON DELETE clause of a FOREIGN KEY constraint of SQL Server 2008?
Answers: • DELETE
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 performance of the stored procedure is not affected.
Each table in SQL Server 2008 can have up to ______ nonclustered indexes.
Answers: • 999
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: • An incorrect syntax error is displayed.
Which of the following is a valid code to remove the key used to encrypt a database using Transparent Data Encryption algorithm?
Answers: • DROP 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: • PERSISTED
The output of a Transact SQL query using the GROUPING function of SQL Server 2008 is of __________ return type.
Answers: • tinyint
What is the data type of the parameter "Audit_file_offset" of function "fn_get_audit_file()" in SQL Server 2008?
Answers: • bigint
Which of the following queries is used to rename a database in SQL Server 2008?
Answers: • sp_renamedb oldname,newname
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,6,5,3,2,4
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
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.
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
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.
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
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
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
Which of the following data types are supported by SQL Server 2008?
Answers:
• date
• datetime
• datetime2
Which of the following statements are true?
Answers: • IGNORE_DUP_KEY cannot be set to ON for XML indexes.
• IGNORE_DUP_KEY cannot be set to ON for indexes created on a view.
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.
• New pages allocated in a heap as part of DML operations will not use PAGE compression until the heap is rebuilt.
Which of the following security features have been introduced in SQL Server 2008?
Answers: • Extensible Key Management
• Transparent Data Encryption
Which of the following encryption algorithms are used by the Transparent Data Encryption security mechanism of SQL Server 2008?
Answers: • Advanced Encryption Standard(AES)
• Triple Data Encryption Standard(3DES)
Which of the following formats are supported by the language_term argument while altering a full-text stoplist in SQL Server 2008?
Answers: • Integer
• Hexadecimal
Which of the following are valid DATETIME functions in SQL Server 2008?
Answers: • SYSDATETIME()
• SYSUTCDATETIME()
Which of the following counters are used to monitor compression of the whole instance of SQL Server?
Answers: • Page compression attempts/sec
• Pages compressed/sec
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 filegroup in the FILESTREAM_ON <filegroup>> clause must have one file defined for the filegroup.
Which of the following spatial data types are used in SQL Server 2008?
Answers: • Geography
• Geometry
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.
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.
• FILESTREAM storage is best used when the BLOB file sizes average 1MB or higher.
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
For which of the following data types can a COLLATE clause be applied?
Answers: • text
• varchar
Columns marked with which of the following constraints/properties allow null values to be inserted in them?
Answers: • UNIQUE
• FOREIGN KEY
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 100
