I need a simple list of all of the datatypes that are available for use in SQL Server 2012.

I found this SQL Server Site , this .NET site and this 3rd Party site but they do not have the information in a useful form.

So far, I have spent a couple of days evolving a simple table that is stored in my local copy of SQL Server 2012.

You can see where I am up to by looking at this png image of a query output from my SQL Server 2012 table.

My questions

1: Has this been done before? - If so - where is the table?

2: From where can I get the data that is missing from my table?

3: What is the easiest way to publish the query output. (I fiddled around with Excel but it seems to need a manual format)

Thanks

 

It's documented in SQL Server Books Online.

Data Types (Transact-SQL)

David

Thanks David.
Yes, I already looked at that - I mentioned it in my initial post.

Regarding "It's documented..."
The form of the "documentation" in the Data Types (Transact-SQL)  is what I would call "turgid"- and that's me being polite!

You have to read through reams of text to find what you want - and the text is not always precise.
I think that that "documentation" is really awful and a big time waster!
Amongst other things, what is the purpose of the taxonomies of "data Type Categories" and "groups".

It seems to me that since there are less than 30 different data types, structuring the documentation according to these  taxonomies just wastes the readers time because the reader has to start by trying to make sense of the classification system before drilling down to a lower level.

I'm trying to make a simple list that presents the data in a clear and unambiguous way.

The Data Types (Transact-SQL)  reference does not meet those criteria.

Hi Ken

I cannot see the image you posted above, but can you provide a sample of the table/query you are looking for.. I found the link that David posted pretty clear and contains everything you should know about datatypes in SQL Server. Anyway, take a look at below query

SELECT * FROM sys.types

or 

SELECT s.name AS 'schema', ts.name AS TableName,
c.name AS column_name, c.column_id,
SCHEMA_NAME(t.schema_id) AS DatatypeSchema,
t.name AS Datatypename
,t.is_user_defined, t.is_assembly_type
,c.is_nullable, c.max_length, c.PRECISION,
c.scale
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id
INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.schemas s ON s.schema_id = ts.schema_id
ORDER BY s.name, ts.name, c.column_id

Hi Uri,

Sorry about the image. (Onedrive problems)

I moved it to Screencast.  Try clicking here.

Thanks

Hi Uri,

SELECT * FROM sys.types
Is good - a nice definitive list of 34 items with additional data.

Any suggestions on a similarly good source for the "notes" column?

Thanks

Hi Uri,

That was a great help but I'm now having trouble with using the query.
What I want to do is to put the output of the query into a database table so that I can use it in further work.

When I use the following query, I get a success message that says:  "(34 row(s) affected)" -  But I can't see the new table in Object Explorer even after lots of refreshes and stop/starts of SQLSMS.

SELECT name,max_length,precision,scale, collation_name,is_nullable,is_assembly_type 
INTO S2102DataTypes1 
FROM sys.types
ORDER BY name;

When I run the query again I get the following error message:
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'S2102DataTypes1' in the database.

BUT - I can't see this table despite lots of refreshes and stop/starts of SQLSMS

Any clues?

OK - I found the new database under System Databases>Master.

What did I do wrong?
How can I re-write the query so that the new database shows up in the "normal" database section?

OK - So I created a new database first and then added "USE - GO" as the first rows of the query.