sql-2.sql

*******************************************************************
-- INLINE CALCULATIONS
SELECT VacationHours + SickLeaveHours as daysOff, JobTitle,VacationHours, SickLeaveHours
FROM [AdventureWorks2012].[HumanResources].[Employee]

SELECT [CommissionPct] ,[SalesYTD], ([SalesYTD]/100)*[CommissionPct] as commission
  FROM [AdventureWorks2012].[Sales].[SalesPerson]



*********************************************
SELECT ISNUMERIC('SQL') AS isnmumeric_result;

SELECT top 1000   productid, [ListPrice], 
      IIF([ListPrice] > 50, 'high','low') AS pricepoint
FROM Production.Product;

SELECT CHOOSE (3, 'a', 'b', 'c') 
SELECT CHOOSE (4, 'a', 'b', 'c') 

SELECT isnull([Title],'Dr.') ,[FirstName]  FROM [AdventureWorks2012].[Person].[Person]

SELECT isnull([Title],'Dr.') , COALESCE([Title],'Dr.'), 
isnull([Title],MiddleName) ,
COALESCE([Title],MiddleName,LastName) , 
[FirstName]  FROM [AdventureWorks2012].[Person].[Person]

select nullif(1,2)
select nullif(5,2)
select nullif(1,1)
select nullif(4,4)

SELECT AVG(ListPrice) , MIN(ListPrice), max(ListPrice),
sum(ListPrice), count(ListPrice), COUNT_BIG(ListPrice)
FROM Production.Product;

SELECT AVG(ListPrice) , MIN(ListPrice), max(ListPrice),
sum(ListPrice), count(ListPrice), COUNT_BIG(ListPrice), color
FROM Production.Product
group by color

SELECT AVG(ListPrice) , MIN(ListPrice), max(ListPrice),
sum(ListPrice), count(ListPrice) as c, COUNT_BIG(ListPrice), color
FROM Production.Product
group by color
having count(*) > 30

-- As a rule of thumb, use WHERE before GROUP BY and HAVING after GROUP BY. It is a rather primitive rule, but it is useful in 90+ %% of cases.

*******************************************
-- CASE

  SELECT [JobTitle] , Gender,
  CASE Gender
    WHEN 'F' THEN 'Woman'
    ELSE 'Men'
  END
FROM [AdventureWorks2012].[HumanResources].[Employee]

*******************************************
SELECT CAST(SYSDATETIME() AS date);
SELECT CAST( '12/01/2013' AS date);
SELECT CAST( '123' AS varchar) + CAST( '2' AS varchar) 
SELECT CAST( '123' AS int) + CAST( '2' AS int) 


-- rank data compared to others
SELECT TOP(10) BusinessEntityID, Rate, RANK() OVER (ORDER BY Rate DESC) AS RankBySalary
FROM HumanResources.EmployeePayHistory  
ORDER BY BusinessEntityID;

-- top with ties
CREATE TABLE #MyTable (Name varchar(20), age INT)
 INSERT INTO #MyTable
 SELECT 'Lai', 20 UNION ALL
 SELECT 'Simson', 20 UNION ALL
 SELECT 'Ted', 20 UNION ALL
 SELECT 'Hajek', 19 UNION ALL
 SELECT 'Rosy', 19 UNION ALL
 SELECT 'Shona', 19 UNION ALL
 SELECT 'Loris', 18 UNION ALL
 SELECT 'Billye', 17

SELECT TOP(1) WITH TIES * FROM #MyTable ORDER BY age desc --WITH TIES grabs all other records that matches the value of “order by” column


*******************************************
-- CONVERT DATA BETWEEN DIFFERENT DATA TYPES.

SELECT PARSE('02/12/2012' AS datetime2 USING 'en-US') AS parse_result; 

select CONVERT(date, 'Monday, 13 December 2010' ) -- failes.
SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result;

SELECT TRY_PARSE('SQLServer' AS datetime2 USING 'en-US') AS try_parse_result; 
select try_CONVERT(date, 'Monday, 13 December 2010' )

BEGIN TRY
  SELECT top 1 * from Person.person --where PersonType = 1
