Run a Stored Procedure when SQL Server starts

Although it is not a very good idea, you can set up a stored procedure to be started when SQL Server starts. Follow these steps:

Step 1: When you are creating the Stored Procedure, make sure the database context is set to Master database

USE master
GO
CREATE PROCEDURE dbo.usp_SomeProcForStart
AS
-- Stored Procedure Definition
GO

Note that your stored procedure cannot have any parameters and the owner is ‘dbo’

Step 2: The next step is to use the sp_procoption to set the stored procedure dbo.usp_SomeProcForStart to execute when SQL Server service starts:

EXEC sp_procoption 'usp_SomeProcForStart', 'startup', 'true'

To turn it off so that this stored procedure ‘does not’ execute when SQL Server service starts, use this command

EXEC sp_procoption 'usp_SomeProcForStart', 'startup', 'false'

Note: You may also want to take a look at sp_configure system stored proc in case your database is not configure for automatic execution of stored procedures.

Display DDL Triggers in your SQL Server Database

To display DDL Triggers in your database (SQL Server 2005/2008), use the sys.triggers catalog view as shown below:

-- Database-scoped DDL triggers in the current database
SELECT name as TriggerName, create_date as CreationDate,
type_desc as [TriggerType(SQLorCLR)], is_disabled
FROM sys.triggers
WHERE parent_class_desc = 'DATABASE'
ORDER BY name

The query shown above displays all the database-scoped triggers.

In order to view the server-scoped triggers, use the sys.servertriggers and sys.server_trigger_events

-- Server-scoped triggers in the current database
SELECT name as TriggerName, create_date as CreationDate,
st.type_desc [TriggerType(SQLorCLR)], is_disabled
FROM sys.server_triggers st
INNER JOIN sys.server_trigger_events ste ON
st.object_id = ste.object_id

You can also check a related post Display DML Triggers in your SQL Server Database

List of SQL Server Forums to ask your Questions

Forums is a great place to hold online discussions and look out for some amazing resources and useful tips. Here are the 8 best SQL Server forums (in no particular order) for DBA’s, BI and T-SQL Developers to ask your questions. These forums are definitely a place to hang around if you are serious in making a career in SQL Server Databases.

SQL Server MSDN Forums

SQL Team Forums

StackOverflow SQLServer Forums (also search for sql-server-2005 and sql-server-2008)

SQL Server Central Forums

SQL Server Performance Forums

Bytes - SQL Server Forums

ASP Free Database Forums

Experts-Exchange MS SQL Server Zone (Paid areas)

Please note there may be many more good forums out there. The ones listed above are only the ones I know of. Feel free to suggest any other useful forums you know of, using the comments section.

Change an Existing Index in SQL Server

With SQL Server, you can use the DROP_EXISTING clause to rebuild the index, add or drop columns, modify options, modify column sort order, or change the partition scheme or filegroup. Let us see how to rebuild the index and then add a column to an existing index

Drop and recreate an existing non-clustered index without changing the index definition

CREATE NONCLUSTERED INDEX Idx_TableName_SomeColumnID ON
TableName (SomeColumnID ASC)
WITH (DROP_EXISTING = ON)
GO

Rebuild an existing non-clustered index and adding a new column to the existing index

CREATE NONCLUSTERED INDEX Idx_TableName_SomeID ON
TableName (SomeOtherColumn, SomeColumnID ASC)
WITH (DROP_EXISTING = ON)
GO

Read more on the DROP_EXISTING clause (search DROP_EXISTING on the page)

Using SORT_IN_TEMPDB to improve Index Performance

The SORT_IN_TEMPDB option directs the index creation processing to the tempdb database, instead of the database in use.

This option may increase rebuild performance for large tables on a high activity environment, if the tempdb system database is located on a separate disk. Although the index creation time may decrease, the disk space requirements increase while using this option.

Note: Make sure a dedicated disk is allotted to the tempdb database and it has sufficient disk space. When using the SORT_IN_TEMPDB option, DBA’s usually calculate the disk space requirement while doing capacity planning.

By default SORT_IN_TEMPDB is OFF. You can enable this option as shown below:

CREATE NONCLUSTERED INDEX AddIndexNmHere ON
DBNm.TableNm (ColumnName)
WITH (SORT_IN_TEMPDB = ON)

Convert VarChar to Currency in SQL Server

To convert a varchar to currency, the recommended practice is to do the formatting in the front end.

However if that option is not available to you, you can use the following T-SQL code to do the formatting. In the code shown below, we are converting varchar into US Dollar currency.

