Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #91 > unrolled thread

Index or ORDER BY Using Another Table's Column

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Index or ORDER BY Using Another Table's Column Gene Wirchenko <genew@ocis.net> - 2011-04-01 14:47 -0700
    Re: Index or ORDER BY Using Another Table's Column Gert-Jan Strik <sorrytoomuchspamalready@xs4all.nl> - 2011-04-02 10:29 +0200
      Re: Index or ORDER BY Using Another Table's Column Gene Wirchenko <genew@ocis.net> - 2011-04-02 21:01 -0700

#91 — Index or ORDER BY Using Another Table's Column

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

[toc] | [next] | [standalone]

#92

>  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

[toc] | [prev] | [next] | [standalone]

#95

On Sat, 02 Apr 2011 10:29:46 +0200, Gert-Jan Strik
<sorrytoomuchspamalready@xs4all.nl> wrote:

>>  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)

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

>>  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.

     thank you.  I missed that.

>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.

     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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web