Sql Server: Local temporary table behavior in nested stored procedure with example

Local temporary table behavior in nested stored procedure with example

local temporary table scope remains only inside the stored procedure, in which it is created. Any inner stored procedure can access the local temporary table created in outer procedure. But any outer stored procedure can not access the local temporary table created in inner procedure.

Error you may get: Msg 208, Level 16, State 0, Procedure procedure1, Line 6
Invalid object name '#temp1'.

Scenario 1: when one procedure calls another procedure and temp table is created in outer procedure procedure1 and accessed by inner procedure procedure2	Scenario 2: when one procedure calls another procedure and temp table is created in inner procedure procedure2 and accessed by outer procedure procedure1
create proc procedure1 as begin create table #temp1(name varchar(30),age int) insert into #temp1 select 'ram',20 union all select 'shyam',23 union all select 'anjum',28 exec procedure2 end GO; create proc procedure2 as begin select * from #temp1 end GO;	create proc procedure2 as begin create table #temp1(name varchar(30),age int) insert into #temp1 select 'ram',20 union all select 'shyam',23 union all select 'anjum',28 end GO; create proc procedure1 as begin exec procedure2 select * from #temp1 end GO;
As local temporary table scope is inside the stored procedure, in which it is created. Procedure2 is nested inside procedure1 , thus procedure2 has access to temp table #temp1.	As local temporary table scope is only inside the stored procedure in which it is created. Temp table #temp1 is created in procedure2, which is called by procedure1, thus any procedure inside procedure2 can access that table, but when procedure 1 tries to access the table #temp1 , which is an outer procedure, doesn’t find the table #temp1

Please provide your feedback for the post, if you find this post useful.

Sql Server: Difference between Row_Number, Rank, Dense_Rank

Difference between Row_Number, Rank, Dense_Rank

Syntax and use:

Row_Number 

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

ROW_NUMBER ( )     OVER ([<partition_by_clause>] <order_by_clause>)

Rank 
Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

RANK ( )    OVER ([< partition_by_clause >] < order_by_clause >)

Dense_Rank

 Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

DENSE_RANK ( )    OVER ([<partition_by_clause> ] < order_by_clause > )

NTILE 

Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

NTILE (integer_expression) OVER ([<partition_by_clause>] < order_by_clause >)

Where

<partition_by_clause>

Divides the result set produced by the From clause into partitions to which the Row_Number/ Rank/ Dense_Rank/ Ntile function is applied.

<order_by_clause>

Determines the order in which the Row_Number/ Rank/ Dense_Rank/ Ntile values are applied to the rows in a partition. 

We will apply these function on the below customer product table CustProd.

name	Product
cust1	decoder
cust2	cable
cust1	cable
cust2	package
cust3	decoder
cust3	cable

Please see the below snapshot for understanding of these function through example

With partition by product and order by name,

When we use partition by product, then it divides the result on the basis of product, as there are three distinct products then there will be 3 partitions.

After partition, order by name is used, that means, in the partitions Row Number, Rank or Dense Rank will be assigned as per the order of name. Here in the below result we see that rank ,row number and dense rank, all are having same value, It’s because in each partition there are distinct name given, if name would have been repeated for the same product then those records will have same rank and dense rank, but row number would have been same as shown below.

When used order by product instead of name , then we see in the below result that, the Rank and dense Rank were 1, Because we did partition of result by product , that means there will be common product in each partition , and rank and dense rank will also be same for same product.

Please provide your feedback for the post, if you find this post useful.

Sql Server : Convert rows into columns

Convert rows into columns

This scenario can be seen as an example for converting rows into columns.

There was list of customer and product in table CustProd.

name	Product
cust1	decoder
cust2	cable
cust1	cable
cust2	package
cust3	decoder
cust3	cable

The result required should be in such format that one could see the products by customer, i.e.; in one row it should contain customer name and all the products it’s having. Say if max three products are there then, it should look like below

name	Product1	Product2	Product3
cust1	cable	decoder	NULL
cust2	cable	NULL	package
cust3	cable	decoder	NULL

For getting such result, we can use below query

Or else

Please provide your feedback for the post, if you find this post useful.

SSIS: SCD-Slowly Changing Dimension

SCD-Slowly Changing Dimension

In this post I will try to include everything about SCD.

What is SCD?

SCD is Slowly Changing Dimension. As the name suggests, a dimension which changes slowly. For Example, say there is a table Employee, which stores information regarding employee as below:

BusinessEntityID, NationalIDNumber, First_Name, last_Name LoginID, OrganizationNode

OrganizationLevel, JobTitle, BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag

CurrentFlag, ModifiedDate

In this Employee table, the data for an employee doesn't change very often, but yes we can’t say that the changes won’t be there. The changes, which may happen, are

·         Mistakenly spelling of First_Name is stored incorrect.

·         The employee gets married and marital status changes.

·         Last_Name changes.

·         The employee gets promotion and job designation changes and organization level changes.

