SQL Server 2008 New Features

Microsoft SQL Server 2008 introduces several important new Transact SQL programmability features and enhances some existing ones

 

Following is the list of key new features

1.Initializing variables

2.Compound assignment operators

3.Enhanced CONVERT function

4.New date and time data types

5.New date and time functions

6.The MERGE statement

7.Grouping sets

8.Table valued parameters

9.Large user-defined types

10.Table value constructor support

11.DDL trigger enhancements

12.Sparse columns

13.Filtered indexes

We will provide the information in detail in next section.

How to import data directly from T-SQL commands without using SQL Server Integration Services?

There are so many ways to import data directly using T-SQL.

Here listed couple of commands which helps to load the Data quickly into the Database:

• BCP
• Bulk Insert
• OpenRowSet
• OPENDATASOURCE
• OPENQUERY

How to insert the values manually for the Identity Column ?

What is Identity Column :

•  For Identity Column, SQL Server will provide the values automatically at the time of inserting each record in the table.

Example :

-- Creating the table

CREATE TABLE TestTable ( TestID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, TestValue NVARCHAR(50) NOT NULL )

-- To insert value to the identity column

INSERT TestTable(TestId, TestValue) VALUES (1, 'record 1')

-- Cannot insert explicit value for identity column in table 'TestTable' when IDENTITY_INSERT is set to OFF.


SET IDENTITY_INSERT TestTable ON       -- Disable the Identity to Insert the values manually

INSERT TestTable(TestId, TestValue) VALUES (1, 'record 1')       -- Insert the values for the identity column

select * from TestTable

SET IDENTITY_INSERT TestTable OFF    -- Enable the Identity to Insert the values by the Server automatically

Differences between Stored procedures and User defined functions

Difference 1:

• Stored procedure used for perform specific tasks
• Normally functions will be used for computing value

Difference 2: 

• Stored procedures may or may not return values
•  But function should return value

Difference 3:

• Stored procedure cannot be used in the select/where/having clause
• But function can be called from select/where/having clause 

Difference 4:

• Stored procedure can run independently. It can be executed using EXECUTE or EXEC command

• But function cannot run independently, It has to be the part of the SQL statement

Difference 5:

• Temporary table (derived) cannot be created on function
• But it can be created in stored procedures 

Difference 6:

• From sql server 2005 onwards, TRY CATCH statements can be used in the stored procedures

• But it cannot be used in the function. But we can use raise error function

Difference 7:

• Stored procedure can call the user defined functions
• But the function cannot call the stored procedures

The function cannot call the stored procedures like procedures. There are many types of stored procedures in sql server.

• System Stored procedure
• User defined Stored procedure
• NET CLR stored procedure
• Extended stored procedure

Except extended stored procedures no one can call the user defined functions

Difference 8:

• Stored procedures can have input and output parameters
• But the function can have only input parameters

Difference 9:

• Stored procedures can have select and all DML operations
• But the function can do only select operation

Functions will not allow to do the DML on existing tables. But still we can do the DML operation only on the table variable inside the user defined functions

Difference 10:

• Function cannot have the transaction statements.

The transaction statement cannot be used in the function. Normally we won't do any DML operations in the function.

• Stored procedure can use transaction statements