Maximum Row Size in SQL Server 2000

Using this code:
CREATE TABLE [dbo].[test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[COMMENT1] [varchar] (8000) NULL ,
[COMMENT2] [varchar] (8000) NULL
) ON [PRIMARY]
GO
I get the error: Warning: The table 'test' has been created but its maximum
row size (16029) exceeds the maximum number of bytes per row (8060).
However, I can create a table with smaller field lengths:
CREATE TABLE [dbo].[test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[COMMENT1] [varchar] (10) NULL ,
[COMMENT2] [varchar] (10) NULL
) ON [PRIMARY]
GO
I can then alter the field lengths in Enterprise Manager back to 8000 and
not get the error. I also notice I can import data from a text file and a
table will be created with numerous varchar fields that are each 8000 in
size. Why cannot I create the table with multiple 8000 length fields but SQL
Server allows me to modify an existing table or import into a table that has
multiple 8000 length fields?
Thank you.Hi,
like you said, you get a "Warning" no error. SQL Server just wants to
keep you informed that the data *might* be truncated, if you insert
more than 8000 characters, but the table *will* be created.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||The warning you get is only a warning. The table is still created. Read the warning text carefully.
The table is created, but if you , for a row, try to have > 8060 bytes (when you do INSERT or
UPDATE), then that operation will fail.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian P" <BrianP@.discussions.microsoft.com> wrote in message
news:7F71DB7B-6F5E-4ABC-90AA-A988691D095A@.microsoft.com...
> Using this code:
> CREATE TABLE [dbo].[test] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [COMMENT1] [varchar] (8000) NULL ,
> [COMMENT2] [varchar] (8000) NULL
> ) ON [PRIMARY]
> GO
> I get the error: Warning: The table 'test' has been created but its maximum
> row size (16029) exceeds the maximum number of bytes per row (8060).
> However, I can create a table with smaller field lengths:
> CREATE TABLE [dbo].[test] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [COMMENT1] [varchar] (10) NULL ,
> [COMMENT2] [varchar] (10) NULL
> ) ON [PRIMARY]
> GO
> I can then alter the field lengths in Enterprise Manager back to 8000 and
> not get the error. I also notice I can import data from a text file and a
> table will be created with numerous varchar fields that are each 8000 in
> size. Why cannot I create the table with multiple 8000 length fields but SQL
> Server allows me to modify an existing table or import into a table that has
> multiple 8000 length fields?
> Thank you.
>
>
>|||Thank you for the information. So I can have a table with numerous varchar
8000 fields and I'm ok as long as a single inserted row does not contain more
than 8060 characters. Is this correct?
"Tibor Karaszi" wrote:
> The warning you get is only a warning. The table is still created. Read the warning text carefully.
> The table is created, but if you , for a row, try to have > 8060 bytes (when you do INSERT or
> UPDATE), then that operation will fail.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
> news:7F71DB7B-6F5E-4ABC-90AA-A988691D095A@.microsoft.com...
> > Using this code:
> >
> > CREATE TABLE [dbo].[test] (
> > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> > [COMMENT1] [varchar] (8000) NULL ,
> > [COMMENT2] [varchar] (8000) NULL
> > ) ON [PRIMARY]
> > GO
> >
> > I get the error: Warning: The table 'test' has been created but its maximum
> > row size (16029) exceeds the maximum number of bytes per row (8060).
> >
> > However, I can create a table with smaller field lengths:
> >
> > CREATE TABLE [dbo].[test] (
> > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> > [COMMENT1] [varchar] (10) NULL ,
> > [COMMENT2] [varchar] (10) NULL
> > ) ON [PRIMARY]
> > GO
> >
> > I can then alter the field lengths in Enterprise Manager back to 8000 and
> > not get the error. I also notice I can import data from a text file and a
> > table will be created with numerous varchar fields that are each 8000 in
> > size. Why cannot I create the table with multiple 8000 length fields but SQL
> > Server allows me to modify an existing table or import into a table that has
> > multiple 8000 length fields?
> >
> > Thank you.
> >
> >
> >
> >
> >
> >
>
>|||Try it :
INSERT INTO TEST VALUES (REPLICATE('*', 80000), REPLICATE('*', 44))
A +
Brian P a écrit :
> Thank you for the information. So I can have a table with numerous varchar
> 8000 fields and I'm ok as long as a single inserted row does not contain more
> than 8060 characters. Is this correct?
> "Tibor Karaszi" wrote:
>> The warning you get is only a warning. The table is still created. Read the warning text carefully.
>> The table is created, but if you , for a row, try to have > 8060 bytes (when you do INSERT or
>> UPDATE), then that operation will fail.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
>> news:7F71DB7B-6F5E-4ABC-90AA-A988691D095A@.microsoft.com...
>> Using this code:
>> CREATE TABLE [dbo].[test] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [COMMENT1] [varchar] (8000) NULL ,
>> [COMMENT2] [varchar] (8000) NULL
>> ) ON [PRIMARY]
>> GO
>> I get the error: Warning: The table 'test' has been created but its maximum
>> row size (16029) exceeds the maximum number of bytes per row (8060).
>> However, I can create a table with smaller field lengths:
>> CREATE TABLE [dbo].[test] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [COMMENT1] [varchar] (10) NULL ,
>> [COMMENT2] [varchar] (10) NULL
>> ) ON [PRIMARY]
>> GO
>> I can then alter the field lengths in Enterprise Manager back to 8000 and
>> not get the error. I also notice I can import data from a text file and a
>> table will be created with numerous varchar fields that are each 8000 in
>> size. Why cannot I create the table with multiple 8000 length fields but SQL
>> Server allows me to modify an existing table or import into a table that has
>> multiple 8000 length fields?
>> Thank you.
>>
>>
>>
>>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Correct. And, btw, this restriction has been removed in SQL Server 2005 ("page overflow").
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian P" <BrianP@.discussions.microsoft.com> wrote in message
news:4AA95B81-5246-470E-A270-11810106F346@.microsoft.com...
> Thank you for the information. So I can have a table with numerous varchar
> 8000 fields and I'm ok as long as a single inserted row does not contain more
> than 8060 characters. Is this correct?
>|||Brian
But be aware that SQL Server 2005 row_overflow data only applies to variable
length fields.
You cannot have multiple char(8000) columns, for example.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ecx6jdYXGHA.4132@.TK2MSFTNGP04.phx.gbl...
> Correct. And, btw, this restriction has been removed in SQL Server 2005
> ("page overflow").
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
> news:4AA95B81-5246-470E-A270-11810106F346@.microsoft.com...
>> Thank you for the information. So I can have a table with numerous
>> varchar
>> 8000 fields and I'm ok as long as a single inserted row does not contain
>> more
>> than 8060 characters. Is this correct?|||Thanks everyone for the good information. I appreciate the feedback.
Brian
"Kalen Delaney" wrote:
> Brian
> But be aware that SQL Server 2005 row_overflow data only applies to variable
> length fields.
> You cannot have multiple char(8000) columns, for example.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:ecx6jdYXGHA.4132@.TK2MSFTNGP04.phx.gbl...
> > Correct. And, btw, this restriction has been removed in SQL Server 2005
> > ("page overflow").
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
> > news:4AA95B81-5246-470E-A270-11810106F346@.microsoft.com...
> >> Thank you for the information. So I can have a table with numerous
> >> varchar
> >> 8000 fields and I'm ok as long as a single inserted row does not contain
> >> more
> >> than 8060 characters. Is this correct?
> >>
>
>

