![]()
Article posted by
Phaneendhra on 1/9/2012 | Views: 4032 | Category:
Sql Server | Level: Intermediate |
Points: 250 
If you found
plagiarised (copied) or inappropriate content,
please
let us know the original source along with your correct email id (to communicate) for further action.
Advertisements
Advertisements
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
If you like this article, subscribe to our 
RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.