END TRY
BEGIN CATCH
  SELECT top 2 * from Person.person
END CATCH;

BEGIN TRY
  SELECT top 1 * from Person.person where PersonType = 1
END TRY
BEGIN CATCH
  PRINT 'Something is wrong';
  THROW;
END CATCH

BEGIN TRY
  SELECT top 1 * from Person.person where PersonType = 1
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE();
  PRINT ERROR_LINE();
  PRINT ERROR_NUMBER();
  PRINT ERROR_SEVERITY();
  PRINT ERROR_STATE();
  THROW;
END CATCH



-- http://msdn.microsoft.com/en-us/library/ms187928.aspx // chart/

SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice, CONVERT(int, ListPrice)
FROM Production.Product
WHERE CONVERT(int, ListPrice) = 35

-- Use CONVERT.
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';


*********************** 
-- SUB QUERY
SELECT * FROM Sales.SalesPerson where TerritoryID in
( select TerritoryID from Sales.SalesTerritory where name = 'Northwest' )

-- Correlated Subqueries
SELECT *
FROM Sales.SalesPerson as SalesQ
where TerritoryID in
( select TerritoryID from Sales.SalesTerritory where name = 'Northwest' and costytd = SalesQ.SalesLastYear)

*******************************************
-- JOINTS

SELECT        Sales.Currency.Name, Sales.CurrencyRate.CurrencyRateDate, Sales.CurrencyRate.FromCurrencyCode, Sales.CurrencyRate.ToCurrencyCode, Sales.CurrencyRate.AverageRate,  Sales.CurrencyRate.EndOfDayRate
FROM            
Sales.Currency INNER JOIN Sales.CurrencyRate 
ON Sales.Currency.CurrencyCode                     = Sales.CurrencyRate.FromCurrencyCode


SELECT        Sales.Currency.Name, Sales.CurrencyRate.CurrencyRateDate, Sales.CurrencyRate.FromCurrencyCode, Sales.CurrencyRate.ToCurrencyCode, Sales.CurrencyRate.AverageRate,  Sales.CurrencyRate.EndOfDayRate
FROM            
Sales.Currency a INNER JOIN Sales.CurrencyRate  b
ON a.CurrencyCode                                  = b.FromCurrencyCode

SELECT        a.Name, b.CurrencyRateDate, b.FromCurrencyCode, b.ToCurrencyCode, b.AverageRate, b.EndOfDayRate, Sales.Currency.Name AS ToName
FROM            
Sales.Currency AS a INNER JOIN Sales.CurrencyRate AS b 
ON a.CurrencyCode                                  = b.FromCurrencyCode 
INNER JOIN Sales.Currency 
ON b.ToCurrencyCode                                = Sales.Currency.CurrencyCode


SELECT        a.Name, b.CurrencyRateDate, b.FromCurrencyCode, b.ToCurrencyCode, b.AverageRate, b.EndOfDayRate
FROM            Sales.CurrencyRate AS b RIGHT OUTER JOIN
Sales.Currency AS a ON b.FromCurrencyCode          = a.CurrencyCode


SELECT        Production.Product.ProductID, Production.Product.Name
FROM            
Production.Product 
INNER JOIN Production.ProductSubcategory 
ON Production.Product.ProductSubcategoryID         = Production.ProductSubcategory.ProductSubcategoryID 
INNER JOIN Production.ProductCategory 
ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID

- self join
CREATE TABLE #MyTable (PersonID INT, Person varchar(20), Parent INT) 
INSERT INTO #MyTable 
SELECT 1, 'Mom', 0 UNION ALL 
SELECT 2, 'Son', 1 UNION ALL 
SELECT 3, 'Daughter', 1

select a.Person, b.Person from #MyTable a
left outer join #MyTable b on a.Parent = b.PersonID

-- cross join

SELECT p.BusinessEntityID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
ORDER BY p.BusinessEntityID;

SELECT p.BusinessEntityID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
WHERE p.TerritoryID = t.TerritoryID
ORDER BY p.BusinessEntityID;



