SELECT DISTINCT TOP (10) HireDate
FROM HumanResources.Employee
ORDER BY HireDate;
SELECT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
WHERE EXISTS ( SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1900 AND 2000
AND SalesOrderID = s.SalesOrderID )
ORDER BY s.PurchaseOrderNumber;
SELECT DISTINCT sh.PurchaseOrderNumber
FROM Sales.SalesOrderHeader AS sh
JOIN Sales.SalesOrderDetail AS sd
ON sh.SalesOrderID = sd.SalesOrderID
WHERE sd.UnitPrice BETWEEN 1900 AND 2000;
SELECT FirstName,
LastName
FROM Person.Person
TABLESAMPLE SYSTEM (2 PERCENT);
SELECT *
FROM (VALUES ('Adam', 'Walkuz'),
('Stefan', 'Jefferson'))
dtPeople(FirstName, LastName);
DECLARE @FirstHireDate DATE,
@LastHireDate DATE;
SELECT @FirstHireDate = MIN(HireDate),
@LastHireDate = MAX(HireDate)
FROM HumanResources.Employee;
SELECT @FirstHireDate AS FirstHireDate,
@LastHireDate AS LastHireDate;
IF OBJECT_ID('dbo.mySales')
IS NOT NULL DROP TABLE dbo.mySales;
SELECT *
INTO dbo.mySales
FROM Sales.SalesOrderDetail
WHERE ModifiedDate = '2011-06-01T00:00:00.000';
SELECT COUNT(*) AS QtyOfRows
FROM dbo.mySales;
IF OBJECT_ID('dbo.fn_WorkOrderRouting') IS NOT NULL DROP FUNCTION dbo.fn_WorkOrderRouting;
GO
CREATE FUNCTION dbo.fn_WorkOrderRouting (@WorkOrderID INT)
RETURNS TABLE
AS
RETURN
SELECT WorkOrderID,
ProductID,
OperationSequence,
LocationID
FROM Production.WorkOrderRouting
WHERE WorkOrderID = @WorkOrderID;
GO
SELECT TOP (5)
w.WorkOrderID,
w.OrderQty,
r.ProductID,
r.OperationSequence
FROM Production.WorkOrder w
CROSS APPLY dbo.fn_WorkOrderRouting(w.WorkOrderID) AS r
ORDER BY w.WorkOrderID,
w.OrderQty,
r.ProductID;
SELECT TOP (5)
w.WorkOrderID,
w.OrderQty,
r.ProductID,
r.OperationSequence
FROM Production.WorkOrder w
CROSS APPLY (
SELECT WorkOrderID,
ProductID,
OperationSequence,
LocationID
FROM Production.WorkOrderRouting
WHERE WorkOrderID = w.WorkOrderId
) AS r
ORDER BY w.WorkOrderID,
w.OrderQty,
r.ProductID;
SELECT s.Name AS ShiftName,
h.BusinessEntityID,
d.Name AS DepartmentName
FROM HumanResources.EmployeeDepartmentHistory h
INNER JOIN HumanResources.Department d
ON h.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.Shift s
ON h.ShiftID = s.ShiftID
WHERE EndDate IS NULL
AND d.Name IN ('Production', 'Engineering', 'Marketing')
ORDER BY ShiftName;
SELECT ShiftName,
Production,
Engineering,
Marketing
FROM
( SELECT s.Name AS ShiftName,
h.BusinessEntityID,
d.Name AS DepartmentName
FROM HumanResources.EmployeeDepartmentHistory h
INNER JOIN HumanResources.Department d
ON h.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.Shift s
ON h.ShiftID = s.ShiftID
WHERE EndDate IS NULL
AND d.Name IN ('Production', 'Engineering', 'Marketing')
)
AS a
PIVOT
(
COUNT(BusinessEntityID)
FOR DepartmentName IN ([Production], [Engineering], [Marketing])
) AS b
FROM table_source
PIVOT ( aggregate_function ( value_column )
FOR pivot_column
IN ( <column_list>)
) table_alias
SELECT s.Name AS ShiftName,
SUM(CASE WHEN d.Name = 'Production' THEN 1 ELSE 0 END) AS Production,
SUM(CASE WHEN d.Name = 'Engineering' THEN 1 ELSE 0 END) AS Engineering,
SUM(CASE WHEN d.Name = 'Marketing' THEN 1 ELSE 0 END) AS Marketing
FROM HumanResources.EmployeeDepartmentHistory h
INNER JOIN HumanResources.Department d
ON h.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.Shift s
ON h.ShiftID = s.ShiftID
WHERE h.EndDate IS NULL
AND d.Name IN ('Production', 'Engineering', 'Marketing')
GROUP BY s.Name;
IF OBJECT_ID('tempdb.dbo.#Contact') IS NOT NULL DROP TABLE #Contact;
CREATE TABLE #Contact
(
EmployeeID INT NOT NULL,
PhoneNumber1 BIGINT,
PhoneNumber2 BIGINT,
PhoneNumber3 BIGINT
)
GO
INSERT #Contact
(EmployeeID, PhoneNumber1, PhoneNumber2, PhoneNumber3)
VALUES (1, 2718353881, 3385531980, 5324571342),
(2, 6007163571, 6875099415, 7756620787),
(3, 9439250939, NULL, NULL);
SELECT EmployeeID,
[PhoneNumber1],
[PhoneNumber2],
[PhoneNumber3]
FROM #Contact c
SELECT EmployeeID,
PhoneType,
PhoneValue
FROM #Contact c
UNPIVOT
(
PhoneValue
FOR PhoneType IN ([PhoneNumber1], [PhoneNumber2],
[PhoneNumber3])
) AS p;
SELECT EmployeeID,
'PhoneNumber1' AS PhoneType,
c.PhoneNumber1 AS PhoneValue
FROM #Contact c
WHERE c.PhoneNumber1 IS NOT NULL
UNION ALL
SELECT EmployeeID,
'PhoneNumber2' AS PhoneType,
c.PhoneNumber2 AS PhoneValue
FROM #Contact c
WHERE c.PhoneNumber2 IS NOT NULL
UNION ALL
SELECT EmployeeID,
'PhoneNumber3' AS PhoneType,
c.PhoneNumber3 AS PhoneValue
FROM #Contact c
WHERE c.PhoneNumber3 IS NOT NULL
ORDER BY EmployeeID, PhoneType;
WITH cte AS
(
SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1900 AND 2000
)
SELECT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
WHERE EXISTS (SELECT SalesOrderID
FROM cte
WHERE SalesOrderID = s.SalesOrderID );
WITH expression_name
[ ( column_name [ ,...n ] ) ]
AS ( CTE_query_SELECT_definition )
WITH CTE(N, D) AS
(
SELECT TOP (5) object_id, type_desc
FROM sys.objects
)
SELECT N,D FROM CTE;
WITH VendorSearch(RowNumber, VendorName, AccountNumber) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY Name) RowNum,
Name,
AccountNumber
FROM Purchasing.Vendor
)
SELECT * FROM VendorSearch;
SELECT * FROM VendorSearch;
IF OBJECT_ID('tempdb.dbo.#Company') IS NOT NULL DROP TABLE #Company;
CREATE TABLE #Company
(
CompanyID INT NOT NULL
PRIMARY KEY,
ParentCompany ID INT NULL,
CompanyName VARCHAR(25) NOT NULL
);
INSERT #Company
(CompanyID, ParentCompanyID, CompanyName)
VALUES (1, NULL, 'A-Corp'),
(2, 1, 'B-Corp'),
(3, 1, 'C-Corp'),
(4, 3, 'D-Corp'),
(5, 4, 'F-Corp'),
(6, 5, 'E-Corp'),
(7, 5, 'G-Corp');
WITH CompanyTree(ParentCompanyID, CompanyID, CompanyName, CompanyLevel) AS
(
-- Anchor Member
SELECT ParentCompanyID,
CompanyID,
CompanyName,
0 AS CompanyLevel
FROM #Company
WHERE ParentCompanyID IS NULL
UNION ALL
-- Recursive Member
SELECT c.ParentCompanyID,
c.CompanyID,
c.CompanyName,
p.CompanyLevel + 1
FROM #Company c
INNER JOIN CompanyTree p
ON c.ParentCompanyID = p.CompanyID
)
SELECT ParentCompanyID,
CompanyID,
CompanyName,
CompanyLevel
FROM CompanyTree;
option (maxrecursion 5)
SELECT ParentCompanyID,
CompanyID,
CompanyName,
CompanyLevel
FROM CompanyTree
option (MaxRecursion 2)
SELECT AVG(Grade) AS AvgGrade,
AVG(DISTINCT Grade) AS AvgDistinctGrade,
MAX(Grade) AS MaxGrade,
MIN(Grade) AS MinGrade
FROM (VALUES (1, 100),
(1, 99),
(1, 99),
(1, 98),
(1, 99),
(1, 32),
(2, 77),
(2, 56),
(2, 88),
(2, 60),
(2, 80)
) dt (StudentId, Grade)
GROUP BY StudentId
SELECT
StudentId,Grade
FROM (VALUES (1, 100),
(1, 99),
(1, 99),
(1, 98),
(1, 99),
(1, 32),
(2, 77),
(2, 56),
(2, 88),
(2, 60),
(2, 80)
) dt (StudentId, Grade)
GROUP BY StudentId
SELECT
StudentId,
AVG(Grade) OVER (ORDER BY StudentId)
FROM (VALUES (1, 100),
(1, 99),
(1, 99),
(1, 98),
(1, 99),
(1, 32),
(2, 77),
(2, 22),
(2, 88),
(2, 60),
(2, 80)
) dt (StudentId, Grade)
SELECT
Distinct StudentId,
AVG(Grade) OVER (ORDER BY StudentId)
FROM (VALUES (1, 100),
(1, 99),
(1, 99),
(1, 98),
(1, 99),
(1, 32),
(2, 77),
(2, 22),
(2, 88),
(2, 60),
(2, 80)
) dt (StudentId, Grade)
SELECT
StudentId,
AVG(Grade) OVER (ORDER BY StudentId)
FROM (VALUES (11, 100),
(1, 99),
(2, 99),
(3, 98),
(4, 99),
(5, 32),
(6, 77),
(7, 22),
(8, 88),
(9, 60),
(10, 80)
) dt (StudentId, Grade)
SELECT duration_seconds,
SUM(duration_seconds) OVER
(ORDER BY id)
AS running_total
FROM [demo].[dbo].[Runinng]
SELECT duration_seconds,
SUM(duration_seconds) OVER
(ORDER BY id DESC)
AS running_total
FROM [demo].[dbo].[Runinng]
SELECT
StudentId,
RANK() OVER (ORDER BY Grade) AS RANK,
DENSE_RANK() OVER (ORDER BY Grade) AS DENSE_RANK,
ROW_NUMBER() OVER (ORDER BY Grade) AS ROW_NUMBER,
AVG(Grade) OVER (ORDER BY Grade) AS AVGGrade
FROM (VALUES (11, 100),
(1, 99),
(2, 99),
(3, 98),
(4, 99),
(5, 32),
(6, 77),
(7, 22),
(8, 88),
(9, 60),
(10, 80)
) dt (StudentId, Grade)
SELECT
StudentId,
LAG(Grade,1) OVER (ORDER BY Grade) AS PreviousGrade,
LEAD(Grade,1) OVER (ORDER BY Grade) AS NextGrade,
FIRST_VALUE(Grade) OVER (ORDER BY Grade) AS FirstGrade,
LAST_VALUE(Grade) OVER (ORDER BY Grade) AS LastGrade
FROM (VALUES (11, 100),
(1, 99),
(2, 99),
(3, 98),
(4, 99),
(5, 32),
(6, 77),
(7, 22),
(8, 88),
(9, 60),
(10, 80)
) dt (StudentId, Grade)
CREATE TABLE #Transactions
(
AccountId INTEGER,
TranDate DATE,
TranAmt NUMERIC(8, 2)
);
INSERT INTO #Transactions
SELECT *
FROM ( VALUES
( 1, '2011-01-01', 500),
( 1, '2011-01-15', 50),
( 1, '2011-01-22', 250),
( 1, '2011-01-24', 75),
( 1, '2011-01-26', 125),
( 1, '2011-01-26', 175),
( 2, '2011-01-01', 500),
( 2, '2011-01-15', 50),
( 2, '2011-01-22', 25),
( 3, '2011-01-22', 5000),
( 3, '2011-01-27', 550),
( 3, '2011-01-27', 95 ),
( 3, '2011-01-30', 2500)
) dt (AccountId, TranDate, TranAmt);
SELECT AccountId,
TranDate,
TranAmt,
-- Suma dla wszystkich transakcji według daty
-- i podzielona/resetowana przez AccountId
RunTotalAmt = SUM(TranAmt)
OVER (PARTITION BY AccountId ORDER BY TranDate)
FROM #Transactions AS t
ORDER BY AccountId,
TranDate;
SELECT AccountId,
TranDate,
TranAmt,
-- Suma dla wszystkich transakcji według daty
RunTotalAmt = SUM(TranAmt)
OVER (ORDER BY TranDate)
FROM #Transactions AS t
ORDER BY
RunTotalAmt;
SELECT AccountId,
TranDate,
TranAmt,
-- Suma dla wszystkich transakcji według daty
RunTotalAmt = SUM(TranAmt) OVER (PARTITION BY AccountId ORDER BY TranDate),
-- Suma dla wszystkich transakcji według daty
-- (jeżeli są dwie takie same daty to nie łączymy)
RunTotalAmt2 = SUM(TranAmt) OVER (PARTITION BY AccountId
ORDER BY TranDate
ROWS UNBOUNDED PRECEDING)
FROM #Transactions AS t
ORDER BY AccountId,
TranDate;
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
SELECT AccountId,
TranDate,
TranAmt,
RunAvg = AVG(TranAmt) OVER (PARTITION BY AccountId ORDER BY TranDate),
RunTranQty = COUNT(*) OVER (PARTITION BY AccountId ORDER BY TranDate),
RunSmallAmt = MIN(TranAmt) OVER (PARTITION BY AccountId ORDER BY TranDate),
RunLargeAmt = MAX(TranAmt) OVER (PARTITION BY AccountId ORDER BY TranDate),
RunTotalAmt = SUM(TranAmt) OVER (PARTITION BY AccountId ORDER BY TranDate)
FROM #Transactions AS t
WHERE AccountID = 1
ORDER BY AccountId, TranDate;
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
SELECT AccountId,
TranDate,
TranAmt,
-- Liczba # obecnych i poprzednich 2 transakcji
SlideQty = COUNT(*)
OVER (PARTITION BY AccountId
ORDER BY TranDate
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
-- Suma obecna i poprzednich 2 transakcji
SlideTotal = SUM(TranAmt)
OVER (PARTITION BY AccountId
ORDER BY TranDate
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM #Transactions AS t
ORDER BY AccountId, TranDate;
SELECT AccountId,
TranDate,
TranAmt,
AccountTotal = SUM(TranAmt)
OVER (PARTITION BY AccountId),
AmountPct = TranAmt / SUM(TranAmt)
OVER (PARTITION BY t.AccountId)
FROM #Transactions AS t
SELECT AccountId,
TranDate,
TranAmt,
-- KolejnyWierszPo AccountId
KolejnyWierszPoId = ROW_NUMBER() OVER (PARTITION BY AccountId ORDER BY AccountId, TranDate),
-- KolejnyWierszPo Wszystkim
KolejnyWierszPoWszystkich = COUNT(*) OVER (PARTITION BY AccountId),
-- KolejnyWierszPo IdWiersza
IdWiersza = ROW_NUMBER() OVER (ORDER BY AccountId, TranDate),
WszystkieWiersze = COUNT(*) OVER ()
FROM #Transactions AS t
ORDER BY AccountId, TranDate;;
SELECT TOP 10
AccountNumber,
OrderDate,
TotalDue,
ROW_NUMBER() OVER
(PARTITION BY AccountNumber ORDER BY OrderDate)
AS RowNumber
FROM AdventureWorks2014.Sales.SalesOrderHeader
ORDER BY AccountNumber;
CREATE TABLE #Test
(
RowID INT IDENTITY,
FName VARCHAR(20),
Salary SMALLINT
);
INSERT INTO #Test (FName, Salary)
VALUES ('Adam', 800),
('Stefan', 950),
('Nosal', 1250),
('Kalina', 1250), --<< powtórzenie
('Patrycja', 1300),
('Bartek', 1500),
('Tomek', 1600),
('Franek', 3000),
('Cezary', 3000), --<< powtórzenie
('Cezaryna', 5000);
SELECT RowID,
FName,
Salary,
SumByRows = SUM(Salary)
OVER (ORDER BY Salary ROWS UNBOUNDED PRECEDING),
SumByRange = SUM(Salary)
OVER (ORDER BY Salary RANGE UNBOUNDED PRECEDING)
FROM #Test
ORDER BY RowID;
SELECT BusinessEntityID,
SalesQuota,
RANK() OVER (ORDER BY SalesQuota DESC) AS RankWithGaps,
DENSE_RANK() OVER (ORDER BY SalesQuota DESC) AS RankWithoutGaps,
ROW_NUMBER() OVER (ORDER BY SalesQuota DESC) AS RowNumber
FROM Sales.SalesPersonQuotaHistory
WHERE QuotaDate = '2014-03-01'
AND SalesQuota < 500000;
SELECT BusinessEntityID,
QuotaDate,
SalesQuota,
NTILE(4) OVER (ORDER BY SalesQuota DESC) AS [NTILE]
FROM Sales.SalesPersonQuotaHistory
WHERE SalesQuota BETWEEN 266000.00 AND 319000.00;
SELECT AccountId,
TranDate,
TranAmt,
NTILE(4) OVER (ORDER BY TranAmt DESC) AS [NTILE]
FROM #Transactions AS t
ORDER BY [NTILE],
TranDate;
CREATE TABLE #Gaps (col1 INTEGER PRIMARY KEY CLUSTERED);
INSERT INTO #Gaps (col1)
VALUES (1), (2), (3),
(50), (51), (52), (53), (54), (55),
(100), (101), (102),
(500),
(950), (951), (952),
(954);
WITH cte AS
(
SELECT col1 AS CurrentRow,
LEAD(col1, 1, NULL) OVER (ORDER BY col1) AS NextRow
FROM #Gaps
)
-- Compare the value of the current row to the next row.
-- If > 1, then there is a gap.
SELECT cte.CurrentRow + 1 AS [Start of Gap],
cte.NextRow - 1 AS [End of Gap]
FROM cte
WHERE cte.NextRow - cte.CurrentRow > 1;
WITH cte_netsales_2018 AS(
SELECT
month,
SUM(net_sales) net_sales
FROM
sales.vw_netsales_brands
WHERE
year = 2018
GROUP BY
month
)
SELECT
month,
net_sales,
LAG(net_sales,1) OVER (
ORDER BY month
) previous_month_sales
FROM
cte_netsales_2018;
SELECT DISTINCT TOP (5)
CustomerID,
-- Get the date for the customer's least expensive order
FIRST_VALUE(OrderDate)
OVER (PARTITION BY CustomerID
ORDER BY TotalDue
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS OrderDateLow,
-- Get the date for the customer's most expensive order
LAST_VALUE(OrderDate)
OVER (PARTITION BY CustomerID
ORDER BY TotalDue
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS OrderDateHigh
FROM Sales.SalesOrderHeader
ORDER BY CustomerID;
SET IMPLICIT_TRANSACTIONS ON;
SET IMPLICIT_TRANSACTIONS OFF;
/* -- Przed Liczeniem */
SELECT BeforeCount = COUNT(*)
FROM HumanResources.Department;
/* -- Zmienna będzie przetrzymywać ostatni błąd */
DECLARE @Error int;
BEGIN TRANSACTION
INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('Accounts Payable', 'Accounting');
SET @Error = @@ERROR;
IF (@Error<> 0)
GOTO Error_Handler;
INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('Engineering', 'Research and Development');
SET @Error = @@ERROR;
IF (@Error <> 0)
GOTO Error_Handler;
COMMIT TRANSACTION
Error_Handler:
IF @Error <> 0
BEGIN
ROLLBACK TRANSACTION;
END
/* -- Po */
SELECT AfterCount = COUNT(*)
FROM HumanResources.Department;
GO
USE AdventureWorks2014;
GO
BEGIN TRANSACTION
DELETE Production.ProductProductPhoto
WHERE ProductID = 317;
DBCC OPENTRAN('AdventureWorks2014');
ROLLBACK TRANSACTION;
GO
--stwórzmy celowo trasakcje która nie jest zamknięta
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
USE AdventureWorks2014;
GO
BEGIN TRAN
SELECT *
FROM HumanResources.Department
INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('Test', 'QA');
--w innym oknie query
SELECT session_id, transaction_id, is_user_transaction, is_local
FROM sys.dm_tran_session_transactions
WHERE is_user_transaction = 1;
--w innym oknie query
SELECT s.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) s
WHERE c.most_recent_session_id = 51;
--użyj session_id zwrócone przez poprzednie zapytanie
GO
--w innym oknie query
SELECT transaction_begin_time
,tran_type = CASE transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction'
END
,tran_state = CASE transaction_state
WHEN 0 THEN 'not been completely initialized yet'
WHEN 1 THEN 'initialized but has not started'
WHEN 2 THEN 'active'
WHEN 3 THEN 'ended (read-only transaction)'
WHEN 4 THEN 'commit initiated for distributed transaction'
WHEN 5 THEN 'transaction prepared and waiting resolution'
WHEN 6 THEN 'committed'
WHEN 7 THEN 'being rolled back'
WHEN 8 THEN 'been rolled back'
END
FROM sys.dm_tran_active_transactions
WHERE transaction_id = 12969598;
-- zmień wartość transaction_id
GO
--stwórzmy celowo trazakcje która nie jest zamknięta
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--GO
--USE AdventureWorks2014;
--GO
--BEGIN TRAN
--SELECT *
--FROM HumanResources.Department
--INSERT INTO HumanResources.Department (Name, GroupName)
--VALUES ('Test', 'QA'); -->
ROLLBACK TRANSACTION;
--stwórzmy celowo trazakcje która nie jest zamknięta
USE AdventureWorks2014;
BEGIN TRAN
SELECT ProductID, ModifiedDate
FROM Production.ProductDocument WITH (TABLOCKX);
--w drugim oknie
SELECT sessionid = request_session_id ,
ResType = resource_type ,
ResDBID = resource_database_id ,
ObjectName = OBJECT_NAME(resource_associated_entity_id,
resource_database_id) ,
RMode = request_mode ,
RStatus = request_status
FROM sys.dm_tran_locks
WHERE resource_type IN ('DATABASE', 'OBJECT');
GO
USE AdventureWorks2014;
GO
ALTER TABLE Person.Address
SET ( LOCK_ESCALATION = AUTO );
SELECT lock_escalation,lock_escalation_desc
FROM sys.tables WHERE name='Address';
GO
USE AdventureWorks2014;
GO
ALTER TABLE Person.Address
SET ( LOCK_ESCALATION = DISABLE);
SELECT lock_escalation,lock_escalation_desc
FROM sys.tables WHERE name='Address';
GO
SET TRANSACTION ISOLATION LEVEL
{
READ UNCOMMITTED
| READ COMMITTED
REPEATABLE READ
SNAPSHOT | SERIALIZABLE
}
session1> NoweOkno;
session1> SELECT firstname FROM names WHERE id = 7;
Aaron
session2> BEGIN Transation
session2> UPDATE names SET firstname = 'Bob' WHERE id = 7;
session2> SELECT firstname FROM names WHERE id = 7;
Bob
session1> SELECT firstname FROM names WHERE id = 7;
................
session2> COMMIT;
session1> Bob
session1> Nowe okno
session1> SELECT firstname FROM names WHERE id = 7;
Aaron
session2> BEGIN Transation;
session2> SELECT firstname FROM names WHERE id = 7;
Aaron
session2> UPDATE names SET firstname = 'Bob' WHERE id = 7;
session2> SELECT firstname FROM names WHERE id = 7;
Bob
session1> SELECT firstname FROM names WHERE id = 7;
Bob
session2> COMMIT;
session1> SELECT firstname FROM names WHERE id = 7;
Bob
session1> Nowe okno
session1> SELECT firstname FROM names WHERE id = 7;
Aaron
session2> BEGIN Transation;
session2> SELECT firstname FROM names WHERE id = 7;
Aaron
session2> UPDATE names SET firstname = 'Bob' WHERE id = 7;
session2> SELECT firstname FROM names WHERE id = 7;
Bob
session2> COMMIT;
session1> SELECT firstname FROM names WHERE id = 7;
Aaron
session1> BEGIN Transation;
session1> SELECT firstname FROM names WHERE id = 7;
Aaron
session2> BEGIN Transation;
session2> SELECT firstname FROM names WHERE id = 7;
Aaron
session2> UPDATE names SET firstname = 'Bob' WHERE id = 7;
session2> ...
session2> ...
session1> END;
session2> UPDATE Done
--pierwsze okno
USE AdventureWorks2014;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION
SELECT AddressTypeID, Name
FROM Person.AddressType
WHERE AddressTypeID BETWEEN 1 AND 6;
GO
--drugie okno
SELECT resource_associated_entity_id, resource_type,
request_mode, request_session_id
FROM sys.dm_tran_locks;
GO
--TRZECIE okno
Update Person.AddressType
SET Name = 'T'
WHERE AddressTypeID = 1
GO
--USE AdventureWorks2014;
--GO
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--GO
-- BEGIN TRANSACTION
-- SELECT AddressTypeID, Name
-- FROM Person.AddressType
-- WHERE AddressTypeID BETWEEN 1 AND 6;
--GO
COMMIT TRANSACTION;
USE AdventureWorks2014;
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
BEGIN TRANSACTION
SELECT AddressTypeID, Name
FROM Person.AddressType
WHERE AddressTypeID BETWEEN 1 AND 6;
GO
SELECT resource_associated_entity_id, resource_type,
request_mode, request_session_id
FROM sys.dm_tran_locks;
GO
--USE AdventureWorks2014;
--GO
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--GO
-- BEGIN TRANSACTION
-- SELECT AddressTypeID, Name
-- FROM Person.AddressType
-- WHERE AddressTypeID BETWEEN 1 AND 6;
--GO
COMMIT TRANSACTION;
ALTER DATABASE AdventureWorks2014
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
USE AdventureWorks2014;
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
BEGIN TRANSACTION
SELECT CurrencyRateID,EndOfDayRate
FROM Sales.CurrencyRate
WHERE CurrencyRateID = 8317;
GO
-- w innym oknie
USE AdventureWorks2014;
GO
UPDATE Sales.CurrencyRate
SET EndOfDayRate = 1.00
WHERE CurrencyRateID = 8317;
GO
-- w oknie transakcji
SELECT CurrencyRateID,EndOfDayRate
FROM Sales.CurrencyRate
WHERE CurrencyRateID = 8317;
GO
-- w oknie transakcji
COMMIT TRANSACTION;
SELECT CurrencyRateID,EndOfDayRate
FROM Sales.CurrencyRate
WHERE CurrencyRateID = 8317;
GO
KILL {SPID | UOW} [WITH STATUSONLY]
--na jednym oknie
USE AdventureWorks2014;
GO
BEGIN TRAN
UPDATE Production.ProductInventory
SET Quantity = 400
WHERE ProductID = 1 AND LocationID = 1;
--w drugim oknie
USE AdventureWorks2014;
GO
BEGIN TRAN
UPDATE Production.ProductInventory
SET Quantity = 406
WHERE ProductID = 1 AND LocationID = 1;
--w trzecim oknie
SELECT blocking_session_id, wait_duration_ms, session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL;
GO
--w czwartym oknie
SELECT t.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t
WHERE c.session_id = 55;
--użyj blocking_session_id z poprzedniego zapytania
GO
KILL 55;
SET LOCK_TIMEOUT timeout_period
SET LOCK_TIMEOUT timeout_period
--w pierwszym oknie
USE AdventureWorks2014;
GO
BEGIN TRAN
UPDATE Production.ProductInventory
SET Quantity = 400
WHERE ProductID = 1 AND LocationID = 1;
--w drugim oknie
USE AdventureWorks2014;
GO
SET LOCK_TIMEOUT 5000;
UPDATE Production.ProductInventory
SET Quantity = 406
WHERE ProductID = 1 AND LocationID = 1;
--pierwsze okno
USE AdventureWorks2014;
GO
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
WHILE 1=1
BEGIN
BEGIN TRAN
UPDATE Purchasing.Vendor
SET CreditRating = 1
WHERE BusinessEntityID = 1494;
UPDATE Purchasing.Vendor
SET CreditRating = 2
WHERE BusinessEntityID = 1492;
COMMIT TRAN
END
--drugie okno
USE AdventureWorks2014;
GO
SET NOCOUNT ON;
WHILE 1=1
BEGIN
BEGIN TRANSACTION
UPDATE Purchasing.Vendor
SET CreditRating = 2
WHERE BusinessEntityID = 1492;
UPDATE Purchasing.Vendor
SET CreditRating = 1
WHERE BusinessEntityID = 1494;
COMMIT TRANSACTION
END
--trzecie okno
CREATE EVENT SESSION [Deadlock] ON SERVER
ADD EVENT sqlserver.lock_deadlock(
ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'D:\Deadlock.xel')
--Upewnij się, że masz dostęp i uprawienia do ścieżki
WITH (STARTUP_STATE=ON)
GO
ALTER EVENT SESSION Deadlock
ON SERVER
STATE = START;
--czwarte okno
SELECT TargetData AS DeadlockGraph
FROM
(SELECT CAST(event_data AS xml) AS TargetData
FROM sys.fn_xe_file_target_read_file
('D:\Deadlock*.xel',NULL,NULL, NULL)
)
AS Data
WHERE
TargetData.value('(event/@name)[1]', 'varchar(50)') = 'xml_deadlock_report';
<event name="xml_deadlock_report" package="sqlserver" timestamp="2020-11-17T12:16:37.572Z">
<data name="xml_report">
<value>
<deadlock>
<victim-list>
<victimProcess id="process177af683088" />
</victim-list>
<process-list>
<process id="process177af683088" taskpriority="0"
logused="144" waitresource="KEY: 5:72057594049789952 (b4903b2250cc)"
waittime="3521" ownerId="468635" transactionname="user_transaction"
lasttranstarted="2020-11-17T13:16:34.050" XDES="0x1779f36c428"
lockMode="X" schedulerid="7" kpid="20452"
status="suspended" spid="56" sbid="0" ecid="0"
priority="0" trancount="2" lastbatchstarted="2020-11-17T13:16:34.050"
lastbatchcompleted="2020-11-17T13:16:34.047" lastattention="1900-01-01T00:00:00.047"
clientapp="Microsoft SQL Server Management Studio - Query" hostname="CEZMSI"
hostpid="1476" loginname="CEZMSI\PanNiebieski"
isolationlevel="read committed (2)" xactid="468635" currentdb="5"
currentdbname="AdventureWorks2014" lockTimeout="4294967295"
clientoption1="673187936" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="8" stmtstart="40" stmtend="200"
sqlhandle="0x02000000eac1af36f412db4e21d9dcc86feb261fa6bcd2300000000000000000000000000000000000000000">
unknown </frame>
<frame procname="adhoc" line="8" stmtstart="300"
stmtend="468" sqlhandle="0x020000005312e433857f0fc42c7763c9f49c0ba8feeff51d0000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
<resource-list>
<keylock hobtid="72057594049789952" dbid="5" objectname="AdventureWorks2014.Purchasing.Vendor" indexname="PK_Vendor_BusinessEntityID" id="lock177a9200900" mode="X" associatedObjectId="72057594049789952">
<owner-list>
<owner id="process1779d5c88c8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process177af683088" mode="X" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594049789952" dbid="5" objectname="AdventureWorks2014.Purchasing.Vendor" indexname="PK_Vendor_BusinessEntityID" id="lock177a9200b80" mode="X" associatedObjectId="72057594049789952">
<owner-list>
<owner id="process177af683088" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process1779d5c88c8" mode="X" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | }
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | [numeric-priority] }
USE AdventureWorks2014;
GO
SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;
WHILE 1=1
BEGIN
BEGIN TRANSACTION
UPDATE Purchasing.Vendor
SET CreditRating = 1
WHERE BusinessEntityID = 1492;
UPDATE Purchasing.Vendor
SET CreditRating = 2
WHERE BusinessEntityID = 1494;
COMMIT TRANSACTION
END
GO
SELECT TOP (15)
FullName =
CONCAT(LastName, ', ', FirstName, ' ', MiddleName)
FROM Person.Person p;
SELECT ASCII('c'),
ASCII('e'),
ASCII('z'),
ASCII('a'),
ASCII('r');
SELECT CHAR(99),
CHAR(101),
CHAR(122),
CHAR(97),
CHAR(114) ;
SELECT UNICODE('G'),
UNICODE('o'),
UNICODE('o'),
UNICODE('d'),
UNICODE('!');
SELECT NCHAR(71),
NCHAR(111),
NCHAR(111),
NCHAR(100),
NCHAR(33) ;
SELECT CHARINDEX('string to find',
'Znajdź mi "string to find" w tym napisie');
SELECT TOP 18
AddressID,
AddressLine1,
PATINDEX('%[0]%Mt%', AddressLine1)
FROM Person.Address
WHERE PATINDEX('%[0]%Mt%', AddressLine1) > 0;
SELECT DISTINCT
SOUNDEX(LastName),
SOUNDEX('Smi'),
LastName
FROM Person.Person
WHERE SOUNDEX(LastName) = SOUNDEX('Smi');
SELECT LEFT('Chce tylko 10 znaków po lewej', 10);
SELECT RIGHT('Chce tylko 10 znaków po prawej', 10);
SELECT TOP (18)
ProductNumber,
ProductName = LEFT(Name, 10),
ProductName = RIGHT(Name, 10)
FROM Production.Product;
SELECT TOP (18)
PhoneNumber,
AreaCode = LEFT(PhoneNumber, 3),
Exchange = SUBSTRING(PhoneNumber, 5, 3)
FROM Person.PersonPhone
WHERE PhoneNumber LIKE
'[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]';
SELECT LEN(N'SQL został opracowany w latach 70. w firmie IBM.
Stał się standardem w komunikacji z
serwerami relacyjnych baz danych.');
SELECT DATALENGTH(N'SQL został opracowany w latach 70. w firmie IBM.
Stał się standardem w komunikacji z serwerami relacyjnych baz danych.');
SELECT
DATALENGTH(123) as [123],
DATALENGTH(123.0) as [123.0],
DATALENGTH('123') as ['123'],
DATALENGTH(GETDATE()) as [GetDate];
SELECT REPLACE('Pierwszą firmą, która włączyła SQL
do swojego produktu komercyjnego, był Oracle.'
, 'Oracle', 'Lichy Dzwig');
SELECT STUFF ( 'Mój kot ma na imię X. Poznałeś go?', 18, 1, 'Stefan' );
SELECT LOWER(DocumentSummary)
FROM Production.Document
WHERE FileName = 'Installing Replacement Pedals.doc';
SELECT UPPER(DocumentSummary)
FROM Production.Document
WHERE FileName = 'Installing Replacement Pedals.doc';
SELECT CONCAT('||',
LTRIM(' Napis '),
'||' );
SELECT CONCAT('||',
RTRIM(' Napis '),
'||' );
SELECT REPLICATE ('W', 30) ;
DECLARE @animals TABLE
(
string1 VARCHAR(20),
string2 VARCHAR(20),
string3 VARCHAR(20)
);
INSERT @animals
VALUES ('elephant', 'dog', 'giraffe'),
('kitty', 'puppy', 'ant'),
('chicken', 'fish', 'marmacet');
SELECT CONCAT(string1, SPACE(40 - LEN(string1)),
string2, SPACE(40 - LEN(string2)),
string3, SPACE(40 - LEN(string3))) AS a
FROM @animals
SELECT REVERSE('Jak Programowac');
