SQL Smack down is back!
There are few topics from training that I feel deserve a little more explanation, so here we go.
Transactions are really useful in handling errors in T-SQL. I will not go into great detail explaining all of the possible uses here, but I will give a few examples of how to use them. The basic usage of a transaction is:
BEGIN TRANSACTION
--T-SQL code here
COMMIT TRANSACTION
--or--
ROLLBACK TRANSACTION
What this will do is tell SQL Server to go through the regular steps of executing your code. However, every change to the data will be logged, and if a problem arises, you can tell SQL Server to "rollback" the transaction, or reset to its previous state. If everything looks like it executed ok, you can "commit" the transaction and the changes will be made permanent in the data.
Error handling gets a little more complex, but the basis of it is still pretty simple. Here is an example:
BEGIN TRANSACTION
-- T-SQL code here
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
END
That is about as simple as it gets. There are a few more things you can add in to help you out when debugging. The "raiserror" command will return to the query analyzer in the form of a message or an error. There are 2 different methods of raising an error. The first is by message. This simply prints the message in the result messages. The parameters in this instance are message, level (or severity), and state. For an explanation of level and state for raiserror, please refer to books online. Example of raising a message error follows:
RAISERROR ('There is an error in the data!', 10, 1)
This will return the following in the query analyzer:
Error in stored procedure Raise Error Demo.
If you wish to return an error to the client you must use an existing error message and call that message by number. The errors are stored in sysmessages in the master database if you wish to look them up. The parameters for these messages are error number, level, and state. Some error messages will have variables that you will need to provide to the raiserror method. These are simply appended to the end of the parameter list after all the other parameters. Variables are denoted by a %d. You may also notice a %s, which returns the current stored procedure name, as well as a few others that do not require input. Please refer to books online for further information on how to use these parameters. To raise a predefined error message:
RAISERROR(3270, 16, 1)
This will return the following to the client:
Server: Msg 3270, Level 16, State 1, Line 1
An internal consistency error occurred. Contact Technical Support for assistance.
You can also add a new message to the list if you cannot find one that suits your needs. All user defined error messages must use an error number above 50000. The parameters for sp_addmessage are error number, level (or severity), and message. The syntax is as follows:
EXEC sp_addmessage 50001, 16, 'Error Message here.'
Also, please keep in mind that there are many optional parameters available for use with these commands that may be useful in your application. Please refer to books online and review all these commands before using them.
Indexes are used to speed up queries on data sets. When you query a table, the SQL server query engine will have to scan through each row of the table looking for matches on the conditions of the where clause. In some cases if there are indexes created on these fields you will notice drastically improved performance. There are many instances however where adding indexes will have little to no effect on the performance of your query, or even worse, will hurt overall performance of your database.
In general indexes should be created on fields that have large numbers of unique values, as sorting these rows will usually have the most impact on performance. In most cases indexes are best suited for fields used in the where clause of a query, or on fields used in joins. Often times indexes on fields used in group by and order by clauses will greatly improve performance also. You will find that creating indexes that cover all the fields used in your query will often result in better performance that multiple single field indexes. The order of the fields used in the query should match the order of the fields used in the index for best performance.
One thing to keep in mind, is that while creating a single index on every single field in the table sounds like a good idea, in reality this is very bad. You also need to think about the size of your table. Usually on smaller tables you will find that performance will be the same or better with no indexes on the table, because the time it takes the query engine to look up the index, then cross reference the table to find the data is often longer than it would take for the query engine to do a scan on the entire table.
Probably the most useful tool when creating indexes is the Display Execution Plan option under the Query menu in the query analyzer. This option will show you exactly what SQL Server is doing to execute the query. If you are in doubt about what is the best index to use, try creating each different option, then execute the query. When the query has finished look at the execution plan to see which indexes SQL Server used. Then you can delete the other index(s) not used in the query. This is really only an option on mid-sized tables, as on very large tables creating indexes can take several hours or more.
Lets look at the different types of indexes.
Clustered indexes physically reorder the data in the table. For this reason you can only have 1 clustered index per table. These indexes are generally good for tables that do not have many inserts performed on them. With a clustered index on your table, every time a row is inserted the data in the table will have to be physically reordered to fit the new row in, which will result in a large performance hit on the database.
Non-clustered indexes are a totally separate entity from the data, as apposed to a clustered index which directly affects the data. A non-clustered index is similar to a look-up table, with the data from the table is stored in sorted order, with a pointer to the location of the data in the database.
I was going to cover Statistics and Fill Factor settings in this edition of SQL Smack down, but I feel I have already covered enough material for this time. Look for those topics in a future edition. As always, if you have any questions about these or any other topics, please talk to Barry.