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 –
- Declaring and initializing new variables.
- Compound assignment operators
- Table value constructor
- Enhanced Convert Function
- Date and Time Data types
- Table Type
1. Declaring and initializing new variables – SQLServer 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.
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.
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 –
IF OBJECT_ID(‘DBO.EMPLOYEE’,’U’) IS NOT NULL DROP TABLE DBO.EMPLOYEE
CREATE TABLE DBO.EMPLOYEE
( ID INT NOT NULL,
— NOW CHECK THE FOLLOWING INSERT STATEMENT
INSERT INTO DBO.EMPLOYEE(ID,NAME,MOBILE_NUMBER,[ADDRESS])
(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
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.
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.
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
DECLARE @CUSTOMER AS DBO.CUSTOMER
INSERT INTO @CUSTOMER
SELECT * FROM @CUSTOMER
Posted on October 15, 2013, in ASP.Net, SQL and tagged ASP.Net Developer, Date and Time Data types, Important T-SQL Programmability feature, Insert (SQL), Mindfire Solutions, Mukund Narayan, SQL, SQLSERVER 2008, T-SQL, Table value constructor. Bookmark the permalink. Leave a comment.