What are "Auxiliary Tables" in SQL Server ?

Posted on 2010-01-07
Medium Priority
Last Modified: 2012-05-08

What are "Auxiliary Tables" in SQL Server ? Can you please provide a simple example on it ? Please explain the code in simple terms so that i can better understand it easily. Please do not provide articles.

Question by:milani_lucie
LVL 60

Expert Comment

ID: 26201137
Ive not heard of an auxiallary sql server table before...are you referring to the system tables?

select * from sys.tables
select * from sys.procedures


select * from sys.dm_exec_requests
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 800 total points
ID: 26201148
LVL 18

Accepted Solution

brejk earned 1200 total points
ID: 26201258
Auxiliary tables are a kind of helper tables. Samples: auxiliary table of numbers and auxiliary number of dates. These tables are usually used for some optimizations. They often allow to perform set based operations instead of cursors or loops.

Here you can find some old fashioned scenarios when an auxiliary table is useful: http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html.

It's fairly easy to create an auxiliary table with Common Table Expression mechanism in SQL Server 2005/2008:

DECLARE @MaxNumber int;
SET @MaxNumber = 1000000;
  SELECT 1 Num
  SELECT Num + 1
  WHERE Num < @MaxNumber
INTO dbo.AuxiliaryTableOfNumbers

Now all you have to do is to put an index on this table (you can simply add a primary key on Num column) and the table is ready for use.
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.


Author Comment

ID: 26201331

I do not have access to that site. Can you please provide me the code and explain it little bit ?

LVL 60

Expert Comment

by:Kevin Cross
ID: 26201554
Yes, it greatly depends on your definition of auxiliary tables.  As Tim pointed out, you could be referring to system tables.  Additionally, you could think of this as utility (helper) tables such as a numbers table.

Here is another look at Numbers table example with thought on having a utility / helper / auxiliary database dedicated to such tables.


So please define what you were looking for with "auxiliary".
LVL 60

Expert Comment

by:Kevin Cross
ID: 26201574
BTW, the link from Aneesh is to an article that is referring to auxiliary tables as helper tables that aid you in making your T-SQL solutions easier/more elegant; therefore, it falls in line with brejk's comment.

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question