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.

Tip: Swapping two variables without using third variable

Just a small trick to swap two variables without using the third variable.

Here we go...

Consider the following example

1) int i =10
2) int j=15
3) i=i+j which is 10+15 making it 25. Now, i=25
4) j=i-j which is 25-15 making j=10
5) i=i-j which is 25-10 making i=15

So, you see that following are the values of both i & j after performing  above code logic
i=15 and j=10

Hope this trick helps in any of the scenario

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

Mobile Application in MVC

Overview

With Visual Studio 2012, there has been various new enhancements in ASP.NET and MVC. I have blogged about certain new features introduced in ASP.NET 4.5. Today, we would get overview of how we can create mobile application in MVC.

So let's start...

1) Open the Visual Studio 2012.I am using Visual C# template. Click on Web and select MVC4 application. Below is the screenshot

2)  Once click OK, you will see various MVC templates available. As we want to develop Mobile application, we would select 'Mobile Application' template. By default, View engine selected should be 'Razor'. Below is the screenshot

3) Once done it would create visual studio application. You can run the application without making any changes. Since, it is mobile application, it doesn't means that it would not run in our default browser (Internet Explorer, Google Chrome etc). Hence, we can run the application in browser too.

Below is the screenshot for Internet Explorer

However, we can run the same application in Mobile Phone Emulator. Below is the screenshot when running in Windows Phone Emulator

About Screen

Contact Screen

Log In Screen

Hope with this article of mine you will get overview of nee mobile template introduced in MVC.

Do get back to me in case of any questions.