Naming conventions for stored procedures

You should always use consistend naming conventions (no sp_ prefix). Beside of the documented recommendation of Microsoft for possible sp_ naming conflicts for future system procedures there is an other important reason:

If you execute a sp_ prefix stored procedure the SQL Server searches the stored procedure in the sys-namespace of the master database by each exec-call. This may causes some performance issues.

A possible way to use consistend naming conventions could be usp_ or other specific prefixes (for example three characters: get, set, chk).

Print Friendly, PDF & Email

Non clustered index on NON-SEQUENTIAL unique identifier vs. SEQUENTIAL unique identifier

If you have a non clustered index on unique identifier you might have identified a serious performance problem.

The SQL server creates for each non clustered index a separate table. By inserting a unique identifier with “NEWUID() stored procedure” each new data row has to be inserted somewhere within the table which needs a lot of performance and creates fragmentation of the table. In this case you need a function to be sure the insertion process will be made at the end of the table.

Continue reading

Print Friendly, PDF & Email