I have a vendor supplied orders table that tracks the fee types associated
with an order. The allowable fee types are A, B, C, D, and E. These fee
types can be in anyone of the six fee code fields. In the corresponding fee
amount field is the fee amount. The application allows NULL fee amounts even
when there is a corresponding fee code entered. See OrderID 7 where fee code
'E' in field FEE_6_CD has a NULL value in FEE_6_AMT.
For the purposes of my SELECT I want to consolidate and sum all fee types
into 2 master types:
AB_Consolidation-All A and B fee types
CDE_Consolidation--All C, D, and E fee types.
See CREATE TABLE and INSERT statements as well as expected results below.
Thanks to anyone who could help.
CREATE TABLE [dbo].[Orders] (
[OrderID] [smallint],
[OrderType] [char] (4),
[OrderDate] [Datetime],
[FEE_1_CD] [char] (1)NULL,
[FEE_2_CD] [char] (1)NULL,
[FEE_3_CD] [char] (1)NULL,
[FEE_4_CD] [char] (1)NULL,
[FEE_5_CD] [char] (1)NULL,
[FEE_6_CD] [char] (1)NULL,
[FEE_1_AMT] [decimal](18, 2) NULL,
[FEE_2_AMT] [decimal](18, 2) NULL,
[FEE_3_AMT] [decimal](18, 2) NULL,
[FEE_4_AMT] [decimal](18, 2) NULL,
[FEE_5_AMT] [decimal](18, 2) NULL,
[FEE_6_AMT] [decimal](18, 2) NULL
) ON [PRIMARY]
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(1,'Buy','20060117','A','C',NULL,NULL,NU
LL,NULL,8,9,NULL,NULL,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(2,'Buy','20060117',NULL,NULL,'D',NULL,N
ULL,NULL,NULL,NULL,11,NULL,NULL,NULL
)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(3,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'B',NULL,NULL,NULL,NULL,NULL,1
5)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(4,'Buy','20060116','A',NULL,NULL,'B',NU
LL,NULL,18,NUll,NULL,85,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(5,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'D',NULL,NULL,NULL,NULL,NULL,5
4)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES (6,'Buy','20060116','A','C','D','B',NULL
,NULL,8,9,11,12,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES (7,'Buy','20060117','A','C',NULL,NULL,'D
','E',3,99,NULL,NULL,14,NULL)
Expect Results:
OrderID,OrderType,OrderDate,ABConsolidat
ion,DEConsolidation
1,Buy,20060117,8,9
2,Buy,20060117,0,11
3,Sell,20060117,15,0
4,Buy,20060116,103,0
5,Sell,20060117,0,54
6,Buy,20060116,20,20
7,Buy,20060117,3,113
Partial SELECT statement
SELECT
OrderID,
OrderType,
OrderDate,
Perform calcs here AS AB_Consolidation,
Perform calcs here AS CDE_Consolidation
FROM ORDERSAs you might have noticed, the vendor table schema is mediocre. In any case,
you can use a derived table to transpose the data & get the results you
need. If this table is being used in multiple areas, a view might be better.
You can create one as:
ALTER VIEW vw ( order_id, order_type, order_date, seq, code, amount )
AS SELECT OrderID, OrderType, OrderDate, seq,
CASE seq WHEN 1 THEN FEE_1_CD
WHEN 2 THEN FEE_2_CD
WHEN 3 THEN FEE_3_CD
WHEN 4 THEN FEE_4_CD
WHEN 5 THEN FEE_5_CD
WHEN 6 THEN FEE_6_CD
END,
CASE seq WHEN 1 THEN FEE_1_AMT
WHEN 2 THEN FEE_2_AMT
WHEN 3 THEN FEE_3_AMT
WHEN 4 THEN FEE_4_AMT
WHEN 5 THEN FEE_5_AMT
WHEN 6 THEN FEE_6_AMT
END
FROM Orders, ( SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6) S ( seq )
GO
Now you can do the query for consolidation like:
SELECT order_id, order_type, order_date,
SUM( CASE WHEN code IN ( 'A', 'B' )
THEN Amt ELSE 0 END ) AS "AB_",
SUM( CASE WHEN code IN ( 'C', 'D', 'E' )
THEN Amt ELSE 0 END ) AS "CDE_"
FROM vw
GROUP BY order_id, order_type, order_date,
ORDER BY order_id ;
Anith|||cant you just do this, or am I missing something?
SELECT
OrderID,
OrderType,
OrderDate,
coalesce(FEE_1_AMT,0)+coalesce(FEE_2_AMT
,0) AS AB_Consolidation,
coalesce(FEE_3_AMT,0) + coalesce(FEE_4_AMT,0) + coalesce(FEE_5_AMT,0) AS
CDE_Consolidation
FROM ORDERS
Basically, just add the collumns using coaless to handle the nulls.
"Terri" <terri@.cybernets.com> wrote in message
news:dqovlt$v0d$2@.reader2.nmix.net...
> I have a vendor supplied orders table that tracks the fee types associated
> with an order. The allowable fee types are A, B, C, D, and E. These fee
> types can be in anyone of the six fee code fields. In the corresponding
fee
> amount field is the fee amount. The application allows NULL fee amounts
even
> when there is a corresponding fee code entered. See OrderID 7 where fee
code
> 'E' in field FEE_6_CD has a NULL value in FEE_6_AMT.
> For the purposes of my SELECT I want to consolidate and sum all fee types
> into 2 master types:
> AB_Consolidation-All A and B fee types
> CDE_Consolidation--All C, D, and E fee types.
> See CREATE TABLE and INSERT statements as well as expected results below.
> Thanks to anyone who could help.
>
> CREATE TABLE [dbo].[Orders] (
> [OrderID] [smallint],
> [OrderType] [char] (4),
> [OrderDate] [Datetime],
> [FEE_1_CD] [char] (1)NULL,
> [FEE_2_CD] [char] (1)NULL,
> [FEE_3_CD] [char] (1)NULL,
> [FEE_4_CD] [char] (1)NULL,
> [FEE_5_CD] [char] (1)NULL,
> [FEE_6_CD] [char] (1)NULL,
> [FEE_1_AMT] [decimal](18, 2) NULL,
> [FEE_2_AMT] [decimal](18, 2) NULL,
> [FEE_3_AMT] [decimal](18, 2) NULL,
> [FEE_4_AMT] [decimal](18, 2) NULL,
> [FEE_5_AMT] [decimal](18, 2) NULL,
> [FEE_6_AMT] [decimal](18, 2) NULL
> ) ON [PRIMARY]
>
> INSERT INTO ORDERS
>
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
> (1,'Buy','20060117','A','C',NULL,NULL,NU
LL,NULL,8,9,NULL,NULL,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(2,'Buy','20060117',NULL,NULL,'D',NULL,N
ULL,NULL,NULL,NULL,11,NULL,NULL,NULL[col
or=darkred]
> )
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(3,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'B',NULL,NULL,NULL,NULL,NULL,1[col
or=darkred]
> 5)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
> (4,'Buy','20060116','A',NULL,NULL,'B',NU
LL,NULL,18,NUll,NULL,85,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(5,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'D',NULL,NULL,NULL,NULL,NULL,5[col
or=darkred]
> 4)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES (6,'Buy','20060116','A','C','D','B',NULL
,NULL,8,9,11,12,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES[/color]
(7,'Buy','20060117','A','C',NULL,NULL,'D
','E',3,99,NULL,NULL,14,NULL)red">
>
> Expect Results:
> OrderID,OrderType,OrderDate,ABConsolidat
ion,DEConsolidation
> 1,Buy,20060117,8,9
> 2,Buy,20060117,0,11
> 3,Sell,20060117,15,0
> 4,Buy,20060116,103,0
> 5,Sell,20060117,0,54
> 6,Buy,20060116,20,20
> 7,Buy,20060117,3,113
> Partial SELECT statement
> SELECT
> OrderID,
> OrderType,
> OrderDate,
> Perform calcs here AS AB_Consolidation,
> Perform calcs here AS CDE_Consolidation
> FROM ORDERS
>
>|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OPVJGPUHGHA.2668@.tk2msftngp13.phx.gbl...
> cant you just do this, or am I missing something?
> SELECT
> OrderID,
> OrderType,
> OrderDate,
> coalesce(FEE_1_AMT,0)+coalesce(FEE_2_AMT
,0) AS AB_Consolidation,
> coalesce(FEE_3_AMT,0) + coalesce(FEE_4_AMT,0) + coalesce(FEE_5_AMT,0) AS
> CDE_Consolidation
> FROM ORDERS
> Basically, just add the collumns using coaless to handle the nulls.
Fee type A can be in any of the 6 fee fields so I don't think that will
work. I'm looking at Anith's solution to see if that will work.
thanks|||>
> Fee type A can be in any of the 6 fee fields so I don't think that will
> work. I'm looking at Anith's solution to see if that will work.
>
Ahhh... I misunderstood that in your first post. The problem is much more
interesting than I thought.
I like how Anith is getting 6 fields from one row to 6. I'll have to
remember this join technique in case I ever run into a similar situation.
If your final code differs from his, please let us know what you come up
with.
Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
Cons w/ UDTs?
I came across a database at my company that contained many columns in many
tables defined as UDTs. Upon further investigation, there were no rules
associated with the UDTs and they were not explicitly bound.
Upon even further investigation, the person who created the database said
they were using a modeling tool to test the quality/validity of the data
model and the tool complained that no data domains were present and suggested
the utilization of UDTs.
After reading more about them, i was intrigued and can definitely see their
value. My question is are there any cons to using them? For example, can
over usage cause performance issues? What issues are encountered if a rule
or data type needs to be altered? Etc... Thanks in advance.parchk,
Are you using SQL Server 2000 or 2005? Things are changing.
User-Defined Types in 2000 and 2005 can simply be an alias for describing
data domains, but for both versions of SQL Server, the gotcha is: "When you
create a user-defined type, it is local to a single database." If you don't
care about cross-database use of the UDT, not even in tempdb, then no
problem. (But even then, 2005 offers some options that 2000 does not.)
(SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
(SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
Then you can use them in your code in the proper database as if they were a
native datatype.
I would guess that this is the type of UDT you have in your database. I
used to use them heavily, but decided that the value of using UDTs as
documentation of data domains was to small to repay the hassle. (And, when
you generate scripts you tend to get the base datatypes scripted out for
you.)
--
With 2005, there is now "CLR User-Defined Types". This is a much richer and
more complex type of UDT, where you define the type and the operations that
work with the datatype. If you need this, you need to read about them in
detail, starting in the SQL Server 2005 Books Online.
RLF
"parchk" <parchk@.discussions.microsoft.com> wrote in message
news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
>I came across a database at my company that contained many columns in many
> tables defined as UDTs. Upon further investigation, there were no rules
> associated with the UDTs and they were not explicitly bound.
> Upon even further investigation, the person who created the database said
> they were using a modeling tool to test the quality/validity of the data
> model and the tool complained that no data domains were present and
> suggested
> the utilization of UDTs.
> After reading more about them, i was intrigued and can definitely see
> their
> value. My question is are there any cons to using them? For example, can
> over usage cause performance issues? What issues are encountered if a
> rule
> or data type needs to be altered? Etc... Thanks in advance.|||If you can imagine a db system that is 'strongly' typed, where
you can define types natively instead of going 'outside' the system
and where any type can be the type of a 'variable' not just
a value then I have a surprise for you :-)
www.beyondsql.blogspot.com
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eMMF6yO6HHA.2380@.TK2MSFTNGP02.phx.gbl...
> parchk,
> Are you using SQL Server 2000 or 2005? Things are changing.
> User-Defined Types in 2000 and 2005 can simply be an alias for describing
> data domains, but for both versions of SQL Server, the gotcha is: "When
> you create a user-defined type, it is local to a single database." If you
> don't care about cross-database use of the UDT, not even in tempdb, then
> no problem. (But even then, 2005 offers some options that 2000 does not.)
> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
> Then you can use them in your code in the proper database as if they were
> a native datatype.
> I would guess that this is the type of UDT you have in your database. I
> used to use them heavily, but decided that the value of using UDTs as
> documentation of data domains was to small to repay the hassle. (And,
> when you generate scripts you tend to get the base datatypes scripted out
> for you.)
> --
> With 2005, there is now "CLR User-Defined Types". This is a much richer
> and more complex type of UDT, where you define the type and the operations
> that work with the datatype. If you need this, you need to read about
> them in detail, starting in the SQL Server 2005 Books Online.
> RLF
>
> "parchk" <parchk@.discussions.microsoft.com> wrote in message
> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
>>I came across a database at my company that contained many columns in many
>> tables defined as UDTs. Upon further investigation, there were no rules
>> associated with the UDTs and they were not explicitly bound.
>> Upon even further investigation, the person who created the database said
>> they were using a modeling tool to test the quality/validity of the data
>> model and the tool complained that no data domains were present and
>> suggested
>> the utilization of UDTs.
>> After reading more about them, i was intrigued and can definitely see
>> their
>> value. My question is are there any cons to using them? For example,
>> can
>> over usage cause performance issues? What issues are encountered if a
>> rule
>> or data type needs to be altered? Etc... Thanks in advance.
>|||Hi Russell,
We are using 2005. Thanks for your response. For databases that make heavy
use of UDTs to define data domains, is there any performance impact? The
volume will not be high; however, the database is involved with replication.
"Russell Fields" wrote:
> parchk,
> Are you using SQL Server 2000 or 2005? Things are changing.
> User-Defined Types in 2000 and 2005 can simply be an alias for describing
> data domains, but for both versions of SQL Server, the gotcha is: "When you
> create a user-defined type, it is local to a single database." If you don't
> care about cross-database use of the UDT, not even in tempdb, then no
> problem. (But even then, 2005 offers some options that 2000 does not.)
> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
> Then you can use them in your code in the proper database as if they were a
> native datatype.
> I would guess that this is the type of UDT you have in your database. I
> used to use them heavily, but decided that the value of using UDTs as
> documentation of data domains was to small to repay the hassle. (And, when
> you generate scripts you tend to get the base datatypes scripted out for
> you.)
> --
> With 2005, there is now "CLR User-Defined Types". This is a much richer and
> more complex type of UDT, where you define the type and the operations that
> work with the datatype. If you need this, you need to read about them in
> detail, starting in the SQL Server 2005 Books Online.
> RLF
>
> "parchk" <parchk@.discussions.microsoft.com> wrote in message
> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
> >I came across a database at my company that contained many columns in many
> > tables defined as UDTs. Upon further investigation, there were no rules
> > associated with the UDTs and they were not explicitly bound.
> >
> > Upon even further investigation, the person who created the database said
> > they were using a modeling tool to test the quality/validity of the data
> > model and the tool complained that no data domains were present and
> > suggested
> > the utilization of UDTs.
> >
> > After reading more about them, i was intrigued and can definitely see
> > their
> > value. My question is are there any cons to using them? For example, can
> > over usage cause performance issues? What issues are encountered if a
> > rule
> > or data type needs to be altered? Etc... Thanks in advance.
>
>|||So overall would you recommend extensive utilization of UDTs for defining
data domains and/or the other capabilites of UDTs?
"Russell Fields" wrote:
> parchk,
> Are you using SQL Server 2000 or 2005? Things are changing.
> User-Defined Types in 2000 and 2005 can simply be an alias for describing
> data domains, but for both versions of SQL Server, the gotcha is: "When you
> create a user-defined type, it is local to a single database." If you don't
> care about cross-database use of the UDT, not even in tempdb, then no
> problem. (But even then, 2005 offers some options that 2000 does not.)
> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
> Then you can use them in your code in the proper database as if they were a
> native datatype.
> I would guess that this is the type of UDT you have in your database. I
> used to use them heavily, but decided that the value of using UDTs as
> documentation of data domains was to small to repay the hassle. (And, when
> you generate scripts you tend to get the base datatypes scripted out for
> you.)
> --
> With 2005, there is now "CLR User-Defined Types". This is a much richer and
> more complex type of UDT, where you define the type and the operations that
> work with the datatype. If you need this, you need to read about them in
> detail, starting in the SQL Server 2005 Books Online.
> RLF
>
> "parchk" <parchk@.discussions.microsoft.com> wrote in message
> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
> >I came across a database at my company that contained many columns in many
> > tables defined as UDTs. Upon further investigation, there were no rules
> > associated with the UDTs and they were not explicitly bound.
> >
> > Upon even further investigation, the person who created the database said
> > they were using a modeling tool to test the quality/validity of the data
> > model and the tool complained that no data domains were present and
> > suggested
> > the utilization of UDTs.
> >
> > After reading more about them, i was intrigued and can definitely see
> > their
> > value. My question is are there any cons to using them? For example, can
> > over usage cause performance issues? What issues are encountered if a
> > rule
> > or data type needs to be altered? Etc... Thanks in advance.
>
>|||Parchk,
The 'alias' form of UDTs should have absolutely no performance overhead. It
is really just a naming / documentation scheme.
In terms of replication (I am not a replication expert) I would say that you
want to make sure that the UDTs are also replicated or created with the same
id on the replicated database. And even that is probably not completely
necessary, since the underlying datatypes are knows on both servers.
(Perhaps someone who does replication a lot, can add a comment.)
RLF
"parchk" <parchk@.discussions.microsoft.com> wrote in message
news:4512EE17-B439-46FE-9504-86F7BD01A882@.microsoft.com...
> Hi Russell,
> We are using 2005. Thanks for your response. For databases that make
> heavy
> use of UDTs to define data domains, is there any performance impact? The
> volume will not be high; however, the database is involved with
> replication.
> "Russell Fields" wrote:
>> parchk,
>> Are you using SQL Server 2000 or 2005? Things are changing.
>> User-Defined Types in 2000 and 2005 can simply be an alias for describing
>> data domains, but for both versions of SQL Server, the gotcha is: "When
>> you
>> create a user-defined type, it is local to a single database." If you
>> don't
>> care about cross-database use of the UDT, not even in tempdb, then no
>> problem. (But even then, 2005 offers some options that 2000 does not.)
>> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
>> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
>> Then you can use them in your code in the proper database as if they were
>> a
>> native datatype.
>> I would guess that this is the type of UDT you have in your database. I
>> used to use them heavily, but decided that the value of using UDTs as
>> documentation of data domains was to small to repay the hassle. (And,
>> when
>> you generate scripts you tend to get the base datatypes scripted out for
>> you.)
>> --
>> With 2005, there is now "CLR User-Defined Types". This is a much richer
>> and
>> more complex type of UDT, where you define the type and the operations
>> that
>> work with the datatype. If you need this, you need to read about them in
>> detail, starting in the SQL Server 2005 Books Online.
>> RLF
>>
>> "parchk" <parchk@.discussions.microsoft.com> wrote in message
>> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
>> >I came across a database at my company that contained many columns in
>> >many
>> > tables defined as UDTs. Upon further investigation, there were no
>> > rules
>> > associated with the UDTs and they were not explicitly bound.
>> >
>> > Upon even further investigation, the person who created the database
>> > said
>> > they were using a modeling tool to test the quality/validity of the
>> > data
>> > model and the tool complained that no data domains were present and
>> > suggested
>> > the utilization of UDTs.
>> >
>> > After reading more about them, i was intrigued and can definitely see
>> > their
>> > value. My question is are there any cons to using them? For example,
>> > can
>> > over usage cause performance issues? What issues are encountered if a
>> > rule
>> > or data type needs to be altered? Etc... Thanks in advance.
>>|||Parchk,
It is handy for defining things that you want to be the same everywhere.
For example:
CREATE TYPE SSN FROM varchar(11) NOT NULL
If you use this everywhere a Social Security Number is needed, then the
columns and variables will always be 11-characters, which leaves room for
the dashes, eg. 123-45-6789. This helps avoid having some places coded for
varchar(9), which is intended for 123456789 without dashes, but might wind
up holding 123-45-67 which, of course, is invalid.
The thing that moved me away from using them was the cross-database issues.
These are describe in the 2005 Books Online topic: "Using User-defined Types
Across Databases". It seems, without testing, like it is a little more
robust than in earlier versions, but there are still things to be dealt
with.
I switched over to using class names for my columns, which has served me
well in the years since then. e.g.
Policy_Holder_SocialSecurityNumber
Mate_SocialSecurityNumber
Business_PhoneNumber
Person_FamilyName
In these examples the final token of the column (or variable) name is the
data class. This does not, of course, enforce that everyone defines the
same datatype for the class names. Documentation and review are needed to
catch errors.
FWIW,
RLF
"parchk" <parchk@.discussions.microsoft.com> wrote in message
news:6B7E0FAD-0EA4-46C7-98AE-825692FC1543@.microsoft.com...
> So overall would you recommend extensive utilization of UDTs for defining
> data domains and/or the other capabilites of UDTs?
> "Russell Fields" wrote:
>> parchk,
>> Are you using SQL Server 2000 or 2005? Things are changing.
>> User-Defined Types in 2000 and 2005 can simply be an alias for describing
>> data domains, but for both versions of SQL Server, the gotcha is: "When
>> you
>> create a user-defined type, it is local to a single database." If you
>> don't
>> care about cross-database use of the UDT, not even in tempdb, then no
>> problem. (But even then, 2005 offers some options that 2000 does not.)
>> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
>> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
>> Then you can use them in your code in the proper database as if they were
>> a
>> native datatype.
>> I would guess that this is the type of UDT you have in your database. I
>> used to use them heavily, but decided that the value of using UDTs as
>> documentation of data domains was to small to repay the hassle. (And,
>> when
>> you generate scripts you tend to get the base datatypes scripted out for
>> you.)
>> --
>> With 2005, there is now "CLR User-Defined Types". This is a much richer
>> and
>> more complex type of UDT, where you define the type and the operations
>> that
>> work with the datatype. If you need this, you need to read about them in
>> detail, starting in the SQL Server 2005 Books Online.
>> RLF
>>
>> "parchk" <parchk@.discussions.microsoft.com> wrote in message
>> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
>> >I came across a database at my company that contained many columns in
>> >many
>> > tables defined as UDTs. Upon further investigation, there were no
>> > rules
>> > associated with the UDTs and they were not explicitly bound.
>> >
>> > Upon even further investigation, the person who created the database
>> > said
>> > they were using a modeling tool to test the quality/validity of the
>> > data
>> > model and the tool complained that no data domains were present and
>> > suggested
>> > the utilization of UDTs.
>> >
>> > After reading more about them, i was intrigued and can definitely see
>> > their
>> > value. My question is are there any cons to using them? For example,
>> > can
>> > over usage cause performance issues? What issues are encountered if a
>> > rule
>> > or data type needs to be altered? Etc... Thanks in advance.
>>
tables defined as UDTs. Upon further investigation, there were no rules
associated with the UDTs and they were not explicitly bound.
Upon even further investigation, the person who created the database said
they were using a modeling tool to test the quality/validity of the data
model and the tool complained that no data domains were present and suggested
the utilization of UDTs.
After reading more about them, i was intrigued and can definitely see their
value. My question is are there any cons to using them? For example, can
over usage cause performance issues? What issues are encountered if a rule
or data type needs to be altered? Etc... Thanks in advance.parchk,
Are you using SQL Server 2000 or 2005? Things are changing.
User-Defined Types in 2000 and 2005 can simply be an alias for describing
data domains, but for both versions of SQL Server, the gotcha is: "When you
create a user-defined type, it is local to a single database." If you don't
care about cross-database use of the UDT, not even in tempdb, then no
problem. (But even then, 2005 offers some options that 2000 does not.)
(SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
(SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
Then you can use them in your code in the proper database as if they were a
native datatype.
I would guess that this is the type of UDT you have in your database. I
used to use them heavily, but decided that the value of using UDTs as
documentation of data domains was to small to repay the hassle. (And, when
you generate scripts you tend to get the base datatypes scripted out for
you.)
--
With 2005, there is now "CLR User-Defined Types". This is a much richer and
more complex type of UDT, where you define the type and the operations that
work with the datatype. If you need this, you need to read about them in
detail, starting in the SQL Server 2005 Books Online.
RLF
"parchk" <parchk@.discussions.microsoft.com> wrote in message
news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
>I came across a database at my company that contained many columns in many
> tables defined as UDTs. Upon further investigation, there were no rules
> associated with the UDTs and they were not explicitly bound.
> Upon even further investigation, the person who created the database said
> they were using a modeling tool to test the quality/validity of the data
> model and the tool complained that no data domains were present and
> suggested
> the utilization of UDTs.
> After reading more about them, i was intrigued and can definitely see
> their
> value. My question is are there any cons to using them? For example, can
> over usage cause performance issues? What issues are encountered if a
> rule
> or data type needs to be altered? Etc... Thanks in advance.|||If you can imagine a db system that is 'strongly' typed, where
you can define types natively instead of going 'outside' the system
and where any type can be the type of a 'variable' not just
a value then I have a surprise for you :-)
www.beyondsql.blogspot.com
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eMMF6yO6HHA.2380@.TK2MSFTNGP02.phx.gbl...
> parchk,
> Are you using SQL Server 2000 or 2005? Things are changing.
> User-Defined Types in 2000 and 2005 can simply be an alias for describing
> data domains, but for both versions of SQL Server, the gotcha is: "When
> you create a user-defined type, it is local to a single database." If you
> don't care about cross-database use of the UDT, not even in tempdb, then
> no problem. (But even then, 2005 offers some options that 2000 does not.)
> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
> Then you can use them in your code in the proper database as if they were
> a native datatype.
> I would guess that this is the type of UDT you have in your database. I
> used to use them heavily, but decided that the value of using UDTs as
> documentation of data domains was to small to repay the hassle. (And,
> when you generate scripts you tend to get the base datatypes scripted out
> for you.)
> --
> With 2005, there is now "CLR User-Defined Types". This is a much richer
> and more complex type of UDT, where you define the type and the operations
> that work with the datatype. If you need this, you need to read about
> them in detail, starting in the SQL Server 2005 Books Online.
> RLF
>
> "parchk" <parchk@.discussions.microsoft.com> wrote in message
> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
>>I came across a database at my company that contained many columns in many
>> tables defined as UDTs. Upon further investigation, there were no rules
>> associated with the UDTs and they were not explicitly bound.
>> Upon even further investigation, the person who created the database said
>> they were using a modeling tool to test the quality/validity of the data
>> model and the tool complained that no data domains were present and
>> suggested
>> the utilization of UDTs.
>> After reading more about them, i was intrigued and can definitely see
>> their
>> value. My question is are there any cons to using them? For example,
>> can
>> over usage cause performance issues? What issues are encountered if a
>> rule
>> or data type needs to be altered? Etc... Thanks in advance.
>|||Hi Russell,
We are using 2005. Thanks for your response. For databases that make heavy
use of UDTs to define data domains, is there any performance impact? The
volume will not be high; however, the database is involved with replication.
"Russell Fields" wrote:
> parchk,
> Are you using SQL Server 2000 or 2005? Things are changing.
> User-Defined Types in 2000 and 2005 can simply be an alias for describing
> data domains, but for both versions of SQL Server, the gotcha is: "When you
> create a user-defined type, it is local to a single database." If you don't
> care about cross-database use of the UDT, not even in tempdb, then no
> problem. (But even then, 2005 offers some options that 2000 does not.)
> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
> Then you can use them in your code in the proper database as if they were a
> native datatype.
> I would guess that this is the type of UDT you have in your database. I
> used to use them heavily, but decided that the value of using UDTs as
> documentation of data domains was to small to repay the hassle. (And, when
> you generate scripts you tend to get the base datatypes scripted out for
> you.)
> --
> With 2005, there is now "CLR User-Defined Types". This is a much richer and
> more complex type of UDT, where you define the type and the operations that
> work with the datatype. If you need this, you need to read about them in
> detail, starting in the SQL Server 2005 Books Online.
> RLF
>
> "parchk" <parchk@.discussions.microsoft.com> wrote in message
> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
> >I came across a database at my company that contained many columns in many
> > tables defined as UDTs. Upon further investigation, there were no rules
> > associated with the UDTs and they were not explicitly bound.
> >
> > Upon even further investigation, the person who created the database said
> > they were using a modeling tool to test the quality/validity of the data
> > model and the tool complained that no data domains were present and
> > suggested
> > the utilization of UDTs.
> >
> > After reading more about them, i was intrigued and can definitely see
> > their
> > value. My question is are there any cons to using them? For example, can
> > over usage cause performance issues? What issues are encountered if a
> > rule
> > or data type needs to be altered? Etc... Thanks in advance.
>
>|||So overall would you recommend extensive utilization of UDTs for defining
data domains and/or the other capabilites of UDTs?
"Russell Fields" wrote:
> parchk,
> Are you using SQL Server 2000 or 2005? Things are changing.
> User-Defined Types in 2000 and 2005 can simply be an alias for describing
> data domains, but for both versions of SQL Server, the gotcha is: "When you
> create a user-defined type, it is local to a single database." If you don't
> care about cross-database use of the UDT, not even in tempdb, then no
> problem. (But even then, 2005 offers some options that 2000 does not.)
> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
> Then you can use them in your code in the proper database as if they were a
> native datatype.
> I would guess that this is the type of UDT you have in your database. I
> used to use them heavily, but decided that the value of using UDTs as
> documentation of data domains was to small to repay the hassle. (And, when
> you generate scripts you tend to get the base datatypes scripted out for
> you.)
> --
> With 2005, there is now "CLR User-Defined Types". This is a much richer and
> more complex type of UDT, where you define the type and the operations that
> work with the datatype. If you need this, you need to read about them in
> detail, starting in the SQL Server 2005 Books Online.
> RLF
>
> "parchk" <parchk@.discussions.microsoft.com> wrote in message
> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
> >I came across a database at my company that contained many columns in many
> > tables defined as UDTs. Upon further investigation, there were no rules
> > associated with the UDTs and they were not explicitly bound.
> >
> > Upon even further investigation, the person who created the database said
> > they were using a modeling tool to test the quality/validity of the data
> > model and the tool complained that no data domains were present and
> > suggested
> > the utilization of UDTs.
> >
> > After reading more about them, i was intrigued and can definitely see
> > their
> > value. My question is are there any cons to using them? For example, can
> > over usage cause performance issues? What issues are encountered if a
> > rule
> > or data type needs to be altered? Etc... Thanks in advance.
>
>|||Parchk,
The 'alias' form of UDTs should have absolutely no performance overhead. It
is really just a naming / documentation scheme.
In terms of replication (I am not a replication expert) I would say that you
want to make sure that the UDTs are also replicated or created with the same
id on the replicated database. And even that is probably not completely
necessary, since the underlying datatypes are knows on both servers.
(Perhaps someone who does replication a lot, can add a comment.)
RLF
"parchk" <parchk@.discussions.microsoft.com> wrote in message
news:4512EE17-B439-46FE-9504-86F7BD01A882@.microsoft.com...
> Hi Russell,
> We are using 2005. Thanks for your response. For databases that make
> heavy
> use of UDTs to define data domains, is there any performance impact? The
> volume will not be high; however, the database is involved with
> replication.
> "Russell Fields" wrote:
>> parchk,
>> Are you using SQL Server 2000 or 2005? Things are changing.
>> User-Defined Types in 2000 and 2005 can simply be an alias for describing
>> data domains, but for both versions of SQL Server, the gotcha is: "When
>> you
>> create a user-defined type, it is local to a single database." If you
>> don't
>> care about cross-database use of the UDT, not even in tempdb, then no
>> problem. (But even then, 2005 offers some options that 2000 does not.)
>> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
>> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
>> Then you can use them in your code in the proper database as if they were
>> a
>> native datatype.
>> I would guess that this is the type of UDT you have in your database. I
>> used to use them heavily, but decided that the value of using UDTs as
>> documentation of data domains was to small to repay the hassle. (And,
>> when
>> you generate scripts you tend to get the base datatypes scripted out for
>> you.)
>> --
>> With 2005, there is now "CLR User-Defined Types". This is a much richer
>> and
>> more complex type of UDT, where you define the type and the operations
>> that
>> work with the datatype. If you need this, you need to read about them in
>> detail, starting in the SQL Server 2005 Books Online.
>> RLF
>>
>> "parchk" <parchk@.discussions.microsoft.com> wrote in message
>> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
>> >I came across a database at my company that contained many columns in
>> >many
>> > tables defined as UDTs. Upon further investigation, there were no
>> > rules
>> > associated with the UDTs and they were not explicitly bound.
>> >
>> > Upon even further investigation, the person who created the database
>> > said
>> > they were using a modeling tool to test the quality/validity of the
>> > data
>> > model and the tool complained that no data domains were present and
>> > suggested
>> > the utilization of UDTs.
>> >
>> > After reading more about them, i was intrigued and can definitely see
>> > their
>> > value. My question is are there any cons to using them? For example,
>> > can
>> > over usage cause performance issues? What issues are encountered if a
>> > rule
>> > or data type needs to be altered? Etc... Thanks in advance.
>>|||Parchk,
It is handy for defining things that you want to be the same everywhere.
For example:
CREATE TYPE SSN FROM varchar(11) NOT NULL
If you use this everywhere a Social Security Number is needed, then the
columns and variables will always be 11-characters, which leaves room for
the dashes, eg. 123-45-6789. This helps avoid having some places coded for
varchar(9), which is intended for 123456789 without dashes, but might wind
up holding 123-45-67 which, of course, is invalid.
The thing that moved me away from using them was the cross-database issues.
These are describe in the 2005 Books Online topic: "Using User-defined Types
Across Databases". It seems, without testing, like it is a little more
robust than in earlier versions, but there are still things to be dealt
with.
I switched over to using class names for my columns, which has served me
well in the years since then. e.g.
Policy_Holder_SocialSecurityNumber
Mate_SocialSecurityNumber
Business_PhoneNumber
Person_FamilyName
In these examples the final token of the column (or variable) name is the
data class. This does not, of course, enforce that everyone defines the
same datatype for the class names. Documentation and review are needed to
catch errors.
FWIW,
RLF
"parchk" <parchk@.discussions.microsoft.com> wrote in message
news:6B7E0FAD-0EA4-46C7-98AE-825692FC1543@.microsoft.com...
> So overall would you recommend extensive utilization of UDTs for defining
> data domains and/or the other capabilites of UDTs?
> "Russell Fields" wrote:
>> parchk,
>> Are you using SQL Server 2000 or 2005? Things are changing.
>> User-Defined Types in 2000 and 2005 can simply be an alias for describing
>> data domains, but for both versions of SQL Server, the gotcha is: "When
>> you
>> create a user-defined type, it is local to a single database." If you
>> don't
>> care about cross-database use of the UDT, not even in tempdb, then no
>> problem. (But even then, 2005 offers some options that 2000 does not.)
>> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
>> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
>> Then you can use them in your code in the proper database as if they were
>> a
>> native datatype.
>> I would guess that this is the type of UDT you have in your database. I
>> used to use them heavily, but decided that the value of using UDTs as
>> documentation of data domains was to small to repay the hassle. (And,
>> when
>> you generate scripts you tend to get the base datatypes scripted out for
>> you.)
>> --
>> With 2005, there is now "CLR User-Defined Types". This is a much richer
>> and
>> more complex type of UDT, where you define the type and the operations
>> that
>> work with the datatype. If you need this, you need to read about them in
>> detail, starting in the SQL Server 2005 Books Online.
>> RLF
>>
>> "parchk" <parchk@.discussions.microsoft.com> wrote in message
>> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
>> >I came across a database at my company that contained many columns in
>> >many
>> > tables defined as UDTs. Upon further investigation, there were no
>> > rules
>> > associated with the UDTs and they were not explicitly bound.
>> >
>> > Upon even further investigation, the person who created the database
>> > said
>> > they were using a modeling tool to test the quality/validity of the
>> > data
>> > model and the tool complained that no data domains were present and
>> > suggested
>> > the utilization of UDTs.
>> >
>> > After reading more about them, i was intrigued and can definitely see
>> > their
>> > value. My question is are there any cons to using them? For example,
>> > can
>> > over usage cause performance issues? What issues are encountered if a
>> > rule
>> > or data type needs to be altered? Etc... Thanks in advance.
>>
Thursday, March 22, 2012
Connectivity Issue
I recently posted a few columns on how to connect. I figured a way, through a mapped network drive. Although this has to be temporary for the extreme nature of connecting this way. It will have to do for now. But, I couldn't get around the issue of connecting my Access 2003 to SQL Server 05.
My new question is this: What do I need to do in order for the tables I created on the server side to show in the link tables window?
It gives me all of the other database library mumbo jumbo, but I don't see the two tables I created...
Another question is this: What about when I create other tables on the server side, will I have to repeat the connection process so I can see those tables as well?
Hi,
ok, if you are sure that you are connected to the right database, you will have to grant the appropiate priviledges to the user who is connecting. The metadata is as of SQL Server 2005 secured, which means that you will only see the tables you have currently access to.
For the other question it depends which project type you are using in access. If you are <using an ADP project (which directly talks to the SQL Server) you can just do a refresh in the listing windows of Access and will see the additional tables and objects. If you are using a "normal" access project, you will have to go through the process of adding the new tables through the "Add new linked table" > "Select the datasource" etc.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||About the first issue, even with SQL Server 2005 secured. Those tables I do have access to are not tables that I have created. What does that mean?
The second issue maybe I should use the ADP method for Access. It sounds like the kind of thing I wanted to do in the first place.
Thanks, for the assistance. I am an Oracle person myself so to be introduced to these databases as the norm is somewhat new to me.
|||If you did not craete them and have acces to the tables you are either granted explicit grants on the tables for your login or you are in a group which has the permissions to access the objects (like a role in ORACLE, e.g. the sysdba role/user).HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
I agree, but I don't understand. Why I would not have access to the two tables I created, they are apart of the same instance as the master table, etc.?
I found out also I may have to back away from the ADP version, it seems to be very webby. I don't mind but here is a very secure culture and I don't know if the interested parties would like that kind of exposure. It is on Access now, and I don't care to much for that. I used to create front end VB interfaces for an Oracle backend. Now I am into Access, trying to make this fly with SQL SERVER. I am hoping to move back into VB/.NET (bread & butter, lol) that maybe the next move.
|||Good luck :-)Jens K. Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)