SQL SERVER – Find the Parameters of a Compiled T-SQL Procedure

Recently, One of my friend Ria Rajan had a Question, The Question is can we find the Parameters of a compiled procedure? I had built a query for her to get the details; I think it will help all of us, mostly developers.

--Creating a sample procedure
 
CREATE PROC Usp_test (@id INT)
AS
  BEGIN
      SELECT @id;
  END  
 
GO 
 
--Query to get the parameters of the sample Procedure
 
SELECT p.name,
       t.name,
       p.max_length
FROM   sys.all_parameters p
       INNER JOIN sys.types t
         ON p.user_type_id = t.user_type_id
WHERE  p.object_id = Object_id('usp_test')  -- Procedure Name

Result:

AGILE- CHALLENGES IN AGILE DATABASE DESIGN

Database Design:

As you all know, database design is a very basic need in a software development. I heard from lot of people that database programming and design can be handled by anyone who has some little experience in database. I am sure that it is a mistake and a strong mistake.

Why I am saying it is a mistake?

Developing software without a proper database design is similar to building a Multi-storied building with no proper foundation. Multi-storied building will collapse once it is occupied or some floors are added to the existing building. The same scenario will happen here also. I will show how it happens with one simple example.

This is one example I got from the real world scenarios. I am forced to see lot of tables without even a primary key. This is one of the basic things which are explained in the database design normalization rules.

 

Here I am going to explain a small scenario which will hit the Performance.

Let’s look this example:

-- Simple Example of coosing DataTypes
CREATE TABLE test4u
  (
     id  UNIQUEIDENTIFIER,
     id2 NVARCHAR(MAX)
  )
 
INSERT INTO test4u
SELECT Newid(),
       Newid()
 
SELECT *,
       Datalength(id)  AS [No of bytes Used for ID],
       Datalength(id2) AS [No of bytes Used for ID2]
FROM   test4u

Look at this scenario,

We are inserting two GuiD values in two columns ID and ID2 and their data types are uniqueidentifier and nvarchar(max). Here I am displaying the no of bytes used to store the data. For ID Column we used unique identifier which uses less bytes than ID2. So storing a GUID value in uniqueidentifier is most recommended method. This will really improve the performance due to the less Memory Utilization. After database comes into production, we can’t change the DB structure since it will affect DB and Application codes.

This is only a very simple example which helps others to think about the database in a Performance point of view. A lot of things will improve the performance. So proper database design is a  very basic need in every project.

 

What is agile methodology?

One line difference for Agile and Waterfall model:

When the client is not clear with requirements we can go for agile methodology.When the client is clear with his requirements we can go for waterfall model.

Developing a database in agile environment:

Here there is a challenge for the development DBA’s. I will explain with a small scenario, which I understood from the internet and books I learned. Some of the links I am posting here. Thanks to john Brady an Oracle database specialist (20 years of Experience), who explains about the agile database development in his blog databaseperformance.blogspot.com.

Requirements:

  • The team should contain Database Administrators/Architects/Developers who has knowledge of agile database design techniques.
  • Application team should have a thorough knowledge of what they are going to do in each iteration.
  • Programmers, Team leads and PM should  work closely with DBA’s
  • DBA’s should know through knowledge of Database performance Tuning.

Implementation:

  • DBA’s should design Database as if database will change in Future rather than we are not clear with the requirements. The Question is how DBA’s will think like that?
  • Look at this simple example,  you will be able to see what happens in each iteration  through database diagrams.

Example requirement:

  • 1st Iteration:  An sms/voice-call (Not Confirmed by Client) should sent to the students and Staffs once they will enter into the college to make them alert that they reached the campus premises.
  • 2nd Iteration: Students parents also should be alerted while entering the college.
  • 3rd Iteration: Finalized that SMS should Sent.

How we will design database with Agile Methodology:

1st Iteration:

Database Diagram for 1st Iteration:

2nd Iteration:

Database Diagram for 2nd Iteration:

3rd Iteration:

