Category Archives: SQL Server

Analytic Functions (Transact-SQL)- LAG

SQL Server 2012 has started to support Analytic functions. These functions calculate an aggregate value based on Group by clause or Partition By (Using OVER clause). Analytic functions give more flaxibility to OVER clause. We can use analytic functions to calculate running total, first value, last value, lead value or lag value. Below is the list of all 8 available analytic functions.

CUME_DIST
LEAD
FIRST_VALUE
PERCENTILE_CONT
LAG
PERCENTILE_DISC
LAST_VALUE
PERCENT_RANK

In this post we will check the LAG function. To demonstrate this let us create a table variable:

DECLARE @ProductPrice TABLE
(
Value_ID INT,
Product_Name VARCHAR(10),
Month_Name VARCHAR(10),
Current_Value INT
)

INSERT INTO @ProductPrice (Value_ID,Product_Name,Month_Name,Current_Value)
SELECT 1,’Bike_A’,'Jan’,2000 UNION ALL
SELECT 2,’Bike_A’,'Feb’,2100 UNION ALL
SELECT 3,’Bike_A’,'Mar’,1990 UNION ALL
SELECT 4,’Car_B’,'Jan’,10000 UNION ALL
SELECT 5,’Car_B’,'Feb’,10000 UNION ALL
SELECT 6,’Car_C’,'Jan’,20000 UNION ALL
SELECT 7,’Car_C’,'Jan’,20100

SELECT * FROM @ProductPrice
Select_Result

 

 

 

 

 
What LAG function does? With the help of LAG function we can get the data of previous row without using self join. It means when we want to compare the result of previous value and current value of rows then we can use this function.
Syntax of LAG function is defined in MSDN as:

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

Now, try to understand this with above table variable, run below T-SQL statement:

SELECT Value_ID, Product_Name,Month_Name,Current_Value,
LAG(Current_Value, 1,0) OVER (PARTITION BY Product_Name ORDER BY Product_Name) AS Previous_Value
FROM @ProductPrice
Select_Lag

 

 

 

 

Now, we will try to achieve the same result without using LAG function. There are several ways to do the same, but in this post we will use Self Join.

SELECT
m.Value_ID,
m.Product_Name,
m.Month_Name,
m.Current_Value,
ISNULL(MAX(l.Current_Value),0) AS Previous_Value
FROM @ProductPrice AS m
LEFT JOIN @ProductPrice AS l
ON m.Product_Name=l.Product_Name AND l.Value_ID < m.Value_ID
GROUP BY m.Value_ID,
m.Product_Name,
m.Month_Name,m.Current_Value

Select_Self Join

 

SQL Server Undocumented Stored Procedures

In all versions of SQL Server have some stored functions which are not documented in MSDN. Microsoft do not document them because they want to use them for their internal use only. Also, Microsoft strongly recommend that not to use these stored procedure in Production environment reason being any time they can make change in the definition of such stored procedures and can also remove them in future.
But still in some of cases we find that these undocumented stored procedures help us a lot. in this post we will learn only 2 of undocumented stored procedures:
sp_MSforeachtable and sp_MSforeachdb.

sp_MSforeachdb: This will loop through each database in SQL Server instance. Instead of use cursor, this helps us a lot when we want to execute something for each database for SQL Server instance.
MSforeachdb
Below are some example of use of this:
1. Print all the database names in a SQL Server Instance
EXEC sp_MSforeachdb ‘USE ?; PRINT DB_NAME()’
2. Print all the tables in all the databases of a SQL Server Instance
EXEC sp_MSforeachdb ‘USE ? SELECT DB_NAME() + ”.” + OBJECT_NAME(object_Id) FROM sys.tables’
3. Display the size of all databases in a SQL Server instance
EXEC sp_MSforeachdb ‘USE ?; EXEC sp_spaceused’

You can also use this stored procedure in taking back up all databases in a single go.

sp_MSforeachtable: Similar to above this will loop through all tables in database.
MSforeachtable
Some common uses are as below:
1. Display the size of all tables in a database:
USE AdventureWorks2012
EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?’”
2. Display number of rows in all tables in a database:
USE AdventureWorks2012
EXEC sp_MSforeachtable ‘SELECT ”?” AS TableName, Count(*) as NumberOfRows FROM ?’
Again, you can also use of this stored procedure for other tasks like to rebuild indexes for each table in database.

How to import Shape files in SQL Server.

 

 

In this post we will discuss how to use import Map (Shape file) in SQL Server. For this we need to download some data and executables. The following two points will give you the complete information about this.

  1. Download the world and India map files from here
  2. Then download the software which will use to import the shape file into SQL Server database from this link.
  3. Now unzipped both of the folders which you have downloaded in above two points.Now follow the instructions as per below image:
  4. Click on Shape2Sql.exe
    Folder_Structure

5. Browse the india_state.shp file from the folder from step 1.Configure the database server and database where you want to import the data of shape file.
6. Enter the table name where you want to import the data, in our case this name is India_State.
7. Finally click on Upload Database to create the table.
8. When you run select statement on India_State table it will show you result as per two images.

UploadShapeFile

 

 

 

 

 

 

 

 

9. When you run select statement on India_State table it will show you result as per two images.
SQLData SQLShape

 

 

 

 

 

 

 

 

 

 

 

 

10. In our next post we will create Map report in SSRS using this data.