Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


problem in select command


problem in select command

Author
Message
Vedran Kesegic
Vedran Kesegic
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 1255
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

Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24285 Visits: 37993
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.

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)
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3046 Visits: 5478

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


w00tw00tw00tw00tw00t
"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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3046 Visits: 5478
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.
Cool


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

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

How to post your question to get the best and quick help
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5852 Visits: 11412
Eugene Elutin (3/18/2013)

w00tw00tw00tw00tw00t
"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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16679 Visits: 17036

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


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)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16679 Visits: 17036

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)
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5852 Visits: 11412
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24285 Visits: 37993
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.

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)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16679 Visits: 17036
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)
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