Is SELECT a part of DML, DDL or something else?

Question

Is SELECT a part of DML, DDL or something else?

michael.leach2015

SSCommitted

Points: 1597
More actions
June 9, 2020 at 11:56 pm

#3761836
According to this post https://www.sqlservercentral.com/blogs/what-is-%e2%80%93-dml-ddl-dcl-and-tcl-in-tsql
SELECT is classified under DML. However, when I check the SQL online documentation,
https://docs.microsoft.com/en-us/sql/t-sql/statements/statements?view=sql-server-ver15
SELECT is not listed under DML.
1. So which language type is SELECT officially classified under? I can't find anything in the online book.
2. The first link above mentions DCL and TCL. Why doesn't the online book recognize these terms?
3. Incidentally when I was taking another look at the online documentation, I also noticed that TRUNCATE TABLE and DELETE are listed under DML. I understand DELETE but isn't TRUNCATE TABLE considered to be a part of DDL?
EDIT: I just came across this page, which apparently classifies SELECT as DML, so I guess that answer's #1 above. Still not sure why the 2nd link I posted doesn't have SELECT under DML. However I am still uncertain about #2 and #3.
- This topic was modified 4 years, 6 months ago by michael.leach2015.
- This topic was modified 4 years, 6 months ago by michael.leach2015.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

jonathan.crawford SSCertifiable Points: 6613 More actions · Answer 1

#2. because it's probably just something the authors at Microsoft didn't put in as usual terminology (low value change, may not have been a concept they even considered). You could put in a request to add it if you really wanted to see it.

#3. TRUNCATE isn't defining anything about the data, so I would definitely consider it to be part of the Manipulation language. DDL is saying what kind of data, how long is the data, what kind of an object holds the data.

As for #1 maybe go DM Joe Celko and ask what they considered it during his time working on ANSI standards. He'll probably have a lot of background if you really want to know.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

michael.leach2015 wrote:

According to this post https://www.sqlservercentral.com/blogs/what-is-%e2%80%93-dml-ddl-dcl-and-tcl-in-tsql
SELECT is classified under DML. However, when I check the SQL online documentation,
https://docs.microsoft.com/en-us/sql/t-sql/statements/statements?view=sql-server-ver15
SELECT is not listed under DML.

<li style="list-style-type: none;">
So which language type is SELECT officially classified under? I can't find anything in the online book.

<li style="list-style-type: none;">
The first link above mentions DCL and TCL. Why doesn't the online book recognize these terms?

<li style="list-style-type: none;">
Incidentally when I was taking another look at the online documentation, I also noticed that TRUNCATE TABLE and DELETE are listed under DML. I understand DELETE but isn't TRUNCATE TABLE considered to be a part of DDL?

EDIT: I just came across this page, which apparently classifies SELECT as DML, so I guess that answer's #1 above. Still not sure why the 2nd link I posted doesn't have SELECT under DML. However I am still uncertain about #2 and #3.

To be honest, I think that page is full of hooie. Someone needs to fix it. It's one of those MS articles that make me cringe due to what I believe are inaccuracies.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 3

Good points. Submitted a PR to add SELECT to DML.

Anyone can do this: http://voiceofthedba.com/2018/01/03/creating-a-books-online-pull-request/

jonathan.crawford SSCertifiable Points: 6613 More actions · Answer 4

Honestly, a little excited to go vote for "this page is full of hooie"...

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 5

>> As for #1 maybe go DM Joe Celko and ask what they considered it during his time working on ANSI standards. He'll probably have a lot of background if you really want to know. <<

SELECT is a DML statement. It manipulates data. Wow! I was not my usual pedantic self!

Please post DDL and follow ANSI/ISO standards when asking for help.

jonathan.crawford SSCertifiable Points: 6613 More actions · Answer 6

So much for my dreams of the epic saga of the SELECT as told by the heroic defender of the faith....

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

Steve Jones - SSC Editor wrote:

Good points. Submitted a PR to add SELECT to DML.
Anyone can do this: http://voiceofthedba.com/2018/01/03/creating-a-books-online-pull-request/

Did it include a request to move TRUNCATE TABLE from DML to DDL?

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

jonathan.crawford wrote:

Honestly, a little excited to go vote for "this page is full of hooie"...

Let's continue to check out the "hooie" factor...

Since they don't provide any links in that article for things like TRUNCATE TABLE, etc, you have to go to the left menu and click there. I'm actually ok with that (when they work correctly) but when I clicked on TRUNCATE TABLE, it took me to the TRUNCATE TABLE page where it says....

Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

That's actually horribly incorrect! It's nothing like DELETE and it can't even come close to logging individual rows deletions because it does actually delete rows. This is a part of the reason that people don't understand that TRUNCATE TABLE is NOT actually minimally logged. Instead, it's fully logged and it can be fully rolled back because it's actually always fully logged.

Ok... so if it's not deleting rows and it's fully logged, what is it actually deleting and what is it logging?

The answer to the former is that it's NOT actually deleting anything! Instead, it's deallocating the pages that the data lives on, which makes it a DDL statement. The answer to the latter is the same answer as the former... it logs ONLY the page deallocations. A rollback simply undoes the page deallocations and that's why both its usage and a rollback are so bloody fast.

So, especially for the TRUNCATE TABLE being improperly listed as DML, the hooie extends to other articles.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 9

Jeff,

Thank you for your input. I'm glad I asked these questions.

With all the replies I am still unclear about which language type SELECT really falls under. Just to be sure, should it be classified as DML, DCL, TCL or something else?

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 10

michael.leach2015 wrote:

Jeff,
Thank you for your input. I'm glad I asked these questions.
With all the replies I am still unclear about which language type SELECT really falls under. Just to be sure, should it be classified as DML, DCL, TCL or something else?

SELECT is patently a DML statement/clause.

TRUNCATE TABLE is patently a DDL statement. You even need to have implied or explicit "DDL Admin" privs to execute it.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 11

Jeff Moden wrote:

Steve Jones - SSC Editor wrote:
Good points. Submitted a PR to add SELECT to DML.
Anyone can do this: http://voiceofthedba.com/2018/01/03/creating-a-books-online-pull-request/
Did it include a request to move TRUNCATE TABLE from DML to DDL?

I didn't, but happy to do so, and include some links, but stacking PRs becomes an issue with their process. I thought about including links, but did't have time, so just really added the SELECT item as DML

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 12

On that note, I'll see if I have the time to add it. I guess it's also time to have a "come to Jesus" meeting with them about their documentation on the TRUNCATE TABLE DDL. 😀

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 13

>> So much for my dreams of the epic saga of the SELECT as told by the heroic defender of the faith... <<

I can write 1000-2000 words on the SELECT statement, but I usually get paid for it 🙂 It is not like assignment statements in other languages ...

Please post DDL and follow ANSI/ISO standards when asking for help.