DECLARE @t TABLE(amount decimal(12,2))
 INSERT INTO @t
 SELECT 27450 union all
 SELECT 2841.2 union all
 SELECT 8786723.62 union all
 SELECT 8723

SELECT amount,'$'+ CONVERT(varchar(100),
CAST(amount as money),1) as converted_amount
FROM @t

Note : The value must be converted to money datatype before the formatting

OUTPUT

When to use STUFF instead of REPLACE – SQL Server

The STUFF function in SQL Server ‘inserts’ a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

REPLACE on the other hand replaces all occurrences of a specified string value with another string value.

The STUFF function is useful when you are not sure of the characters to replace, but you do know the position of the character. I saw an excellent implementation of this function over here by GMastros and Even. Here’s the code with a practical usage of STUFF:

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String NVARCHAR(MAX),
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = STUFF(@String, PatIndex(@MatchExpression,
@String), 1, '')

    RETURN @String

END

SELECT dbo.fn_StripCharacters('a1$%s#22d&39#f4$', '^a-z')
as OnlyAlphabets

OUTPUT

Count the Number of Occurrence of Characters in a String – SQL Server Challenge

BeyondRelational.com is back with another challenge this week. This challenge is all about counting the number of occurrence of characters in a string using a SET based query. The task is to list all the characters and count of that character within the given string

Challenge URL: Count the Number of Occurrence of Characters in a String

I had written a similar article sometime back which can give you some hints on how to solve the challenge. Check the article here Count Occurrences of Characters in a String – SQL Server

DECLARE @longWord varchar(28)='AntiDisEstablishmentArianism'

SELECT CountCharacters =
DATALENGTH(@longWord)
- DATALENGTH (REPLACE(REPLACE(LOWER(@longWord),'a',''),'i',''))

Remember the code shown above is just a hint on counting character occurrence and not the solution. Read the challenge and its rules carefully over here.

Calculate Interest on an Amount Between two Dates using SQL Server

We were working on a financial calculation recently where given two dates, the interest had to be calculated for a given amount. Thank to my colleague Satyam to help me out with the query

Here we are calculating the interest accumulated on the amount 395.80 at the rate of 15% per annum between 09-22-2009 and 07-13-2010. Please note that the interest rate calculated here is based on the business rules we had and the output expected. It may be a different in your case, for which you will have to modify the query.

The formula is as follows

Interest = Amount * InterestRate^(Days/365.25)

Query

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
DECLARE @Amount Decimal(6,3)

SET @StartDate = '2009-09-22'
SET @EndDate = '2010-07-13'
SET @Amount = 395.80


SELECT
@Amount*(POWER(1.1500, CONVERT(NUMERIC(8,3),
DATEDIFF(d, @StartDate, @EndDate)/365.25))) - @Amount
as TotalInterest

In the query shown above, we are using the POWER function which returns the value of the given expression to the specified power. Here are Convert the Date Difference into numeric value to provide it to the POWER function which comes out to be .805 i.e POWER(1.1500, .805). The interest rate is 15%,

Effectively this query comes out to be

SELECT @Amount * (POWER(1.1500, .805)) - @Amount
as TotalInterest

or

SELECT @Amount * 1.1191 - @Amount

OUTPUT

SQL Server Migration Assistant

Microsoft SQL Server Migration Assistant (SSMA) is a toolkit that dramatically cuts the effort, cost, and risk of migrating from databases like Oracle, Access, MySQL and Sybase to SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2 and SQL Azure.

Here are the download links:

SSMA for Oracle v4.2 - migrate from Oracle to SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2

SSMA for Access v4.2 - migrate from Access to SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Azure

SSMA for MySQL v1.0 - migrate from MySQL to SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Azure

SSMA for Sybase v4.2 - migrate from Sybase ASE to SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2

Working with Different Time Zones in SQL Server 2008

SQL Server 2008 introduces the new DATETIMEOFFSET data type that provides a time zone offset for working with dates and times in different time zones.

Note: SQL Server 2008 adds four new date and time data types: date, time, datetime2, and datetimeoffset.

The current date and time (with the offset) can be obtained using SYSDATETIMEOFFSET.To convert values from one timezone to another, you can use the SWITCHOFFSET function. Let us see this in action while converting your local timezone to Pacific Standard Time (US West Coast; Los Angeles, CA)

