1

Create a new database in MS SQL Server 2008 R2 and then create a new table type with the following command

CREATE TYPE typeMyType AS TABLE (ID INT)

Now execute the following query

SELECT OBJECT_NAME (object_id) AS ObjectName, *
FROM   sys.indexes 
WHERE  index_id <= 1
ORDER BY ObjectName

This will show you that an object of type HEAP was created which is fine as the data for typeMyType has to be stored somewhere.
But the object is called TT_typeMyType_01142BA1 in my case.

Question:
Why isn't it called typeMyType and how can I overwrite this obviously server generated name?

Marc
  • 9,012
  • 13
  • 57
  • 72
  • 2
    The correct place to look for the metadata is `sys.types`. – Martin Smith Jan 08 '14 at 14:34
  • Or `sys.table_types` might be more appropriate actually. Shows up in both but only the second one exposes `type_table_object_id` – Martin Smith Jan 08 '14 at 14:52
  • It shows up in other places also. See my answer below. – CRAFTY DBA Jan 08 '14 at 14:56
  • @CRAFTYDBA - The type itself only shows up in `sys.types` and `sys.table_types`. SQL Server creates another internal object in the `sys` schema that shows up in the other places. – Martin Smith Jan 08 '14 at 15:01
  • I looked up the link you provided. So the schema is [sys]. for the table? – CRAFTY DBA Jan 08 '14 at 15:01
  • @CRAFTYDBA - Yes. It looks like it stores metadata about it in a similar way to creating an instance of the type (though in user database and nothing in `sys.partitions`) – Martin Smith Jan 08 '14 at 15:03
  • But is the statistics updated for the object or just a place holder? When you declare an object of type X, it uses tempdb for allocation. – CRAFTY DBA Jan 08 '14 at 15:04
  • Good discussion, Tx Martin! – CRAFTY DBA Jan 08 '14 at 15:05
  • @CRAFTYDBA - Yes agree, This dummy object is just a handy way of storing the various table oriented metadata about columns/indexes/constraints I suppose. – Martin Smith Jan 08 '14 at 15:09
  • OK so I also voted to close this as a duplicate. Too bad I couldn't find the other question first. Also I assume that we can't provide the name for this object as it is always generated by the server. – Marc Jan 08 '14 at 15:25

1 Answers1

1

The table type is listed in several places that can be looked at via the following system tables (sys.sysobjects, sys.indexes, sys.table_types and sys.types).

I can understand that table_types is a subset of types. Therefore, those two places are where to look for my table type AssociativeArray in my AdventureWorks2012 database.

The question I have is why is it looking like a table in sysobjects and sysindexes?

We have not defined any use for the variable yet. However, it looks like a table. It must be how the engine defines meta data for future use.

One take away, does the information in sysobjects and sysindexes get updated a run-time when we declare a variable of type AssociativeArray?

Also, what happens when two SPIDS create the same variable at the same time with different data being inserted?

That is a in-depth engine question that maybe a someone from Microsoft CAT team might know off the top of their head.

I guess I could do some research to find out.

enter image description here

Enclosed is a link stating that table variables use tempdb. That is what I know is a fact. Again, I wonder if the sys.objects and sys.indexes get updated or just place holders.

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

CRAFTY DBA
  • 14,351
  • 4
  • 26
  • 30