[Teradata] Temporal table example

I wanted to show you an example of the TEMPORAL feature in Teradata. Its a pretty neat feature.

Basically, it will allow us to keep a history of changes where required. For example, the FULL REFRESH dimensions, will change (based on the customers feedback) to a CDC (change data capture). This is normally a pretty complex process.

With the TEMPORAL feature, coupled with UPSERTS (MERGE), it becomes quite simple.

First - TEMPORAL. Basically Teradata under the covers of a table will manage a "PERIOD" data type - instead of a process having to manage the classic START_DT and END_DT logic. In fact, you dont even need to know about the column.

Also - when a user does a select * from the table, they will only get the CURRENT rows returned.

Please see this example to help.

Example FULL REFRESH Table that will move to this process:

CREATE MULTISET TABLE EDW_STAGE.tempo_test_auto_purchase_pack_size ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      auto_purchase_pack_size_id INTEGER,
      duration_mins INTEGER,
      pack_size INTEGER,
      display_order INTEGER,
      is_active CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      is_default CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      created_dt TIMESTAMP(6),
      created_by_user_id VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      last_update_dt TIMESTAMP(6),
      last_updated_by_user_id VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      is_deleted CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      source_sys_id SMALLINT,
      company_brand_cd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
       duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
         AS TRANSACTIONTIME
      
      )
 PRIMARY INDEX ( auto_purchase_pack_size_id );
 

NOTE the column called duration - it will NOT have values inserted...Teradata manages this.

I will do an initial insert of 3 columns and those look like this when we select them:

select * from tempo_test_auto_purchase_pack_size;

Note The 2nd row (ID of 3) where the column duration_mins is 40.

We issue an update (or merge) to this table:

update tempo_test_auto_purchase_pack_size set duration_mins = 95 where auto_purchase_pack_size_id = 3;

Now look at the select * from tempo_test_auto_purchase_pack_size;

Note that the new value is the LATEST row....BUT if we want to see history:

  TRANSACTIONTIME AS OF TIMESTAMP '2015-01-10 11:24:10' select * from tempo_test_auto_purchase_pack_size;

And finally - so see them all:

 NONSEQUENCED TRANSACTIONTIME select * from tempo_test_auto_purchase_pack_size;

Note that with this query you see the duration column for this temporal feature.

Hope this helps.

[SQL Server] Change Schema Name Of Table In SQL Server

I want to change schema name of table Employees in Database. In the current table Employees database schema name is dbo I want to change it to exe. How can I do it ?

example:

FROM

dbo.Employees

TO

exe.Employees

I tried with this query.

ALTER SCHEMA exe TRANSFER dbo.Employees

But this give me error.
Error:
Cannot alter the schema 'exe', because it does not exist or you do not have permission.
What I missed or did wrong ?

Solution:

Create Schema :

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'exe')) 
BEGIN
    EXEC ('CREATE SCHEMA [exe] AUTHORIZATION [dbo]')
END

 

ALTER Schema :

ALTER SCHEMA exe 
    TRANSFER dbo.Employees

 

[T-SQL] Contained Database

A Contained Database is a database which contains all the necessary settings and metadata, making database easily portable to another server. This database will contain all the necessary details and will not have to depend on any server where it is installed for anything. You can take this database and move it to another server without having any worries.

The real question is, “What about users who are connecting to this database?” Once the contained database is moved, the users are moved as well, and users who belong to the contained database will have no access outside the contained database.

In summary, “Database is now self-contained. Database which is ’contained’ will not depend on anything on the server where it is installed.”

Let us try out this feature on SQL Server Denali. We will do the following steps:

1. Enable Contained Database
2. Create Contained Database
3. Create User in Contained Database
4. Try if the user can access outside Contained Database

We can do various tests on this subject; however, in this blog post we will limit out exercise to the above four points.

Enable Contained Database

Run the following code on SQL Server Denali. This code will enable the settings for the contained database.

sp_configure 'show advanced options',1
GORECONFIGURE WITH OVERRIDE
GOsp_configure 'contained database authentication', 1
GORECONFIGURE WITH OVERRIDE
GO

Create Contained Database

CREATE DATABASE [ContainedDatabase]
CONTAINMENT = PARTIALON PRIMARY( NAME = N'ContainedDatabase', FILENAME = N'C:\ContainedDatabase.mdf')LOG ON( NAME = N'ContainedDatabase_log', FILENAME = N'C:\ContainedDatabase_log.ldf')GO

Create User in Contained Database

USE [ContainedDatabase]
GOCREATE USER ContainedUserWITH PASSWORD = 'pass@word';GO

Try if this user can access out side Contained Database

To test this, we will attempt to login in the database with default settings (where login database is the master).

When we attempt this, we will be not able to login in the server simply because the user does not exist at the server level.

Now, let us try to login in the system using the username which was created in the Contained Database.

You will notice that the login would be successful in the server. When expanded it, the user will have access to the contained database only, and not to any other database.

We will tackle more about this interesting subject in the future.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

[T-SQL] How to create new error log file

There are total two methods to do this:

1. Restart the server
2. EXEC sp_cycle_errorlog

First option is not a suitable one as we restart the server every time.

Hope this post is helpful to many of the dba's.

[T-SQL] Create and Encrypted Stored Procedure

An answer to a very simple question , how to create a stored procedure any problem if we create it.

Here is the script

USE AdventureWorks
GO
-- Create Encyrpted SP
CREATE PROCEDURE uspEnc
WITH ENCRYPTION
AS
SELECT *
FROM Sales.SalesOrderDetail
UNION
SELECT *
FROM Sales.SalesOrderDetail
GO
-- Execute SP
EXEC uspEnc
GO

Question: if any stored procedure is encrypted can we see its definition in Activity Monitor.
Answer: No

Question: Any tool to decrypt the SP
Answer: SQL Decryptor, Decrypt SQL

Question: Should a DBA recommend it
Answer: Encrypted stored procedures are horrible. You can’t see what queries are executed, you don’t have a clue what the execution plan is. So if users are complaining about the slowness of the system, and everything is encrypted… then it will not be possible to look into it.