DECLARE @local DATETIMEOFFSET = SYSDATETIMEOFFSET()
SELECT SYSDATETIMEOFFSET() as 'CurrentDT-W/Offset'
SELECT SWITCHOFFSET (@local, '-08:00') as 'ConvertedDT-W/Offset'

OUTPUT

Note: When you are working with a DateTimeOffset, you can specify an offset that complies with the ISO 8601 standard.

SQL Server 2008 R2 Monitoring Management Pack

Microsoft recently released an updated version 6.1.314.36 of its SQL Server Management Pack to sync with the original release of Microsoft SQL Server 2008 R2. This pack provides the capabilities for Operations Manager 2007 SP1 and R2 to discover SQL Server 2005, 2008, and 2008 R2. It monitors SQL Server components such as database engine instances, databases, and SQL Server agents.

Here’s a feature summary of the SQL Server Monitoring Management Pack quoted over here

• Support for Enterprise, Standard and Express editions of SQL Server 2005, 2008, and 2008 R2 and 32bit, 64bit and ia64 architectures.
• Support for both simple and complex SQL configurations such as clustered installations, multiple instances and 32bit roles running on a 64bit OS. For full details on supported configurations refer to the guide included with the management pack.
• Discovery and monitoring of SQL Server roles such as DB Engine, Reporting Services, Analysis Services, Integrations Services.
• Discovery of SQL components such as databases, the SQL Agent and SQL jobs.
• Views covering areas such as database free space, SQL related performance, SQL related alerts, and lists of the various SQL roles and components which are discovered and their related state.
• Discovery and basic monitoring for SQL Server Reporting Services and Integration Services.
• Reports for longer-term analysis of common problem areas related to SQL Server such as SQL Server lock analysis and top deadlocked databases, SQL Server service pack levels across discovered roles, user connection activity. Likewise the generic reports from the Microsoft Generic Report Library can be used against the roles and components discovered by the SQL MPs to review availability and performance across many systems and over longer periods of time.
• Role and component specific tasks which provide access to common tools, triage information, or corrective actions without needing to leave the Operations Console in most cases.
• Monitoring of databases covers database status, database free space, log shipping monitoring for both the source and destination, and transaction log free space.
• Monitoring of key SQL related services.
• Monitoring for persistent SPID blocking.
• Monitoring of numerous SQL events and performance data points. Alerts bring the issue to your attention and provide knowledge on the impact and possible resolutions.
• A low-privilege configuration for discovery and monitoring that eliminates the need for SQL sysadmin, dbo, db_owner, and box admin privileges.

Download Link: SQL Server Management Pack

Different ways to get Identity of New Inserted Rows in SQL Server

There are different methods to know the Identity Value of a newly added row.

Let us consider the following example:

--Create a Table
CREATE TABLE test
(
id int identity(1,1),
names varchar(100)
)

--Insert Data
INSERT INTO test(names) SELECT 'testing'

--Get Identity Value that is Populated in the Current Scope
SELECT scope_identity()


--Get Identity value that is Populated in the Current Session
SELECT @@identity

--Get Identity value that is Populated in the Table
--Regardless of Scope and Session
SELECT ident_current('test')

Note that first two methods wont give correct values if data are added to the ‘different tables’.

OUTPUT

Synchronize SQL Azure with SQL Server using Sync Framework

SQL Azure Database is a cloud database service from Microsoft. Microsoft Sync Framework is a comprehensive synchronization platform enabling collaboration and offline for applications, services and devices.

Microsoft recently released a document on best practices on synchronizing SQL Azure with SQL Server using Sync Framework.

You can download the document here Sync Framework for SQL Azure

Converting Ntext column to Nvarchar(max)

In this short post, I will show you how to convert ntext column to nvarchar(max). Let us consider the following simple temporary table

create table #t(i int, n ntext)
insert into #t(i,n)
select 1,'testing' union all
select 1,'Français' union all
select 1,char(13)+char(10)+'testing'
select * from #t

As the datatype of column ‘n’ is ntext, it is not possible to apply the len() function. So on running the following code

select len(n) from #t

you get the following error

Also it is not possible to apply many string functions directly on the ntext datatype. So let us change the column ‘n’ datatype to nvarchar(max)

alter table #t
alter column n nvarchar(max)
select * from #t

Now it is possible to apply the len() function and string functions too

select len(n) from #t

Both these datatypes store pointer in the row than the actual data. However you can set option to have data in the row also. These points are described well at this Knowledge base.Make sure to read this

http://msdn.microsoft.com/en-US/library/ms189087.aspx