Alter Example

--ADD Column
IF NOT EXISTS(Select * from INFORMATION_SCHEMA.COLUMNS
where Table_name='tblsample' and Column_name='MobileNo')
BEGIN
ALTER TABLE tblsample add MobileNo bigint NULL
END

--Create Table
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS         
WHERE TABLE_NAME = 'TblReportMaster' )       
BEGIN       
CREATE TABLE [dbo].[TblReportMaster](       
 [ReportID] [int] NULL,       
 [ReportDisplayName] [nvarchar](100) NULL,       
 [SPName] [nvarchar](100) NULL       
) ON [PRIMARY]       
END

--DROP COLUMN
IF  EXISTS(Select * from INFORMATION_SCHEMA.COLUMNS where Table_name='tblsample' and Column_name='SEX')
BEGIN
ALTER TABLE tblsample
DROP COLUMN SEX
END

--Set Primary key and Foreign Key
IF EXISTS(Select * from INFORMATION_SCHEMA.COLUMNS where Table_name='tblsample' and Column_name='SEX')
BEGIN
ALTER TABLE salesman
ADD CONSTRAINT prim_sales PRIMARY KEY (no),
CONSTRAINT foreign_sales FOREIGN KEY(product_no)
REFERENCES product
END

With Query Example

 with sam as 
(
 select * from TblSample
  )
  select * from  sam as s join TblSample1 as Sn on sn.id=s.id

OutPut
ID	Name	Age	Sex	ID	Name	Age	Sex
1	vino	23	Male	1	vino	23	Male
2	karthick	25	Male	2	karthick	25	Male
3	Rajesh	24	Male	3	Rajesh	24	Male

Insert Query Example

CREATE PROC  [dbo].[Tblsample_insert]
(
 @Name varchar(40),
 @Age int,
 @Sex varchar(45)
 )
AS
BEGIN
INSERT INTO [LINQ].[dbo].[TblSample]
           ([Name]
           ,[Age]
           ,[Sex])
     VALUES
           (@NAME,
            @Age,
            @Sex)
END

Merge Example

MERGE tblsample AS TARGET
USING
      (SELECT Id,
              Name,
              Age,
              Sex FROM tblsample1) AS Mer ON TARGET.id=Mer.id

 WHEN MATCHED AND
      
       (TARGET.Name<>mer.Name)or
       (TARGET.Age<>mer.Age)or
       (TARGET.Sex<>mer.Sex)

 THEN UPDATE
 SET    TARGET.Name=mer.Name,
        TARGET.Age=mer.Age,
        TARGET.Sex=mer.Sex
   
 WHEN NOT MATCHED BY TARGET
 THEN INSERT
        (
         Name,
         Age,
         Sex
         )
 VALUES
        ( mer.Name,
          mer.Age,
          mer.Sex
         );

Pivot example

DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
SELECT  @listCol = STUFF(( SELECT DISTINCT
                                '],[' + ltrim(str(YEAR(transactiondate)))
                        FROM   TblBillingTransaction
                        ORDER BY '],[' + ltrim(str(YEAR(transactiondate)))
                        FOR XML PATH('')
                                    ), 1, 2, '') + ']'
print @listCol
SET @query =

'select * from(select scanamount, datepart(m,transactiondate) as Month ,year(transactiondate) as transactiondate  from TblBillingTransaction where Obsolete=0 and BillingId!=0)as v
pivot (sum(scanamount) for transactiondate
in('+@listCol+') ) as PVT order by 1 asc'


EXECUTE (@query)

RESULT

Month	2010	2011
1	1678000	8983115
2	4612000	11186996
3	5699800	10595608
4	5960000	9839065
5	6264010	9913435
6	7591395	11367465
7	9239360	12233055
8	7814870	11166010
9	8532125	11613364
10	8831590	12208845
11	8928700	7786655
12	10024105	NULL

STUFF Example

STUFF ( character_expression , start , length ,character_expression )

The following example returns a character string created by deleting three characters from the first string, abcdef, starting at position 2, at b, and inserting the second string at the deletion point.

SELECT STUFF('abcdef', 2, 3, 'ijklmn');
GO
Result
aijklmnef

At first sight it might not be clear directly how to insert a character (or a string) instead of substituting characters in the original string.
To insert a string, without replacing/substituting characters in the original string you need to specify a length of 0 (zero).

SELECT STUFF('abcghi', 4, 0, 'DEF');

Result
abcDEFghi

"Identify Missing Identity Values"

-- Step #1: Create Table and Populate with ValuesCREATE TABLE #CarType ([ID]
[Name] INT IDENTITY, VARCHAR(20) )INSERT INTO #CarType ( [Name] ) VALUES ( 'Bentley' )INSERT INTO #CarType ( [Name] ) VALUES ( 'BMW' )INSERT INTO #CarType ( [Name] ) VALUES ( 'Ferrari' )INSERT INTO #CarType ( [Name] ) VALUES ( 'Lamborghini' )INSERT INTO #CarType ( [Name] ) VALUES ( 'Hummer' )INSERT INTO #CarType ( [Name] ) VALUES ( 'Jaguar' )INSERT INTO #CarType ( [Name] ) VALUES ( 'Lexus' )INSERT INTO #CarType ( [Name] ) VALUES ( 'Mercedes Benz' )INSERT INTO #CarType ( [Name] ) VALUES ( 'Porsche' )INSERT INTO #CarType ( [Name] ) VALUES ( 'Volvo' )SELECT * FROM #CarType--The output of the SELECT statement will be as follows:ID Name------ --------------1 Bentley
2 BMW
3 Ferrari
4 Lamborghini
5 Hummer
6 Jaguar
7 Lexus
8 Mercedes Benz
9 Porsche
10 Volvo--Let's say certain records have been deleted from the table, as shown in the following script:
-- Step #2: Delete IDsDELETE FROM #CarType WHERE [ID] IN (3, 4, 9)SELECT * FROM #CarType--The table now has the following records:ID Name------ --------------1 Bentley
2 BMW
5 Hummer
6 Jaguar
7 Lexus
8 Mercedes Benz
10 Volvo--One way to identify the missing/deleted identity values is to loop through the table
--starting from 1 up to the maximum identity value as shown in the following script:
-- Step #3 (Option #1): Identify Missing IDENTITY ValuesDECLARE
DECLARE
DECLARE @ID INT @MaxID INT @MissingCarTypeIDs TABLE ( [ID] INT )SELECT @MaxID = [ID] FROM #CarTypeSET @ID = 1WHILE @ID <= @MaxIDBEGIN
IF NOT EXISTS (SELECT 'X' FROM #CarTypeWHERE [ID] = @ID)INSERT INTO @MissingCarTypeIDs ( [ID] )VALUES ( @ID )SET @ID = @ID + 1END
SELECT * FROM @MissingCarTypeIDs

Alter table

IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tblpatient' and COLUMN_NAME='Externalid')BEGIN
ALTER TABLE tblpatient ADD vinoth NVARCHAR(9) NULLEND