Some Important T-SQL Programmability feature of SQLSERVER 2008

There are lots of new feature introduced with SQLServer 2008 for improving the SQL experience of developers. Here we will cover some very important and useful features of SQLServer 2008. Features which we are going to cover is –

  1. Declaring and initializing new variables.
  2. Compound assignment operators
  3. Table value constructor
  4. Enhanced Convert Function
  5. Date and Time Data types
  6. Table Type

1. Declaring and initializing new variablesSQLServer 2008 provides the new way to declare and initialize the variables.Previously we were using separate DECLARE and SET statement for declaring and initializing variables. Now we can declare and initialize within a single statement.

Example:-

            DECLARE @COUNT AS INT = 1, @NAME AS VARCHAR(50) = ‘MUKUND NARAYAN’, @DATE AS  DATETIME = GETDATE()

SELECT @COUNT AS [COUNT], @NAME AS NAME, @DATE AS DATE

2. Compound Assignment Operators –   CAO shortens the query. Compound operators execute some operation and set an original value to the result of the operation.

Example:-

                DECLARE @COUNT AS INT = 2
SET @COUNT += 4
SELECT @COUNT AS [COUNT]

Note: – This code sets the value of @COUNT to it’s current value. (i. e. 2 + 4 = 6)

SQLSERVER 2008 Provides following compound operators –
+= (plus equal), -= (minus equal), *= (multiplication equals), /= (division equals),
%= (modulo equals)

3.Table Value Constructor Support through the VALUES Clause –  With the help of this new feature we can add multiple rows in ta table with one single VALUES clause. Let us   understand this with one example –

            USE TEMPDB
IF OBJECT_ID(‘DBO.EMPLOYEE’,’U’) IS NOT NULL DROP TABLE DBO.EMPLOYEE

CREATE TABLE DBO.EMPLOYEE

(               ID INT NOT NULL,
NAME VARCHAR(50),
MOBILE_NUMBER VARCHAR(20),
[ADDRESS] VARCHAR(100)

)

— NOW CHECK THE FOLLOWING INSERT STATEMENT

INSERT INTO DBO.EMPLOYEE(ID,NAME,MOBILE_NUMBER,[ADDRESS])

VALUES
(1,’VINOD’,’8989845345′,’ADDRESS 1′),
(2,’MUKUND’,’8987443443′, ‘ADDRESS 2′),
(3,’UTTAM’, ‘4564545654’, ‘ADDRESS 3’)

SELECT * FROM DBO.EMPLOYEE

4.Enhanced CONVERT function – The CONVERT function  is enhanced in SQLSERVER 2008 for supporting new conversion option between character strings and binary data types. Let us check an Example –

            If you execute the following query you will get value 0x4D554B554E44

SELECT CONVERT(VARBINARY(10), ‘MUKUND’) — value will be 0x4D554B554E44

We can determine the conversion option by specifying the style. Style can be 0,1, 2. 0 is default   which was in earlier version also.

SELECT (CONVERT(VARCHAR(10),0x4D554B554E44,0)) AS ‘Style 0’ — MUKUND (default behavior)
SELECT (CONVERT(VARCHAR(10),0x4D554B554E44,1)) AS ‘Style 1’ — 0x4D554B55
SELECT (CONVERT(VARCHAR(10),0x4D554B554E44,2)) AS ‘Style 2’ – 4D554B554E

Style 0 works the same as on 2005 it converts binary to VARCHAR, if you have 0x4D554B55    Then you will get ‘MUKUND’
Style 1 converts binary to VARCHAR but the values stay the same, if you have 0x4D554B55     You will get ‘0x4D554B55’.
Style 2 strips the 0x but leaves the rest of the values, if you have 0x4D554B55                            You will get  ‘4D554B554E’

5.Date and Time Data Types:- In SQLServer 2008 Microsoft provided separate data types for Date and Time

Date and Time Data Types

Date and Time Data Types

Here are some examples –

DECLARE @DATE AS DATE = ’10-10-2010′,

@TIME AS TIME = ’02:04:12.12345′,
@DATETIME2 AS DATETIME2 = ‘2013-10-10 02:04:12.12345’,
@DATETIMEOFFSET AS DATETIMEOFFSET = ‘2013-10-10 02:04:12.1234567 +02:00’

SELECT @DATE AS NEWDATE,

@TIME AS TIME,
@DATETIME2 AS DATE_TIME,
@DATETIMEOFFSET AS DATE_TIME_OFFSET

Note:- New Data Types are fully supported by SqlServer Native client like OLE DB, ODBC providers as well as by ADO.NET in Microsoft Visual Studio 2008.
Here is the mapping of new data types with ADO.NET types.

SQL ADO.NET
DATE DateTime
TIME TimeSpan
DATETIME2 DateTime
DATETIMEOFFSET DateTimeOffset

Apart from above date and time data types, Sqlserver 2008 provided some important functions which are very useful. Here are those functions.

SYSDATETIME – returns the current system date and time in format of DateTime2.

SYSUTCDATETIME – returns the current system date and time in UTC in format of DateTime2.

SYSDATETIMEOFFSET – returns the current system date and time along with the system time zone as DATETIMEOFFSET.

SWITCHOFFSET – this function adjust system DATETIMEOFFSET to a specified time zone, while preserving the UTC.

Example – SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), ‘+02:00’)

TODATETIMEOFFSET – sets the time zone offset to input date.

Example – SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), ‘+02:00’)

6.Table Type:- Table Types enable us to save table definition in database and use it later to define table variables and parameters to stored procedure and functions.
This is very helpful for reducing the chances for errors.

Example –

USE NORTHWIND
GO
CREATE TYPE DBO.CUSTOMER AS TABLE

(
CUSTOMER_ID INT NOT NULL PRIMARY KEY,
CUSTOMER_NAME VARCHAR(50) NOT NULL,
CUSTOMER_MOBILE_NUMBER VARCHAR(50) NOT NULL

)

GO

DECLARE @CUSTOMER AS DBO.CUSTOMER
INSERT INTO @CUSTOMER
VALUES

(1,’MUKUND’,’787878787878′),
(2,’EJAZ’, ‘766765565576’)

SELECT * FROM @CUSTOMER

Written By: Mukund Narayan, ASP.Net Developer, Mindfire Solutions

Advertisements

Posted on October 15, 2013, in ASP.Net, SQL and tagged , , , , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: