Questions tagged [user-defined-table-type]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1
vote
2answers
26 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 ...
4
votes
2answers
1k 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 #...
4
votes
2answers
285 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 ...
0
votes
1answer
94 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
2k 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 ...
5
votes
2answers
11k 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
585 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
146 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
182 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 ...
3
votes
2answers
2k 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
776 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
8k 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
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 ...
2
votes
1answer
5k 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 ...