Speaking about Stored procedures, do not prefix your stored procedure names with 'sp_'. This keeps the data access clean and consistent across all the modules of your application, and at the same time centralizing the business logic within the database. Instead, create stored procedures, and let your applications access these stored procedures. Where as with older style joins, the WHERE clause handles both the join condition and filtering data.ĭo not let your front-end applications query/manipulate the data directly using SELECT or INSERT/UPDATE/DELETE statements. With ANSI joins, the WHERE clause is used only for filtering data. Use the more readable ANSI-Standard Join clauses instead of the old style joins. Choose the right indexes on the right columns. Make sure your queries do an 'Index seek' instead of an 'Index scan' or a 'Table scan.' A table scan or an index scan is a very bad thing and should be avoided where possible. Cursors can often be avoided by using SELECT statements instead.Īlways use the graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze your queries. Always stick to a 'set-based approach' instead of a 'procedural approach' for accessing and manipulating data.

Try to avoid server side cursors as much as possible. Try to use always the smallest datatype that you can and index all the fields most used in queries. The type of answers that I am looking for are:īasically those little things that make the coding experience a bit more efficient and pleasant. I am not looking for tips for writing efficient TSQL code but rather for advice on how to efficiently use Management Studio to speed up the coding process itself. I am afraid that I could have been misunderstood by some of you. The best habits I would normally pick from working together with other people which I cannot do now. Since I started working with SQL Server I have been left on my own. with time significantly improved our efficiency of writing code. Picking up new shortcuts, code snippets etc. I remember how many small things we could learn from each other when working together on the code. I used to work in a place where a common practice was to use Pair Programming.

It is not currently accepting new answers or interactions. This question and its answers are locked because the question is off-topic but has historical significance.

Sql prompt 9 hints