------------------------------------------------------------------------------------------------------------
1.What is the use of CTE ( Common Table Expression)?
a.no need of temp table & also no loop required.
------------------------------------------------------------------------------------------------------------
ans.
SQL SERVER – Common Table Expression (CTE) and Few Observation
SQL SERVER – 2005 – 2008 – Delete Duplicate Rows
Deleting duplicate rows
Counting duplicate rows
Delete duplicate rows using CTE & ROW_NUMBER( ) feature of SQL Server 2005 & SQL Server 2008.
/* Create Table */
CREATE TABLE DuplicateRecordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRecordTable
SELECT 1,1
UNION ALL
SELECT 1,1 --duplicate
UNION ALL
SELECT 1,2
UNION ALL
SELECT 1,2 --duplicate
...
GO
/* Delete Duplicate records */
WITH CTE (Col1,Col2, DuplicateCount)
AS
(
SELECT Col1,Col2,
ROW_NUMBER( ) OVER(PARTITION BY Col1,Col2 ORDER BY Col1) AS
DuplicateCount
FROM DuplicateRecordTable
)
DELETE FROM CTE WHERE DuplicateCount > 1
GO
Note:
MAXRECURSION - Prevent CTE Infinite Loop
SELECT * FROM Emp_CTE OPTION (MAXRECURSION 5)
------------------------------------------------------------------------------------------------------------
(dba)
2.What is a snap-shot?
ans.
introduction to sql server 2008 create snapshot
create snapshot - sql server 2008 - enterprise edition only.
Microsoft SQL Server Management Studio
yournamedatabase(SQL Server 10.0.1600 - yourname\Administrator)
-Database
+System Databases
Database Snapshots
+AdventureWorks
+..
+Security
+Server Objects
+Replication
SQLQuery window:By Clicking New Query tab.
CREATE DATABASE AdventureWorks_SNAPSHOT_TODAY_DATE_TIME
ON
(NAME = AdventureWorks_data, FILENAME =
'c:\snapshot\AdventureWorks_SNAP_SHOT_TODAY_DATE_TIME.mdf')
AS SNAPSHOT OF AdventureWorks
GO
--After running the above command it creates an empty file, it creates with .mdf
--database and empty file ,its readonly even if its of database
--no transaction lob as there will no write to it.
a.what really happens.
i.when you change some to a table or field, Before writing to the database
it writes the original things of database to the snap shot.
This slows down the database.Keep on modify database the snapshot file
will change.Say you change 50% of database file snap shot will be upto that.
When snapshot is written it writes when first time the modificaiton done.But second , third time whatever
is modified its not captured. Even the first modification won't be , previous to first modification whatever
original it is , written to the snap shot.
b.if you query the snap shot , what happens? It searches for the information in the snap shot then when
not found searches in the database .mdf file.It searched in both. As if there would be no modification to the
data it won't be written to the snapshot.
c.You can not have a full indexed database with a snap shot.
i.once you have created a snap shot u can not drop the database.
ii.you can not deattach or restore a database.
(sysdatabase u can not create snapshot)
iii.u can have multiple snapshots but need to delete the previous ones.
u won't be able to revert the data from snap shot.
iv.when you restore the database from snap shot the backup link is broken
so you have to backup fully again.
iv.if you have multiple files for database, each file has to be specified for
snap-shot.
i.what is snap shot?
ans.
Snap shot is not replica of database or copy of database.
At the beginning snap shot is not a copy of database its a "Parse File" .
Parse File is empty it does not have User Data.
Question is why we have a empty file at the beginning.
If from the begining it has all the data its a very big file that not the idea for snap shot.
Snap shot is not backup to database, backup is required.
Snap is protetion to the database.Backup has to be their.
If anything goes wrong with database it can revert it back, provided if you
are ok to loose the data after the change is done.
Snapshot is good for reporting tool.
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
3.Slowly changing dimension
http://en.wikipedia.org/wiki/Slowly_changing_dimension
Type I
This methodology overwrites old with new data, and therefore does not track historical data. Its common uses are for misspelled names. (Assuming you won't need to know how it was misspelled in the past.)
Type II
This method tracks historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers. Unlimited history is preserved for each insert.
For example, if the supplier relocates to Illinois the version numbers will be incremented sequentially:
Type III
This method tracks changes using separate columns and preserves limited history. The Type II preserves unlimited history as it's limited to the number of columns designated for storing historical data. The original table structure in Type I and Type II is the same but Type III adds additional columns. In the following example, an additional column has been added to the to record the supplier's original state - only the previous history is stored.
------------------------------------------------------------------------------------------------------------
(dba)
Tells similar things to prepare.
Learn how to Replicate Data in Microsoft SQL Server 2008-Designing and
Log Shipping, Mirroring are not form of replication.
The above for high availibity of the database.
Both Shipping & Mirroring dealing with the primary database.
There is a secondary database.
Publisher
Subscriber
Agent
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
Oracle Database:
1.What is a Materialized Views?
ans.
Materialized Views
Materialized View Concepts and Architecture.
Materialized View Concepts
1. Oracle uses material views ( also known as snapshots in prior releases) to replicate data.
What is a Materialized View?
1. A materialized view is a replica of a target master from a single point in time.
Why Use Materialized Views?
ans.
Ease Network Loads:
i. If one goal is to reduce network loads, use materialized views to distribute
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
