SQL Server T-SQL Quiz-I

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

The stored procedure Test_1 can be altered to do an index seek by modifying the WHERE clause of the SELECT statement. Instead of using the equality operator (=) to match the Col column with the @Get parameter, a range or inequality operator (such as >, =,

Explanation

The stored procedure Test_1 can be altered to do an index seek by modifying the WHERE clause of the SELECT statement. Instead of using the equality operator (=) to match the Col column with the @Get parameter, a range or inequality operator (such as >, =,

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

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.

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.

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

not-available-via-ai

Explanation

not-available-via-ai

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

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.

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.

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

True

False

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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

not-available-via-ai

Explanation

not-available-via-ai

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.

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.

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.