·         The columns which doesn't change except if we assume that no mistake happens while data entry are HireDate, Gender, NationalIDNumber

The changes discussed don’t happen frequently, but may happen after certain time.

SCD supports four types of changes: 
changing attribute, historical attribute, fixed attribute, and inferred member.

Type 1 (changing attribute): When the changes in any attribute or column overwrites the existing records.

For example; as discussed first name of employee is misspelled and wrong spelling is stored in first name of that employee. For making the first name correct, we don’t need to add one more record for the same employee, so we can overwrite the first name. SCD which does this kind of changes comes into type 1 category. This SCD transformation directs these rows to an output named Changing Attributes Updates Output.

Emp ID	First Name	Last Name
1	Rajan	Gupta
1	Ranjan	Gupta

This SCD transformation directs these rows to an output named Changing Attributes Updates Output.

Type 2 (historical attribute): when we need to maintain the history of records, whenever some particular column value changes.

For example the employee gets promotion, designation changes and organization level changes. In such case we need to maintain the history of the employee, that with which designation he joined, and when his designation and organizational level changes.

For these kinds of changes, there will be multiple records for the same employee with different designation. Then to indentify the current records, we can either add a column as current flag, which will be ‘y’ for the current or latest records, Or else we can add two column as start date and end date (expiry date), through which we can maintain history of employees records. This SCD directs these rows to two outputs: Historical Attribute Inserts Output and New Output.

EmpID	FirstName	DEsignation	StartDate	EndDate	Current
1	Ranjan	Graduate Engineer	20-01-2010	25-01-2011	N
1	Ranjan	Analyst Programmer	25-01-2011	25-01-2012	N
1	Ranjan	Business Analyst	25-01-2012	1-01-2099	Y

Fixed attribute: when the attribute must not change.

For example HireDate, Gender, NationalIDNumber should never change. So whenever changes will occur in these columns value then either it should throw error or the changes can be saved in some other destination. But changes should not be applied in the columns.

This SCD transformation detects changes and directs the rows with changes to an output named Fixed Attribute Output.

Inferred member:  are those records of the dimension, which are found missing during fact load. 

For example, say there is a fact table which contains employee and department information. While generating the fact table from employee_stg table and department_stg table , sometimes happenes that employee_stg contains some departments name which has no records in department table , and during fact table generation those records are found missing from department table, these kind of member of dimension department are called inferred member. It’s like ‘Fact arriving earlier than dimensions’.

 This SCD transformation directs these rows to an output named Inferred Member Updates. When data for the inferred member is loaded, you can update the existing record rather than create a new one.

Cllick For Implementation of SCD type 1 step by step
For Implementation of SCD type 2 step by step
For Alternate methods of SCD 

Please provide your feedback for the post, if you find this post useful.

Sql Server: Delete all the objects from the database which are created by user, and not in template database.

Delete all the objects from the database which are created by user, and not in template database.

This kind of scenario exists generally in dev environment. When we have one template database and our db in dev environment should contain only objects which are present in template database. But every day, some db developer works on original database and creates some objects, which he forgets to delete. In such case every day, this operation will be performed and objects created by the developer for their experiment will be deleted.

In the below script AdventureWorks2008 is my template database and sampleadventure is database in dev environment. So sampleadventure should only contain objects present in AdventureWorks2008 database.

I have used cursor for this. Collate is optional, try without collate. I have used collate because the collation was different for both database.

DECLARE @name nVARCHAR(255) ,@object_id int ,@type nVARCHAR(10), @prefix nVARCHAR(255) , @sql nVARCHAR(255)

DECLARE curs CURSOR FOR

   SELECT o.object_id as objectid,o.name as name,o.type as type

    FROM sampleadventure.sys.objects  o

    WHERE o.NAME   NOT IN (SELECT name collate Latin1_General_CI_AS_KS_WS                        FROM  AdventureWorks2008.sys.objects)

    and

    o.type IN ('U', 'P', 'FN', 'IF', 'TF', 'V', 'TR')          

    ORDER BY name

OPEN curs

FETCH NEXT FROM curs INTO @object_id, @name, @type

WHILE @@FETCH_STATUS = 0

BEGIN

  

    SET @prefix = CASE @type 

        WHEN 'U' THEN 'DROP TABLE'

        WHEN 'P' THEN 'DROP PROCEDURE'

        WHEN 'FN' THEN 'DROP FUNCTION'

        WHEN 'IF' THEN 'DROP FUNCTION'

        WHEN 'TF' THEN 'DROP FUNCTION'

        WHEN 'V' THEN 'DROP VIEW'

        WHEN 'TR' THEN 'DROP TRIGGER'

    END

    SET @sql = @prefix + ' ' + @name

    PRINT @sql

    EXEC(@sql)

    FETCH NEXT FROM curs INTO @name, @type

END

CLOSE curs

DEALLOCATE curs

Please provide your feedback for the post, if you find this post useful.