Table Parameters and Table Types

A new feature in SQL 2008 is table-valued parameters. You can pass a table variable as a parameter to a stored procedure. When you create your procedure, you don't put the table definition directly in the parameter list of the procedure, instead you first have to create a table type, and use that in the procedure definition. At first glance it may seem like step of extra work, but when you think of it, it makes very much sense: you will need to declare the table in at least two places, in the caller and in the callee. So why not have the definition in one place?

Here is a quick example that illustrates how you do it:

CREATE TYPE my_table_type AS TABLE(a int NOT NULL,
b int NOT NULL)
go
CREATE PROCEDURE the_callee @indata my_table_type READONLY AS
INSERT targettable (col1, col2)
SELECT a, b FROM @indata
go
CREATE PROCEDURE the_caller AS
DECLARE @data my_table_type
INSERT @data (a, b)
VALUES (5, 7)
EXEC the_callee @data
go

So this is the final solution that makes everything else I've talked of in this article of academic interest? Unfortunately, it's the other way round. See that word READONLY in the procedure definition? That word is compulsory with a table parameter. That is, table parameters are for input only, and you cannot use them to get data back. There are of course when input-only tables are of use, but most of the time I share a temp table or use a process-keyed table it's for input-output or output-only.

Using the cursor Data Type in an OUTPUT Parameter

A comprehensive example of how to use cursor data type in an output parameter. If you are interested view it @

What is New in SQL Server "Denali" (part II)

1. Encryption

SQL Server 2008 R2 supports MD2, MD4, MD5, SHA, or SHA1 hash algorithms for encryption of you data. The new release add support for SHA2_256 and SHA2_512 algorithms. You can use HASHBYTES function as previous version.

2. User defined server role

One of the new security features added to Denali is the ability for user now to add user defined server roles which wasn't possible in the previous versions. User can now create, drop, alter user defined server roles.

3. SQL server security model

Users don't require logins when access to contained database is permitted. This is a big change and an area that needs to be looked at properly. To understand how this is implemented you can get more info Designing and Implementing a Contained Database

4. New permission

Due to the addition of user defined server role, there are also associated permission. Permission to grant, deny and revoke on user defined server roles.

What is New in SQL Server "Denali" (part I)

Part I : Availability and manageability Enhancements

As we all know microsoft is working on the next generation of SQL Server called Denali. It is at early stages to say what will be released but I would like to go through some of the features.


1. HADR

The introduction of the "HADR" solution for enhancing availability of user databases in an enterprise environment. This new enhancement of the database engine will help database administrators to enable to maximize availability for one or more of your user databases. HADR is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. For more information , overview and to deploy, configure and administer HADR please refer to HADR

2.Combination of BIDS and Management studio


As we all know SQL Server managment studio was a one stop shop for your SQL server development and administation. The same is also true of Business Intellegence development studio (BIDS). The new version of SQL server will combine the two studio into one IDE. I think this is pretty cool.

3.Column-Based Query Accelerator

Column-Based Query Accelerator will help dramatically increase query performance ~10x as per microsoft's claim. I will probably test if this statement is true and will post my results on this blog. Colum base query accelerator will reduce performance tuning through interactive experiences with data for near instant response times and streamlined setup which removes the need to build summary aggregates.

4.SQL Server Management Studio enhancements

The Database EngineQuery Editor introduces enhanced functionality for Transact-SQL debugging and IntelliSense. Ability to debug T-SQL running on SQL2005 SP2 and later,
The Watch window and Quick Watch can now be used to watch T-SQL expressions,
Moving your cursor over T-SQ identifier will bring up a quick info pop up that displays the name of the expression and its value are just few of many new features that has been introduced in the next version of SQL Server

5. PowerShell

Windows powershell is no more part of the SQL server installation but it is part of pre-requisite. I am not sure if this is considered to be a new feature but nice to know.

6.Contained Databases
In new version of SQL server when moving a database from one instance of database engine to another instance, the dependancy of users in a contained database no longer associated with the logins on the instance. Microsoft claims that many other depenpendencies on the instance is also removed. For more info on contained database and the terms @read more on contained database

7. Database engine start-up options

Database start up option is now configured from SQL server configration manager.