Total Pageviews

Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Tuesday, May 10, 2011

Capitalize the First Character of Each Word using queries

I have found t-sql challenges question where they were asked to provide a solution of oracle INITCAP function in sql.

I was started to find the solution of INITCAP function & found lots of user defined function.

But here I have shared a solution where queries doesn't use any user defined function as well as any variable.

--First of all create one temp table    
DECLARE @t TABLE(ID INT IDENTITY, Data VARCHAR(1000))

--insert the sample data
INSERT INTO @t 
SELECT 'yogesh bhadauriya' 


--use the Common table expression
;with CTE AS
(

    --anchor query where we are finding the first character for replacing with uppercase
    SELECT Cast(STUFF(LOWER(Data), 1, 1, UPPER(LEFT(Data, 1))) As varchar(max)) As Data,
    PATINDEX('%[^a-zA-Z][a-z]%', Data COLLATE Latin1_General_Bin) As Position,ID,Data As OldData
    From @t
    
    Union All
    
   --recursive query
    SELECT Cast(STUFF(CTE.Data, Position, 2, UPPER(SUBSTRING(CTE.Data, CTE.Position, 2))) As Varchar(max)) as Data,
    Cast(PATINDEX('%[^a-zA-Z][a-z]%', CTE.Data COLLATE Latin1_General_Bin) As Int) As Position,CTE.ID,T.Data As OldData
    From @t As T
    Inner Join CTE On CTE.ID=CTE.ID
    where CTE.Data is not null
)
Select ID,OldData As Data,Data As Results
From CTE
where Data is not null
and Position=0


---output
--Id Data    Results
--1 yogesh bhadauriya Yogesh Bhadauriya
    

Friday, April 29, 2011

User defined function to convert the number into charcter

How to convert the numeric value into its specific character? If this is your question, than create the following User Defined Function into your database.


--create the following function 
CREATE FUNCTION dbo.udf_Num_ToWords 
(
 @Number Numeric (38, 0) -- Input number with as many as 18 digits

) RETURNS VARCHAR(8000) 
/*
* Converts a integer number as large as 34 digits into the 
* equivalent words.  The first letter is capitalized.
*
* Attribution: Based on NumberToWords by Srinivas Sampath
*        as revised by Nick Barclay
*
* Example:
select dbo.udf_Num_ToWords (1234567890) + CHAR(10)
      +  dbo.udf_Num_ToWords (0) + CHAR(10)
      +  dbo.udf_Num_ToWords (123) + CHAR(10)
select dbo.udf_Num_ToWords(76543210987654321098765432109876543210)
 
DECLARE @i numeric (38,0)
SET @i = 0
WHILE @I <= 1000 BEGIN 
    PRINT convert (char(5), @i)  
            + convert(varchar(255), dbo.udf_Num_ToWords(@i)) 
    SET @I  = @i + 1 
END
*
* Published as the T-SQL UDF of the Week Vol 2 #9 2/17/03
****************************************************************/
AS BEGIN

DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)

IF @Number = 0 Return 'Zero'

-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
     , @outputString = ''
     , @counter = 1
SELECT @length   = LEN(@inputNumber)
     , @position = LEN(@inputNumber) - 2
     , @loops    = LEN(@inputNumber)/3

-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1

-- insert data for the numbers and words
INSERT INTO @NumbersTable   SELECT '00', ''
    UNION ALL SELECT '01', 'one'      UNION ALL SELECT '02', 'two'
    UNION ALL SELECT '03', 'three'    UNION ALL SELECT '04', 'four'
    UNION ALL SELECT '05', 'five'     UNION ALL SELECT '06', 'six'
    UNION ALL SELECT '07', 'seven'    UNION ALL SELECT '08', 'eight'
    UNION ALL SELECT '09', 'nine'     UNION ALL SELECT '10', 'ten'
    UNION ALL SELECT '11', 'eleven'   UNION ALL SELECT '12', 'twelve'
    UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'
    UNION ALL SELECT '15', 'fifteen'  UNION ALL SELECT '16', 'sixteen'
    UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'
    UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'
    UNION ALL SELECT '30', 'thirty'   UNION ALL SELECT '40', 'forty'
    UNION ALL SELECT '50', 'fifty'    UNION ALL SELECT '60', 'sixty'
    UNION ALL SELECT '70', 'seventy'  UNION ALL SELECT '80', 'eighty'
    UNION ALL SELECT '90', 'ninety'   

