SQL programming is an equally fun and challenging task. This is especially true for developers whose backgrounds are in traditional languages like C, C++, Java, and VB and who have become so accustomed to manual coding that they find it especially difficult to adopt a set-based mindset. Just the same, even seasoned database administrators and SQL developers are not immune to the many pitfalls of the language. The key is mastering your fundamentals and being sensitive to common mistakes, even though they are often difficult to spot. Here are common SQL server mistakes that you should watch out for when using Ad Hoc Reporting Tools:
- An asterisk in SELECT statements – It is critical to define and specify columns that you want to return in your queries. This standard is crucial for security and performance reasons. Take for instance the query SELECT * FROM Customer, this will return all customer values, including any and all sensitive data that you might be keeping in the same table. This will cause a great deal of trouble if you keep your customers’ social security number, password, or any other sensitive information within the table. Even when these values are encrypted, the hash value can still help hackers. There will also be a huge performance issue if the table contains dozens of columns. Defining and specifying columns will not only limit the size of the query’s record set but also the data available for hackers, thus preventing breaches.
- Excessive looping with cursors – Looping structures are the bane of any SQL database performance. They permit looping through millions of records and running statements against each of these records, individually. While this may appear advantageous, it can actually destroy your performance, as they make programming inefficient. A good workaround would be to write the procedure in a way that avoids affecting database performance.
