Transact SQL Ερωτήσεις και Απαντήσεις Tips & Tricks

Transact SQL Ερωτήσεις και Απαντήσεις

Transact SQL Ερωτήσεις και Απαντήσεις, είναι ερωτήσεις σε μορφή Quiz μέσα σε μια Δωρεάν Αντρόιντ εφαρμογή που την λένε T-SQL Tips&Tricks Demo δημοσιευμένη στο Google Play. Δείτε περισσότερα ερωτηματολόγια εδώ στο UncoverThe.com με SQL περιεχόμενο.

1. SQL SERVER 2014. Your OLTP database has more users and more queries and your buffer pool gets under pressure.

You have no budget to buy extra RAM but your tempdb is on a fast SSD drive. Is there a way to resolve this memory problem?

Possible Answers:

1. Place some data files on the SSD drive. Data can be read faster from an SSD and make the memory problem less urgent.
2. Implement BUFFER POOL EXTENSION on your SSD drive to create VIRTUAL RAM on this fast disk.
3. Compress your biggest tables and indexes.

2. You add a CHECK Constraint on SalesHeader defining that the column SubTotal must be greater than 0.

ALTER TABLE [Sales].[SalesOrderHeader] ADD CONSTRAINT CK_TOTAL CHECK (SubTotal >0)
When you run the following query, what will be the result (Subtotal has no index defined)?

SELECT * FROM [Sales].[SalesOrderHeader] WHERE SubTotal = 0;

Possible Answers:

1. There will be a table scan looking for values where the SubTotal = 0.
2. There will be no scan at all, as the optimizer knows that there could be not a value like that. There will be no IO.

3. You want to replace your ADHOC queries with prepared queries.

What will be the difference in error handling in those 2 cases? (select a non-existing column)

BEGIN TRY
SELECT dummy FROM Sales.Customer;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE();
END CATCH

BEGIN TRY
EXEC sp_executesql N’SELECT dummy FROM Sales.Customer;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE();
END CATCH

Possible Answers:

1. Both statements will show the error message:Invalid column name ‘dummy’.
2. The first statement will not go into the CATCH block as there is a compile error. The second will go into the CATCH block as it runs in another context.

4. You use the dmf sys.dm_db_index_physical_stats() and remove for a table unused indexes.

Some of them are UNIQUE indexes. Some of your queries which use that table run slower afterwards. What can be the reason?

1. Together with the removed indexes also their statistics were removed. Those were used by the optimizer in order to create execution plans.
2. The values of an unique index are taken into consideration by the optimizer in order to create an execution plan. Without those values the optimizer can choose a suboptimal plan.

5. You have 2 tables with a FOREIGN KEY relationship: [Sales].[SalesTerritoryHistory] and [Sales].[SalesTerritory] on the TerritoryID column.

No new records will be inserted on both tables and you decide to disable this constraint. What will be the result for the following query? (with and without the foreign key relationship)

SELECT STH.*
FROM [Sales].[SalesTerritoryHistory] STH INNER JOIN [Sales].[SalesTerritory] ST
ON STH.TerritoryID = ST.TerritoryID

Possible Answers:

1. No difference. The foreign key is only used for insertion of records. For SELECT statements the existence of a foreign key is irrelevant.
2. In the query only historydata is used (STH.*). With a foreign key, only a table scan on this table would be necessary. Without a foreign key an JOIN would have been necessary causing more IO.

6. You rebuild fragmented indexes on a table.

Afterwards you want to execute sp_updatestats. Is there any problem which will be caused by the sp_updatestats statement?

1. No, in contrary. You will have optimal statistics on this table.
2. Yes there will be a problem. An index rebuild also updates your statistics by counting all records. sp_updatestats takes only a sample of the rows. If sp_updatestats runs after an index rebuild it will make your statistics less precise.

7. You can interrupt an index rebuild or and index reorganize statement.

What will be the difference in both cases?

Possible Answers:

1. There will be no difference. Both statements will ROLL BACK and the fragmentation on the tables will be in the original state.
2. An index rebuild is a single transaction. When interrupted the index will be in the state before the rebuild. When interrupting an index reorganize the fragmentation will be at the moment of the interruption.

8. You have table Sales.Customer and you create a unique index on the AccountNumber column which has a varchar(10) data type.

CREATE UNIQUE NONCLUSTERED INDEX [AK_Cst] ON [Sales].[Customer] ([AccountNumber] ASC)
What will be the result of the two queries concerning index usage?
SELECT AccountNumber FROM [Sales].[Customer] WHERE AccountNumber = N’AW00000043′;
SELECT AccountNumber FROM [Sales].[Customer] WHERE AccountNumber = ‘AW00000043’;

Possible Answers:

1. Both will use the nonclustered index for an index seek.
2. The first query will have an index scan because it searches an index on a varchar column with a nvachar value. The second query will have an index seek because the data type of the index is the same as the one of the WHERE clause.

9. You create a table: DATES. You create a stored procedure which inserts rows in this table.

CREATE TABLE dbo.DATES
(inputDate DATE NOT NULL)
GO
CREATE PROCEDURE dbo.InsertDates AS
BEGIN TRANSACTION
INSERT INTO dbo.DATES(inputDate)
VALUES(‘2009-01-01’);

INSERT dbo.DATES(inputDate)
VALUES(‘2017-01-01’),
(‘ABCDE’) — WRONG DATE

INSERT dbo.DATES(inputDate)
VALUES(‘2018-01-01’);
COMMIT TRANSACTION
You run the sp: EXEC dbo.InsertDates. How many rows will the procedure return?

Possible Answers:

1. 0 rows. Due to a CONVERSION ERROR the whole transaction is rolled back and the batch is aborted.
2. 2 rows. The transactions are atomic on a statement level. 2 statements succeed, one fails. (‘2009-01-01′,’2018-01-01’ succeed)
3. 1 row. After the first insert, the second statement and third statements will not be reached.

10. You create an index on a table: CREATE INDEX IX_1 ON [Sales].[SalesOrderDetail]([CarrierTrackingNumber])
(CarrierTrackingNumber can have NULL values; SalesOrderID is the primary key)

You run the following query and you get and index scan instead of a SEEK:

SELECT SalesOrderID FROM [Sales].[SalesOrderDetail] WHERE ISNULL(CarrierTrackingNumber,’5822-4DF1-92′) = ‘5822-4DF1-92’;
How can you BEST rewrite and optimize the query so it will use an inde seek?

Possible Answers:

1. SELECT SalesOrderID FROM [Sales].[SalesOrderDetail] WHERE CarrierTrackingNumber =’5822-4DF1-92’OR CarrierTrackingNumber IS NULL ;
2. SELECT SalesOrderID FROM [Sales].[SalesOrderDetail] WHERE COALESCE(CarrierTrackingNumber,’5822-4DF1-92′) = ‘5822-4DF1-92’;

11. In the table Production.ProductListPriceHistory you create an index on the columns ( StartDate, ProductID). StartDate is a datetime column.

You want to find the price of a product for one specific day. Which is the best query causing the least overhead?

SELECT StartDate, ProductID
FROM Production.ProductListPriceHistory
WHERE CAST(StartDate AS DATE) = ‘2005-07-01’;

SELECT StartDate, ProductID
FROM [Production].[ProductListPriceHistory] WHERE DATEPART(YEAR,StartDate) = 2005
AND DATEPART(MONTH,StartDate) = 7
AND DATEPART(DAY,StartDate) = 1;

Possible Answers:

