usual high resource utilizing statements in a stored procedure in sql server

I want to know which开发者_Python百科 sql statements are usually high resource utilizing and high memory consuming amongst all the types of statements like select (with/without joins, where, etc), insert, update, delete, setting of variables, creating hash tables, conditional statements, looping statements, etc. It would be better if anyone can give with respect to sql server.

I think this is a very broad statement but it would be great if anyone can put some light on this.

It's important to remember that SQL is just a high-level language for describing the dataset you want, and what to do with it. It (deliberately) doesn't specify how to do anything. The actual operations are things like full table scans, hash joins etc which will have their own costs depending on the structure and contents of your database. The RDBMS will choose which operations to perform in order to carry out your query, using an optimiser to work out the best way to do it. You can add hints for the optimiser in your query (in SQL Server they usually take the form of an 'option' clause at the end of the query) but it's usually not a good idea to do this unless you have advanced knowledge of the database as the optimiser is pretty good at its job. If you want to see what the optimiser is going to do with a query, select the query and press ctrl+L in SQL Server Management Studio. This will show you not only what it's going to do, but how expensive it thinks each operation is. If you see an operation which is expensive and you think you can avoid or reduce by restructuring your query, that's a good place to start.

It's probably not the answer you want, but optimising queries is a difficult subject worthy of its own study. However, the following tips will help in general to build more efficient queries:

1. To use less memory, only select the columns you need (i.e. don't always select *). This makes a difference in large queries on tables with lots of rows.
2. Use indexes where they're available. Create indexes on columns that are often in the where clauses of your queries. Especially create indexes on primary and foreign keys. Remember that indexes can be quite large, so don't just spam them over everything.
3. If there's one column that's nearly always used to filter a table, create a clustered index on it. You can only have one clustered index per table.
4. Sometimes adding extra conditions to your where clause can make a query faster. For instance, if your table has an index on date and you know that the records you're looking for are from the last month, filtering on the month will reduce the number of rows the database has to sift through using the other conditions.
5. Keep your database statistics up to date. Not strictly to do with your queries, but if you run database statistics regularly, it will help the optimiser make better plans for your query.
6. Study the plans the optimiser makes for all your queries. Get a feel for how expensive each operation is on each of your tables. It won't make your queries faster, but hopefully over time it will make you write faster queries, especially if you Google all the things you don't understand in the plans ;)

Hope that helps.