webcast sql server 2014 inside out
DESCRIPTION
Material da apresentação online SQL Server 2014 Inside Out para Microsoft Partner Network emTRANSCRIPT
SQL Server 2014Inside Out
Regis GimenisMCS Architect
Michael Stonebraker (http://slideshot.epfl.ch/play/suri_stonebraker)
Latching24%
Locking24%
Recovery24%
Buffer Pool24%
Operação (I,U,D)
4%
CREATE TABLE Customer (
CustomerID INT NOT NULL
PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
Name NVARCHAR(250) NOT NULL
INDEX [Name] HASH WITH (BUCKET_COUNT = 1000000),
CustomerSince DATETIME NULL)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
Demo
10
CREATE CLUSTERED COLUMNSTORE INDEX cci
ON FactSales;
OrderDateKey ProductKey StoreKey RegionKey Quantity SalesAmount
20140301 1001 1 1 4 4.004,00
20140301 1020 4 3 3 3.060,00
20140301 1010 4 1 2 2.020,00
20140301 1020 3 2 3 3.060,00
20140301 1005 5 3 1 1.005,00
20140302 1005 2 1 4 4.020,00
20140302 1001 2 2 3 3.003,00
20140302 1020 3 3 4 4.080,00
20140302 1010 1 1 2 2.020,00
20140303 1020 4 3 3 3.060,00
OrderDateKey ProductKey StoreKey RegionKey Quantity SalesAmount
20140301 1001 1 1 4 4.004,00
20140301 1020 4 3 3 3.060,00
20140301 1010 4 1 2 2.020,00
20140301 1020 3 2 3 3.060,00
20140301 1005 5 3 1 1.005,00
OrderDateKey ProductKey StoreKey RegionKey Quantity SalesAmount
20140302 1005 2 1 4 4.020,00
20140302 1001 2 2 3 3.003,00
20140302 1020 3 3 4 4.080,00
20140302 1010 1 1 2 2.020,00
20140303 1020 4 3 3 3.060,00
OrderDateKey ProductKey StoreKey RegionKey Quantity SalesAmount
20140301 1001 1 1 4 4.004,00
20140301 1020 4 3 3 3.060,00
20140301 1010 4 1 2 2.020,00
20140301 1020 3 2 3 3.060,00
20140301 1005 5 3 1 1.005,00
OrderDateKey ProductKey StoreKey RegionKey Quantity SalesAmount
20140302 1005 2 1 4 4.020,00
20140302 1001 2 2 3 3.003,00
20140302 1020 3 3 4 4.080,00
20140302 1010 1 1 2 2.020,00
20140303 1020 4 3 3 3.060,00
OrderDateKey ProductKey StoreKey RegionKey Quantity SalesAmount
20140301 1001 1 1 4 4.004,00
20140301 1020 4 3 3 3.060,00
20140301 1010 4 1 2 2.020,00
20140301 1020 3 2 3 3.060,00
20140301 1005 5 3 1 1.005,00
OrderDateKey ProductKey StoreKey RegionKey Quantity SalesAmount
20140302 1005 2 1 4 4.020,00
20140302 1001 2 2 3 3.003,00
20140302 1020 3 3 4 4.080,00
20140302 1010 1 1 2 2.020,00
20140303 1020 4 3 3 3.060,00
OrderDateKey ProductKey StoreKey RegionKey Quantity SalesAmount
20140301 1001 1 1 4 4.004,00
20140301 1020 4 3 3 3.060,00
20140301 1010 4 1 2 2.020,00
20140301 1020 3 2 3 3.060,00
20140301 1005 5 3 1 1.005,00
OrderDateKey ProductKey StoreKey RegionKey Quantity SalesAmount
20140302 1005 2 1 4 4.020,00
20140302 1001 2 2 3 3.003,00
20140302 1020 3 3 4 4.080,00
20140302 1010 1 1 2 2.020,00
20140303 1020 4 3 3 3.060,00
OrderDateKey
20140301
20140301
20140301
20140301
20140301
OrderDateKey
20140302
20140302
20140302
20140302
20140303
ProductKey
1001
1020
1010
1020
1005
ProductKey
1005
1001
1020
1010
1020
StoreKey
1
4
4
3
5
StoreKey
2
2
3
1
4
RegionKey
1
3
1
2
3
RegionKey
1
2
3
1
4
Quantity
4
3
2
3
1
Quantity
4
3
4
2
3
SalesAmount
4.004,00
3.060,00
2.020,00
3.060,00
1.005,00
SalesAmount
4.020,00
3.003,00
4.080,00
2.020,00
3.060,00
SELECT ProductKey, Sum (SalesAmount)
FROM FactSales
WHERE OrderDateKey > 20140301
GROUP BY ProductKey
OrderDateKey
20140301
20140301
20140301
20140301
20140301
OrderDateKey
20140302
20140302
20140302
20140302
20140303
ProductKey
1001
1020
1010
1020
1005
ProductKey
1005
1001
1020
1010
1020
StoreKey
1
4
4
3
5
StoreKey
2
2
3
1
4
RegionKey
1
3
1
2
3
RegionKey
1
2
3
1
4
Quantity
4
3
2
3
1
Quantity
4
3
4
2
3
SalesAmount
4.004,00
3.060,00
2.020,00
3.060,00
1.005,00
SalesAmount
4.020,00
3.003,00
4.080,00
2.020,00
3.060,00
OrderDateKey
20140301
20140301
20140301
20140301
20140301
OrderDateKey
20140302
20140302
20140302
20140302
20140303
ProductKey
1001
1020
1010
1020
1005
ProductKey
1005
1001
1020
1010
1020
StoreKey
1
4
4
3
5
StoreKey
2
2
3
1
4
RegionKey
1
3
1
2
3
RegionKey
1
2
3
1
4
Quantity
4
3
2
3
1
Quantity
4
3
4
2
3
SalesAmount
4.004,00
3.060,00
2.020,00
3.060,00
1.005,00
SalesAmount
4.020,00
3.003,00
4.080,00
2.020,00
3.060,00
OrderDateKey ProductKey StoreKey RegionKey Quantity SalesAmount
20140304 1001 3 4 3 3.003,00
20140304 1020 5 2 2 4.020,00
20140304 1010 2 2 1 1.010,00
INSERT INTO FactSales
VALUES (20140304, 1010, 2, 2, 1, 1.010,00)
Demo
19
Secundário
Assíncrono
Síncrono
Windows Azure Virtual Network
Dados e Log
Windows Azure Virtual Network
Demo
25
