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.

Service Broker in Microsoft SQL Server

Service Broker in Microsoft SQL Server allows internal and external processes to send and receive guaranteed and asynchronous messaging.

Messages can also be sent to remote servers hosting databases as well. 


Service Broker use the concept of queues to put a message in a queue and continue with other applications asynchronously. This enables client applications to process messages at their leisure without blocking the service broker. 


The concepts of message ordering, coordination, multithreading and receiver management is used in Service Broker to solve some major message queuing problems. It allows for loosely coupled services, for database applications.

Analysis Service Repository in Microsoft SQL Server

Microsoft SQL Server running analysis service has a repository to store objects of the computer running Analysis Services an Analysis service repository.

It stores the information about the cubes, dimensions and other objects of the analysis server. 


Server computer where the analysis service is installed has by default repository an .mdf database.

Microsoft SQL Server Lock Escalation

Lock escalation is dynamically managed by Microsoft SQL Server. It is process of conversion of row locks and page locks into table locks thereby “escalating” the smaller or finer locks.

Lock escalation increases the system performance as each lock is nothing but a memory structure. Too many locks would mean more consumption of memory. Hence, escalation is used.