Using Variables

I am going to talk about Transaction SQL variables. Any stored program that does complex business processing will use one of more of variables to craft an algorithm. During my college days, I had to take a couple physics classes. Newton’s second law states that force is equal to mass times acceleration. The comic strip on the left shows how variables in the fourth picture can be used as place holders in a complex equation. This concept is key for crafting very complex programs. The DECLARE keyword is used in…

MS Excel -N- Azure SQL Database

I knew Windows Azure SQL Databases have been around for a couple of years but never had the time or need to research them. With a possible data center consolidation in the future, I was wondering if my company could leverage a Windows Azure SQL Databases to reduce the total cost of ownership for certain applications? Today, I will be working with the sample [AUTOS] cloud database that I created in a previous blog. I want to test connectivity to this cloud database using common programs and tools that my…

MS Access -N- Azure SQL Database

I knew Windows Azure SQL Databases have been around for a couple of years but never had the time or need to research them. With a possible data center consolidation in the future, I was wondering if my company could leverage a Windows Azure SQL Databases to reduce the total cost of ownership for certain applications? Today, I will be working with the sample [AUTOS] cloud database that I created in a previous blog. I want to test connectivity to this cloud database using common programs and tools that my…

Command Line Utilities -N- Azure

I knew Windows Azure SQL Databases have been around for a couple of years but never had the time or need to research them. With a possible data center consolidation in the future, I was wondering if my company could leverage a Windows Azure SQL Databases to reduce the total cost of ownership for certain applications? Today, I will be working with the sample [AUTOS] cloud database that I created in a previous blog. I want to test connectivity to this cloud database using common programs and tools that my…

User Defined Views – Part 4

  Most information technology professionals do not know that data can be modified thru a VIEW. The insert, update, and delete statements, Data Manipulation Language (DML), can be executed on a view as long as the following set of rules are adhered to.   Modifications must reference columns from only one base table. The columns must directly reference the underlying data table. Today, I am going to explore how to modify data in a view using the [AUTOS] sample database. Enclosed is the complete TSQL for this article including the…

Outer Apply Operator

The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. Today, I am continuing our exploration of this reserved word by reviewing queries that merge data using the APPLY operator. The APPLY operator come in two flavors: CROSS and OUTER. I am going to concentrate on the later during this discussion. I will be using…

Cross Apply Operator

The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. Today, I am continuing our exploration of this reserved word by reviewing queries that merge data using the APPLY operator. The APPLY operator come in two flavors: CROSS and OUTER. I am going to concentrate on the former during this discussion. I will be using…

Summarizing Data – Part 3

The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. I am continuing our exploration by reviewing SELECT queries that aggregate data. Data aggregation is the process of converting many records into a few records with special meaning. I will be using the Adventure Works 2012 sample database supplied by Microsoft during this talk. I…

Summarizing Data – Part 2

The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. I am continuing our exploration by reviewing SELECT queries that aggregate data. Data aggregation is the process of converting many records into a few records with special meaning. I will be using the Adventure Works 2012 sample database supplied by Microsoft during this talk. Today,…

Summarizing Data – Part 1

The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. Today, I am continuing our exploration by reviewing SELECT queries that aggregate data. Data aggregation is the process of converting many records into a few records with special meaning. Many of the functions used in aggregation have statistical meaning such as minimum value, maximum value,…