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







share|improve this question




















  • 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 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




    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/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

















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







share|improve this question




















  • 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 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




    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/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















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







share|improve this question















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 19 at 15:30









Sami

6,79531038




6,79531038










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 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




    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/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
















  • 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 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




    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/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










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



















active

oldest

votes











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















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






























active

oldest

votes













active

oldest

votes









active

oldest

votes






active

oldest

votes
















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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







-

Popular posts from this blog

Costa Masnaga

Image
.mw-parser-output .avviso .mbox-text-div>div,.mw-parser-output .avviso .mbox-text-full-div>div{font-size:90%}.mw-parser-output .avviso .mbox-image div{width:52px}.mw-parser-output .avviso .mbox-text-full-div .hide-when-compact{display:block} Questa voce o sezione sull'argomento Lombardia non cita le fonti necessarie o quelle presenti sono insufficienti . Puoi migliorare questa voce aggiungendo citazioni da fonti attendibili secondo le linee guida sull'uso delle fonti. Segui i suggerimenti del progetto di riferimento. Costa Masnaga comune Localizzazione Stato   Italia Regione Lombardia Provincia Lecco Amministrazione Sindaco Sabina Panzeri (Al centro il cittadino) dall'08/06/2009 Territorio Coordinate 45°46′N 9°17′E  /  45.766667°N 9.283333°E 45.766667; 9.283333  ( Costa Masnaga ) Coordinate: 45°46′N 9°17′E  /  45.766667°N 9.283333°E 45.766667; 9.283333  ( Costa Masnaga ) Altitudine 318 m s.l.m...
Read more

Fotorealismo

Image
Guerrino Boatto, Venice's St. Barnaba Church and canal Il fotorealismo è un genere di pittura basato sull'uso di una o più fotografie dalle quali l'artista prende le informazioni necessarie da utilizzare poi nel processo della creazione dell'opera, in genere ad olio o ad acrilico, al fine di avere un aspetto finale il più simile possibile alla fotografia. Indice 1 Storia 2 Lista dei fotorealisti 3 Note 4 Voci correlate 5 Altri progetti Storia | Il fotorealismo è un movimento che nasce negli Stati Uniti d'America alla fine degli anni sessanta [1] sulla scia della Pop Art [2] [3] [4] , e insieme condividono il carattere reazionario verso la travolgente ascesa dei media, che alla metà del ventesimo secolo si trasforma in un fenomeno di massa talmente imponente da minacciare il valore dell'immagine nell'arte. [5] [6] Ne consegue così che, colto il gesto d'inizio che fu della Pop Art, i fotorealisti aprono ad un ...
Read more

Sidney Franklin

Image
Questa voce sull'argomento registi statunitensi è solo un abbozzo . Contribuisci a migliorarla secondo le convenzioni di Wikipedia. Sidney Franklin, foto di Carl Van Vechten Oscar alla memoria Irving G. Thalberg 1943 Sidney Franklin , all'anagrafe Sidney Arnold Franklin , (San Francisco, 21 marzo 1893 – Santa Monica, 18 maggio 1972), è stato un regista, produttore cinematografico e attore statunitense. Il 20 giugno 1963, si sposò con l'attrice australiana Enid Bennett. Il matrimonio durò fino alla morte dell'attrice, il 14 maggio 1969 [1] Indice 1 Filmografia 1.1 Regista 1.2 Aiuto regia (parziale) 1.3 Produttore 1.4 Attore (parziale) 2 Note 3 Altri progetti 4 Collegamenti esterni Filmografia | Regista | The Baby , co-regia Chester M. Franklin (1915) The Rivals , co-regia Chester M. Franklin (1915) Little Dick's First Case , co-regia Chester M. Franklin (1915) Her Filmland Hero , co-...
Read more