Best Practices to Use while Writing SQL
- Always use table aliases when your SQL statement involves more than one source
- Do not use column number in order by and group by clause
- use trim function in sql
If more than one table is involved in a from clause, each column name must be qualified using either the complete table name or an alias. The alias is preferred. It is more human readable to use aliases instead of writing columns with no table information.
Always use column names in an order by clause. Avoid positional references. Consider the following example in which the second query is more readable than the first one:
SELECT id, name FROM table_name ORDER BY 2 SELECT id, name FROM table_name ORDER BY name
trim has three types
- ltrim : trim left spaces
- rtrim : trim right spaces
- trim : trim both spaces
Sometime it might happen your column contains an extra spaces after the value in the column.
for example : consider a table
but the actual value stored in the column may be “XYZ ” some spaces after value. In This case
your select query : “select * from table_name where name = ‘XYZ’ ” won’t work. So use trim function
“select * from table_name where trim(name) = ‘XYZ’ ”
Note : trim function may differ with different database.
Always specify the target columns when executing an insert command. This helps in avoiding problems when the table structure changes (like adding or dropping a column). Consider the following table:
CREATE TABLE table_name ( id INT PRIMARY KEY, name VARCHAR(25) )
Here’s an INSERT statement without a column list , that works perfectly:
INSERT INTO table_name VALUES (1, ‘XYZ’)
Now, let’s add a new column to this table:
ALTER TABLE table_name ADD categoryClass bit
Now run the above INSERT statement. You get the following error from SQL Server: Insert Error: Column name or number of supplied values does not match table definition. This problem can be avoided by writing an INSERT statement with a column list as shown below:
INSERT INTO EuropeanCountries (id, name) VALUES (2, ‘ABC’)
With ANSI joins, the WHERE clause is used only for filtering data. Where as with older style joins, the WHERE clause handles both the join condition and filtering data. Furthermore ANSI join syntax supports the full outer join. The first of the following two queries shows the old style join, while the second one shows the new ANSI join syntax:
-- old style join SELECT a.Au_id, t.Title FROM TITLES t, AUTHORS a, TITLEAUTHOR ta WHERE a.Au_id = ta.Au_id AND ta.Title_id = t.Title_id AND t.Title LIKE ‘%Computer%’ --ANSI join syntax SELECT a.Au_id, t.Title FROM AUTHORS a INNER JOIN TITLEAUTHOR ta ON a.Au_id = ta.Au_id INNER JOIN TITLES t ON ta.Title_id = t.Title_id WHERE t.Title LIKE ‘%Computer%’
The prefix sp_ is reserved for system stored procedure that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the owner. So you can really save time in locating the stored procedure by avoiding the “sp_” prefix.
The formatting of SQL code may not seem that important,but consistent formatting makes it easier for others to scan and understand your code. SQL statements have a structure, and having that structure be visually evident makes it much easier to locate and verify various parts of the statements. Uniform formatting also makes it much easier to add sections to and remove them from complex T-SQL statements for debugging purposes. Instant SQL Formatter is a free online SQL tidy tool that makes your SQL script readable instantly.