sql-3

sql3.sql

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.


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

*******************************************
-- 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%';



*******************************************
-- 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

========================
-- 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]

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


==============================
-- 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]

--when you ORDER a set of records in analytic functions you can specify a range of rows to consider,ignoring the others. You can do this using the ROWS clause
--UNBOUNDED PRECEDING The range starts at the first row of the partition.
--UNBOUNDED FOLLOWING The range ends at the last row of the partition.
--CURRENT ROW range begins at the current row or ends at the current row
--n PRECEDING or n FOLLOWING The range starts or ends n rows before or after the current row

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 sum(discountPct) over(partition by Category order by Category ) as SumPct, 
sum(discountPct) over(partition by Category order by Category rows between unbounded preceding and current row) as SumPct2,discountPct, Category
  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'

-- return value
ALTER PROCEDURE SP_Address (@HowMuch as int)
 AS
Begin
 SELECT top(@HowMuch) * from  [Person].[Address]
 Return 1
END

Declare @i int
exec @i = SP_Address 1
select @i

-- change "global" variable value

ALTER PROCEDURE SP_Address (@x as varchar(100) output)
as 
 begin 
	set @x = 'hi'
 end

declare @a as varchar(100) = 'hello'
select @a
exec SP_Address @a output
select @a

-- 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 --Local/Globle
  for 
   select firstname, lastname from [Person].[Person]
open test_cursor
fetch next from test_cursor
--select @@FETCH_STATUS --0 = successful, -1 = not successful

--while @@FETCH_STATUS = 0
    --fetch next from test_cursor

close test_cursor -- close the loop, but cursor can be opend.
deallocate test_cursor -- remove cursor from the sytem

--
Declare test_cursor cursor scroll --Read_Only (Can not update)/Scroll_Lock (record get locked in the table)/Optimistic(http://technet.microsoft.com/en-us/library/ms191493(v=sql.105).aspx)
for 
select firstname, lastname from [Person].[Person]
open test_cursor
fetch next from test_cursor
--fetch first from test_cursor
--fetch last from test_cursor
--fetch prior from test_cursor
--fetch absolute 10 from test_cursor
--fetch absolute -10 from test_cursor --from the end
--fetch relative 10 from test_cursor
--fetch relative -10 from test_cursor

close test_cursor  
deallocate test_cursor 

--

Declare @F varchar(100)
Declare @L varchar(100)

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

fetch next from test_cursor
 into @F, @L
 Print 'My Name is '+@F +' ' +@L

close test_cursor  
deallocate test_cursor 


--

Create Proc test_proc (
  @F varchar(100),
  @L varchar(100)
) AS 
BEGIN 
  Print 'My Name is '+@F +' ' +@L
END

Declare test_cursor cursor scroll for 
select firstname, lastname from [Person].[Person]
open test_cursor
Declare @F varchar(100)
Declare @L varchar(100)
fetch next from test_cursor
 into @F, @L
 exec test_proc @F,@L

close test_cursor  
deallocate test_cursor 

--

Declare test_cursor cursor 
for 
select firstname, lastname from [Person].[Person]
for 
update of FirstName
open test_cursor
update [Person].[Person] set
FirstName = 'Jake'
where Current of test_cursor

close test_cursor  
deallocate 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


--

DECLARE @i as int = 1
DECLARE @fname as varchar(200)
Declare @myTable as Table (name varchar(100))

while @i < 5 
BEGIN
   select @fname = firstname  from person.person 
   where BusinessEntityID = @i
   insert into @myTable 
   (name) values
   (@fname)
  SET @i = @i +1 
END

select * from @myTable

==================
 
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 an trigger to run after insert
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

-- run after update
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), updated smallint )

insert into MyTest 
(name,updated) values
('george',1)

select * from MyTest

create trigger updated 
on MyTest 
After Update as 
Begin
 update MyTest set 
 updated = 1+updated
 where id in (select id from inserted)
end

update MyTest set 
name = 'Peter'

-- instead of delete 
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), updated smallint,valid bit )

insert into MyTest 
(name,updated,valid) values
('george',1,'1')

select * from MyTest

create trigger deleteperson 
on MyTest 
instead of delete
as
Begin
 update MyTest set 
 valid = '0'
 where id in (select id from deleted)
end

delete from myTest