Showing posts with label sql server. Show all posts
Showing posts with label sql server. Show all posts

Monday, July 23, 2012

Advantages of Stored Procedure in Microsoft SQL Server


There are many advantages of using stored procedures in Microsoft SQL Server. These are :
  1. Easier to maintain and troubleshoot as they are modular.
  2. Stored procedures enable better tuning for performance.
  3. While using stored procedures is much easier from a GUI end than building complex queries.
  4. Stored procedures help in reducing network usage.
  5. Stored procedures provide more scalability to an application.
  6. Reusable and hence reduce code.
  7. Stored procedures 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.


How to Code Efficient Transactions in Microsoft SQL Server

To Code efficient transactions in Microsoft SQL Server, We must take care of following things :

  1. Don't allow input from users during a transaction.
  2. Don't open transactions while browsing through data.
  3. Keep the transaction as short as possible.
  4. Use lower transaction isolation levels.
  5. Access the least amount of data possible while in a transaction.

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.


Sunday, July 22, 2012

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.



Friday, July 13, 2012

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.

Blocking in Microsoft SQL Server

Blocking is one of the main problem which Microsoft SQL Server experience. Blocking happens due to following reason :

When one connection from an application holds a lock and a second connection requires a conflicting lock type. This causes the second connection to wait, blocked on the first. 


Second connection will be active only when first connection release lock required by second connection.

Failover Clustering in Microsoft SQL Server

Failover clustering in Microsoft SQL Server is used for data availability.

Typically two machines are used in a failover cluster. One machine provides the basic services and the second is available to run the service when the primary system fails. 


The primary system is monitored periodically to check if it works. This monitoring may be performed by the failover computer or an independent system also called as cluster controller. 


In an event of failure of primary computer, the failover system takes control.

Builtin Administrator Account in Microsoft SQL Server

Every Instance of Microsoft SQL Server has Built in Administrator Account. It is used during some setup to join some machine in the domain.

Built in Administrator account in Microsoft SQL Server should be disabled immediately thereafter. 


For any disaster recovery, the built in administrator account will be automatically enabled. It should not be used for normal operations.

Microsoft SQL Server Public Role

In Microsoft SQL Server, every database has a public role which holds all the default permissions for the users in database.

Public role in Microsoft SQL Server cannot be dropped and we cannot assign roles or groups to it. 


Minimum permissions should be granted to public role, due to security reasons.

Thursday, July 12, 2012

Scalability of Databases in Microsoft SQL Server

Scalability of Database System can be enhanced efficiently by Microsoft SQL Server.

Microsoft SQL Server 2000 automatically adjusts the granularity of locking for each table used by a query. 


Parallel Execution Plans is in placed automatically. This split the processing of a SQL statement into pieces that can be run on a different CPU, which causes the complete result set to build faster.

Why Use English Query in Microsoft SQL Server

English Query in Microsoft SQL Server used to build applications that can accept query in questions form e.g question is in English language.

While processing English Query, the application passes the questions string to the English query engine. This English Query engine returns a SQL statement or a Multi-Dimensional Expression (MDX) query to the application, Which will return the answer to the user’s question.

Wednesday, July 11, 2012

Microsoft SQL Server Execute SQL Statements in Different Ways

There are many different ways Microsoft SQL Server uses to execute SQL statements. These different way are following :
  • Single SQL statement processing
  • Batch processing
  • Stored procedure and trigger execution
  • Execution plan caching and reuse
  • Parallel query processing


Why Use SQL Profiler in Microsoft SQL server

Uses of SQL Profiler in Microsoft SQL server

SQL Profiler captures SQL Server events from a server. These events are saved in a trace file that can be used to analyze and diagnose problem.

There are many other useful function of SQL Profiler, these are following :

  • SQL Profiler used to find the cause of the problem by stepping through problem queries.
  • SQL Profiler is very useful to analyze the cause of slow running queries.
  • SQL Profiler can be used to tune workload of the Microsoft SQL server.
  • SQL Profiler also stores security-related actions that can be reviewed by a security administrator.
  • SQL Profiler also supports auditing the actions performed on instances of Microsoft SQL Server.


Why Use Full Text Query in Microsoft SQL Server

Full Text Query in Microsoft SQL Server

Full Text Query search used to search for the entire text instead of using “%”. We use it to search for text with data type TEXT or NOTE etc . 

Some predicates like FREETEXT, CONTAINS are used in full text query search. FREETEXT finds the word or words you give it anywhere in the search column. For example

SELECT Apparel FROM Products WHERE FREETEXT (Apparel, 'Van' )

The output of above query will be : Best Van Huesen trousers

Complete Phases of Transaction in Microsoft SQL Server


There are several phases of transaction in Microsoft sql server. These are following :

When transaction starts, database is in consistent state .

BEGIN TRANSACTION statement starts the transaction explicity.

Transaction generates the first record for data modification and write these records to log.

Modification starts one table at time, at this stage database is in inconsistent state.

Upon successful completion of all modification, database is once again consistent, and then application commits the transaction.

In case some errors occur, it rolls back all the modifications processed on data. This process returns the database to the point of consistency it was at before the transaction started.