Untuk siswa masa depan pada kursus "MS SQL Server Developer" kami menyiapkan terjemahan dari artikel yang bermanfaat.
Kami juga mengundang semua orang ke webinar terbuka dengan topik "Polybase: kehidupan sebelum dan sesudah" . Di webinar, kita akan melihat bagaimana mungkin untuk berinteraksi dengan database lain sebelum Polybase, dan bagaimana cara kerjanya sekarang.
: « SQL Server , ?»
, , , . . , : « — ».
, - Stack Overflow , :
USE StackOverflow;
GO
/* Create date partition function by day since Stack Overflow's origin,
modified from Microsoft Books Online:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver15#BKMK_examples
DROP PARTITION SCHEME [DatePartitionScheme];
DROP PARTITION FUNCTION [DatePartitionFunction];
*/
DECLARE @DatePartitionFunction nvarchar(max) =
N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime)
AS RANGE RIGHT FOR VALUES (';
DECLARE @i datetime = '2008-06-01';
WHILE @i <= GETDATE()
BEGIN
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(20)) + '''' + N', ';
SET @i = DATEADD(DAY, 1, @i);
END
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(20))+ '''' + N');';
EXEC sp_executesql @DatePartitionFunction;
GO
/* Create matching partition scheme, but put everything in Primary: */
CREATE PARTITION SCHEME DatePartitionScheme
AS PARTITION DatePartitionFunction
ALL TO ( [PRIMARY] );
GO
Users, CreationDate:
DROP TABLE IF EXISTS dbo.Users_partitioned;
GO
CREATE TABLE [dbo].[Users_partitioned](
[Id] [int] NOT NULL,
[AboutMe] [nvarchar](max) NULL,
[Age] [int] NULL,
[CreationDate] [datetime] NOT NULL,
[DisplayName] [nvarchar](40) NOT NULL,
[DownVotes] [int] NOT NULL,
[EmailHash] [nvarchar](40) NULL,
[LastAccessDate] [datetime] NOT NULL,
[Location] [nvarchar](100) NULL,
[Reputation] [int] NOT NULL,
[UpVotes] [int] NOT NULL,
[Views] [int] NOT NULL,
[WebsiteUrl] [nvarchar](200) NULL,
[AccountId] [int] NULL
) ON [PRIMARY];
GO
CREATE CLUSTERED INDEX CreationDate_Id ON
dbo.Users_partitioned (Id)
ON DatePartitionScheme(CreationDate);
GO
INSERT INTO dbo.Users_partitioned (Id, AboutMe, Age,
CreationDate, DisplayName, DownVotes, EmailHash,
LastAccessDate, Location, Reputation, UpVotes,
Views, WebsiteUrl, AccountId)
SELECT Id, AboutMe, Age,
CreationDate, DisplayName, DownVotes, EmailHash,
LastAccessDate, Location, Reputation, UpVotes,
Views, WebsiteUrl, AccountId
FROM dbo.Users;
GO
Let’s c
Users Users_partitioned. , Users_partitioned , , , :
CREATE INDEX DisplayName ON dbo.Users(DisplayName);
CREATE INDEX DisplayName ON dbo.Users_partitioned(DisplayName);
, :
SET STATISTICS TIME, IO ON;
SELECT * FROM dbo.Users WHERE DisplayName = N'Brent Ozar';
GO
SELECT * FROM dbo.Users_partitioned WHERE DisplayName = N'Brent Ozar';
GO
, , , , 0% , — 100%:
, 0,001, — 15. , (compile time), (execution time) (logical reads) . , — ( ):
27 . , : « 27 ?» — ! , 250 . , . - .
, , ETL- . , 250 .
, ?
Users_partitioned . , ON PRIMARY , .
CREATE INDEX DisplayName ON dbo.Users(DisplayName); CREATE INDEX DisplayName ON dbo.Users_partitioned(DisplayName) ON [PRIMARY];
:
SET STATISTICS TIME, IO ON;
SELECT * FROM dbo.Users WHERE DisplayName = N'Brent Ozar';
GO
SELECT * FROM dbo.Users_partitioned WHERE DisplayName = N'Brent Ozar';
GO
:
- . , — :
, -
, , . (, rowstore- 100 ), , , . , , .
— , : «, , !» , : « , ».