28 Oct SQL – 8 tips for developers

SQL – 8 tips for developers

SQL Developer tips

1) All tables should have a primary key – this may be a composite key (multiple columns)

• This does not apply to #temp tables

2) INSERT statements should ALWAYS have a column list.

• SQL syntax allows the column list to be omitted where all columns in the table are being inserted into (in order). BUT YOU SHOULD AVOID DOING THIS
• If more columns are subsequently added to the table the INSERT will break
• If columns are reordered then data will be inserted into the wrong columns

3) SELECT statements should ALWAYS have an ORDER BY clause which generates consistent ordering of returned data (i.e. the ORDER BY columns should be unique for each row)

• Failure to do this can lead to inconsistent results from the same SELECT which can cause bugs which are extremely difficult to solve
• Inner SELECTs and EXISTS clauses do not need an ORDER BY

4) IDENTITY columns should not be overused

• The use of an IDENTITY column to provide a unique primary key in tables which are expected to grow over time is sensible (e.g. a table containing a collection of bookings or customers)
• IDENTITY columns should NOT be used to define primary keys in static lookup tables where the value of the primary key has a specific meaning (e.g. a table containing a collection of booking statuses and text to describe their use)

5) DON’T use the dbo schema or ALWAYS use the dbo schema

• In a database containing more than just a few tables it is sensible to group tables into meaningful schema (e.g. Booking, User, Invoice)
• Objects in the dbo schema can be referenced without explicitly including the dbo prefix which can lead to sloppy SQL which is harder to follow
  If the dbo schema is used along side other schemas then any objects in the schema should be explicitly prefixed when referenced
• In trivial databases with just a few tables and no requirement for schemas, no objects should be prefixed with a schema (which will lead to all objects residing in the dbo schema) – but when your trivial database grows into something else you may regret this

6) Be consistent with pluralisation of table names

• As a personal preference I believe all tables should be named in the SINGULAR (e.g. a table containing a collection of bookings would be named [Booking] or perhaps [Booking].[Booking]
• Despite the fact that a table stores multiple objects of a given type, the name of the table should reflect the name of the object
• A table containing all of a user’s settings, however, would quite rightly be called [UserSettings] or maybe [User].[Settings] since a single record would contain multiple settings
• There’s a lot of debate about this sort of think and the vote seems to be fairly evenly divided between singularization and pluralization
• So if you prefer pluralised tables (e.g. [Bookings]) then that’s fine BUT BE CONSISTENT – make sure you pluralise everything – but be aware you may end up with a table called [UserSettingss] or [UserSettingses]

7) Don’t over abbreviate object and column names

• Common abbreviations such as Admin or Config can be used to keep names short, but avoid making up your own abbreviations
• For example InvoicePaidDate makes for better reading than InvPdDt

8) Keep indexes to a sensible minimum

• Indexes on foreign key columns are usually a very good idea and date/time columns will often benefit from being indexed
• But don’t be tempted to create all the indexes SSMS suggests when running a query
• Remember, each index comes with an overhead during inserts, updates and deletes and too many indexes will do more harm than good

In Conclusion

Thats it for our 8 tips for SQL Developers. Just the tip of the iceberg – yes! SQL has wealth of possibilities for its users, check out our free download if you are working with SQL databases on a regular basis. Tiny steps are what you need to take if you are new Structured Query Language (SQL) and there is always more to learn, as the saying goes “only a fool knows everything!” Good luck and let us know your thoughts.

New to SQL? Check out this handy guide.

Author

Steve Pratt

Steve started programming on a BBC Micro Model B at the age of 14. He didn’t want to work for a living but figured he would make most money in the least amount of time as a programmer.

Still incredibly lazy, Steve has managed to use the power of code to make his job as easy as possible. He has worked on a raft of projects with numerous clients including pharmaceutical, FINTEC, Local Authorities and hospitality booking platforms, to name but a few.

He has two daughters, lives in Cornwall, UK and loves surfing.