SQL Server - Good practice how we create sp - Asked By Iqbal Singh on 03-Apr-09 04:28 AM

Hi everyone,

I just want to know wt is good practice to create a store proc. which take less number of cpu read. and wt is benefit of where  1=1 ,

It give me all record but is this good practice to use in sp

Select * From tblStudent_Nameit Where  1 =1

Good one. - [)ia6l0 iii replied to Iqbal Singh on 03-Apr-09 04:39 AM

We must first and foremost, target the databases and tables to store data efficiently. 

Given the case, you do that efficiently, there are many things that you can do to make your SP efficient. Some of them measures wont be feasible in certain environments too. 

There is no hard and fast rule that you need to comply these rules. but if you do, then you are good. 

You should follow conventions across your scripting, like naming of stored procedure, naming of variables, naming of blocks etc.

A good documentation of both simple and complex logics inside your stored procedure will not only help others, but you too, when you return to the sp for a glance. 

Break down into several modularized stored procedures, if it is one big.

Finally , use the Profiler tools to ensure that you dont write queries that perform bad table scans for little data.

Enable the system messages like Warnings and disable the "NoCount"
Santhosh N replied to Iqbal Singh on 03-Apr-09 04:46 AM

Its always better to check the execution plan of the SP for performance related issues and ways of improving the SP both in terms of maintainability and Performance fronts...

Check here on how to follow the exection plan and improve the SP for better performance..




re - Web Star replied to Iqbal Singh on 03-Apr-09 05:06 AM

this is a long topic for study which is best but

nothing is universal thing its depends upon condition like

Select * From tblStudent_Nameit 

this is good instead of this

Select * From tblStudent_Nameit Where  1 =1

because in excution plan u see last one is taking some operational time also

here are some tips very useful to follow while creating - Venkat K replied to Iqbal Singh on 03-Apr-09 06:18 AM

stored procedures:

As a common practice, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases, such as an identity column or some other column where the value is unique. In many cases, the primary key is the ideal column for a clustered index.

 Indexes should be measured on all columns that are frequently used in WHERE, ORDER BY, GROUP BY, TOP and DISTINCT clauses.

 Do not automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by the queries run against the table.

 For historical (static) tables, create the indexes with a FILLFACTOR and a PAD_INDEX of 100 to ensure there is no wasted space. This reduces disk I/O, helping to boost overall performance.

 Queries that return a single row are just as fast using a non-clustered index as a clustered index.

 Queries that return a range of rows are just as fast using a clustered index as a non-clustered index.

 Do not add more indexes on your OLTP tables to minimize the overhead that occurs with indexes during data modifications.

 Do not add the same index more than once on a table with different names.

 Drop all those indexes that are not used by the Query Optimizer, generally. You probably won't want to add an index to a table under the following conditions:

  • If the index is not used by the query optimizer. Use the Query Analyzer's "Show Execution Plan" option to see if your queries against a particular table use an index or not.
  • If the table is small, most likely indexes will not be used.
  • If the column(s) to be indexed are very wide.
  • If the column(s) are defined as TEXT, NTEXT or IMAGE data types.
  • If the table is rarely queried but insertion, updating is frequent.