WHILE @counter <= @loops BEGIN

 -- get chunks of 3 numbers at a time, padded with leading zeros
 SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)

 IF @chunk <> '000' BEGIN
  SELECT @tensones = SUBSTRING(@chunk, 2, 2)
       , @hundreds = SUBSTRING(@chunk, 1, 1)
       , @tens = SUBSTRING(@chunk, 2, 1)
       , @ones = SUBSTRING(@chunk, 3, 1)

  -- If twenty or less, use the word directly from @NumbersTable
  IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
   SET @outputString = (SELECT word 
                                      FROM @NumbersTable 
                                      WHERE @tensones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' thousand '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '  WHEN 13 THEN ' undecillion '
                       ELSE '' END
                               + @outputString
      END
   ELSE BEGIN -- break down the ones and the tens separately

             SET @outputString = ' ' 
                            + (SELECT word 
                                    FROM @NumbersTable 
                                    WHERE @tens + '0' = number)
              + '-'
                             + (SELECT word 
                                    FROM @NumbersTable 
                                    WHERE '0'+ @ones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '   WHEN 13 THEN ' undecillion '
                       ELSE '' END
                            + @outputString
  END

  -- now get the hundreds
  IF @hundreds <> '0' BEGIN
   SET @outputString  = (SELECT word 
                                      FROM @NumbersTable 
                                      WHERE '0' + @hundreds = number)
                 + ' hundred ' 
                                + @outputString
  END
 END

 SELECT @counter = @counter + 1
      , @position = @position - 3

END

-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)


RETURN @outputString -- return the result
END





To check the output of the above User Defined Function, execute the below query into your database.

--now run the following query to get the result

select dbo.udf_Num_ToWords(5800000)

--output
--Five thousand eight hundred thousand


--
Happy Coding to All

Recursive Query To Find All Child Node in SQL server 2005+

How to find the all child of particular parent using the Recursive Query
--here we are creating the table variable so that we can insert some dummy record
Declare @Table Table
(
 TId int,
 ParentId int,
 Name varchar(10)
)
--inserting some records
--using the union all to insert more than one records
insert into @Table
Select 1,NULL,'ICT'
Union All
Select 2,1,'ICT-M1'
Union All
Select 4,1,'ICT-M2'
Union All
Select 7,2,'ICT-M1U1'
Union All
Select 8,2,'ICT-M1U2'
Union All
Select 9,4,'ICT-M2U1'
Union All
Select 10,4,'ICT-M2U2'
Union All
Select 11,7,'ICT-M1U1P1'
Union All
Select 12,7,'ICT-M1U1P2'
Union All
Select 13,8,'ICT-M1U2P1'
Union All
Select 14,8,'ICT-M1U2P2'
Union All
Select 15,9,'ICT-M2U1P1'
Union All
Select 16,9,'ICT-M2U1P2'
Union All
Select 17,10,'ICT-M2U2P1'
Union All
Select 18,10,'ICT-M2U2P2'

--variable to hold data
Declare @ChildNode varchar(1000)
Set @ChildNode='';

 
--use the standard recursive query
;with [CTE] as 
(
 --anchor query where we are finding the all parents
    select TId,ParentId,Name,CAST(ISNULL(CAST(ParentId as varchar(10)),'0') As Varchar(100)) As ChildNode
    from @Table c where c.[ParentId] is null

    union all

 --recursive query where we are finding the all child according to anchor query parent
    select c.TId,c.ParentId,c.Name,
    CAST( p.ChildNode +','+cast(c.TId as varchar(10) ) As Varchar(100)) As ChildNode
    from [CTE] p, @Table c where c.[ParentId] = p.[TId]    
)
--select the child node as per the id
--Assigin the all Ids into  one variable
select @ChildNode=@ChildNode+','+Cast(TId as varchar(10))
from [CTE]
Cross Apply
dbo.Split(ChildNode,',')
where items=9
order by TId

select SUBSTRING(@ChildNode,2,LEN(@ChildNode))

--outpu
--2,7,8,11,12,13,14


----
--create the tabular function to split the multi valued into table
Create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
 declare @idx int     
 declare @slice varchar(8000)     
    
 select @idx = 1     
  if len(@String)<1 or @String is null  return     
    
 while @idx!= 0     
 begin     
  set @idx = charindex(@Delimiter,@String)     
  if @idx!=0     
   set @slice = left(@String,@idx - 1)     
  else     
   set @slice = @String     
  
  if(len(@slice)>0)
   insert into @temptable(Items) values(@slice)     

  set @String = right(@String,len(@String) - @idx)     
  if len(@String) = 0 break     
 end 
return     
end


--
Happy Coding

Wednesday, May 19, 2010

Age calculation with SQL Server

There seem to be many different methods being suggested to calculate an age in SQLServer.  Some are quite complex but most are simply wrong.  This is by far the simplest and accurate method that I know.

Declare @Date1 datetime
Declare @Date2 datetime


Select @Date1 = '15Feb1971'Select @Date2 = '08Dec2009'select CASE
WHEN dateadd(year, datediff (year, @Date1, @Date2), @Date1) > @Date2
THEN datediff (year, @Date1, @Date2) - 1
ELSE datediff (year, @Date1, @Date2)END as Age
 
--
happy coding 

Wednesday, February 24, 2010

Reset Identity Column Value in SQL Server

If you are using an identity column on your SQL Server tables, you can set the next insert value to whatever value you want. An example is if you wanted to start numbering your ID column at 1000 instead of 1.


It would be wise to first check what the current identify value is. We can use this command to do so:


DBCC CHECKIDENT ('tablename', NORESEED) 
 
For instance, if I wanted to check the next ID value of my orders table, I could use this command:


DBCC CHECKIDENT (orders, NORESEED) 
 
To set the value of the next ID to be 1000, I can use this command:
DBCC CHECKIDENT (orders, RESEED, 999)

Note that the next value will be whatever you reseed with + 1, so in this case I set it to 999 so that the next value will be 1000.


Another thing to note is that you may need to enclose the table name in single quotes or square brackets if you are referencing by a full path, or if your table name has spaces in it. (which it really shouldn’t)


DBCC CHECKIDENT ('databasename.dbo.orders',RESEED, 999)

Wednesday, December 30, 2009

Some Sql Server Interview Questions

* Difference between Set and Select
Ans:
1) Set is a ANSI standard for variable assignment.
   Select is a Non-ANSI standard when assigning variables.We can assign only one variable at a time

2) We can assign multiple variable at a time.When assigning from a query that returns more than one value, SET will fail with an error.
   When assigning from a query that returns more than one value, SELECT will assign the last value returned by the query and hide the fact that the query returned

* What is Network Packet Size in SQL
Ans:
NPS(Network Packet Size) is the size of the TDS (tabular data stream) packets used to communicate between your applications and your relational database engine and default packet size is 4 kilobytes and its manily depends on network packet size configuration option.


* How many different locks in SQL SERVER
Ans:
(1)Intent
(2)shared
(3)Update
(4)Exclusive
(5)Schema
(6)Bulk Update

* Diffrence between temp table and table variable
Ans:
(1)Temp Tables are created in the SQL Server TEMPDB database and therefore require more IO resources and locking. Table Variables and Derived Tables are created in memory.

(2)Temp Tables will generally perform better for large amounts of data that can be worked on using parallelism whereas Table Variables are best used for small amounts of data (I use a rule of thumb of 100 or less rows) where parallelism would not provide a significant performance improvement.

(3)You cannot use a stored procedure to insert data into a Table Variable or Derived Table. For example, the following will work: INSERT INTO #MyTempTable EXEC dbo.GetPolicies_sp whereas the following will generate an error: INSERT INTO @MyTableVariable EXEC dbo.GetPolicies_sp.

(4)Derived Tables can only be created from a SELECT statement but can be used within an Insert, Update, or Delete statement.

(5) In order of scope endurance, Temp Tables extend the furthest in scope, followed by Table Variables, and finally Derived Tables.

* What is Cursor
Ans:
  Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable

* Write some disadvantage of Cursor
Ans:
  Cursor plays there row quite nicely but although there are some disadvantage of Cursor . Because we know cursor doing roundtrip it will make network line busy and also make time consuming methods. First of all select query gernate output and after that cursor goes one by one so roundtrip happen.Another disadvange of cursor are ther are too costly because they require lot of resources and temporary storage so network is quite busy.

* Can you tell me the difference between DELETE and TRUNCATE commands
Ans:
  Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

