declare dynamic variable text type

Question

declare dynamic variable text type

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
October 15, 2010 at 9:40 am

#228072

I am using the below code:
declare @test-2 ntext
set @test-2='Hello'
print @test-2
and doesn't works.
Yes, i do not want to use nvarchar because the sql generated is more than 8000 characters. I thought i could use text data type and pass it as string but seems like it is not that simple. Ofcourse i can concatenate two strings but was trying to get in a much better way. Any suggestions?

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

The text datatype is depreciated, and will be removed in the next version of SQL Server. You should use (n)varchar(max) instead.

So, this does work:

declare @test-2 nvarchar(max)

set @test-2='Hello'

print @test-2

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

Thanks Wayne, as i said in my first post the o/p is more than 8000 characters so i cant use nvarchar.Any idea?

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

nvarchar(max) will allow for the length to be pretty much as large as it needs.

http://msdn.microsoft.com/en-us/library/ms186939%28v=SQL.90%29.aspx

_______________________________________________________________

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/

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

Do be careful, there can be HUGE performance losses when converting to nvarchar(max) on large amount of data.

_______________________________________________________________

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/

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 5

hmm.. i don't completely agree."Unless the text is over 8,000 bytes at which point it behaves like an NTEXT and stores the text value in the LOB".

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

That was my point. The OP said his data is over 8,000 in length. 🙂

_______________________________________________________________

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/

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 7

sqldba_icon (10/15/2010)
hmm.. i don't completely agree."Unless the text is over 8,000 bytes at which point it behaves like an NTEXT and stores the text value in the LOB".

You don't completely agree with who's statement? Wayne's or Sean's?

Remember that just because the engine treats the nvarchar(Max) like an NText doesn't mean that it's just another name for NText. NText is being removed in a future edition of SQL Server. And Microsoft won't necessarily tell us when they do it because everyone's been warned ahead of time that the datatype is deprecated. That, as far as they're concerned, is warning enough to not use it.

Second, have you tried using SELECT instead of PRINT? PRINT is just... I dunno, ugly? It's not actually one of the commands that makes for clean, readable T-SQL Code. This is just my opinion, though. I avoid the use of that word in T-SQL because I don't want to flip from the Results screen to the Messages screen every time I want to see something.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 8

Thanks Brandie, and i was refering to Wayne's statement. There is limit of 8000 characters on nvarchar and my sql dynamically generates more than 8000 characters of code and stores in a variable of type nvarhar(max). So i am not able to store more than 8000 characters and was wondering if there is any other data type where i can use instead of using nvarchar? Thanks

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 9

sqldba_icon (10/18/2010)
Thanks Brandie, and i was refering to Wayne's statement. There is limit of 8000 characters on nvarchar and my sql dynamically generates more than 8000 characters of code and stores in a variable of type nvarhar(max). So i am not able to store more than 8000 characters and was wondering if there is any other data type where i can use instead of using nvarchar? Thanks

Nvarchar(Max) is the best choice you have in this situation. Don't mistake it for an 8000 character only datatype. It holds up to 2 GB worth of data, which is why it's treated differently after it reaches the 8000 max.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 10

True Brandie, but after it reaches 8000 characters i need to do some conversion right? How do developers do this task in real world?I am sure there would be code more than 8000 characters and how do they achieve this?

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 11

No. You don't convert. Just create the datatype as type Nvarchar(Max). It's really that simple.

EDIT: SQL Server will known how to handle your datatype and will only use the amount of space it needs for each record. "Trust the Server, Luke."

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

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

What changes after it exceeds 8,000 characters is how sql stores it internally. From the developer side there is no change required. SQL looks at the field and knows how to retrieve the data. It is capable of handling pretty much any amount of data. 2GB is an absolutely enormous amount of space at 2 bytes per character.

_______________________________________________________________

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/