70-461 Querying Microsoft SQL Server 2012/2014

Hi, My name is Jimmy Sam. If you need DBA help, please email to jimmy_sam001@yahoo.com for further discussion. Thanks.
mssql certification 70-461     70-461     20461     70-462     70-463     70-464     70-465    

Quick Search


search term:      
   



Create Database Objects


Create and alter tables using T-SQL syntax (simple statements)
        Create tables without using the built in tools; 
        ALTER; DROP; ALTER COLUMN; CREATE

Create and alter views (simple statements)
        Create indexed views; 
        create views without using the built in tools; CREATE, ALTER, DROP

Design views
        Ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); 
        security implications

Create and modify constraints (simple statements)
        Create constraints on tables; 
        define constraints; 
        unique constraints; 
        default constraints; 
        primary and foreign key constraints

Create and alter DML triggers
        Inserted and deleted tables; 
        nested triggers; 
        types of triggers; 
        update functions; 
        handle multiple rows in a session; 
        performance implications of triggers


Working With Data


Query data by using SELECT statements

Use the ranking function to select top(X) rows for multiple categories in a single query; 
        write and perform queries efficiently using the new (SQL 2005/8->) code items such as synonyms, and joins (except, intersect); 
        implement logic which uses dynamic SQL and system metadata; 
        write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; 
        determine what code may or may not execute based on the tables provided; 
        given a table with constraints, determine which statement set would load a table; 
        use and understand different data access technologies; 
        case versus isnull versus coalesce 

Implement sub-queries
        Identify problematic elements in query plans; 
        pivot and unpivot; 
        apply operator; 
        cte statement; 
        with statement

Implement data types
        Use appropriate data; 
        understand the uses and limitations of each data type; 
        impact of GUID (newid, newsequentialid) on database performance, when to use what data type for columns

Implement aggregate queries
        New analytic functions; 
        grouping sets; 
        spatial aggregates; 
        apply ranking functions

Query and manage XML data
        Understand XML datatypes and their schemas and interop w/, limitations and restrictions; 
        implement XML schemas and handling of XML data; 
        XML data: how to handle it in SQL Server and when and when not to use it, including XML namespaces; 
        import and export XML; 
        XML indexing


Modify Data


Create and alter stored procedures (simple statements)
        Write a stored procedure to meet a given set of requirements; 
        branching logic; 
        create stored procedures and other programmatic objects; 
        techniques for developing stored procedures; 
        different types of storeproc result; 
        create stored procedure for data access layer; 
        program stored procedures, triggers, functions with T-SQL

Modify data by using INSERT, UPDATE, and DELETE statements
        Given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; 
        know which SQL statements are best to solve common requirements; 
        use output statement

Combine datasets
        Difference between UNION and UNION all; 
        case versus isnull versus coalesce; 
        modify data by using MERGE statements

Work with functions
        Understand deterministic, non-deterministic functions; 
        scalar and table values; 
        apply built-in scalar functions; 
        create and alter user-defined functions (UDFs)


Troubleshoot and Optimize


Optimize queries
        Understand statistics; 
        read query plans; 
        plan guides; 
        DMVs; 
        hints; 
        statistics IO; 
        dynamic vs. parameterized queries; 
        describe the different join types (HASH, MERGE, LOOP) and describe the scenarios they would be used in 

Manage transactions
        Mark a transaction; 
        understand begin tran, commit, and rollback; 
        implicit vs explicit transactions; 
        isolation levels; 
        scope and type of locks; 
        trancount

Evaluate the use of row-based operations vs. set-based operations
        When to use cursors; 
        impact of scalar UDFs; 
        combine multiple DML operations

Implement error handling
        Implement try/catch/throw; 
        use set based rather than row based logic; 
        transaction management


select



select

output statement

union and union all 




dml


insert 

delete 

update 



DDL


Merge 


isolation level


isolation level:
Read Uncommitted: No Locks
Read Committed:   Select only locks during execution
Repeatable Read:  Select locks data that has been returned
Serializable:     Select locks the range, May incur a table lock
Snapshot Isolation: Uses versions: use tempdb for versioning, 
                    Must be enabled at the database level, 
READ COMMITTED SNAPSHOT --> Converts read committed to snapshot

-- Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.

isolation levels 



Transaction



SET IMPLICIT_TRANSACTIONS { ON | OFF }
implicit vs explicit 

Transaction Statements 

BEGIN TRANSACTION 

COMMIT TRANSACTION 

ROLLBACK TRANSACTION 

trancount  

transaction management 



SELECT ... ... FOR XML



For XML: AUTO RAW EXPLICIT PATH 

for xml: elements 

for xml: root 

XSINIL with ELEMENTS Directive 
USE AdventureWorks2012;
GO
SELECT ProductID, Name, Color
FROM Production.Product
FOR XML RAW, ELEMENTS XSINIL ;

for xml: xsinil 

for xml: BINARY BASE64 

for xml: xmldata XMLSCHEMA 


Use RAW Mode with FOR XML 
x 
x 


understand XML datatypes and their schemas and interoperability with limitations and restrictions;
  

implement XML schemas and handling of XML data;
  

how to handle XML data in SQL Server and when and when not to use it, including XML namespaces;
  

import and export XML 
  

XML indexing 

USE AdventureWorks2012;
GO
SELECT ProductModelID, Name 
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119 or ProductModelID=115
FOR XML RAW, ROOT('MyRoot')
go

x 

Use AUTO Mode with FOR XML 

SELECT Cust.CustomerID, 
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID, 
       OrderHeader.Status,
       Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO

Use EXPLICIT Mode with FOR XML 

Use PATH Mode with FOR XML 



TABLE



CREATE TABLE Inventory
(ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL);

CREATE TABLE [dbo].[Customers] (
 [CustomerID]   [bigInt] NOT NULL,
 [MObileNumber] [nvarchar](25) NOT NULL,
 [HomeNumber]   [nvarchar](25) NULL,
 [Name]         [nvarchar](50) NOT NULL,
 [Country]      [nvarchar](25) NOT NULL,
  CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED (
     [CustomerId] ASC
  ) ON [PRIMARY] 
) ON [PRIMARY]

CREATE TABLE DocumentStore 
( 
   [Id] INT NOT NULL PRIMARY KEY, 
   [Document] VARBINARY(MAX) NULL ) 
GO

ALTER TABLE Inventory
ADD TotalItems AS ItemsInStore + ItemsInWarehouse;

ALTER TABLE Inventory 
ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED;

CREATE TABLE 
DROP TABLE 
ALTER TABLE  _  

Calculated Column 

CREATE TABLE 
CREATE TABLE dbo.doc_exa (column_a INT) ;  
CREATE TABLE t1 ( c1 int, INDEX ix_1 NONCLUSTERED (c1))
CREATE TABLE t2 ( c1 int  INDEX ix_1 NONCLUSTERED (c1))   
CREATE TABLE t3 ( c1 int, c2 int  INDEX ix_1 NONCLUSTERED)   
CREATE TABLE t4 ( c1 int, c2 int, INDEX ix_1 NONCLUSTERED (c1,c2))

ALTER TABLE 
ALTER TABLE table_name ADD column_name datatype
ALTER TABLE table_name DROP COLUMN column_name
ALTER TABLE table_name ALTER COLUMN column_name datatype

ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;  
ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL CONSTRAINT exb_unique UNIQUE ;  
ALTER TABLE dbo.doc_exz ADD CONSTRAINT col_b_def DEFAULT 50 FOR column_b ;  

ALTER TABLE <table_name>  
  SET ( REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED ) ) ;  
ALTER TABLE <table> REBUILD PARTITION 
ALTER TABLE <table> REBUILD WITH 

ALTER TABLE dbo.doc_exe ADD   
  
-- Add a PRIMARY KEY identity column.  
column_b INT IDENTITY  
CONSTRAINT column_b_pk PRIMARY KEY,   
  
-- Add a column that references another column in the same table.  
column_c INT NULL    
CONSTRAINT column_c_fk   
REFERENCES doc_exe(column_a),  
  
-- Add a column with a constraint to enforce that   
-- nonnull data is in a valid telephone number format.  
column_d VARCHAR(16) NULL   
CONSTRAINT column_d_chk  
CHECK   
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR  
column_d LIKE  
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),  
  
