is there a function "IsType"?

Hi guys
I want to know how to write a function IsNumericType
IsNumericType(value)
so it returns: SmallInt, or Int, or TinyIntThere is a ISNUMERIC function, but in many cases it is of little utility.
One can create a scalar UDF with PATINDEX to determine if a given value is
numeric or not. Search the archives of this newsgroup and you'll find
several examples.
In t-SQL, you cannot distingush the exact type of a value, unless it is
assigned to a declared variable or belongs to a typed column.
Anith|||so let say it belongs to a typed column,
how to find out what type of column is it ?
I don't want to use sp_help, sp_help shows all the columns, but I only want
to show one.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eTR7xhEmFHA.1232@.TK2MSFTNGP15.phx.gbl...
> There is a ISNUMERIC function, but in many cases it is of little utility.
> One can create a scalar UDF with PATINDEX to determine if a given value is
> numeric or not. Search the archives of this newsgroup and you'll find
> several examples.
> In t-SQL, you cannot distingush the exact type of a value, unless it is
> assigned to a declared variable or belongs to a typed column.
> --
> Anith
>|||SELECT data_type
FROM information_schema.columns
WHERE table_schema = 'dbo'
AND table_name = 'table_name'
AND column_name = 'column_name' ;
David Portas
SQL Server MVP
--

Is one multi-key index faster than having lots of single-key indexes

Hi Guys/Gals,

I'm trying to improve the performance of our MS SQL 2000 database.

I've got a table with 5 int colums(among other things) and most SP's query data based on all five.

After some initial tests, my results are somewhat inconsistent/inconclusive.

I'd like to ask someone who's been doing this for quite some time.

Is it better to have one multi-key index(5 ints) or have a separate index for each individual column?

Thank you in advance,
Vlad OrlovskyI believe it depends upon the version of SQL Server, but I am pretty confidant that SQL 2000 for sure will use multiple single segment keys when available. (though this would likely add some overhead over a single index). As always, I would confirm with your data and testing (showing execution plan in Query Analyzer). If you ever use the columns individually (and not always starting with the one that is leftmost) then you would want the individual indexes.|||The SQL Server query engine is going to pick the best index and use it. It won't use multiple indexes for a single WHERE clause. So it will only use one of your indexes. If you are normally querying with all 5 columns in your WHERE clause, you will typically get better performance if you include all 5 columns in a single index. As always, it depends on the data, number of rows, the cardinality of the data, etc. to determine how much difference this will make in performance. But generally SQL Server doesn't have to work as hard if the WHERE clause is covered in a single index.

cs|||Books online for SQL 2000 says it will.

"Do not use multiple aliases for a single table in the same query to simulate index intersection. This is no longer necessary because SQL Server automatically considers index intersection and can make use of multiple indexes on the same table in the same query. For example, given the sample query:
SELECT * FROM lineitem
WHERE partkey BETWEEN 17000 AND 17100 AND
shipdate BETWEEN '1/1/1994' AND '1/31/1994"

SQL Server can exploit indexes on both the partkey and shipdate columns, and then perform a hash match between the two subsets to obtain the index intersection."|||really? that's not been my experience in the real world. not sure what conditions must be met for this to happen. i've just been doing quite a bit of query tuning and based on the execution plan, I have rarely seen SQL Server actually use more than one index. but thanks for tracking that down Douglas, good to know what the BOL says.

i'm not sure the multiple alias thing is quite the same animal anyway. just sharing what I've seen based on personal experience. as always, it's worth trying different approaches to see what performs best in a particular case.

cs|||Thanks for your tips guys. "Execution plan" thing is a very useful feature.
I did some testing on 93K+ records. And it turns out that having one single multi-key index is better than multiple indexes when selecting all keys in the WHERE clause.

Once I got my multi-key index to sort in the right order, time spent on final "Sorting" of the data has disappeared(that was something like .0183 I/O Cost).

Sincerely,
Vlad Orlovsky|||Yes, sorting can be an issue when using multiple keys.

Is one better than the other?

Sql server 2000 SP4.

Here is the schema, other details are ignored for simplicity.

Create Table TableC ( TCID int NOT NULL)

Create Table TableW (TCID int NOT NULL , EndDate datetime)

TCID is PK in both the tables.

I have two queries one with subquery with IN operator and the other with traditional join. Execution plans are almost identical except one uses right semi join and the other uses inner join. Resource cost is also same except 'Estimated row size' for right semi-join is less than the traditional join. Does Semi-joins cost less than the traditional inner joins?

How are these two different from each other? Is there any performance related difference between semi joins and joins?

select * from dbo.TableC where TCID in
(select TCID from dbo.TableW where EndDate IS NULL )

StmtText
|--Merge Join(Right Semi Join, MERGE: ([TableW].[TCID])=([TableC].[TCID]), RESIDUAL: ([TableC].[TCID]=[TableW].[TCID]))
|--Clustered Index Scan(OBJECT: ([DBNAME].[dbo].[TableW].[PK_TableW]), WHERE: ([TableW].[cEndDate]=NULL) ORDERED FORWARD)
|--Clustered Index Scan(OBJECT: ([DBNAME].[dbo].[TableC].[PK_TableC]), ORDERED FORWARD)

select c.* from TableC c join TableW w
on c.TCID = w.TCID
and EndDate IS NULL

StmtText
|--Merge Join(Inner Join, MERGE: ([w ].[TCID])=([c ].[TCID]), RESIDUAL: ([w ].[TCID]=[c ].[TCID]))
|--Clustered Index Scan(OBJECT: ([DBNAME].[dbo].[TableW].[PK_TableW] AS [w ]), WHERE: ([w ].[cEndDate]=NULL) ORDERED FORWARD)
|--Clustered Index Scan(OBJECT: ([DBNAME].[dbo].[TableC].[PK_TableC] AS [c ]), ORDERED FORWARD)

Sankar,

One thing is that the two queries are not equivalent. The IN query returns each row of TableC at most once, but the JOIN query may return rows from TableC more than once. That's where the join vs. semijoin difference comes in. From what you show here, I don't see a reason why the row sizes are different. In the graphical query plan, do you see more columns being passed through the plan in one query than in the other? I don't have an instance of 2000 handy to check whether you can inspect an operators Output List to see this information, but if you can see it, and there's a difference, can you figure out why more columns would need to be carried through?

I know that's not quite an answer to your question, but maybe it will help a bit.

Steve Kass

Drew University

http://www.stevekass.com

|||

Steve,

"One thing is that the two queries are not equivalent." I thought about this but TCID is PK in both the tables and we will get the row atmost once only. I checked the graphical plan and output list is the same for both queries. The "estimated row size" is different for the merge join operator not for the individual "index scan operators". I wonder whats causing this number to be lower for the semi join.

|||This part of my reply was suggesting how you might investigate the row size difference: "From what you show here, I don't see a reason why the row sizes are different. In the graphical query plan, do you see more columns being passed through the plan in one query than in the other? I don't have an instance of 2000 handy to check whether you can inspect an operators Output List to see this information, but if you can see it, and there's a difference, can you figure out why more columns would need to be carried through?"

SK
|||

Interesting stuff. I ran the same query against the SQL 2005 instance (not live version, trial version running on my pc) and the execution plan is same for both the queries. I didn't get the Semi Join for the subquery with IN clause and optimizer re-wrote it to the inner join.

Is my table scan unavoidable?

Hi guys-n-gals...

I have a table that contains the following:
PortfolioID(int), EndDate(smalldatetime), Begdate(smalldatetime), WklyCloseIndex (float)

It has a primary key which is PortfolioID/BegDate/EndDate

I need to create a table that summarizes, by date range, the weeklycloseindex of several portfolios, like this:

BegDate EndDate Portfolio2 Portfolio67 Portfolio11 Portfolio90
05/28/2004 06/04/2004 xx.xx xx.xx xx.xx xx.xx
05/21/2004 05/28/2004 xx.xx xx.xx xx.xx xx.xx

So I wrote a function...

This function results in a table scan *GASP!!!!* (at least that's what the 'splain plan tells me when I run it in SQL Analyzer). Before I made it into the function, when I was testing the code in SQL Analyzer, it resulted NOT in a table scan, but rather a series of nested loops (the joins) and clustered index seeks...resulting in about 1/3 the total cost of the function.

I suspected originally that it was the TOP/ORDER BY that the function insisted upon, but even if I remove those, still get a table scan.

Wassup? Why does the function turn my cool lil' self-join into a table scan? Whut am I missing? Any thoughts? Disgusted Derisions? Hurled Insults? Bring it on!!! (please! ;) )

My predecessor did this in a similar project using a separate cursor for each portfolio by date, then looped through the dates, pulling in the per-portfolio index value and building the output table. I would rather avoid the cursors if I can.it wasn't the logic inside the function that was the cause of the table scan...but rather the code 'Select * from <function> ' that caused it to be reported as a table scan :( *blush*

So...how does one get an execution plan for what goes on inside a function anyway? Just cut out the code and run it in the analyzer? Seems like ya ought to be able to 'splain the internal cost of a function by calling it too...Hmmm...

Anyway, thanks for reading!|||Highlight it in the object browser on Query Analyzer and hit "Display Estimated Execution Plan". ??|||Highlight it in the object browser on Query Analyzer and hit "Display Estimated Execution Plan". ??

Nope, doesn't give me the results I would like...since in order to execute the function, I have to do something like select * from dbo.fn_Get_Performance_Table ('2004-01-01', '2004-06-01') (my function returns a table...). So the execution plan displayed just gives me a table scan due to the "select * from" part of the overall query.

I just used cut-n-paste to copy the internal function code to the QA window, then used "show execution plan" to actually get what I need...just thought (or hoped) there was a way to "see inside" the function in an explain (sorry...Tandem on the brain...OUCH!) ...errr...EXECUTION plan...

Thanks for taking a shot!|||This has been a bit of a problem with the Show Estimated Execution feature in Query Analyzer. It is blind to the costs of UDFs. I have come across a number of postings, and anecdotes about people using functions, and being really happy about how clean their code looks. After they test it, though, they go back to the ugly code without functions, and vow never to drink-er, that is, never to use functions again.

The performance problem in a function is that if you have a query in the function, that goes out and racks up say 20 reads and 3 ticks of CPU, then you put that function into the field list of a query that returns 1000 rows, you have a query that runs up 20,000 reads, and 3 full seconds of CPU time.

As with anything, of course, use caution and test.

Is my self-referencing table well built ?

here is my CREATE TABLE statement:

/* Sections Table */
CREATE TABLE PsychoCMS.dbo.Sections (
ID Int NOT NULL IDENTITY(0,1)
CONSTRAINT PK_Section_ID PRIMARY KEY,
Father Int NULL
CONSTRAINT FK_Section_Father
FOREIGN KEY REFERENCES PsychoCMS.dbo.Sections(ID),
Title Nvarchar(50) NOT NULL,
InitialDoc Int NULL
CONSTRAINT FK_Section_InitialDoc
FOREIGN KEY REFERENCES PsychoCMS.dbo.Documents(ID)
)
ALTER TABLE PsychoCMS.dbo.Sections
ADD CONSTRAINT DF_Section_Father
DEFAULT 0 FOR Father

Since each section can be a child of another section the Father field reference the ID field.
I think everything fine, but I'm not sure about the root sections. I made the Father field nullable so if the field is empty, it mean it's a root section, but is this the good way to go ? Will I get error from foreign key when I try to add a root section ?Looks fine to me.|||Thanks a lot.