SQL Server -List of Procedure modified / created within specified date

Below is the SQL query which we can use to find out if any procedure has been modified recently in last 7 days or if any procedure has been created in last 7 days

Modified Procedure
use Northwind
select name
from sys.objects
where type='P' and DATEDIFF(D,modify_date,GETDATE())<7

Below is the result you would see

Created Procedure
select name,create_date
from sys.objects

where type='P' and DATEDIFF(d,create_date,GETDATE())<7


Hope this small tip would be beneficial for you in your coding. Till then enjoy !!

SQL - CrossTab Queries using PIVOT

Today, we would be going through CrossTab queries using the PIVOT function available in SQL.
So let's start....and consider the below scenario

Below is the design of the table

Below is the data present in the table

Now, we want to get data in the manner so that we can see how much money Employee has spent on each travel medium. So, we would be making use of PIVOT here


select EmployeeName,[Train] as Train,[Bus] as Bus
from
(
select EmployeeName,TravelMedium,AmountSpent
from EmployeeExpenses)  ee
PIVOT
(
SUM(AmountSpent)
FOR TravelMedium IN ([Train],[Bus])
) as pvt





Below is the result:

Hope so you get an overview of PIVOT with this.

SQL Query: Removing Zero's

Today, we would consider a scenario of removing ZERO's from left side present in the column for one SQL Table.

Consider we are having following data in the table:


Select * from
[dbo].[RemoveZero]

Result:

Column1
0001
000100
100100
000 0001
00.001
01.001

and following is the result we are looking for:

Column1
1
100
100100
1
.001
1.001

So, how to do this? 

We can achieve the above result by using REPLACE and TRIM functionality

Below is the query which would produce the above result:

Select replace(ltrim(replace(numcol,'0',' ')),' ' ,'0') from 

[dbo].[RemoveZero]

So ,if you see:

a) We are replacing '0' with space by using Replace function

b) Then we are using LTRIM function as it would only Left trim the spaces from Left side

c) Then we are again replacing the spaces with Zero's , so this would bring back the zero anywhere already present.

Hope you would have liked this scenario.

DISTINCT Keyword

Today, we would be discussing about the DISTINCT keyword. Often we use distinct in our SQL queries to get the distinct records.

Consider the below data present in Employee table

Employee Name	Salary
Varun Khanna	50000
Garima Khanna	60000
Garima Khanna	70000
Varun Khanna	75000

If we execute below query 

SELECT distinct Employee_Name,Salary

  FROM Employees_Salary

Result

Employee Name	Salary
Garima Khanna	60000
Garima Khanna	70000
Varun Khanna	50000
Varun Khanna	75000

Now you must have expected distinct record of each employee; however this is not the case as to how DISTINCT works.

When we apply distinct in above query it doesn't search only for Employee Name; however it looks for distinct records for both Employee Name and Salary. Meaning if there have been duplicate record then it would have been shown only once.

 It is just a tag that you can put after the word SELECT to indicate that you want only distinct combinations of all columns in the result set returned.

Below are the key points

• DISTINCT always operates on all columns in the final result
• DISTINCT is not a function that accepts a column as an argument

We can modify the above query to return us the distinct employee Name.

Query would be 

SELECT distinct Employee_Name,max(Salary) as MaximumSalary

  FROM Employees_Salary

  group by Employee_Name

Result

Employee Name	Salary
Garima Khanna	70000
Varun Khanna	75000

Hope this article gives some basic understanding of DISTINCT keyword.

Difference between Function and Stored Procedure

Today, i am listing down just the basic difference between Function and Stored Procedure often asked in interviews.

	Function	Stored Procedure
1	Function always needs to return the value	Stored Procedure may or may not return the value
2	Function can be called from within the Stored Procedure	Stored Procedure cannot be called from Function
3	Function cannot have any DML statement i.e. it cannot be used for Data Manipulation in Tables	Stored Procedure can have DML statements i.e. it can be used to perform Data Manipulation in Tables
4	Function cannot have an output parameter	Stored Procedure may or maynot have an output parameter defined
5	Function can be called directly in Select statement	Stored Procedure can be called using EXEC or EXECUTE keyword

Hope this helps !!

SQL Server Function - SP_RENAME and NEWID

Today, we will have an overview of two SQL Server functions

• SP_RENAME
• NEWID

SP_RENAME is used to rename table name or rename any column of the table.
NEWID is used to extract random records from table.

Renaming of Column in Table

Syntax
sp_RENAME 'Tablename.ColumnName','NewColumnName','Column'

I had a table named as Employee_Salary in my SQL Server 2012 under Northwind database. It has four columns ID (Primary Key), Employee_Name,Salary and Sal_Month.
I want to rename column named as 'Employee_Name' to 'Employees_Name'

So, below is the command that needs to be executed

sp_RENAME 'Employee_Salary.Employee_Name','Employees_Name','Column'

Once done, you will see that column name would be updated.

Renaming of Table

We can rename the table with sp_RENAME function provided in SQL Server.
Syntax
sp_RENAME 'TableName' , 'TableNewName'

Below is the command executed

sp_RENAME 'Employee_Salary','Employees_Salary'

NEWID

As told, NEWID() is used to extract random records from the table.
We had table named as Employees_Salary containing employees salary for all the months. Below are the records present in the table

Below is the query to extract random records


select top 2 * from Employees_Salary order by NEWID()

Below is the data extracted when executed the query

If we execute the above query again below is the data returned

So, you have seen every time we execute the same query we are getting different records from table.

Hope with this article of mine, you have become familiar with very little but important functions of SQL Server.

SQL Function - ROW_NUMBER() and PARTITION BY

I recently came across one of new functions of SQL - ROW_NUMBER and PARTITION BY. So, how did i had encounter with these function. Let me tell you the scenario

Scenario

I had a Employee table containing the columns Employee Name, Total Salary credited and for which month salary was credited. I need to get the last paid salary of each employee. Employee table had all the salary records of the employee who ever worked with the company. This means that table had records of those employee also who had already left the company and who are presently working in the company.

Below is the table design:

Below is the data in the table

Below is the result we are expecting 

So, what we have to do to get this result. SQL  has function named as ROW_NUMBER() which returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Below is the syntax:

ROW_NUMBER ( ) 
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

Below is the query to get our expected result:

With summary as
(
select Employee_Name,Salary,Sal_Month,ROW_NUMBER() over (Partition by Employee_Name order by Sal_month desc) as rk
from [Employee_Salary]
)

select * from summary
where rk=1

I hope you would find many scenarios where you can use this function.

Get back to me in case of any queries / questions

Difference between Delete and Truncate Command

DELETE Command

• Delete Command always delete data from TABLE on basis of WHERE command specified in the SQL Query
• Delete Command doesn't resets the identity coulmn of the Table
• Delete command can be rolled back
• Delete comand is DML(Data Manipulation Language) statement.
• Delete command is slower as compared to Truncate command
• Delete command can initiate the TRIGGER

TRUNCATE Command

• Truncate command deletes entire table
• Truncate command resets the identity column of the table
• Truncate command cannot be rolled back
• Truncate command is DDL(Date Definition Language) statement.
• Truncate command is faster as compared to Delete command
• Truncate command cannot initiate the TRIGGER