The Basics of Good T-SQL Coding Style – Part 2: Defining Database Objects – Simple Talk

The Basics of Good T-SQL Coding Style – Part 2: Defining Database Objects – Simple Talk

.

Lot’s of good here.

A database team must be proactive when it comes to outdated code. Many T-SQL and ANSI SQL elements have been deprecated over the years or are slated for deprecation at some point in the future. Chances are good you have deprecated elements lurking somewhere in your production code, given how the standards are always changing. For example, in SQL Server 2016, the SET ROWCOUNT option has been deprecated for INSERT, UPDATE, and DELETE statements. So too have the data types text, ntext, and image. Even the CREATE DEFAULT and DROP DEFAULT statements are (finally) approaching their sunset years.

 

The problem is that the ISNUMERIC function will sometimes call a value numeric that cannot be converted to a numeric data type, as with $456. It will even interpret values such as 7e9 and $., as numeric. A better solution is to use the TRY_CONVERT function to test whether a conversion will work before actually trying to convert the value

 

Some snips from Part 2

Be aware, however, that SQL Server adds the sp_ prefix to its system stored procedure names. As a result, when the database engine encounters any procedure using this prefix, it first tries to locate the it in the master database. If the procedure is user-defined, the database engine must waste processing cycles to locate it. In addition, the database ignores any user-defined procedures that share the same name as a system stored procedure. Plus, the sp_ prefix can result in confusion when reviewing database objects

 

GUIDs also require more memory and storage when compared to integers, which can be a factor with large data sets. However, if you must use GUIDs for your clustered index, at least consider using the NEWSEQUENTIALID function, rather than NEWID to help minimize some of the fragmentation. However, use NEWSEQUENTIALID only if you’re not concerned about privacy because the function makes it possible to guess the value of the next generated GUID.

This one builds on the case I have made in the past on the dangers of using GUID for row identifiers on sequential data.  It is easy to think that GUIDS are the end all, be all, but they can pose a performance penalty if you are dealing with data that needs to be accessed sequentially.  This is not to say that you should not use GUID data types.  Just that if you do use them, you should be aware of when it is a good idea, and when it is not.

As with previous examples, this statement is full of questionable code. For example, the first column, VendorID, is defined as the primary key and uses the UNIQUEIDENTIFIER data type, which means the column will store only GUID values. Because no clustered index is defined elsewhere on the table, the database engine will create a clustered index based on this column, which can result in significant page and index fragmentation, especially when used in conjunction with the NEWID function.

 

GUIDs also require more memory and storage when compared to integers, which can be a factor with large data sets. However, if you must use GUIDs for your clustered index, at least consider using the NEWSEQUENTIALID function, rather than NEWID to help minimize some of the fragmentation. However, use NEWSEQUENTIALID only if you’re not concerned about privacy because the function makes it possible to guess the value of the next generated GUID.

Source: The Basics of Good T-SQL Coding Style – Part 2: Defining Database Objects – Simple Talk

About the author

Brent is Principal Technology Strategist at Stone Technologies where he works with clients to develop manufacturing technology strategies that help them meet their business objectives. He believes that a cohesive technology strategy is a fundamental way to enable information to flow to the people that need it, enabling decision making backed with accurate information. Manufacturing adds some unique requirements to IT, and as a consultant, solution architect, and solution implementer, Brent works with clients to bring the capabilities of IT to the plant floor as a means to enable the manufacturing experts to be aware of manufacturing performance, and have the necessary information to make informed operational decisions.

Leave a Reply

%d bloggers like this: