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.
