Create Table [TestUser]
(
Id uniqueidentifier DEFAULT NEWID() not null PRIMARY KEY,
Name nvarchar(100),
Email nvarchar(100),
[Address] nvarchar(300),
CreatedOn Datetime not null
)
Create Table TestBook
(
Id uniqueidentifier DEFAULT NEWID() not null PRIMARY KEY,
Name nvarchar(100),
Content nvarchar(max),
BorrowFrom datetime,
Author nvarchar(100),
CreatedOn Datetime not null,
BorrowBy uniqueidentifier foreign key References [TestUser](Id)
)
declare @i int=0;
while(@i<1000000)
Begin
Declare @NewGuid uniqueidentifier = NEWID();
Declare @now datetime = GETDATE();
Declare @istr nvarchar(10) = Cast(@i as nvarchar(10));
Insert into [TestUser]
Values(@NewGuid, 'UserName'+@istr,'email@e.ca','address'+@istr,@now)
Insert into TestBook(Name,Content,BorrowFrom,Author,CreatedOn,BorrowBy)
Values ('Name'+@istr, 'This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...This is a super test...'
, @now, 'Author'+@istr, @now, @NewGuid)
set @i=@i+1;
End
Select top 10 * from TestBook
order by Author
SELECT top 10 [dbo].[TestBook].Id ,[dbo].[TestUser].Name , [dbo].[TestBook].Name, [dbo].[TestBook].Content
FROM [dbo].[TestBook]
inner join [dbo].[TestUser]
on [dbo].[TestBook].BorrowBy = [dbo].[TestUser].Id
where [dbo].[TestUser].Name like '%name%'
order by [dbo].[TestUser].Name
select top 10 * from TestUser
order by Name
CREATE nonCLUSTERED INDEX IX_TestBook_BorrowBy
ON TestBook (BorrowBy);
CREATE nonCLUSTERED INDEX IX_TestUser_Name
ON TestUser (Name);
Create Table [User]
(
Id uniqueidentifier DEFAULT NEWID() not null PRIMARY KEY,
Name nvarchar(100),
Email nvarchar(100),
[Address] nvarchar(300),
CreatedOn Datetime not null
)
Create Table Book
(
Id uniqueidentifier DEFAULT NEWID() not null PRIMARY KEY,
Name nvarchar(100),
BorrowFrom datetime,
Author nvarchar(100),
CreatedOn Datetime not null,
BorrowBy uniqueidentifier foreign key References [User](Id)
)
declare @i int=0;
while(@i<1000000)
Begin
Declare @NewGuid uniqueidentifier = NEWID();
Declare @now datetime = GETDATE();
Declare @istr nvarchar(10) = Cast(@i as nvarchar(10));
Insert into [User]
Values(@NewGuid, 'UserName'+@istr,'email@e.ca','address'+@istr,@now)
Insert into Book(Name,BorrowFrom,Author,CreatedOn,BorrowBy)
Values ('Name'+@istr, @now, 'Author'+@istr, @now, @NewGuid)
set @i=@i+1;
End
/****** Script for SelectTopNRows command from SSMS ******/
USE [LargeTest]
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
SELECT [dbo].[User].Id , [dbo].[Book].Name
FROM [dbo].[Book]
inner join [dbo].[User]
on [dbo].[Book].BorrowBy = [dbo].[User].Id
where [dbo].[User].Name in( 'UserName364543','UserName369543','UserName368543','UserName364544')
declare @Id uniqueidentifier;
select Id
from [dbo].[User]
where Name in( 'UserName364543','UserName369543','UserName368543','UserName364544')
select Id, Name
FROM [dbo].[Book]
where BorrowBy in
('7CE414B4-AE4B-49DB-AF29-000006B91C4F',
'16C0C6F4-9B4D-4AF9-9119-6EBDAFCA870E',
'240B0406-CF19-494F-8AFE-DCE43236237B',
'3D60040A-38D7-4CEC-A173-FADDC598DD44'
)
select Id, Name
FROM [dbo].[Book]
where BorrowBy in(
select Id
from [dbo].[User]
where Name in( 'UserName364543','UserName369543','UserName368543','UserName364544')
)
DECLARE @StartTime datetime,@EndTime datetime
SELECT @StartTime=GETDATE()
select Id
from [dbo].[User]
where Name in( 'UserName364543','UserName369543','UserName368543','UserName364544')
SELECT @EndTime=GETDATE()
SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in milliseconds]
Create Index IX_BookBorrowBy
On [dbo].[Book](BorrowBy)
