Column Encryption And Decryption In MSSQL

-- Create Master Key
CREATE MASTER KEY ENCRYPTION BY
    PASSWORD ='mandep@123'
GO
  
-- Create Certificate
CREATE CERTIFICATE test
    WITH SUBJECT='mandeep'
GO
 
 
-- Create Symmetric Key
CREATE SYMMETRIC KEY TESTKEY
    WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY CERTIFICATE tEST
GO

CREATE TABLE USERS (UID INT IDENTITY(100,1),UFNAME VARCHAR(100),ULNAME VARCHAR(100),ULOGINDI VARCHAR(50),
UPASSWORD VARBINARY(256))

-- Create a Procedure to Insert Data in Table 

CREATE PROC InsertUSER
  @UFNAME VARCHAR(100),
  @ULNAME VARCHAR(100),
  @ULOGINDI VARCHAR(12),
  @UPASSWORD VARCHAR(20)
AS
BEGIN
-- you must open the key as it is not already
    OPEN SYMMETRIC KEY TESTKEY
        DECRYPTION BY CERTIFICATE TEST;
    
-- Insert statement
    INSERT INTO [USERS]
    (UFNAME, ULNAME, ULOGINDI, UPASSWORD)
    VALUES
    (@UFNAME, @ULNAME, @ULOGINDI,
     EncryptByKey(Key_GUID('TESTKEY'), @UPASSWORD));
     
END;

Predefined SQL Database Roles In Sql Server

SQL roles make your work easier, they allow assigning permissions to a role, or group of users, instead to individual users

Predefined SQL database roles are:

db_owner - members have full access
db_accessadmin - members can manage Windows groups and SQL Server logins
db_datareader - members can read all data
db_datawriter - Members can add, delete, or modify data
db_ddladmin - members can run data definition statements
db_securityadmin - members can modify role membership and manage permissions
db_bckupoperator - members can create backups
db_denydatareader - members cannot see the database data
db_denydatawriter - members cannot change/delete database data

Reserved keywords In Sql Server

Reserved keywords

Avoid using reserved keywords for SQL Server database object names. If you do, make sure you use either quoted identifiers or delimited identifiers

Here are the lists of SQL Server, ODBC and future SQL Server reserved keywords

SQL Reserved Key words List.

ADD	EXTERNAL	PROCEDURE
ALL	FETCH	PUBLIC
ALTER	FILE	RAISERROR
AND	FILLFACTOR	READ
ANY	FOR	READTEXT
AS	FOREIGN	RECONFIGURE
ASC	FREETEXT	REFERENCES
AUTHORIZATION	FREETEXTTABLE	REPLICATION
BACKUP	FROM	RESTORE
BEGIN	FULL	RESTRICT
BETWEEN	FUNCTION	RETURN
BREAK	GOTO	REVERT
BROWSE	GRANT	REVOKE
BULK	GROUP	RIGHT
BY	HAVING	ROLLBACK
CASCADE	HOLDLOCK	ROWCOUNT
CASE	IDENTITY	ROWGUIDCOL
CHECK	IDENTITY_INSERT	RULE
CHECKPOINT	IDENTITYCOL	SAVE
CLOSE	IF	SCHEMA
CLUSTERED	IN	SECURITYAUDIT
COALESCE	INDEX	SELECT
COLLATE	INNER	SEMANTICKEYPHRASETABLE
COLUMN	INSERT	SEMANTICSIMILARITYDETAILSTABLE
COMMIT	INTERSECT	SEMANTICSIMILARITYTABLE
COMPUTE	INTO	SESSION_USER
CONSTRAINT	IS	SET
CONTAINS	JOIN	SETUSER
CONTAINSTABLE	KEY	SHUTDOWN
CONTINUE	KILL	SOME
CONVERT	LEFT	STATISTICS
CREATE	LIKE	SYSTEM_USER
CROSS	LINENO	TABLE
CURRENT	LOAD	TABLESAMPLE
CURRENT_DATE	MERGE	TEXTSIZE
CURRENT_TIME	NATIONAL	THEN
CURRENT_TIMESTAMP	NOCHECK	TO
CURRENT_USER	NONCLUSTERED	TOP
CURSOR	NOT	TRAN
DATABASE	NULL	TRANSACTION
DBCC	NULLIF	TRIGGER
DEALLOCATE	OF	TRUNCATE
DECLARE	OFF	TRY_CONVERT
DEFAULT	OFFSETS	TSEQUAL
DELETE	ON	UNION
DENY	OPEN	UNIQUE
DESC	OPENDATASOURCE	UNPIVOT
DISK	OPENQUERY	UPDATE
DISTINCT	OPENROWSET	UPDATETEXT
DISTRIBUTED	OPENXML	USE
DOUBLE	OPTION	USER
DROP	OR	VALUES
DUMP	ORDER	VARYING
ELSE	OUTER	VIEW
END	OVER	WAITFOR
ERRLVL	PERCENT	WHEN
ESCAPE	PIVOT	WHERE
EXCEPT	PLAN	WHILE
EXEC	PRECISION	WITH
EXECUTE	PRIMARY	WITHIN GROUP
EXISTS	PRINT	WRITETEXT
EXIT	PROC

How to add a timestamp to a backup name

Schedule a SQL Server job to create database backups. Instead of using a script such as:

BACKUP DATABASE [AdventureWorks2012]

TO DISK = N'E:\Test\AdventureWorks.bak' 

Use :

DECLARE @SQLStatement VARCHAR(2000)

SET @SQLStatement= 'E:\Test\AdventureWorks' +_ CONVERT(nvarchar(30), GETDATE(), 110) +'.bak'

BACKUP DATABASE [AdventureWorks2012] TO DISK = @SQLStatement

The variable and CONVERT(nvarchar(30), GETDATE(), 110) allow to add the current date. The backups created are named like below.

AdventureWorks_07-29-2013

AdventureWorks_07-30-2013

AdventureWorks_07-31-2013

View SQL Server Trace

To view a specific SQL trace, use fn_trace_getinfo and specify the ID of the trace

 SELECT *_

FROM ::fn_trace_getinfo(trace_id)

For this option, you would have to know the trace ID

Another option, which I find more user friendly is simply by opening SQL Server Profiler and selecting a specific file trace

Mail From Sqlserver Database Error solution

Minimal permissions to send Database mail

If you encounter the following error when trying to send a database mail

EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'

you have a problem with SQL Server privileges

The easies way to fix this is to make your user a member of the *DatabaseMailUserRole* database role in the msdb database. You can do that in SQL Server Management Studio, or using the following SQL

EXEC msdb.dbo.sp_addrolemember @rolename='DatabaseMailUserRole' ,@membername='<user or role name>'

Note that the DatabaseMailUserRole doesn't exist in the SQL Server Security | Server Role list, just in the msdb database roles

Oracle 12c New Features for Developers

Oracle Database 12c introduces a new multitenant architecture that makes it easy to deploy and manage database clouds. Oracle 12c is a pluggable database environment, where we can plug multiple databases into single database container. All these databases then share same background processes and memory. This helps in reducing the overhead of managing multiple databases.

I have tried to compile some of the important new features of Oracle Database 12c. Below are the top 15 new features of Oracle Database 12c for Oracle Developer & professional.

1. Sequence as Default Value
With Oracle Database 12c, we can directly assign sequence nextval as a default value for a column, So you no longer need to create a trigger to populate the column with the next value of sequence, you just need to declare it with table definition.

Example:

create sequence test_seq start with 1 increment by 1 nocycle;

create table test_tab
(
    id number default test_seq.nextval primary key
);

2. Invisible column:
Oracle Database 12c provides you the Invisible column feature. A Column defined as invisible, will not appear in generic queries (select * from). An Invisible Column need to be explicitly referred to in the SQL statement or condition. Also invisible column must be explicitly referred in INSERT statement to insert the database into invisible columns.

Example:

SQL> create table my_table
  2  (
  3  id number,
  4  name varchar2(100),
  5  email varchar2(100),
  6  password varchar2(100) INVISIBLE
  7  );
  
SQL> ALTER TABLE my_table MODIFY (password visible);  

3. Multiple indexes on the same column
Before Oracle Database 12c, we could not have multiple indexes on a single column. In Oracle Database 12c a column may have multiple indexes but all should be of different types. Like a column may have B-Tree and BitMap Index both. But, only one index will be used at a given time.


4. VARCHAR2 length up to 32767
Form Oracle Database 12c, a varchar2 column can be sized upto 32767, which was earlier 4000. The maximum size of the VARCHAR2, NVARCHAR2, and RAW data types has been increased from 4,000 to 32,767 bytes. Increasing the allotted size for these data types allows users to store more information in character data types before switching to large objects (LOBs).


5. Top-N feature
A Top-N query is used to retrieve the top or bottom N rows from an ordered set. Combining two Top-N queries gives you the ability to page through an ordered set
Example:

SQL> SELECT value
  2  FROM   mytable
  3  ORDER BY value DESC
  4  FETCH FIRST 10 ROWS ONLY;

6. IDENTITY Columns
In Oracle Database 12c, We can define Table columns with SQL keyword IDENTITY which is a American National Standards Institute (ANSI) SQL keyword. Which are auto-incremented at the time of insertion (like in MySQL).
Example:

SQL> create table my_table
  2  (
  3  id number generated as identity,
  4  name varchar2(100),
  5  email varchar2(100),
  6  password varchar2(100) INVISIBLE
  7  );

7. With Clause improvement
In Oracle 12c, we can declare PL/SQL functions in the WITH Clause of a select statement and use it as an ordinary function. Using this construct results in better performance as compared with schema-level functions
Example:

SQL> WITH
  2    FUNCTION f_test(n IN NUMBER) RETURN NUMBER IS
  3    BEGIN
  4      RETURN n+1;
  5    END;
  6  SELECT f_test(1)
  7  FROM   dual
  8  ;

8. Cascade for TRUNCATE and EXCHANGE partition.
With Oracle Database 12c, The TRUNCATE can be executed with CASCADE option which will also delete the child records.


9. Online RENAME/MOVE of Datafiles
Oracle Database 12c has provided a simple way to online renamed or moved data files by simply "ALTER DATABASE MOVE DATAFILE" command. Data files can also be migrated online from ASM to NON-ASM and NON-ASM to ASM easily now.

Examples:

Rename datafile:  
  SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/indx.dbf' TO '/u01/oradata/indx_01.dbf';
Move Datafile:    
  SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/indx.dbf' TO '/u01/oradata/orcl/indx.dbf';
NON-ASM to ASM:   
  SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/indx.dbf' TO '+DISKGROUP_DATA01';

10. Move table partition to different Tablespace online
From Oracle 12c, it become very easy to move Table Partition to different tablespace and does not require complex steps
Example:

  SQL> ALTER TABLE MY_LARGE_TABLE MOVE PARTITION MY_LARGE_TABLE_PART1 TO TABLESPACE USERS_NEW;

11. Temporary Undo
Before Oracle Database 12c, undo records of temporary tables used to be stored in undo tablespace. With the temporary undo feature in Oracle Database 12c, the undo records of temporary tables can now be stored in a temporary table instead of stored in undo tablespace. The main benefits of temporary undo are 1) Low undo tablespace usages 2) less redo data generation. For using this feature Compatibility parameter must be set to 12.0.0 or higher and TEMP_UNDO_ENABLED initialization parameter must be Enabled.


12. DDL logging
By using the ENABLE_DDL_LOGGING initiation parameter in Oracle Database 12c, we can now log the DDL action into xml and log files to capture when the drop or create command was executed and by whom under the $ORACLE_BASE/diag/rdbms/DBNAME/log|ddl location. The parameter can be set at the database or session levels.
Example:

  SQL> ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE;

13. PGA_AGGREGATE_LIMIT parameter
Oracle Database 12c has provided us a way to limit PGA by PGA_AGGREGATE_LIMIT parameter. Before Oracle Database 12c there was no option to limit and control the PGA size. Oracle will automatically abort the session that holds the most untenable PGA memory when PGA limits exceeds the defined value.


14. SQL statement in RMAN
From Oracle Database 12c, we can execute any SQL and PL/SQL commands in RMAN without SQL prefix
Example:

  RMAN> SELECT username,machine FROM v$session;

15. Turning off redo for Data Pump the import
The new TRANSFORM option, DISABLE_ARCHIVE_LOGGING, to the impdp command line causes Oracle Data Pump to disable redo logging when loading data into tables and when creating indexes. This feature provides a great relief when importing large tables, and reduces the excessive redo generation, which results in quicker imports. This attribute applies to tables and indexes.
Example:

  impdp directory=mydir dumpfile=mydmp.dmp logfile=mydmp.log TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

How to enable and configure Filestream in SQL SERVER

Filestream was introduced in Sql Server 2008 for the storage and management of unstructured data.
Follow the below steps to enable this filestream.

To enable filestream through SQL Server configuration manager:

1.Open SQL Server configuration manager.Open SQL Server services
2.Select the instance for which you want to enable Filestream.Right click the instance->properties.
3.In the SQL Server Properties dialog box, click the Filestream tab.
4.Select the Enable Filestream for Transact-SQL access.
5.If you want to read and write Filestream data from Windows, click Enable Filestream for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
6.If remote clients must access the Filestream data that is stored on this share, select Allow remote clients to have streaming access to Filestream data.
7.Click Apply.


Enable Filestream access level server configuration option:
In SQL Server Management Studio

[0 -Disables FILESTREAM,
1 -Enables FILESTREAM for T-SQL,
2 -Enables FILESTREAM for T-SQL and Win32 streaming access]

Syntax:
EXEC sp_configure filestream_access_level,2
RECONFIGURE with override

Create filestream enabled database:

1. We can enable file stream while creating the database  (or) If the database is already created we can enable filestream using alter database.
   

To create file stream enable database you can use below query

CREATE DATABASE DBname
ON
PRIMARY ( NAME = test1,
    FILENAME = ‘c:\data\testdat1.mdf’),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = test3,
    FILENAME = ‘c:\data\test1′)
LOG ON  ( NAME = testlog1,
    FILENAME = ‘c:\data\test1.ldf’)
GO

Enable filestream on existing database:
To enable file stream on existing database you can use alter database  command with  SET FILESTREAM similar to the example below or SSMS 
ALTER DATABASE [DBNAME] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N’Directoryname’ ) WITH NO_WAIT
GO
 
Enable filestream for database using SQL Server 2008 Management Studio:
 
1. Connect to SQL Server Instance using SQL Server Management Studio
2. In the Object Explorer, right click the instance and select Properties.
3. On the left panel click on the Advanced tab, then click on the drop down list of Filestream Access Level and select Full access enabled option.


4. Click Ok to save the changes.

sqlchallenges

A Table DIVISION (divid, divname ) have many units, UNIT (unitid, unitname, divid[fk] ) have many locations, LOCATION ( locid, locname, unitid[fk]) have many criminals, CRIMINAL (criminalid, fname, lname, locid[fk]) Can anyone give me a SQL QUERY . I need 2 fields only one is DIVISION and other is VAL(with values the sum of each criminal in a particular division). Division count ---------------- Div1 5 Div2 10 Answer: SELECT d.divname, count( d.divid ) FROM division d JOIN unit u ON ( d.divid = u.divid ) JOIN location l ON ( l.unitid = u.unitid ) JOIN CRIMINAL c ON ( c.locid = l.locid ) GROUP BY divname

SQL Server consistency check

How can you be sure everything is OK with your Sql server‎ database?

Run a consistency check to check the database objects logical and physical integrity

- DBCC CHECKDB - checks the entire database. Executes CHECKALLOC, CHECKCATALOG and CHECKTABLE for every table and view
- DBCC CHECKALLOC - checks the consistency of disk space allocation for a specific database
- DBCC CHECKCATALOG - checks catalog consistency for a specific online database
- DBCC CHECKTABLE - checks the integrity of all pages and structures in a specific table/indexed view
- DBCC CHECKFILEGROUP - executes CHECKALLOC and CHECKTABLE for every table in the filegroup you specified

Solve Query...

I have One table Like

Table1:
======

ID      Name           Date             Value
--------------------------------------------------
1        naimish        10/4/12          50
2        jugal             12/4/12        150
3        vimal            15/4/12        300
4        mohit            20/4/12        450

Display Output Like Below (using Query Only)

ID      Name            Date              Value          Date_Modified        New_Value
------------------------------------------------------------------------------------------------
1        naimish         10/4/12          50              12/4/12                  100
2        jugal              12/4/12         150             15/4/12                  150
3        vimal             15/4/12         300             20/4/12                  150
4        mohit             20/4/12         450             Null                        Null

Ans:

SELECT t1.id, t1.name,t1.date,t1.value , t2.date Date_Modified , t2.value - t1.value New_Value FROM `temp` t1
join (SELECT id - 1 as id ,name,date,value FROM `temp`) as t2
on (t1.id = t2.id)
union all
select id, name ,date ,  value , null ,null from temp
where id in(select max(id) from temp)

Cache Memory Information

To See What type of information is stored in cache memory

SELECT COUNT(*) AS cached_pages_count,
name AS BaseTableName, IndexName,
IndexTypeDesc
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT s_obj.name, s_obj.index_id,
s_obj.allocation_unit_id, s_obj.OBJECT_ID,
i.name IndexName, i.type_desc IndexTypeDesc
FROM
(
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id ,allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id, allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS s_obj
LEFT JOIN sys.indexes i ON i.index_id = s_obj.index_id
AND i.OBJECT_ID = s_obj.OBJECT_ID ) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id, IndexName, IndexTypeDesc
ORDER BY cached_pages_count DESC;
GO

To clean Buffer Use utility.

DBCC DROPCLEANBUFFERS

 

Query Optimization Tips

Before optimize any query clean buffer first after we can see what's exactly going on

  - DBCC dropcleanbuffer
  - DBCC FreeProcCatch

For See Statistsics Also See In Execution Plan

  - Set Statistics IO ON
  - Set Statistics Time ON

For Optimization We are Concentrate on 3 Most Statistic

1) NO of logical reads
2) CPU Time
3) Query Cost (QC)

Both 1st and 2nd You will Get From Upper Commands
But For QC Use Execution Plan

See and Note All Statistic and Then Apply This Instruction For optimization that Given Below:

1) Limit no of colums return from query
2) Create Primary Key
3) Create index which are using in workhours
4) Limit the no of rows by using top
5) Working with join have index on the column in the join
6) If you use multiple columns in where or order by clause then create compound index
7) If you are trying to fetch unique values use group by instead of distinct
8) Use exists in behalf of in().
9) set nocount on

Query Optimization OR and Union

When we execute query with OR clause,filtering the records from two 
different field. Oracle Database can not use index to filter the that.

1) create Index Idx_empno on emp(empno)

2) create Index Idx_salary on emp(salary)

Select * from emp
where empno = 7319 or salary > 15000

In this query Both the field have an index then also you can see
that it takes to much time because of "or" indexing field not work because of OR

so use Union to overcome this,

Select * from emp where empno = 7319
Union
Select * from emp where salary > 15000

 

Replace word in all table..

--To replace all occurences of 'America' with 'USA' in all Tables of database
EXEC SearchReplace 'America', 'USA'


CREATE PROC SearchReplace
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
      ) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' =  REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END

SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

System Configuration,Files and paths of Database,OS information

1) System Configuration
SELECT *
FROM sys.configurations
ORDER BY name OPTION (RECOMPILE);

2) Filename and Paths of Database
SELECT DB_NAME([database_id])AS [DBName],
name, physical_name, type_desc, state_desc,
CONVERT( bigint, size/128.0) [SizeinMB]
FROM sys.master_files
ORDER BY DB_NAME([database_id])

3)OS level information
SELECT *
FROM sys.dm_os_sys_info

Xp_cmdshell For OS commands

Introduced in sql server 2005
xp_cmdshall option is a server configuration option that enables system administrator.


"xp_cmdshell" is an extended stored procedure provided by Microsoft and stored
in the master database. This procedure allows you to issue operating system commands
 directly to the Windows command shell via T-SQL code


-- To allow advanced options to be changed.


EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
________________________________________________________________
exec master.dbo.xp_cmdshell 'dir c:\temp\*.sql'
________________________________________________________________
exec master.dbo.xp_cmdshell 'mkdir "c:\temp\SQL Agent Output\new_job\"'
________________________________________________________________
DECLARE @rc int
EXEC @rc = master.dbo.xp_cmdshell 'copy c:\temp\doesnotexist.txt c:\temp\workfile.txt'
print @rc
IF @rc <> 0
BEGIN
  PRINT 'Copy Failure Skip work'
