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)

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

Generate Sequential Number in SQL Server

 

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

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

Difference Between SET and SELECT commands in SQL Server

SET	SELECT
Can assign the value of an expression to the variable	Can assign the value of an expression to the variable
Cannot retrieve data from data source. It can only retrieve data from the expressions.	Can retrieve data from data source and also uses other SELECT clauses (WHERE, FROM etc)
Use SET only when you want to assign value of a function result or constant to a variable	Can use for both i.e. retrieve value of function result

Example

–Variable declaration
DECLARE
    @vName VARCHAR(10),
    @vobject_id VARCHAR(30),
    @vName1 VARCHAR(30);

–assigning a single value
SET @vName1=’Test Value’;

–Selecting and retrieving multiple values in a single select statement
SELECT
    @vName=name,
    @vobject_id=object_id
FROM
    sys.objects
WHERE
    name =’sysrowsetcolumns’

–printing the retrieved values
SELECT  @vName1,@vName, @vobject_id

–By Harsh Shah

Download SQL Server 2008 and SQL Server 2005 Sample Database

SQL Server 2008 Sample Database for SQL Server 2008 February CTP

Following is the link to the CodePlex (CodePlex is Microsoft’s open source project hosting web site) site from where you can download the examples

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

SQL Server 2005 Sample Database – For SQL Server 2005 SP2a

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

Microsoft is Positioned Leader in Gartner’s Business Intelligence Top Ranking

Microsoft Is Positioned in Leaders Quadrant of Latest Magic Quadrant for Data Warehousing.

This is indeed a great news for all of us. The Magic Quadrant is an esteemed evaluative model established by Gartner. It positions vendors according to their ability to execute and completeness of vision. For more information please visit the following links.

Microsoft Is Positioned in Leaders Quadrant of Latest Magic Quadrant for Data Warehousing
http://www.microsoft.com/presspass/press/2007/oct07/10-12DWMQPR.mspx

Magic Quadrant for Business Intelligence Platforms, 1Q07
http://mediaproducts.gartner.com/reprints/oracle/145507.html

TOP 10 Microsoft Business Intelligence Benefits

1. Connect people to information efficiently and effectively.
2. Empower employees.
3. Simplify collaboration and sharing.
4. Analyze and gain insight.
5. Improve alignment.
6. Harness the power of the 2007 Microsoft Office system.
7. Deliver business intelligence to your entire organization.
8. Reduce training needs
9. Deliver sophisticated analysis and reporting.
10. Deliver enterprise-grade business intelligence.

(Source : office.microsoft.com)

SQL Server: How to change SQL Server Authentication Mode

To change the SQL Server Authentication mode please follow the below steps

1. Logon to SQL Server and Right Click on the instance name (see in the below image) and select Properties.

2. You will see the Server Properties for the selected instance, Here you select Security page from the left side pane. Once you select the Security page you will get the option to change the Server Authentication mode.

By Harsh Shah (Sr. Consultant)