MS SQL Server Managerment Objects (MS-SMO) - Using SMO

The SMO namespace is implemented as a .NET assembly using which you can include all SMO functionality in your .NET application. The SMO namespace is Microsoft.SqlServer.SMO and is located by default in “C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies” directory. It is installed with the client tools of SQL Server 2005 and requires Common Language Runtime (CLR) to be installed as well. The assemblies required to work with SMO in managed environment are:

• Microsoft.SqlServer.ConnectionInfo
• Microsoft.SqlServer.Smo
• Microsoft.SqlServer.SmoEnum
• Microsoft.SqlServer.SqlEnum

Other assemblies present in SMO can be used as and when required.

SMO can be used even in an unmanaged environment, as there exist the COM wrappers around the SMO classes. The SQLSMO.dll and SQLSMO.tlb files enable the SMO to be used with unmanaged code.

SMO Classes

The SMO Object Model Contains two types of classes: Instance Classes and Utility Classes. Each of them is discussed below in details.

Instance Classes

SMO Instance Classes contains the SMO Objects in a hierarchy that matches the hierarchy of a database server. At the top of the hierarchy is the Server Instance class that represents a SQL Server instance, and under it there are other classes in hierarchy representing the other objects of the database such as databases, tables, columns, indexes, stored procedures, etc.

A sample hierarchy of Instance Classes is depicted in the figure below:


Utility Classes

SMO Utility Classes are meant for performing some specific task, being independent of the SQL Server Instance. Lists of tasks, which can be performed using these utility classes, are:

• Generate Database Scripts
• Backup / Restore Databases
• Transfer Database Schema between database instances
• Administering the Database Mail subsystem
• Administering the SQL server Agent
• Administering the Service Broker
• Administering the Notification Services

MS SQL Server Managerment Objects (MS-SMO) Introduction

Microsoft has introduced Server Management Objects (SMO) with SQL Server 2005 that enhances the capability of Distributed Management Objects (DMO) of the earlier version of SQL Server. In SQL Server 2005, DMO is abandoned in favour of SMO. It allows managing the database server programmatically, and is compatible with the earlier versions of SQL Server (2000 and 7.0). However, SMO cannot be used to manage databases with compatibility level 60 and 65. The only functionality that SMO doesn’t offer as compared to DMO is that, replication objects are not included in SMO. Instead, there is separate Replication Management Objects (RMO) that exists for replication in SQL Server 2005.

To enlist few of the tasks that you can do with SMO are:

• Connect to Database Server
• Create Database
• Drop Database
• Backup Database
• Attach / Detach Database
• Copy Database Objects
• Create / Edit / Drop Objects (Tables / Views / Indexes / Stored Procedures / etc.)
• Create / Edit / Drop Relationship between tables
• Generate Scripts
• Handle HTTP and SOAP requests using EndPoints objects

The list still goes on. It’s almost everything that you can do in a Server; you can do it through SMO. In addition, the Capture Execution feature in SMO is an interesting new feature that allows capturing scripts for later execution. For example, suppose you have a section of your code that creates a database or table, adds an index, populates data, for example, in an installation routine. After testing, you can actually use SMO to capture this as a script for later execution, or on a separate server.

continued...

Naming Conventions for SQL Server 2005

Simple rules for naming Objects in SQL Server 2005:

1. Do not use any spaces in object names: With SQL Server it is possible to have such names by enclosing these in square brackets [and], but are not recommend as you may miss them out and end up in failure quite often.
2. Avoid using reserved words: Even if the word you use doesn't cause an error, it will still cause confusion when someone is editing code and Query Analyzer applies color highlighting to table or column names.
3. Start object names with a letter: Several applications like Enterprise Manager, Query Analyzer, DTS, VBScript, Visual Studio may cause problems when accessing objects with name starting from numbers.
4. Keep names short but meaningful: This is self-explanatory and fairly logical, as there may be table name either ridiculously long, making it cumbersome both to type and to commit to memory, or abbreviated to the point of utter confusion. "SlQ" is too short. "SalesFiguresForCompanyByFiscalQuarter" is just silly.
5. Avoid Hungarian notation*: The name of the object should make it pretty obvious what type of data it contains, and if for some reason it does not, then there is always the metadata tables and/or the documentation you should have written when designing the system. Using datatype-style prefixes for columns like IContactID (integer) and VEmail (varchar) not only make the column names harder to read, they also make them less flexible.
6. Use CamelCasing instead**: Using a standard convention like CameCase for naming all the objects can help in maintaining consistency in object names.
7. Use the dbo. Prefix: When you are logged in as a non-dbo user, and you create a table without giving it an owner prefix, other users won't see it, because it is stored in the system as you.objectName instead of dbo.objectName. If you consistently use the dbo. prefix, you will eliminate the possibility of creating the same object name twice, with different owner names.

*Hungarian notation:
Hungarian notation is a naming convention in computer programming, in which the name of a variable indicates its type or intended use. In Hungarian notation, a variable name starts with one or more lower-case letters which are mnemonics for the type or purpose of that variable, followed by whatever the name the programmer has chosen

**CamelCasing:
CamelCase is a writing convention in which words are combined by taking out the spacing in between and capitalizing the first letter of each word, such as CamelCase. The name is derived from the idea of the capital letters being reminiscent of humps on a camel's back. The CamelCase convention is used in computer systems in which naming require words to be contiguous. With CamelCase, words are readily distinguished and names more easily read.

