?
Solved

What are "Auxiliary Tables" in SQL Server ?

Posted on 2010-01-07
6
Medium Priority
?
2,503 Views
Last Modified: 2012-05-08
Hi,

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.

Thanks
0
Comment
Question by:milani_lucie
6 Comments
 
LVL 60

Expert Comment

by:chapmandew
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

or

select * from sys.dm_exec_requests
0
 
LVL 75

Assisted Solution

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

Accepted Solution

by:
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;
;WITH CTE_Nums AS (
  SELECT 1 Num
  UNION ALL
  SELECT Num + 1
  FROM CTE_Nums
  WHERE Num < @MaxNumber
)
SELECT Num
INTO dbo.AuxiliaryTableOfNumbers
OPTION (MAXRECURSION 0);

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

by:milani_lucie
ID: 26201331
aneeshattingal:

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

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

http://sqlservernation.com/blogs/tipweek/archive/2009/04/27/the-numbers-table-round-2.aspx

So please define what you were looking for with "auxiliary".
0
 
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.
0

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