Security Features of Stored Procedures in Microsoft SQL Server

Security features of stored procedures are available in Microsoft SQL Server. These are :

1. Provide more granular security control through stored procedures rather than complete control on underlying data in tables.
2. Grants users permissions to execute a stored procedure irrespective of the related tables.
3. Grant 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.
4. Stored procedures can be granted execute permissions rather than setting permissions on data itself.

Usage of Triggers in Microsoft SQL Server

Triggers are used in Microsoft SQL Server for various purpose. These are used when :

1. Creation of audit log of database activity.
2. For applying business rules.
3. For applying calculation on data from tables which is not stored in them.
4. To enforce referential integrity.
5. When alter data in a third party application needed.
6. To execute SQL statements as a result of an event or condition automatically.

Restriction on Views in Microsoft SQL Server

Restrictions are applied when we create views in Microsoft SQL Server. These restrictions are listed below :

1. Views created, only referencing tables and views in current database. 
2. View name must not match with the name of table owned by that user.
3. Views can be built based on other views and on procedures that reference views.
4. We cannot associate Rules or DEFAULT definitions with views.
5. We can only associate INSTEAD OF triggers with views.
6. ORDER BY, COMPUTE, COMPUTE BY clauses and INTO keyword cannot be included in query that defines view.
7. Full-text index definitions cannot be used for views.
8. Temporary views cannot be created
9. Views cannot be created on temporary tables.

Checkpoints in Transaction Log in Microsoft SQL Server

Checkpoints are created in a transaction log When :

1. Checkpoint is explicitly executed.
2. Logged operation is performed on the database.
3. Database files have been altered using Alter Database command.
4. Microsoft SQL Server has been stopped explicitly or on its own.
5. Microsoft SQL Server periodically generates checkpoints.
6. Backup of a database is taken.

User Defined Data Types in Microsoft SQL Server

User defined data types in Microsoft SQL Server are based on system data types.

User defined data types are used when multiple tables need to store the same type of data in a column and you need to ensure that all these columns are exactly the same including length, and nullability.

User defined datatype contains following parameters:

• Name
• System data type on which user defined data type is based upon.
• Nullability

For example

User defined data type in Microsoft SQL Server called user_string could be created based on char system data type.