Questions tagged [user-defined-table-type]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
3
votes
1answer
108 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 #...
0
votes
0answers
16 views

Converting User-Defined Datatypes to JSON in Oracle

I'm trying to use User-Defined Datatypes in Oracle to handle flexible fields in an application: CREATE OR REPLACE TYPE udf_typ AS OBJECT ( name VARCHAR2(4000), value VARCHAR2(...
2
votes
2answers
62 views

There is no ALTER TYPE command in SQL Server. However I need to alter a type. What can I do?

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 ...
0
votes
1answer
22 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?
2
votes
1answer
1k 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 ...
4
votes
2answers
4k 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 ...
1
vote
0answers
507 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 ...
1
vote
1answer
127 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 ...
2
votes
2answers
130 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 ...
2
votes
2answers
1k 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 ...
3
votes
1answer
2k 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 ...
0
votes
1answer
656 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 ...
5
votes
1answer
7k 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 ...
18
votes
2answers
4k 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 ...
2
votes
1answer
4k 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 ...