Ms Sql Server Technology Discussions

[deals2buy]

[b] Changing Default Installation Path for SQL Server[/b]


http://blog.sqlauthority.com/2012/11/11/sql-server-changing-default-installation-path-for-sql-server/

[deals2buy]

[b] SCD Implementation with TSQL[/b]



SCD ETL is a typical problem in DataWarehouse.
Theory is at : [url="http://en.wikipedia.org/wiki/Slowly_changing_dimension"]http://en.wikipedia.org/wiki/Slowly_changing_dimension[/url]
The proposed example refers to SCD type 6 :
[url="http://en.wikipedia.org/wiki/Slowly_changing_dimension#Pure_Type_6_implementation"]http://en.wikipedia.org/wiki/Slowly_changing_dimension#Pure_Type_6_implementation[/url]
Let's say we want an ETL that does not use SSIS.
The solution is quite simple , uses TSQL except.



[CODE]
-- Create the SCD dimension
CREATE TABLE fi_drop_dim_a
( Synth_Id INT not null, -- DWH synthetic key
Natural_Key VARCHAR(50) not null, -- Business key from source system
Attr1 VARCHAR(50), Attr2 VARCHAR(50), Attr3 VARCHAR(50) ,
Date_From INT not null,
Date_To INT ,
CONSTRAINT pk_fi_drop_dim_a PRIMARY KEY CLUSTERED ( Synth_Id , Date_from ) ON dwhsk_stage_data
) ON dwhsk_stage_data

ALTER TABLE fi_drop_dim_a ADD CONSTRAINT uk_fi_drop_dim_a UNIQUE ( Natural_Key , Date_from )
ON dwhsk_stage_data
-- Create the source table
CREATE TABLE fi_drop_src_a
( Natural_Key VARCHAR(50) not null,
Attr1 VARCHAR(50), Attr2 VARCHAR(50), Attr3 VARCHAR(50) ) ON dwhsk_stage_data

INSERT INTO fi_drop_src_a VALUES ( 'k-0','uno','due','tre')
INSERT INTO fi_drop_src_a VALUES ( 'k-1','undici','dodici','tredici')
INSERT INTO fi_drop_src_a VALUES ( 'k-2','ventuno','ventidue','ventitre')
INSERT INTO fi_drop_src_a VALUES ( 'k-3','trentuno','trentadue','trentatre')
INSERT INTO fi_drop_src_a VALUES ( 'k-4','quarantauno','quarantadue','quarantatre')-- Follows the TSQL script that fills the dimension in one transaction
DECLARE @processing_date INT = 20120924
DECLARE @ins INT = -1, @upd INT = -1, @del INT =-1
BEGIN TRAN
INSERT INTO fi_drop_dim_a ( Synth_Id , Natural_Key , Attr1, Attr2, Attr3, Date_From )
SELECT CASE WHEN exi.Synth_Id IS not null THEN exi.Synth_Id ELSE
qmax.max_id +
ROW_NUMBER() OVER ( ORDER BY
isnull(exi.Synth_Id,-1), q1.Natural_Key ) END new_id ,
q1.* , @processing_date FROM (
SELECT Natural_Key , Attr1, Attr2, Attr3 FROM fi_drop_src_a
EXCEPT
SELECT Natural_Key , Attr1, Attr2, Attr3 FROM fi_drop_dim_a ) q1
LEFT join fi_drop_dim_a exi ON q1.Natural_Key = exi.Natural_Key and exi.date_to IS null
join ( SELECT ISNULL(MAX(Synth_Id),0) max_id FROM fi_drop_dim_a ) qmax ON 1=1

SET @ins = @@ROWCOUNT
-- Close the old version of the rows that have changed
UPDATE tg SET date_to =
cast( CONVERT( varchar(10), DATEADD(day,-1, cast( CAST( @processing_date as varchar(10)) as datetime) ), 112 ) as int )
FROM fi_drop_dim_a tg join fi_drop_dim_a n ON tg.natural_key = n.natural_key and n.date_to IS null
and N.date_from = @processing_date and tg.date_from < @processing_date and tg.date_to IS null
SET @upd = @@ROWCOUNT

-- Close the rows that have been deletes, thus they are not found into source
UPDATE tg SET date_to =
cast( CONVERT( varchar(10), DATEADD(day,-1,
cast( CAST( @processing_date as varchar(10))
as datetime) ), 112 ) as int )
FROM fi_drop_dim_a tg LEFT join fi_drop_src_a s ON tg.Natural_Key = s.Natural_Key
WHERE tg.Date_To IS null and s.Natural_Key IS null
SET @del = @@ROWCOUNT
SELECT @ins-@upd AS inserted_rows, @upd AS updated_rows, @del AS deleted_rows
-- rollback commit
SELECT * FROM fi_drop_dim_a
SELECT * FROM fi_drop_src_a

-- After 3 days the dimension changes
INSERT INTO fi_drop_src_a VALUES ( 'k-5','cinquantauno','cinquantadue','cinquantatre')
UPDATE fi_drop_src_a SET Attr1 = 'eleven' , Attr2 = 'twelve' WHERE Natural_Key = 'k-1'
-- Run now the ETL script with @processing_date = 2012-09-27 and check the resultSELECT * FROM fi_drop_dim_a
SELECT * FROM fi_drop_src_a
DROP TABLE fi_drop_dim_a
DROP TABLE fi_drop_src_a
[/CODE]

[9Pardhu]

Happy Diwali to all SQL Server Guys....

[deals2buy]

[quote name='9pardhu' timestamp='1352825691' post='1302789720']
Happy Diwali to all SQL Server Guys....


[/quote]
n wish you the same too

[gundugadu]

happy diwali mamals

[deals2buy]

[quote name='gundugadu' timestamp='1352826984' post='1302789821']
happy diwali mamals
[/quote]
mamals endi vayya gundu? [img]http://i56.tinypic.com/2w2r5gm.jpg[/img]

[deals2buy]

[b] Rename Columnname or Tablename[/b]


Very basic scenario...might be helpful to freshers

http://www.youtube.com/watch?v=5xviNDISwis


[color=blue]USE [/color][color=black]tempdb
GO[/color]
[color=blue]CREATE TABLE [/color][color=black]TestTable [/color][color=gray]([/color][color=black]ID [/color][color=blue]INT[/color][color=gray], [/color][color=black]OldName [/color][color=blue]VARCHAR[/color][color=gray]([/color][color=black]20[/color][color=gray]))[/color]
[color=black]GO[/color]
[color=blue]INSERT INTO [/color][color=black]TestTable[/color]
[color=blue]VALUES [/color][color=gray]([/color][color=black]1[/color][color=gray], [/color][color=red]'First'[/color][color=gray])[/color]
[color=black]GO[/color]
[color=green]-- Check the Tabledata[/color]
[color=blue]SELECT [/color][color=gray]*[/color]
[color=blue]FROM [/color][color=black]TestTable
GO[/color]
[color=green]-- Rename the ColumnName[/color]
[color=darkred]sp_RENAME [/color][color=red]'TestTable.OldName'[/color][color=gray], [/color][color=red]'NewName'[/color][color=gray], [/color][color=red]'Column'[/color]
[color=black]GO[/color]
[color=green]-- Check the Tabledata[/color]
[color=blue]SELECT [/color][color=gray]*[/color]
[color=blue]FROM [/color][color=black]TestTable
GO[/color]
[color=green]-- Rename the TableName[/color]
[color=darkred]sp_RENAME [/color][color=red]'TestTable'[/color][color=gray], [/color][color=red]'NewTable'[/color]
[color=black]GO[/color]
[color=green]-- Check the Tabledata - Error[/color]
[color=blue]SELECT [/color][color=gray]*[/color]
[color=blue]FROM [/color][color=black]TestTable
GO[/color]
[color=green]-- Check the Tabledata - New[/color]
[color=blue]SELECT [/color][color=gray]*[/color]
[color=blue]FROM [/color][color=black]NewTable
GO[/color]
[color=green]-- Cleanup[/color]
[color=blue]DROP TABLE [/color][color=black]NewTable
GO[/color]

[gundugadu]

[quote name='deals2buy' timestamp='1352829035' post='1302790007']
mamals endi vayya gundu? [img]http://i56.tinypic.com/2w2r5gm.jpg[/img]
[/quote]

trnslations aaapavayya engalipeesuloki
mama lu annanu

[deals2buy]

[quote name='gundugadu' timestamp='1352903195' post='1302794332']
trnslations aaapavayya engalipeesuloki
mama lu annanu
[/quote]
[img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img]

[Desamudhuru]

First day in office lappy inka evaledu setup chestunaru. Kali ga kurcholeka AFDB open chesi kurchuna.

[Darling999]

saduukoo manchigaa

[mtkr]

[quote name='Desamudhuru' timestamp='1352911798' post='1302795109']
First day in office lappy inka evaledu setup chestunaru. Kali ga kurcholeka AFDB open chesi kurchuna.
[/quote]
ATB

[Desamudhuru]

[quote name='DARLING...' timestamp='1352911981' post='1302795127']
saduukoo manchigaa
[/quote]
Maximum kastha padatha baaa...


[quote name='mtkr' timestamp='1352912890' post='1302795227']
ATB
[/quote]

Thank you very much baa..

[deals2buy]

[quote name='Desamudhuru' timestamp='1352911798' post='1302795109']
First day in office lappy inka evaledu setup chestunaru. Kali ga kurcholeka AFDB open chesi kurchuna.
[/quote]


Previous works chudu...ela implement chesaro chudu.,..daanni follow ayipo.. ATB

[Desamudhuru]

[quote name='deals2buy' timestamp='1352917733' post='1302795707']


Previous works chudu...ela implement chesaro chudu.,..daanni follow ayipo.. ATB
[/quote]
Edi SQL 7.0 mida work cheyali baa... Chudali anni DTS mida vunatu vundi.