-- Add a nonnull column with a default.  
column_e DECIMAL(3,3)  
CONSTRAINT column_e_default  
DEFAULT .081 ;  
GO  

ALTER COLUMN 
ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;  
GO

DROP TABLE
DROP TABLE IF EXISTS T1;  
GO 
DROP TABLE ProductVendor1 ;  
DROP TABLE [IF EXISTS] [database_name.[schema_name].|schema_name.]table_name

IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL   
DROP TABLE #temptable;  
GO  


VIEW


CREATE VIEW Sales.ORdersByTerritory
AS
SELECT OrderID,OrderDate,SalesTerritoryID,TotalDue FROM Sales.Orders;

create view vOrders
with schemabinding
as
select o.ProductID, o.OrderDate, SUM(od.UnitPrice*od.OrderQty) AS Amount
from OrderDetails AS od INNER JOIN 
 Orders As o On od.OrderID = o.OrderID
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY o.OrderDate, o.ProductionID
GO

CREATE VIEW
Sales.vwCustomerRevenue
WITH SCHEMABINDING
AS
SELECT
  o.CustomerID
 , C.CustomerName
 , SUM(o.SubTotal) as CustomerTotal
 , COUNT_BIG(*) as RecCount
FROM Sales.SalesOrderHeader As o
JOIN Sales.Customer as C on C.CustomerID = o.CustomerID
-- GROUP BY O.CustomerID, C.CustomerName
GO

-- Create a clustered index on the view
Create UNIQUE CLUSTERED INDEX idx_vwCustomerRevenue
ON Sales.vwCustomerRevenue ( CustomerID );
Go
CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON Sales.vOrders (OrderDate, ProductID);
GO
Crete Unique Index idx_vwCustomerRevenue
ON Sales.vwCustomerRevenue ( CustomerID );
GO

CRETE VIEW 
ALTER VIEW 
DROP VIEW 
CREATE INDEXED VIEW 

Create Index 


Data Type


Reference: Date and Time Types 
Reference: datetimeoffset 

Data Type:
Use the DATETIMEOFFSET data type.

Use a user-defined table type.

Data Type:
Numeric:
Money And Smallmoney 


Inline Table-Valued Function


Inline Table-Valued Function 
CRETE FUNCTION Slaes.fn_OrdersByTerritory ( @T int )
RETURNS TABLE
AS
RETURN
(
SELECT OrderID,ORderData,Sales,TerritoryID,TOtalDue
FROM Sales.OrdersByTerritory
WHERE Sales.TerritoryID = @T
);

SELECT D.deptid, D.deptname, D.deptmgrid
    ,ST.empid, ST.empname, ST.mgrid
FROM Departments AS D
    CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST;


Analytical Function



Analytical Function 
Over 
Aggregate Functions 
Ranking Functions 
Builtin Functions 

SELECT StudentCode as Code, 
       NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value 
FROM StudentMarks 
GROUP BY StudentCode;

SELECT StudentCode as Code, 
       RANK ( ) OVER (ORDER BY AVG (Marks) DESC) AS Value 
FROM StudentMarks 
GROUP BY StudentCode;

SELECT StudentCode as Code,
DENSE_RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value 
FROM StudentMarks 
GROUP BY StudentCode;

SELECT 
StudentCode AS Code,
Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, 
                      RANK() OVER (PARTITION BY SubjectCode 
                                   ORDER 3Y Marks DESC) AS Rank 
                      FROM StudentMarks) tmp 
WHERE Rank = 1;

-- Profit for each year, and it's previous year
SELECT Territory, Year, Profit, 
LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit 
FROM Profits;



-- TRIGGER




Encryption


CREATE PROCEDURE Purchases.PurgeInactiveSuppliers 
AS 
DELETE FROM Purchases.Suppliers
WHERE IsActive = 0;
GO

GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser;
GO

Stored PRocedure with ENCRYPTION

ALTER FUNCTION dbo.getHash ( @inputString VARCHAR(20) )
RETURNS VARBINARY(8000) 
WITH ENCRYPTION 
... ...
GO

ALTER PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS
BEGIN
 PRINT 'This text is going to be decrypted'
END 
GO

create and alter user-defined functions (UDFs) 
  

