SQL Server Index: Creating a Nonclustered Index in AdventureWorks of SSMS2012.  Indexing Stairway-Level 1 code. Invalid object name 'Person.Contact' !!??

Hi all,

I just started learning the SQL Server Indexes in SQL Server 2012 Management Studio (SSMS2012). I read some tutorials from the websites of Microsoft MSDN and sqlservercentral. I did 3 sets of basic sql codes of creating  NonClusterIndex, ClusterIndex and UniqueIndex (Microsoft msdn examples) successfully. From the sqlservercentral website, I read an article "Stairway to SQL Server Indexes: Level 1, Introduction to Indexes", written by David Durant (dated: 2011/01/26) and tried to execute the following code (copied from the article):

-- Indexing Stairway; Level 1 code
USE AdventureWorks 
GO
IF db_name() <> 'AdventureWorks' RAISERROR('Database AdventureWorks not found', 22, -1) with log;
GO


--***    
--***    Batch 1.
--***    

SET statistics io on
SET statistics time on
GO


--***    
--***    Ensure that the FullName index does
--***        not exist when testing begins.
--***    

IF EXISTS (SELECT * FROM sys.indexes
            WHERE object_id = object_id('Person.Contact')
            AND name = 'FullName')
DROP INDEX Person.Contact.FullName;


--***    Batch 2.
--***    
--***    Run the query (without index).
--***    

SELECT *
  FROM Person.Contact
  WHERE FirstName = 'Helen'
    AND LastName = 'Meyer';
GO


--***    Batch 3.
--***    
--***    Create the index.
--***    

CREATE INDEX FullName 
    ON Person.Contact
     ( LastName,
       FirstName );
GO       


--***    Batch 4.
--***    
--***    Run the query (with index).
--***    

SELECT *
  FROM Person.Contact
  WHERE FirstName = 'Helen'
    AND LastName = 'Meyer';
GO


--***    
--***    End of script.
--***    

I got the following Messages:

SQL Server parse and compile time: 
   CPU time = 3 ms, elapsed time = 3 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Msg 208, Level 16, State 1, Line 19
Invalid object name 'Person.Contact'.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Msg 1088, Level 16, State 12, Line 8
Cannot find the object "Person.Contact" because it does not exist or you do not have permissions.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Msg 208, Level 16, State 1, Line 8
Invalid object name 'Person.Contact'.

I have no ideas (i) what they mean, and (ii) why I got: Invalid object name 'Person.Contact' three times in the Messages. Please kindly help, advise and respond.

Thanks in advance,

Scott Chang     

May 21st, 2015 8:57am

I have no ideas (i) what they mean, and (ii) why I got: Invalid object name 'Person.Contact' three times in the Messages. Please kindly help, advise and respond.

Since you have set statistics I/O on and statistics time ON so you are getting stats about how SQL Server brought pages, how many reads and writes it did and other things.

You are using SQL Server 2012 and I guess post was for older version of SQL Server that is why person.contact is not there. can you go to database and see if table named person.contact

Free Windows Admin Tool Kit Click here and download it now
May 21st, 2015 9:01am

I have no ideas (i) what they mean, and (ii) why I got: Invalid object name 'Person.Contact' three times in the Messages.

Hello Scott,

(i) means the table "Person.Contact" don't exists in the database; AFAIK the table was renamed to "Person.Contact" in a former version

(ii) because in the script there are 3 Batches where this missing table is addressed

May 21st, 2015 9:05am

Hi Olef Helper, Thanks for your response.

I am still not able to figure out your answer for (i).  Do you know the new table name in AdventureWorks2012 for the table/object "Person.Contact" that was in the old AdventureWorks2005?  Please kindly help, advise and respond again.

Thanks again,

Scott Chang

P. S.  Is it possible to create a new table in AdventureWorks2012 for Person.Contact? This is an alternate way for me to start learning this Level 1 of Stairway to SQL Server Indexes. 

Free Windows Admin Tool Kit Click here and download it now
May 21st, 2015 4:20pm

You are far better off finding the correct version of adventureworks for this series of articles.  This is likely one of many such errors you will encounter working through the series due to changes in the schema.  Trying to both learn the information and correct the scripts for these changes will be difficult if you are not familiar with the schema differences - and most people are not.  I suggest you try the 2008r2 version or the 2008 version.  I'm not familiar with the LT versions but I suggest you ignore those.

adventureworks - versions

May 21st, 2015 4:27pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics