|
|
You're in the
sql server programming
group:varchar vs. text in stored procs
sql server programming:
Just a quick one which I have not been able to find a straight answer for yet... When programming a stored proc, you can declare a list of parameters in the top. Currently I am using varchar 8000 to collect a long string of information which is then broken into an array from entered information on the web page which calls the process. However as you can imagine, this limits my input capabilities. If I was to change the variable from varchar 8000 to text would this make a difference in call time? I know when data is stored in a database in text or ntext it's stored in a file off the database, is this the same for declared parameters? Are there any 'down sides' to using text instead of varchar 8000 for this type of application? Any help would be appreciated! - Steve
Text fields ARE stored in the database, but in a different structure... They
have been (traditionally ) slower to access and update ( so do some testing)... The downside to using text/image data types in a stored procedure is that you can NOT create a variable of these data types, therefore you can not pass a parameter of these data types... You would have to create a row in the database with a key and the text value and pass the key to the row to the SP. Then the SP would have to look up the row and do the work, then delete the row ( I suppose). -- Wayne Snyder, MCDBA, SQL Server MVP Mariner, Charlotte, NC www.mariner-usa.com (Please respond only to the newsgroups.) I support the Professional Association of SQL Server (PASS) and it's community of SQL Server professionals. www.sqlpass.org [quoted text, click to view] "Anubis" <anubis@bluestreem.com.au> wrote in message news:%23xtuhUhQEHA.396@TK2MSFTNGP12.phx.gbl... > Hi All, > > > Just a quick one which I have not been able to find a straight answer for > yet... > > When programming a stored proc, you can declare a list of parameters in the > top. Currently I am using varchar 8000 to collect a long string of > information which is then broken into an array from entered information on > the web page which calls the process. However as you can imagine, this > limits my input capabilities. > > If I was to change the variable from varchar 8000 to text would this make a > difference in call time? I know when data is stored in a database in text > or ntext it's stored in a file off the database, is this the same for > declared parameters? > > Are there any 'down sides' to using text instead of varchar 8000 for this > type of application? > > Any help would be appreciated! > > - Steve > > > >
But the upside is that you can store up to 2GB in a single row :)
Also, you can pass parameters of type text, you just cannot do any manipulation with the parameter: create table testText ( testTextId int primary key, textCol text ) go create procedure testTextInsert ( @testTextId int, @textCol text ) as set nocount on begin insert into testText (testTextId, textCol) values (@testTextId, @textCol) end go exec testTextInsert 1, 'text' go It would be way to ugly to demonstrate that you can insert > 8000 chars, but it does work (I tested it with 10000.) -- ---------------------------------------------------------------------------- Louis Davidson (drsql@hotmail.com) Compass Technology Management Pro SQL Server 2000 Database Design http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies will be ignored :) [quoted text, click to view] "Wayne Snyder" <wsnyder@computeredservices.com> wrote in message news:ulyhENkQEHA.3988@tk2msftngp13.phx.gbl... > Text fields ARE stored in the database, but in a different structure... They > have been (traditionally ) slower to access and update ( so do some > testing)... > > The downside to using text/image data types in a stored procedure is that > you can NOT create a variable of these data types, therefore you can not > pass a parameter of these data types... You would have to create a row in > the database with a key and the text value and pass the key to the row to > the SP. Then the SP would have to look up the row and do the work, then > delete the row ( I suppose). > > > > -- > Wayne Snyder, MCDBA, SQL Server MVP > Mariner, Charlotte, NC > www.mariner-usa.com > (Please respond only to the newsgroups.) > > I support the Professional Association of SQL Server (PASS) and it's > community of SQL Server professionals. > www.sqlpass.org > > > "Anubis" <anubis@bluestreem.com.au> wrote in message > news:%23xtuhUhQEHA.396@TK2MSFTNGP12.phx.gbl... > > Hi All, > > > > > > Just a quick one which I have not been able to find a straight answer for > > yet... > > > > When programming a stored proc, you can declare a list of parameters in > the > > top. Currently I am using varchar 8000 to collect a long string of > > information which is then broken into an array from entered information on > > the web page which calls the process. However as you can imagine, this > > limits my input capabilities. > > > > If I was to change the variable from varchar 8000 to text would this make > a > > difference in call time? I know when data is stored in a database in text > > or ntext it's stored in a file off the database, is this the same for > > declared parameters? > > > > Are there any 'down sides' to using text instead of varchar 8000 for this > > type of application? > > > > Any help would be appreciated! > > > > - Steve > > > > > > > > > >
Steve -
[quoted text, click to view] >I know when data is stored in a database in text > or ntext it's stored in a file off the database, is this the same for > declared parameters? Text, ntext and image values are stored on the Text/Image pages separately from the other data, stored on the Data pages. SQL Server 2000 now supports a "text in row" table option that specifies that small text, ntext, and image values be placed in the Data pages with other data values in the same data row instead of Text/Image pages. This increases the speed of read and write operations and reduces the amount of space used to store small text, ntext, and image data values. You can set a text in row table option with sp_tableoption stored procedure [quoted text, click to view] > If I was to change the variable from varchar 8000 to text would this make a > difference in call time? MSDN on "text in row" and performance - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ta-tz_1cfi.asp Text performance tips for SQL Server 2000 - http://www.sql-server-performance.com/database_design.asp http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=309 Hope this helps answer some of your questions. -- Regards, Ryan [quoted text, click to view] "Anubis" <anubis@bluestreem.com.au> wrote in message news:%23xtuhUhQEHA.396@TK2MSFTNGP12.phx.gbl... > Hi All, > > > Just a quick one which I have not been able to find a straight answer for > yet... > > When programming a stored proc, you can declare a list of parameters in the > top. Currently I am using varchar 8000 to collect a long string of > information which is then broken into an array from entered information on > the web page which calls the process. However as you can imagine, this > limits my input capabilities. > > If I was to change the variable from varchar 8000 to text would this make a > difference in call time? I know when data is stored in a database in text > or ntext it's stored in a file off the database, is this the same for > declared parameters? > > Are there any 'down sides' to using text instead of varchar 8000 for this > type of application? > > Any help would be appreciated! > > - Steve > > > >
Hello All,
Thank you for your assistance. It's certainly given me some food for thought. :-) -Steve [quoted text, click to view] "Ryan" <pdtbn1094@excite.com> wrote in message news:SsQsc.9372$y04.3382@fe1.columbus.rr.com... > Steve - > > >I know when data is stored in a database in text > > or ntext it's stored in a file off the database, is this the same for > > declared parameters? > > Text, ntext and image values are stored on the Text/Image pages separately > from the other data, stored on the Data pages. > SQL Server 2000 now supports a "text in row" table option that specifies > that small text, ntext, and image values be placed in the Data pages with > other data values in the same data row instead of Text/Image pages. This > increases the speed of read and write operations and reduces the amount of > space used to store small text, ntext, and image data values. > You can set a text in row table option with sp_tableoption stored procedure > > If I was to change the variable from varchar 8000 to text would this make > a > > difference in call time? > > MSDN on "text in row" and performance - > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ta-tz_1cfi.asp > > Text performance tips for SQL Server 2000 - > > http://www.sql-server-performance.com/database_design.asp > http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=309 > > Hope this helps answer some of your questions. > > -- > Regards, > > Ryan > "Anubis" <anubis@bluestreem.com.au> wrote in message > news:%23xtuhUhQEHA.396@TK2MSFTNGP12.phx.gbl... > > Hi All, > > > > > > Just a quick one which I have not been able to find a straight answer for > > yet... > > > > When programming a stored proc, you can declare a list of parameters in > the > > top. Currently I am using varchar 8000 to collect a long string of > > information which is then broken into an array from entered information on > > the web page which calls the process. However as you can imagine, this > > limits my input capabilities. > > > > If I was to change the variable from varchar 8000 to text would this make > a > > difference in call time? I know when data is stored in a database in text > > or ntext it's stored in a file off the database, is this the same for > > declared parameters? > > > > Are there any 'down sides' to using text instead of varchar 8000 for this > > type of application? > > > > Any help would be appreciated! > > > > - Steve > > > > > > > > > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |