Database Technet Quiz - 6
-
What will be the out of the following script? IF object_id('table_a') IS NOT NULL DROP TABLE table_a CREATE TABLE table_a ( a INT IDENTITY(50,1), b INT ) SET identity_insert table_a ON --stmt 1 INSERT INTO table_a (a, b) VALUES (100, 1) SET identity_insert table_a OFF --stmt 2 INSERT INTO table_a VALUES (2) --stmt 3 SET identity_insert table_a ON INSERT INTO table_a (a, b) VALUES (4, 3) SET identity_insert table_a OFF --stmt 4 INSERT INTO table_a VALUES (4) SELECT * FROM table_a where b=4
-
52,4
-
5,4
-
102,4
-
51,4
-
101,4
-
About This Quiz
The 'Database TechNet Quiz - 6' assesses knowledge on SQL Server database maintenance, including script outputs, stored procedures, and dependency views. It tests practical skills crucial for database administration and maintenance.
(29).webp "Database Technet Quiz - 6 - Quiz")
Quiz Preview
- 2.
When we install and configure EBS Database Maintenance utility will create ‘BonusingInfrastructure’ Database to store purge history?
-
True
-
False
Correct Answer
A. TrueExplanation
When we install and configure EBS Database Maintenance utility, it will create a 'BonusingInfrastructure' Database to store purge history. This means that when the utility is set up, it automatically creates this specific database for the purpose of storing purge history. Therefore, the statement "True" is the correct answer.Rate this question:
-
- 3.
Which System function used to get SQL Server Edition(i.e Standard/Enterprise)?
-
SERVERPROPERTY
-
OBJECTPROPERTY
-
DATABASEPROPERTY
-
DATABASEPROPERTYEX
Correct Answer
A. SERVERPROPERTYExplanation
The SERVERPROPERTY function is used to retrieve various properties of the SQL Server instance, including the edition (Standard/Enterprise). It can be used to programmatically determine the edition of the SQL Server being used, which can be useful for performing different actions or implementing specific features based on the edition.Rate this question:
-
- 4.
What are the results for cp.pkey when you run following statement: CREATE TABLE dbo.Sales(name nvarchar(50), pkey int); insert dbo.Sales values('AAA',0); insert dbo.Sales values('BBB',1); SELECT up.name, cp.name,up.pkey,cp.pkey FROM dbo.Sales AS up, Sales AS cp WHERE up.pkey = 1 or cp.pkey = 0 ORDER BY 4; drop table dbo.Sales;
-
0,0,1,1
-
0,1,1,1
-
0,1,1
-
0,0,1
-
0,1
Correct Answer
A. 0,0,1Explanation
The query is selecting the "name" and "pkey" columns from two instances of the "dbo.Sales" table, aliased as "up" and "cp". The WHERE clause specifies that the "up.pkey" should be equal to 1 or the "cp.pkey" should be equal to 0. The ORDER BY clause is ordering the results based on the fourth column, which is "cp.pkey". Since the only row where "cp.pkey" is 0 is the second row, it will be the first row in the result set. Therefore, the correct answer is 0,0,1.Rate this question:
-
- 5.
What will be the result set? Declare @customer Table ( ID int , Name varchar(20) ); Declare @Order Table ( OrderID int identity(1,1) , customerID int ); Insert into @customer Values (1,'JOHNNY BRAVO'), (2,'MICKI'), (3,'POWERPUFF GIRLS') INSERT INTO @Order VALUES (1), (3) SELECT ID, NAME FROM @customer C WHERE NOT EXISTS ( SELECT 1 FROM @Order WHERE ID = c.ID)
-
SQL Server will show an error.
-
One row will be in the result set.
-
No rows will be in the result set.
Correct Answer
A. No rows will be in the result set.Explanation
The query is selecting the ID and NAME columns from the @customer table where there is no record in the @Order table with the same ID. Since there is a record in the @Order table with ID = 1, the WHERE NOT EXISTS condition will evaluate to false for the first row in the @customer table. Therefore, no rows will be in the result set.Rate this question:
-
- 6.
Which option would we use to encrypt the definition of the view and prevent users of the database from seeing it?
-
SCHEMABINDING
-
Transparent Data Encryption (TDE)
-
ENCRYPTION
-
None of the above
Correct Answer
A. ENCRYPTIONExplanation
ENCRYPTION is the correct option to use in order to encrypt the definition of the view and prevent users of the database from seeing it. Encryption is a security measure that transforms data into a coded form that can only be accessed or deciphered with the use of a decryption key. By encrypting the definition of the view, it ensures that unauthorized users cannot view or understand the underlying structure or content of the view. This helps to protect sensitive information and maintain data privacy and security.Rate this question:
-
- 7.
How many rows a returned from this batch? CREATE PROC #TEMP AS DECLARE @A INT = 0 DECLARE @TABLE TABLE (ID INT IDENTITY(1,1), VAL INT) WHILE 1 = 1 BEGIN INSERT INTO @TABLE SELECT 1 IF @a = 10 BEGIN INSERT INTO @TABLE SELECT @a RETURN 1; INSERT INTO @TABLE SELECT 2 END SET @a += 1; END SELECT * FROM @TABLE WHERE VAL > 1; GO EXEC #TEMP DROP PROC #TEMP
-
Two rows in the result set.
-
Only one row in the result set.
-
No row in the result set.
Correct Answer
A. No row in the result set.Explanation
The code creates a temporary table called @TABLE and inserts rows into it using a while loop. The loop continues indefinitely until a condition is met. Inside the loop, the code checks if the value of @a is equal to 10. If it is, it inserts a row with the value of @a into the @TABLE and then returns 1, which ends the execution of the stored procedure. However, since @a is initially set to 0 and is incremented by 1 inside the loop, it will never reach the value of 10. Therefore, the loop will continue indefinitely and no rows will be inserted into the @TABLE. As a result, when the SELECT statement is executed at the end of the code, there will be no rows with a value greater than 1 in the @TABLE, resulting in no rows in the result set.Rate this question:
-
- 8.
Number of execution threads in SQL Server varies based on number of CPU and Server Editions(32-bit/64-bit)?
-
True
-
False
Correct Answer
A. TrueExplanation
The number of execution threads in SQL Server can vary based on the number of CPUs and the server edition (32-bit or 64-bit). SQL Server can utilize multiple execution threads to process queries and perform tasks concurrently, which can improve performance and scalability. The number of threads is typically determined by the number of available CPU cores and the server edition's limitations. Therefore, the statement is true.Rate this question:
-
- 9.
Which batches execute successfully? (select 4) --First batch create table dbo.existing (c int); go --Second batch create procedure dbo.test_existing as if (1<>1) begin select c from dbo.non_existing; end else begin select c from dbo.existing; end go --Third batch execute dbo.test_existing; go --4th batch alter procedure dbo.test_existing as if (1=1) begin select c from dbo.non_existing; end else begin select c from dbo.existing; end go --5th batch execute dbo.test_existing; go
-
1st
-
2nd
-
3rd
-
4th
-
5th
Correct Answer(s)
A. 1st
A. 2nd
A. 3rd
A. 4thExplanation
The first batch executes successfully because it creates a table called "existing". The second batch does not execute successfully because it tries to select from a table called "non_existing" which does not exist. The third batch executes successfully because it calls the "test_existing" procedure, which selects from the "existing" table. The fourth batch executes successfully because it alters the "test_existing" procedure to select from the "existing" table. The fifth batch executes successfully because it calls the altered "test_existing" procedure.Rate this question:
-
- 10.
From SQL Server 2008 and above, Which Database Manitenance View(DMV) is used to find dependent Stored Procedures for given table?
-
Sys.tables
-
Sys.sql_modules
-
Sys.sql_expression_dependencies
-
Sys.dm_sql_referencing_entities
Correct Answer
A. Sys.sql_expression_dependenciesExplanation
The sys.sql_expression_dependencies DMV is used to find dependent Stored Procedures for a given table in SQL Server 2008 and above. This DMV provides information about the objects that depend on the specified table, including stored procedures, views, functions, and triggers. It helps in understanding the impact of any changes to the table structure on the dependent objects.Rate this question:
-
Quiz Review Timeline (Updated): Jun 6, 2023 +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Jun 06, 2023Quiz Edited by
ProProfs Editorial Team -
Aug 22, 2013Quiz Created by
Guna24x7
Back to top