XPost: microsoft.public.sqlserver.programming   
   From: sorrytoomuchspamalready@xs4all.nl   
      
   >  Can I do this (get at Accounts.ACOrd) in SQL Server without a join?   
      
   No. Since you need columns from two tables, you need both tables in your   
   query (joined on the foreign key relationship)   
      
   >  Did I define my indexes properly?   
      
   When you declare a Primay Key constraint or Unique constraint, SQL   
   Server will automatically create unique indexes to enforce these   
   constraints. Because of that, you should not declare an identical index   
   yourself such as index ACUK or index SAUK.   
      
   Other than that, your indexes look fine.   
      
   If you are not already familiar with it, and want to learn more about   
   it, then it might be useful for you to read about the differences   
   between clustered and nonclustered indexes.   
      
   Good luck,   
   Gert-Jan   
      
      
   Gene Wirchenko wrote:   
   >   
   > Dear SQLers:   
   >   
   >      For a start, I decided to model how I do banking.  I have several   
   > bank accounts, and I split these up for setasides.  When I list the   
   > subaccounts, the order I want is first by the Accounts order, then the   
   > Subaccounts order, and then the Subaccounts name.   
   >   
   >      The SQL below sets up my database and does what I want.  (Go to   
   > the very end for the SELECT statement in question.)  Can I do this   
   > (get at Accounts.ACOrd) in SQL Server without a join?  Did I define my   
   > indexes properly?   
   >   
   > ***** Start of Included Code *****   
   > /* Banking Learning Database */   
   > /* Version of 2011-04-01 14:42 */   
   >   
   > /* Database Setup */   
   >   
   > use master   
   > go   
   >   
   > drop database Banking   
   > go   
   >   
   > create database Banking   
   > go   
   >   
   > use Banking   
   > go   
   >   
   > /* Bank Accounts Table */   
   >   
   > create table Accounts   
   >  (   
   >  ACUK nvarchar(4) primary key,     /* arbitrary length */   
   >  ACOrd int,   
   >  ACName nvarchar(40),     /* arbitrary length */   
   >  ACNr nvarchar(20)     /* arbitrary length */   
   >  )   
   >   
   > create unique index ACUK on Accounts(ACUK)   
   > create index ACOrd on Accounts(ACOrd,ACName)   
   >   
   > /* Subaccounts Table */   
   > /* Some are set-asides (S/A). */   
   >   
   > create table Subaccounts   
   >  (   
   >  SAUK nvarchar(4) unique,     /* arbitrary length */   
   >  SAOrd int,   
   >  SAName nvarchar(40),     /* arbitrary length */   
   >  SAInACUK nvarchar(4),     /* arbitrary length */   
   >  foreign key (SAInACUK) references Accounts(ACUK)   
   >  )   
   >   
   > create unique index SAUK on Subaccounts(SAUK)   
   > create index SAOrd on Subaccounts(SAOrd,SAInACUK,SAName)   
   >   
   > go   
   >   
   > /* Inserts */   
   >   
   > insert into Accounts   
   >  (ACUK,ACOrd,ACName,ACNr)   
   > values   
   >  ('INC',10,'Income','12345-6 01'),   
   >  ('MAIN',20,'Main','12345-6 02'),   
   >  ('SAVE',30,'Savings','12345-6 11')   
   >   
   > insert into Subaccounts   
   >  (SAUK,SAOrd,SAName,SAInACUK)   
   > values   
   >  ('INC',10,'Income','INC'),   
   >  ('IBE',90,'Income Account Banking Expenses S/A','INC'),   
   >  ('IINT',95,'Income Account Interest','INC'),   
   >  ('PKT',10,'Pocket S/A','MAIN'),   
   >  ('RENT',20,'Rent S/A','MAIN'),   
   >  ('PH',30,'Phone S/A','MAIN'),   
   >  ('MBE',90,'Main Account Banking Expenses S/A','MAIN'),   
   >  ('MINT',95,'Main Account Interest','MAIN'),   
   >  ('RES',10,'Reserves','SAVE'),   
   >  ('ITSA',20,'Income Tax S/A','SAVE'),   
   >  ('SINT',95,'Savings Account Interest','SAVE')   
   >   
   > /* Further Testing Inserts */   
   >   
   > insert into Accounts   
   >  (ACUK,ACOrd,ACName,ACNr)   
   > values   
   >  ('BOG',40,'Bogus','00000-0')   
   >   
   > insert into Subaccounts   
   >  (SAUK,SAOrd,SAName,SAInACUK)   
   > values   
   >  ('BOG2',20,'Bogus 2 S/A','BOG'),   
   >  ('BOG1',10,'Bogus 1 S/A','BOG')   
   >   
   > go   
   >   
   > /* Get results. */   
   >   
   > select * from Accounts order by ACOrd,ACName   
   >   
   > select Subaccounts.*,ACOrd   
   > from Subaccounts   
   > join Accounts on SAInACUK=ACUK   
   > order by ACOrd,SAOrd,SAName   
   > ***** End of Included Code *****   
   >   
   > Sincerely,   
   >   
   > Gene Wirchenko   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)