Five SQL Server Tips


Five quick tips to improve your query performance on SQL  Server and simplify your schemas.

Keep Names Simple

  • Use one word for a table name instead of two. If you do need to use multiple words use underscores instead of spaces or full stops.
  • Having dots in names of objects will cause confusion between schema and database names and using spaces means you need to add quotes in your query to make it run.
  • Keep column and table names consistently in lowercase so users don’t have to remember which is which if you move to a database which is case sensitive.

Dealing With Dates In SQL Server

  • Convert datetimes to dates to improve performance. They can then be used in an index and take up less space.
  • It’s harder to work with dates that are stored as strings so make sure these never represent dates.
  • Don’t split out the year, month, and day in separate columns. This makes queries much harder to write and filter.
  • Always use UTC for your timezone. If you have a mix of non-UTC and UTC it makes understanding the data much more difficult.

Understand The Order Of Execution

  • Understanding query order can help you understand how a query runs or worse, why your query won’t run.


The Limitations Of NULL

  • NULL means that the value is unknown, not zero and not blank. This makes it difficult to compare values if you are comparing NULLs with NULLs.
  • Depending on what you are asking your code to do, influences the strategy you need to take. Read more about NULLs and how to tackle the problem.

Know how to create a table

  • When creating a table from a table use SELECT TOP 0 to create the structure of a table before inserting the data into it. It takes two steps instead of one but slashes processing time.

  • If updating a table with new data use the TRUNCATE command. It deletes all of the rows from the table without deleting the format and headers


There you have it, five SQL Server tips to simplify schemas and improve performance.


Photo by Min An from Pexels

 

Bitnami