END
ELSE
BEGIN
  Print 'Copy worked now we can do some more stuff'
  ....
END
________________________________________________________________

Server-side paging with Row_number()

If you are a programmer working with SQL Server, you must have found it little embarrassing to display information which spans across multiple pages (web pages). SQL Server 2000 did not allow you to retrieve a specific range of records, say, records 51 to 100 ordered by a certain column.

For example, assume that you are working on a web page which lists the names of all the cities in different countries. Assume that you need to display 25 records in a page. The database has 50,000 records consisting all the cities/towns across the globe. In the above scenario, it really makes sense to retrieve only the required records. for example, in the first page, retrieve 1 to 25 records. When the user clicks on "next" button, retrieve records 26 to 50 and so on. at this stage the user might click on another column to change the sort order. Earlier it was ordered by city name but now the display is based on Zip code.

With SQL Server 2000, it was not very easy to achieve this. Some times people used temp tables achieve this. Others put the paging responsibility to the application which retrieved all the records and then displayed the information needed for the current page. (this approach will not only overload server resources, but also degrades performance of the application as well as the database server.)

SQL Server 2005 introduces a helpful function ROW_NUMBER() which helps in this scenario. Using ROW_NUMBER()  you can generate a sequence number based on a given sort order and then select specific records from the results. Here is an example:

ROW_NUMBER() OVER (ORDER BY City) as Seq
The syntax ideally says the following. "Order the records by City, and then assign a serial number to each record". You can use it in a query as follows.

SELECT * FROM
(
    SELECT  ROW_NUMBER() OVER (ORDER BY City) AS row, *
    FROM Cities
) AS a WHERE row BETWEEN 101 AND 125

SQL Server CE 4.0 introduced a new TSQL extension that makes paging queries much easier. For example, to fetch rows 21 to 30, a query can be written like this.

SELECT * FROM Orders ORDER BY OrderID
OFFSET 20 ROWS
FETCH NEXT 10 ROW

Trigger with example

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.

DML triggers execute when a user tries to modify data through a data manipulation language (DML) event.

Syntex:
CREATE TRIGGER [owner.]trigger_name

ON[owner.] table_name

FOR[INSERT/UPDATE/DELETE] AS

IF UPDATE(column_name)

[{AND/OR} UPDATE(COLUMN_NAME)...]

{ sql_statements }

INSERT trigger

When an INSERT trigger statement is executed ,new rows are added to the trigger table and to the inserted table at the same time. The inserted table is a logical table that holds a copy of rows that have been inserted. The inserted table can be examined by the trigger ,to determine whether or how the trigger action are carried out.

The inserted table allows to compare the INSERTED rows in the table to the rows in the inserted table.The inserted table are always duplicates of one or more rows in the trigger table.With the inserted table ,inserted data can be referenced without having to store the information to the variables.

DELETE trigger

When a DELETE trigger statement is executed ,rows are deleted from the table and are placed in a special table called deleted table.

UPDATE trigger

When an UPDATE statement is executed on a table that has an UPDATE trigger,the original rows are moved into deleted table,While the update row is inserted into inserted table and the table is being updated.

For e.g
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
    DROP TRIGGER Sales.reminder2;
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2008R2 Administrator',
        @recipients = 'danw@Adventure-Works.com',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';
GO

Simple Example of Cursor

Using cursor Back up of all database

DECLARE @DataBaseName VARCHAR(50)
DECLARE @Path VARCHAR(200)
DECLARE @DataBaseFileName VARCHAR(200)

SET @Path = 'E:\Backup\'

DECLARE DataBase_Cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('form')

OPEN DataBase_Cursor

FETCH NEXT FROM DataBase_Cursor INTO @DataBaseName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @DataBaseFileName = @Path + @DataBaseName + '.BAK'
BACKUP DATABASE @DataBaseName TO DISK = @DataBaseFileName

FETCH NEXT FROM DataBase_Cursor INTO @DataBaseName
END

CLOSE DataBase_Cursor
DEALLOCATE DataBase_Cursor