How to compare two tables - SQL Server

http://www.sql-server-tool.com/compare-two-tables.htm


Sample SQL statements to compare data in two tables with identical structure.
(Statements work for MS SQL Server, as well as for many other databases.)
To find records which exist in source table but not in target table:

SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t2.Id = t1.Id)
or
SELECT * FROM t1 LEFT OUTER JOIN T2 on t1.Id = t2.Id WHERE t2.Id IS NULL


If the primary key consists of more than one column, you can modify SQL statement:

SELECT Id, Col1 FROM t1 WHERE NOT EXISTS
(SELECT 1 FROM t2 WHERE t1.Id = t2.Id AND Col1.t1 = Col2.t2)

On SQL Server 2005 or newer you can use the EXCEPT operator:

SELECT Id, Col1 FROM t1 EXCEPT SELECT Id, Col1 FROM t2


To find records which exist in source table but not in target table, as well as records which exists in target table but not in source table:

SELECT * FROM (SELECT Id, Col1 FROM t1, 'old'
UNION ALL
SELECT Id, Col1 FROM t2, 'new') t
ORDER BY Id

Note: For tables with large amounts of data UNION statement might be very slow.

Rigid & Flexible relationship of attributes in SSAS

Rigid relationships are defined where you are shre that members will not change levels or their respective attribute relationships. For example. The time dimension. We know that month "January 2009" will only belong to Year "2009".

Flexible relations on the contarary are defined where there is a possibility that members can move around. For example, an employee and department. An employee can be in accounts department today but it is possible that the emplyee will be in Marketing department tomorrow.


The key point here is that flexible relationships force Analysis Services to drop and re-compute any existing aggregations during incremental dimension processing.Rigid relationships do not require the re-computing of existing aggregations during incremental processing and thereby reduce total processing time. Hierarchies with rigid relationships can also be queried faster than those with flexible relationships. The flip side of the coin is that changing any dependent attribute that has a rigid relationship within any hierarchy requires full process of the entire dimension.

updating evaluation Microsoft Products

1.execute CMD as administrator user

2.input the command "slmgr.vbs/rearm"

Using XEvents (Extended Events) in SQL Server 2008 to detect which queries are causing Page Splits

http://msmvps.com/blogs/eladio_rincon/archive/2008/12/07/using-xevents-extended-events-in-sql-server-2008-to-detect-which-queries-are-causing-page-splits.aspx

volatile table & staging table

A volatile table is defined as a table whose contents can vary from empty to very large at run time.

A staging table allows incremental maintenance support for deferred materialized query table.

Quick test for SAN throughput

backup database Trace_DB4DBA to disk = 'Nul'

dtexec & dtutil

dtexec /sq PWeb /ser 10.1.1.1\uat /va /l "DTS.logprovidertextfile;c:\log.txt"

dtexec /ser 137.154.158.191\uat /sq Callista_IncrementalEnrolmentProcess_PWeb /va /U ssis /P ssis /l "DTS.LogProviderTextFile.1;c:\log.txt"

dtexec /ser 137.154.158.191\uat /sq Callista_IncrementalEnrolmentProcess_PWeb /U ssis /P ssis /l "DTS.LogProviderTextFile.1;c:\log.txt"


dtexec /ser nelson\dev /sq "Ipay Delegations Register Package DEV" /va


dtutil /Sql "Ipay Delegations Register Package DEV" /copy dts;abc