Thursday, December 24, 2009

How to pad a numeric type column with leading zeros?

Execute the following Microsoft SQL Server T-SQL example scripts to demonstrate SQL padding of numbers and strings with leading and trailing zeros as well as with other designated characters, and techniques
for removing (trim) leading zeros. 



-- SQL Server leading zero - T-SQL padding numbers - lpad tsql - sql pad zero

SELECT ListPrice,

  Padded=RIGHT('0000000000' + CONVERT(VARCHAR,ListPrice), 10)

FROM AdventureWorks2008.Production.Product WHERE ListPrice > 0.0

------------

-- T SQL pad leading zeros - transact sql leading zeros

SELECT ProductID, ReorderPoint = REPLACE(STR(ReorderPoint, 6), SPACE(1), '0')

FROM AdventureWorks2008.Production.Product

/* ProductID            ReorderPoint

      1                 000750

      2                 000750

      3                 000600  */

 ------------

-- SQL Server leading zero - SQL leading zeros - sql server pad leading zero

USE AdventureWorks2008;

DECLARE @Number int = 789

SELECT RIGHT ('000000'+ CAST (@Number AS varchar), 6)

-- 000789

------------

USE AdventureWorks;

-- SQL padding salary pay rate money data type with leading zeroes

-- SQL left pad number - prefix number with zeros - sql server leading zero

-- SQL convert number to text - cast number to varchar string

SELECT EmployeeID,

       Rate,

       PaddedRate = RIGHT(REPLICATE('0',8) + CAST(Rate AS VARCHAR(8)),8)

FROM   HumanResources.EmployeePayHistory
/* Partial results

EmployeeID        Rate              PaddedRate

1                 12.45             00012.45

2                 13.4615           00013.46

3                 43.2692           00043.27

*/


-- SQL zero padding ListPrice money data type - t sql leading zero

-- SQL left pad - T-SQL string concatenation - sql concat

-- SQL convert number to string - pad numeric with zeros

SELECT ProductID,

       ListPrice,

       PaddedListPrice = RIGHT(REPLICATE('0', 8) + CAST(ListPrice AS VARCHAR(8)),8)

FROM Production.Product

/* Results sample

ProductID               ListPrice               PaddedListPrice

965                     742.35                  00742.35

*/


-- SQL month leading zero - sql pad month number with zero

SELECT RIGHT('0' + convert(varchar(2), month(GetDate())), 2)

-- 06
----------

-- trim leading zeros - sql trim leading zeros - remove leading zeros sql

USE AdventureWorks2008;

DECLARE @num  varchar(32)= '00091234560'

SELECT      right(@num, len(@num)+1 - patindex('%[^0]%', @num))

-- 91234560

------------

-- SQL pad numeric data type - SQL pad digits - transact sql leading zeros

-- SQL pad with leading zeroes - append leading zeros - T-SQL top function

-- SQL pad with trailing zeroes - MSSQL append trailing zeros

-- SQL cast money to numeric - cast numeric to string - mssql newid function

SELECT TOP (5)

      ProductName = Name,

      ListPrice = RIGHT(REPLICATE('0', 10)

      + CAST(CAST(ListPrice AS NUMERIC(9,3)) AS VARCHAR) ,10)  

FROM AdventureWorks.Production.Product

ORDER BY NEWID()

/* ProductName                      ListPrice

LL Mountain Frame - Black, 40       000249.790

HL Touring Frame - Yellow, 46       001003.910

Bike Wash - Dissolver               000007.950

Metal Sheet 7                       000000.000

HL Road Frame - Red, 56             001431.500

*/

----------


-- PAD leading zeros function - sql server leading zeros - UDF

USE AdventureWorks2008;

GO

CREATE FUNCTION fnPadNumber

               (@n      DECIMAL(26,2),

                @length TINYINT)

RETURNS VARCHAR(32)

AS

  BEGIN

    RETURN ( replicate('0',@length - len(convert(VARCHAR(32),@n))) +

             convert(VARCHAR(32),@n))

  END

GO


SELECT dbo.fnPadNumber(1234567890.12,16)

-- 0001234567890.12
------------

-- T SQL computed column zero padding - sql generate alphanumeric sequence

USE tempdb; -- SQL Server 2008 T-SQL

CREATE TABLE Celebrity (

  ID           INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,

  CelebrityID  AS 'CEL' + RIGHT('0000' + CAST( ID as varchar),5), -- computed column

  FirstName    VARCHAR(32),

  LastName     VARCHAR(32),

  ModifiedDate DATE    DEFAULT getdate())

GO


INSERT Celebrity

      (FirstName,

       LastName)

VALUES('Jennifer','Aniston'),

      ('Drew','Barrymore'),

      ('Diana','Princess of Wales'),

      ('Tom','Jones'),

      ('Lucille','Ball'),

      ('Frank','Sinatra'),

      ('Elvis','Presley')


SELECT * FROM   Celebrity

GO

-- CelebrityID is zero padded alphanumeric sequence

/* 

ID    CelebrityID       FirstName  LastName          ModifiedDate

1     CEL00001          Jennifer    Aniston          2012-07-04

2     CEL00002          Drew        Barrymore        2012-07-04

3     CEL00003          Diana       Princess of Wales 2012-07-04

4     CEL00004          Tom         Jones             2012-07-04

5     CEL00005          Lucille     Ball              2012-07-04

6     CEL00006          Frank       Sinatra          2012-07-04

7     CEL00007          Elvis       Presley          2012-07-04

*/

-- Cleanup demo

DROP TABLE Celebrity

GO
------------

-- SQL removing leading zeros when no spaces in string - trimming Leading Zeros

USE AdventureWorks2008;

DECLARE @NumberString varchar(16)='000000246'

SELECT REPLACE(LTRIM(REPLACE(@NumberString, '0', ' ')), ' ', '0')

-- 246

------------

-- SQL remove leading zeros - sql trim leading zeros - numeric test

DECLARE @StringWithLeadingZeros    VARCHAR(12) = '000000654321'

SELECT CAST(CAST(@StringWithLeadingZeros AS INT) AS VARCHAR(10))

WHERE ISNUMERIC (@StringWithLeadingZeros)=1

-- 654321

------------

-- LPAD & RPAD string scalar-valued user-defined functions (UDF)

USE AdventureWorks;

GO

-- Left pad string function

CREATE FUNCTION LPAD

               (@SourceString VARCHAR(MAX),

                @FinalLength  INT,

                @PadChar      CHAR(1))