deterministic and non-deterministic functions 

write a stored procedure to meet a given set of requirements
Stored Procedures (Database Engine) 

branching logic 

create stored procedures and other programmatic objects 


implement try/catch/throw 
  


Index Reorganize


index re-org
ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE


ColumnStore Index


-- Columnstore Index
Add a columnstore index to cover the query.
Create Columnstore Index


CTE: Common Table Expressions


CTE: cte vs. UDF -- User Defined Function

cte 

with 
with 

-- convert from:
SELECT c.CustomerName FROM Sales.Customer c
WHERE Sales.ufnGetLastOrderDate(c.CustomerID) < DATEADD (DAY, -90, GETDATE());

CREATE FUNCTIOn Sales.ufnGetLastOrderDate ( @CustomerID int )
RETURNS datatime
AS
BEGIN
  DECLARE @lastOrderDate datetime
  SELECT @lastOrderDate = MAX(OrderDate)
  FROM Sales.SalesOrder
  WHERE CustomerID = @CustomerID
  RETURN @lastOrderDate
END
-- To:
WITH cte(CustomerID, LastOrderDate) AS (
SELECT CustomerID,MAX(OrderDate) AS [LastOrderDate]
FROM Sales.SalesOrder
GROUP BY CUstomerID
)
SELECT c.CustomerName
FROM cte
INNER JOIN Sales.Customer c
ON cte.CustomerID = c.CusomerID
WHERE cte.LastOrderDate < DATEADD(DAY, -90, GETDATE());


Statistics SHOW PLAN


Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query

understand statistics 

DMVs 

query hints 
USE AdventureWorks2012;
GO
SELECT * 
FROM Sales.Customer AS c
INNER JOIN Sales.vStoreWithAddresses AS sa 
    ON c.CustomerID = sa.BusinessEntityID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);
GO

USE AdventureWorks2012;
GO
DECLARE @city_name nvarchar(30);
DECLARE @postal_code nvarchar(15);
SET @city_name = 'Ascheim';
SET @postal_code = 86171;
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );
GO

USE AdventureWorks2012;
GO
--Creates an infinite loop
WITH cte (CustomerID, PersonID, StoreID) AS
(
    SELECT CustomerID, PersonID, StoreID
    FROM Sales.Customer
    WHERE PersonID IS NOT NULL
  UNION ALL
    SELECT cte.CustomerID, cte.PersonID, cte.StoreID
    FROM cte 
    JOIN  Sales.Customer AS e 
        ON cte.PersonID = e.CustomerID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT CustomerID, PersonID, StoreID
FROM cte
OPTION (MAXRECURSION 2);
GO

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e1
UNION
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO

USE AdventureWorks2012;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10);
GO

USE AdventureWorks2012 ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (MAXDOP 2);
GO

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE e.OrganizationLevel = 2;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e, INDEX (IX_Employee_OrganizationLevel_OrganizationNode)))';
GO
EXEC sp_create_plan_guide 
    @name = N'Guide2', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE e.OrganizationLevel = 2;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e, INDEX(PK_Employee_BusinessEntityID, IX_Employee_OrganizationLevel_OrganizationNode)))';
GO

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide3', 
    @stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.JobTitle
              FROM HumanResources.Employee
              JOIN Person.Person AS c ON HumanResources.Employee.BusinessEntityID = c.BusinessEntityID
              WHERE HumanResources.Employee.OrganizationLevel = 3
              ORDER BY c.LastName, c.FirstName;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT( HumanResources.Employee, FORCESEEK))';
GO

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide4', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_OrganizationLevel_OrganizationNode ) ) 
                       , TABLE HINT ( c, FORCESEEK) )';
GO

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide5', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_OrganizationLevel_OrganizationNode))
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e))';
GO

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide6', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 3;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_OrganizationLevel_OrganizationNode) , NOLOCK, FORCESEEK ))';
GO

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide7', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 2;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, NOLOCK))';
GO


join hints 
-- Using HASH
USE AdventureWorks2012;
GO
SELECT p.Name, pr.ProductReviewID
FROM Production.Product AS p
LEFT OUTER HASH JOIN Production.ProductReview AS pr
ON p.ProductID = pr.ProductID
ORDER BY ProductReviewID DESC;

