Sql Server - Create a copy of database and Renaming it

Steps to attach a database with different name:

1. Detach database from local PC
2. Copy the mdf and ldf files and rename them.
3. Copy\Move the files to the server directory where the original Database is stored.
4. Use the below code

USE [master]
GO
CREATE DATABASE [example_db] ON ( FILENAME = N'D:\Data\example_db.mdf' ),
( FILENAME = N'E:\Logs\example_db_log.ldf' )FOR ATTACH
GO

you can also copy by using the Sql-server Management studio:

• go to database and tasks->Copy Database
• Select the source and destination server (same if you wants to make a copy on the same server)
• change the destination database name to required.
• Done

Stored procedure slow when called from web, fast from Management Studio

Some time this problem occurs due to compiled query plan became corrupt, or invalid.

Stored procedure slow when called from web, fast from Management Studio becuase Basically connections through Sql Server Management Studio (SSMS) by default have SET ARITHABORT ON. ADO.NET connections do not.

The main difference when running with or without this setting, a different query plan is created for the both stored procedure calls. In case of ARITHABORT was OFF, the SSMS command would use the pre-compiled cached query plan that the ADO.NET connection was using (compiled query plan became corrupt, or invalid.), and therefore timeout.

Use following Command to refresh the query Plans.

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

Query parameterization promotes the reuse of cached execution plans, thereby avoiding unnecessary compilations, and reducing the number of ad-hoc queries in the plan cache. This is called Parameter Snifting.

Sql-server using Parameter Snifting (Default Enabled).

You can change the Behaviour of Parameter snifting, is you have knowledge of Parameter snifting.

Remove Duplicate Data from Sql-server 2005

First Declare a temp table
DECLARE @table Table(column1 INT, Column2 INT, total int)

insert duplicate data to temp table
insert INTO @table SELECT column1, Column2,count(*) FROM Catalogue.MailingListCategoryContacts GROUP BY column1, Column2 HAVING count(*) > 1

Delete from all the data from original table
DELETE FROM Catalogue.MailingListCategoryContacts WHERE column1 IN (SELECT column1 FROM @table)

again insert into the original table
INSERT INTO Catalogue.MailingListCategoryContacts(column1, Column2) SELECT column1, Column2 FROM @table

how to add A DAY IN DATE, SQL-SERVER

DATEADD(type, value, date)

• date is the date you want to manipulate
• value is the integere value you want to add (or subtract if you provide a negative number)
• type is one of:
  
  • yy, yyyy: year
  • qq, q: quarter
  • mm, m: month
  • dy, y: day of year
  • dd, d: day
  • wk, ww: week
  • dw, w: weekday
  • hh: hour
  • mi, n: minute
  • ss or s: second
  • ms: millisecond
  • mcs: microsecond
  • ns: nanosecond

SELECT DATEADD(dd, 1, GETDATE()) will return a current date + 1 day

Sql - server - Find Last updated stored procedures

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
ORDER BY modify_date desc, create_date desc