How To Check Column Data Types In SQL Server Database – 2 Secrets To Be SQL Master!

You are currently viewing How To Check Column Data Types In SQL Server Database – 2 Secrets To Be SQL Master!
Share This Post, Help Others, And Earn My Heartfelt Appreciation! :)
5
(2287)

There are at least a few ways to get column data types in Microsoft SQL Server database. I will show you how to check column data types in SQL Server database TWO the most popular ways and explain you the difference between MAX_LENGH (CHARACTER_OCTET_LENGTH) and CHARACTER_MAXIMUM_LENGTH columns.

How to check column data types in SQL Server?

First Method

The first way to check data types for SQL Server database is query with SYS schema table. The below query uses COLUMNS and TYPES tables:

SELECT C.NAME AS COLUMN_NAME,
       TYPE_NAME(C.USER_TYPE_ID) AS DATA_TYPE,
       C.IS_NULLABLE,
       C.MAX_LENGTH,
       C.PRECISION,
       C.SCALE
FROM SYS.COLUMNS C
JOIN SYS.TYPES T
     ON C.USER_TYPE_ID=T.USER_TYPE_ID
WHERE C.OBJECT_ID=OBJECT_ID('your_table_name');

Second Method

The other way to check data types is the statement with using INFORMATION_SCHEMA database. In the below statement you need COLUMNS table:

SELECT COLUMN_NAME,
       DATA_TYPE,
       IS_NULLABLE,
       CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION,
       NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='your_table_name';

If you try use the both of these queries, you can see that for some attributes MAX_LENGTH have bigger value than CHARACTER_MAXIMUM_LENGTH for the second query. But why?

Difference between MAX_LENGH and CHARACTER_MAXIMUM_LENGTH

The MAX_LENGTH attributes from SYS.COLUMNS table stores maximum length in bytes, whereas CHARACTER_MAXIMUM_LENGTH stores maximum length in characters.

For attributes with char or varchar data types is does not matter, but for nchar or nvarchar it is. MAX_LENGTH attribute will be the same like CHARACTER_OCTET_LENGTH at INFORMATION_SCHEMA.COLUMNS table.

About Data Types

Each column, expression, parameter and local variable has a data type – an attribute that specifies the type of data that the object can store like:

  • Integer
  • Character
  • Monetary
  • Date and time
  • Binary strings
  • and so on.

Using Transact-SQL you can also create your own data types in the Microsoft .NET Framework. Please find the example of DDL script to create table in SQL Server database:

CREATE TABLE Test_BigData_ETL (
    ID int,
    NAME varchar(255),
    PAGE_URL nvarchar(255),
    RATING decimal(3,2),
    LOCATION varchar(100)
);

SQL Server Management Studio

If you are wondering which program is the best to support SQL Server database, my favourite is: SQL Management Studio.
For a simple reason – it is written by Microsoft and is a native tool for maintaining the SQL Server database.

To download the latest version of SQL Server Management Studio, please visit the publisher’s official website.

If you enjoyed this post please add the comment below and share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 2287

No votes so far! Be the first to rate this post.

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments