Monthly Archives: February 2012

Difference between Dataset and Datareader?

  By Gaurav Agrawal | February 9, 2012 - 11:00 PM |
Leave a comment

Data Set is a connection-less service and Data reader is a connection oriented service. Dataset is used to store the data, it contains collections of Datatable. Datareader is used to connect to the database for retrieving data. Data Reader – Forward only where as Dataset – Can loop through dataset. Data Reader – Connected Recordset where as DataSet – Disconnected Recordset Data Reader – Less Memory Occupying where as DataSet – It occupies more memory Data Reader – Only Single Table can be used where as Dataset – Datatable Concept allows data to be stored in multiple tables. Data Reader … Click here to continue reading. →

What are the basic functions of master, msdb, model, tempdb and resource databases in SQL Server?

  By Gaurav Agrawal | February 8, 2012 - 9:33 PM |
Leave a comment

The master database holds information for all databases located on the SQL Server instance and is theglue that holds the engine together. Because SQL Server cannot start without a functioning masterdatabase, you must administer this database with care. The msdb stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping. The tempdb holds temporary objects such as global and local temporary tables and stored procedures. The model is essentially a template database used in the creation of any new user database created in the instance. The resoure is … Click here to continue reading. →

What is an execution plan? When would you use it? How would you view the execution plan?

  By Gaurav Agrawal | February 7, 2012 - 9:38 PM |
Leave a comment

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display … Click here to continue reading. →

What is Trigger?

  By Gaurav Agrawal | February 6, 2012 - 11:36 AM |
Leave a comment

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not … Click here to continue reading. →