maximum row size exceeds the maximum number of bytes per row (8060

Hi,
I have created a table (say Table1) with few columns, with one of the
columns (say column1) having the data type as Varchar(8000).
Now I run the below query -
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
if not exists (select * from dbo.syscolumns
where id = object_id(N'[dbo].[Table1]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1
and name = 'Column1')
ALTER TABLE dbo.Table1 ADD
Column1 varchar(500) NULL
GO
COMMIT
Now that the column Column1 already exists, the add column statement won't
be executed. But still I get the warning -
"Warning: The table 'Table1' has been created but its maximum row size
(8579) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes."
My question is, now that the column already exists, the statement itself
won't be executed. Then why do I still get this warning?That has to do with the sequence of query processing and when the warning is generated. Obviously
the warning (the fact that this table will have a row size for which you can exceed the limit in
your data) is generated in a stage which is earlier than when the statements are actually executed.
In other words, the If statement haven't been executed at this stage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"swap@.@.@." <swap@.discussions.microsoft.com> wrote in message
news:73AC8FFC-4041-4B22-AB25-4CCDB94598FC@.microsoft.com...
> Hi,
> I have created a table (say Table1) with few columns, with one of the
> columns (say column1) having the data type as Varchar(8000).
> Now I run the below query -
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> if not exists (select * from dbo.syscolumns
> where id = object_id(N'[dbo].[Table1]')
> and OBJECTPROPERTY(id, N'IsUserTable') = 1
> and name = 'Column1')
> ALTER TABLE dbo.Table1 ADD
> Column1 varchar(500) NULL
> GO
> COMMIT
> Now that the column Column1 already exists, the add column statement won't
> be executed. But still I get the warning -
> "Warning: The table 'Table1' has been created but its maximum row size
> (8579) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes."
> My question is, now that the column already exists, the statement itself
> won't be executed. Then why do I still get this warning?
>

Maximum Possible Length

Hello,
Could someone please tell me what is the maximum possible length for a
variable? I am currently using varchar (8000). I need something
larger. I am constructing code and storing it in a variable and then
executing it.
exec(@.Code)
@.Code is just an insert statement. It explicitly lists all of the
columns for a particular table.
Thanks.
I have not tested in that scenario but you can use varchar(max).
Hope this helps,
Ben Nevarez
"Wing9897@.hotmail.com" wrote:

> Hello,
> Could someone please tell me what is the maximum possible length for a
> variable? I am currently using varchar (8000). I need something
> larger. I am constructing code and storing it in a variable and then
> executing it.
> exec(@.Code)
>
> @.Code is just an insert statement. It explicitly lists all of the
> columns for a particular table.
>
> Thanks.
>
|||In SQL Server 2005 you can declare @.Code as varchar(max). If SQL Server 2000
then you can construct the code in multiple variables and concatenate at
execution, like:
exec (@.Code1 + @.Code2 + @.Code3)
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||What version of sql server are you working on ?
Sincerely,
John K
Knowledgy Consulting, LLC
knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
<Wing9897@.hotmail.com> wrote in message
news:b2223f0c-5970-4cab-8910-5d6b2031d5fc@.s8g2000prg.googlegroups.com...
> Hello,
> Could someone please tell me what is the maximum possible length for a
> variable? I am currently using varchar (8000). I need something
> larger. I am constructing code and storing it in a variable and then
> executing it.
> exec(@.Code)
>
> @.Code is just an insert statement. It explicitly lists all of the
> columns for a particular table.
>
> Thanks.

Maximum Possible Length

Hello,
Could someone please tell me what is the maximum possible length for a
variable? I am currently using varchar (8000). I need something
larger. I am constructing code and storing it in a variable and then
executing it.
exec(@.Code)
@.Code is just an insert statement. It explicitly lists all of the
columns for a particular table.
Thanks.I have not tested in that scenario but you can use varchar(max).
Hope this helps,
Ben Nevarez
"Wing9897@.hotmail.com" wrote:
> Hello,
> Could someone please tell me what is the maximum possible length for a
> variable? I am currently using varchar (8000). I need something
> larger. I am constructing code and storing it in a variable and then
> executing it.
> exec(@.Code)
>
> @.Code is just an insert statement. It explicitly lists all of the
> columns for a particular table.
>
> Thanks.
>|||In SQL Server 2005 you can declare @.Code as varchar(max). If SQL Server 2000
then you can construct the code in multiple variables and concatenate at
execution, like:
exec (@.Code1 + @.Code2 + @.Code3)
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||What version of sql server are you working on ?
--
Sincerely,
John K
Knowledgy Consulting, LLC
knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
<Wing9897@.hotmail.com> wrote in message
news:b2223f0c-5970-4cab-8910-5d6b2031d5fc@.s8g2000prg.googlegroups.com...
> Hello,
> Could someone please tell me what is the maximum possible length for a
> variable? I am currently using varchar (8000). I need something
> larger. I am constructing code and storing it in a variable and then
> executing it.
> exec(@.Code)
>
> @.Code is just an insert statement. It explicitly lists all of the
> columns for a particular table.
>
> Thanks.

max(field) but the field value is not always numeric

I need to find the highest number in one column. Here is a catch, the column
type is varchar and some of the rows will have non number character in it. I
am trying to find the highest number ignoring any row that is not a numeric
value. Here is my query.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
Thanks for any input.
You may want to cast the column as an int within the max
SELECT max(cast(item_abrv as int)) as mx from item where
isnumeric(item_abrv) = 1
"UGH" <nospam@.noSPam.com> wrote in message
news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> I need to find the highest number in one column. Here is a catch, the
column
> type is varchar and some of the rows will have non number character in it.
I
> am trying to find the highest number ignoring any row that is not a
numeric
> value. Here is my query.
>
> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
> Thanks for any input.
>
|||I tired that and I got an error. its said converting the varchar value
'100ISBN' to a column of data type int. I just need the query to leave out
any field that has non numeric characters in it.
Thanks.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
> You may want to cast the column as an int within the max
>
> SELECT max(cast(item_abrv as int)) as mx from item where
> isnumeric(item_abrv) = 1
> "UGH" <nospam@.noSPam.com> wrote in message
> news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> column
> I
> numeric
>
|||What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390
AMB
"UGH" wrote:

> I tired that and I got an error. its said converting the varchar value
> '100ISBN' to a column of data type int. I just need the query to leave out
> any field that has non numeric characters in it.
> Thanks.
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
>
>
|||That was it and I modified my query to do this.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1 and
item_abrv not like '%e%' and item_abrv not like '%d%'
Thanks for your help.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E52CDDD8-D5ED-49EA-B6B1-FAA39EC0D07C@.microsoft.com...[vbcol=seagreen]
> What is wrong with IsNumeric()?
> http://www.aspfaq.com/show.asp?id=2390
>
> AMB
> "UGH" wrote:

max(field) but the field value is not always numeric

I need to find the highest number in one column. Here is a catch, the column
type is varchar and some of the rows will have non number character in it. I
am trying to find the highest number ignoring any row that is not a numeric
value. Here is my query.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
Thanks for any input.You may want to cast the column as an int within the max
SELECT max(cast(item_abrv as int)) as mx from item where
isnumeric(item_abrv) = 1
"UGH" <nospam@.noSPam.com> wrote in message
news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> I need to find the highest number in one column. Here is a catch, the
column
> type is varchar and some of the rows will have non number character in it.
I
> am trying to find the highest number ignoring any row that is not a
numeric
> value. Here is my query.
>
> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
> Thanks for any input.
>|||I tired that and I got an error. its said converting the varchar value
'100ISBN' to a column of data type int. I just need the query to leave out
any field that has non numeric characters in it.
Thanks.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
> You may want to cast the column as an int within the max
>
> SELECT max(cast(item_abrv as int)) as mx from item where
> isnumeric(item_abrv) = 1
> "UGH" <nospam@.noSPam.com> wrote in message
> news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> column
> I
> numeric
>|||What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390
AMB
"UGH" wrote:

> I tired that and I got an error. its said converting the varchar value
> '100ISBN' to a column of data type int. I just need the query to leave out
> any field that has non numeric characters in it.
> Thanks.
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
>
>|||That was it and I modified my query to do this.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1 and
item_abrv not like '%e%' and item_abrv not like '%d%'
Thanks for your help.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E52CDDD8-D5ED-49EA-B6B1-FAA39EC0D07C@.microsoft.com...[vbcol=seagreen]
> What is wrong with IsNumeric()?
> http://www.aspfaq.com/show.asp?id=2390
>
> AMB
> "UGH" wrote:
>

max(field) but the field value is not always numeric

I need to find the highest number in one column. Here is a catch, the column
type is varchar and some of the rows will have non number character in it. I
am trying to find the highest number ignoring any row that is not a numeric
value. Here is my query.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
Thanks for any input.You may want to cast the column as an int within the max
SELECT max(cast(item_abrv as int)) as mx from item where
isnumeric(item_abrv) = 1
"UGH" <nospam@.noSPam.com> wrote in message
news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> I need to find the highest number in one column. Here is a catch, the
column
> type is varchar and some of the rows will have non number character in it.
I
> am trying to find the highest number ignoring any row that is not a
numeric
> value. Here is my query.
>
> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
> Thanks for any input.
>|||I tired that and I got an error. its said converting the varchar value
'100ISBN' to a column of data type int. I just need the query to leave out
any field that has non numeric characters in it.
Thanks.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
> You may want to cast the column as an int within the max
>
> SELECT max(cast(item_abrv as int)) as mx from item where
> isnumeric(item_abrv) = 1
> "UGH" <nospam@.noSPam.com> wrote in message
> news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
>> I need to find the highest number in one column. Here is a catch, the
> column
>> type is varchar and some of the rows will have non number character in
>> it.
> I
>> am trying to find the highest number ignoring any row that is not a
> numeric
>> value. Here is my query.
>>
>> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
>> Thanks for any input.
>>
>|||What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390
AMB
"UGH" wrote:
> I tired that and I got an error. its said converting the varchar value
> '100ISBN' to a column of data type int. I just need the query to leave out
> any field that has non numeric characters in it.
> Thanks.
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
> >
> > You may want to cast the column as an int within the max
> >
> >
> > SELECT max(cast(item_abrv as int)) as mx from item where
> > isnumeric(item_abrv) = 1
> >
> > "UGH" <nospam@.noSPam.com> wrote in message
> > news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> >> I need to find the highest number in one column. Here is a catch, the
> > column
> >> type is varchar and some of the rows will have non number character in
> >> it.
> > I
> >> am trying to find the highest number ignoring any row that is not a
> > numeric
> >> value. Here is my query.
> >>
> >>
> >> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
> >>
> >> Thanks for any input.
> >>
> >>
> >
> >
>
>|||That was it and I modified my query to do this.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1 and
item_abrv not like '%e%' and item_abrv not like '%d%'
Thanks for your help.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E52CDDD8-D5ED-49EA-B6B1-FAA39EC0D07C@.microsoft.com...
> What is wrong with IsNumeric()?
> http://www.aspfaq.com/show.asp?id=2390
>
> AMB
> "UGH" wrote:
>> I tired that and I got an error. its said converting the varchar value
>> '100ISBN' to a column of data type int. I just need the query to leave
>> out
>> any field that has non numeric characters in it.
>> Thanks.
>> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
>> news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
>> >
>> > You may want to cast the column as an int within the max
>> >
>> >
>> > SELECT max(cast(item_abrv as int)) as mx from item where
>> > isnumeric(item_abrv) = 1
>> >
>> > "UGH" <nospam@.noSPam.com> wrote in message
>> > news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
>> >> I need to find the highest number in one column. Here is a catch, the
>> > column
>> >> type is varchar and some of the rows will have non number character in
>> >> it.
>> > I
>> >> am trying to find the highest number ignoring any row that is not a
>> > numeric
>> >> value. Here is my query.
>> >>
>> >>
>> >> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
>> >>
>> >> Thanks for any input.
>> >>
>> >>
>> >
>> >
>>

MAX() for non-HEX numbers

Hi,
I sit with a situation where I want to select the biggest number of a
field (varchar) which does NOT have a HEX Number in this field eg:
field
1
2
3E
A5
4
I would like to get 4 returned.
Any ideas ?
Thanx
Technically, 1, 2 and 4 are hex (as well as base 10 and octal). Perhaps you
want to consider only those values that do not have non-numerics?
Try:
select
max (field)
from
MyTable
where
field not like '%[~0-9]%'
Now, if you want numerical comparison of the values, then I'd change this
to"
select
max (cast (field as int))
from
MyTable
where
field not like '%[~0-9]%'
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"AlexC" <alex.caudron@.mail.co.za> wrote in message
news:1130238860.255680.184090@.g43g2000cwa.googlegr oups.com...
Hi,
I sit with a situation where I want to select the biggest number of a
field (varchar) which does NOT have a HEX Number in this field eg:
field
1
2
3E
A5
4
I would like to get 4 returned.
Any ideas ?
Thanx

MAX() for non-HEX numbers

Hi,
I sit with a situation where I want to select the biggest number of a
field (varchar) which does NOT have a HEX Number in this field eg:
field
--
1
2
3E
A5
4
I would like to get 4 returned.
Any ideas ?
ThanxTechnically, 1, 2 and 4 are hex (as well as base 10 and octal). Perhaps you
want to consider only those values that do not have non-numerics?
Try:
select
max (field)
from
MyTable
where
field not like '%[~0-9]%'
Now, if you want numerical comparison of the values, then I'd change this
to"
select
max (cast (field as int))
from
MyTable
where
field not like '%[~0-9]%'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"AlexC" <alex.caudron@.mail.co.za> wrote in message
news:1130238860.255680.184090@.g43g2000cwa.googlegroups.com...
Hi,
I sit with a situation where I want to select the biggest number of a
field (varchar) which does NOT have a HEX Number in this field eg:
field
--
1
2
3E
A5
4
I would like to get 4 returned.
Any ideas ?
Thanx

MAX() for non-HEX numbers

Hi,
I sit with a situation where I want to select the biggest number of a
field (varchar) which does NOT have a HEX Number in this field eg:
field
--
1
2
3E
A5
4
I would like to get 4 returned.
Any ideas ?
ThanxTechnically, 1, 2 and 4 are hex (as well as base 10 and octal). Perhaps you
want to consider only those values that do not have non-numerics?
Try:
select
max (field)
from
MyTable
where
field not like '%[~0-9]%'
Now, if you want numerical comparison of the values, then I'd change this
to"
select
max (cast (field as int))
from
MyTable
where
field not like '%[~0-9]%'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"AlexC" <alex.caudron@.mail.co.za> wrote in message
news:1130238860.255680.184090@.g43g2000cwa.googlegroups.com...
Hi,
I sit with a situation where I want to select the biggest number of a
field (varchar) which does NOT have a HEX Number in this field eg:
field
--
1
2
3E
A5
4
I would like to get 4 returned.
Any ideas ?
Thanx

Max width for indexed column?

Is column width a factor for an indexed column? Would indexing a
VARCHAR(100) be a bad idea? Any links to discussion of this topic?
Thanks for your advice.
- Joe Geretz -'bad' is of course a relative term...
Yes, width of an index will affect performance. This is a LONG topic, but
here a a few thoughts...
* width of a clustered index can be more important to worry about than a NC
index since a clustered index key is propogated through ALL the NC indexex.
* yes, width does matter. Intuitively, it just makes sense. There is less
CPU and much less IO involved for a 10 byte index than a 100 byte index.
Would it be quicker for you to read a list of 10 letter words or 100 letter
sentences? Of course that's a silly example, but yes... narrow indexes will
be better all things considered.
* having a wide index isn't the end of hte world and probably isn't a big
deal with smaller data sets.
* if you DO need to index wide char based fields and you are worred about
performance... you may want to consider creating a checksum based on the
chat column and indexing the checsum col (which will be an integer). That
would be much faster...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:e7LEw0RiEHA.344@.TK2MSFTNGP10.phx.gbl...
> Is column width a factor for an indexed column? Would indexing a
> VARCHAR(100) be a bad idea? Any links to discussion of this topic?
> Thanks for your advice.
> - Joe Geretz -
>