RETURNS VARCHAR(MAX)

AS

  BEGIN

    RETURN

      (SELECT Replicate(@PadChar,@FinalLength - Len(@SourceString)) + @SourceString)

  END

GO


-- T-SQL Test left padding

SELECT LeftPaddedString = dbo.LPAD(Cast(84856 AS VARCHAR),12,'0')

GO

-- 000000084856


-- MSSQL right pad string function

CREATE FUNCTION RPAD

               (@SourceString VARCHAR(MAX),

                @FinalLength  INT,

                @PadChar      CHAR(1))

RETURNS VARCHAR(MAX)

AS

  BEGIN

    RETURN

      (SELECT @SourceString + Replicate(@PadChar,@FinalLength - Len(@SourceString)))

  END

GO


-- Test right padding

SELECT RightPaddedString = dbo.RPAD(Cast(84856 AS VARCHAR),12,'*')

GO

-- 84856*******

----------


-- Padding a money column with leading zeroes - sql convert leading zero

-- SQL convert money data type to string

SELECT PaddedUnitPrice = RIGHT(replicate('0',20) +

                         convert(varchar,UnitPrice,1), 20)

FROM Northwind.dbo.Products

/* Partial results


PaddedUnitPrice

00000000000000018.00

00000000000000019.00

00000000000000010.00

00000000000000022.00

*/


/**************** Zero padding other numeric data ****************/


-- SQL Server 2008 version featuring the LEFT function

-- SQL convert integer to text - convert integer to varchar

USE AdventureWorks2008;

DECLARE @InputNumber int = 522, @OutputLength tinyint = 12

DECLARE @PaddedString char(12)

SET @PaddedString = LEFT( replicate( '0', @OutputLength ),

    @OutputLength - len( @InputNumber ) ) + convert( varchar(12), @InputNumber)

SELECT PaddedNumber=@PaddedString

/* Result


PaddedNumber

000000000522

*/



-- SQL format currency and pad with leading spaces

-- SQL Server lpad to 9 characters

SELECT   TOP (3)  ProductName=Name,

                  Price=   CONVERT(char(9), ListPrice, 1)

FROM Production.Product

WHERE ListPrice > 0.0 ORDER BY Newid()

/*

ProductName                         Price

LL Touring Frame - Blue, 62           333.42

LL Road Seat Assembly                 133.34

Road-250 Red, 58                    2,443.35

*/

------------

-- Padding with zeroes in the middle of string

DECLARE @Number varchar(10)

SET @Number = '99999'

PRINT 'TRK' + REPLICATE('0', 12 - LEN(@Number)) + @Number

SELECT [Zero Padding]='TRK' + REPLICATE('0', 12 - LEN(@Number)) + @Number

-- Result: TRK000000099999


-- Applying the STUFF string function for zero padding

-- SQL convert integer data type to string

DECLARE @SerialNo int, @OutputSize tinyint

SET @OutputSize = 10

SET @SerialNo = 6543

SELECT PaddedSerialNo =  STUFF(replicate('0', @OutputSize),

    @OutputSize - len(@SerialNo)+1, len(@SerialNo), convert(varchar(9),@SerialNo))

-- Result: 0000006543

-- SQL pad integer with 0-s

-- SQL str function - numeric to character conversion

SELECT TOP 5

CAST(replace(str(ProductID,6),' ','0') AS char(6)) AS ProdID

FROM AdventureWorks.Production.Product

ORDER BY Name

/* Results

ProdID

000001

000879

000712

000003

000002

*/


-- SQL pad string with character

-- SQL create function

-- SQL user-defined function

CREATE FUNCTION dbo.fnLeftPadString (

      @Input VARCHAR(255),

      @PadChar CHAR(1),

      @LengthToPad TINYINT

      )

RETURNS VARCHAR(255) AS 

BEGIN

DECLARE @InputLen TINYINT

SET @InputLen = LEN(@Input)

RETURN

   CASE

      WHEN @InputLen < @LengthToPad

      THEN REPLICATE(@PadChar, @LengthToPad - @InputLen) + @Input

      ELSE @Input

   END -- CASE

END -- UDF

GO


-- SQL pad string – left padding - SQL left pad with asterisk

-- SQL check printing - SQL currency formatting

DECLARE @DollarAmount varchar(20)

SET @DollarAmount = '234.40'

SELECT PaddedString='$'+dbo.fnLeftPadString(@DollarAmount, '*', 10)

GO

-- Result: $****234.40


-- SQL currency formatting with asterisk-fill

DECLARE @Amount MONEY

SET @Amount = '3534.40'

SELECT CurrencyFormat = '$' + REPLACE(Convert(char(12),@Amount,1),' ','*')

-- $****3,534.40

SELECT      PaddedProductID =

                  dbo.fnLeftPadString (CONVERT(varchar, ProductID), '0', 6),

            ProductName=Name,

            ListPrice

FROM Production.Product

ORDER BY PaddedProductID

GO


/* Partial results


PaddedProductID         ProductName       ListPrice

000757                  Road-450 Red, 48  1457.99

000758                  Road-450 Red, 52  1457.99

000759                  Road-650 Red, 58  782.99

000760                  Road-650 Red, 60  782.99

000761                  Road-650 Red, 62  782.99

*/

----------

-- Generating tracking numbers

-- SQL pad zeroes

WITH cteSequence(SeqNo)

     AS (SELECT 1

         UNION ALL

         SELECT SeqNo + 1

         FROM   cteSequence

         WHERE  SeqNo < 1000000)

SELECT TOP 100 CAST(('TRK' + REPLICATE('0',

               7 - LEN(CAST(SeqNo AS VARCHAR(6)))) +

               CAST(SeqNo AS VARCHAR(6))) AS VARCHAR(10)) AS TrackingNo

FROM   cteSequence

OPTION (MAXRECURSION 0)

GO

/* Partial results


TrackingNo

TRK0000001

TRK0000002

TRK0000003

TRK0000004

TRK0000005

TRK0000006

TRK0000007

*/

----------

-- SQL server pad

-- SQL str function

-- SQL pad integer

-- SQL left pad

SELECT TOP (4) StaffName=LastName+', '+FirstName,

PaddedEmployeeID = REPLACE(STR(EmployeeID, 6, 0), ' ', '0')

FROM HumanResources.Employee e

INNER JOIN Person.Contact c

      ON e.ContactID = c.ContactID

ORDER BY NEWID()

/* Results


StaffName         PaddedEmployeeID

Dyck, Shelley     000214

Hines, Michael    000039

Ford, Jeffrey     000015

Caron, Rob        000168

*/


-- SQL asterisk padding

-- SQL pad with asterisk

-- SQL right pad

SELECT  TOP ( 2 * 2 )

              AddressID

            , City+REPLICATE('*', 20-len(City)) AS City

            , PostalCode

FROM AdventureWorks.Person.[Address]

WHERE LEN(City) <= 20

ORDER by NEWID()

GO

/*

AddressID   City                          PostalCode

13465       Imperial Beach******    91932

23217       Corvallis***********    97330

18548       Milwaukie***********    97222

24893       Goulburn************    2580

*/

------------

------------

-- SQL left pad any size string Alpha with any length string Beta

------------

-- SQL user-defined function - UDF - scalar-valued string function

-- T-SQL varchar(max)

USE AdventureWorks2008;

GO

CREATE FUNCTION fnMaxPad

               (@SourceString  VARCHAR(MAX),

                @PaddingString VARCHAR(MAX),

                @OutputLength  INT)

RETURNS VARCHAR(MAX)

AS

  BEGIN

    DECLARE  @WorkString VARCHAR(MAX) =

      ISNULL(REPLICATE(@PaddingString,@OutputLength - len(ISNULL(@SourceString,0))),

                            '') + @SourceString

    RETURN RIGHT(@WorkString, @OutputLength)

  END

GO


-- Execute UDF

SELECT TOP ( 5 ) [SQL Padding Demo] =

  dbo.fnMaxPad(CONVERT(VARCHAR,FLOOR(ListPrice)),'_/',21)

FROM     Production.Product

ORDER BY NEWID()

GO

/* Results


SQL Padding Demo

_/_/_/_/_/_/_/2443.00

/_/_/_/_/_/_/_/_/0.00

/_/_/_/_/_/_/_/147.00

/_/_/_/_/_/_/_/_/0.00

_/_/_/_/_/_/_/1003.00

*/

------------

-- SQL left pad unicode string Alpha with any length unicode string Beta

-- MSSQL pad international

-- SQL user-defined function - UDF - scalar-value function

-- T-SQL nvarchar(max)

USE AdventureWorks2008;

GO

CREATE FUNCTION fnMaxPadInternational

               (@SourceString  NVARCHAR(MAX),

                @PaddingString NVARCHAR(MAX),

                @OutputLength  INT)

RETURNS NVARCHAR(MAX)

AS

  BEGIN

    DECLARE  @WorkString NVARCHAR(MAX) =

      ISNULL(REPLICATE(@PaddingString,@OutputLength - len(ISNULL(@SourceString,0))),

                            '') + @SourceString

    

    RETURN RIGHT(@WorkString, @OutputLength)

  END

GO


-- Execute UDF

SELECT TOP ( 5 ) [SQL Padding Demo] =

dbo.fnMaxPadInternational(LEFT(Description,6),'_/',21)

FROM     Production.ProductDescription

ORDER BY NEWID()

GO

/* Results


SQL Padding Demo

/_/_/_/_/_/_/_/ล้อ ที่

/_/_/_/_/_/_/_/Roue d

/_/_/_/_/_/_/_/شوكة ط

/_/_/_/_/_/_/_/Each f

/_/_/_/_/_/_/_/Jeu de

*/

------------
Reference from sqlusa.com

Saturday, December 19, 2009

How to configure SQL Server 2005 to allow remote connections?

To configure SQL Server 2005 to allow remote connections, complete all the following steps.
• Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
• Turn on the SQL Server Browser service.
• Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.

Enable remote connections for SQL Server 2005 Express

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.
Note Click OK when you receive the following message:
Changes to Connection Settings will not take effect until you restart the Database Engine service.
4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop. wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

Enable the SQL Server Browser service

1.Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2.On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3.On the Surface Area Configuration for Services and Connections page, click SQL Server Browser, click Automatic for Startup type, and then click Apply.
Note When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.
4.Click Start, and then click OK.

Create exceptions in Windows Firewall

SQL Server 2005 uses an instance ID as part of the path when you install its program files. To create an exception for each instance of SQL Server, you must identify the correct instance ID. To obtain an instance ID, follow these steps:
1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, click the SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click Properties.
3. On the SQL Server Browser Properties page, click the Advanced tab, locate the instance ID in the property list, and then click OK.
To open Windows Firewall, click Start, click Run, type firewall.cpl, and then click OK.
Create an exception for SQL Server 2005 in Windows Firewall
To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:
1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe executable program, click Open, and then click OK.
Note The path may be different depending on where SQL Server 2005 is installed. MSSQL.1 is a placeholder for the instance ID that you obtained in step 3 of the previous procedure.
4. Repeat steps 1 through 3 for each instance of SQL Server 2005 that needs an exception.
Create an exception for the SQL Server Browser service in Windows Firewall
To create an exception for the SQL Server Browser service in Windows Firewall, follow these steps:
1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, click Open, and then click OK.

---
Hope this helps everyone.

Friday, December 18, 2009

Differences between varchar and nvarchar in SQL Server

=>SQL Server 2000 and SQL Server 7, a row cannot exceed 8000 bytes in size. This means that a VARBINARY column can only store 8000 bytes (assuming it is the only column in a table), a VARCHAR column can store up to 8000 characters and an NVARCHAR column can store up to 4000 characters (2 bytes per unicode character). This limitation stems from the 8 KB internal page size SQL Server uses to save data to disk.
To store more data in a single column, you needed to use the TEXT, NTEXT, or IMAGE data types (BLOBs) which are stored in a collection of 8 KB data pages that are separate from the data pages that store the other data in the same table. These data pages are arranged in a B-tree structure. BLOBs are hard to work with and manipulate. They cannot be used as variables in a procedure or a function and they cannot be used inside string functions such as REPLACE, CHARINDEX or SUBSTRING. In most cases, you have to use READTEXT, WRITETEXT, and UPDATETEXT commands to manipulate BLOBs.

To solve this problem, Microsoft introduced the VARCHAR(MAX),  NVARCHAR(MAX), and VARBINARY(MAX) data types in SQL Server 2005. These data types can hold the same amount of data BLOBs can hold (2 GB) and they are stored in the same type of data pages used for other data types. When data in a MAX data type exceeds 8 KB, an over-flow page is used. SQL Server 2005 automatically assigns an over-flow indicator to the page and knows how to manipulate data rows the same way it manipulates other data types. You can declare variables of MAX data types inside a stored procedure or function and even pass them as variables. You can also use them inside string functions.



