Tuesday, July 16, 2019

CHAPTER 2: Microsoft SQL Sever

CHAPTER 2. UNIONS/TABLE VARIABLES/SELECT all rows and columns from table


As we have seen before, a Join adds column from different table sources. But what if you want to combine rows from different sources? In this case you can use a UNION. Suppose you're planning a party and want to invite not only employees but also customers. Then you could run this query to do it:

SELECT FirstName+' '+LastName as ContactName, Address, City FROM Employees
SELECT ContactName, Address, City FROM Customers

It will return names, addresses and cities from the employees and customers in one single table. Note that duplicate rows (if there should be any) are automatically eliminated (if you don't want this, use a UNION ALL instead). The column number, column names, oder and data type must match across all the select statements that are part of the union- this is why the first SELECT combines FirstName and LastName from Employee into ContactName.


It can be useful, if you need to deal with temporary data (especially in a stored procedure), to use table variables;
The difference between a "real" table and a table variable is that it just exist in memory for temporary processing.

  RegionID int,
  RegionDescription NChar (50)

creates a table memory. In this case the @ prefix is mandatory because it is a variable. You can perform all DML , operations mentioned above to insert, delete and select rows
, e.g

  INSERT INTO @Region values (3,Northern)
  INSERT INTO @Region values (4,Southern)

But normally, you would populate it based on real table like

  INSERT INTO \@Region
  SELECT * FROM dbo.Region WHERE RegionID=2;

which would read the filtered values from the real table dbo.Region and inserting it into the memory table @region- where it can be used for further processing. For example, you could use it in a join like:

  SELECT * FROM Territories t
  JOIN @Region r on t.RegionID = r.RegionID

NOTE : Microsoft only recommends the use of the table variables if the number of rows of data in the table variable are less than 100. If you will be working  with larger amounts of data, use a temporary table or temp. table, instead.



 SELECT * FROM table_name

Using the asterisk operator serves a shortcut for selecting all the columns in the table. All rows will also be selected because this SELECT statement does not have a WHERE clause, to specify any filtering criteria. This would also work the same way if you added an alias to the table, for instance in this case:

  SELECT * FROM Employees AS e

or if you wanted to select all from a specific table you can use the alias +".*":

  SELECT e .*, d.DepartmentName
  FROM Employees AS e
  INNER JOIN Department AS d
  ON e.DepartmentID = d.DepartmentID

Database object may also be accessed using fully qualified names:

 SELECT * FROM [server_name].[database_name].[schema_name].[table_name]

This is not necessarily recommended, as changing the server and/or database names would cause the queries using fully_qualified names to no longer execute due to invalid object names. Note that the fields before table_name can be omitted in many case if the queries are executed on a single server, database and schema, respectively. However, it is common for a database to have multiple schema and in these cases the schema name should not be omitted when possible.

Warning: Using SELECT * in production code or stored procedures can lead to problems later on (as new columns are added to the table, or if columns are rearranged in the table), especially if your code makes simple assumption about the order of columns returned. So it's safer to always explicitly specify column names in SELECT statements for production code.

  SELECT col1, col2, col3

  FROM table_name