SQL

RESTORE DATABASE ---

RESTORE DATABASE [Monitoring_Agency]
FROM DISK = N'D:\MonitoringOrigianl\Monitoring_Agency.bak'
WITH FILE = 1,
MOVE N'Monitoring_Agency'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Monitoring_Agency.mdf',
MOVE N'Monitoring_Agency_Log'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Monitoring_Agency.ldf',
NOUNLOAD, REPLACE, STATS = 10

GO

----------------------------------------------------------------------------------

 Delete duplicate rows in sql

Here is the SQL query that does the job. PARTITION BY divides the query result set into partitions.

WITH EmployeesCTE AS

(

   SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) ASRowNumber

   FROM Employees

)

DELETE FROM EmployeesCTE WHERE RowNumber > 1

Result-

---------------------------------------------------

U can use also this query

delete from where rowid not in 

                   ( select min(rowid) 

                     from exp group by column1..,column2,...column3..);

---------------------------------------------------

Transform rows into columns in sql server

Using PIVOT operator we can very easily transform rows to columns

Select Country, City1, City2, City3

From

(

  Select Country, City,

    'City'+

      cast(row_number() over(partition by Country order by Country)

             as varchar(10)) ColumnSequence

  from Countries

) Temp

pivot

(

  max(City)

  for ColumnSequence in (City1, City2, City3)

) Piv

Result-

SQL Query to find department with highest number of employees.

SELECT TOP 1 DepartmentName
FROM Employees
JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID
GROUP BY DepartmentNameORDER BY COUNT(*) DESC

* SQL query to find rows that contain only numerical data-

 Select Value from TestTable Where ISNUMERIC(Value) = 1

ISNUMERIC function returns 1 when the input expression evaluates to a valid numeric data type, otherwise it returns 0

Result

 

* What Are Stored Procedures?

Have you ever written SQL statements, like inserts, selects, and updates? Then you have already written most of a stored procedure. A stored procedure is an already written SQL statement that is saved in the database. If you find yourself using the same query over and over again, it would make sense to put it into a stored procedure. When you put this SQL statement in a stored procedure, you can then run the stored procedure from the database’s command environment (I am a SQL Server kind of guy, and run stored procedures from the Query Analyzer), using the exec command.


An example is:
exec usp_displayallusers
The name of the stored procedure is “usp_displayallusers”, and “exec” tells SQL Server to execute the code in the stored procedure. (Note: “usp_” in front of the stored procedure name is used to designate this stored procedure as a user-created stored procedure.) The code inside the stored procedure can be something as simple as:


SELECT * FROM USERLIST


This “select” statement will return all data in the USERLIST table. You may think, skeptically, that stored procedures aren’t terribly useful. Just save the query and run it when you need to. Too easy, right?


Well, there is more to the story. Many queries get more complex than “select * from . . .”  Also, you may want to call the stored procedure from an application, such as an ASP page, Visual Basic application, or a Java servlet. With a stored procedure, you can store all the logic in the database, and use a simple command to call the stored procedure. Later, if you decide to migrate from ASP to J2EE, you only need to change the application layer, which will be significantly easier. Much of the business logic will remain in the database.


Enough background—let’s write some stored procedures.


Getting Started with Stored Procedures


What do I need to get started? I have heard that question often. To begin writing stored procedures, the following are essential:


A database management system.
A database built inside the database management system (see the end of this article for a sample).
A text editor, such as Notepad or Query Analyzer.


Items 1 and 2 are absolutely essential. You can’t write stored procedures without a database. They would be useless. Sometimes, I write my procedures in Notepad (or another text editor), and copy them into the New Stored Procedure window in SQL Server. The New Stored Procedure window is a bit small, and in Notepad I can spread things out a bit (you’ll see later).


Next, you will have to decide what you want your stored procedure to do. It can be tempting to just dive right into the task at hand, but it is always prudent to sketch out some ideas first. Some considerations should be:
Do you want to view data in the database (SELECT), insert new records (INSERT INTO), or do I want to change an existing record (UPDATE)?
With which tables will you have to work? Does it make sense to create a VIEW first?
How often will this procedure actually be used?
Once you have struggled with these questions (something of an exaggeration, I guess), you will be ready to start coding!


Note: Throughout this article, I will focus on stored procedures for SQL Server. You can apply the same principles to other database management systems, but I will make clear references to working in a SQL Server environment.


Continues…
Rename Column in Sql 2008 R2
ALTER TABLE table_name RENAME COLUMN old_name to new_name;