Enhancements in SQL Server 2008

Phaneendhra
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 5832 red flag
Rating: 4 out of 5  
 2 vote(s)

With SqlServer 2008 version various new Transact-SQL programmability features and Enhancements of some existing features has been introduced by Microsoft. This article covers some new features/ Enhancements in Sql server 2008

Summary

With SqlServer 2008 version various new Transact-SQL programmability features and Enhancements of some existing features has been introduced by Microsoft. This paper covers some new features/ Enhancements in Sql server 2008

  • Table value constructor
  • Table -Valued parameters
  • Compound assignment operators
  • Enhancements to the CONVERT function

Table Value Constructor

Introduction

In earlier versions of SqlServer like SqlServer 2000 or SqlServer 2005 to insert multiple values in a table, we used to write multiple Insert statement for each record. In Sql Server 2008 Version there is a new mechanism or feature called

Table Value Constructor is available. It is one of the new programmability enhancements in Database Engine. It implements using VALUES Clause. We can use a single VALUES clause to construct a set of rows. With Table Constructor we can insert multiple values using a single INSERT statement. Sometimes this featured might be termed as Row Constructor also.

Syntax:

VALUES ( <

row value expression list> ) [ ,...n]

<

row value expression list> ::=

{<

row value expression> } [ ,...n]

<

row value expression> ::=

{ DEFAULT | NULL |

expression}

VALUES

Introduces the row value expression lists. Each list must be enclosed in parentheses and separated by a comma. The number of values specified in each list must be the same and the values must be in the same order as the columns in the table. A value for each column in the table must be specified or the column list must explicitly specify the columns for each incoming value.

DEFAULT

Forces the Database Engine to insert the default value defined for a column. If a default does not exist for the column and the column allows null values, NULL is inserted. DEFAULT is not valid for an identity column. When specified in a table value constructor, DEFAULT is allowed only in an INSERT statement.

expression

Is a constant, a variable, or an expression. The expression cannot contain an EXECUTE statement.

Sample Illustration

Inserting different Accounts in Accounts Table in sqlserver 2 000 or sqlserver 2005.Please verify the example specified below

CREATE TABLE [dbo].[Account] (

[AccountCode] CHAR(2),

[AccountName] VARCHAR(20)

)

GO

INSERT INTO [dbo].[Account]

VALUES ('A', 'Health')

INSERT INTO [dbo].[Account]

VALUES ('B', 'Banking')

INSERT INTO [dbo].[Account]

VALUES ('C', 'Finance')

INSERT INTO [dbo].[Account]

VALUES ('D', 'Auto')

GO

SELECT * FROM [dbo].[Account]

GO

AccountCode AccountName

--------- ------------

A Health

B Banking

C Finance

D Auto

The individual INSERT INTO... VALUES statement can now be simplified with the Row/Table constructor. The script above will now look as follows:

CREATE TABLE [dbo].[Account]

(

[AccountCode] CHAR(2),

[AccountName] VARCHAR(20)

)

GO

INSERT INTO [dbo].[Account]

VALUES ('A', 'Health'),

('B', 'Banking'),

('C', 'Finance'),

('D', 'Auto')

GO

SELECT * FROM [dbo].[Account]

GO

AccountCode AccountName

--------- ------------

A Health

B Banking

C Finance

D Auto

Limitations and Restrictions

  • Maximum number of rows that can be constructed using the table value constructor is 1000.
  • Only single scalar values are allowed as a row value expression. A sub query that involves multiple columns is not allowed as a row value expression.

Table-Valued Parameters

In earlier versions of sql server it is difficult to pass numerous parameters to T-Sql Statements, Stored Procedures or Functions. The common approach generally we follow is creating a Temporary Table on Demand and insert the values to the Temporary Table and then call the necessary procedure.

Advantages of Using Table Valued Parameters

  • They are Strongly Typed
  • We can specify the sort order and the unique key
  • Reduce Round trips to the server
  • We can have the table structure of different cardinality
  • It doesn't need or cause the statement to recompile
  • It doesn't need lock to initially populate the data from the client
  • Enables us to include complex business logic in a single routine
  • It provides a very simple programming model

Limitations and Restrictions         

  • SQL Server will not maintain statistics on columns of table-valued parameters
  • Table-valued parameters must be passed as input READONLY parameter to the T-SQL Routine and no DML operations are permitted like insert/update/delete on such parameters of the Table-valued type.
  • Table-valued parameter can't be used as the target of the SELECT INTO or INSERT EXEC statement. A table valued parameter can be in the FROM Clause in the SELECT INTO and INSERT EXEC.

Compound Assignment Operators

In SQL Server 2008 a new feature called Compound Assignment Operators was introduced. It is just like normal assignment Operational functionality which is common in normal Programming Languages did. Compound assignment operators help abbreviate code that assigns a value to a column or a variable. The new operators are:

· += (plus equals)

· -= (minus equals)

· *= (multiplication equals)

· /= (division equals)

· %= (modulo equals)

Sample Illustration

declare @i int set @i = 100

/**************************

 Addition and assignment

 ***************************/

 set @i += 1

select @i

———–

 101

/**************************

 Subtraction and assignment

***************************/

set @i -= 1

select @i

———–

100

/**************************

Multiplication and assignment

 ***************************/

set @i *= 2

 select @i

———–

200

/**************************

Division and assignment

 ***************************/

 set @i /= 2

select @i

———–

100

/**************************

 xor operation and assignment

***************************/

set @i ^= 2

select @i

———–

 3

/**************************

 Bitwise & operation and assignment

***************************/

set @i &= 2

select @i

———–

 2

/**************************

Bitwise | operation and assignment

 ***************************/

set @i |= 2

 select @i

———–

2

CONVERT Function

Convert function is not a new feature but comes as Enhancement in SQL Server 2008. In this feature it supports new conversion options between character strings and binary data types. You determine the conversion option to use by specifying a style number as the third argument. Style 0 is the default behavior that was supported in previous SQL Server versions—this style translates character strings to the binary representation of the ASCII codes for the characters and vice versa.

Styles 1 and 2 introduce new functionality. These styles convert a hex string literal to a binary value that contains the same digits when presented in hex form and vice versa. If you use Style 1 when converting from a character string to a binary value, the input character string should include the 0x prefix; if you use Style 2, it should not. Similarly, if you use Style 1 when converting from a binary value to a character string, the output character string will contain the 0x prefix; if you use Style 2, it will not.

Sample Illustration

The following example demonstrates using styles 1 and 2:

SELECT

CONVERT (VARCHAR (12), 0x51747A849B, 1) AS [Bin to Char 1],

CONVERT (VARBINARY (5), '0x51747A849B', 1) AS [Char to Bin 1],

CONVERT (VARCHAR (12), 0x51747A849B, 2) AS [Bin to Char 2],

CONVERT (VARBINARY (5), ‘51747A849B’, 2) AS [Char to Bin 2];

Output:

Bin to Char 1 Char to Bin 1 Bin to Char 2 Char to Bin 2

0x51747A849B 0x51747A849B 51747A849B 0x51747A849B

Page copy protected against web site content infringement by Copyscape

About the Author

Phaneendhra
Full Name: phaneendhra aswanikumar
Member Level: Starter
Member Status: Member
Member Since: 3/17/2011 1:44:53 AM
Country: India



Login to vote for this post.

Comments or Responses

Posted by: Websoftcreation on: 1/9/2012 | Points: 25
Its really interesting and helpful.Gr8 man go ahead.


Regards
Websoftcreation,jaipur
(ASP.net/PHP/Sales force/Oracle Trainer)

Login to post response

Comment using Facebook(Author doesn't get notification)