8
Besides SQLCLR, you could also store both values in either XML or JSON (if you are using SQL Server 2016 or newer) and retrieve them as discrete values. Similarly you could simply convert them to VARCHAR and place them, separated by a comma, in a single column (I don't recommend this at all, but it is functionally similar to the other approaches I just ...
6
You would need to use a CLR user defined datatype for this.
It would be very similar to the example code for Point here.
But I don't recommend it. It builds in a requirement for CLR that makes your database less portable (you won't be able to migrate it to Azure SQL database).
Updating the datatype when in use is quite cumbersome too and I can't think of ...
5
As often with such questions, the \set ECHO_HIDDEN on command of psql helps. \dT+ will show the possible values of the enum, if the type in question is an enum. The query behind the output is rather complex, but one can simplify it to fit your needs like
SELECT format_type(t.oid, NULL) AS name,
array_agg(e.enumlabel ORDER BY e.enumsortorder) AS ...
4
I do not believe there is any issue here with regards to the User-Defined Type (UDT) as I am able to execute this successfully:
Please note that I am using the Free version of the SQL# library (which I wrote) to test with:
CREATE TABLE [dbo].[Dic:Table:With&Weird:Name](
[Id] [tinyint] NOT NULL,
[Group_Id] [tinyint] NOT NULL,
[Name] [...
4
You can do something like this,
SELECT true
FROM pg_enum
WHERE enumtypid = pg_typeof('You'::foo)::regtype
FETCH FIRST ROW ONLY;
You can create a simple function that does that too,
CREATE FUNCTION is_enum(x regtype)
RETURNS bool
AS $$
SELECT true
FROM pg_enum
WHERE enumtypid = x
FETCH FIRST ROW ONLY;
$$ LANGUAGE sql
IMMUTABLE;
SELECT is_enum(...
3
Need an efficient routine to pass object name, with or without “[” and “]” brackets, to get ObjectID
Some notes:
You can get rid of PARSENAME and get the object name from your existing sys.objects query since you are using the OBJECT_ID() built-in function to handle getting the object regardless of how it is named.
Getting rid of PARSENAME means that you won't be handling Database names, but the comment at the top of your original code in the question does ...
3
I agree with the comment about providing a minimal, complete and verifiable example of your problem. I'm not sure why you're trying to use CAST/CONVERT when attempting to define a UserType. Simply use the base SystemType.
DROP TABLE IF EXISTS TestUdt;
DROP TYPE IF EXISTS DecimalPrecision5Scale4;
DROP TYPE IF EXISTS DecimalPrecision11Scale2;
GO
CREATE ...
2
SQL Server does not rely on the binary order for its "own" data types. For CLR datatypes you could use the iComparable interface, but as @MattJohnson mentioned, SQL Server ignores it:
http://connect.microsoft.com/SQLServer/feedback/details/252230/sqlclr-provide-the-ability-to-use-icomparable-or-a-similar-mechanism-for-udts
Microsoft does not publish the ...
2
It would be easier to use Object Mapper that is a separate part of the DataStax Java driver - in this case you can easily map UDT and table into case classes. Something like this (I wrote an article on using Object Mapper with Scala - it's too long to past it here completely):
@UDT(name = "scala_udt")
case class UdtCaseClass(id: Integer, @(Field @field)(...
1
User-defined types are supported in Azure SQL Database except those based on a CLR assembly, which is not supported.
1
It makes sense to have a Schema lock (on the UDT itself) when accessing any of its properties or methods, but not when accessing the base VARBINARY value. When accessing the base value, it is simply a VARBINARY set of bytes that does not require any special interpretation and/or handling. But, when accessing any of the properties or methods of the User-...
1
When you use SQL CLR objects across databases, SQL Server will automatically convert them if certain conditions are met (https://technet.microsoft.com/en-us/library/ms178069(v=sql.105).aspx).
In my case, this automatic conversion was taking out the SCH-M locks. I fixed the problem by manually converting the contents of my SQL CLR type to binary, and ...
1
Okay, here is a repro:
USE tempdb;
GO
-- create one table with padding off:
SET ANSI_PADDING OFF;
GO
CREATE TABLE dbo.ap_off(a CHAR(1),
CONSTRAINT ck_apOff CHECK (a IN ('N','Y')));
GO
-- and other with padding on:
SET ANSI_PADDING ON;
GO
CREATE TABLE dbo.ap_on(a CHAR(1),
CONSTRAINT ck_apOn CHECK (a IN ('N','Y')));
GO
-- now, let's test queries
-- with ...
Only top voted, non community-wiki answers of a minimum length are eligible
