Today, while changing the column definition i came across the following message

“Saving Changes is not premitted. The changes you have made require the following tables to be dropped and recreated……..”

I created a table without primary key and then later tried to add the primary key using the ALTER TABLE command but got the below message.

The solution to the above message is:

1. In the SQL Server Management Studio, go to Tools –> Options. you will get the options window as show below.

2.  Uncheck the 6th option under Table options i.e. Prevent saving changes that require table re-creation. See the image below

3. Now you should be able to modify the table definition.

Happy Learning !!!

To resolve the error , you can download the update- Microsoft Visual Studio 2005 Service Pack 1 Update for Microsoft SQL Server 2008 Support

This update enables developers to use Microsoft Visual Studio 2005 Service Pack 1 with Microsoft SQL Server 2008. For more information please visit the download link.

Download link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=e1109aef-1aa2-408d-aa0f-9df094f993bf&displaylang=en

Happy Learning !!!

The Training Kit will help you understand how to build web applications which deeply exploit the rich data types, programming models and new development paradigms in SQL Server 2008.

The SQL Server 2008 Developer Training Kit will help you understand how to build web applications which deeply exploit the rich data types, programming models and new development paradigms in SQL Server 2008.

Thanks to Microsoft Developer and Platform Evangelism for the excellent Training Kit.

Download Link: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=e9c68e1b-1e0e-4299-b498-6ab3ca72a6d7

Training Kit Contents

• Presentations (6)
  • Filestream
  • Spatial
  • T-SQL
  • Date and Time Types
  • SQLCLR
  • Reporting Services
• Demos (12)
  • AdventureWorks Racing All-Up SQL Server 2008 Demo
  • SQL Server 2008 All-Up Spatial Demo
  • Spatial Types Demo
  • Intro to Filestream Demo
  • SQL CLR Nullable Types Demo
  • Programming with Filestream Demo
  • Reporting Services Web Application Integration Demo
  • Date and Time Support in SQL Server 2008 Demo
  • T-SQL Table-Valued Parameters Demo
  • T-SQL Row Constructors Demo
  • T-SQL Grouping Sets Demo
  • T-SQL Merge Demo
• Hands-on Labs (3)
  • Using Spatial Data in TSQL
  • Using Spatial Data in Managed Code
  • Using SQL CLR in SQL Server 2008

  Note: Description taken from the above download link.

Happy Learning !!!!

Today, while searching for the SQL Server 2008 Edition list, I found the details of various editions and feature comparisons. So thought let me share with you all for your quick reference.

SQL Server 2008 Editions

http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx

Compare Edition Features

http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx

SQL Server 2008 Pricing

http://www.microsoft.com/sqlserver/2008/en/us/pricing.aspx

Download SQL Server licensing overview

http://download.microsoft.com/download/1/e/6/1e68f92c-f334-4517-b610-e4dee946ef91/2008%20SQL%20Licensing%20Overview%20final.docx

Happy Learning !!!

Clustered Index

1. Leaf node contains the actual data pages.
2. The data row of the table are sorted and stored in the table based on their clustered index key (i.e. based on the index column(s)).
3. You can have only one clustered index per table.
4. The RowLocator in Clustered Index is the clustered Index key.

Non Clustered Index

1. Leaf node contains index pages instead of data pages.
2. You can have up to 249 Non Clustered Index per table.
3. The data row of the table are not sorted and stored in the table based on their clustered index key (i.e. based on the index column(s)).
4. The row locator in Non Clustered Index is a pointer to the row. Row locator is built based on the following.
   ROW ID (RowLocator)= file identifier + page number + row number on the page
5. You can have the functionality of Non key Columns (Included Columns) in case of Non Clustered Index.
6. Non key columns are stored only at the leaf level whereas Key columns (Non Clustered Index Key columns) are stored at all the levels of non clustered index.

What are Heaps?

1. Heaps are tables without clustered index.
2. Data Rows are not stored in a particular order.
3. Sequences of the Data pages are not ordered as well as they are not linked in a linked list.

Examples

Clustered Index Example

Clustered can be created in the following ways

1. Create Table with Primary key – this will by default create clustered index based on the primary key defined.
2. Create clustered Index using the CREATE CLUSTERED INDEX command.

–create table with primary key
CREATE TABLE Employee (empno NUMERIC (10) PRIMARY KEY, EmpName Varchar(10));

–check the existence of Index for the table

SELECT * FROM SYS.INDEXES WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME=‘EMPLOYEE’); 
 




–create table without primary key

CREATE TABLE Employee2 (empno NUMERIC (10), EmpName Varchar(10));

–Create Clustered Index using Clustered Index  Command

CREATE CLUSTERED INDEX IDX_CLUST_EMP ON Employee2 (empno);

–check the existence of Index for the table

SELECT * FROM SYS.INDEXES WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME=‘Employee2′);

    

Non Clustered Index Example

