 Ease of use with a lot of wizards and designers.

 Flexible data model creation and management

 Scalable architecture to handle OLAP

 Provides integration of administration tools, data sources, security, caching, and reporting etc.

 Provides extensive support for custom applications

 Breaking of relationships is prevented once referential integrity on a database is enforced.

 Can’t delete a row from primary table if there are related rows in secondary table.

 Can’t update primary table’s primary key if row being modified has related rows in secondary table.

 Can’t insert a new row in secondary table if there are not related rows in primary table.

 Can’t update secondary table’s foreign key if there is no related row in primary table.

 Need INSERT permissions on destination table while importing.

 Need SELECT permissions on source table while exporting.

 Need SELECT permissions on sysindexes, sysobjects and syscolumns tables.

 Cursor reads every row one by one.

 Cursors can be used to update a set of rows or a single specific row in a resultset

 Cursors can be positioned to specific rows.

 Cursors can be parameterized and hence are flexible.

 Cursors lock row(s) while updating them.

 Cursor Types: Data access behavior depends on the type of cursor; forward only, static, keyset-drive and dynamic.

 Cursor behaviors: Keywords such as SCROLL and INSENSITIVE along with the Cursor declaration define scrollability and sensitivity of the cursor.

 They are easier to maintain and troubleshoot as they are modular.

 Stored procedures enable better tuning for performance.

 Using stored procedures is much easier from a GUI end than building/using complex queries.

 They can be part of a separate layer which allows separating the concerns. Hence Database layer can be handled by separate developers proficient in database queries.

 Help in reducing network usage.

 Provides more scalability to an application.

 Reusable and hence reduce code.

 Do not ask for an input from a user during a transaction.

 Get all input needed for a transaction before starting the transaction.

 Transaction should be atomic

 Transactions should be as short and small as possible.

 Rollback a transaction if a user intervenes and re-starts the transaction.

 Transaction should involve a small amount of data as it needs to lock the number of rows involved.

 Avoid transactions while browsing through data.

 Grants users permissions to execute a stored procedure irrespective of the related tables.

 Grant users users permission to work with a stored procedure to access a restricted set of data yet no give them permissions to update or select underlying data.

 Stored procedures can be granted execute permissions rather than setting permissions on data itself.

 Provide more granular security control through stored procedures rather than complete control on underlying data in tables.

 To create a audit log of database activity.

 To apply business rules.

 To apply some calculation on data from tables which is not stored in them.

 To enforce referential integrity.

 Alter data in a third party application

 To execute SQL statements as a result of an event/condition automatically.

 A view cannot be indexed.

 A view cannot be Altered or renamed. Its columns cannot be renamed.

 To alter a view, it must be dropped and re-created.

 ANSI_NULLS and QUOTED_IDENTIFIER options should be turned on to create a view.

 All tables referenced in a view must be part of the same database.

 Any user defined functions referenced in a view must be created with SCHEMABINDING option.

 Cannot use ROWSET, UNION, TOP, ORDER BY, DISTINCT, COUNT(*), COMPUTE, COMPUTE BY in views.

 Broker event category includes events produced by Service Broker.

 Cursors event category includes cursor operations events.

 CLR event category includes events fired by .Net CLR objects.

 Database event category includes events of data.log files shrinking or growing on their own.

 Errors and Warning            event category includes SQL Server warnings and errors.

 Full text event category include events occurred when text searches are started, interrupted, or stopped.

 Locks event category includes events caused when a lock is acquired, released, or cancelled.

 Object event category includes events of database objects being created, updated or deleted.

 OLEDB event category includes events caused by OLEDB calls.

 Performance event category includes events caused by DML operators.

 Progress report event category includes Online index operation events.

 Scans event category includes events notifying table/index scanning.

 Security audit event category includes audit server activities.

 Server event category includes server events.

 Sessions event category includes connecting and disconnecting events of clients to SQL Server.

 Stored procedures event category includes events of execution of Stored procedures.

 Transactions event category includes events related to transactions.

 TSQL event category includes events generated while executing TSQL statements.

 User configurable event category includes user defined events.

 When a checkpoint is explicitly executed.

 A logged operation is performed on the database.

 Database files have been altered using Alter Database command.

 SQL Server has been stopped explicitly or on its own.

 SQL Server periodically generates checkpoints.

 Backup of a database is taken.