Questions tagged [user-defined-table-type]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
-3
votes
0answers
34 views

How do I create User Defined Function in SQL using the AdventureWorks Database [closed]

Using the HumanResources.Employee Table in the Adventureworks Database Create a UDF that accepts BusinessEntityID ( Older Tables have it as ' EmployeeID' )and returns UserLoginID. The UserLoginID is ...
0
votes
1answer
30 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 ...
0
votes
1answer
50 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 ...
0
votes
4answers
37 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....
1
vote
2answers
88 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
2k 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
508 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
135 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
14k 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
613 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
173 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
187 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
917 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
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 ...
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
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 ...