October 22, 2007 at 1:11 am
Hi All,
Can you Please help me in this topic?
I executed the following Sql Script and Its working Fine,
CREATE TABLE t1 (a int, b int, c AS a/b);
---------
INSERT INTO t1 VALUES (10, 5);
---------
SELECT * FROM t1;
But I wants to know,
1st : - the column 'c' is Which type of datatype?
2nd : - How "c AS a/b" is working even we are not specifying the
datatype in the DDL statement?
If so, then Please tell me how it internally works in SQL server,
basically the command "c AS a/b" script.
Cheers!
Sandy
--
October 22, 2007 at 1:43 am
Hi Sandy,
you can get the above information form the system views. Schema objects are in sys.objects, columns are in sys.columns, data types are in sys.types (I assume you are on 2005 since this is a 2005 forum 🙂 on 2000 these are different (dbo.sysobject, dbo.syscolumns, dbo.systypes)
You can get the type information from these like:
SELECT c.name
, t.name
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id = t.user_type_id
WHERE object_id = OBJECT_ID('t1')
Concerning what the resulting datatype is SQL Server will decide on its own (and if you use user defined types, then SQL Server will decide differently depending on its version).
Regards,
Andras
October 22, 2007 at 1:47 am
Hi Sandy,
in addition to my previous post, you can get more information about the computed columns from the sys.computed_columns view. This also contains the computed expression you have specified. Note that the expression is NOT 100 per cent the same. SQL Server changes this in a few ways. You can see more info on this on my blog http://www.simple-talk.com/community/blogs/andras/archive/2005/11/04/1.aspx
Regards,
Andras
October 22, 2007 at 2:25 am
Hi Andras Belokosztolszki,
Thanks for your quick reply,
I got the solution,
Thanks again for the same.
Cheers!
Sandy.
--
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply