Dynamic Index Creation
-
Maybe I'm missing something obvious here, but...
I'm trying to create an index dynamically on a temp table in an sp. I can build the statement happily but the index MUST by desc. SQL seems to have a problem with DESC. If I create an identical physical table and run the statement the sp builds, it's fine (either asc or desc), but it doesn't work in the sp.
If I can avoid it, I don't want to have to dynamically create the insert statement for the temp table so that the records are sorted on their way in.
Any ideas?
-
Hi Tim,
quote:
I'm trying to create an index dynamically on a temp table in an sp. I can build the statement happily but the index MUST by desc. SQL seems to have a problem with DESC. If I create an identical physical table and run the statement the sp builds, it's fine (either asc or desc), but it doesn't work in the sp.can you post your statements?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Frank,
edited version below:
DECLARE
@str varchar(255),
@sortcolumn varchar(50)
CREATE TABLE #tmpIncentive
( f1 int Null,
f2 int Null,
f3 int Null
)
SELECT @sortcolumn = lower(IsNull(int_sort_column, 'f3')),
FROM incentive
WHERE prod_nmbr = @prod_nmbr
SELECT @str = 'CREATE CLUSTERED INDEX idxSort ON #tmpincentive(' + @sortcolumn + ' DESC)'
print @str
EXEC(@str)
INSERT INTO #tmpIncentive
( f1, f2, f3 )
VALUES
( 1,2,3)
INSERT INTO #tmpIncentive
( f1, f2, f3 )
VALUES
( 3,1,2)
INSERT INTO #tmpIncentive
( f1, f2, f3 )
VALUES
( 2,3,1)
SELECT * FROM #tmpIncentive
In this scenario, I'd expect the results to be (as the default order is f2:
2,3,1
1,2,3
3,1,2
but no joy I'm afraid!
-
I figured it was maybe something to do with the EXEC statement going out of scope and therefore the temp table not existing when it tries to create the index, but if you remove the DESC from the statement, it's fine.
-
Tim,
Without an ORDER BY in your SELECT statement, SQL does not guarantee the order in which rows are returned, regardless of any clustered index.
I know that 99% of the time, a SELECT returns rows in clustered index order, but an ORDER BY is imperative to ensure consistent/reliable results.
If you create a permanent table in the same manner then it may eventually reach a point (after adding LOTS of rows) when unordered selects return rows in a wacky sequence.
Cheers,
- Mark
Cheers,
- Mark -
Hi Mark,
quote:
Without an ORDER BY in your SELECT statement, SQL does not guarantee the order in which rows are returned, regardless of any clustered index.I know that 99% of the time, a SELECT returns rows in clustered index order, but an ORDER BY is imperative to ensure consistent/reliable results.
nothing on TV down under ?
Or do you also have a six week old baby at home and suddenly feel the strong need to work overtime ?
Apart from this you're right!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Mark,
The temp table should take care of the number of records. The incentives we're report against generally only run for a couple of months and the data is summarised by consultant anyway. I suspect the largest set would be maybe 300-400 rows.
In addition, another parameter defines how many rows we return. Once we've sorted the data, we'll only return the top x.
I realise this makes it all the more important to use an Order By clause, but I really don't want to run a dynamic select to get my final set.
-
Hi Tim,
quote:
I realise this makes it all the more important to use an Order By clause, but I really don't want to run a dynamic select to get my final set.what is dynamic when you add the DESC keyword in your statement SELECT * FROM #tmpIncentive ORDER BY ...(DESC)?
Cheers,
Frank
Edited by - a5xo3z1 on 08/14/2003 06:42:13 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Nothing in that scenario. But I've got to return the top x records.
I can change the final bit to:
SELECT @str = 'SELECT TOP ' + Convert(varchar(10), @topn) + ' * FROM #tmpIncentive ORDER BY ' + @sortcolumn + ' DESC'
EXEC(@str)
It works, but I'd prefer a neater solution.
I can't understand why DESC doesn't work in the index creation!
-
Seeing there's nothing on TV, I might as well add my last 2 cents worth.
Now this is just hypothesis, so feel free to trash it... maybe with the permanent table the rows are flushed to disk in physical clustered index order, but with the #temporary table they're kept in dirty buffers in memory and SQL doesn't feel compelled to physically order the rows in memory. ?? Just another wild one.
Cheers,
- Mark
Cheers,
- Mark -
Hi Tim,
quote:
SELECT @str = 'SELECT TOP ' + Convert(varchar(10), @topn) + ' * FROM #tmpIncentive ORDER BY ' + @sortcolumn + ' DESC'EXEC(@str)
I think dynamic SQL will be the only way for your statement. Even if you might be able to avoid this on the TOP keyword, by using a sproc with default value, you certainly will need dynamic SQL when you need to have varying sort order
quote:
I can't understand why DESC doesn't work in the index creation!Even it is created in this way, as Mark mentioned, it is not necessarily guaranteed that you retrieve your rows in exactly that order, unless you explicitely state ORDER BY.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yeah, it's looking that way isn't it?
Never mind! Don't you just hate software that doesn't do what you want it to do?
-
quote:
Never mind! Don't you just hate software that doesn't do what you want it to do?yes, I'm dealing every day with M$ and my own
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply