Querying Microsoft SQL Server 2012_Realworld_Examtips

Real World Float Trouble

We remember a case where a customer used FLOAT to represent barcode numbers of

products, and was then surprised by not getting the right product when scanning the

products’ barcodes. Also, recently, we got a query about conversion of a FLOAT value to

NUMERIC, resulting in a different value than what was entered. Here’s the case.

DECLARE @f AS FLOAT = '29545428.022495';

SELECT CAST(@f AS NUMERIC(28, 14)) AS value;

Can you guess what the output of this code is? Here it is.

Value

---------------------------------------

29545428.02249500200000

Real world Anticipating Errors

When you handle errors in a CATCH block, the number of errors that can occur is quite large, so it is difficult to anticipate all of them. Also, the types of transactions or procedures involved might be specialized. Some T-SQL developers prefer to just return the values of the error functions as in the previous SELECT statement. This can be most useful for utility stored procedures. In other contexts, some T-SQL developers use a stored procedure that can be called from the CATCH block and that will provide a common response for certain commonly encountered errors.

THROW vs. RAISERROR in TRY/CATCH

In the TRY block, you can use either RAISERROR or THROW (with parameters) to generate an error condition and transfer control to the CATCH block. A RAISERROR in the TRY block must have a severity level from 11 to 19 to transfer control to the CATCH block. Whether you use RAISERROR or THROW in the TRY block, SQL Server will not send an error message to the client.

In the CATCH block, you have three options: RAISERROR, THROW with parameters, or THROW without parameters.

You can use a RAISERROR in the CATCH block to report the original error back to the client, or to raise an additional error that you want to report. The original error number cannot be re-raised. It must be a custom error message number or, in this case, the default error number 50000. To return the error number, you could add it to the @error_message string. Execution of the CATCH block continues after the RAISERROR statement.You can use a THROW statement with parameters, like RAISERROR, to re-raise the error in the CATCH block. However, THROW with parameters always raises errors with a custom error number and a severity level of 16, so you don't get the exact information. THROW with parameters terminates the batch, so commands following it are not executed.

A THROW without parameters can be used to re-raise the original error message and send it back to the client. This is by far the best method for reporting the error back to the caller.

Now you get the original message sent back to the client, and under your control, though it does terminate the batch immediately.

Exam Tip

You must take care that the THROW with or without parameters is the last statement you want executed in the CATCH block, because it terminates the batch and does not execute any remaining commands in the CATCH block.

Exam Tip

1.Understanding the built-in tools T-SQL provides for generating surrogate keys like the sequence object, identity column property, and the NEWID and NEWSEQUENTIALID functions, and their impact on performance, is an important skill for the exam.

2. COALESCE and ISNULL can impact performance when you are combining sets; for example,

with joins or when you are filtering data. Consider an example where you have two tables T1 and T2 and you need to join them based on a match between T1.col1 and T2.col1. The attributes do allow NULLs. Normally, a comparison between two NULLs yields unknown,and this causes the row to be discarded. You want to treat two NULLs as equal. What some do in such a case is use COALESCE or ISNULL to substitute a NULL with a value that they know cannot appear in the data. For example, if the attributes are integers, and you know that you have only positive integers in your data (you can even have constraints that ensure this), you might try to use the predicate COALESCE(T1.col1, -1) = COALESCE(T2.

col1, -1), or ISNULL(T1.col1, -1) = ISNULL(T2.col1, -1). The problem with this form is that,because you apply manipulation to the attributes you’re comparing, SQL Server will not rely on index ordering. This can result in not using available indexes efficiently. Instead, it is recommended to use the longer form: T1.col1 = T2.col1 OR (T1.col1 IS NULL AND T2.col1 IS NULL), which SQL Server understands as just a comparison that considers NULLs as equal. With this form, SQL Server can efficiently use indexing.

3. Understanding the impact of using COALESCE and ISNULL on performance is an important skill for the exam.

4. Often, when joining tables, you join them based on a foreign key–unique key relationship.

For example, there’s a foreign key defined on the supplierid column in the Production .Products table (the referencing table), referencing the primary key column supplierid in the Production.Suppliers table (the referenced table). It’s also important to note that when you define a primary key or unique constraint, SQL Server creates a unique index on the constraint columns to enforce the constraint’s uniqueness property. But when you define a foreign key, SQL Server doesn’t create any indexes on the foreign key columns. Such indexes could improve the performance of joins based on those relationships. Because SQL Server doesn’t create such indexes automatically, it’s your responsibility to identify the cases where they can be useful and create them. So when working on index tuning, one  interesting area to examine is foreign key columns, and evaluating the benefits of creating indexes on those.

5. As explained in the discussion of window aggregate functions, window functions are allowed only in the SELECT and ORDER BY clauses of the query. If you need to refer to those in other clauses—for example, in the WHERE clause—you need to use a table expression such as a CTE. You invoke the window function in the inner query’s SELECT clause, assigning the expression with a column alias. Then you refer to that column alias in the outer query’s WHERE clause. You have a chance to practice this technique in this lesson’s exercises.

6 Although full-text search is not on the list of the exam objectives, an indirect question about it could appear. Remember that full text predicates can also be a part of the WHERE clause of a query.

7. The FREETEXT predicate is less selective than the CONTAINS predicate, and thus it usually returns more rows than the CONTAINS predicate.

8. Semantic search is available through the table-valued functions only; it does not support any specific predicates for the WHERE clause of a query.

9. The FOR XML clause comes after the ORDER BY clause in a query.

10. Navigation through XML can be quite tricky; make sure you understand the complete path.

11. You can move a table from one schema to another by using the ALTER SCHEMA TRANSFER statement. Assuming there is no object named Categories in the Sales database schema, the following statement moves the  Production.Categories table to the Sales database schema.

ALTER SCHEMA Sales TRANSFER Production.Categories;

To move the table back, issue the following.

ALTER SCHEMA Production TRANSFER Sales.Categories;

12. The unique constraint does not require the column to be NOT NULL. You can allow NULL in

a column and still have a unique constraint, but only one row can be NULL.

13. Because joins often occur on foreign keys, it can help query performance to create a nonclustered index on the foreign key in the referencing table. There is already a unique index on the corresponding column in the referenced table, but if the referencing table,like Production.Products, has a lot of rows, it may help SQL Server resolve the join faster if it can use an index on the big table.

14. Results of a view are never ordered. You must add your own ORDER BY when you SELECT from the view. You can include an ORDER BY in a view only by adding the TOP operator or the OFFSET FETCH clause to the SELECT clause. Even then, the results of the view will not be ordered. Therefore, an ORDER BY in a view, even when you can enter it, is useless.

15. Synonyms cannot refer to other synonyms. They can only refer to database objects such as

tables, views, stored procedures, and functions. In other words, synonym chaining is not allowed.

16. Make sure you understand how modification statements are affected by constraints defined in the target table.

17. An important technique in multiple choice questions in the exam is to first eliminate answers that are obviously incorrect. For example, suppose you get a question and a few possible answers that contain code samples. And suppose one of the answers contains code defining a table that has two columns with an IDENTITY property or two PRIMARY KEY constraints. You can quickly eliminate such answers from consideration. This way, you can spend more time on fewer answers.

18. Note that transactions can span batches. This includes both implicit transactions and explicit transactions—that is, GO statements. However, it is often a best practice to make sure that each transaction takes place in one batch.

19. An inner COMMIT statement has no real effect on the transaction, only decrementing @@TRANCOUNT by 1. Just the outermost COMMIT statement, the one executed when @@TRANCOUNT = 1, actually commits the transaction.

20. Note that it doesn't matter at what level you issue the ROLLBACK command. A transaction can contain only one ROLLBACK command, and it will roll back the entire transaction and reset the @@TRANCOUNT counter to 0.

21. Isolation levels are set per session. If you do not set a different isolation level in your session, all your transactions will execute using the default isolation level, READ COMMITTED. 

For on-premise SQL Server instances, this is READ COMMITTED. In Windows Azure SQL

Database, the default isolation level is READ COMMITTED SNAP SHOT.

22. The statement before the THROW statement must be terminated by a semicolon (;). This reinforces the best practice to terminate all T-SQL statements with a semicolon.

23. You must take care that the THROW with or without parameters is the last statement you want executed in the CATCH block, because it terminates the batch and does not execute any remaining commands in the CATCH block.

24. You should leave QUOTED_IDENTIFIER set to ON because that is the `ANSI standard and the SQL Server default.

25. The ability to parameterize means that sp_excutesql avoids simple concatenations like those used in the EXEC statement. As a result, it can be used to help prevent SQL injection.

26. You should use SQL Server Extended Events instead of SQL Trace and SQL Server Profiler because Extended Events is more lightweight and SQL Trace and SQL Server Profiler are deprecated in future versions of SQL Server.

27 The NOCOUNT setting of ON or OFF stays with the stored procedure when it is created. Placing a SET NOCOUNT ON at the beginning of every stored procedure prevents the procedure from returning that message to the client. In addition, SET NOCOUNT ON can improve the performance of frequently executed stored procedures because there is less network communication required when the "rows(s) affected" message is not returned to the client.

28. Always include the EXEC command when calling a stored procedure. That will avoid getting unexpected and confusing errors. If the statement is no longer the first statement in the batch, it will still run.

29. It is a best practice to name the parameters when you call stored procedures. Although passing parameter values by position may be more compact, it is also more error prone. If you pass parameters by name and the parameter order changes in the stored procedure, your call of the procedure will still work.

30. When you code a WHILE loop, it is critical to ensure that something happens in the loop that will eventually make the WHILE condition evaluate to false so that the WHILE loop will terminate. Always check the body of the WHILE loop and make sure that a counter is incremented  or a value changes so that the loop will always terminate under all conditions.

31. Even though a BEGIN/END block is optional in a WHILE loop if you only have one statement,it is a best practice to include it. The BEGIN/END block helps you organize your code, makes it easier to read, and makes it easier to modify in the future. Any statement block in a WHILE loop with more than one statement requires the BEGIN/END construct.

32. When an UPDATE or DELETE occurs and no rows are affected, there is no point in proceeding with the trigger. You can improve the performance of the trigger by testing whether @@ROWCOUNT is 0 in the very first line of the trigger. It must be the first line because @@ROWCOUNT will be set back to 0 by any additional statement. When the AFTER trigger begins, @@ROWCOUNT will contain the number of rows affected by the outer INSERT,UPDATE, or DELETE statement.

33. It is not a good practice to return result sets from triggers. In SQL Server 2012 and earlier versions, returning a rowset from a trigger is allowed, but it cannot be relied on. You can also disable it with the sp_configure option called Disallow Results From Triggers. In addition,the ability to return result sets from a trigger is deprecated and will be dropped in the next version of SQL Server after SQL Server 2012.

34. Only a nested loops join algorithm supports non-equijoins.