Database Reference
In-Depth Information
Be aware of the number of rows you're loading into each table. Managing hundreds of
millions of rows requires a lot more care than managing one or two million.
Note
Data relationships: If you're storing data in multiple tables, you need to carefully plan how
to join the tables together. It is recommended that you design your joins to be based on
columns that are integers or smaller.
Avoid joining on character-based columns, as it may lead to slower performance.
Caution
Data update: A big consideration in your system is your approach to data updates. As you
acquire new data from source systems or third parties, you need to update that data into
your system. That may involve appending existing data or doing a complete reload, depend-
ing on the strategy you've chosen. Following an append strategy is more complicated, but it
leads to faster update performance and helps you avoid changing historical results.
Reporting and analysis requirements: This is the main driver behind your analytics system
and should be your starting point of the design. Everything you do in your system should be
based on the outcomes you are seeking. Spend adequate time thinking through those
requirements and make sure your system can meet them.
Creating a table
To create a table in SQL Server, follow these steps:
1. Right-click the Tables folder in the Object Explorer window and choose New Table.
The table view appears in the Query window, as shown in Figure 8-15.
2. Enter your column name, choose a data type, and specify whether you want to allow nulls in
your column.
Nulls in SQL Server are handled in a special way. You cannot join on null columns; if
you're looking for nulls, use the Is Null clause in your query.
Note
3. Click the X in the top-right corner of the Query window.
A message box opens asking if you want to save the table.
4. Click Yes, enter the table name, and your table is created.
 
Search WWH ::




Custom Search