5 life hacks for more efficient SQL work

Name columns and tables with simple names

Use one word to name the table instead of two. If you still need to use a few words for the table, use underscores instead of spaces or dots in the title.

If you use dots in the names of objects, you get confused between the names of schemas and databases. If you use spaces, you will need to add quotation marks in the request for it to start.

Capitalize the names of columns and tables so that users don’t need to remember where to write which letter if you go to a case-sensitive database.

Handle dates in SQL correctly

Write dates in datetime format so that the databases work faster.

Dates stored in strings are more difficult to work with. Make sure there are no dates in the strings.

Do not divide the year, month, and day into separate columns. This makes it harder to write and filter queries.

Use UTC for your time zone. If you have a hybrid of non-UTC and UTC zones, understanding the data will be much more difficult.

Understand the execution order

When you understand the order in which queries are executed, you can understand how the query works, or why it does not start.

FROM - includes JOINs, so use a CTE or subquery to filter data first.

WHERE - restricts the attached dataset.

GROUP BY - collapses fields with aggregate functions (COUNT, MAX, SUM, AVG)

HAVING - performs the same function as WHERE with aggregated values.

SELECT - sets the values ​​and aggregations in the data array after grouping.

ORDER BY - Returns a table sorted by one or more columns.

LIMIT - sets how many rows to return so as not to output too much data.

Remember the NULL Limitations

NULL means that the value is unknown - but it is not zero and not empty. Therefore, if you are comparing NULL values ​​with NULL, it is difficult to understand something. Which query you ask in the code affects the strategy you need to choose.

Create a table correctly

When you create a table from a table, use SELECT TOP 0 to create a table structure before inserting data there. To do this, you need to perform two steps instead of one, but in the end the request will be processed faster:

insert into <table name2>

select [ID], [CreatedDate], [RegionName], [SalesPerson]

from <table name1>