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…

Identity Column Insert

Today, I want to talk about how to insert a value into a identity column. Most of the time, the auto increment or IDENTITY column will just be happy by being left alone. Very seldom, a power user does something stupid like turn off the foreign key constraint and remove a key from a reference table. How do we add back the key? I will be dusting off the [WILDLIFE] database from a prior article. This database has one table named [ANIMALS]. It has 445 unique animal names each with…

Import & Export Data – Part 1

There are not many real life databases that do not import data from external sources or do not export data for delivery to external targets. I am going to devote a series of talks on different ways to perform these functions. The INSERT statement is the easiest way to load small sets of static information into a database. We will be working again with the Boy Scouts of America (BSA) hypothetical database. The first example loads the rank table with a data. We can see the auto-increment surrogote key is…

Temporary Tables – Part 5

Today, I am going to talking about common table expressions. A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single DML statement. A CTE is simular to a derived table in which the results are not stored as an object; However, CTE’s can be recursive or built upon other CTE’s. The WITH key word is used to indicate the start of a CTE and needs to be preceeded with a semicolon. The rest of the syntax is like a stored…

Temporary Tables – Part 4

Today, I am going to talk about regular tables in tempdb. Please check out the Microsoft white paper on working with tempdb for a detailed discussion. The advantage of creating a table in tempdb is that it is persistent until the server is rebooted. Upon startup of SQL Server, tempdb is created again from scratch. One use of this technique is to have startup procedures create tables in tempdb that are used by an application program. One can leverage the fact that operations within tempdb are minimally logged. The major…

Temporary Tables – Part 3

Today, I am going to talk about table variables. In general, TSQL variables are defined by using the DECLARE key word and preceeding the variable name with a @ sign. Table variables are created by using the TABLE key word just after the variable name. The declaration following this key word includes column definitions — names, data types, and constraints. The only constraint types allowed in the declaration are PRIMARY KEY, UNIQUE, NULL, and CHECK. Indexes are a bi-product of a PRIMARY and UNIQUE constraints. The scope of a table…

Temporary Tables – Part 2

Today, I am going to talk about local and global temporary tables. A local temporary table is defined by using the CREATE TABLE syntax with the name of the table prefixed by one number sign #. The scope of the local temporary table is the current session or SPID. A global temporary table is defined by using the CREATE TABLE syntax with the name of the table prefixed by two number signs ##. The table is visible to all sessions. The scope of the global temporary table is the current…

Temporary Tables – Part 1

Today, I am going to be starting a series of articles on temporary tables. I will be focusing on derived tables. A derived table is a sub-query that is used to as input to the outer query. An table alias should be used outside the parenthesis to reference the inner query. I think of derived tables as a cross between temporary tables and dynamic views that go away after the query executes. The first example of a derived table creates the car ownership table for person a. Please see the…