Database Reference
In-Depth Information
Listing 6-6 shows code requesting only the columns needed to fill up the DimTitles table. If you compare the
listed columns to the possible Titles table's columns shown in Figure
6-2
, you see that this represents about a 30%
reduction over selecting all the columns by using a query such as
SELECT * FROM Titles
.
Listing 6-6.
Selecting Only Data Required for the Destination Table
Select
[title_id]
, [title]
, [type]
, [pub_id]
, [price]
, [pubdate]
From [Pubs].[dbo].[Titles]
Using Column Aliases
You may want to change the name of your source columns to match the names of your destination columns by
using a column alias. This makes it easier for others to see the correlation between your sources and destinations,
as well as aids in the SSIS configuration (covered in the next chapter). SQL Server allows you to create column
aliases in two formats; the first is
[column name] AS [alias]
, and the second is
[alias]=[column name]
. Both
of these accomplish the same thing, and both are shown in Listing 6-7.
Listing 6-7.
Using Different Styles of Column Aliases
-- Older style column aliases: [column name] as [alias]
Select
[title_id]
as
[TitleId]
, [title] as [TitleName]
, [type] as [TitleType]
--, [pub_id] Will be replaced with a PublisherKey
, [price] as [TitlePrice]
, [pubdate] as [PublishedDate]
From [Pubs].[dbo].[Titles]
-- Newer style column aliases: [alias] = [column name]
Select
[TitleId]
= [title_id]
, [TitleName] = [title]
, [TitleType] = [type]
--, [pub_id] Will be replaced with a PublisherKey
, [TitlePrice] = [price]
, [PublishedDate] = [pubdate]
From [Pubs].[dbo].[Titles]
Ti
■
The first style is an older one that many programmers, including the authors of this topic, were introduced to
when we first started sQL programming. Although it is familiar and simple, it has one basic problem: the aliases are more
difficult to spot when reading the code. The newer style aligns all the aliases on the left side of the column listings and
provides for easier reading and troubleshooting. Perhaps this is why Microsoft documentation typically uses the left-hand
alias style. But whatever the reason may be, to provide consistency, the newer style is what we use throughout this topic.
