Archive

Author Archive

TRUNCATE Table Vs DELETE Table

October 3, 2009 shahharsh Leave a comment

TRUNCATE Table

  • Fastest method for deleting all the rows from a table.
  • We can say Truncate Table is similar to Delete Statement without WHERE clause (for.e.g. DELETE FROM EMP)
  • Uses fewer system and transaction log resources. Only the page deallocations are recorded in the Transacton Log.
  • Fewer locks are used. Truncate table always locks the table and page but not the row.
  • Without exception, zero pages are left in the table
  • If the column in a table is having IDENTITY colum then Truncate Table command will reset the identity to it’s seed value. If no seed is defined then value 1 is used as default value.

DELETE Table

  • Deletes one row at a time and each deleted row is logged in the transaction log.
  • Delete statement locks each row for deletion (when used with Row Lock)
  • Once the delete process is over, table still contains the empty pages in a heap.
  • If the DELETE statement doesn’t use the Table Lock then the table may contain many empty pages .
  • To deallocate the empty pages, use TABLOCK hint in the DELETE statement. This will put a shared lock on the table instead of row or page lock and deallocate the pages.

Note: When empty pages are not de allocated, the space cannot be reused by other objects.

Example

TRUNCATE TABLE EMP_TEST 

DELETE FROM EMP_TEST WITH (TABLOCK)

Categories: Interview, SQL2005, SQL2008 Tags:

Saving Changes is not permitted The changes you have made require the following tables to be dropped and recreated – SQL Server 2008

June 20, 2009 shahharsh 5 comments

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.

image

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.

image

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

 

image

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

Happy Learning !!!

Categories: SQL2008 Tags:

This server version is not supported. Only servers up to Microsoft SQL Server 2005 are supported –Visual Studio 2005

May 25, 2009 shahharsh Leave a comment

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 !!!

Download SQL Server 2008 Developer Training Kit

May 24, 2009 shahharsh Leave a comment

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 !!!!

Categories: SQL2008 Tags: ,

SQL Server 2008 Editions

May 19, 2009 shahharsh Leave a comment

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 !!!

Categories: SQL2008 Tags: , ,

Difference between Clustered and Non Clustered Index in SQL Server 2005

May 16, 2009 shahharsh 5 comments

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’); 
     

    image

    –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′);

         image


    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’);

    image

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

Happy Learning !!!

Categories: SQL2005, SQL2008 Tags:

Introduction to Struct in C#

March 23, 2009 shahharsh Leave a comment

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

Download Application Architecture Guidance for .Net

November 28, 2008 shahharsh Leave a comment

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 !!!

How to change default language in Visual Studio 2005

November 28, 2008 shahharsh 8 comments

 

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

image

2.  Select Reset all settings and then click next.

image

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.

image 

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.

image

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.

 

image

Note: Please try this in test environment only.

Happy Learning..!!!

By Harsh Shah

Categories: Visual Studio 2005

Generate Sequential Number in SQL Server

October 2, 2008 shahharsh 1 comment

 

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

Categories: SQL2005