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 [...]
Archive of entries posted by
Using DBCC INPUTBUFFER
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
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
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 [...]
A Query Method Faster Than BETWEEN
This is an invaluable trick that can be sometimes be used instead of the BETWEEN operator. A common place this can be used is in an historical table that contains EffectiveFrom & EffectiveTo dates. In these kinds of tables, many historical versions of a record can be stored. Each record in this scenario will contain [...]
SELECT INTO IDENTITY
Adding an identity column to a SELECT..INTO query is probably something I use nearly on a daily basis. I’m providing it here merely for reference purposes. It useful when you want to loop through a temporary table. SELECT id = IDENTITY(int,1,1) ,FirstName = pc.FirstName ,LastName = pc.LastName INTO #person_contact FROM Person.Contact pc WHERE EmailPromotion = [...]
Making a Procedure Available in all Databases
If you’ve ever wondered how to make a procedure available in any database, it’s actually pretty simple. If you create a procedure in the master database with the prefix of “sp_”, it will be callable from any database. I personally think this feature is great for utilities, however I would not use this for dependent [...]
Find Triggers and Associated Tables
These simple snippets of code will show all triggers in the current database, along with the tables they belong to. Unfortunately the INFORMATION_SCHEMA views do not show triggers so we need to revert to using less documented options. The proper way would be to use the INFORMATION_SCHEMA.TRIGGERS view, however SQL Server has yet to create [...]
Compare Stored Procedure Output by Creating a Test Harness
When optimizing procedures often times you need to rewrite the procedure in order to speed it up. However, when you’re finished how can you be sure that the result set is still the same? This article will show you how to run a thorough test to make sure. We’ll execute the old and new stored [...]
Avoiding IF.. ELSE by using WHERE EXISTS
When coming from a programming background, it’s natural to want to frame conditional operations within the familiar if.. else constructs. This happens in a lot of SQL code I have worked with (and I used to be a contributor also). The technique below is based on a common scenario. Say you want to insert new [...]