� To provide up-to-date statistics, the query optimizer needs to make smart query optimization decisions. You will generally want to leave the "Auto Update Statistics" database option on. This helps to ensure that the optimizer statistics are valid, ensuring that queries are properly optimized when they are run.

 Keep the "width" of your indexes as narrow as possible. This reduces the size of the index and reduces the number of disk I/O reads required to read the index.

 If possible, try to create indexes on columns that have integer values instead of characters. Integer values use less overhead than character values.

 If you have two or more tables that are frequently joined together, then the columns used for the joins should have an appropriate index. If the columns used for the joins are not naturally compact, then consider adding surrogate keys to the tables that are compact in order to reduce the size of the keys. This will decrease I/O during the join process, which increases overall performance.

 When creating indexes, try to make them unique indexes if at all possible. SQL Server can often search through a unique index faster than a non-unique index. This is because, in a unique index, each row is unique and once the needed record is found, SQL Server doesn't have to look any further.

 If a particular query against a table is run infrequently and the addition of an index greatly speeds the performance of the query, but the performance of INSERTS, UPDATES and DELETES is negatively affected by the addition of the index, consider creating the index for the table for the duration of when the query is run and then dropping the index. An example of this is when monthly reports are run at the end of the month on an OLTP application.

 Avoid using FLOAT or REAL data types as primary keys, as they add unnecessary overhead that can hurt performance.

 If you want to boost the performance of a query that includes an AND operator in the WHERE clause, consider the following:

  • Of the search criteria in the WHERE clause, at least one of them should be based on a highly selective column that has an index.
  • If at least one of the search criteria in the WHERE clause is not highly selective, consider adding indexes to all of the columns referenced in the WHERE clause.
  • If none of the columns in the WHERE clause are selective enough to use an index on their own, consider creating a covering index for this query.

 The Query Optimizer will always perform a table scan or a clustered index scan on a table if the WHERE clause in the query contains an OR operator and if any of the referenced columns in the OR clause are not indexed (or do not have a useful index). Because of this, if you use many queries with OR clauses, you will want to ensure that each referenced column in the WHERE clause has an index.

 If you have a query that uses ORs and it is not making the best use of indexes, consider rewriting it as a UNION and then testing performance. Only through testing can you be sure that one version of your query will be faster than another.

 If you use the SOUNDEX function against a table column in a WHERE clause, the Query Optimizer will ignore any available indexes and perform a table scan.

 Queries that include either the DISTINCT or the GROUP BY clauses can be optimized by including appropriate indexes. Any of the following indexing strategies can be used:

  • Include a covering, non-clustered index (covering the appropriate columns) of the DISTINCT or the GROUP BY clauses.
  • Include a clustered index on the columns in the GROUP BY clause.
  • Include a clustered index on the columns found in the SELECT clause.
  • Adding appropriate indexes to queries that include DISTINCT or GROUP BY is most important for those queries that run often.

 Avoid clustered indexes on columns that are already "covered" by non-clustered indexes. A clustered index on a column that is already "covered" is redundant. Use the clustered index for columns that can better make use of it.

 Ideally a clustered index should be based on a single column (not multiple columns) that are as narrow as possible. This not only reduces the clustered index's physical size, it also reduces the physical size of non-clustered indexes and boosts SQL Server's overall performance.

 When you create a clustered index, try to create it as a unique clustered index, not a non-unique clustered index.

 SET NOCOUNT ON at the beginning of each stored procedure you write. This statement should be included in every stored procedure, trigger, etc. that you write.

 Keep Transact-SQL transactions as short as possible within a stored procedure. This helps to reduce the number of locks, helping to speed up the overall performance of your SQL Server application.

 If you are creating a stored procedure to run in a database other than the Master database, don't use the prefix sp_ in its name. This special prefix is reserved for system stored procedures. Although using this prefix will not prevent a user defined stored procedure from working, what it can do is to slow down its execution ever so slightly.

 Before you are done with your stored procedure code, review it for any unused code, parameters or variables that you may have forgotten to remove while you were making changes and remove them. Unused code just adds unnecessary bloat to your stored procedures, although it will not necessarily negatively affect performance of the stored procedure.

 For best performance, all objects that are called within the same stored procedure should be owned by the same object owner or schema, preferably dbo, and should also be referred to in the format of object_owner.object_name or schema_owner.object_name.

 When you need to execute a string of Transact-SQL, you should use the sp_executesql stored procedure instead of the EXECUTE statement.

 If you use input parameters in your stored procedures, you should validate all of them at the beginning of your stored procedure. This way, if there is a validation problem and the client application needs to be notified of the problem, it happens before any stored procedure processing takes place, preventing wasted effort and boosting performance.

 When calling a stored procedure from your application, it is important that you call it using its qualified name, for example:

exec dbo.myProc