t-sql 2012 datatype

Question

t-sql 2012 datatype

wendy elizabeth

SSCoach

Points: 16915
More actions
July 12, 2016 at 2:08 pm

#328197

In sql server 2012, I would like to know what datatype can hold the maximum number of characters? (The datatype will need to contain letters, numbers, and potentially any special characters.)
In addition, to insert, update and delete data from this datatype, is there any special t-sql that is required?
I am asking this question since I know sql server 2000 had the blob datatype and that required some 'special' t-sql to update the data in this data type.
In addition, would you tell me what other few datatypes are available and what the maximum number of characters each datatype can hold?
**Note, I do not need any data that contains 'N' values since I am only working with data where people speak English.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Raphra Mr or Mrs. 500 Points: 530 More actions · Answer 1

I say you could go with varchar(MAX). "max" for varchar(max) indicates a maximum storage of 2^31-1 bytes. SQL 2012 does support text datatype, MSDN recommends using varchar(max) instead.

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 2

I wouldn't use TEXT. It's on the deprecation list.

Here are the data types in the SQL Server documentation. You're describing what is CHAR, VARCHAR or TEXT, only you shouldn't use TEXT. You can click on each of the data types in the list to get the details of the type.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

I agree with Grant 100000% on not using text. My only change would one of not being as subtle as "you shouldn't use the text datatype". I would say you need to run away from that datatype like it is an atomic bomb. It is awful to work with, you can't search it unless you cast it first. You can't index it. The only good about it is that I have never had an issue with inplace data change to a varchar. 😛

Many times I see tables using varchar(max) but it is serious overkill. The columns contain 1-2k characters. The varchar datatype can hold up to 8,000 characters which is an awful lot of information. Unless you actually need to store that much information you don't need the max datatype.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 4

wendy elizabeth (7/12/2016)
In sql server 2012, I would like to know what datatype can hold the maximum number of characters? (The datatype will need to contain letters, numbers, and potentially any special characters.)
In addition, to insert, update and delete data from this datatype, is there any special t-sql that is required?
I am asking this question since I know sql server 2000 had the blob datatype and that required some 'special' t-sql to update the data in this data type.
In addition, would you tell me what other few datatypes are available and what the maximum number of characters each datatype can hold?
**Note, I do not need any data that contains 'N' values since I am only working with data where people speak English.

My recommendation would be to stop whatever you're doing and read up on two things from "Books Online"...

1. All datatypes

2. All functions

You may not use all of either but 1) you'll learn how to find the answers to such basic questions and 2) you'll at least be familiar to the point of knowing something is at least possible.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

durga.palepu Hall of Fame Points: 3530 More actions · Answer 5

text datatype is supported in SQL 2012 but will be deprecated in future versions.

alternative is to use varchar(max) and if you need multilingual support use nvarchar(max) datatype.

Thanks,

Durga

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

durga.palepu (7/12/2016)
text datatype is supported in SQL 2012 but will be deprecated in future versions.
alternative is to use varchar(max) and if you need multilingual support use nvarchar(max) datatype.
Thanks,
Durga

Actually it has been deprecated since SQL 2005. They should have removed it by now but for some reason it is still around.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 7

Sean,

Don't some of the Internal Diagnostic views still use TEXT data types or have they all been changed to (n)varchar(max) datatypes?

I also believe that when you pipe an SQL trace log into a table rather than file the TEXTDATA column is classified as an NTEXT

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 8

durga.palepu (7/12/2016)
text datatype is supported in SQL 2012 but will be deprecated in future versions.
alternative is to use varchar(max) and if you need multilingual support use nvarchar(max) datatype.
Thanks,
Durga

Yeah, trace is still there too. Wish they would clean out some of the stuff they tell us they're going to clean out.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Jason-299789 (7/13/2016)
Sean,
Don't some of the Internal Diagnostic views still use TEXT data types or have they all been changed to (n)varchar(max) datatypes?
I also believe that when you pipe an SQL trace log into a table rather than file the TEXTDATA column is classified as an NTEXT

Yes I believe some of them still do. This is probably why they haven't actually removed it, because they can't bother to fix their own code.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 10

Sean Lange (7/13/2016)
Jason-299789 (7/13/2016)
Sean,
Don't some of the Internal Diagnostic views still use TEXT data types or have they all been changed to (n)varchar(max) datatypes?
I also believe that when you pipe an SQL trace log into a table rather than file the TEXTDATA column is classified as an NTEXT
Yes I believe some of them still do. This is probably why they haven't actually removed it, because they can't bother to fix their own code.

Thanks for the clarification.

What Microsoft not removing depreciated features in their own code, I'm shocked!!!!! :crazy:

_________________________________________________________________________
SSC Guide to Posting and Best Practices

wendy elizabeth SSCoach Points: 16915 More actions · Answer 11

What other data type would you recommend? That is large but not as large as varchar(max) is?

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

wendy elizabeth (7/13/2016)
What other data type would you recommend? That is large but not as large as varchar(max) is?

Define large. A varchar can be as large as varchar(8000). That is quite a bit of data. If you need more than 8,000 characters you would need to use varchar(max).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 13

wendy elizabeth (7/13/2016)
What other data type would you recommend? That is large but not as large as varchar(max) is?

I'm curious now, what are you trying to store? And which data type is not large enough?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning