Archive for August 2010

uCertify’s Back 2 School Sale : Save 40%

By on August 28, 2010
This entry is filed under Uncategorized.

uCertify is offering a 40% discount on all Prepkits. A user can select from extensive selection of over 300 PrepKits to help prepare for their IT certification exam(s). You will be very delighted to know that uCertify offer goes like this: Buy any two PrepKits for $139.99 (compare to about $119.99 for each PrepKit!).
This time you can also take advantage of an additional discount by joining us on Facebook! All anyone have to do is like our page to be entitled to this private discount.

http://www.facebook.com/pages/ucertify/176752623633?v=app_4949752878

So what are you waiting for, offer is valid for first 300 customers, So HURRY UP!!!!

Share

Difference between clustered and non clustered index

By on August 10, 2010
This entry is filed under SQL Server and tagged .

There can be two types of indexes for a table: clustered and nonclustered. The difference between clustered and nonclustered indexes is that in a clustered index, the data itself is sorted in a particular order, while in a nonclustered index, the data is stored in one place, an index in another, with pointers to the storage location of data. Only one clustered index can be created per table. By default, a clustered index is created for the primary key of the table. As the clustered index is a physical order of a table, a table and a clustered index cannot be separated and stored on different filegroups.

Share

Disadvantages of using a cursor

By on August 9, 2010
This entry is filed under SQL Server and tagged .

Following are some of the disadvantages of using the cursor which may vary from database system to database system:

  1. Fetching a row from the cursor may result in a network round trip each time. This uses much more network bandwidth than would ordinarily be needed for the execution of a single SQL statement like DELETE. Repeated network round trips can severely impact the speed of the operation using the cursor.
  2. Some DBMSs try to reduce this impact by using block fetch. Block fetch implies that multiple rows are sent together from the server to the client. The client stores a whole block of rows in a local buffer and retrieves the rows from there until that buffer is exhausted.
  3. Cursors allocate resources on the server, for instance locks, packages, processes, temporary storage, etc. For example, Microsoft SQL Server implements cursors by creating a temporary table and populating it with the query’s result-set.
  4. If a cursor is not properly closed (deallocated), the resources will not be freed until the SQL session (connection) itself is closed. This wasting of resources on the server can not only lead to performance degradations but also to failures.
Share

Indexing

By on August 8, 2010
This entry is filed under SQL Server Business Intelligence and tagged .

All databases can take advantage of indexing to increase their speed. An index allows a set of table rows matching some criterion to be quickly located. Typically, indexes are also stored in the various forms of data-structure, such as B-trees, hashes, and linked lists. Usually, a database designer selects specific techniques to increase efficiency in the particular case of the type of index required.

An index speeds up access to data, but it has disadvantages as well. First, every index increases the amount of storage used on the hard drive which is also necessary for the database file, and second, the index must be updated each time the data is altered, and this costs time. Thus, an index saves time in the reading of data, but it costs time in entering and altering data. It thus depends on the use to which the data is to be put whether an index is overall a net plus or minus in the quest for efficiency. The whole process is termed as indexing.

Share

HIERARCHYID data type

By on August 6, 2010
This entry is filed under SQL Server Business Intelligence and tagged .

A HIERARCHYID data type is a new data type introduced in SQL Server 2008. It is a system-supplied CLR (Common language runtime) UDT (user defined type) that may be used for the manipulation and storage of the hierarchies. It is stored internally as a VARBINARY value that signifies the location of the current node in the hierarchy. It is possible to carry out manipulations on the HIERARCHYID data type by using either Transact-SQL or client APIs to call upon methods exposed by the type.
is a new data type introduced in SQL Server 2008. It is a system-supplied CLR (Common language runtime) UDT (user defined type) that may be used for the manipulation and storage of the hierarchies. It is stored internally as a VARBINARY value that signifies the location of the current node in the hierarchy. It is possible to carry out manipulations on the HIERARCHYID data type by using either Transact-SQL or client APIs to call upon methods exposed by the type.

Share

Aggregations

By on August 5, 2010
This entry is filed under SQL Server Business Intelligence.

Aggregations are pre-calculated summaries of data from leaf cells. They are very efficient in reducing the query execution time and improving query response time by preparing the answers before the questions are asked. Aggregations are used to provide performance improvements by allowing Microsoft SQL Server Analysis Services (SSAS) to retrieve pre-calculated totals directly from cube storage instead of recalculating data from an underlying data source for each query.

Share

Page

By on August 4, 2010
This entry is filed under SQL Server Business Intelligence.

The page is the most fundamental unit of data storage in SQL Server. In SQL Server, the page is one common denominator for all objects. The page size is always 8KB. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are carried out at the page level, which means SQL Server reads or writes entire data pages. Many types of pages exist, but every page has some factors in common. The page always has a header, leaving about 8,060 bytes of usable space on every page.

Share

Relational Online Analytical Processing

By on August 3, 2010
This entry is filed under SQL Server Business Intelligence and tagged .

ROLAP (Relational Online Analytical Processing) is the storage mode for the dimensions. The dimensions that uses ROLAP store the data in the tables that are used to define dimension. The ROLAP storage mode, at the expense of query performance, can support large dimensions without duplicating large amount of data. The ROLAP storage mode can also support real-time OLAP because the dimension relies directly on the tables in the data source view that is used to define the dimensions.

Share

Filestream data type

By on August 2, 2010
This entry is filed under SQL Server and tagged .

The Filestream data type is recommended for the files that are larger than 1 MB in size or for which the user wants to improve read performance. Filestream storage must be implemented as a varbinary(max) column. Varbinary(max) data can be stored within the table, or in the file systems. Data is stored as a binary large object (BLOB) in the file system. Additionally, the Filestream data is stored in a Filestream filegroup. When data is stored as Filestream data, SQL can perform the select, insert, and update statements against the data.

Share

Caching

By on August 1, 2010
This entry is filed under SQL Server and tagged .

Caching is a process that improves the performance of a system. A cache is a component that improves the performance by transparently storing data such that future requests for that data can be served faster. The data that is stored within a cache might be values, which have been computed earlier or duplicates of the original values that are stored elsewhere. If requested data is contained in the cache (cache hit), this request can be served by simply reading the cache, which is comparably faster. Otherwise (cache miss), data has to be recomputed or fetched from its original storage location, which is comparably slower. Hence, more the requests served from the cache, better the overall system performance is. The overall process is known as Caching.

Share