Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In Oracle PL/SQL scripts, you can declare a variables using the syntax tablename%rowtype for its type. This will make that variable have the same "type" as a row of that table

(i.e. a it will be a structure with the same field names and types).

This is a convenient way to transfer data between database tables and PL/SQL.

I am looking at doing the same thing (Data Type) in Microsoft SQL Server 2008. Is there an equivalent feature?

share|improve this question
up vote 4 down vote accepted

No version of SQL Server has this. It would be useful (PostreSQL has the same and I like it).

In SQL Server, there are table types (CREATE TYPE), temporary tables (CREATE TABLE #foo) and table variables (DECLARE @var TABLE), but you can't mimic or inherit a table definition using %TYPE or CREATE TABLE .. INHERITS/OF

share|improve this answer
Thanks , But i think it's very perfect data type in oracle (i mean "table%rowtype") , and i don't know why MS SQl have not it – Ali Sarshogh Jul 4 '13 at 7:33
@gbn: Can't the SELECT INTO be used to create a table of the same type as an existing table? – ypercubeᵀᴹ Jul 4 '13 at 8:50
@ypercube: yes, but limited. You have no keys and can't be used in parameters like PG CREATE FUNCTION. It's nowhere as near as useful and I can't see how it replaces the %TYPE concept. – gbn Jul 4 '13 at 8:56

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.