Subtitles of the Movie

Let's finish up our section on Transact-SQL statements by talking about some coding tips. Let's begin with just some generalized tips. First, do not use spaces in the names of database objects. The database name itself, the tables, the fields, they should never include spaces or special characters. Remember, if we're using a/or a plash or a dash, they each have a function within our formulas and our coding; as a result, should never be used in the name. This also holds through with keywords. Word such as first, last that can also be used within our coding should never be used as a name of an object. Whenever possible, avoid the use of NOT. Use positives instead such as EXISTS, IN, or LIKE. Keep in mind that when you're working with the word not, the database is not always able to stop looking as soon as it finds that entry. It has to prove that no entry exists, so it has to actually scan through the entire table. If a column is cross referencing a column within another table, also make sure that it is the primary table that serves as the linking mechanism. It's really important to minimize how much jumping around that we actually have to do within the coding and as we've talked about, use Indexes. If the index is unique, make the index unique. Also, consider using indexes for functions especially things such as max and minimum. It will definitely speed the process along as you're working with your database itself. Now, on the structure side, make sure that each table does have a primary key. Remember also that traditionally, it's the name of the table followed by the letters ID. Normalize your data to at least the third normal form. Now remember, there are other forms that are out there. It's best to do your research, but minimum go to the third normal form. If you are using default values, do not allow NULLs. Remember, the blank value should be replaced with your default and as much as possible, make sure all your stored procedures are in the same database of the tables they will use. Crossing over between databases especially with stored procedures is, of course, just a drain on resources. In formatting your code, make sure you use upper case for the SQL statements. These are verbiage such as SELECT, INSERT, UPDATE. Remember, it certainly makes it a lot easier to troubleshoot the coding as they stand out and use your comments especially with your blocks of code. We tend to use a single comment line marker to allow to know that this is information for the person reading the code. Now, reserve the slashes for blocking out sections of code. This way, it's easy for people to see what is instructional and what is just removed or remarked code. Use BEGIN and END blocks only when multiple statements are present within the conditional code. Use spaces so expressions read like sentences and again, it's easier for the viewer and place set statements before the existing code within a procedure itself. Other concepts that we should keep in mind with coding include Optimize Your Queries. Use the tools provided and remember, do not use SELECT everything. It's pulling too much data in. Return multiple sets from one stored procedure to avoid trips to the application. Remember, we wanna minimize how many times that we have to crossover the network. We wanna make one trip for as many different components as we can. Also avoid unnecessary use of those temporary tables. They can be a drain. Avoid using not equal to as a comparison operator. Remember, we wanna stay positive. Use number IN 1, 3, 4 and 5 instead of the number not equal to 2. There is less action required on the part of the server. Do not define your default values for your parameters on the server. If a default is needed, the front end should be supplying that value, not on the server itself. And finally, place all your DECLARE statements at the front of a procedure. Again, we wanna be conscious of how many trips are we taking on the network, how much information are we pulling up from our actual tables and what can we do to avoid the drain on our resources.