SQL Server Best Practise -- Writing Queries.

1.Always Refer objects with their schema qualified name.It avoids overhead in resolving the names.
Example:Consider a tables called orders under the sales schema
if you execute the query given below
select * from orders
SQL Server first try to find the table under the default schema of the user logged in , then it will search in remianing objects.if the same object is present in two different schemas then query returns an error.
Why give this overhead.
Always use two part identifies when referring to objects in the database.
Like select * from sales.orders

2.it is good practice to add semicolon at the end of each statement.Not All the statement in SQL Server requires a semicolon at the end of each statement.But some statements in SQL Server (like the statement before the CTE).

3.Don't use the follwing aliasing technique.
select Column_Name Alias_Name from table
It is always good practice to use AS keyword when aliasing or = .They make the code more readable and less prone to errors
4.It is recommended that you list all the columns in the select list instead of using Asteric (*) even if you want all the columns from the table for the following reasons
(i) To avoiding errors caused by schema changes
(ii) In terms of performance listing all the columns is better than using "*" although it is very minimal to negligible comared to total cost of the query.
Any ways it is a win win situation using column names instead od Asteric
5.T sql allows you to specify in th ORDER BY clause ordinal positions of the columns based on the order in whihc they appea in the select list.
you can use
SELECT id,qty FROM orders ORDER BY id,qty
or
SELECT id,qty FROM orders ORDER BY 1,2
This is a bad programming practice for two reasons
(i) Relational model does not guarantee column order in a set.(but T-SQL Does)
(ii) if you choose to modify the select list and forgot to modify the order by clause this will throw an error.





More to be Posted....