Tag: SQL Server
Clone SQL Server database
Option 1 Detach Original Database, copy its MDF and LDF files and create a new Database from copied files. USE master; GO EXEC sp_detach_db @dbname = N'OriginalDB'; GO copy "c:\\OriginalDB.mdf" "c:\\NewDB.mdf" copy "c:\\OriginalDB_log.ldf" "c:\\NewDB_log.ldf" USE master; GO CREATE DATABASE OriginalDB ON (FILENAME = 'C:\\OriginalDB.mdf'), (FILENAME = 'C:\\OriginalDB_log.ldf') FOR ATTACH; GO CREATE DATABASE NewDB ON (FILENAME […]
SQL Server format date
Format Date in SQL Server by using a user defined functions. /* author: Rizwan Ansari */ create function dbo.fnFormatDate (@datetime datetime, @format varchar(35)) returns varchar(35) as begin declare @date varchar(35) set @date = @format if (charindex ('yyyy',@date) > 0) set @date = replace(@date, 'yyyy', datename(YY, @datetime)) else if (charindex ('yy',@date) > 0) set @date = […]
Using comma separated value CSV parameter strings in SQL IN clauses
Below function when called with a CSV list returns a temporary table which can be used in “IN” of WHERE clause /****** Object: User Defined Function [dbo].[CSVTable] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[CSVTable] (@InStr VARCHAR(MAX)) RETURNS @TempTab TABLE (id varchar(10) not null) AS BEGIN SET @InStr = REPLACE(@InStr + […]
Store historical records in a history table in SQL Server
You can maintain historical changes made in a table by creating another table with the same schema which holds historical data. SomeTable is the base table containing current data rows SomeTable-History has exact same schema with 2 additional columns trans_type varchar(10) date_time datetime and this table will hold historical data Below triggers created on SomeTable […]
SQL Server Handle Transaction and Try Catch
SQL Server handles exceptions just like any programming language. Below is a stub to help understand SQL exception handling and you can use in your Procedures as a starting point. BEGIN TRAN BEGIN TRY EXEC P1 EXEC P2 COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN DECLARE @ErMessage NVARCHAR(2048), @ErSeverity INT, @ErState INT SELECT @ErMessage […]
SQL SERVER – Remove the last character in a string
Sometimes it is required to remove last character usually a comma from SQL string, below are 3 way to achieve it. Option 1: Using LEFT function DECLARE @String as VARCHAR(50) SET @String='1,2,3,4,5,' SELECT @String As [String] ,LEFT(@String,LEN(@String)-1) As [Last Character removed from string] GO Option 2: Using STUFF function DECLARE @String as VARCHAR(50) SET @String='1,2,3,4,5,' […]
SQL Server CONVERT() Function
Definition and Usage The CONVERT() function is a general function that converts an expression of one data type to another. The CONVERT() function can be used to display date/time data in different formats. Syntax CONVERT(data_type(length),expression,style) Value Description data_type(length) Specifies the target data type (with an optional length) expression Specifies the value to be converted style […]
SQL Snippets
Rounding off to 2 decimal places cast(round([value],2) as decimal(18,2)) Compare only Year from datetime field select * from [table_name] where year(date_time_field) = year(getdate()) check if a SQL server string is null or empty ISNULL(NULLIF(text, ''), '') AS Offer_Text The differences between LEN and DATALENGTH in SQL Server LEN Returns the number of characters, rather than […]
SQL Joins
Comprehensive image to show how SQL joins work