TRUNCATE Table Vs DELETE Table

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)

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

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

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

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

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

SQL Server 2008 Editions

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

Difference between Clustered and Non Clustered Index in SQL Server 2005

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

Download SQL Server Product Samples

 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

Microsoft BI Demos

 

You can find quick demos on Microsoft Business Intelligence at the below location.

http://www.microsoft.com/bi/resources/demos.aspx

Happy Learning…

Harsh Shah

SQL Server 2008 Release Candidate 0

Download SQL Server 2008 Release Candidate – Here

SQL Server 2008 RC0 will automatically expire after 180 days.

SQL Server 2008 – A Quick Overview

SQL Server 2008 Overview

SQL Server 2008 delivers on Microsoft’s Data Platform vision by helping your organization manage any data, any place any time. So before we discuss more about SQL Server 2008 new features, let’s talk about what is Microsoft Data Platform Vision?

Microsoft Data Platform Vision

Today we live in the world of information explosion where in we come across many types of data on a day to day basis. We store, retrieve and transact data into different types such as images, video, audio, digital information, sensor information from RFID tags and the list goes on. As we now know that organizations have become considerably large with the global presence and due to this the need to make data more secure and regulatory compliance has become mandatory. Also the data stored should be available Any Where – Any Time.

Today the user and application has become completely mobile and we want our data/information to be viewed on any device -any time – anywhere. So the question here is how to deal with this kind of complexities and manage the information explosion in an effective and efficient way.

The answer to the above challenges is Microsoft Data Platform Vision. It helps organization to overcome the challenges of information explosion by providing a solution that organization can use to store many types of data, including the following

1. XML
2. e-mail
3. time/calendar
4. File
5. Documents and Geospatial

SQL Server 2008 provides reach set of services to interact with the data , query the data , performing data analysis , reporting, integration and robust synchronization. Above all , users can access information on any device.

What SQL Server 2008 Delivers?

SQL Server 2008 delivers on Microsoft’s Data Platform vision by helping your organization manage any data, any place, any time. It enables you to store data from structured, semi-structured, and unstructured documents such as images, music, documents directly within the database.

Top New Features

• Use Resource Governor to manage concurrent workloads
• Enforce policy compliance consistently across the enterprise with the Policy-Based Management
• Reduce storage requirements and increase query performance with data compression and Sparse Columns
• Protect sensitive data with Transparent Data Encryption and advanced auditing
• Troubleshoot, tune, and monitor SQL Server 2008 instances across the enterprise with Performance System Analysis
• Build high performance analysis solutions with scalability, performance, data mining and user interface enhancements in SQL Server Analysis Services
• Take advantage of improved performance, usability, visualization, and integration with the 2007 Microsoft Office system in SQL Server Reporting Services
• Integrate location-enabled applications through support for spatial data 

 

Top New Security Features

· Use the Declarative Management Framework to help enforce security polices for data services across the enterprise.

· Encrypt data without modifying applications, using Transparent Data Encryption.

· Employ enterprise-wide encryption solutions with Extensible Key Management and Hardware Security Modules.

· Audit all actions with the new Audit object.

Top New High Availability Features

• Recover corrupt data pages from a mirror server with enhanced database mirroring features. Watch the short demo.
• Take advantage of failover clustering enhancements in Windows Server 2008.
• Add new nodes to a peer-to-peer replication solution without taking replication offline.
• Improve restore times, and reduce backup volumes with new support for backup compression.
• Improve concurrency with locking enhancements.
• Reduce downtime for hardware maintenance with hot-add CPU capabilities.
• Use Resource Governor to proactively control workload prioritization.

Top New Performance and Scalability Features

• Take control of resource allocation with Resource Governor.
• Troubleshoot, tune, and monitor SQL Server 2008 instances across the enterprise with Performance Data Collector.
• Build high-performance analysis solutions with scalability and performance enhancements in SQL Server Analysis Services.  Take advantage of on-demand processing and better performance with the re-engineered reporting engine for SQL Server Reporting Services.
• Increase extract, transform, and load (ETL) performance in SQL Server Integration Services with scalable lookup tasks.

Happy Learning…..I will be sharing few more information in my next article.

Harsh Shah
(Sr. Consultant)