– Adding a column the existing employee table

ALTER TABLE EMPLOYEE
ADD TAX_ID NUMERIC(10);

–create non clustered index on TAX_ID column
CREATE INDEX IDX_NON_CLUST ON EMPLOYEE (TAX_ID);

–check the existence of Index for the table
SELECT * FROM SYS.INDEXES WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME=‘EMPLOYEE’);

This is a very basic and a quick information on the differences between Clustered and Non Clustered Indexes in SQL Server.

Happy Learning !!!

This article provides an introduction to Structs in C#. It gives a basic understanding on the following

• What is struct?
• When to use Struct?
• Struct Examples

1. Struct is a value type.
2. It is used to encapsulate small related group of variables.
3. It can also contain the following
   • Constructor
   • Fields
   • Constants
   • Methods
   • Properties
   • Indexers
   • Operators
   • Events
   • Nested types

Note: If you are planning to use all the above listed members then it is better to use a class. The reason is struct are mainly used for light weight objects.

4. Struct can implement interface but cannot inherit from another struct. Thus struct members cannot be declared as protected.

5. Except constants and static fields, we cannot initialize fields within the struct declaration.

6. We can declare constructors with parameters for structs.

7. All structs are inherited from System.ValueType (which inherits from System.Object).

8. Struct can be used as nullable type and can also be assigned a null value.

9. If you want to initialize the struct members then the only way is to declare a parameterized constructor and in that constructor initialize the struc members (Refer to the example 2 in this article)

How Structs are defined?

The Structs are defined by using struct keyword.

Example

Public struct TableDimension

{

//Declare Fields, constants,properties, methods ….here

}

Example 1: Creating a Struct

struct TableDimensions

{ 
            public int tableHeight;

public int tableLength;

}

Using struct:

private void btnStruct1_Click(object sender, EventArgs e)

{

          //Declare and Initialize the struct objects
          TableDimensions tbDim;

tbDim.tableHeight = 10;

tbDim.tableLength = 20;

        //Displaying value from the struct objects

         MessageBox.Show("Table Length " + tbDim.tableLength.ToString()); 
} 

Note:

1. In the above example1 we can see that the struct object is created without using the new operator.

2. This means that there will be no constructor call for struct if we create an object without using the new operator.

3. In this case the struct fields will remain unassigned and cannot be used until they are assigned values. If you comment the fields assignment in the above example and try to compile the code then it will result in error.

Example 2: Creating a Parameterized constructor for struct and initialized the struct members 

//Declaring a struct

struct TableDimensions

{ 
public int tableHeight; 
public int tableLength;

public TableDimensions(int height1,int length1)

{
tableHeight = height1;

tableLength = length1;

}

}

private void btnStruct1_Click(object sender, EventArgs e)

{

TableDimensions tbDim1 = new TableDimensions(30, 60);

MessageBox.Show("Table Length " + tbDim1.tableLength.ToString()

);

}

Notes:

1. In the above example we have created a struct with the parameterized constructor to initialize the structure fields

2. When we create an object of a struct using new operator, the new operator doesn’t acts like a class but it simply calls the declared constructor and initialize all the fields.

3. Parameterized constructors are the only way in structs to initialize the struct members.

Hope you liked the introduction to struct in C#…your suggestions are welcome.

Happy Learning…by Harsh Shah

This guide provides guidance on Application Architecture for .Net Applications. For more information please download the guide from the below CodePlex Site.

Thanks to the CodePlex team, Patterns and Practices Team ,Industry Experts and all the professionals who contributed to the App Arch Guide.  

Download App Arch Guide: http://www.codeplex.com/AppArch

Happy Learning !!!

Note: Please try this in your test environment only and also export your previous settings before you proceed.

1. Click on Visual Studio 2005 menu and go to Tools –> Import and Export Settings

2.  Select Reset all settings and then click next.

3.  Once you click next you will get the below screen. Here you will provide path to save your current settings. It is advisable to save your current settings before you proceed. So that you can even import later if you want your previous settings back.

Click Next once you provide the appropriate path.

4.  Now, you will get the below screen where you will select the default language you would like to have when you select New Project in Visual Studio 2005. Select your desired language and click Next.

5.  Once you click Next, it will process and set your desired language as a default settings. After completing the process you will get the below dialog box.

Note: Please try this in test environment only.

Happy Learning..!!!

By Harsh Shah

Here is the small example on how to generate sequential number in SQL Server.

DECLARE @cnt INT
SET @cnt=0
WHILE (@CNT<=10)
BEGIN
    PRINT @CNT
    SET @CNT=@CNT+1
END

 SQL Server 2005 Product Samples

http://www.codeplex.com/SqlServerSamples/Release/ProjectReleases.aspx?ReleaseId=4000

SQL Server 2005 Sample Database

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

SQL Server 2008 Product Samples

http://www.codeplex.com/SqlServerSamples