Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

problem in select command Expand / Collapse
Author
Message
Posted Monday, March 18, 2013 1:02 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, February 21, 2014 4:34 PM
Points: 369, Visits: 1,197
Cte in some situations with really long DT can help readability, but this is not the case here. Some folks (ab)use cte in every statement because they don't know how to write DT. They do not have freedom (or knowledge) to choose between two. You have, and that's good, but original poster maybe would learn cte without knowing dt, and start to write everything as cte. Not because he decided he likes cte better, but because he doesn't know how to write regular dt, and thus cannot choose. It turns that many tsql beginners don't, unfortunately.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1432090
Posted Monday, March 18, 2013 5:31 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:47 PM
Points: 22,504, Visits: 30,216
I find that derived tables make the queries more difficult to read and understand. Using CTEs allows you to move the derived table to an easy to understand construct just before the query and use it CTE name just like a table in the query simplifying the query.


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)
Post #1432147
Posted Monday, March 18, 2013 5:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874

...
Derived table wins as it does not contain extra words ";WITH MyData AS ".
...



"extra words" is a greatest argument to declare a "code" winner I'have ever seen.

Extra typing always makes anything more complex.
By definition.


There is no such definition! Actually, the opposite is quite more common.
Short size of code doesn't make it simpler and that is relevant not only to T-SQL but to many other programming languages.

From my experience, most of developers finding CTE is much more clear way to write query and therefore less complex then using "derived tables".



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1432155
Posted Monday, March 18, 2013 6:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
I really liked it, so my second response !
Sergiy (3/17/2013)
Lynn Pettis (3/17/2013)
Really, CTEs are
complex? I find them to make writing queries easier as you don't have to write derived tables.



Let's compare:
;WITH MyData AS (
SELECT TOP 2 *
FROM dbo.NFFeeds
ORDER BY id DESC
)
SELECT * FROM MyData
ORDER BY id

VS.
SELECT * FROM (
SELECT TOP 2 *
FROM dbo.NFFeeds
ORDER BY id DESC) MyData
ORDER BY id

Derived table wins as it does not contain extra words ";WITH MyData AS ".

Apparently, CTE's are more complex comparing to derived tables.


I want to be part of your competition. So, my version wins over yours one as it contains less number of characters:

SELECT * FROM (SELECT TOP 2 * FROM dbo.NFFeeds ORDER BY id DESC) m ORDER BY id

It wins as:
1. It has less New Line characters
2. It doesn't have tabs (or extra spaces) for indent
3. Most important one - my alias "m" is 7 (seven!) times shorter than yours one "MyData"!
May I have a medal now?





_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1432156
Posted Monday, March 18, 2013 12:48 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
Eugene Elutin (3/18/2013)


"extra words" is a greatest argument to declare a "code" winner I'have ever seen.

Did you find anything else different?
Anything?

If you missed it - I responded on this statement:
Lynn Pettis (3/17/2013)
Really, CTEs are complex? I find them to make writing queries easier as you don't have to write derived tables.


So, I compared those 2 ways of writing queries.
Having no other points of differece, less typing makes DT queries easier to write.

Extra typing always makes anything more complex.
By definition.


There is no such definition! Actually, the opposite is quite more common.

Opposite is stupid.
Smeh bez prichiny - priznak durachiny.

If you you do typing in sake of typing - you're wasting your time and wasting time of those who read it (including SQL optimizer).

There must be a reason to add an extra line to a code.
Inline documentation, better formatting, whatever else - everytime there must be a reason.

I cannot see any advantage of that particular extra line.
Can you point on it?


From my experience, most of developers finding CTE is much more clear way to write query and therefore less complex then using "derived tables".


Most developers used to wrire "CSS style" code.
CTE fits there patterns and allows not to learn SQL-specific best practices.

That's why it's so popular.
Post #1432311
Posted Monday, March 18, 2013 2:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007

Extra typing always makes anything more complex.
By definition.


Actually by definition that makes it more verbose. Verbosity is not the same thing as complex.


But if to ingnore our personal preferences - the query written using DT is simpler than the same query written using CTE.



This type of discussion comes up around here over and over. Should we use a subquery or a cte. In cases like this they almost always proven to produce 100% identical execution plans. So in the end it all boils down to preference. There is no right or wrong. It is simply two ways of saying the same thing.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1432342
Posted Monday, March 18, 2013 2:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007

Most developers used to wrire "CSS style" code.
CTE fits there patterns and allows not to learn SQL-specific best practices.

That's why it's so popular.


What does that mean? What is "CSS style" code and how does whatever that mean prevent somebody from using "SQL-specific best practices"?



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1432344
Posted Monday, March 18, 2013 2:54 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
Sean Lange (3/18/2013)
This type of discussion comes up around here over and over. Should we use a subquery or a cte. In cases like this they almost always proven to produce 100% identical execution plans. So in the end it all boils down to preference. There is no right or wrong. It is simply two ways of saying the same thing.

Exactly my point.

Unlike Lynn said, using CTE has no preference comparing to using derived tables.
+1.

But CTE imposes a danger.
CTE's are strictly in-memory objects. They do not use tempdb for storing excessive data.
So, if you carelessly put too big data set into CTE and then use them in some hash join it could easily kill the server, even if tempdb has terabytes of free space to spare.
Especially it's dangerous in multi-user environment.
Post #1432350
Posted Monday, March 18, 2013 3:01 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:47 PM
Points: 22,504, Visits: 30,216
Sergiy (3/18/2013)
Sean Lange (3/18/2013)
This type of discussion comes up around here over and over. Should we use a subquery or a cte. In cases like this they almost always proven to produce 100% identical execution plans. So in the end it all boils down to preference. There is no right or wrong. It is simply two ways of saying the same thing.

Exactly my point.

Unlike Lynn said, using CTE has no preference comparing to using derived tables.
+1.

But CTE imposes a danger.
CTE's are strictly in-memory objects. They do not use tempdb for storing excessive data.
So, if you carelessly put too big data set into CTE and then use them in some hash join it could easily kill the server, even if tempdb has terabytes of free space to spare.
Especially it's dangerous in multi-user environment.


So a derived table in a FROM clause would have the same problem, wouldn't it.

It would seem that a view would also have the same issue as well.



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)
Post #1432355
Posted Monday, March 18, 2013 3:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
Sergiy (3/18/2013)
Sean Lange (3/18/2013)
This type of discussion comes up around here over and over. Should we use a subquery or a cte. In cases like this they almost always proven to produce 100% identical execution plans. So in the end it all boils down to preference. There is no right or wrong. It is simply two ways of saying the same thing.

Exactly my point.

Unlike Lynn said, using CTE has no preference comparing to using derived tables.
+1.

But CTE imposes a danger.
CTE's are strictly in-memory objects. They do not use tempdb for storing excessive data.
So, if you carelessly put too big data set into CTE and then use them in some hash join it could easily kill the server, even if tempdb has terabytes of free space to spare.
Especially it's dangerous in multi-user environment.


Of course a CTE can use tempdb. Just like a temp table, if the memory pressure is too much it will absolutely start writing data to tempdb.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1432356
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse