Exam 70-762 topic 1 question 70 discussion

Answer is incorrect. The second statement leads to table scan when searching AccountNumber

table scan is NOT THE SAME THING AS AN index scan. get it through your head people. it does not matter if the "scan" is occurring on a clustered index. the question is specifically talking about a table scan.

Why do people think this will table scan? Both statements will not table scan it is an index on the where.

I can see the first one doing a full scan but how does the second statement give you a full scan? You have a nonclustered index on product code and you filter on the product code. I will look at this tomorrow.

Here is the SQL for the test: CREATE TABLE dbo.Account ( AccountNumber int not null, ProductCode char(2) not null, Status tinyint not null, OpenDate date not null, CloseDate date, Balance decimal(15,2), AvailableBalance decimal(15,2) ); GO INSERT INTO dbo.Account(AccountNumber,ProductCode,Status,OpenDate,Balance) values (111101,'AB',1,GETDATE(),£200.50), (111102,'CD',1,GETDATE(),£300.50), (111103,'EF',1,GETDATE(),£500.50), (111104,'GH',0,GETDATE(),£100.50), (111104,'IJ',0,GETDATE(),£100.50), (111104,'KL',0,GETDATE(),£100.50), (111104,'MN',0,GETDATE(),£100.50), (111104,'OP',0,GETDATE(),£800.50), (111104,'QR',0,GETDATE(),£600.50), (111104,'ST',0,GETDATE(),£100.50); GO 10000 In total, there will be 100 000 records in the table. Both queries give you Table scan. Add the index: CREATE NONCLUSTERED INDEX IX_Account_ProductCode ON Account(ProductCode); Both queries still give you Table scan. So the answer is NO.

It´s yes for the first query and no for the second. I have checked it in sql server. FIRST QUERY PRODUCTS: SELECT <-- COMPUTER SCALAR <-- STREAM AGGREGATE <-- NESTED LOOPS <-- INDEX SEEK & RID LOOKUP SECOND QUERY: SELECT <-- TABLE SCAN

create table dbo.account ( accountnumber int not null, productcode char(2) not null, status tinyint not null, opendate date not null, closedate date null, balance decimal(15,2), availablebalance decimal(15,2) ); insert into dbo.account(accountnumber,productcode,status,opendate,balance) values(111101,'AB',1,GETDATE(),£200.50), (111102,'Ac',1,GETDATE(),£300.50), (111103,'CB',1,GETDATE(),£500.50), (111104,'FB',0,GETDATE(),£100.50); create nonclustered index idx_account_productcode on dbo.account(productcode); select productcode,sum(balance) as TotalSUM from dbo.account where productcode<>'FB' group by productcode; select accountnumber,balance from dbo.account where productcode='FB'; actual execution plan: tested answer is wrong: 1st query will be INDEX SEEK 2nd query will be INDEX SCAN unless balance col include with above ncl index

Answer is YES. With a NCI on ProductCode, you will have only index seeks (for ProductCode) and RID lookup (for select AccoundNumber and Balance). However the best solution is NIC on ProductCode INCLUDE Balance and a CI on AccountNumber.

Question for the group - why do you think an inequality (<>) in a query dictates that an index scan will always be used? My understanding of when a scan is used is when there is no index that will allow the optimizer to reduce its workload - and this index should I believe. In addition, there is no requirement that seeks can only use clustered indexes - although I'm sure its much more common.

Answer is no. Tested it. Create a table add few records and test it yourself. :)

this is same question as #62, but the given answers are conflicting

part 2: our goal: You must avoid table scans when you run the queries. An index scan or table scan is when SQL Server has to scan the data or index pages to find the appropriate records. A scan is the opposite of a seek, where a seek uses the index to pinpoint the records that are needed to satisfy the query. the given command "CREATE NONCLUSTERED INDEX IX_Account_ProductCode ON Account(ProductCode);" will allow optimizer to perform seek (not scan) on queries " where productcode ..." thus answer is Yes

part 1: what the clues are telling us: the CREATE TABLE has no primary key = no clustered index 1 billion rows = needs some indexes The Account Number column uniquely identifies each account. = every row has unique Account Number , but it's not defined as primary key The ProductCode column has 100 different values/The values are evenly distributed in the table. = each ProductCode covers about 10 million rows Table statistics are refreshed and up to date. = " In some cases you can improve the query plan and therefore improve query performance by updating statistics" from https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver15 both select queries where clause looks for productcode

Wrong answer. If it was a cluster index then would be ok.