The broad range of data types in SQL Server can sometimes throw people through a loop, especially when the data types seem to be highly interchangeable. Two in particular that constantly spark questions are VARCHAR and NVARCHAR: what's the difference between the two, and how important is the difference? VARCHAR is an abbreviation for variable-length character string. It's a string of text characters that can be as large as the page size for the database table holding the column in question. The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR to 8,000 bytes.
The "N" in NVARCHAR means uNicode. Essentially, NVARCHAR is nothing more than a VARCHAR that supports two-byte characters. The most common use for this sort of thing is to store character data that is a mixture of English and non-English symbols — in my case, English and Japanese.
The key difference between the two data types is how they're stored. VARCHAR is stored as regular 8-bit data. But NVARCHAR strings are stored in the database as UTF-16 — 16 bits or two bytes per character, all the time — and converted to whatever codepage is being used by the database connection on output (typically UTF-8). That said, NVARCHAR strings have the same length restrictions as their VARCHAR cousins — 8,000 bytes. However, since NVARCHARs use two bytes for each character, that means a given NVARCHAR can only hold 4,000 characters (not bytes) maximum. So, the amount of storage needed for NVARCHAR entities is going to be twice whatever you'd allocate for a plain old VARCHAR.
Because of this, some people may not want to use NVARCHAR universally, and may want to fall back on VARCHAR — which takes up less space per row — whenever possible.


Wednesday, December 2, 2009

Parameter Sniffing and OPTIMZE FOR

Parameter Sniffing refers to a process whereby SQL Server’s execution environment “sniffs” the parameter values during first invocation, and passes it along to the query optimizer so that they can be used to generate optimal query execution plans.


While parameter sniffing is certainly a powerful feature, it can cause problems when a procedure’s plan happens to have been kicked out of the procedure cache (or was never in it) just prior to the procedure being called with atypical parameter values. This can result in a plan that is skewed toward atypical use, one that is not optimal when called with typical values. Since, once cached, a query plan can be reused for parameter values that vary widely, the ideal situation is to have a plan in the cache that covers the typical usage of the procedure as much as possible. If a plan makes it into the cache that is oriented toward atypical parameter values, it can have a devastating effect on performance when executed with typical values. 

Click here to read full article.

Tuesday, November 24, 2009

Timestamp (rowversion) Data Type

A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated. Of course this means we should not use it as a primary key, because we can get many orphans quickly if updates are performed on other columns. As a row is modified in a table, the timestamp is updated with the current database timestamp value obtained from the @@DBTS function.

Timestamp is the wrong name, quite confusing in fact. It has nothing to do with time. Microsoft will rename it rowversion in the future. Rowversion is the synonym for timestamp in SQL Server 2005 and SQL Server 2008.

It is an 8 bytes unique binary key within the database.

Here is how it looks like: 0x0000000000000743. After an update to the row: 0x0000000000000745.
The rowversion(timestamp) starts changing as soon as the transaction begins. If the transaction is rolled back, it returns to the original value.

So how can we use it?

The main purpose is row versioning in multi user environment, in other words concurrency checking.
Assume you are a developer and developing a program in Visual Basic to update the name and address table of customers. There will be 100 staff member who can perform this application function. How can you be sure that while staff A typing in the change, staff X is not changing the same row?

Here is what you do:

1. Read the name and address table including the timestamp. You display the info to the user for update and save the timestamp.
2. Certain amount of time later, like 2 minutes, the user presses the submit button after changes were typed in.
3. You open a transaction with Begin Transaction
4. You read the timestamp of the name and address row
5. You compare the current timestamp to the saved timestamp.
6. If the timestamps are same, you update the row and commit the transaction
7. If timestamps are different, you roll back the transaction and notify the user about the fact that the data was changed by someone else. You can let the user decide what to do or follow the appropriate company business rule for data entry conflict resolution.
This is pretty common practice in multi user environment. The alternate would be to examine a datetime column, or the entire row which is more processing intensive.

The following example shows timestamp (rowversion in SQL Server 2008) in action:
-- SQL Server 2008 T-SQL Code
USE tempdb;

-- SQL create table for Concurrency Checking demo
CREATE TABLE Celebrity (
CelebrityID INT     IDENTITY     PRIMARY KEY,
FirstName   VARCHAR(25),
LastName     VARCHAR(30),
VERSIONSTAMP  ROWVERSION)
GO

-- SQL insert - populate table
INSERT Celebrity (FirstName, LastName)
VALUES
('Jessica', 'Simpson'),
('Nick', 'Carter'),
('Stevie', 'Brock'),
('Christina', 'Aguilera'),
('Frank','Sinatra'),
('Doris','Day'),
('Elvis', 'Presley')
GO

SELECT * FROM Celebrity
GO

/* Results
CelebrityID FirstName  VERSIONSTAMP
1             Jessica      Simpson      0x0000000000000876
2             Nick         Carter       0x0000000000000877
3             Stevie       Brock        0x0000000000000878
4             Christina   Aguilera     0x0000000000000879
5             Frank        Sinatra      0x000000000000087A
6             Doris        Day          0x000000000000087B
7             Elvis        Presley      0x000000000000087C
*/

-- SQL update demo: SOMEONE UPDATED RECORD since it was read
CREATE TABLE #Semaphore (ID int identity(1,1) primary key,
StartVersion bigint,
PK int)
DECLARE @MyKey int
INSERT INTO #Semaphore (StartVersion, PK)
SELECT  VERSIONSTAMP, 1
FROM Celebrity WHERE CelebrityID=1
SELECT @MyKey = SCOPE_IDENTITY()

-- SIMULATION: somebody else updating the same record
UPDATE Celebrity
SET     FirstName = 'Celine',
LastName = 'Dion'
WHERE  CelebrityID = 1

-- We are attempting to update.
BEGIN TRANSACTION
IF (SELECT StartVersion
FROM   #Semaphore
WHERE  ID = @MyKey) = (SELECT VERSIONSTAMP
FROM   Celebrity
WHERE  CelebrityID = 1)
BEGIN
UPDATE Celebrity
SET     FirstName = 'Lindsay',
LastName = 'Lohan'
WHERE  CelebrityID = 1

COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
PRINT 'ROLLBACK - UPDATE CONFLICT'
RAISERROR ('Celebrity update conflict.',10,0)
END
DELETE #Semaphore WHERE ID = @MyKey
SELECT * FROM   Celebrity
GO
/* CelebrityID     FirstName   LastName     VERSIONSTAMP
1             Celine       Dion         0x000000000000087D
2             Nick         Carter       0x0000000000000877
3             Stevie       Brock        0x0000000000000878
4             Christina   Aguilera     0x0000000000000879
5             Frank        Sinatra      0x000000000000087A
6             Doris        Day          0x000000000000087B
7             Elvis        Presley      0x000000000000087C
*/

-- SQL UPDATE with NO CONFLICT
DECLARE @MyKey int
INSERT INTO #Semaphore (StartVersion, PK)
SELECT  VERSIONSTAMP, 1
FROM Celebrity WHERE CelebrityID=1
SELECT @MyKey = SCOPE_IDENTITY()

-- We are trying to update.
BEGIN TRANSACTION
IF (SELECT StartVersion
FROM   #Semaphore
WHERE  ID = @MyKey) = (SELECT VERSIONSTAMP
FROM   Celebrity
WHERE  CelebrityID = 1)
BEGIN
UPDATE Celebrity
SET     FirstName = 'Lindsay',
LastName = 'Lohan'
WHERE  CelebrityID = 1

COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
PRINT 'ROLLBACK - UPDATE CONFLICT'
RAISERROR ('Celebrity update conflict.',10,0)
END
DELETE #Semaphore WHERE ID = @MyKey
SELECT * FROM   Celebrity
GO
/*
CelebrityID FirstName   LastName     VERSIONSTAMP
1             Lindsay      Lohan        0x000000000000087E
2             Nick         Carter       0x0000000000000877
3             Stevie       Brock        0x0000000000000878
4             Christina   Aguilera     0x0000000000000879
5             Frank        Sinatra      0x000000000000087A
6             Doris        Day          0x000000000000087B
7             Elvis        Presley      0x000000000000087C
*/
-- Cleanup
DROP TABLE #Semaphore
DROP TABLE Celebrity

---
got mail from http://www.sqlusa.com/register/

Saturday, November 21, 2009

Time Delay While Running T-SQL Query – Sleep Facility in SQL Server

Today, When I was created one store procedure, I fetched one problem. I have created on store procedure which will
generate 25 digits Primary key based on date and time.

I have to generate the primary key for 100k records to insert into another table.
I was using my Store procedure to generate the ID, but it gives me error which is very familiar for us.

Violation of PRIMARY KEY constraint


Now i have searching for function or way to wait for seconds or mili seconds in sql which is vary familiar in our programming language Sleep method to wait for some time before completion of code.


Today I will explain one very small but interesting feature of SQL Server. This is not much known feature of SQL Server. In SQL Server sometime, there are requirement like I described above when T-SQL script has to wait for some time before executing next statement. It is quite common that developers depends on application to take over this delay issue. However, SQL Server itself has very strong time management function of WAITFOR. Let us see two usage of WAITFOR clause.

Official explanation of WAITFOR clause from Book Online is “Blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row.”

Please run the following query into your sql server query analyzer.


SELECT GETDATE() WAITFOR DELAY '00:00:00:010' 
-- wait for 10 mill seconds to run the below code SELECT GETDATE()


--
Happy programming

Tuesday, November 17, 2009

Update data in one table with data from another table

How to update more than one column in a table with values
from columns in another table and explains how to do it in the three RDBMS that we support.

Table Structures and values:

TableA has four columns: a, b, c, d (a is the primary key column)
TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)

The foreign key relationship between the two tables is based on A.a = B.a1

The data in these 2 tables is as follows:
I.    TableA
a    b    c    d
1    x    y    z
2    a    b    c
3    t    x    z

II.    TableB
a1    b1    c1    d1    e1
1    x1    y1    z1    40
2    a1    b1    c1    50

The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.


SQL Server:

UPDATE TABLEA
SET     b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40
GO

Note: This is an extension in SQL Server i.e. the FROM clause – it does make it simple to understand and is a nice feature.

Results after the update:

a    b    c    d
————————————
1     x          y           z
2     a1        b1         c1
3     t           x           z



NOTE:

It is very important to make sure that your where clause for the update statement is correct since
that is what identifies the records that the update statement is going to qualify and do the update upon.
If it is incorrect, then you can get wrong results.  The reason I am mentioning this is
because I have seen people write wrong where clauses and then wondering what went wrong
because they specified the correct condition in the SET clause.

In the above example,
if the Where condition was omitted, the other record’s columns would be updated to NULL value
and this will be the final result set:

a    b    c    d
————————————
1     Null      Null      Null
2     a1        b1         c1
3     Null     Null      Null

--
happy programming

Saturday, November 14, 2009

Ten Common SQL Programming Mistakes

Without further ado, here is the list:
  1. NULLs and the NOT IN predicate
  2. Functions on indexed columns in predicates
  3. Incorrect subquery column
  4. Data type mismatch in predicates
  5. Predicate evaluation order
  6. Outer joins and placement of predicates
  7. Subqueries that return more than one value
  8. Use of SELECT *
  9. Scalar user-defined functions
  10. Overuse of cursors
The examples are presented using SQL Server's Transact-SQL dialect, but most of the concepts are valid in any SQL implementation.


Read full article here
--
Happy Programming

Friday, November 13, 2009

How to Get the Record Count of SQLDataSource?

To get the Record Count or Row Count returned by SQLDataSource control, we have to use DataSourceSelectArgument class.

Create an object to the DataSourceSelectArgument class, set its RetrieveTotalRowCount property to true and by using a DataView we can retrieve the Row Count of the SQLDataSource object. The sample block of code is given below. Try it.


<pre name="code" class="CSharp">

DataSourceSelectArguments dssa = new DataSourceSelectArguments();
dssa.AddSupportedCapabilities(DataSourceCapabilities.RetrieveTotalRowCount);
dssa.RetrieveTotalRowCount = true;
DataView dv = (DataView)SqlDataSource1.Select(dssa);
Response.Write("No of Records : " + dv.Table.Rows.Count);

</pre>

--
happy programming 

Thursday, November 12, 2009

Use Output clause in Delete Statement in sqlserver 2005

Here i give simple example of delete statement with output clause.
if you want track the deleted row information at that time generally
we preffered trigger right?
here we inserte the deleted row in the other history table. we create
the two talbe one is data table and other for history purpose.
1) Create Table (Name:Table_1)
GO
CREATE TABLE [dbo].[Table_1]

