SQL Server Planet

User Defined Functions and Performance

Derek Dieter — Wed, 25 Apr 2012 06:55:07 +0000

There is definitely a lack of awareness in the SQL world regarding the use of user defined functions and the potential performance hit they can have when using within your queries. Don’t get me wrong, I would love nothing more than to be able to centralize my commonly used code into functions for reuse. In [...]

Increase Job History

Derek Dieter — Mon, 09 Apr 2012 04:35:43 +0000

The SQL Server Agent by default only keeps 1000 rows of history for all jobs. There is also a limitation for each job to keep only 100 rows each. These rows do not only include the a row for the entire job, but the rows for each step in the job also. These rows can [...]

Using DBCC INPUTBUFFER

Derek Dieter — Mon, 09 Apr 2012 04:03:54 +0000

This command shows the last statement executed for a particular SPID. This statement is mostly used for troubleshooting purposes to determine the exact command a particular SPID is running. You must be a member of the sys admin fixed server role, or have VIEW SERVER STATE permission (if SQL 2005+). As for a real world [...]

Creating Unique Random Numbers

Derek Dieter — Fri, 06 Apr 2012 10:51:59 +0000

This is typically a hot topic and I’m going to try and tackle it with my rudimentary math. Ultimately given time and the frequency of generation, there is no such thing as a completely unique random number. There will always be some chance that a random number can be regenerated even though the chances do [...]

Overcome Divide by Zero Using NULLIF

Derek Dieter — Fri, 06 Apr 2012 05:08:41 +0000

Anytime we are dividing we need to think of the divide by zero scenario. Even if you think you will never encounter this with your result set, it’s advisable to guard against it because when divide by zero is encountered, an error is thrown. The best method I’ve found to overcome this is by using [...]