========================
-- UNION VS UNION ALL
CREATE TABLE #MyTable (Name varchar(20), age INT) 
INSERT INTO #MyTable 
SELECT 'Lai', 20 UNION ALL 
SELECT 'Simson', 20 UNION ALL 
SELECT 'Ted', 20 UNION ALL 
SELECT 'Hajek', 19 UNION ALL 
SELECT 'Rosy', 19 UNION ALL 
SELECT 'Shona', 19 UNION ALL 
SELECT 'Loris', 18 UNION ALL 
SELECT 'Billye', 17


CREATE TABLE #MyTable2 (Name varchar(20), age INT) 
INSERT INTO #MyTable2 
SELECT 'Lai', 20 UNION ALL 
SELECT 'Simson', 20 UNION ALL 
SELECT 'Ted', 20 UNION ALL 
SELECT 'Hajek', 19 UNION ALL 
SELECT 'Rosy', 19 UNION ALL 
SELECT 'Shona', 19 UNION ALL 
SELECT 'Blair', 18 UNION ALL 
SELECT 'Adan', 17

select * from #MyTable
union 
select * from #MyTable2

select * from #MyTable
union all
select * from #MyTable2

select name from #MyTable
INTERSECT 
select name from #MyTable2

select name from #MyTable
EXCEPT  
select name from #MyTable2