( [id] [int] IDENTITY(1,1) NOT NULL,
[code] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]

2) Create Table For Save Deleted Record in other table (DeletedTblInfo)
GO
CREATE TABLE [dbo].[DeletedTblInfo]
( [DeletedTableInfoId] [int] IDENTITY(1,1) NOT NULL,

[DeletedTableId] [int] NULL,
[DeletedRowId] [int] NULL,
 [DeletedDateTime] [datetime] NULL,
CONSTRAINT [PK_DeletedTblInfo] PRIMARY KEY CLUSTERED ( [DeletedTableInfoId] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]

3) insert data into data table
go
INSERT INTO Table_1
 (code)
VALUES
(‘test1′)

 go
INSERT INTO Table_1
 (code)
 VALUES
(‘test2′)
go

INSERT INTO Table_1
(code)
VALUES
(‘test3′)
go
INSERT INTO Table_1

(code)
VALUES
(‘test4′)
4) now delete from table 1 where id =4
–here the record save in the DeletedTblInfo
–DeletedTableId : the table id where data is deleted or the table name if you want then.
–DeletedRowId : deleted RowId

–DeletedDateTime : deleted DateTime
DELETE FROM Table_1
OUTPUT 1,DELETED.ID,getdate()
INTO DeletedTblInfo WHERE (Id = 4);

–now see the result in the history table
DeletedTableInfoId
DeletedTableId
DeletedRowId
DeletedDateTime
1
1
4
5/7/2008 12:53:52 PM

Thursday, October 29, 2009

EXISTS or COUNT(*) Which Do You Use To Check How Many Rows There Are?

Do you use this

IF (SELECT COUNT(*) FROM SomeTable
WHERE SomeColumn = SomeValue ) > 0

Or do you use this

IF EXISTS (SELECT * FROM SomeTable WHERE SomeColumn = SomeValue )

If you answered COUNT(*) then maybe you should take a look these two articles

Andrew Kelly has a nice post on SQLBlog
http://sqlblog.com/blogs/andrew_kelly/archive/2007/12/15/exists-vs-count-the-battle-never-ends.aspx

Matija Lah has a good post on his snaps & snippets blog
http://milambda.blogspot.com/2006/10/exists-or-not-exists-that-is-question.html

Thursday, October 8, 2009

How do I get the IDENTITY / AUTONUMBER value for the row I inserted?

To get the indentity value of table we can use any of these:

1) INDENT_CURRENT : - IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
 
SELECT IDENT_CURRENT(’tablename’):- It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

2) @@IDENTITY :- @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
       
SELECT @@IDENTITY :- It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.



3) SCOPE_IDENTITY :-  SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

SELECT SCOPE_IDENTITY() :- It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.


Examaple of each:

Create Table #Table
(
    Id int identity(1,1),
    [name] varchar(10)
)
insert into #Table
select 'P'

insert into #Table
select 'S'

insert into #Table
select 'G'

insert into #Table
select 'PP'


select * From #Table

Select  @@Identity As InsertId,IDENT_CURRENT('#Table') As InsertId,SCOPE_IDENTITY() As InsertId

Drop table #Table


------------------------------------------
Note : To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Friday, September 18, 2009

Bulk Insert From CSV OR Text File In SQL SERVER 2005

One of my friends contacted me recently and said they needed some help creating a stored procedure that imported data from a text file. They wanted the procedure to accept three parameters: PathFileName, ColumnDelimter, and RowDeliter. The PathFileName is simply the name and physical location of the source file on the hard drive, the Delimer are used to identify Columns and rows respectively.
So that i will have give her following store procedure.


Create Proc BulkInsertFromFile
(
@FileName Varchar(500), -- depends on path
@ColumnDelimier Varchar(10), -- delimiter for columns
@RowDelimier Varchar(10)--, -- delimier for Rows  
--@Output varchar(50)  -- used to specify error or success msg
)
As

DECLARE @doesExist INT

SET NOCOUNT ON
EXEC xp_fileexist @FileName, @doesExist OUTPUT
SET NOCOUNT OFF

-- i m creating Temp Table to demonstrate the example
-- Temp table holds the Results copies from FIle

Create Table #Temp
(
[CategoryId] [int],
[UserId] [int] NULL,
[ParentId] [int] NULL,
[CategoryName] [varchar](500),
[Description] [varchar](1000),
[Active] [bit] NULL,
[ImagePath] [varchar](max),
[CreatedDate] [datetime] NULL,
[DeleteStatus] [bit] NULL,
)

IF @doesExist = 1
BEGIN

Declare @SQL  nVarchar(max)  
Declare @Param nVarchar(500)
Set @Param='@FileName Varchar(500),@ColumnDelimier Varchar(20),@RowDelimier Varchar(20)'
SET @SQL = 'BULK INSERT #Temp FROM '''+@FileName+'''
WITH
(
FIELDTERMINATOR ='''+@ColumnDelimier+''',
ROWTERMINATOR ='''+@RowDelimier+''',
FIRE_TRIGGERS
)'

print @SQL
EXEC sp_executesql @SQL,@param,@FileName,@ColumnDelimier,@RowDelimier
--    Set @Output='Bulk insert Completed'
END
--Else
--Begin
--    Set @Output='File does not exist'
--End

---- u can also use the output  param to specify the error msg


Select * From #Temp

Drop table #Temp


What are the difference between DDL, DML and DCL commands

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

* CREATE - to create objects in the database
* ALTER - alters the structure of the database
* DROP - delete objects from the database
* TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
* COMMENT - add comments to the data dictionary
* RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

* SELECT - retrieve data from the a database
* INSERT - insert data into a table
* UPDATE - updates existing data within a table
* DELETE - deletes all records from a table, the space for the records remain
* MERGE - UPSERT operation (insert or update)
* CALL - call a PL/SQL or Java subprogram
* EXPLAIN PLAN - explain access path to data
* LOCK TABLE - control concurrency

DCL


Data Control Language (DCL) statements. Some examples:

* GRANT - gives user's access privileges to database
* REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

* COMMIT - save work done
* SAVEPOINT - identify a point in a transaction to which you can later roll back
* ROLLBACK - restore database to original since the last COMMIT
* SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

Blog Archive

Ideal SQL Query For Handling Error & Transcation in MS SQL

BEGIN TRY BEGIN TRAN --put queries here COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT>0 BEGIN SELECT @@ERROR,ERRO...