Questions tagged [user-defined-table-type]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
-1 votes
1 answer
45 views

Can you index an Oracle UDT?

Can you index an Oracle User-Defined Table Type? CREATE OR REPLACE TYPE MY_OBJECT AS OBJECT(ID int, key varchar2(5), value varchar2(10)); CREATE TYPE MY_OBJECT_TABLE AS TABLE OF MY_OBJECT; Given the ...
user avatar
  • 101
0 votes
1 answer
47 views

Calling function that takes user defined table type as input, in a select query, with data in the table type from the table selected from

Hopefully the title makes some amount of sense. Basically I have a scalar function that takes a user defined table as input, f_doCalc(@MyUserDefinedTableType). The table type has 2 columns, theValue ...
user avatar
  • 249
0 votes
1 answer
54 views

I am wanting to use UDTT in my database, but we have CLR turned off, and according to the docs for create type, all types require a CLR assembly

Is this true? Or can I use User defined Table Types with having CLR off? (I.e. they don't require the use of CLR) If they do require CLR, is there an alternative I can use for my stored procedure ...
user avatar
0 votes
4 answers
43 views

use a string array without CREATE privileges

I have a set of tables where I need to determine if a particular set of about 1000 row IDs are present in one specific field in each table. select 'Accounts', row_id Acct_id, pr_postn_id from schema....
user avatar
  • 109
1 vote
2 answers
163 views

Temporarily Turn Off Table Type Reference Checking On A Development Database

During my database design process I have begun to add Table Types to the database and have run into the need to alter a table type. It is painfully clear that one cannot remove or drop the table type ...
user avatar
  • 377
4 votes
2 answers
3k views

how to script out the user defined table types?

I can Get name and definition of all table types using either of the following scripts: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED IF OBJECT_ID('TEMPDB..#RADHE') IS NOT NULL DROP TABLE #...
user avatar
4 votes
2 answers
619 views

ALTER TYPE Not Available So How To Alter a Type?

I know there are other questions out there regarding this issue, but this one is really simple so I'm not looking for a complicated answer! So I have this script: IF EXISTS (SELECT * FROM sys.types ...
user avatar
  • 161
0 votes
1 answer
161 views

Are user defined types (UDTs) available in an Azure SQL database managed instance?

Is it possible to create user defined types and user defined table types in an Azure SQL database managed instance?
user avatar
  • 24.2k
2 votes
1 answer
3k views

SQL Server - Nonclustered Index with Include on User Defined Table Type?

I was working with in-memory User Defined Table Types and got stuck on creating indexes on them. Here's what I am trying to do: CREATE TYPE dbo.CustomersTbl AS TABLE ( customerID int NOT ...
user avatar
  • 123
6 votes
2 answers
15k views

How to do ALTER TABLE TYPE efficiently

Unlike tables, a table type cannot be altered by a simple command. I need to do the following: Rename existing table type Create the new table type Open each affected objects (i.e. ALTER SP) and ...
user avatar
  • 255
1 vote
0 answers
635 views

User-Defined Table Type script with constraints (SSMS 2016)

How can one script a User-Defined Table Type with default constraints in SQL Server Management Studio (SSMS) 2016? I can generate a script, but it does not include these constraints. One cannot ...
user avatar
  • 121
1 vote
1 answer
179 views

TSQL Stored Procedure rollback without any warning/error?

This is more of a curiosity, but I noticed a strange behavior today. Note - there are no explicitly defined transactions in this code. I have a stored procedure that takes in a user defined table ...
user avatar
2 votes
2 answers
189 views

JOIN on LIKE when input udtt count is > 1

This query accepts a User-Defined Table Type variable and then joins that with a table. If the input is a single record, I want the join to be LIKE '%'+input+'%', otherwise it should be =input. This ...
user avatar
3 votes
2 answers
3k views

View Default value for User Defined Table Type columns in SQL Server

I have a user defined table type in SQL Server 2014 that I need to drop and recreate. However, if I use the script from SSMS, the default value constraints that are on some of the columns, will not be ...
user avatar
  • 563
3 votes
1 answer
3k views

Why are Column Defaults on my User-Defined Table Type (UDTT) not being honored?

I create a table type in SQL: CREATE TYPE [dbo].[MyObject_TableType] AS TABLE ( [Name] VARCHAR(MAX) DEFAULT '', [Index] VARCHAR(MAX) DEFAULT '' ) I build a DataTable and populate it with one ...
user avatar
  • 770
0 votes
1 answer
1k views

Grant reference on type

What does GRANT REFERENCES ON TYPE::dbo.myType TO public exactly mean? I found this statement together with every single user defined type we have. I wonder if this is useful or not. AFAIK this ...
user avatar
  • 4,595
5 votes
1 answer
9k views

Getting error "must declare the scalar variable" in SQL function even though it is declared

I am altering function like: ALTER FUNCTION [dbo].[fn_CalculateListing](@Listing TypeListingDatePrice READONLY) RETURNS TypePreviousListingResult AS BEGIN DECLARE @tbl_ListingDateDetails ...
user avatar
  • 517
18 votes
2 answers
5k views

Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?

I am trying to understand why using a table variable is preventing the optimizer from using an index seek and then bookmark lookup versus an index scan. Populating the table: CREATE TABLE dbo.Test ...
user avatar
  • 2,549
2 votes
1 answer
6k views

How to Retrieve value from a User-Defined Table Type (UDTT)

Goal: I want to reach and display the value of @data inside of [wwtest]. Problem: The UDTT is from the database [test] and I receive an error message: The type name 'test.dbo.tvf_id' contains ...
user avatar
  • 331