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

Groups > comp.databases.ms-sqlserver > #92

Re: Index or ORDER BY Using Another Table's Column

Cross-posted to 2 groups.

Show all headers | View raw

>  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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web