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?

1 Answer 1


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

  • 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 Jul 4, 2013 at 7:33
  • 1
    @gbn: Can't the SELECT INTO be used to create a table of the same type as an existing table? Jul 4, 2013 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, 2013 at 8:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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