-- Using LOOP
USE AdventureWorks2012;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
FROM Sales.SalesPersonQuotaHistory AS spqh
    INNER LOOP JOIN Sales.SalesPerson AS sp
    ON spqh.SalesPersonID = sp.SalesPersonID
WHERE sp.SalesYTD > 2500000.00;
GO

-- Using MERGE
USE AdventureWorks2012;
GO
SELECT poh.PurchaseOrderID, poh.OrderDate, pod.ProductID, pod.DueDate, poh.VendorID 
FROM Purchasing.PurchaseOrderHeader AS poh
INNER MERGE JOIN Purchasing.PurchaseOrderDetail AS pod 
    ON poh.PurchaseOrderID = pod.PurchaseOrderID;
GO

table hints 
FROM t (TABLOCK)
FROM t WITH (TABLOCK, INDEX(myindex))
FROM dbo.MyTable WITH (FORCESEEK)
FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex))
FROM dbo.MyTable WITH (FORCESEEK (MyIndex (col1, col2, col3)))

SELECT StartDate, ComponentID FROM Production.BillOfMaterials
    WITH( INDEX (FIBillOfMaterialsWithComponentID) )
    WHERE ComponentID in (533, 324, 753, 855, 924);

USE AdventureWorks2012;
GO
UPDATE Production.Product
WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';
GO

USE AdventureWorks2012;
GO
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO

USE AdventureWorks2012; 
GO
SELECT h.SalesOrderID, h.TotalDue, d.OrderQty
FROM Sales.SalesOrderHeader AS h
    INNER JOIN Sales.SalesOrderDetail AS d 
    WITH (FORCESEEK (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID (SalesOrderID))) 
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00); 
GO

USE AdventureWorks2012; 
GO
SELECT h.SalesOrderID, h.TotalDue, d.OrderQty
FROM Sales.SalesOrderHeader AS h
    INNER JOIN Sales.SalesOrderDetail AS d 
    WITH (FORCESCAN) 
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);



statistics IO 



create constraints on tables


create constraints on tables


Inserted And Deleted Tables


Inserted And Deleted Tables


Triggers



CREATE TRIGGER 
create trigger 
alter trigger 

CREATE TRIGGER TrgPhoneNumberChange ON Customers 
FOR UPDATE AS 
IF UPDATE (HomeNumber) OR UPDATE (MobileNumber) - - Create Audit Records

Nested Triggers:
Nested Triggers 

Types Of Triggers:
DML Triggers 

DDL Triggers 

Use the inserted and deleted Tables 

OBJECT_ID, OBJECT_DEFINITION sp_helptext 


multiple rows in a session


handle multiple rows in a session:
Create DML Triggers 


select top(x)


select top(x)
select top(x) 


synonym and joins


write and perform queries efficiently using the new code items such as synonyms and joins (except, intersect);
except and intersect 


pivot and unpivot


pivot and unpivot
pivot and unpivot 


Apply Operator


Apply Operator
Apply Operator 


Sequence


Sequence 
create sequence 

CREATE SEQUENCE Schema.SequenceName AS int INCREMENT BY 1 ;

CREATE SEQUENCE Schema.SequenceName AS int START WITH 1 INCREMENT BY 1 ;

CREATE SEQUENCE Test.RangeSeq AS int START WITH 1
    INCREMENT BY 1 MINVALUE 1 MAXVALUE 25 CYCLE CACHE 10;

SELECT * FROM sys.sequences WHERE name = 'TestSequence' ;

SELECT NEXT VALUE FOR Test.DecSeq;
INSERT Test.Orders (OrderID, Name, Qty) VALUES (NEXT VALUE FOR Test.CountBy1, 'Tire', 2) ;
SET @NextID = NEXT VALUE FOR Test.CountBy1;
NEXT VALUE FOR 

sp_sequence_get_range 


In-Memory OLTP (In-Memory Optimization)


In-Memory OLTP (In-Memory Optimization) 

with:
x 


New Guid


new guid:
newid() 
NEWSEQUENTIALID 


Spatial Aggregates


Spatial Aggregates 


case vs. isnull vs. coalesce


case vs. isnull vs. coalesce