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

No. Since you need columns from two tables, you need both tables in your
query (joined on the foreign key relationship)

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:

On Sat, 02 Apr 2011 10:29:46 +0200, Gert-Jan Strik
<sorrytoomuchspamalready (AT) xs4all (DOT) nl> wrote:

I figured that was probably so. Thank you for confirming it.

thank you. I missed that.

I will be doing a reread shortly, I think.

Thank you for the help.

Some of my questions probably seem silly. It is not that I do
not know so much as I do not know the best way. I am transitioning
from Visual FoxPro to SSE.

Sincerely,

Gene Wirchenko