SQL Server T-SQL Quiz-I

Reviewed by Editorial Team
The ProProfs editorial team is comprised of experienced subject matter experts. They've collectively created over 10,000 quizzes and lessons, serving over 100 million users. Our team includes in-house content moderators and subject matter experts, as well as a global network of rigorously trained contributors. All adhere to our comprehensive editorial guidelines, ensuring the delivery of high-quality content.
Learn about Our Editorial Process
| By Guna24x7
G
Guna24x7
Community Contributor
Quizzes Created: 4 | Total Attempts: 3,221
| Attempts: 2,749
SettingsSettings
Please wait...
  • 1/10 Questions

    I have a table contains a large number of rows (the relevant table structure is abbreviated here) for this CREATE TABLE FirstTable (ID INT, Col VARCHAR(100)) GO CREATE INDEX IX_Col ON FirstTable (Col);   I have this stored procedure CREATE PROC Test_1    @Get NVARCHAR(20) AS  SELECT ID,Col   FROM FirstTable   WHERE Col = @Get; The execution of Test_1 results in a table scan. I can NOT alter the table in any way. I have permission to alter the procedure. The question is: Can I alter the procedure to do an index seek?

    • True
    • False
Please wait...
About This Quiz

If you are a computer nerd that is really into programming then this quiz is specifically for you. It aims to find out how much knowledge you have on the SQL server T-SQL. All the best.

SQL Server T-SQL Quiz-I - Quiz

Quiz Preview

  • 2. 

    I execute the following query on SQL Server Management Studio: Query1: Create Table #Table1(Col1 int, Col2 Varchar(50), Col3 DateTime) Then I open another query window and execute the following query: Query2: Create Table #Table1(Col1 int, Col2 Varchar(50)) A column is different in query 1 and query2, but the table name is same. Will query 2 execute successfully?

    • Yes

    • No

    Correct Answer
    A. Yes
    Explanation
    Yes, query 2 will execute successfully. In SQL Server Management Studio, when a table is created with a temporary table name (#Table1 in this case), it is only accessible within the session or connection where it was created. Therefore, even though query 2 has a different column compared to query 1, it will not cause any conflicts because it is being executed in a separate query window. Each query window has its own session and can create its own temporary tables with the same name as long as they are not accessed simultaneously.

    Rate this question:

  • 3. 

    What does the select statement return? Create Table #T (        N1 Real      , N2 Float);   Insert Into #T Values (99.99999999991       , 99.99999999997);   Select * From #T;   Drop Table #T;

    • The select statement returns < 100 for N1 and 100 for N2

    • The select statement returns 100 for N1 and < 100 for N2

    • The select statement returns < 100 for both columns

    • The select statement returns 100 for both columns

    Correct Answer
    A. The select statement returns 100 for N1 and < 100 for N2
  • 4. 

    What is returned? create procedure dbo.testProc @testVar varchar(4) as select @testVar GO exec testProc 'This is a test string' GO

    • This is a test string

    • This

    • Error: string or binary data would be truncated.

    • NULL

    Correct Answer
    A. This
    Explanation
    The given code snippet is creating a stored procedure called testProc that takes a parameter called @testVar of type varchar(4). Inside the procedure, the @testVar is selected, which means it will be returned as the result of the procedure.

    When the stored procedure is executed with the parameter 'This is a test string', the result returned will be 'This'. However, it is important to note that an error message is also displayed stating "string or binary data would be truncated". This error occurs because the length of the input string exceeds the maximum length defined for the @testVar parameter, which is 4 characters.

    Rate this question:

  • 5. 

    True or False: Stored procedures are optimized at creation – and their optimized/compiled plan is saved to disk.

    • True

    • False

    Correct Answer
    A. False
    Explanation
    Stored procedures are not optimized at creation and their optimized/compiled plan is not saved to disk. The optimization process for stored procedures occurs at runtime when they are executed. The database management system analyzes the query and creates an execution plan based on the current state of the database and the available resources. This allows for flexibility and adaptability in the optimization process, as the execution plan can be adjusted based on the specific conditions at the time of execution.

    Rate this question:

  • 6. 

    What will be the output of below query when it is executed? (In the answer options, the rows are comma separated) SELECT TOP(6) WITH TIES COL FROM ( SELECT 1 COL UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 4 UNION ALL SELECT 3 UNION ALL SELECT 4 ) A  ORDER BY COL

    • 1,2,3,3,4,4

    • 1,2,3,4,5

    • 1,2,3,3,4,4,4

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

    Correct Answer
    A. 1,2,3,3,4,4
    Explanation
    The TOP(6) WITH TIES clause is used to retrieve the top 6 rows from the result set, including ties. In this case, the result set is created by the UNION ALL of selected values. The ORDER BY clause then sorts the result set based on the COL column in ascending order. The WITH TIES ensures that if there are ties (equal values), those additional tied rows are included in the output.

    Rate this question:

  • 7. 

    Create table #temptable1 (name1 char(10),  name2 char(10),  zip int ) GO   insert into #temptable1 values (NULL,NULL,11), ('1','2a',NULL), (NULL,'a2',111), (NULL,NULL,NULL) GO select name1,name2,zip ,COALESCE(name1,name2,zip) AS name3  from #temptable1 While executing the select statement, an error for a data type conversion is returned. Which row (numbered in insert order) gives the error?

    • 1

    • 2

    • 3

    • 4

    Correct Answer
    A. 3
    Explanation
    The error occurs in row number 3 because the COALESCE function is trying to convert the value 'a2' to an integer data type for the zip column. Since 'a2' cannot be converted to an integer, a data type conversion error is returned.

    Rate this question:

  • 8. 

    I have three select statements with different cast options : -- a. select CAST('aaaaaaaaaaaaaaaaaaaaabb' as varchar) -- b. select CAST('aaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbcccccccc' as varchar) -- c. select CAST('aaaaaaaaaaaaaaaaaaaaabbddddddddddddFF' as varchar(100)) Among these three which gives correct output, meaning the complete string inside quotes is returned?

    • A and b

    • A and c

    • B and c

    • Only c

    Correct Answer
    A. A and c
    Explanation
    The correct answer is "a and c". In statement a, the cast option is not specified, so the default cast is applied which returns the complete string inside quotes. In statement c, the cast option is specified as varchar(100), which also returns the complete string inside quotes. Statement b does not return the complete string inside quotes because the length of the string exceeds the default length for varchar.

    Rate this question:

  • 9. 

    What is the number of locks held by any T-SQL statement on a single reference of a table that triggers Lock Escalation (i.e. Row lock to Page level lock)?

    • 200

    • 1000

    • 50

    • 5000

    Correct Answer
    A. 5000
  • 10. 

    Create table address_staging   (clientid int primary key,addressdetails varchar(250));   insert into address_staging  select 100,'hyderbad,india'  union all  select 101,'banglore,india'  union all  select 102,'banglore,india' ; create table address_oltp  (client_id int primary key,address_details varchar(250));   insert into address_oltp  select 104,'newyork,usa'  union all  select 105,'chicago,usa'  union all  select 106,'washington,usa' ; select *  from address_oltp  where client_id in (select client_id from address_staging) How many rows are returned from the last SELECT?  

    • No rows and no errors.

    • 3 rows from address_oltp

    • 3 rows from address_staging

    • No rows, but a syntax error is returned.

    Correct Answer
    A. No rows and no errors.
    Explanation
    This result occurs because the subquery in the WHERE clause (SELECT client_id FROM address_staging) does not return any matching client_id from the address_oltp table, leading to an empty result set but without any errors.

    Rate this question:

Quiz Review Timeline (Updated): Apr 28, 2024 +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • Apr 28, 2024
    Quiz Edited by
    ProProfs Editorial Team
  • Jul 19, 2013
    Quiz Created by
    Guna24x7
Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.