select * from #MyTable as a
OUTER APPLY 
(select name from #MyTable2) as b

select * from #MyTable as a
CROSS APPLY 
(select name as Name2 from #MyTable2) as b

select * from #MyTable as a
OUTER APPLY 
(select name from #MyTable2 where name = a.name) as b

select * from #MyTable as a
CROSS APPLY 
(select name from #MyTable2 where name = a.name) as b


=========================
-- OFFSET 
SELECT top 10 Percent * FROM [Production].[Product]

--(windowing)
SELECT  * FROM [Production].[Product]
order by ProductID
offset 10 rows fetch first 10 rows only

============================
-- VIEWS

Create view Person.FullAddress 
as
    SELECT Person.Person.Title , Person.Person.FirstName , Person.Person.MiddleName
    , Person.Person.LastName , Person.Person.Suffix , 
    Person.Address.AddressLine1, Person.Address.AddressLine2, Person.Address.City, Person.Address.StateProvinceID, Person.Address.PostalCode
    FROM            Person.Address INNER JOIN
    Person.BusinessEntityAddress ON Person.Address.AddressID = Person.BusinessEntityAddress.AddressID INNER JOIN
    Person.Person ON Person.BusinessEntityAddress.BusinessEntityID = Person.Person.BusinessEntityID
-- order by Title

select * from Person.FullAddress

Drop view Person.FullAddress

==============================
-- TABLE-VALUED FUNCTIONS

DECLARE @BusinessEntityID INT = 1;
select * from Person.Person where BusinessEntityID = @BusinessEntityID

DECLARE @FN Varchar(20) = 'kim';
select * from Person.Person where FirstName = @FN


CREATE FUNCTION Person.fn_FullAddress (@BusinessEntityID INT)
RETURNS TABLE
AS
RETURN
    SELECT Person.Person.Title , Person.Person.FirstName , Person.Person.MiddleName
    , Person.Person.LastName , Person.Person.Suffix , 
    Person.Address.AddressLine1, Person.Address.AddressLine2, Person.Address.City, Person.Address.StateProvinceID, Person.Address.PostalCode
    FROM Person.Address INNER JOIN
    Person.BusinessEntityAddress ON Person.Address.AddressID       = Person.BusinessEntityAddress.AddressID INNER JOIN
    Person.Person ON Person.BusinessEntityAddress.BusinessEntityID = Person.Person.BusinessEntityID
    WHERE   Person.Person.BusinessEntityID                         = @BusinessEntityID ;


select * from Person.fn_FullAddress(1)

================================
-- DERIVED TABLES 
select firstName from 
( select * from person.person ) as a

select * from 
( select * from person.person where firstName = 'kim' ) as a
where MiddleName = 'b'

select FiirstName from 
( 
  select * from Person.fn_FullAddress(1)
) as a

===================================
-- COMMON TABLE EXPRESSIONS
WITH FakeTable AS
  ( select * from person.person
  )
SELECT LastName FROM FakeTable where firstname = 'kim'

==================================

SELECT ROW_NUMBER() OVER(ORDER BY [City] DESC) AS Row, 
    [AddressLine1],[City]
FROM [Person].[Address]
--order by [AddressID]

select * from 
( SELECT ROW_NUMBER() OVER(ORDER BY [City] DESC) AS Row, 
    [AddressLine1],[City]
FROM [Person].[Address] ) as a
-- where row > 10 and row < 20

SELECT ROW_NUMBER() OVER(PARTITION BY stateprovinceid ORDER BY [City] DESC) AS Row, 
    [AddressLine1],[City], stateprovinceid
FROM [Person].[Address] 

select count(*) as addcount, stateprovinceid --,City
from [Person].[Address] 
group by stateprovinceid

SELECT count(*) OVER(PARTITION BY stateprovinceid ) AS addcount, 
    [AddressLine1],[City], stateprovinceid
FROM [Person].[Address] 

select * from [Person].[Address] tablesample(10 percent)

select * from [Person].[Address] tablesample(10 percent) REPEATABLE (56) 
select * from [Person].[Address] tablesample(1000 rows)

SELECT sum(discountPct) 
  FROM [AdventureWorks2012].[Sales].[SpecialOffer]

SELECT sum(discountPct) over(partition by Category) as SumPct, Category
  FROM [AdventureWorks2012].[Sales].[SpecialOffer]

SELECT sum(discountPct) over(order by Category
  rows between unbounded preceding and current row
) as AddUpPct, Category, discountPct
  FROM [AdventureWorks2012].[Sales].[SpecialOffer]

SELECT sum(discountPct) over(order by Category
  rows between unbounded preceding and unbounded following
) as AddUpPct, Category, discountPct
  FROM [AdventureWorks2012].[Sales].[SpecialOffer]

SELECT first_value(discountPct) over(order by Category
  rows between unbounded preceding and unbounded following
) as first,
last_value(discountPct) over(order by Category
  rows between unbounded preceding and unbounded following
) as last,
 Category, discountPct
  FROM [AdventureWorks2012].[Sales].[SpecialOffer]

SELECT lead(discountPct) over(order by Category
) as leading,
lag(discountPct) over(order by Category
) as laggin,
 Category, discountPct
  FROM [AdventureWorks2012].[Sales].[SpecialOffer]

SELECT  Category, discountPct,
ntile(2) over (order by Category ) as grp
  FROM [AdventureWorks2012].[Sales].[SpecialOffer]

SELECT TOP(10) BusinessEntityID, Rate, dense_RANK() OVER (ORDER BY Rate DESC) AS DRankBySalary, RANK() OVER (ORDER BY Rate DESC) AS RankBySalary
FROM HumanResources.EmployeePayHistory  
ORDER BY RankBySalary;

==================================================
SELECT [BusinessEntityID]
      ,[EmailAddressID]
      ,[EmailAddress]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [Person].[EmailAddress]
for xml path

SELECT [BusinessEntityID]
      ,[EmailAddressID]
      ,[EmailAddress]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [Person].[EmailAddress]
for xml path('Email')

SELECT [BusinessEntityID]
      ,[EmailAddressID]
      ,[EmailAddress]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [Person].[EmailAddress]
for xml path('Email'), root('Emails')

SELECT [BusinessEntityID] as [@id]
      ,[EmailAddressID]
      ,[EmailAddress]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [Person].[EmailAddress]
for xml path('Email'), root('Emails')

SELECT [BusinessEntityID] as [@id]
      ,[EmailAddressID] as [extra/addressid]
      ,[EmailAddress]
      ,[rowguid] as [extra/guid]
      ,[ModifiedDate]
  FROM [Person].[EmailAddress]
for xml path('Email'), root('Emails')

================================================
DECLARE @x xml
SET @x = '
 <Orders>
  <Order OrderID="100" OrderDate="1/30/2012">
   <OrderDetail ProductID="1" Quantity="3">
    <Price>350</Price>
   </OrderDetail>
   <OrderDetail ProductID="2" Quantity="8">
    <Price>500</Price>
   </OrderDetail>
   <OrderDetail ProductID="3" Quantity="10">
    <Price>700</Price>
   </OrderDetail>
  </Order>
  <Order OrderID="200" OrderDate="2/15/2012">
   <OrderDetail ProductID="4" Quantity="5">
    <Price>120</Price>
   </OrderDetail>
  </Order>
 </Orders>'

SELECT @x.query('/Orders')
SELECT @x.query('/Orders/Order/OrderDetail')
SELECT @x.query('/Orders/Order[1]/OrderDetail[2]')
SELECT @x.query('/Orders/Order/OrderDetail[@ProductID="2"]')
SELECT @x.query('/Orders/Order[@OrderID="100"]')
SELECT @x.query('/Orders/Order[@OrderID="100"]/OrderDetail[Price>600]')
SELECT @x.value('/Orders[1]/Order[@OrderID="100"][1]/OrderDetail[Price>600][1]', 'float')
SELECT @x.value('/Orders[1]/Order[@OrderID="200"][1]/@OrderDate', 'datetime')
================================================

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' as ns)
SELECT Instructions.query('/ns:root/ns:Location')
FROM Production.ProductModel

===============================================
-- Grouping SETS - Try with Union ALL

SELECT count(*)as total, lastname
  FROM [AdventureWorks2012].[Person].[Person]
  group by lastname

SELECT lastname, firstname, count(*)
FROM [AdventureWorks2012].[Person].[Person]
GROUP BY GROUPING SETS ( lastname, firstname )


===============================================
--CUBE 
create table ##Inventory (item Varchar(20), Color Varchar(20), Quantity int )

-- insert a single raw into the new database
insert into ##Inventory
(item,Color,Quantity)
values ('Table','Blue',124) 

-- insert multiple raws at once (compare with UNION ALL used in previous insert statements)
insert into ##Inventory
(item,Color,Quantity)                      
values 
('Table','Red',223)                      
, ('Chair','Blue',101)                        
, ('Chair','Red',210)    

-- cube show all possible combinations 
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM ##Inventory
GROUP BY Item, Color WITH CUBE
--- both the same 
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM ##Inventory
GROUP BY CUBE(Item, Color)

-- Compare CUBE with ROLL UP
--- ROLL UP 
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM ##Inventory
GROUP BY rollup(Item, Color)

-- Try changing column order
SELECT Color,Item  SUM(Quantity) AS QtySum
FROM ##Inventory
GROUP BY rollup(Item, Color)

-- group ID flag totals and subtotals 
SELECT GROUPING_ID(Color) as ColorID,
  GROUPING_ID(Item) as ItemID,
  Color,Item,  
  SUM(Quantity) AS QtySum
FROM ##Inventory
GROUP BY cube(Item, Color)

==============================================
== Fetch System information 

-- fetch user tables of the system
SELECT  name, object_id, schema_id, type, type_desc
FROM sys.tables
order by name

-- Fetch all objects of the system
SELECT name, object_id, schema_id, type, type_desc
FROM sys.objects;
-- try to fetch user tables using sys.objects

-- Fetch Table information of the current database
SELECT TABLE_CATALOG, TABLE_SCHEMA, 
  TABLE_NAME, TABLE_TYPE
FROM  INFORMATION_SCHEMA.TABLES

-- fetch resources used by VIEWS. 
SELECT VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME,  TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,  COLUMN_NAME
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE;

--- system variables 
SELECT @@VERSION AS SQL_Version;
SELECT SERVERPROPERTY('ProductVersion') AS version;
SELECT SERVERPROPERTY('Collation') AS collation;

--- there are some system SP too
EXEC sys.sp_databases;
EXEC sys.sp_help N'Sales.Store'
EXEC sys.sp_tables 

EXEC sys.sp_tables 
@table_owner = 'Sales';

EXEC sp_who
Execute sp_lock
sp_helplanguage

--Dynamic Management Views 
SELECT session_id, login_time, program_name
FROM sys.dm_exec_sessions

--Dynamic Management Functions
SELECT  referencing_schema_name,  referencing_entity_name,  referencing_class_desc
FROM  sys.dm_sql_referencing_entities(
  'Person.Person', 'OBJECT');

==========================================
-- Dynamic SQL

Exec ('SELECT * from [Person].[Address]')

EXEC sys.sp_executesql @statement = N'SELECT * from [Person].[Address]'

DECLARE @MySQLString as nvarchar(200) = 'SELECT * from [Person].[Address]'
EXEC sys.sp_executesql @statement = @MySQLString

DECLARE @Table as varchar(100) = 'Person.Password'
DECLARE @MySQLString as nvarchar(200) = 'SELECT * from '+@Table
EXEC sys.sp_executesql @statement = @MySQLString

DECLARE @Table as varchar(100) = 'Person.Password'
DECLARE @top as int = 10

--DECLARE @topString as nvarchar(10)
--SET @topString = cast(@top as nvarchar(10) )

DECLARE @MySQLString as nvarchar(200) = 'SELECT top '+@topString+' * from '+@Table
EXEC sys.sp_executesql @statement = @MySQLString

======================================================
Stored Procedures:
- Can be used to read and modify data.
- To run an SP Execute or Exec is used, cannot be used with SELECT statement.
- Cannot JOIN a SP in a SELECT statement.
- Can use Table Variables as well as Temporary Tables inside an SP.
- Can create and use Dynamic SQL.
- Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP.
- Can use used with XML FOR clause.
- Can use a UDF inside a SP in SELECT statement.
- Cannot be used to create constraints while creating a table.
- Can execute all kinds of functions, be it deterministic or non-deterministic.

User Difinded Functions:
- Can only read data, cannot modify the database.
- Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER).
- Can JOIN a UDF in a SELECT statement.
- Cannot use a Temporary Table, only Table Variables can be used.
- Cannot use a Dynamic SQL inside a UDF.
- Cannot use transactions inside a UDF.
- Cannot be used with XML FOR clause.
- Cannot execute an SP inside a UDF.
- Can be used to create Constraints while creating a table.
- Cannot execute some non-deterministic built-in functions, like GETDATE().

-- Create a simple SP
CREATE PROCEDURE SP_Address 
AS
SELECT * from  [Person].[Address]
-- Drop SP
drop PROCEDURE SP_Address

-- bring in variables.
CREATE PROCEDURE SP_Address (@HowMuch as int)
 AS
SELECT top(@HowMuch) * from  [Person].[Address]

EXEC SP_Address @HowMuch=10

-- edit SP
ALTER PROCEDURE  SP_Address (@HowMuch as int,@City as Varchar(20) )
 AS
SELECT top(@HowMuch) * from  [Person].[Address] where city = @City

SP_Address @HowMuch=10, @city='york'

-- bring in the previous dynamic sql into a SP
CREATE PROCEDURE getAnything 
(@Table as varchar(100), @top as int)
as 
Begin
  DECLARE @topString as nvarchar(10)
  SET @topString = cast(@top as nvarchar(10) )

  DECLARE @MySQLString as nvarchar(200) = 'SELECT top '+@topString+' * from '+@Table
  EXEC sys.sp_executesql @statement = @MySQLString
End

getAnything @Table = 'Person.Password', @top = 10


Declare test_cursor cursor for 
  select firstname, lastname from [Person].[Person]

open test_cursor
fetch next from test_cursor

--close test_cursor

============================================
create table #MyTest (id int NOT NULL PRIMARY KEY)

BEGIN TRY
  create table #MyTest (id int NOT NULL PRIMARY KEY)
END TRY
BEGIN CATCH
  Drop Table #myTest
  create table #MyTest (id int NOT NULL PRIMARY KEY)
END CATCH

select  * from tempdb.dbo.sysobjects where name like '#mytest_%'
select OBJECT_ID('TempDB.dbo.#MyTest')

if  OBJECT_ID('TempDB.dbo.#MyTest') is not null drop table #MyTest
if  OBJECT_ID('TempDB.dbo.#MyTest') is not null 
drop table #MyTest

-- fail
if  OBJECT_ID('TempDB.dbo.#MyTest') is not null 
  select 1 
  drop table #MyTest

-- group multi line IF condition 
if  OBJECT_ID('TempDB.dbo.#MyTest') is not null 
BEGIN
  select 1 
  drop table #MyTest
END

----
create table #MyTest (id int NOT NULL PRIMARY KEY)
insert into #MyTest
(id) values
(1)
,(2)
,(3)
select * from #MyTest

---
if  OBJECT_ID('TempDB.dbo.#MyTest') is not null 
BEGIN
  drop table #MyTest
END
create table #MyTest (id int NOT NULL PRIMARY KEY)
insert into #MyTest
(id) values
(1)
,(2)
,(3)
,(3)
select * from #MyTest
---

if  OBJECT_ID('TempDB.dbo.#MyTest') is not null 
BEGIN
  drop table #MyTest
END
BEGIN TRANSACTION
  BEGIN TRY
    create table #MyTest (id int NOT NULL PRIMARY KEY)
    insert into #MyTest
    (id) values
    (1)
    ,(2)
    ,(3)
    ,(3)
    COMMIT
  END TRY
  BEGIN CATCH
    ROLLBACK
  END CATCH

select * from #MyTest
--
if OBJECT_ID('TempDB.dbo.#MyTest') is not null 
BEGIN
  drop table #MyTest
END
BEGIN TRANSACTION
  SET XACT_ABORT ON
    create table #MyTest (id int NOT NULL PRIMARY KEY)
    insert into #MyTest
    (id) values
    (1)
    ,(2)
    ,(3)
    ,(3)
COMMIT

select * from #MyTest

==============================
-- While loop
DECLARE @i as int = 1

while @i < 5 
BEGIN
  PRINT @i
  SET @i = @i +1 
END

--
DECLARE @i as int = 1
DECLARE @fname as varchar(200)

while @i < 5 
BEGIN
   select @fname = firstname from person.person where BusinessEntityID = @i
   PRINT @fname
  SET @i = @i +1 
END

==================

CREATE INDEX Person_Name
ON Person.Person (FirstName)
--

SET STATISTICS TIME off;
SET STATISTICS IO off;

SELECT Person.*
FROM Person.Person INNER JOIN
 Person.Password ON Person.Person.BusinessEntityID = Person.Password.BusinessEntityID

==================
== Update, Alter
update Person.Person set
FirstName = 'John',
MiddleName = 'A'
where BusinessEntityID = 1

create table #MyTest (id int NOT NULL PRIMARY KEY)
    insert into #MyTest
    (id) values
    (1)
    ,(2)
    ,(3)
select * from #myTest
delete from #myTest where id = 3
select * from #myTest

select * from #myTest
TRUNCATE TABLE #myTest
select * from #myTest

ALTER TABLE #MyTest 
ADD UserName varchar(200)
select * from #myTest

--- update a table though a view
Create view Person.TestView as 
select BusinessEntityID, firstname, lastname from Person.Person

select * from Person.TestView where BusinessEntityID = 1

update Person.TestView set
FirstName = 'Ken'
where BusinessEntityID = 1

--***  Database Engine must be able to unambiguously trace modifications from the view definition to one base table

==================================
== Trigger

if OBJECT_ID('TempDB.dbo.#MyTest') is not null 
BEGIN
  drop table #MyTest
END
create table #MyTest (id int NOT NULL PRIMARY KEY identity, name varchar(30) )

insert into #MyTest 
(name) values
('george')

select * from #MyTest

--

if OBJECT_ID('AdventureWorks2012.dbo.MyTest') is not null 
BEGIN
  drop table MyTest
END
create table MyTest (id int NOT NULL PRIMARY KEY identity, name varchar(30) )
--
create trigger ToUpperCase on MyTest after insert as
update MyTest set name = upper(name) where id = (select id from inserted)
--
insert into MyTest 
(name) values
('george')

select * from MyTest