Naming Conventions for SQL 2005:


*Do not use prefix sp_ for stored procedures. SQL Server searches for system stored procedures first when it encounters the sp_ prefix. Another reason- if Microsoft decides to rename all system stored procedures using the prefix to identify the system stored procedures, yours too would get renamed (since it has the same prefix) causing your application to fail.

MS SQL Server - Index Internals III

Today’s discussion continues on the different types of Indexes available on MS SQL Server.

Unlike from my previous example of index on the back of an Atlas, which normally has one index, a table may have multiple indexes. These indexes can be defined on one or more columns of the tables. The two major types of indexes in SQL Server are Clustered and Non-clustered Indexes.

Clustered Indexes: A clustered when defined on a column of a table, the data of the table in the data pages are sorted in order of the column on which index is defined. This column is referred as an Index Key. Since the data is sorted physically on the disk, the leaf page of the index pages are the data pages of the table.

Inserting a new row in a table with clustered index defined, SQL Server ensures that the row is placed in the correct physical location in key sequence order. Structure of the Clustered Index is as given below:Non-Clustered Indexes: A Non-clustered index contain only the index key (column on which non-clustered index is defined) and a reference to find the data. The items in the index are stored in the order of the index key values, but the information in the table is stored in a different order (which can be dictated by a clustered index). If no clustered index is created on the table, the rows are not guaranteed to be in any particular order.

Non-clustered index structure is depicted as: At any given level in the index, the pages are linked together as shown in Figure below, and this is true regardless of whether the index is a Clustered index or a Non-clustered index.
Both the clustered and non-clustered indexes can be defined on one or more columns of a table, to serve the frequent database queries. However there can be only one clustered index defined on a table while there can be 249 non-clustered indexes created on a table.

MS SQL Server - Index Internals II

Following to my earlier post on how SQL Server stores and access data, lets now get into details of what Indexes and how are they processed by SQL Server for improving performance.

As per the definition “An Index is a pointer to data in a table”. Here when we say pointer it’s not much different from the pointers that we access in programming language like C/C++. If you have worked with pointers, you know that “A pointer is used to store the location of a variable or we say a Data Member”. Similarly in SQL Server an Index stores the locations of the data rows of a table and occupies a discrete memory in SQL Server.

Index is stored separately from the table for which the index is created. When an Index is defined, the Index pages are created by SQL Server to store the address of the data rows, based on the Indexed column. These Indexes are linked to each other in a B-tree structure. Let’s consider a table products with the following structure and data inserted in following order in data pages:


Now Suppose an Index is defined on the UnitPrice field of the table. The Index Pages will be defined for the table as shown below:

SQL Server uses these indexes to find data quickly when a query is processed. Indexes can be created or dropped with no effect on the data, as it is stored as separate entity from the table. Indexes operate 'behind the scenes' in support of the database engine to improve the performance of data retrieval.

Consider the following query on the products table above. This query retrieves products in a specific price range.

SELECT ProductID, ProductName, UnitPrice FROM Products WHERE (UnitPrice > 4.5) AND (UnitPrice <10)

If there was no index defined on the table, a table scan will be performed to find the products with price ranging between 7.5 and 10. There will be 24 iterations (navigating through each row) to find the required result.

With an index defined on the UnitPrice field, navigating through the Main Index Page, the query can be served in 4 iterations. Hence you can distinguish the remarkable difference an Index can make in the performance of a query.

Later in the tread I will be talking about the different types of Indexes in SQL Server.

MS SQL Server - Index Internals I

I dedicate this post of mine to my most interesting subject of MS SQL Server – Index Internals, for everyone interested in getting a depth of how Indexing in SQL Server works.

While running a query in SQL Server, many of them wonder at times “why particular query performs so terrible while the other is just fine”. One of the key factors that can affect a query performance is the Indexing available on the related tables.

Best possible way to recognize how index can benefit queries and what would make things worse; is to understand how SQL Server processes these indexes internally.

Let us dive into the details of how SQL Server stores and access data in database.

SQL Server can store data in two ways:

1. In Heap: The Order in which the data is inserted is stored in the same order in the data pages of the table.


2. Order of the Clustered Indexed Column Key: The data is added to the data pages of a table sorted in the order of the column having clustered index. Index Pages are added for the table to store the indexes.

These data in SQL Server can be accessed in two ways:

1. Table Scan: The data in the heap is accessed by scanning through each row in the data pages of the table to serve a query request.


2. Using Indexes: With the help of Indexes, SQL Server scan through the Index Pages using B-Tree traversing algorithm to arrives to the resultset in the data pages. Index scan helps SQL Server to search data in the table in a similar way as we scan through the Index page of an Atlas to find a location on the book.

Below is an illustration to help you understand the data storage and access in the SQL Server database:

• Let us have two tables, TableA and TableB that collect names of the students, with the field Name.
• TableA stores the data in the Heap
• TableB has a index defined on the field Name


• The data in both the tables are entered in following order: John, Sam, Anna, Denial, Rudd, Andrew, Samantha and Brown.


• 
  
• 
  

Later in this topic I will be discussing in detail what the indexes are, how they are stored in SQL Server and how it help in improving the performance.

continued...