SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Graph DB edge tables


Graph DB edge tables

Author
Message
Jan Van der Eecken
Jan Van der Eecken
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5117 Visits: 6543
I'm one of those people who insist on an INSERT statement showing an explicit list of column names to be inserted. However, with the EDGE tables in SQL 2017 that seems to be impossible since the $from_id and $to_id columns have an arbitrary suffix appended to them, which leads me to believe that only dynamic SQL could make me feel "happy". Or is there some other way to accomplish that, like in some reserved words that identify those columns without specifically naming them? Also, how would I "re-parent" or "re-sibling" a row in an UPDATE statement if I don't know the column names? Just delete and re-insert?

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
sgmunson
sgmunson
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43160 Visits: 5422
Well, there's always INFORMATION_SCHEMA.COLUMNS to play with. If you go the dynamic SQL route, you can find all the column names for any given table, and then use the QUOTENAME function to enclose them in brackets so you don't have column name issues.

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Jan Van der Eecken
Jan Van der Eecken
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5117 Visits: 6543
sgmunson - Wednesday, December 20, 2017 12:49 PM
Well, there's always INFORMATION_SCHEMA.COLUMNS to play with. If you go the dynamic SQL route, you can find all the column names for any given table, and then use the QUOTENAME function to enclose them in brackets so you don't have column name issues.

I'm aware of that, Steve, but that's exactly what I'm trying to avoid.


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222636 Visits: 40381
sgmunson - Wednesday, December 20, 2017 12:49 PM
Well, there's always INFORMATION_SCHEMA.COLUMNS to play with. If you go the dynamic SQL route, you can find all the column names for any given table, and then use the QUOTENAME function to enclose them in brackets so you don't have column name issues.


Personally, I stay away from INFORMATION_SCHEMA views. If I need metadata information I go to the sys.<system views> since they have more information should I need it.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
sgmunson
sgmunson
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43160 Visits: 5422
Jan Van der Eecken - Wednesday, December 20, 2017 1:12 PM
sgmunson - Wednesday, December 20, 2017 12:49 PM
Well, there's always INFORMATION_SCHEMA.COLUMNS to play with. If you go the dynamic SQL route, you can find all the column names for any given table, and then use the QUOTENAME function to enclose them in brackets so you don't have column name issues.

I'm aware of that, Steve, but that's exactly what I'm trying to avoid.

Yeah, I understand, ... but ... what's the alternative? If you have specific columns that you know the starting x number of characters for the column name, dynamic SQL is probably the ONLY way to have automation handle things. Also, you might be able to use a Script Task within SSIS and use ADO code with VB or C# to maybe make the code more specific than doing it all in T-SQL, but no matter how you slice it, something will have to be dynamic.

I've seen Lynn's post about using the sys.tables or sys.views or sys.columns type of metadata info instead of INFORMATION_SCHEMA, and for some things, those are more practical, but if you just need to identify column names for some dynamic SQL ...


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222636 Visits: 40381
sgmunson - Thursday, December 21, 2017 7:01 AM
Jan Van der Eecken - Wednesday, December 20, 2017 1:12 PM
sgmunson - Wednesday, December 20, 2017 12:49 PM
Well, there's always INFORMATION_SCHEMA.COLUMNS to play with. If you go the dynamic SQL route, you can find all the column names for any given table, and then use the QUOTENAME function to enclose them in brackets so you don't have column name issues.

I'm aware of that, Steve, but that's exactly what I'm trying to avoid.

Yeah, I understand, ... but ... what's the alternative? If you have specific columns that you know the starting x number of characters for the column name, dynamic SQL is probably the ONLY way to have automation handle things. Also, you might be able to use a Script Task within SSIS and use ADO code with VB or C# to maybe make the code more specific than doing it all in T-SQL, but no matter how you slice it, something will have to be dynamic.

I've seen Lynn's post about using the sys.tables or sys.views or sys.columns type of metadata info instead of INFORMATION_SCHEMA, and for some things, those are more practical, but if you just need to identify column names for some dynamic SQL ...


Just my preference, especially since INFORMATION_SCHEMA views aren't as standard as some think.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)

Group: General Forum Members
Points: 504607 Visits: 44234
I'll admit that it's a totally different take on the answer to this but, like many things, I find that the built in functionality is just totally bloated and would probably never use it. I'll write my own, instead.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222636 Visits: 40381
Jeff Moden - Thursday, December 21, 2017 9:24 AM
I'll admit that it's a totally different take on the answer to this but, like many things, I find that the built in functionality is just totally bloated and would probably never use it. I'll write my own, instead.


If I am reading this right, it makes sense considering what I use from the system tables on a regular basis it would be reasonable to create views or functions to eliminate the redundant code I seem to write.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)

Group: General Forum Members
Points: 504607 Visits: 44234
Lynn Pettis - Thursday, December 21, 2017 9:41 AM
Jeff Moden - Thursday, December 21, 2017 9:24 AM
I'll admit that it's a totally different take on the answer to this but, like many things, I find that the built in functionality is just totally bloated and would probably never use it. I'll write my own, instead.


If I am reading this right, it makes sense considering what I use from the system tables on a regular basis it would be reasonable to create views or functions to eliminate the redundant code I seem to write.


My bad, Lynn.... I was talking about graph tables and not things like sys.columns, etc.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jan Van der Eecken
Jan Van der Eecken
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5117 Visits: 6543
My question remains, why did MS decide to add this useless suffix to the @from_id and @to_id columns? It just makes a joke of the "Explicitly name columns" best practice that we have been taught for ages.

Edit: fixed a typo.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search