Wednesday, October 28, 2009

Basic Storage Modes (MOLAP, ROLAP and HOLAP) in Analysis Services

Basic Storage Modes (MOLAP, ROLAP and HOLAP) in Analysis Services
There are three standard storage modes (MOLAP, ROLAP and HOLAP) in OLAP applications which affect the performance of OLAP queries and cube processing, storage requirements and also determine storage locations. To learn more about these standard storage modes, pros and cons of each one, click here.

Database Impersonation with EXEC AS in SQL Server

Database Impersonation with EXEC AS in SQL Server
SQL Server 2005/2008 provides the ability to change the execution/security context with the EXEC or EXECUTE AS clause. You can explicitly change the execution context by specifying a login or user name in an EXECUTE AS statement for batch execution or by specifying the EXECUTE AS clause in a module (stored procedure, triggers and user-defined functions) definition. Once the execution context is switched to another login or user name, SQL Server verifies the permission against the specified login or user (specified with EXECUTE AS statement) for subsequent execution instead of the execution context of current user. To learn more about this feature and how it works click here.

Spatial Data Types (GEOMETRY and GEOGRAPHY) in SQL Server 2008

Spatial Data Types (GEOMETRY and GEOGRAPHY) in SQL Server 2008
SQL Server 2008 provides support for geographical data through the inclusion of new spatial data types, which you can use to store and manipulate location-based information. These native data types come in the form of two new data types viz. GEOGRAPHY and GEOMETRY. These two new data types support the two primary areas of spatial model/data viz. Geodetic model and Planar model. Geodetic model/data is sometimes called round earth because it assumes a roughly spherical model of the world using industry standard ellipsoid such as WGS84, the projection used by Global Position System (GPS) applications whereas Planar model assumes a flat projection and is therefore sometimes called flat earth and data is stored as points, lines, and polygons on a flat surface. To learn more about this new feature click here.

FILESTREAM Data Type in SQL Server 2008

FILESTREAM Data Type in SQL Server 2008
The new SQL Server 2008 FILESTREAM data type enables SQL Server applications to store unstructured data, such as documents and images, on the file system with a pointer to the data in the database. This enables client applications to leverage the rich NTFS streaming APIs and performance of the file system while maintaining transactional consistency between the unstructured data and corresponding structured data with same level of security. Backups can include or exclude the binary data, and working with the data is with the standard SELECT, INSERT, UPDATE, and DELETE statements in T-SQL. FILESTREAM storage is implemented as a varbinary(max) column in which the data is stored as BLOBs in the file system. The sizes of the BLOBs are limited only by the volume size of the file system. The standard varbinary(max) limitation of 2-GB file sizes does not apply to BLOBs that are stored in the file system. To learn more about this new feature click here.

Large User Defined Types in SQL Server 2008

Large User Defined Types in SQL Server 2008
With SQL Server 2005, Microsoft integrated the .NET Common-Language Runtime (CLR) into the database engine itself, with that now you were allowed to create used defined type (UDT) and use it in SQL Server in a similar way as you use any in-built data type once assembly containing UDT is registered into the database. It was good starting point, but the problem with it is, the size of UDT is limited up to 8000 bytes only. SQL Server 2008 overcomes this limitation by introducing Large User Defined Type and increases size all the way to go upto 2GB. Learn more here.

HIERARCHYID Data Type in SQL Server 2008

HIERARCHYID Data Type in SQL Server 2008
SQL Server 2008 has introduced a new data type HIERARCHYID to store hierarchical data in database table. HIERARCHYID is a variable length system data type, and used to locate the position in the hierarchy of the element. The HIERARCHYID data type is optimized for representing trees, which are the most common type of hierarchal data. The HIERARCHYID data type should be used to represent the position in a hierarchy, that is, a column of type HIERARCHYID does not represent a tree itself, but rather it simply represents the position of a row/node within a defined tree. HIERARCHYID data type exposes many different methods which can be used to retrieve a list of ancestors and descendants as well as a means of traversing a tree etc. For more details click here.

Tuesday, October 27, 2009

New Date and Time Data Types in SQL Server 2008

SQL Server 2008 introduces four new DATETIME data types which are more optimized for type of usage and memory requirement, along with DATATIME2 which is now SQL compliant and compatible with .Net type DATETIME. To learn more about it and how it works click here.

User-Defined Table Type and Table Valued Parameter (TVP) in SQL Server 2008

User-Defined Table Type and Table Valued Parameter (TVP) in SQL Server 2008
With SQL Server 2008, you can create a user-defined table type which represents the definition of a table structure. To ensure that the data in a user-defined table type meets specific requirements, you can also create unique constraints and primary keys on this type. Further, to send multiple rows of data to a stored procedure or a function without creating a temporary table or many parameters, you can use a user-defined table type to declare table-valued parameters for stored procedures or functions.
Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. Table-valued parameters offer the following benefits; for example it does not acquire locks for the initial population of data from a client, it does not cause a statement to recompile, reduce round trips to the server, enable the client to specify sort order and unique keys etc. To learn more about these new exciting features and how to use it from .Net application, clich here.

Tuesday, October 6, 2009

Backup and Restore SQL Server databases programmatically with SMO

Backup and Restore SQL Server databases programmatically with SMO
In this article I am going to provide examples to SQL Server Database Administrators on how to backup and restore SQL Server databases programmatically with SMO. I will start with how you can issue different types (Full, Differential and Log) of backups with SMO and how to restore them when required programmatically using SMO. Click here for more details....

Generate SQL Scripts for database objects with SMO

Generate SQL Scripts for database objects with SMO
In this article I take about how you can generate SQL object scripts programmatically. Though you can do this through SQL Server Management Studio (SSMS) there might be times (more details on usage scenarios given below) when you would need to create SQL scripts automatically. Click here for more details....

Accessing SQL Server programmatically with SQL Server Management Objects (SMO)

Accessing SQL Server programmatically with SQL Server Management Objects (SMO)
SQL Server 2005 and 2008 provide SQL Server Management Objects (SMO), a collection of namespaces which in turn contain different classes, interfaces, delegates and enumerations, to programmatically work with and manage a SQL Server instance. SMO extends and supersedes SQL Server Distributed Management Objects (SQL-DMO) which was used for SQL Server 2000. In this article, I discuss how you can get started with SMO and how you can programmatically manage a SQL Server instance with your choice of programming language. Click here for more details....

Change Tracking in SQL Server 2008

Change Tracking in SQL Server 2008
Change Tracking is a light-weight feature which provides a synchronization mechanism between two applications. In other words, it tracks a table for net DML (INSERT, UPDATE and DELETE) changes that occur on a table, so that an application (like a caching application) can refresh itself with just the changed dataset. In this article, I am going to discuss in detail about what Change Tracking is, how it works, how to configure it, an application scenario and how it differs from Change Data Capture. Click here for more details....

Reorganize and Rebuild Index in SQL Server 2005 and 2008

Reorganize and Rebuild Index in SQL Server 2005 and 2008
Once you have identified the high fragmentation level in your database, which could be a bottleneck in your SQL Server performance, what is the next step of fixing this high fragmentation. In this article, I am going to discuss the different methods and its feasibility to fix the identified high fragmentation levels by Reorganize and Rebuild, click here for more details....