SQL Server data in the same record
up vote
0
down vote
favorite
Let's say I have a master table that referred by three other tables,
Table A(Id int,...some columns...) -- The master Table
Table B(Id int,...some columns..., A_Id int)
Table C(Id int,...some columns..., A_Id int)
Table D(Id int,...some columns..., A_Id int)
Where A_Id is a foreign key refers to Table A Id column.
My question now is, what if I create three columns in the master table with data type ntext, and save the table B data as xml in one column, save the table D data as xml in one column,save the table C data as xml in one column, and drop the three tables.
So in each record in table A I have all related data in the same record insted of fetching that from three other tables?
Is this a good technique?
sql
sql-server edited Nov 19 at 15:30
Sami
6,79531038
asked Nov 19 at 15:27
Imad Abu Hayyah
907
|
show 7 more comments
up vote
0
down vote
favorite
Let's say I have a master table that referred by three other tables,
Table A(Id int,...some columns...) -- The master Table
Table B(Id int,...some columns..., A_Id int)
Table C(Id int,...some columns..., A_Id int)
Table D(Id int,...some columns..., A_Id int)
Where A_Id is a foreign key refers to Table A Id column.
My question now is, what if I create three columns in the master table with data type ntext, and save the table B data as xml in one column, save the table D data as xml in one column,save the table C data as xml in one column, and drop the three tables.
So in each record in table A I have all related data in the same record insted of fetching that from three other tables?
Is this a good technique?
sql
sql-server edited Nov 19 at 15:30
Sami
6,79531038
asked Nov 19 at 15:27
Imad Abu Hayyah
907
1
Without knowing more about the tables and the data, we can only guess.
– jarlh
Nov 19 at 15:28
1
The only thing i can definitively say is no, ntext is not a good technique. The rest, I have no idea because we don't know what you are trying to do.
– dfundako
Nov 19 at 15:29
3
Is replacing relational tables with XML fields a good idea? No, almost never. At the very least, you need to know why you're doing this and what you expect to get out of it. There's no tax on tables. There is a tax on I/O, but the assumption that stuffing data in XML columns means less I/O is probably wrong (depending on your use case). Also, using the deprecatedNTEXTtype is just never a good idea period -- useNVARCHAR(MAX)or (in this case) the dedicatedXMLtype instead.
– Jeroen Mostert
Nov 19 at 15:29
1
You can very well fetch data from different tables in one single query using joins, or using multiple result sets that are processed by the client. If you're so inclined you can even collapse the related data into one single row ony the fly withFOR XML, if this particular use case warrants it. This still does not (necessarily) mean the data must be stored that way. Consider the cost of updating, for one.
– Jeroen Mostert
Nov 19 at 15:33
1
You typically solve that by having whatever receives the posted data parse the input, figure out what to do and issue the appropriateUPDATE/INSERTorMERGE. Things like Entity Framework specialize in these kinds of graph updates. If the data is really never treated as anything but a big blob and is never (or only rarely) accessed individually in SQL, then yes, you might consider using XML columns instead. But it's a big if -- if you started off with tables, there's probably a good reason for that.
– Jeroen Mostert
Nov 19 at 15:41
|
show 7 more comments
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Let's say I have a master table that referred by three other tables,
Table A(Id int,...some columns...) -- The master Table
Table B(Id int,...some columns..., A_Id int)
Table C(Id int,...some columns..., A_Id int)
Table D(Id int,...some columns..., A_Id int)
Where A_Id is a foreign key refers to Table A Id column.
My question now is, what if I create three columns in the master table with data type ntext, and save the table B data as xml in one column, save the table D data as xml in one column,save the table C data as xml in one column, and drop the three tables.
So in each record in table A I have all related data in the same record insted of fetching that from three other tables?
Is this a good technique?
sql
sql-server edited Nov 19 at 15:30
Sami
6,79531038
asked Nov 19 at 15:27
Imad Abu Hayyah
907
Let's say I have a master table that referred by three other tables,
Table A(Id int,...some columns...) -- The master Table
Table B(Id int,...some columns..., A_Id int)
Table C(Id int,...some columns..., A_Id int)
Table D(Id int,...some columns..., A_Id int)
Where A_Id is a foreign key refers to Table A Id column.
My question now is, what if I create three columns in the master table with data type ntext, and save the table B data as xml in one column, save the table D data as xml in one column,save the table C data as xml in one column, and drop the three tables.
So in each record in table A I have all related data in the same record insted of fetching that from three other tables?
Is this a good technique?
sql
sql-server sql
sql-server edited Nov 19 at 15:30
Sami
6,79531038
asked Nov 19 at 15:27
Imad Abu Hayyah
907
edited Nov 19 at 15:30
Sami
6,79531038
asked Nov 19 at 15:27
Imad Abu Hayyah
907
edited Nov 19 at 15:30
Sami
6,79531038
edited Nov 19 at 15:30
Sami
6,79531038
edited Nov 19 at 15:30
Sami
6,79531038
6,79531038
asked Nov 19 at 15:27
Imad Abu Hayyah
907
asked Nov 19 at 15:27
Imad Abu Hayyah
907
asked Nov 19 at 15:27
Imad Abu Hayyah
907
907
1
Without knowing more about the tables and the data, we can only guess.
– jarlh
Nov 19 at 15:28
1
The only thing i can definitively say is no, ntext is not a good technique. The rest, I have no idea because we don't know what you are trying to do.
– dfundako
Nov 19 at 15:29
3
Is replacing relational tables with XML fields a good idea? No, almost never. At the very least, you need to know why you're doing this and what you expect to get out of it. There's no tax on tables. There is a tax on I/O, but the assumption that stuffing data in XML columns means less I/O is probably wrong (depending on your use case). Also, using the deprecatedNTEXTtype is just never a good idea period -- useNVARCHAR(MAX)or (in this case) the dedicatedXMLtype instead.
– Jeroen Mostert
Nov 19 at 15:29
1
You can very well fetch data from different tables in one single query using joins, or using multiple result sets that are processed by the client. If you're so inclined you can even collapse the related data into one single row ony the fly withFOR XML, if this particular use case warrants it. This still does not (necessarily) mean the data must be stored that way. Consider the cost of updating, for one.
– Jeroen Mostert
Nov 19 at 15:33
1
You typically solve that by having whatever receives the posted data parse the input, figure out what to do and issue the appropriateUPDATE/INSERTorMERGE. Things like Entity Framework specialize in these kinds of graph updates. If the data is really never treated as anything but a big blob and is never (or only rarely) accessed individually in SQL, then yes, you might consider using XML columns instead. But it's a big if -- if you started off with tables, there's probably a good reason for that.
– Jeroen Mostert
Nov 19 at 15:41
|
show 7 more comments
1
Without knowing more about the tables and the data, we can only guess.
– jarlh
Nov 19 at 15:28
1
The only thing i can definitively say is no, ntext is not a good technique. The rest, I have no idea because we don't know what you are trying to do.
– dfundako
Nov 19 at 15:29
3
Is replacing relational tables with XML fields a good idea? No, almost never. At the very least, you need to know why you're doing this and what you expect to get out of it. There's no tax on tables. There is a tax on I/O, but the assumption that stuffing data in XML columns means less I/O is probably wrong (depending on your use case). Also, using the deprecatedNTEXTtype is just never a good idea period -- useNVARCHAR(MAX)or (in this case) the dedicatedXMLtype instead.
– Jeroen Mostert
Nov 19 at 15:29
1
You can very well fetch data from different tables in one single query using joins, or using multiple result sets that are processed by the client. If you're so inclined you can even collapse the related data into one single row ony the fly withFOR XML, if this particular use case warrants it. This still does not (necessarily) mean the data must be stored that way. Consider the cost of updating, for one.
– Jeroen Mostert
Nov 19 at 15:33
1
You typically solve that by having whatever receives the posted data parse the input, figure out what to do and issue the appropriateUPDATE/INSERTorMERGE. Things like Entity Framework specialize in these kinds of graph updates. If the data is really never treated as anything but a big blob and is never (or only rarely) accessed individually in SQL, then yes, you might consider using XML columns instead. But it's a big if -- if you started off with tables, there's probably a good reason for that.
– Jeroen Mostert
Nov 19 at 15:41
1
1
Without knowing more about the tables and the data, we can only guess.
– jarlh
Nov 19 at 15:28
Without knowing more about the tables and the data, we can only guess.
– jarlh
Nov 19 at 15:28
1
1
The only thing i can definitively say is no, ntext is not a good technique. The rest, I have no idea because we don't know what you are trying to do.
– dfundako
Nov 19 at 15:29
The only thing i can definitively say is no, ntext is not a good technique. The rest, I have no idea because we don't know what you are trying to do.
– dfundako
Nov 19 at 15:29
3
3
Is replacing relational tables with XML fields a good idea? No, almost never. At the very least, you need to know why you're doing this and what you expect to get out of it. There's no tax on tables. There is a tax on I/O, but the assumption that stuffing data in XML columns means less I/O is probably wrong (depending on your use case). Also, using the deprecated
NTEXT type is just never a good idea period -- use NVARCHAR(MAX) or (in this case) the dedicated XML type instead.– Jeroen Mostert
Nov 19 at 15:29
Is replacing relational tables with XML fields a good idea? No, almost never. At the very least, you need to know why you're doing this and what you expect to get out of it. There's no tax on tables. There is a tax on I/O, but the assumption that stuffing data in XML columns means less I/O is probably wrong (depending on your use case). Also, using the deprecated
NTEXT type is just never a good idea period -- use NVARCHAR(MAX) or (in this case) the dedicated XML type instead.– Jeroen Mostert
Nov 19 at 15:29
1
1
You can very well fetch data from different tables in one single query using joins, or using multiple result sets that are processed by the client. If you're so inclined you can even collapse the related data into one single row ony the fly with
FOR XML, if this particular use case warrants it. This still does not (necessarily) mean the data must be stored that way. Consider the cost of updating, for one.– Jeroen Mostert
Nov 19 at 15:33
You can very well fetch data from different tables in one single query using joins, or using multiple result sets that are processed by the client. If you're so inclined you can even collapse the related data into one single row ony the fly with
FOR XML, if this particular use case warrants it. This still does not (necessarily) mean the data must be stored that way. Consider the cost of updating, for one.– Jeroen Mostert
Nov 19 at 15:33
1
1
You typically solve that by having whatever receives the posted data parse the input, figure out what to do and issue the appropriate
UPDATE/INSERT or MERGE. Things like Entity Framework specialize in these kinds of graph updates. If the data is really never treated as anything but a big blob and is never (or only rarely) accessed individually in SQL, then yes, you might consider using XML columns instead. But it's a big if -- if you started off with tables, there's probably a good reason for that.– Jeroen Mostert
Nov 19 at 15:41
You typically solve that by having whatever receives the posted data parse the input, figure out what to do and issue the appropriate
UPDATE/INSERT or MERGE. Things like Entity Framework specialize in these kinds of graph updates. If the data is really never treated as anything but a big blob and is never (or only rarely) accessed individually in SQL, then yes, you might consider using XML columns instead. But it's a big if -- if you started off with tables, there's probably a good reason for that.– Jeroen Mostert
Nov 19 at 15:41
|
show 7 more comments
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53377810%2fsql-server-data-in-the-same-record%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Without knowing more about the tables and the data, we can only guess.
– jarlh
Nov 19 at 15:28
1
The only thing i can definitively say is no, ntext is not a good technique. The rest, I have no idea because we don't know what you are trying to do.
– dfundako
Nov 19 at 15:29
3
Is replacing relational tables with XML fields a good idea? No, almost never. At the very least, you need to know why you're doing this and what you expect to get out of it. There's no tax on tables. There is a tax on I/O, but the assumption that stuffing data in XML columns means less I/O is probably wrong (depending on your use case). Also, using the deprecated
NTEXTtype is just never a good idea period -- useNVARCHAR(MAX)or (in this case) the dedicatedXMLtype instead.– Jeroen Mostert
Nov 19 at 15:29
1
You can very well fetch data from different tables in one single query using joins, or using multiple result sets that are processed by the client. If you're so inclined you can even collapse the related data into one single row ony the fly with
FOR XML, if this particular use case warrants it. This still does not (necessarily) mean the data must be stored that way. Consider the cost of updating, for one.– Jeroen Mostert
Nov 19 at 15:33
1
You typically solve that by having whatever receives the posted data parse the input, figure out what to do and issue the appropriate
UPDATE/INSERTorMERGE. Things like Entity Framework specialize in these kinds of graph updates. If the data is really never treated as anything but a big blob and is never (or only rarely) accessed individually in SQL, then yes, you might consider using XML columns instead. But it's a big if -- if you started off with tables, there's probably a good reason for that.– Jeroen Mostert
Nov 19 at 15:41