How to get Even or Odd or last 'N' Number of records in SQL Server.

/* Get Even or Odd rows number */

In my previous article I discussed about  SQL Server database showing "Recovery In" mode.

EVEN :

select * from(
select PatientId,Row_Number() OVER(ORDER BY PatientID) AS RowNumber  from tblpatient)aa where aa.RowNumber%2=0

ODD :

select * from(
select PatientId,Row_Number() OVER(ORDER BY PatientID) AS RowNumber  from tblpatient)aa where aa.RowNumber%2=1


/* Get last N number of records */

USE AdventureWorks2012
GO
--------------------------------------------------
-- SQL Server 2012
--------------------------------------------------
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET (@PageNumber-1)*@RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
GO
--------------------------------------------------
-- SQL Server 2008 / R2
-- SQL Server 2005
--------------------------------------------------
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM (
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail ) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)
GO
--------------------------------------------------
-- SQL Server 2000
--------------------------------------------------
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM
(
SELECT TOP (@RowsPerPage)
SalesOrderDetailID, SalesOrderID, ProductID
FROM
(
SELECT TOP ((@PageNumber)*@RowsPerPage)
SalesOrderDetailID, SalesOrderID, ProductID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
) AS SOD
ORDER BY SalesOrderDetailID DESC
) AS SOD2
ORDER BY SalesOrderDetailID ASC
GO

SQL Server database showing "Recovery In" mode.

If your database is in recovery mode, run a command similar to the following to bring it online(replace AdventureWorks with the name of your database):

  RESTORE DATABASE AdventureWorks WITH RECOVERY

Check out the previous post :

SQL SERVER Alter table statement with Examples

How to use different Facebook Plugins in Asp Net Page

SQL SERVER Alter table statement with Examples

ALTER TABLE STATEMENT

DESCRIPTION:

This SQL blog explains how to use the SQL ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table .

RECOMMENDED : SQL SERVER Select Statement with Examples

ADD COLUMN IN TABLE

Syntax

To add a column in a table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD column_name column-definition;

Example

Let's look at a SQL ALTER TABLE example that adds a column.

For example:

ALTER TABLE Employees
  ADD Employee_Name varchar(50);

This SQL ALTER TABLE example will add a column called Employee_Name to the Employees table.

ADD MULTIPLE COLUMNS IN TABLE

Syntax

To add multiple columns to an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD (column_1 column-definition,
       column_2 column-definition,
       ...
       column_n column_definition);

Example

Let's look at SQL ALTER TABLE example that adds more than one column.

For example:

ALTER TABLE Employees
  ADD (Employee_Name varchar(50),
       Salary decimal(15,2));

This SQL ALTER TABLE example will add two columns, Employee_Name as a varchar(50) field and Salary as a decimal(15,2) field to the Employees table.

MODIFY COLUMN IN TABLE

Syntax

To modify a column in an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  ALTER COLUMN column_name column_type;

Example

Let's look at an example of how to modify a column called Employee_Name using the ALTER TABLE statement.

ALTER TABLE Employees
  ALTER COLUMN Employee_Name VARCHAR(100) NOT NULL;

MODIFY MULTIPLE COLUMNS IN TABLE

Syntax

To modify multiple columns in an existing table, the SQL ALTER TABLE syntax is

ALTER TABLE table_name
  ALTER COLUMN column_name TYPE column_definition,
  ALTER COLUMN column_name TYPE column_definition,
  ...
;

Example

Let's look at an example that uses the ALTER TABLE statement to modify more than one column. In this example, we will modify two columns called Employee_Name and Salary.

ALTER TABLE Employees
  ALTER COLUMN Employee_Name TYPE NVARCHAR(100),
  ALTER COLUMN Employee_Name SET NOT NULL,
  ALTER COLUMN Salary TYPE VARCHAR(50);

DROP COLUMN IN TABLE

Syntax

To drop a column in an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  DROP COLUMN column_name;

Example

Let's look at an example that drops (delete) a column from a table.

For example:

ALTER TABLE Employees
  DROP COLUMN Employee_Name;

This SQL ALTER TABLE example will drop the column called Employee_Name from the table called Employees.

RENAME COLUMN IN TABLE

Syntax

To rename a column in an existing table, the SQL ALTER TABLE syntax is:

sp_rename 'table_name.old_column', 'new_name', 'COLUMN';

Example

Let's look at an example that renames a column in the Employees table from Employee_Name to EName.

sp_rename 'Employees.Employee_Name', 'EName', 'COLUMN';

RENAME TABLE

Syntax

To rename a table, the SQL ALTER TABLE syntax is:

sp_rename 'table_name', 'new_table_name';

Example

Let's look at an example that renames a table called Employees to the new name tblEmployee.

sp_rename 'Employees', 'tblEmployee';

PREVIOUS BLOG: How to use different Facebook Plugins in Asp Net Page

SQL SERVER Select Statement with Examples

SQL SERVER

SQL Server is a relational database technology developed by Microsoft.

In this tutorial I will try to elaborate with the basics of SQL Server such as how to retrieve and manipulate data. Then we will move to the more advanced topics such as how to create tables, functions, and procedures. 

SQL SERVER: SELECT STATEMENT

This SQL Server tutorial explains how to use the SELECT statement in SQL Server (Transact-SQL) with syntax and examples.

DESCRIPTION

The SQL Server (Transact-SQL) SELECT statement is used to retrieve records from one or more tables in a SQL Server database.

SYNTAX

SELECT expressions
FROM tables
[WHERE conditions];

EXAMPLE

1. SELECT ALL RECORDS FROM SINGLE TABLE

SELECT *
FROM Employees
WHERE Salary> 10000
ORDER BY Employee_ID ASC;

In this SQL Server SELECT statement example, we've used * to signify that we wish to select all fields from the Employee table where the salary is greater than 10000. The result set is sorted by Employee_ID in ascending order.

2. SELECT INdividuAL RECORDS FROM SINGLE TABLE

SELECT Employee_Name,Employee_Salary,Employee_Designation
FROM Employees
WHERE Salary> 10000 and Employee_Designation='Developer'
ORDER BY Employee_ID ASC;