Database Diagram for 3rd Iteration:

References: http://www.agiledata.org/essays/bestPractices.html

References: http://databaseperformance.blogspot.com/2009/10/challenge-of-agile-database-design.html

SQL SERVER – MERGE STATEMENT – SIMPLE EXAMPLE

Merge statement is a very powerful feature in T-SQL. I already know merge statement before, but when I worked with DIMITRI FURMAN (Microsoft SME) he usually use the MERGE Statement in his code. This helps me to know the scalability of the MERGE statement.

I will explain the MERGE Statement with a small scenario.

Consider a bank which has some existing loan options. The details are,

ID LoanName InterestRate
1 CarLoan 12.50
2 BikeLoan 10.50
3 HouseLoan 8.00
4 GoldLoan 12.00
6 FlatLoan 15.00

Now the bank has planned to change the interest rate of the existing loans and to include the some new loans. They also planned to stop some existing loans. Their new loan and their rates are

ID LoanName InterestRate
1 CarLoan 10.00
2 BikeLoan 8.00
3 HouseLoan 7.00
4 GoldLoan 12.00
5 AgricultureLoan 5.00

 

After Implementing the new loan structure, the loan details are ,

 ID LoanName InterestRate
1 CarLoan 10.00
2 BikeLoan 8.00
3 HouseLoan 7.00
4 GoldLoan 12.00
5 AgricultureLoan 5.00

 

I think you are clear about the scenario. Here we need to so some INSERT, UPDATE, and DELETE operation when we come to T-SQL. The Operations are ,

  1. Inserting the New loan into the existing table.
  2. Updating the new Loan interest rates to existing table.
  3. Deleting the dropped loan from the existing table.

Can we do this operation in a single step? Rather than doing it separately? The answer is yes.We can achieve this operation in a single pass with the help of Merge statement.Merge statement will Improve the performance.

I am going to Implement the business scenario with the help of  T-SQL.

 

--  Creating the table to hold the existing Bank Loan deatils
 
Create Table BankLoan
( ID int Primary key,
LoanName varchar(50),
InterestRate decimal(5,2)
)
 
--Inserting the data with existing Bank Loan deatils
 
Insert into BankLoan
Select 1,'CarLoan',12.5
union all
Select 2,'BikeLoan',10.5
union all
select 3,'HouseLoan',8
union all
select 4,'GoldLoan', 12
union all
select 6,'FlatLoan',15
 
--  Creating the table to hold the revised Bank Loan deatils
 
Create Table BankLoan_Revised
( ID int Primary key,
LoanName varchar(50),
InterestRate decimal(5,2)
)
 
--Inserting the data with revised Bank Loan deatils
 
Insert into BankLoan_Revised
Select 1,'CarLoan',10
union all
Select 2,'BikeLoan',8
union all
select 3,'HouseLoan',7
union all
select 4,'GoldLoan', 12
union all
select 5,'AgricultureLoan',5
 
--using Merge statement to  Implementing Insertion,updation,
--Deletion in a single pass
 
Merge BankLoan as TARGET
USING BankLoan_Revised as SOURCE
ON TARGET.ID = SOURCE.ID --joining condition
When MATCHED AND TARGET.LOANNAME = SOURCE.LOANNAME
THEN
UPDATE SET TARGET.InterestRate = SOURCE.InterestRate --updating target table
WHEN NOT MATCHED BY TARGET
THEN
INSERT (ID,LOANNAME,INTERESTRATE) --Inserting data if no record in the target
VALUES (SOURCE.ID,SOURCE.LOANNAME,SOURCE.INTERESTRATE)
When Not matched by source
Then
Delete -- Deleting data if no data is matched.
-- Displaying which action is performed and which records are affected
Output $action,
Deleted.ID as DeletedID,
Deleted.LoanName as DeletedLoanName,
Deleted.Interestrate as DeletedInterestrate,
getdate() ;
 
GO
Select * from BankLoan;

Result :

Select * from BankLoan;

Result of output clause: