Note on <Beginning Microsoft SQL Server 2012 Programming> - 01

Installing AdventureWork Database

You will find its a .mdf file after you download it from wrox.com. And I am using SQL Server 2012 Express on Windows 7, the following instructions may merely make sense within the same environment.

According to the post, we should attach the file.

At this point, I got error as:

I did all the following steps and so that it worked eventually:

First of all, copy the .mdf file to dir: C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA, and change the property like:

And you will still got error, saying the file adventurework2008R2_data.mdf cannot be found, it seems that the metadata about the physical file is wrong so you have to specify it yourself, you need to click on '...' button to locate the file, and in addition, you must remove the log file. 

Chapter 1: Rdbms basics: what makes up a sql server database?

master	This database holds a special set of tables (system tables) that keeps track of the system as a whole. All extended and system-stored procedures are stored in this database. The system tables, including those found in the master database, were, in the past, occasionally used in a pinch to provide system confi guration information.
model	The model database forms a template for any new database that you create.
msdb	msdb is where the SQL Agent process stores any system tasks. Other major subsystems in SQL Server make similar use of msdb. SSIS packages and policy-based management defi nitions are examples of other processes that make use of msdb.
tempdb	Whenever you issue a complex or large query that SQL Server needs to build interim tables to solve, it does so in tempdb. Whenever you create a temporary table of your own, it is created in tempdb. It has the distinction of being the only database in your system that is rebuilt from scratch every time you start your SQL Server.

The Transaction Log

The changes you make to the data are written serially to the transaction log. At some later point in time, the database is issued a checkpoint; it is at that point in time that all the changes in the log are propagated to the actual database file.

Rules for Naming

• The name of your object must start with any letter, as defined by the specifi cation for Unicode 3.2. This includes the letters most Westerners are used to: A–Z and a–z. Whether "A" is different from "a" depends on the way your server is confi gured, but either makes for a valid beginning to an object name. After that first letter, you're pretty much free to run wild; almost any character will do.
• The name can be up to 128 characters for normal objects and 116 for temporary objects.
• Any names that are the same as SQL Server keywords or contain embedded spaces must be enclosed in double quotes ("") or square brackets ([]). Which words are considered keywords varies depending on the compatibility level to which you have set your database.

Chapter 2: Learning the tools of the trade

You’ll want to leave Shared Memory enabled for when you’re accessing the machine locally. (It works only when the client is on the same physical server as the SQL Server installation.) But you need to enable at least one other NetLib if you want to be able to contact your SQL Server remotely (say, from a web server or from diff erent clients on your network).

The Aliases list is a listing of all the servers on which you have defined a specific NetLib to be used when contacting that particular server.

Interacting through the Query Window

Show Execution Plan

Chapter 3: The Foundation Statements Of T-SQL

The INSERT INTO . . . SELECT Statement

Fits when you want to INSERT a block of data from another source, such as:

• Another table in your database
  
• A totally different database on the same server
  
• A heterogeneous query from another SQL Server or other data
  
• The same table (usually you're doing some sort of math or other adjustment in your SELECT statement, in this case)
  

Chapter 4: JOINs

SELECT <select list>
FROM <first_table>
	<join_type> <second_table>
		[ON <join_condition>]

There are 4 forms of the JOIN clause:

• INNER JOIN: exclusive on both sides.
  Alternative syntax:

  ESELECT *
  FROM TABLE1, TABLE2
  WHERE TABLE1.FIELD1 = TABLE2.FIELD1

• OUTER JOIN (both LEFT and RIGHT): inclusive, one very common use for the inclusive nature of OUTER JOINs is finding unmatched records in the exclusive table, like orphan.
  Alternative syntax:

  SELECT *
  FROM TABLE1, TABLE2
  WHERE TABLE1.FIELD1 *= TABLE2.FIELD1

• FULL JOIN: inclusive on both sides.
  
• CROSS JOIN:
  Alternative syntax:

  SELECT *
  FROM TABLE1, TABLE2

What the author said:

the use of the * operator in joins is a bad habit. Select only the columns that you are going to be using and make your WHERE clause as restrictive as possible. Every additional record or column that you return takes up additional network bandwidth and often additional query processing on your SQL Server. The upshot is that selecting unnecessary information hurts performance not only for the current user, but also for every other user of the system and for users of the network on which the SQL Server resides.

When you want to refer to a column where the column name exists more than once in your JOIN result, you must fully qualify the column name. You can do this in one of two ways:

• Provide the name of the table that the desired column is from, followed by a period and the column name (Table.ColumnName)
  
• Alias the tables, and provide that alias, followed by a period and the column name (Alias .ColumnName), as shown in the previous example.
  

To aliase table, you state the alias you want to use right after the name of the table, you can choose to or not to use the AS keyword.

Using an alias is an all-or-nothing proposition. Once you decide to alias a table, you must use that alias in every part of the query. This is on a table-by-table basis, which means you jst cannot mix them.

Dealing with More Complex OUTER JOINs

It is when combining an OUTER JOIN with other JOINs that the concept of sides becomes even more critical. What's important to understand here is that everything to the “left” — or before — the JOIN in question will be treated just as if it were a single table for the purposes of inclusion or exclusion from the query. The same is true for everything to the “right” — or after — the JOIN.

The scenario is that you perform an OUTER JOIN and then perform a INNER JOIN afterward, you may lose the records that do not have a match in the INNER JOIN, to solve this, there are three workaround:

1. Add yet another OUTER JOIN:
   

   SELECT ...
   FROM ...
   LEFT JOIN ...
   	ON ...
   LEFT JOIN ...
   	ON ...

2. Change the order of the JOINs:
   

   SELECT ...
   FROM ...
   INNER JOIN ...
   	ON ...
   RIGHT OUTER JOIN ...
   	ON ...

3. Group the JOINs together:
   

   SELECT ...
   FROM ...
   LEFT JOIN (
   	...
   	JOIN ...
   		ON ...
   )
   	ON ...

   The key to grouping joins is the order of the join conditions, not the parentheses in the example.
   

STACKING RESULTS WITH UNION

• All the UNIONed queries must have the same number of columns in the SELECT list.
  
• The headings returned for the combined result set will be taken only from the first of the queries.
  
• The data types of each column in a query must be implicitly compatible with the data type in the same relative column in the other queries. Refer to the conversion table in Chapter 1).
  
• Unlike non-UNION queries, the default return option for UNIONs is DISTINCT rather than ALL. With UNION, you must explicitly say you want ALL rows in order to see duplicates.
  
• SQL Server will treat rows with identical NULL columns as duplicates.