Create relational large table for test

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)