Pages

Search This Blog

Wednesday, December 31, 2014

[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

 

Tuesday, December 9, 2014

[DI] ETL versus ELT

When does ETL win?

  • Ordered transformations not well suited to set processing.
  • Integration of third party software tools best managed by Informatica outside of the RDBMS (e.g., name and address standardization utilities).
  • Maximize in-memory execution for multiple step transformations that do not require access to large volumes of historical or lookup data (note: caching plays a role).
  • Streaming data loads using message-based feeds with "real-time" data acquisition.

When does ELT win?

  • Leverage of high performance DW platform for execution reduces capacity requirements on ETL servers - this is especially useful when peak requirements for data integration are in a different window than peak requirements for data warehouse analytics.
  • Significantly reduce data retrieval overhead for transformations that require access to historical data or large cardinality lookup data already in the data warehouse.
  • Batch or mini-batch loads with reasonably large data sets, especially with pre-existing indices that may be leveraged for processing.
  • Optimize performance for large scale operations that are well suited for set operations such as complex joins and large cardinality aggregations.

[DI] A Taxonomy of Data Integration Techniques

 
 There are three main approaches:


1.ETL Approach: (1) Extract from the source systems, (2) Transform inside the Informatica engine on integration engine servers, and (3) Load into target tables in the data warehouse.

2.ELT Approach: (1) Extract from the source systems, (2) Load into staging tables inside the data warehouse RDBMS servers, and (3) Transform inside the RDBMS engine using generated SQL with a final insert into the target tables in the data warehouse.

3.Hybrid ETLT Approach: (1) Extract from the source systems, (2) Transform inside the Informatica engine on integration engine servers, (3) Load into staging tables in the data warehouse, and (4) apply further Transformations inside the RDBMS engine using generated SQL with a final insert into the target tables in the data warehouse.