When scripting out jobs using ssms (sql server management studio) the default script for a drop statement is to drop the job according to it’s job_id. This is not beneficial however when pushing code to different environments. Job_id’s are specific to the windows environment in which they are created. To get around this is easy however, you simply need to pass the @job_name parameter to the sp_delete_job stored procedure. [cc lang=”sql”] IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N’jobnamegoeshere’) EXEC sp_delete_job @job_name = N’jobnamegoeshere’ , @delete_unused_schedule=1 [/cc]
Continue reading ...
There are many factors to speeding up stored procedures. Knowing these in advance can save you and your company many hours of headaches. Using these practices when writing procedures, you can turn your SQL Server into a fine-tuned machine. This tutorial will cover the specifics to help you tune your procedures optimally. Know your tables The biggest way you can help SQL Server speed up your queries, is by helping it determine how to limit the result set as soon as possible. One of the biggest factors in this is in knowing the tables you are joining together. This translates to: How many records are in your tables? What are the best ways to join your tables? What are the existing indexes on the tables? Knowing the number of records in a table is very important. When joining tables using inner joins, you always want to join the smallest table first. Why you ask? Say you do not use a WHERE clause and you simply join three tables together. If you start your FROM clause with the largest table, then the first table SQL sees is a very large table, so it sets up an execution plan for a very large result set. Then let’s say the last of the 3 tables only has 10 records, by the time SQL visits that table it’s too late, the execution plan has been set to output 100,000 records, whereas if you specified the first table with 10 records after the FROM clause, […]
Continue reading ...
The substring function takes 3 arguments. The first argument is the actual string in which you want to extract from. The second argument is the start position, and the third argument is the number of characters to extract. [cc lang=”sql”] DECLARE @BaseString varchar(max) SET @BaseString = ‘Quick Brown Fox’ SELECT SUBSTRING ( @BaseString — The base string to extract from ,7 — Start Position ,5 — Length of Characters ) [/cc] The above query returns the value ‘Brown’. Note: This is equivalent to the Oracle function: substr
Continue reading ...
Many Oracle developers trying to find the SQL Server function compatible with their Contains clause will most likely end up on this page. Therefore, this page title is directed towards the Oracle developer rather than for the SQL Server’s Contains function which is used for full-text searching. The most similar function to Oracle’s contains is charindex. The usage is similar except the first two parameters are reversed: [cc lang=”sql”] DECLARE @BaseString varchar(max) SET @BaseString = ‘Quick Brown Fox’ SELECT CHARINDEX(‘Brown’, @BaseString, 1) [/cc] The result returns the integer value 7, indicating the character position for the string ‘grand’ within the @BaseString variable. The last parameter allows a start position to be specified.
Continue reading ...
Using a numbers table is helpful for many things. Like finding gaps in a supposed sequence of primary keys, or generating date ranges or any numerical range. In some cases, you will be in a production system that does not already contain a numbers table and you will also be unable to add one. In this situation, the dynamic numbers table comes in handy. [cc lang=”sql”] DECLARE @MaxNumber int = 5000 ;WITH Numbers AS ( SELECT 1 AS Num UNION ALL SELECT Num+1 FROM Numbers WHERE Num
Continue reading ...
