Questions tagged [user-defined-type]
User-Defined Types (UDTs) are custom datatypes in Microsoft SQL Server 2005 or newer. UDTs are one of the five SQLCLR object types and rely on the .NET Framework. They are most commonly written in C# or VB.NET.
37
questions
0
votes
1
answer
215
views
Database structure for multiple user types
I am developing a database that will store users of different types. Currently, there are only two types of users, clients and agents. In the future, there may be a new type of user, "...
- 1
0
votes
1
answer
39
views
What is the suggested value for guardrail fields_per_udt_warn_threshold?
Cassandra has added guardrails feature some time ago, bust none of the guardrails have any default value set in the configuration file (see cassandra.yaml). I generally wonder how can a db admin know ...
- 103
1
vote
1
answer
312
views
DB<>Fiddle: Custom type and function (ORA-24344)
I've made a custom version of Oracle's GetVertices() function. The solution involves creating a user-defined type and a function.
Here is the code (working):
CREATE TYPE vertex_type_cust AS object
(
...
- 1,447
-1
votes
3
answers
2k
views
How can I cast to a user-defined type in SQL Server?
SQL Server provides an ability to create a user defined type with CREATE TYPE
CREATE TYPE foo FROM int NOT NULL ;
However, there is no mention how I construct a value with that type. Is this of use ...
- 62.2k
0
votes
1
answer
59
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 ...
3
votes
1
answer
450
views
SQL Server sp_describe_first_result_set: find user defined types
I need to find the column definitions from a stored procedure.The docs for sp_describe_first_result_set show an entry for user_type_name where I'd expect to see my User Defined Types. Sadly, data for ...
- 235
1
vote
2
answers
369
views
Oracle MV requires object type to be defined as FINAL?
I want to create an Oracle 18c materialized view (MV) on a table that has a user-defined datatype called ST_GEOMETRY:
The MV would do a COMPLETE refresh on a schedule — on a local table.
Some info ...
- 1,447
0
votes
4
answers
193
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....
- 119
0
votes
2
answers
123
views
Do user defined data types always cause implicit conversion to occur when fields of those UDDTs are used as predicates?
I have a database (from a vendor application) that for some unknown reason has a UDDT on almost every column, even when the UDDT is logically the same as the underlying type it implements. E.g. a ...
- 35.2k
-1
votes
1
answer
975
views
Unable to declare user-defined variable in MySQL
I'm creating procedure called min_max to find minimum and maximum for every column in a table.
But I'm encountering an error for declaring the variable. See below:
DELIMITER //
DROP PROCEDURE IF ...
- 101
2
votes
1
answer
457
views
How to insert nested UDT types
I have all these case classes :-
case class Role(id:UUID,
roleName:String,
description:Option[String],
isVisible:Boolean,
isDefault:...
- 21
0
votes
1
answer
288
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?
- 35.2k
2
votes
1
answer
411
views
Can't use CAST, Convert with UDT in Sql Server 2014 [closed]
My tables consist of many decimal columns with different precision.
I wanted to standardize the usage of precision by creating several UDTs with predefined precision and use them as column type ...
- 123
3
votes
3
answers
1k
views
How do I create a bounded range type in the SQL Server?
I want to create a bible verse table that has one field representing a range of bible verses. Currently I represent this range as pair of values (VerseBegId and VerseEndId). I want something like ...
- 33
1
vote
0
answers
162
views
PostgreSQL- user-defined types: note sure what internallength/alignment is for linked list type
I'm currently trying to implement at user-defined data type in Postgres in the form of a linked list with each node holding an int and a pointer to the next node, here are my input functions:
// ...
- 11
6
votes
2
answers
1k
views
Determine if user-defined type is ENUM
Is there a way to determine whether or not a user-defined type in PostgreSQL is an ENUM?
Essentially we have the following:
CREATE TYPE foo AS ENUM (
'Sometimes',
'You',
'Wanna',
'Go',
'...
0
votes
1
answer
42
views
Noob question about user accounts
I'm new to this whole database thing. Got a uni project which I was hoping y'all could help with.
So I'm creating a webapp that is essentially an online art gallery store. The database will contain ...
1
vote
1
answer
386
views
Need an efficient routine to pass object name, with or without "[" and "]" brackets, to get ObjectID
Someone in their wisdom created a table called user:
select schema_name(schema_id)
,*
from sys.tables
where type = 'U'
and name like '%user%'
select top 100 *
from usr.user
Msg 156, Level 15, ...
- 15.8k
3
votes
1
answer
328
views
UPDATE STATISTICS and User-Defined Types (UDT) from Assemblies
I have a problem with UPDATE STATISTICS where the column is a user-defined type (UDT) from an assembly.
Step by step:
Assembly:
CREATE ASSEMBLY [UDT.TParams] FROM 0x4D...(stripped) WITH ...
- 223
1
vote
1
answer
2k
views
Can I pass a TABLELIKE parameter to a PostgreSQL FUNCTION/PROCEDURE?
I have a function which returns:
TABLE (id TEXT, collected TIMESTAMPTZ)
In another function, I would like to be able to pass the result of that first function as a parameter. For example:
CREATE ...
- 151
3
votes
1
answer
3k
views
Supertypes and subtypes
I am trying to design a database that has supertype/subtypes. I have the following users:
- basic user
- business user
- administrator (super user).
All three users share a common table (users) ...
- 203
2
votes
2
answers
257
views
SQLCLR User-Defined Type (UDT) SCH-M blocking
I’m hoping someone can provide some guidance on a problem I’m having with schema locking on C# SQLCLR UDT’s. I’ve been on the Internet for an extended period of time researching my problem without ...
- 23.7k
1
vote
0
answers
2k
views
Proper way to handle NULL (empty) values for user-defined table types passed as params to stored procedure
My stored procedures accepts table value parameters for querying data.
Usually those are custom user-defined table types for where clause. Business logic of stored procedures push me to these @...
- 2,403
5
votes
1
answer
9k
views
How to list the permissions on User-Defined Table Types?
I am using some user-defined table types. They are very useful.
you can see information about them by running this simple script:
SELECT o.* from sys.table_types o
I have a procedure that shows me ...
- 15.8k
19
votes
1
answer
19k
views
Multiple User Types - DB Design Advice
I'm developing a web application that will support user authentication with corresponding roles for each user. Also, my users can be of different types and have different fields associated with them. ...
- 293
7
votes
1
answer
23k
views
Operand type clash: my first User Defined Type with list of Integers, how to test via SSMS
I found this wonderful code here
CREATE TYPE [dbo].[StringList] AS TABLE(
[Item] [NVARCHAR](MAX) NULL
);
GO
CREATE PROCEDURE [dbo].[sp_UseStringList]
@list StringList ...
- 1,365
0
votes
1
answer
294
views
User defined data types causing implicit conversions
user defined datatypes, i'm looking at some execution plans and seeing implicit conversions happening in the background when a var is declared of type user defined data type and pulling a value into ...
- 319
3
votes
2
answers
3k
views
User defined table type with a check constraint
I have a user defined table type where I want to make sure that a certain column is within a given range. This range however is defined in another table. So I thought I can use a check constraint on ...
- 2,877
7
votes
1
answer
1k
views
Understanding MS SQL Server Date Types
Consider the following:
declare @dt datetime, @dt2 datetime2, @d date
set @dt = '2013-01-01'
set @dt2 = '2013-01-01'
set @d = '2013-01-01'
select convert(varbinary, @dt) as dt,
convert(...
5
votes
1
answer
950
views
PostgresSQL: Get single attribute of UDT in SELECT statement
I created a user-defined type in a PostgreSQL 9.2 database and am trying in the SELECT statement to get only an attribute of the UDT. However, I don't seem to get my code to work.
Creation of type ...
- 589
34
votes
7
answers
63k
views
Display user-defined types and their details
I've created a few new UDTs in PostgreSQL. However, now I have two problems:
how to see which UDTs have been defined?
how to see the columns defined within these UDTs?
Unfortunately, I couldn't ...
- 589
1
vote
2
answers
603
views
Postgresql: type_modifier in UDT
In the manual for creating UDTs in postgresql I found the following statement:
PostgreSQL allows user-defined types to take one or more simple constants or identifiers as modifiers. However, this ...
- 589
9
votes
2
answers
9k
views
MySQL Permissions Necessary To Reset Password
On my team there is a desire for some users to periodically update their MySQL password.
Our MySQL server is shared between a few projects (and certain users are assigned to certain projects). If I'm ...
- 192
3
votes
2
answers
246
views
How can I mimic functionality of SQL Server `TYPE` in Oracle?
I apologize if question title is vague, but I was not able to formulate it better. What I'd like to have is an alternative to TSQL CREATE TYPE, so I can use it in column definition.
For instance, I ...
- 8,970
4
votes
3
answers
33k
views
What's the difference between DBA and DA?
In categorization of users I have seen two different categorizations:
First one:
End user
DBA
DBP
and Second one:
End user
DBA
DA
What's the difference between DA and DBA ?
- 143
5
votes
2
answers
8k
views
Does mysql SUPER privilege include ALL?
I'm granting the following rights to a user:
grant super on . to 'user'@'host';
does this include the rights on dbname given by:
grant all on dbname.* to 'user'@'host';
thank you
- 733
14
votes
1
answer
14k
views
Why does SQL*PLUS need a slash after CREATE TYPE?
I just had the problem that I defined a Type and tested it in TOAD and all was OK. But running under SQL*PLUS it threw an error.
Example:
CREATE OR REPLACE TYPE MyType AS OBJECT (
Item1 NUMBER,
...
- 12.2k