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 Northwind database fully optimised ?

Hi Guys
I am trying to figure out how to optimise a sql server database. For
starters I pumped in to the orders table of Northwind database around
120,000 records.
I have now queried the database to retrive those records.
A simple join query like this
SELECT * FROM Orders O INNER JOIN Employees E ON E.Employeeid=O.Employeeid
takes 22 minutes to execute. I know its a Select * from query but its just
120,000 records. I would imagine this number of records to be fairly normal
in most applications.
Another query
SELECT E.*
FROM Orders O INNER JOIN Employees E ON E.Employeeid=O.Employeeid
takes over 4 minutes to execute.
My applications have started to grow to such large sizes and I want to
optimise the reporting tools.
What are the suggestions to increase these queries and to optimise the
database. It is pretty clear Northwind database supplied by MS is not
optimised to handle more than 120,000 records. Index tuning wizard does not
recommend any new indexes as well.
Thanks for any suggestions.
VkHi
In today's terms Northwind is a DB that shows you most of the things that
are bad practice.
It is far from optimized and far from a good model. The model is so old that
probably the developer at Microsoft who created it has retired.
http://www.sql-server-performance.com/ is a good place to start.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"call me VK" <orthopodSPAM74@.yahoo.co.uk> wrote in message
news:3ofs8pF5pgbcU1@.individual.net...
> Hi Guys
> I am trying to figure out how to optimise a sql server database. For
> starters I pumped in to the orders table of Northwind database around
> 120,000 records.
> I have now queried the database to retrive those records.
> A simple join query like this
> SELECT * FROM Orders O INNER JOIN Employees E ON E.Employeeid=O.Employeeid
> takes 22 minutes to execute. I know its a Select * from query but its just
> 120,000 records. I would imagine this number of records to be fairly
> normal
> in most applications.
> Another query
> SELECT E.*
> FROM Orders O INNER JOIN Employees E ON E.Employeeid=O.Employeeid
> takes over 4 minutes to execute.
> My applications have started to grow to such large sizes and I want to
> optimise the reporting tools.
> What are the suggestions to increase these queries and to optimise the
> database. It is pretty clear Northwind database supplied by MS is not
> optimised to handle more than 120,000 records. Index tuning wizard does
> not
> recommend any new indexes as well.
> Thanks for any suggestions.
> Vk
>
>|||Northwind sucks. And so do the other Vendor's sample RDBMS schemas and
sample data (Oracle's Scott/Tiger, Centura's presidents, etc.) Most
of the are not normalized, etc.
You might want to actually read a book about database design, RDBMS,
etc. And you might want to learn that rows are not records. Your
whole approach to learning by lookng at bad code is wrong.