1. Both queries will result into an index seek generating the same IO.
2. The first query will have an index seek, the second and index scan causing extra IO (and more time).
3. The first query will have an index scan (causing extra IO and the second and index seek.

12. You run queries on a table which filters on gender and BirthDate.

If you would create an index to optimize this query, what should be the order of the columns in the index?

SELECT BusinessEntityID FROM HumanResources.Employee
WHERE Gender = ‘F’ AND BirthDate BETWEEN ‘1968-12-13’ AND ‘1981-03-27’

Possible Answers:

1. Index on Gender first, then BirhtDate.
2. Index on BirthDate first, then Gender.

13. You create a schema SalesEurope and a User JOHN2 who has SalesEurope as a default schema.

He is also member of the db_owner fixed database role.

You create 2 stored procedures with the same name but in different schemas: Test2 in schema dbo and in SalesEurope. When JOHN2 will bas to proc Test2, which one will be called?

CREATE SCHEMA SalesEurope AUTHORIZATION DBO;
CREATE USER [JOHN2] WITHOUT LOGIN WITH DEFAULT_SCHEMA = [SalesEurope] ;
ALTER ROLE [db_owner] ADD MEMBER [JOHN2]

CREATE PROCEDURE dbo.Test2 AS SELECT ‘FROM DBO SCHEMA’ GO
CREATE PROCEDURE SalesEurope.Test2 AS SELECT ‘FROM SalesEurope SCHEMA’ GO

EXECUTE AS USER = ‘JOHN2’
EXEC Test2
REVERT

Possible Answers:

1. The procedure of his default schema: SalesEurope
2. The procedure from the dbo schema.

14. You have SQL SERVER 2014 Standard Edition with 128 gigabyte if RAM.

Your tempdb is on a fast SSD drive. You have memory pressuse because your RAM size is insufficient (128 gigabytes for standard edition).

Possible Answers:

1. Enable buffer pool extension and use the SSD drive as virtual RAM
2. Upgrate to Enterprise Edition and buy more RAM.

15. What is the result if you run the following batches?

SELECT CAST(1245678 AS TINYINT)
SELECT ‘BATCH 1 COMMENT VISIBLE’
GO
SELECT CAST(‘ABDCD’ AS TINYINT)
SELECT ‘BATCH 2 COMMENT VISIBLE’

Possible Answers:

1. None of the BATCH COMMENTS will be visible due to the errors which are generated.
2. BATCH 1 will be visible. BATCH 2 not visible as it will generate a batch terminating error.
3. BATCH 1 will not be visible as it will generate a batch terminating error. BATCH 2 will be visible.

16. You create an index: CREATE INDEX IX_CTNR ON Sales.SalesOrderDetail(CarrierTrackingNumber) and you run the following 2 queries:

SELECT SalesOrderID FROM Sales.SalesOrderDetail
WHERE CarrierTrackingNumber LIKE N’4911%’

SELECT SalesOrderID FROM Sales.SalesOrderDetail
WHERE LEFT(CarrierTrackingNumber,4) = N’4911′
What is true concerning the index operations on both the queries?

Possible Answers:

1. Both queries will perform an index scan.
2. Both queries will perform an index seek.
3. First query will perform and index seek, the second an index scan.
4. First query will perform and index scan, the second an index seek.

17. You have a stored procedure with a truncate table statement inside a transaction.

If there is an error inside that transaction in the procedure can you rollback the transaction with the truncate table statement?

BEGIN TRAN
TRUNCATE TABLE TAB1
ROLLBACK TRAN

Possible Answers:

1. No. the truncate table statement is not logged (the reason why it is very fast). The disadvantage is that it cannot be rolled back.
2. You can not place a truncate table in a BEGIN TRAN COMMIT/ROLLBACK TRAN clause.
3. You can rollback a truncate table statement. This statement is minimally logged (extend (de)allocations are logged in the transaction log). You can rollback the deallocated extents.
4. Rollback of a truncate table statement depends on the recovery mode of the database the statement runs.

18. You write a stored procedure to find currencyrates for a certain period.

In order to work optimal you create an index on CurrencyRateDate to have index seeks.

However sometimes you have an index scan and due to parameter sniffing the plan is kept in cache. How to achieve that you always have an index seek? (most optimal way, causing the least overhead)

CREATE PROCEDURE dbo.GetRates @CurrencyRateDateStart DATE, @CurrencyRateDateEnd DATE
AS SELECT CurrencyRateDate,ToCurrencyCode, AverageRate FROM Sales.CurrencyRate
WHERE CAST(CurrencyRateDate AS DATE)
BETWEEN @CurrencyRateDateStart AND @CurrencyRateDateEnd

Possible Answers:

1. Place at the end of the statement OPTION(RECOMPILE)
2. Rewrite the query: FROM Sales.CurrencyRate WITH(FORCESEEK) to always have an indexes seek instead of a scan.
3. Place at the end of the statement OPTION(OPTIMIZE FOR UNKNOWN)

19. You have your sales data for Europe and the US in different tables.

For a report you want to unify those data sets. Which 2 statement should use in order to have the best performance?

SELECT * FROM Sales.SalesOrderDetail_EUR
UNION ALL
SELECT * FROM Sales.SalesOrderDetail_US
— OR—
SELECT * FROM Sales.SalesOrderDetail_EUR
UNION
SELECT * FROM Sales.SalesOrderDetail_US

Possible Answers:

1. Both statements will have the same overhead involving CPU and IO.
2. The UNION statement will be faster.
3. UNION ALL will be faster. The UNION statement has to do an ordering of the result set to exclude double entries.

20. In a table you have a PRIMARY key constraint of type UNIQUEIDENTIFIER.

You can choose to have as a default a NEWID value or a NEWSEQUENTIALID value. What is the best choice for the scan performance of your queries?

Possible Answers:

1. NEWID and NEWSEQUENTIALID will not make a difference on the performance of your queries. They have the same 16 byte column length.
2. Inserts with a NEWID default value will cause much more fragmentation on an index than with a NEWSEQUENTIALID. The first creates keys in random order and the latter generates keys in sequential order. Fragmentation causes delays in scan queries.

21. You have a view Sales.vSalesPerson which joins on multiple tables.

You want to update 2 columns (SET Title = ‘MR’, EmailAddress=”). Those columns belong to 2 different tables in the view. How to achieve this?

Possible Answers:

1. Run an update statement: UPDATE [Sales].[vSalesPerson] SET Title = ‘MR’, EmailAddress = ”;
2. Create an update trigger on this view to update the columns Title and EmailAddress.
3. Create an instead of update trigger on this view to update the columns Title and EmailAddress.

22. You create a delete trigger on HumanResources.Employee.

When an employee is deleted a record is inserted into the DeleteHistory table. What will be the result when you execute the TRUNCATE HumanResources.Employee statement?

Possible Answers:

1. The table will be emptied and the deleted records inserted in the DeleteHistory table.
2. The table will be emptied and the deleted records will NOT be inserted in the DeleteHistory table. The truncate statement does not fire triggers.

23. You want to add a FOREIGN KEY relationship between the SalesOrderHeader (SOH) table and SalesOrderDetail (SOD) table.

For a certain key in SOD the same key must exists in SOH as a primary key or unique index. What will be the right syntax?

Possible Answers:

1. ALTER TABLE Sales.SalesOrderDetail
ADD CONSTRAINT FKtest FOREIGN KEY(SalesOrderID)
REFERENCES Sales.SalesOrderHeader(SalesOrderID)
2. ALTER TABLE Sales.SalesOrderHeader
ADD CONSTRAINT FKtest2 FOREIGN KEY(SalesOrderID)
REFERENCES Sales.SalesOrderDetail(SalesOrderID)

24. You must create a program which will create and use temporary tables in order to make calculations.

What type of object would be optimal to run this program?

Possible Answers:

1. Stored procedure.
2. Multistatement table valued function.
3. Inline table valued fuction.

25. You query the table Sales.SalesOrderHeader on the column PurchaseOrderNumber.

50 Percent of the values in this column are null and those values are filtered out in the query.

You want to create an index in order to have better performance and use minimal disk space. What index to use?

Possible Answers:

1. CREATE NONCLUSTERED INDEX IX1 ON Sales.SalesOrderHeader (PurchaseOrderNumber) WHERE PurchaseOrderNumber IS NOT NULL ;
2. CREATE NONCLUSTERED INDEX IX1 ON Sales.SalesOrderHeader (PurchaseOrderNumber) WHERE PurchaseOrderNumber IS NULL ;

26. SQL SERVER 2012. You have a large datawarehouse table with a nonclustered COLUMNSTORE index.

Your database is in Full Recovery Mode. Every week you bulk insert rows into this table. What is the first thing you have to do before the insertion of the rows?

Possible Answers:

1. Disable the COLUMNSTORE index.
2. Drop the COLUMNSTORE index.
3. Change the Recovery Mode to Bulk Logged in order to have minimal logging.

27. You need to create primary keys for 2 tables.

These keys should be monotonically increasing and should use minimal disk space. Also, they should be unique between the 2 tables. Which data type or object to use?

Possible Answers:

1. UNIQUEIDENTIFIER column with a default of NEWSEQUENTIALID().
2. An Identity column of type integer.
3. A SEQUENCE object of data type integer

28. You have a WEB application with a GRID which can show maximum 20 rows.

When you retrieve rows from the database you want to get 20 rows at a time. What is the best statement to do this task if you want to read rows 80-100?

Possible Answers:

1.SELECT TOP(100) * FROM dbo.Clients ORDER BY ClientID
EXCEPT
SELECT TOP(80) * FROM dbo.Clients ORDER BY ClientID
2. SELECT * FROM dbo.Clients ORDER BY ClientID OFFSET 80 ROWS FETCH NEXT 20 ROWS ONLY.
3. SELECT * FROM dbo.Clients ORDER BY ClientID FETCH NEXT 20 ROWS ONLY.

29. You set up a FOREIGNK KEY relationship between Sales.SalesOrderHeader (SOH) and Sales.SalesOrderDetails (SOD).

In case that a record from SOH is removed then also the referencing row from SOD should be automatically removed. What clause is needed after the following statement?

ALTER TABLE Sales.SalesOrderDetail ADD CONSTRAINT FKtest FOREIGN KEY(SalesOrderID) REFERENCES Sales.SalesOrderHeader(SalesOrderID)

Possible Answers:

1. ON DELETE CASCADE.
2. ON DELETE NO ACTION
3. ON UPDATE CASCADE

30. You want to see your sales per product together with the cumulative running totals for 1 productid.

PRID DATE LineTotal RunTotal
317 2005-06-07 14882.175 14882.175
317 2006-03-19 16268.175 31150.35
317 2006-03-19 14882.175 46032.525
317 2006-06-10 16268.175 62300.70

SELECT ProductID AS PRID
, CAST(ModifiedDate AS DATE) AS DATE
, LineTotal
, [???] FROM Purchasing.PurchaseOrderDetail
WHERE ProductID = 317

Which statement to include?

Possible Answers:

1. SUM(LineTotal) OVER(PARTITION BY ProductID ORDER BY ModifiedDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunTotal.
2. SUM(LineTotal) OVER(PARTITION BY ProductID ORDER BY ModifiedDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS RunTotal.

31. You want to see your sales per product together with the sales at the previous date for 1 productid.

PRID DATE LineTotal PrevTotal
317 2005-06-07 14882.175 NULL
317 2006-03-19 16268.175 14882.175
317 2006-03-19 14882.175 16268.175
317 2006-06-10 16268.175 14882.175

SELECT ProductID AS PRID
, CAST(ModifiedDate AS DATE) AS DATE
, LineTotal
, [???] as PrevTotal
FROM Purchasing.PurchaseOrderDetail
WHERE ProductID = 317

Possible Answers:

1. LAG(LineTotal,1,NULL) OVER(PARTITION BY ProductID ORDER BY ModifiedDate) AS PrevSale
2. LAG(LineTotal,-1,NULL) OVER(PARTITION BY ProductID ORDER BY ModifiedDate) AS PrevSale

32. For a table you can create a primary key constraint or an unique contraint. What is the difference?

Possible Answers:

1. A primary key is always clustered by default.
2. A primary key can never have null values. An unique constraint can have a null value.
3. An unique constraint is always clustered by default.

33. In a table you want to have an UNIQUEIDENTIFIER column as a primary key.

Daily you will insert thousands of records. What is the best definition if you consider fragmentation as a thread for performance?

Possible Answers:

1. C1 UNIQUEIDENTIFIER DEFAULT NEWID()
2. C1 UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID()

34. You have 2 tables: OrderHeader and Customer which have a foreign key relationship ON CustomerID.

You have the following query:
SELECT OH.SalesOrderId
FROM Sales.SalesOrderHeader OH INNER JOIN Sales.Customer CU on OH.CustomerID = CU.CustomerID;

What is the advantage of having a foreign key relationship for this query?

Possible Answers:

1. No disadvantage. We have the right indexes to perform a fast join.
2. By having a foreign key we can perform a query plan simplification by using a foreign key join elimination. By having a foreign key the join would not be necessary as the optimizer knows that for 1 record in orderdetail there would exist one record in the orderHeader.

35. In a table variable you insert bonuspercentages for your salespeople.

The percentages will be from 8.00 to 10.00 percent.
In a loop you fill this variables with 20 values (8.00, 8.1, 8.2,…9.9, 10.00). What will be the result of this action?

DECLARE @Bonus float= 8.00 ;
DECLARE @BonusPct TABLE
(PCT float)

WHILE @Bonus != 10.00
BEGIN
INSERT INTO @BonusPct(PCT)
VALUES( @Bonus) ;
SET @Bonus = @Bonus +0.10;
END;

Possible Answers:

1. In the table variable values will be inserted from 8.1 to 10 with increment of 0.1.
2. The insert operation inside the loop will never stop. @Bonus is a float (approximate data type) and not an exact data type. The value 10 will never be reached exactly.

36. You create a view with an ORDER BY clause.

Can you guarantee the order of the data when you run a SELECT query on the view?

CREATE VIEW TopBonusses AS
SELECT TOP 100 PERCENT BusinessEntityID, Bonus
FROM Sales.SalesPerson;
GO
SELECT * FROM TopBonusses;

Possible Answers:

1. Yes, the ORDER BY in the VIEW definition guarantees the correct sort order.
2. No. The order by clause is only used in order to enable the TOP 100 PERCENT clause.

37. You create applications which use stored procedures which you install at a client.

You do not want him to see the TSQL code inside those procedures. What option to use to hide the code?

Possible Answers:

1. Create a procedure with the WITH ENCRYPTION option.
2. Enable TDE for your client’s database.
3. Create an encryption function in CLR in order to encrypt the code of your procedure.

38. You write error handling code for a stored procedure which is called by other procedures.

In case of an error and an open transaction you want this transaction to roll back. Which statement to place?
BEGIN TRY
BEGIN TRAN
INSERT INTO Sales.Currency(CurrencyCode,Name,ModifiedDate)
VALUES(‘EUR’,’EURO’,’2018-01-01′) —GENERATES AN ERROR: PRIMARY KEY VIOLATION
COMMIT TRAN
END TRY
BEGIN CATCH
[?????] ROLLBACK TRAN
END CATCH

Possible Answers:

1. IF XACT_STATE() = 1.
2. IF XACT_STATE() = 0.
3. IF @@TRANCOUNT = 1
4. IF @@ERROR > 1

39. For a bookkeeping database you want to store amounts with fixed precision and a scale of 2 digits.

Which data type should you use?

Possible Answers:

1. Money.
2. Decimal.
3. Integer.
4. Float.

40. In a database there are 2 tables where you are not the owner.

However, you have SELECT permissions on those tables.

You want to create a stored procedure which will use those tables. How to guarantee other users can run this procedure without having any permission problem?

Possible Answers:

1. Inside the procedure write EXECUTE AS CALLER.
2. Inside the procedure write EXECUTE AS SELF.
3. Inside the procedure write OPTION(OPTIMIZE FOR UNKNOWN).

41. You have 2 statements in order to find if the color BLACK is not in a set or records.

What will be the result of those 2 statements?

WITH CTE1
AS(
SELECT * FROM (VALUES(‘RED’),(‘YELLOW’), (‘GREEN’), (NULL)) AS D(COLORS))

1) SELECT ‘TRUE’ WHERE ‘BLACK’ NOT IN(SELECT COLORS FROM CTE1 )

2) SELECT ‘TRUE’ FROM CTE1 WHERE NOT EXISTS(SELECT 1 FROM CTE1 WHERE COLORS = ‘BLACK’)

Possible Answers:

1. Both will return NULL.
2. The first statement will return NULL. The second three times TRUE.
3. Both will return ‘TRUE’.

42. You want to rank your SalesPeople with the most sales per day.

In case of the same SalesAmount you want the same ranking.

Also, you do not want any gaps or rankings in your result set.
SELECT BusinessEntityID, Bonus, [???] FROM Sales.SalesPerson

Possible Answers:

1. DENSE_RANK() OVER(ORDER BY Bonus DESC)
2. RANK() OVER(ORDER BY Bonus DESC)
3. ROW_NUMBER OVER(ORDER BY Bonus DESC)

43. You have 2 tables with a FOREIGN KEY relationship: Sales.SalesOrderDetail (SOD) and Sales.SalesOrderHeader (SOH).

You migrate and copy the records to other tables and you want to delete all the records on those tables using DELETE or TRUNCATE statements. You NOCHECK the foreign key constraints. What will be the result of both statements?

BEGIN TRAN
DELETE FROM Sales.SalesOrderDetail
DELETE FROM Sales.SalesOrderHeader
COMMIT TRAN TRAN
— OR —–
BEGIN TRAN
TRUNCATE TABLE Sales.SalesOrderDetail
TRUNCATE TABLE Sales.SalesOrderHeader
COMMIT TRAN

Possible Answers:

1. The first statement will succeed. The second statement will fail. You cannot truncate a table with a FOREIGK KEY constraint.
2. The first statement will fail, the second will succeed.

44. You create table variables and you want to create a primary key on it. What will be the result?

DECLARE @Sales TABLE
(SaleID INT PRIMARY KEY CLUSTERED,
Amount DECIMAL(18,2)
)
GO
DECLARE @Sales TABLE
(SaleID INT ,
Amount DECIMAL(18,2)
)
ALTER TABLE @Sales ADD CONSTRAINT PK1 PRIMARY KEY CLUSTERED(SaleID)

Possible Answers:

1. Both statements will fail. You cannot create constraints on table variales.
2. The first statement will succeed. The second will fail. On a table variable you can only create an inline constraint.
3. The first statement will fail. The second will succeed. On a table variable you cannot create an inline constraint.

45. You have a query:

SELECT employeeCategory,Name,
ROW_NUMBER() OVER(PARTITION BY employeeCategory ORDER BY salary DESC).
You create nonclustered indexes to optimize this WINDOW function. Which one should you use?

Possible Answers:

1. (employeeCategory, salary) INCLUDE(name)
2. (employeeCategory, salary, name)
3. (employeeCategory, salary)
4. (salary, employeeCategory, name)

46. Your .Net application written in C# is accessing a production database.

.Net uses in their connection settings ARITHABORT = OFF. In SSMS where you debug the SQL code generated from .Net this option is set to ON. What will happen if 2 queries run in different batches?
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SELECT 1/0
GO
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SELECT 1/0

Possible Answers:

1. Both statements will return a DIVISION BY ZERO error.
2. The first statement will return NULL, the second an error (DIVISION BY ZERO)

47. You have a table Production.UnitMeasure which has as primary key UnitMeasureCode.

You insert 3 rows and the third row generates a Primary Key violation. What rows are inserted? (you have no begin tran or commit/rollback defined)

SET XACT_ABORT ON
GO
INSERT INTO Production.UnitMeasure(UnitMeasureCode,Name,ModifiedDate)
VALUES(‘BOX’,’NAME2′,’2017-01-03′) — PK violation, error
INSERT INTO Production.UnitMeasure(UnitMeasureCode,Name,ModifiedDate)
VALUES(‘AAA’,’NAME1′,’2017-01-01′)
INSERT INTO Production.UnitMeasure(UnitMeasureCode,Name,ModifiedDate)
VALUES(‘DDD’,’NAME2′,’2017-01-02′)

SELECT * FROM Production.UnitMeasure

Possible Answers:

1. The second and third rows are inserted: AAA, DDD
2. No row is inserted. XACT_ABORT aborts the whole batch after the first error. It does not matter that no transaction is defined.

48. You have 2 persons in a helpdesk (John, James) and you want to divide the incoming calls between those two.

You have a table Helpdesk for the incoming calls and you create a SEQUENCE object to allocate the work (by using the modulo function).
What will be the result of the SELECT query?
DECLARE @ErrorDescription NVARCHAR() = ‘RUNTIME ERROR’;
CREATE SEQUENCE dbo.WorkAlloc AS SMALLINT
INSERT INTO HelpDesk(Agent,Task)
SELECT IIF(NEXT VALUE FOR dbo.WorkAlloc %2 =0, ‘John’,’James’), @ErrorDescription

Possible Answers:

1. The IIF function applied on a SEQUENCE object will give an error.
2. Depending on an even or uneven SEQUENCE value there will be an allocation of work between 2 persons.

49. In a table you insert 10000 rows in a single transaction.

What is the results concerning the obtained locks?
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
DECLARE @x int = 0;
WHILE (@x < 10000)
BEGIN
insert INTO dbo.NUMBERS(nr) VALUES(@x) ;
SET @x = @x + 1;
END
COMMIT TRAN

Possible Answers:

1. The locks on the table NUMBERS will escalate to a table lock as you have more than 5000 Exclusive row locks on this table inside a transaction. These row locks will be released at the end of the transaction.
2. The row locks generated by the insert statement will remain row locks because the lock escalation threshold in SQL Server is defined at a statement level, not at transaction level. You will have 10000 insert statements.

50. A view Sales.vSalesPerson has joins on multiple tables. You want to update 1 column (SET Title = ‘MR’). How to achieve this?

Possible Answers:

1. Run an update statement: UPDATE Sales.vSalesPerson SET Title = ‘MR’
2. Create an instead of update trigger on this view to update the column Title.
3. Create an update trigger on this view to update the column Title.

Dimitri Stathatos

Dimitri is a fanatic MVP DBA who sleeps and wakes up with SQL commands in his mind. Since 2017, also makes questionnaires about Microsoft’s certification exams and in co-operation with Nikolaos Katsogridakis, published many preparation apps on Google_Play. He would like to UncoverThe all his experience within these pages.