Tuesday, June 30, 2009

SSMS-Table Changes

The other day I happened to add a column to a table using SSMS, normally i use the alter table script to do such a task. Once i added the column and tried to save the schema changes in SSMS i got an error saying Cannot save changes that require table re-creation. I digged around and found that there is an Option in SSMS to avoid such errors and enable table changes. In SSMS Under Tools -> Click On Options, a popup window appears like the one shown below. In Designers option, there is a choice for Table and Database Designers:

Under Table Options, Uncheck the box which says "Prevent Saving Changes that require table re-creation" and click the OK button.
Once this is done, this should allow table changes to be performed from SSMS, this is a new feature in SQL Server 2008.

Thursday, June 25, 2009

SQL Server 2008-Execution Plan

When writing stored procedures or writing complex queries it is pretty much a standard thing to do is the use of execution plans. The execution plans point to how the query is being executed, what kind of indexes are being used etc. In SQL Server 2008, there is an option called Include Execution plan which can be set prior to the execution of a query or a stored procedure. In SSMS this option can be found under the Query Menu option, the Keyboard shortcut is Ctr+M.

Once the query is executed, the execution plan can be see in the results window, one of the neat features in 2008 is that it points out where indexes are missing and gives recommendation on the kind of indexes that need to be created on a table in order to improve query performance.

In the execution plan, one has the option to save the plan as a .sqlplan file, also the query where the missing indexes are recommended can be edited in a new query window.

Wednesday, June 17, 2009

SQL Server 2008-Change Data Capture

One of the neat features available in SQL Server 2008 is Change Data Capture (CDC), there is also an other feature called Change Data Tracking. The Change data capture feature provides historical information for a user table by capturing data changes that were made. This feature allows the developer to indicate which columns in a particular table needs to be captured. This feature allows to build in audit capabilites which could be used for compliance purposes. In order to use CDC, one has to enable the database to perform Change Data Capture: This can be done by:
Use DatabaseName
EXECUTE sys.sp_cdc_enable_db;
Once this is done, One can selectively enable Change Data Capture on tables that need to be audited, this can be done by:

EXEC sys.sp_cdc_enable_table
@source_schema = N'Test'
,@source_name = N'Test_Table'
,@role_name = N'dbo'
,@capture_instance = N'tbl_audit_Test_Table'
,@captured_column_list = N'Col1Primarykey,Col2,Col3,Col4
,@filegroup_name = N'PRIMARY';

One of the requirement here is that there needs to be a Primary key defined on the Source Table. Here the Test_table has Col1 has the Primary key.
This would enable CDC on the table called Test_table, the audit data would be captured in the database object called tbl_audit_test_table_CT(_CT is added to the name given in the capture instance by the system) , in order to query the data in this, the Select statement would be

SELECT Col1,Col2,Col3,Col4 FROM cdc.tbl_test_table_CT.

There are supporting functions which can be used to access data from the captured instance. MSDN and Books line have lot of information regarding these.

In order to Disable CDC on a table run this command:

EXECUTE sys.sp_cdc_disable_table
@source_schema =N'Test',
@source_name = N'Test_Table',
@capture_instance = N'tbl_audit_Test_Table';

To disable CDC on the Database, run the following command:
USE DatabaseName
EXECUTE sys.sp_cdc_disable_db;