Which query method is best to use and when?

Question

Which query method is best to use and when?

Viewing 6 posts - 16 through 21 (of 21 total)

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

Paul White SSC Guru Points: 150468 More actions · Answer 1

Bhuvnesh (4/27/2010)
I dont see any difference between temp table and table variable except their "creation syntax" ?

Please read Wayne's excellent article, referenced in Gianluca's post for a pretty full comparison.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

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

aaa-322853 (4/27/2010)
I think I get it. Some people will use a loop and iterate through each number ending up with 8,000 result sets.
If you use a tally table you can return the 1 to 8,000 using one select, in a set based way. Something like;
SELECT n
FROM Nums
WHERE n <= 8000
ORDER BY n

Exactly. And you can warp it to change things to dates or times with DATEADD, etc, etc.

Need more numbers than what a Tally table has in it? Like, a Million? Use a modification of Itzik Ben-Gan's code...

WITH

E01(N) AS ( --=== Create Ten 1's

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 --10

),

E02(N) AS (SELECT 1 FROM E01 a, E01 b), --100

E04(N) AS (SELECT 1 FROM E02 a, E02 b), --10,000

E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000

E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10,000,000,000,000,000

cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM E16)

SELECT TOP (1000000) N

FROM cteTally

That's the type of stuff that will make your report writers better. That and a wicked understanding of how to do other things such as use Cross Apply, how to build static and dynamic Cross Tabs (notice I didn't say PIVOT 😉 ), how to "Pre-aggregate" data, and a couple of other "Black Arts" techniques.

Lunch time seminars may work better than "coding guidlines". 😀 Get people used to "set based" thinking. Like it says in my signature line...

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

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Jeff, didn't they completely remove support for "SELECT 1 FROM E01 a, E01 b" in SQL 2008 or 2008R2? I thought that non-ANSI stuff was going away. In any case probably best to use explicit CROSS JOIN syntax.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150468 More actions · Answer 4

TheSQLGuru (4/27/2010)
Jeff, didn't they completely remove support for "SELECT 1 FROM E01 a, E01 b" in SQL 2008 or 2008R2? I thought that non-ANSI stuff was going away. In any case probably best to use explicit CROSS JOIN syntax.

According to Itzik's latest book, the SQL-89 syntax for cross joins is likely to remain supported by SQL Server until ANSI deprecate it (not happened yet). CROSS JOIN is recommended and preferred though.

Support for *= =* outer join syntax is gone from R2 I believe (tolerated in 80 compatibility mode on 2005/2008).

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 5

TheSQLGuru (4/27/2010)
Jeff, didn't they completely remove support for "SELECT 1 FROM E01 a, E01 b" in SQL 2008 or 2008R2? I thought that non-ANSI stuff was going away. In any case probably best to use explicit CROSS JOIN syntax.

Like Paul said, I imagine it's going away someday (don't know how they'll support CROSS APPLY or correlated subqueries when they do) but, in the meantime, if it's good enough for Itzik, it's good enough for me. 😛

I do wish they'd stop deprecating stuff... especially the useful stuff.

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

aaa121 SSCertifiable Points: 5902 More actions · Answer 6

Paul, in response to your questions...

1.. Why do production DBAs care about tempdb usage in the Data Warehouse? Aren't these two physically separate environments? I'm sure there is a good reason for the concern - I just wonder what it is.

The disk space on the server in question is fairly tight. TempDB is under HEAVY use and although the db is set to autogrow there is a max size limit which is reached frequently.

2, I am a great believer in making life as easy as possible for report writers. A good environment makes extensive use of pre-aggregations. The fact that huge intermediate result sets are being generated in tempdb suggests that a layer of abstraction is missing here.

Couldn't agree more, I am a BI/DW developer myself. The situation with my current client is that previous DWs have failed. There is a very sorry looking which exists at the moment. No one uses it because it's useless.

The data they are querying is a read only copy of the staging area data. This is a true staging area with no transformations, aggregations etc. They are querying this as the source systems are mainframe based and this is the only current way they can access the data. The next step is to scrap the existing DW and build a new one providing a hugely more efficient reporting platform.