Database Technet Quiz - 6

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

The script first checks if a table named "table_a" exists, and if it does, it drops the table. Then, it creates a new table called "table_a" with two columns: "a" with an identity starting at 50 and incrementing by 1, and "b".

Next, it sets identity_insert on for "table_a" and inserts a row with values 100 for "a" and 1 for "b". Then, it sets identity_insert off and inserts a row with a value of 2 for "b".

After that, it sets identity_insert on again and inserts a row with values 4 for "a" and 3 for "b". Finally, it sets identity_insert off and inserts a row with a value of 4 for "b".

The script then selects all rows from "table_a" where "b" is equal to 4.

The correct answer, 102,4, represents the second row inserted into the table, with "a" being 102 and "b" being 4.

Explanation

The script first checks if a table named "table_a" exists, and if it does, it drops the table. Then, it creates a new table called "table_a" with two columns: "a" with an identity starting at 50 and incrementing by 1, and "b".

Next, it sets identity_insert on for "table_a" and inserts a row with values 100 for "a" and 1 for "b". Then, it sets identity_insert off and inserts a row with a value of 2 for "b".

After that, it sets identity_insert on again and inserts a row with values 4 for "a" and 3 for "b". Finally, it sets identity_insert off and inserts a row with a value of 4 for "b".

The script then selects all rows from "table_a" where "b" is equal to 4.

The correct answer, 102,4, represents the second row inserted into the table, with "a" being 102 and "b" being 4.

2. Which System function used to get SQL Server Edition(i.e Standard/Enterprise)?

SERVERPROPERTY

OBJECTPROPERTY

DATABASEPROPERTY

DATABASEPROPERTYEX

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.

Explanation

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.

3. When we install and configure EBS Database Maintenance utility will create 'BonusingInfrastructure' Database to store purge history?

True

False

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.

Explanation

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.

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

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.

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.

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

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.

Explanation

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.

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

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.

Explanation

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.

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.

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.

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.

8. Number of execution threads in SQL Server varies based on number of CPU and Server Editions(32-bit/64-bit)?

True

False

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.

Explanation

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.

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

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.

Explanation

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.

Submit

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

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.

Explanation

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.