SQL Server TSQLquiz - 8

Approved & Edited by ProProfs Editorial Team
The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.
Learn about Our Editorial Process
| By Guna24x7
G
Guna24x7
Community Contributor
Quizzes Created: 4 | Total Attempts: 3,017
Questions: 10 | Attempts: 323

SettingsSettingsSettings
SQL Server TSQLquiz - 8 - Quiz

This is your description.


Questions and Answers
  • 1. 

    I have a table, Sales, with the Notes field having the Filestream attribute. I run this query: select  salesmen, notes  into #salestemp  from Sales What is the data type of the Notes field in the temporary table?  

    • A.

      Varchar(max)

    • B.

      Varchar(max) with the filestream attribute

    • C.

      Varbinary(max)

    • D.

      Varbinary(max) with filestream attribute

    Correct Answer
    C. Varbinary(max)
    Explanation
    The data type of the Notes field in the temporary table is varbinary(max) because the original Notes field in the Sales table has the Filestream attribute, which indicates that the data is stored as binary data. Therefore, when the data is copied into the temporary table, it retains the varbinary(max) data type.

    Rate this question:

  • 2. 

    True or False: sparse Columns can have the Filestream attribute defined on them?

    • A.

      True

    • B.

      False

    Correct Answer
    B. False
    Explanation
    Sparse columns cannot have the Filestream attribute defined on them. The Filestream attribute is used to store large binary data outside the database, while sparse columns are used to optimize storage for columns that contain mostly NULL values. These two features serve different purposes and cannot be used together.

    Rate this question:

  • 3. 

    What technique is used by the SQL Server to maintain Durability of Transactions?

    • A.

      BEGIN...END Block

    • B.

      Write Ahead Logging

    • C.

      Locking

    • D.

      AutoCommit Transactions

    Correct Answer
    B. Write Ahead Logging
    Explanation
    Write Ahead Logging is a technique used by SQL Server to maintain Durability of Transactions. This technique ensures that all changes made by a transaction are first written to a log file before being written to the database. This log file serves as a record of all the changes made, allowing for recovery in case of system failure or crash. By using Write Ahead Logging, SQL Server ensures that committed transactions are durable and can be recovered even in the event of a failure.

    Rate this question:

  • 4. 

    What is the difference between the following 2 queries:    -- 1 SELECT ROW_NUMBER() OVER (ORDER BY NEWID())  FROM sys.all_columns   -- 2 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NEWID()))  FROM sys.all_columns  

    • A.

      The first query returns an error and the second returns a Tally table.

    • B.

      The first query returns a Tally table and the second returns the same row count and numbers but in a randomized order. The first query returns a Tally table and the second returns the same row count and numbers but in a randomized order. The first query returns a Tally table and the second returns the same row count and numbers but in a randomized order.

    • C.

      The second query returns a Tally table and the first returns the same row count and numbers but in a randomized order.

    • D.

      No difference

    Correct Answer
    D. No difference
  • 5. 

    What will be the values returned by the COUNT(*) statements ? (select the answer that show the 3 values in correct order) CREATE TABLE #TempTable (RowID INT) CREATE TABLE UserTable (RowID INT)     BEGIN TRAN  INSERT UserTable (RowID) VALUES (1) ROLLBACK   INSERT UserTable (RowID) VALUES (2)   BEGIN TRAN  INSERT #TempTable (RowID) VALUES (1) ROLLBACK   INSERT #TempTable (RowID) VALUES (2)   DECLARE @TableVariable TABLE (RowID INT)   BEGIN TRAN  INSERT @TableVariable (RowID) VALUES (1) ROLLBACK   INSERT @TableVariable (RowID) VALUES (2)   SELECT COUNT(*) FROM UserTable  SELECT COUNT(*) FROM #TempTable SELECT COUNT(*) FROM @TableVariable   DROP TABLE #TempTable DROP TABLE UserTable

    • A.

      1,1,1

    • B.

      1,1,2

    • C.

      1,2,2

    • D.

      2,2,2

    Correct Answer
    B. 1,1,2
    Explanation
    The first INSERT statement is rolled back, so no rows are actually inserted into the UserTable. The second INSERT statement inserts one row with a value of 2 into the UserTable. Therefore, the COUNT(*) statement for the UserTable will return a value of 1.

    The first INSERT statement for the #TempTable is also rolled back, so no rows are inserted. The second INSERT statement inserts one row with a value of 2 into the #TempTable. Therefore, the COUNT(*) statement for the #TempTable will return a value of 1.

    The first INSERT statement for the @TableVariable is rolled back, so no rows are inserted. The second INSERT statement inserts one row with a value of 2 into the @TableVariable. Therefore, the COUNT(*) statement for the @TableVariable will return a value of 1.

    Rate this question:

  • 6. 

    You wish to enable xp_cmdshell. You run the following code:   EXECUTE sys.sp_configure      @configname = 'show advanced options'     ,@configvalue = 1 GO RECONFIGURE GO EXECUTE sys.sp_configure      @configname = 'dsh'     ,@configvalue = 1 GO RECONFIGURE GO

    • A.

      The first call to sp_configure succeeds

    • B.

      The second call to sp_configure fails

    • C.

      The second call to sp_configure succeeds

    Correct Answer(s)
    A. The first call to sp_configure succeeds
    C. The second call to sp_configure succeeds
    Explanation
    The first call to sp_configure succeeds because it sets the value of the 'show advanced options' configuration option to 1. This allows the system to display advanced configuration options. The second call to sp_configure also succeeds because it sets the value of the 'dsh' configuration option to 1, enabling the xp_cmdshell feature.

    Rate this question:

  • 7. 

    What is a table called, if it does not have either aClustered or a nonclustered index?

    • A.

      Heap

    • B.

      Temporary table

    • C.

      Partitioned table

    • D.

      Unclustered table

    Correct Answer
    A. Heap
    Explanation
    A table without either a clustered or a nonclustered index is called a heap. In a heap, the data is stored in an unordered manner, without any specific organization or sorting. This can result in slower data retrieval and manipulation compared to tables with indexes. However, heaps can be useful in certain scenarios, such as when the table is frequently modified or when the data is accessed in a sequential manner.

    Rate this question:

  • 8. 

    NTILE returns what?

    • A.

      The sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

    • B.

      The rank of each row within the partition of a result set. After a tie there will numeric gaps in the next ranked number

    • C.

      The rows in an ordered partition into a specified number of groups.

    • D.

      The rank of rows within the partition of a result set, without any gaps in the ranking

    Correct Answer
    C. The rows in an ordered partition into a specified number of groups.
    Explanation
    NTILE returns the rows in an ordered partition into a specified number of groups. This means that it divides the result set into equal-sized groups based on the specified number. Each row is assigned a sequential number representing the group it belongs to. This function is useful for analyzing data distribution and creating percentiles or quartiles.

    Rate this question:

  • 9. 

    What will be the output of these SELECT queries? DECLARE @var money = 123457756 Declare @dec decimal =12345 Declare @dec1 Decimal(10,2) = 12345   --Select 1 SELECT LEN(@var)   --Select 2 SELECT LEN(@dec)   --Select 3 SELECT LEN(@dec1)

    • A.

      12,5,8

    • B.

      9,5,5

    • C.

      12,8,8

    • D.

      9,8,5

    Correct Answer
    A. 12,5,8
    Explanation
    The first SELECT query returns the length of the string representation of the @var variable, which is 12.

    The second SELECT query returns the length of the string representation of the @dec variable, which is 5.

    The third SELECT query returns the length of the string representation of the @dec1 variable, which is 8.

    Rate this question:

  • 10. 

    Declare @value int set @value = 5555555 PRINT CONVERT(varchar(6), @value)  What will be the output?  

    • A.

      Arithmetic overflow error converting expression to data type varchar.

    • B.

      *

    • C.

      555555

    • D.

      5555555

    Correct Answer
    B. *
    Explanation
    The code snippet sets the value of the variable @value to 5555555 and then tries to convert it to a varchar(6) data type. However, since the value is too large to fit within 6 characters, it causes an arithmetic overflow error. Therefore, the correct answer is "*".

    Rate this question:

Related Topics

Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.